Backing up your valuable data is an inseparable part of running Microsoft SQL (MSSQL) Server workloads, due to the importance and value of data in today’s enterprises. SQL Server backup and restore operations can complexify as your number of databases grows and your recovery time and recovery point objectives approach zero. These factors make it critical for database administrators to find a backup strategy that suits their business needs.

With AWS, you have two options for running SQL Server instances. You can run your SQL Server instances on Amazon RDS, which is a managed database service that includes automatic backup plans. Alternatively, you can deploy SQL Server on Amazon EC2, which gives you full control of your infrastructure. When using EC2 to deploy SQL Server, you are responsible for implementing a backup solution, which can involve using either database-level backups or server-level backups. Database-level backups use native SQL Server backup capabilities, but are often cumbersome and complex at scale due to less backup granularity. Server-level backups can often be more nimble, using point-in-time snapshots that allow you to backup databases without having to consistently do full backups, but can often require special hardware.

AWS Backup provides a fully managed data protection solution across variety of AWS services, and simplifies creating VSS-enabled (Volume Shadow Copy Service) snapshots for application-consistent server-level backups of entire SQL Server instances. AWS Backup integrates with AWS Systems Manager to execute VSS-enabled snapshots as part of your regular backup plans.

In this blog post, we begin by covering the two different SQL Server backup strategies you can implement while using Amazon EC2, database-level backups and server-level backups. Then, we walk through simplifying a server-level backup and restore solution using AWS Systems Manager, AWS Backup, and VSS. This solution can help simplify your backup and restore strategy of SQL Server instances that host multiple databases, especially when you must back up all those databases together at exactly the same time.

SQL Server backup strategies

There are two methods for backing up your SQL Server database. These two options are database-level backup and server-level backup.

Database-level backup

This option uses native SQL Server backup capabilities. This type of backup uses a special blend of full database backups, differential backups, and transaction log backups (also known as T-log backups). Since restoring a full DB backup has the simplest restore process, more frequent full DB backups simplifies the overall backup and restore solution. However, full backups are inefficient in terms of storage utilization. Every time a full backup is taken, the entire content of the database is copied into the backup media.

To improve storage efficiency, you can also use differential and transaction log backups. However, in that case, instead of a single and simple full backup, you may have to restore a database from combination of a full backup and several differential and T-log backups. As the number of differential and T-log backups taken in between full backups increase, the restore solution also gets more complex. Furthermore, backing up a database and restoring it from a single full backup file can take a lengthy period. The bigger the database, the longer it takes to create its backup or restore it.

In many cases, an application might use several databases, all of which have to be restored to the same point in time. Point-in-time recovery is supported by SQL Server, but it’s also the most complex type of backup and restore operation, especially when it has to be performed on multiple databases at the same time.

Server-level backup

This option snapshots the underlying storage where SQL Server database files are stored. Using this method reduces the backup time to a few seconds, regardless of how large or small the databases are, as point-in-time snapshots allow you to backup databases without having to consistently do full backups. This method enables backing up the databases in the SQL Server instance, all at the same time, regardless of how many databases are hosted on the server.

Server-level backups can greatly simplify backup and restore operations. However, a major problem with server-level backups is the need to get special hardware, in addition to the complexity of setting up a VSS-enabled backup solution.

AWS Backup enables creating server-level backups using Amazon EBS snapshots through integration with AWS Systems Manager and Window VSS.

Block storage for server-level backups

Block storage is used by applications to persist files and data. Block storage can be a hard disk, a SAN, or another type of storage solution. AWS customers use Amazon EBS volumes for their block storage. Shadow copies, also known as snapshots, are point-in-time mirror images of the block storage used by applications. You can create snapshots from EBS volumes by invoking an AWS API. Once invoked, a snapshot is created from the state of the EBS volume at a specific point in time and subsequent changes to the volume do not impact the snapshot.

To ensure consistency of data and avoid any data loss, snapshots of block storage can be created in two ways. One way is to bring down all applications before taking the snapshot. This is typically done by stopping the server, and therefore means significant application downtime. The other way is to keep applications up and running, but take the snapshot exactly at a time when the underlying storage is in a consistent state.

To do this, there has to be a way to coordinate behavior of applications, storage media, and backup solution; this is where the VSS service jumps in.

Using AWS Systems Manager and VSS-enabled snapshots to back up SQL Server

Windows VSS is part of the Windows operating system. Windows applications can use the VSS service to enable application-consistent snapshots. For example, SQL Server supports VSS, and allows you create crash-consistent snapshots through the Windows VSS service.

When a backup solution wants to snapshot the underlying block storage, it first sends a request to the VSS service. The VSS service then tells all writer applications, such as SQL Server, to prepare for a snapshot. Once application becomes aware then the VSS service signals the backup provider to snapshot the block storage. AWS customers can do this by invoking the Amazon EBS snapshot API.

