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 of the following is not a backup preference options are supported in SQL Server Always On Availability Group?

Secondary only

Preferred Secondary

None of the above

Any replica

2 / 20

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

Log Shipping

Failover Clustering

Database Mirroring

Always On Availability Groups

3 / 20

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

To create a new database

To modify the database schema

To delete an existing databas

To configure the database for high availability and disaster recovery

4 / 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 improve query performance

To automate backups

5 / 20

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

ADD SECONDARY REPLICA

ALTER AVAILABILITY GROUP

MODIFY AVAILABILITY GROUP

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

MANUAL

SYNCHRONOUS_COMMIT

ASYNCHRONOUS_COMMIT

AUTOMATIC

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

WRITE_ROUTING_LIST

READ_ONLY_ROUTING_LIST

8 / 20

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

True

False

9 / 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 delete the database

To ensure the database do not recover existing files

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

NONE

BASIC

EXTERNAL

WSFC

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

sp_add_log_shipping_monitor

sp_add_log_shipping_secondary_database

12 / 20

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

ALTER AVAILABILITY GROUP MyAG
ADD DATABASE ___;

MyDB

DB1

MyDatabase

Database1

13 / 20

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

net start SQLAGENT

net start MSSQLSERVER

net start SQLSERVERAGENT

net start SQLSERVICE

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

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

1440

720

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

5023

1434

1433

5022

17 / 20

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

ALTER AVAILABILITY GROUP MyAG
SET (FAILOVER_MODE = ___);

SYNCHRONOUS

MANUAL

ASYNCHRONOUS

AUTOMATIC

18 / 20

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

To create a communication endpoint for database mirroring

To create a new database

To delete an existing database

To modify the database schema

19 / 20

Users can configure SQL Server Availability Groups for system databases.

True

False

20 / 20

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

To set the database to full recovery mode

To create a new database

To modify the database schema

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!