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

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

False

True

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

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

MANUAL

AUTOMATIC

ASYNCHRONOUS_COMMIT

4 / 20

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

net start SQLSERVERAGENT

net start SQLAGENT

net start SQLSERVICE

net start MSSQLSERVER

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

EXTERNAL

BASIC

NONE

WSFC

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

READ_ROUTING_LIST

WRITE_ROUTING_LIST

7 / 20

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

8 replicas

3 replicas

2 replicas

10 replicas

8 / 20

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

To delete an existing databas

To modify the database schema

To create a new database

To configure the database for high availability and disaster recovery

9 / 20

How often is a T-log backup taken?

Every 24 hours

Every 15 minutes

When SQL Server is opened

Every hour

10 / 20

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

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.

Ensure the secondary replica is synchronized, manually initiate the failover, and verify status.

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

5023

1433

1434

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

13 / 20

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

sp_add_log_shipping_monitor

sp_add_log_shipping_secondary_database

sp_add_log_shipping_backup

sp_add_log_shipping_primary_database

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

NORECOVERY

STANDBY

REPLACE

RECOVERY

15 / 20

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

CREATE AVAILABILITY GROUP

ALTER AVAILABILITY GROUP

ADD SECONDARY REPLICA

MODIFY AVAILABILITY GROUP

16 / 20

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

To create a new database

To delete an existing database

To modify the database schema

To set the database to full recovery mode

17 / 20

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

False

True

18 / 20

Complete the script to create a failover cluster instance:

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

FALSE

TRUE

OFF

19 / 20

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

Prompt Assessment

Transactional Replication

Log Shipping

Database Mirroring

20 / 20

Users can configure SQL Server Availability Groups for system databases.

True

False

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!