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

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

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';

2 / 20

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

3 / 20

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

4 / 20

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

ALTER AVAILABILITY GROUP MyAG
ADD DATABASE ___;

5 / 20

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

6 / 20

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

ALTER AVAILABILITY GROUP MyAG
SET (FAILOVER_MODE = ___);

7 / 20

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

8 / 20

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

9 / 20

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

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

11 / 20

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

12 / 20

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

13 / 20

How often is a T-log backup taken?

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

15 / 20

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

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

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

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

19 / 20

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

20 / 20

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

Your score is

The average score is 0%

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!