After initiating the snapshot, the backup provider signals the VSS service to notify its completion. Consequently, the VSS service also notifies all applications, such as SQL Server, to unfreeze and resume their write I/O operations.

AWS Backup supports VSS-enabled snapshots of EBS volumes attached to EC2 instances. With AWS Backup, you can centrally configure backup policies and monitor backup activity across your AWS resources. With just a few clicks in the AWS Backup console, you can create backup policies that automate backup schedules and retention management. AWS Backup enables customers to centralize and automate data protection across AWS services and accounts, helping them support their regulatory compliance obligations and meet their business continuity goals.

The VSS support in AWS Backup can simplify SQL Server backup and restore planning and operations and improve visibility across your entire fleet of servers. This is because now you can schedule and monitor MSSQL backup jobs directly from the AWS Backup console, which becomes a single pane of glass for these, in addition to other backup operations.

Walkthrough

The following diagram shows the workflow of AWS Backup taking VSS-enabled snapshots of MSSQL instances running on EC2 instances. The subsequent sections explain these steps and walk you through setting up this solution.

Workflow of taking server-level backups using AWS Backup service

Prerequisites

To back up and restore VSS-enabled Windows resources running Amazon EC2, the following are required for the target EC2 instances (instances that you want to back up).

1. Windows Server 2008 R2 or later (Windows Server 2008 R2 Core is currently not supported).

2. SSM Agent version 2.2.58.0 or later.

3. AWS Tools for Windows PowerShell 3.3.48.0 or later.

Prepare EC2 instance with AWS Systems Manager and installing VSS components

Following steps prepare the EC2 instance for running the VSS agent, which is a requisite to run VSS-enabled snapshots.

1. Create an IAM role and attach it to one or more target EC2 instance(s). There are two IAM policies that must be attached to this IAM role (instance profile):

• • An IAM policy that allows AWS Systems Manager to interact with the instance. For this, we are going to use the managed policy named AmazonSSMManagedInstanceCore.
  • A policy that allows VSS agent to take snapshots, as showed in following snapshot.

Note: You can restrict the preceding policies depending on the security needs of your environment. For the purpose of this blog, we are going to use the policies mentioned.

Attaching required managed and custom policies.

2. Open the AWS Systems Manager console.

3. In the navigation pane, choose Distributor and select AwsVssComponents.

4. Select Install one time or Install on a schedule. The install on a schedule option assures that the VSS agent gets updated based on the schedule that you can define in AWS Systems Manager State Manager. For the purpose of this blog, we are going to choose install one time.

5. Next, the console takes you to the Run a command AWS-ConfigureAWSPackage is already selected. Next, Install AwsVssComponents that are prepopulated as Command parameters.

For target instances, identify the instances on which you want to run this operation by specifying tags or selecting instances manually and leave other parameters as default.

6. Choose Run and then VSS components get installed on the EC2 instances.

Creating a Windows VSS backup in AWS Backup

After the VSS setup is completed, the AWS Backup console can be used to take consistent snapshot backups of EC2 instances that are running Microsoft SQL Server databases. With AWS backup, you can create on-demand backups or schedule backup plans.

Steps for scheduled backup plan

1. Navigate to the AWS Backup console.

2. On the AWS Backup console dashboard, select Manage Backup plans and choose Create Backup plan.

3. Choose Start with a template (you can also build a new plan using GUI or importing a JSON file).

4. Choose a template and provide a backup plan name.

5. Ensure that you select the check box for Windows VSS under Advanced backup settings. 

6. Once the backup plan is created, click on assign resources under resource assignments.

7. Provide a Resource assignment name, choose EC2 for Resource type, and choose the instances where you installed the VSS agent through previous steps.

Steps for on-demand backup

1. Navigate to the AWS Backup console.

2. On the AWS Backup console dashboard, select Create on-demand backup.

3. In the Advanced backup settings section, choose Windows VSS. This enables you to take application-consistent Windows VSS backups.

4. Select Create on-demand backup.

Conclusion

In this blog post, we covered how you can use AWS Backup with VSS to simplify the way you back up Microsoft SQL Server. Server-level backups are an option for backing up SQL Server databases, but require complex, customized, VSS-enabled backup solutions or even special hardware, preventing many businesses from adopting this method.

AWS Backup solves these problems through its integration with AWS Systems Manager and EBS volumes. With EBS volumes that provide snapshot capabilities as a simple software API, there is no need to get and maintain special hardware. Furthermore, AWS Backup leverages AWS Systems Manager to perform all the heavy lifting of executing VSS-enabled snapshots on target SQL Server instances. You can schedule application-consistent backups, define their lifecycle policies, and perform consistent restores with AWS Backup without requiring custom scripts or shutting down your instances. Server-level backups using AWS Backup are a practical solution to solve some of the real-world problems of backing up SQL Server databases.

Thanks for reading this post. We hope you found it helpful. If you have any feedback or questions, please leave them in the comments section.