Protecting SQL and Active Directory Running on AWS EC2

Your first thought when reading this title might be why run databases on Amazon Compute Cloud (EC2) instead of Amazon Relational Database Service (RDS)? At a high-level, RDS tends to be a great solution for smaller IT organizations who might not have dedicated databases administrators (DBAs), and/or organizations who don't have strict security and availability requirements. This won't provide a deep dive into when and why to choose one over the other as I assume you're running SQL/Active Directory on EC2s and looking for a way to protect and granularly restore if you're still reading this so let's dive in!

Prerequisites and Assumptions:

A lot of chatter and documentation exists on the basics to setup, configure and take crash consistent backups of EC2 instances with Veeam Backup for AWS. Instead this post will focus on the nuances and specifics to backup and restore SQL/AD on AWS EC2s. Before illustrating how-to backup and restore SQL let's meet the prereqs first.

1. Install AWS Systems Manager Agent (SSM) on any Windows EC2 instances that need app-aware processing. Most commonly these will be SQL or Active Directory servers. Invoke the below commands via PowerShell.

Invoke-WebRequest `
    https://s3.amazonaws.com/ec2-downloads-windows/SSMAgent/latest/windows_amd64/AmazonSSMAgentSetup.exe `
    -OutFile $env:USERPROFILE\Desktop\SSMAgent_latest.exe

Start-Process `
    -FilePath $env:USERPROFILE\Desktop\SSMAgent_latest.exe `
    -ArgumentList "/S"

rm -Force $env:USERPROFILE\Desktop\SSMAgent_latest.exe

2. Install the VSS package via PowerShell which is used to achieve app-aware processing for Windows machines.

Set-AWSCredentials –AccessKey {key_name} –SecretKey {key_name}

Set-DefaultAWSRegion -Region us-east-2 (enter the region you're using)

Send-SSMCommand -DocumentName AWS-ConfigureAWSPackage -InstanceId "instance_ID"-Parameter @{'action'='Install';'name'='AwsVssComponents'}

3. Install Veeam's External Repository in Veeam Backup and Replication server. This is covered well here in public documentation.

Protecting SQL and Active Directory Running on EC2s

Veeam offers AWS native protection for SQL and AD workloads. You can schedule both snapshots and backups to S3 Buckets. For organizations with more stringent backup and archival needs this is crucial because backing up to S3 is significantly cheaper than storing snapshots. EBS snapshots are ~$.05 per GB whereas S3 is ~$.02 per GB depending on which region you live in. If you have a retention of 30 days and tens or hundreds of TBs this can become very costly to say the least. Similar to on-premises, snapshots should still be leveraged for short-term retentions and quick recoveries, but long-term retentions should be placed on cheaper media for an overall better total cost of ownership to the business.

1. Enable app-aware processing on the policy. For bonus setup your policies by tag to automate the backup of SQL and AD servers

2. Define snapshot, backup and replication schedules and retentions to meet the availability needs for your mission critical servers.

3. Verify integration with VSS.

Restore SQL and Active Directory Running on EC2s

Now for the fun part! What good is a backup without a restore? And the beauty of this integration is you can leverage the famous Veeam Explorers for SQL and AD for granular recoveries.

1. Granularly restore databases by going to Veeam's External Repository and select Application Item Restore.

2. Restore a specific item, entire database or publish a database. Publishing is a common task to spin-up an environment for test/dev or for DBAs to run DBCC CHECKDB commands to verify they have a usable backup.

3. Verify Active Directory backups match production by opening the Veeam Explorer for AD. This is a great feature that scans the backup files for any changes compared to production. If anything looks odd you can restore only the differences.

Not only it is important to have a solution that can protect your databases running on EC2s, but also it is crucial that the solution is cost effective. Having the ability to orchestrate snapshots, backups and replicas from one console and one license empowers IT organizations to design a solution that meets their availability needs on a budget.

#SQL #AWS #EC2 #AD #dataprotection #backup