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 configure a database for synchronous-commit mode in an Always On Availability Group:

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

SYNCHRONOUS_COMMIT

ASYNCHRONOUS_COMMIT

AUTOMATIC

MANUAL

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

ShipLog

BackupDB

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

STANDBY

RECOVERY

NORECOVERY

REPLACE

4 / 20

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

False

True

5 / 20

Users can configure SQL Server Availability Groups for system databases.

False

True

6 / 20

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

To delete an existing database

To create a communication endpoint for database mirroring

To modify the database schema

To create a new database

7 / 20

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

Any replica

Preferred Secondary

None of the above

Secondary only

8 / 20

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

net start SQLAGENT

net start MSSQLSERVER

net start SQLSERVICE

net start SQLSERVERAGENT

9 / 20

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

To ensure the database do not recover existing files

To delete the database

To keep the database in a restoring state

To recover the database

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

11 / 20

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

True

False

12 / 20

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

To configure the database for high availability and disaster recovery

To modify the database schema

To create a new database

To delete an existing databas

13 / 20

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

To improve query performance

To provide high availability and disaster recovery

To automate backups

To manage user permissions

14 / 20

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

ALTER AVAILABILITY GROUP MyAG
ADD DATABASE ___;

DB1

Database1

MyDB

MyDatabase

15 / 20

What is the purpose of the ALTER AVAILABILITY GROUP statement?

To create a new availability group

To delete an existing availability group

To modify the properties of an existing availability group

To modify the database schema

16 / 20

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

ALTER AVAILABILITY GROUP MyAG
SET (FAILOVER_MODE = ___);

SYNCHRONOUS

MANUAL

AUTOMATIC

ASYNCHRONOUS

17 / 20

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

MODIFY AVAILABILITY GROUP

CREATE AVAILABILITY GROUP

ADD SECONDARY REPLICA

ALTER AVAILABILITY GROUP

18 / 20

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

Node and Disk Majority

Node and File Share Majority

Node Majority

Node and Network Share Majority

19 / 20

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

sp_add_log_shipping_backup

sp_add_log_shipping_primary_database

sp_add_log_shipping_monitor

sp_add_log_shipping_secondary_database

20 / 20

How often is a T-log backup taken?

Every 15 minutes

Every hour

Every 24 hours

When SQL Server is opened

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!