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 command is used to start the SQL Server Agent service?

net start SQLSERVICE

net start MSSQLSERVER

net start SQLSERVERAGENT

net start SQLAGENT

2 / 20

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

Database Mirroring

Failover Clustering

Log Shipping

Always On Availability Groups

3 / 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'

BackupLog

BackupDB

LogBackup

ShipLog

4 / 20

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

To delete an existing databas

To configure the database for high availability and disaster recovery

To create a new database

To modify the database schema

5 / 20

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

CREATE AVAILABILITY GROUP MyAGWITH (CLUSTER_TYPE = ___)FOR DATABASE MyDatabaseREPLICA 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

6 / 20

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

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

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

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.

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

8 / 20

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

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

MANUAL

AUTOMATIC

SYNCHRONOUS_COMMIT

ASYNCHRONOUS_COMMIT

9 / 20

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

Secondary only

Preferred Secondary

Any replica

None of the above

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

1440

720

360

180

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

12 / 20

Complete the script to add a database to an existing Always On Availability Group:

ALTER AVAILABILITY GROUP MyAGADD DATABASE ___;

DB1

MyDatabase

Database1

MyDB

13 / 20

Users can configure SQL Server Availability Groups for system databases.

False

True

14 / 20

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

2 replicas

3 replicas

8 replicas

10 replicas

15 / 20

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

To recover the database

To ensure the database do not recover existing files

To keep the database in a restoring state

To delete the database

16 / 20

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

sp_add_log_shipping_secondary_database

sp_add_log_shipping_monitor

sp_add_log_shipping_primary_database

sp_add_log_shipping_backup

17 / 20

Complete the script to restore a log shipping secondary database:

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

RECOVERY

STANDBY

NORECOVERY

REPLACE

18 / 20

What is the purpose of the ALTER AVAILABILITY GROUP statement?

To delete an existing availability group

To modify the properties of an existing availability group

To create a new availability group

To modify the database schema

19 / 20

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

ALTER AVAILABILITY GROUP MyAGSET (FAILOVER_MODE = ___);

AUTOMATIC

MANUAL

ASYNCHRONOUS

SYNCHRONOUS

20 / 20

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

To create a new database

To modify the database schema

To set the database to full recovery mode

To delete an existing database

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!