How to backup large SQL Server using AWS VSS Snapshot
Hosting windows machines on AWS cloud is very efficient. You can enjoy all windows functionalities, including backing up entire machines containing large SQL servers in seconds. With snapshots, you can spin up new identical machines in a matter of minutes, which can serve for testing environments or for any other purpose you may need.
What is VSS?
VSS is a technology developed by Microsoft that allows backup applications to safely back up locked and open files, and prevents data inconsistency during backups. Supports Microsoft SQL Server and Exchange Server.
Step #1 – Create IAM role for your instance Navigate to the IAM service on aws console – https://console.aws.amazon.com/iam/home.
- Create a VSS Policy:
- From the left menu click on Policies, and then Create policy.
- Switch to JSON tab, and copy/paste the following json:
- Click on Review policy.
- Name the Policy as you wish, I’ll be naming it VSS.
- Click on Create policy.
- You should now be redirected to the Policies page and see a success message on the top of the page.
2. Create a role for your EC2 instance
- From the left menu click on Roles, and then Create role.
- Choose use case as EC2
- Next, attach permissions policies.
- Search and select the following policies:
- AmazonEC2RoleforSSM (this is so we can grant access to AWS system manager)
- VSS (or the name you gave the policy we created above)
- Next, review and name the role (I’ll be calling it VSSAccess).
- Click on Create role.
- You should be redirected now to the Roles page and see a success message on the top of the page.
3. Attach the role to your EC2 instance
- Navigate to the EC2 service on aws console – https://console.aws.amazon.com/ec2/v2/home.
- From the left menu click on Instances.
- Find the instance you would like to create a vss-snapshot for, and right click on it.
- Under Instance Settings, click on Attach/Replace IAM Role.
- From the IAM role dropdown list choose the role we created, in my case it will show as VSSAccess.
- Click Apply.
- You should get a success message.
- Click Close.
This will allow the instance to be managed by aws systems manager services on your behalf, which we will be using for creating our vss-snapshots.
Step #2 – Install VSS components on the instance
- Navigate to the AWS Systems Manager service – https://console.aws.amazon.com/systems-manager/home.
- From the left menu click on State Manager and then on Create an Association.
- Name your association, I’ll be naming it VSSinstall.
- Search and select the command document – AWS-ConfigureAWSPackage.
- Under Parameters set:
- Action to Install
- Name (this is case sensitive), I’ll be naming it AwsVssComponents
- Version to latest
- Under Targets select Manually Selecting Instance option.
- Select your Instance.
- Under Specify schedule select On Schedule option.
- Choose specify with Rate schedule builder (this will make sure that the instance is always running the latest version of the VSS components).
- Set the time under Association runs, I’ll be setting it to every 30 days.
- Click on Create Association.
- You should be redirected now to the State Manager page and see the Association we created in status Pending.
- Once status switches to Success the installation is completed.
The instance is now ready for the vss-snapshot to be created.
Step #3 – Setup automatic vss-snapshots
- Create Maintenance window
- Navigate to AWS Systems Manager service – https://console.aws.amazon.com/systems-manager/home.
- From the left menu click on Maintenance Windows and then on Create maintenance window.
- Name and describe the maintenance.
- Check the Allow unregistered targets option.
- Under Schedule section set the following:
- Specify with – choose Cron schedule builder.
- Window starts – choose when you would like the maintenance to run (I’ve set mine to ‘Every Day at 21:00’).
- Duration – 1 hour.
- Stop initiating tasks – 0 hour before the window closes.
- Click on Create maintenance window.
- You will now be redirected to the main maintenance windows page, and you should see the maintenance you created listed.
2. Add Task to Maintenance window
- Click on the maintenance window you just created.
- Navigate to the Tasks tab
- From the Register tasks’ drop down menu, select Register Run command task.
- Name and describe the task, I’ll be naming mine VssSnapshot.
- Under Command document search and select AWSEC2-CreateVssSnapshot
- Set Document version to Default version at runtime.
- Under Targets, choose ‘Selecting unregistered targets’ and select your windows instance.
- Under Rate control, set 1 targets and 1 errors.
- Under IAM service role choose Use the service-linked role for Systems Manager
- Under Parameters set the following:
- Description – vss snapshot created by Maintenance Window.
- Tags – Key=Name,Value=VSS Snapshot
- Click on Register Run command task.
You’re all set! Your windows instance is now taken care of.
Need help? Contact us at email@example.com