Backup and Restore Your Microsoft SQL Server on AWS Cloud – Part 2

n2ws In our previous article, we discussed the built-in backup and restore processes for MSSQL databases.

In this article, we will look at AWS cloud’s specific backup and restore procedures for MSSQL servers.

With AWS, the process is easy—users can either create AMIs or take snapshots of EBS volumes, which employ point-in-time backups. While there are multiple ways of using snapshots, to keep things simple, we will focus on either manually processing them (which might involve maintaining multiple scripts), or using Cloud Protection Manager (CPM).

CPM is a full-featured enterprise-class backup, recovery and disaster recovery solution for Amazon EC2 Instances, EBS volumes, and RDS databases, utilizing AWS’ native EBS and RDS snapshots.

Creating an Amazon EBS Snapshot

Amazon uses EBS snapshots to back up and restore volumes with a single click. After writing data to an Amazon EBS volume, you can periodically create snapshots of the volume to use as a baseline for new volumes or for data backup. Even though snapshots are saved incrementally, the snapshot deletion process is designed so that you only need to retain the most recent snapshot in order to restore the volume.

Snapshots have several advantages, such as:

  • Point-in-time data backup & restore
  • Disaster recovery and high availability – users can create new volumes in separate AZs or even copy snapshots to a separate region in order to create a volume in that region
  • Increased EBS volume size
  • Immediate instance and data recovery for improved RTO (recovery time objective)

To create a snapshot using the console:

  1. Open the Amazon EC2 console.
  2. Click Snapshots in the navigation pane.
  3. Click Create Snapshot.
    n2ws
  4. In the Create Snapshot dialog box, select the volume you’d like to create a snapshot for, then click Create.

This is the simplest method. It lets you create a backup of your volume in a single click. Nonetheless, it is important to note that these snapshots are crash-consistent and might have recovery issues.
This is because a process could be in progress or there might be data in memory when the snapshot is taken.
In addition, logical volumes that were created from multiple disks, may have inconsistent data since it may have been in the process of being written on disk, or more than one disk, in a single I/O operation.
One more challenge with crash-consistent backup is that there is no way to automatically truncate database transaction logs after backup.

Backup and Restore Using CPM

CPM provides a simple, intuitive, and user-friendly web interface to easily manage your EC2 backup operations. People who work in production environments understand the importance of automated backup and recovery options. CPM offers an advantage on top of Amazon services, making backup administrators’ lives easier.

CPM has many features that make it a suitable option for backing up AWS cloud-hosted MSSQL servers. Along with its flexible backup policies, CPM retries when failures occur. It can also copy EBS volumes to other regions. You can restore a complete backup with a single click.

One of the advantages of CPM is achieving consistent and persistent application data backup. CPM employs VSS support for Windows machines, which creates shadow copies of multiple volumes in a synchronized manner. The concept is to freeze applications that use multiple volumes; for example, an MSSQL database will have a volume for data and a separate volume for transaction logs. Here, CPM helps achieve the goal of backing up multiple volumes at a single point in time using the VSS utility.

CPM supports continuous and persistent backup and DR both for MSSQL AWS cloud-hosted (run on an EC2 instance) as well MSSQL stored in Amazon RDS (managed relational database service). For RDS, CPM supports automatic and flexible backup scheduling as well as the capability to provide enhanced RPO (Recovery Point Objective) policy. Learn more

In this article we will discuss how to backup your AWS cloud-hosted MSSQL using CPM.

Setting up CPM
CPM allows you to create backup policies and schedules. These are used to identify the objects (instances/volumes/RDS) that you want to backup and schedule when backups should be performed.
Below, you can see the AWS account credentials that we used to set up our CPM account. It is recommended to create an IAM login with AWS that holds the required permissions instead of providing your root account credentials.

After setting up the account, you will see an entry similar to the one below on your CPM home page.
bar-microsoft

Scheduling Backup in CPM
CPM allows you to schedule backups by the minute, hour, day, or week. The screenshot below shows that we have scheduled a backup every four hours.
n2ws

