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

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

BASIC

WSFC

NONE

EXTERNAL

2 / 20

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

Always On Availability Groups

Database Mirroring

Log Shipping

Failover Clustering

3 / 20

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

ALTER AVAILABILITY GROUP MyAG
SET (FAILOVER_MODE = ___);

SYNCHRONOUS

AUTOMATIC

MANUAL

ASYNCHRONOUS

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

STANDBY

NORECOVERY

RECOVERY

5 / 20

What is the purpose of the ALTER AVAILABILITY GROUP statement?

To create a new availability group

To modify the properties of an existing availability group

To modify the database schema

To delete an existing availability group

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

MANUAL

AUTOMATIC

SYNCHRONOUS_COMMIT

7 / 20

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

2 replicas

10 replicas

3 replicas

8 replicas

8 / 20

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

ADD SECONDARY REPLICA

ALTER AVAILABILITY GROUP

CREATE AVAILABILITY GROUP

MODIFY AVAILABILITY GROUP

9 / 20

How often is a T-log backup taken?

Every 24 hours

Every hour

When SQL Server is opened

Every 15 minutes

10 / 20

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

Log Shipping

Database Mirroring

Prompt Assessment

Transactional Replication

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

720

360

180

1440

12 / 20

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

ALTER AVAILABILITY GROUP MyAG
ADD DATABASE ___;

Database1

MyDatabase

MyDB

DB1

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

LogBackup

BackupDB

ShipLog

14 / 20

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

net start MSSQLSERVER

net start SQLSERVICE

net start SQLSERVERAGENT

net start SQLAGENT

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

5022

1433

5023

1434

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

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.

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

18 / 20

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

To keep the database in a restoring state

To ensure the database do not recover existing files

To delete the database

To recover the database

19 / 20

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

True

False

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

READ_ROUTING_LIST

READ_ONLY_ROUTING_LIST

WRITE_ROUTING_LIST

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!