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

by

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 create a failover cluster instance:

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

2 / 20

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

3 / 20

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

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

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

6 / 20

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

7 / 20

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

8 / 20

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

ALTER AVAILABILITY GROUP MyAG
ADD DATABASE ___;

9 / 20

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

10 / 20

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

11 / 20

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

12 / 20

How often is a T-log backup taken?

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

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

15 / 20

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

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

17 / 20

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

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

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

ALTER AVAILABILITY GROUP MyAG
SET (FAILOVER_MODE = ___);

20 / 20

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

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!