SQL Server High Availability and Disaster Recovery: Expert-Level Quiz

Test your mastery of the complexities of SQL Server High Availability (HA) and Disaster Recovery (DR) with this expert-level quiz. These questions are designed to evaluate your knowledge of advanced HA/DR practices, focusing on practical implementation through scripts and code and general practical questions. The quiz is for seasoned professionals looking to validate their expertise and ensure their SQL Server environments are resilient and reliable.

1 / 20

Which of the following commands is used to configure a database for log shipping?

sp_add_log_shipping_backup

sp_add_log_shipping_primary_database

sp_add_log_shipping_secondary_database

sp_add_log_shipping_monitor

2 / 20

which of the following is not a High Availability and Disaster Recovery solution that is available in Microsoft SQL Server?

Database Mirroring

Prompt Assessment

Log Shipping

Transactional Replication

3 / 20

Complete the script to configure a listener for an Always On Availability Group:

CREATE AVAILABILITY GROUP LISTENER MyListener
WITH IP (('192.168.1.100', '255.255.255.0'))
PORT = ___;

5023

5022

1433

1434

4 / 20

Do you have to kill all SQL Server connections before restoring a database?

False

True

5 / 20

Users can configure SQL Server Availability Groups for system databases.

True

False

6 / 20

Which command adds a secondary replica to an Always On Availability Group?

ADD SECONDARY REPLICA

MODIFY AVAILABILITY GROUP

CREATE AVAILABILITY GROUP

ALTER AVAILABILITY GROUP

7 / 20

AlwaysOn Failover Cluster Instance needs shared storage between all the cluster nodes. AlwaysOn Availability Groups do not require shared disk storage.

False

True

8 / 20

What is the purpose of the ALTER DATABASE statement with the SET HADR option?

To configure the database for high availability and disaster recovery

To create a new database

To delete an existing databas

To modify the database schema

9 / 20

Fill in the missing code to configure a read-only routing list:

ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'PrimaryServer'
WITH (SECONDARY_ROLE (___________ = ('SecondaryServer')));

WRITE_ROUTING_LIST

READ_WRITE_ROUTING_LIST

READ_ONLY_ROUTING_LIST

READ_ROUTING_LIST

10 / 20

Which command is used to start the SQL Server Agent service?

net start SQLAGENT

net start MSSQLSERVER

net start SQLSERVERAGENT

net start SQLSERVICE

11 / 20

Which of the following is not a backup preference options are supported in SQL Server Always On Availability Group?

Any replica

Secondary only

None of the above

Preferred Secondary

12 / 20

Fill in the missing code to configure a database for synchronous-commit mode in an Always On Availability Group:

ALTER AVAILABILITY GROUP MyAG
MODIFY REPLICA ON 'PrimaryServer'
WITH (AVAILABILITY_MODE = ___);

ASYNCHRONOUS_COMMIT

SYNCHRONOUS_COMMIT

AUTOMATIC

MANUAL

13 / 20

Which of the following is NOT a valid SQL Server Failover Cluster Quorum configuration?

Node and File Share Majority

Node and Disk Majority

Node Majority

Node and Network Share Majority

14 / 20

What is the purpose of the CREATE ENDPOINT statement in SQL Server?

To modify the database schema

To create a new database

To delete an existing database

To create a communication endpoint for database mirroring

15 / 20

Complete the script to restore a log shipping secondary database:

RESTORE DATABASE MyDatabase
FROM DISK = 'C:\LogShipping\Backup\MyDatabase.bak'
WITH _________,
MOVE 'MyDatabase_Data' TO 'C:\Data\MyDatabase.mdf',
MOVE 'MyDatabase_Log' TO 'C:\Logs\MyDatabase.ldf';

REPLACE

RECOVERY

STANDBY

NORECOVERY

16 / 20

Which of the following is the correct sequence of steps to manually failover an Always On Availability Group?

Suspend the secondary replica, sync the databases, then initiate the failover.

Back up transaction logs, initiate failover, then restore transaction logs on the new primary.

Set the secondary replica as READ_WRITE, sync the databases, and switch listeners.

Ensure the secondary replica is synchronized, manually initiate the failover, and verify status.

17 / 20

What is the purpose of the ALTER DATABASE statement in configuring database mirroring?

To create a new database

To delete an existing database

To modify the database schema

To set the database to full recovery mode

18 / 20

Complete the script to configure a failover policy for an Always On Availability Group:

ALTER AVAILABILITY GROUP MyAG
SET (FAILOVER_MODE = ___);

ASYNCHRONOUS

MANUAL

SYNCHRONOUS

AUTOMATIC

19 / 20

Complete the script to configure a backup schedule for log shipping:

EXEC sp_add_schedule
@schedule_name = 'LogShippingBackupSchedule',
@freq_type = 4, -- Daily
@freq_interval = 1,
@active_start_time = ___;

0200

0100

0000

0300

20 / 20

How many secondary replicas can be configured in SQL Server 2016?

8 replicas

2 replicas

3 replicas

10 replicas

Your score is

The average score is 0%

In today’s digital and database world, ensuring SQL Server high availability and disaster recovery is crucial. In addition, it is important that you’re prepared for any unexpected issues to keep your business moving smoothly and your data safe.

This quiz is ideal for seasoned SQL Server experts who want to showcase their skills in setting up and looking after complex high availability and disaster recovery setups. It’s also a great way to prove you’re at the top of your game in ensuring your SQL environments are resilient and reliable.

Good luck!