Manage high availability and disaster recovery

Thought experiment

In this thought experiment, demonstrate your skills and knowledge of the topics covered in this chapter. You can find answers to this thought experiment in the next section.

You work as a Database Administrator for World Wide Importers. You need to design a disaster recovery and high availability strategy for your multi-database solution that is used internally. Your company has a primary office in Sydney and another office in Wagga Wagga.

The multi-database solution has the following characteristics:

• The main OLTP database is 400GB in size

• There is a 200GB database which is used for auditing and logging records

• There are 5 more databases that are used. They are all under 50GB in size.

• All databases currently use the full recovery model.

• All databases are hosted on a single SQL Server instance

• OLAP and real-time reports are impacting performance of the OLTP transactions

You have an existing 2 Node failover cluster based in Sydney that hosts a vendor database solution. This database solution does not support Availability Groups.

Management has asked you to solve the following business problems:

• The business requires a high availability solution that supports automatic failover.

• The databases should be highly available in Sydney’s data center.

• If that data center in Sydney fails the disaster recovery solution should have Wagga Wagga with an RTO of 2 hours and RPO of 15 minutes.

• Management wants to reduce the impact of running reports on the OLTP transactions. Analysts in Wagga Wagga want to report off a “snapshot” of the database at close of business on the previous day.

Question 1

Management wants you to create a high-availability solution strategy for the multi-database solution that meets their requirements. What high availability solution should you use:

1. Create an Availability Group with 4 replicas:

   • 3 replicas in Sydney

   • 2 synchronous replicas in Sydney will be used as failover partners

   • 1 readable synchronous replica in Sydney for OLAP reporting

   • 1 asynchronous secondary replica in Wagga Wagga

2. Create an Distributed Availability Group with 4 replicas:

   • 3 replicas in Sydney

   • 2 synchronous replicas in Sydney will be used as failover partners

   • 1 readable synchronous replica in Sydney for OLAP reporting

   • 1 asynchronous secondary replica in Wagga Wagga

3. Create a 3 node failover cluster:

   • 2 nodes will be based in Sydney

   • 1 node will be based in Wagga Wagga

4. Create an Availability Group in Sydney. Use log shipping between Sydney and Wagga Wagga:

   • 3 replicas in Sydney

   • 2 synchronous replicas in Sydney will be used as failover partners

   • 1 readable synchronous replica in Sydney for OLAP reporting

   • Perform log backups every 15 minutes

Question 2

Management wants to extend the failover cluster to Wagga Wagga. They plan to add two more nodes to the cluster in Wagga Wagga. What quorum configuration should you use?

1. Use a node majority quorum with no witness.

2. Use a node majority quorum with a file share witness.

3. Use a node majority with a cloud witness

4. Use a node majority with a disk witness.

Question 3

After implementing an Availability Group for your 400GB OLTP database you notice that the physical disk that has been provisioned for the database’s MDF file is running out of space. Management has provisioned a new 1TB PCIe SSD for the database on all replicas. You need to ensure that the database does not run out of space with minimal downtime while maintaining high availability. What should you do?

1. Perform the following actions:

   • Take the database offline

   • Detach the database

   • Move the MDF files to the new storage

   • Attach the database

2. Perform the following actions:

   • Suspend Availability Group

   • Backup the database

   • Drop the database

   • Restore database to new storage

   • Resume Availability Group

   • Wait for secondary replicas to replicate your changes

3. Perform the following actions:

   • Remove the 400GB database from the Availability Group

   • Detach the database

   • Move the database file to the new storage

   • Attach the database

   • Drop the 400GB database from all secondary replicas

   • Add the database back to the Availability Group with the Direct Seeding option

4. Perform the following actions:

   • Add a new secondary file for the database on the new storage