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

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

2 / 20

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

sp_add_log_shipping_primary_database

sp_add_log_shipping_monitor

sp_add_log_shipping_backup

sp_add_log_shipping_secondary_database

3 / 20

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

None of the above

Secondary only

Any replica

Preferred Secondary

4 / 20

Users can configure SQL Server Availability Groups for system databases.

True

False

5 / 20

Complete the script to create a failover cluster instance:

CREATE CLUSTERED INDEX MyClusteredIndex
ON MyTable (MyColumn)
WITH (ONLINE = ___);

TRUE

FALSE

OFF

6 / 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 = ___;

0300

0000

0200

0100

7 / 20

What is the primary purpose of Always On Availability Groups in SQL Server?

To provide high availability and disaster recovery

To manage user permissions

To automate backups

To improve query performance

8 / 20

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

True

False

9 / 20

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

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

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

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

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

10 / 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')));

READ_ROUTING_LIST

READ_ONLY_ROUTING_LIST

WRITE_ROUTING_LIST

READ_WRITE_ROUTING_LIST

11 / 20

Fill in the missing code to create an Always On Availability Group:

CREATE AVAILABILITY GROUP MyAG
WITH (CLUSTER_TYPE = ___)
FOR DATABASE MyDatabase
REPLICA ON 'PrimaryServer' WITH (
    ENDPOINT_URL = 'TCP://PrimaryServer:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC),
'SecondaryServer' WITH (
    ENDPOINT_URL = 'TCP://SecondaryServer:5022',
    AVAILABILITY_MODE = SYNCHRONOUS_COMMIT,
    FAILOVER_MODE = AUTOMATIC);

EXTERNAL

BASIC

WSFC

NONE

12 / 20

When configuring log shipping, which T-SQL command is used to back up the transaction log from the primary database for log shipping?

BACKUP LOG [DB_Name] TO DISK = N'\\NetworkPath\____.trn'

LogBackup

ShipLog

BackupLog

BackupDB

13 / 20

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

10 replicas

3 replicas

8 replicas

2 replicas

14 / 20

What is the purpose of the ALTER AVAILABILITY GROUP statement?

To delete an existing availability group

To modify the database schema

To modify the properties of an existing availability group

To create a new availability group

15 / 20

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

ALTER AVAILABILITY GROUP MyAG
SET (FAILOVER_MODE = ___);

ASYNCHRONOUS

SYNCHRONOUS

AUTOMATIC

MANUAL

16 / 20

Fill in the missing code to configure log shipping:

EXEC sp_add_log_shipping_primary_database
@database = 'MyDatabase',
@backup_directory = 'C:\LogShipping\Backup',
@backup_retention_period = ___;

360

180

720

1440

17 / 20

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

net start SQLSERVERAGENT

net start SQLAGENT

net start MSSQLSERVER

net start SQLSERVICE

18 / 20

Which SQL Server feature allows you to create a secondary replica for read-only access?

Database Mirroring

Failover Clustering

Always On Availability Groups

Log Shipping

19 / 20

What is the purpose of the RESTORE DATABASE statement with the NORECOVERY option?

To keep the database in a restoring state

To recover the database

To ensure the database do not recover existing files

To delete the database

20 / 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

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!