Implementing a Resilient SQL Server Hybrid Cloud Solution

Overview

In this hands-on lab, you will build a disaster recovery site for an on-premises environment. You will enhance the existing database solution to support a hybrid cloud-based disaster recovery solution, implement an archiving strategy and a backup/restore strategy designed to protect data.

At the end of the lab, you will be better able to design and use availability sets, Managed Disks, SQL Server Always on Availability Groups, Stretch DB and SQL Server Managed Backup.

Contoso has asked you to deploy their infrastructure in a resilient manner to ensure their infrastructure will be available for their users and gain an SLA from Microsoft.

Solution Architecture

The solution architecture involves a cloud-based disaster recovery site. The key components are:

![Enterprise disaster recovery architecture.][]

Key Exercises

  1. Configure Azure Key Vault integration with the SQL VM resource provider: Integrate SQL Server VMs with Azure Key Vault to securely manage encryption keys and enable Transparent Data Encryption (TDE).
  2. Implement a SQL Backup solution: Leverage the automated backup feature provided by the SQL Server resource provider to maintain backups offsite and accessible to the DBA team.
  3. Implement a Data Archive Strategy with SQL Server Stretch Database: Use SQL Server Stretch Database to automatically archive older data to Azure, freeing up storage on the on-premises database.
  4. Create an archive solution using table partitioning: Partition the production database tables to separate historical data from current data, minimizing the impact of the archival process.
  5. Build SQL Availability Group for Database Disaster Recovery: Deploy a Windows Failover Cluster and SQL Always-On Availability Group to ensure high availability and disaster recovery for the database tier.
  6. Configure Azure Site Recovery for Web Tier DR: Use Azure Site Recovery to protect the web application and enable failover to the disaster recovery site.
  7. Failing Over to the Disaster Recovery Site: Perform the failover of the data tier and web tier to the disaster recovery site.

By the end of this hands-on lab, you will have enhanced the existing infrastructure to be more resilient, highly available, and able to quickly recover from disasters, all while optimizing storage and performance.

Requirements

  • Microsoft Azure Subscription
  • Virtual Machine built during the hands-on lab or a local machine with the latest Azure PowerShell cmdlets installed

Help References