Setting up Backup Policies in CPM
Setting up a backup policy is the most important part of CPM. Providing you with the ability to select various options, CPM determines whether backup scripts, DR policies, and other various options should be used. It defines logical groupings of objects (backup targets) that should be backed up together, such as EC2 instances, EBS snapshots/volumes, and RDS instances.

The backup policy created below is linked to the schedule created above at four-hour intervals.
n2ws

Our backup target is the AWS instance where our MSSQL is hosted. There is no need to set up EBS volumes individually if you have multiple EBS volumes that are attached to a single instance. CPM will take care of that.
n2ws

Setting up Backup Targets
Simply click the backup target on the policy you defined, then select the instance from your AWS instance, as shown below.
n2ws

It is not always mandatory to configure backup options, however configurations can be performed in Policies.
If you are working with a Windows instance, it is recommended to enable the VSS (Volume Shadow Copy Service) agent on the instance.

VSS is a consistent backup infrastructure for Windows servers. VSS is very useful for cases where exact point in time backup across volumes/disks is needed. VSS notifies MSSQL that it is being backed up and makes sure backup is consistent across all volumes.

Learn more about Volume Shadow Copy Service (VSS).

To setup VSS, first take note of the backup agent key that was created as part of the options.

n2ws

CPM only supports VSS on Windows 2008 or 2012 servers. CPM uses the “System Provider” to perform shadow copies.

This process is very simple — when CPM creates a backup, the CPM Thin Backup Agent asks the system provider to create shadow copies of all relevant volumes. In this case, differential copies are created, so the complete data of the volume is not copied, only changes that were made from the time the backup began.

As discussed above, crash-consistent snapshots will not truncate log files when the transaction is complete. CPM provides consistent application backup, which will automatically truncate the logs. You can execute this script after CPM completes the snapshot by inserting it in the complete script in the policy.

We first set up the CPM Thin Backup Agent on your EC2 instance to enable VSS in the backup process.
You can download the CPM Thin Backup Agent from your CPM server (you can find it at the footer of the CPM system) as shown below-
n2ws

Once downloaded –
n2ws

Monitoring Your Backup
You can monitor the backup jobs you have scheduled by clicking the Backup Monitor tab.
n2ws

In the section below, we will show you how to backup and restore data using CPM, looking at a sample case where certain data was stored and deleted by mistake.
We will use CPM to restore the lost data.
The MSSQL server has an efy database and a table named Persons in it. The screenshot below shows a sample data record of the table.
n2ws

A table may be deleted due to an error, as seen below with the Persons table.
n2ws

Restore Using CPM
If we set up CPM correctly, it is likely that it took a snapshot before the table was accidentally deleted.
You can find a Recover option on the same screen where you monitor backups. After you click the Recover button, select Volumes Only and select all volumes as the recovery option in the following screen.
n2ws

Select the volume or the instance you need to recover.
n2ws

The restore option is easy (one click).

Once the data is restored, CPM will create a new volume and attach it to the running EC2 instance. As shown in the screenshot below, once data is restored, both our table and the data that were available in MSSQL when CPM took the snapshot, are available again in MSSQL.
n2ws

Final Note:

We all know that AWS offers durable block level storage with EBS volumes that are very useful for data persistence. EBS also offers snapshots as a useful mechanism for backup and recovery. Nonetheless, if not automated, EBS snapshots present a few challenges:

  • You need to remember to take snapshots at regular intervals.
  • Snapshots may not be persistent when there are large or multi-striped EBS volumes. The consistency issue may happen for a single EBS volume, too, with non-committed or in memory data.
  • If a number of snapshots are created, their management and cleaning process is cumbersome.

However, CPM makes automated backups and snapshot management much easier. By providing an efficient way to regularly backup your system, CPM ensures that Windows instances stay consistent with VSS, which is crucial for critical production environments, and quick data restoration.

Share this post →

You might also like: