Introduction

Ensuring the safety and availability of your database backups is crucial for any organization. While storing backups in a single location may seem convenient, it can be risky. In the event of a crisis, having a backup stored offsite provides an extra layer of protection. This article will guide you through the process of syncing your SQL Server database backups from AWS EC2 to an AWS S3 bucket.

Problem

Although the public cloud has revolutionized data storage, the need for offsite backups remains. Even with highly reliable provisioned AWS EBS disks, the possibility of an entire zone going down cannot be ignored. To mitigate this risk, it is essential to have a backup stored in a different zone. Unfortunately, AWS does not offer a native backup to URL option for SQL Server like Microsoft Azure does. However, with the help of the AWS Command Line Interface (CLI), we can easily sync our backup files to an S3 bucket.

Solution

Follow these steps to sync your database backup files to an S3 bucket:

1. Install the AWS Command Line Interface for Windows from here.
2. Create a new S3 bucket (if necessary) to store your backup files.
3. Use a PowerShell file to initiate the synchronization process between your local backup folder and the S3 bucket. The PowerShell file will call the AWS s3 sync command, which copies missing or outdated files between the source and target. You can also use the –delete option to remove files from the target that are not present in the source.
4. Add both the config and credentials files to the .aws folder under the user folder. The config file contains profile data, while the credentials file contains the necessary keys for accessing the S3 bucket.
5. Create an additional SQL Server step for all backup jobs to run the PowerShell file.

Once you have completed these steps, you can verify that the job is running successfully and see the same database backup files on your attached EBS volume in the S3 bucket. With this offsite backup process in place, you can rest assured that your data is secure even if your AWS EC2 instance becomes unavailable or your servers are compromised.

Conclusion

Implementing an offsite backup process is essential for ensuring the availability and recoverability of your SQL Server databases. By syncing your database backups to an AWS S3 bucket, you add an extra layer of protection against potential disasters. With the help of the AWS Command Line Interface, this process becomes straightforward and reliable. Take the necessary steps to safeguard your data and enjoy the peace of mind that comes with knowing your backups are secure.

Disclaimer

This blog post is based on the following resources:

• SQL Server Backup to URL – Microsoft Docs
• AWS CLI S3 Sync – AWS Documentation
• AWS CLI S3 Commands – AWS Documentation