Your choices for SQL Server backup in AWS (SQL Server backup part 2 of 3)

Welcome to part two of a three-part blog series on backing up SQL Server databases running on AWS. This series will cover SQL Server backup concepts, implementation options and methods in depth. Part two focuses on the different options for backing up SQL Server running in AWS. If you find this content helpful, you can download the complete ebook.

With the foundational knowledge gained in the previous blog in this series, we will now explore backup strategies for SQL Server on AWS, and trade-offs among the different deployment options — SQL Server on Amazon RDS, on Amazon EC2 instances, and on VMC on AWS.

Whether you are a DBA, IT professional, or a decision-maker responsible for data protection in your organization, this chapter aims to provide you with a comprehensive understanding of your choices for SQL Server backups in AWS. We’ll also discuss how third-party backup solutions like Clumio can help streamline this process and provide additional layers of data protection.

Native SQL Server backups

Whether you’re running your SQL Server on Amazon EC2, Amazon RDS or VMC on AWS, you can back up SQL Server databases using native differential or full backups. This method provides portability of SQL Server databases across Amazon RDS, Amazon EC2, and VMware instances, and backups can be stored in EBS, S3, or FSX. However, it’s important to understand the nuances and considerations.

Since SQL Server backups supported only full or differential backups (and not incremental backups), the size of these backups can get pretty large, especially for fast-changing databases. Moreover, storing native SQL backups in EBS or FSX can get expensive and cause storage and throughput bottlenecks. To get around this, customers sometimes manually export their SQL Server backups to Amazon S3. However, since Amazon S3 has a file size limit of 5TB, this method is not recommended for larger databases. In addition, customers cannot back up to or restore from more than 10 backup files at the same time, and cannot run more than two concurrent backup / restore tasks.

From a usability standpoint, native SQL Server backups come with some limitations. You cannot take a native backup during a snapshot or automated backup. And for differential backups to work, you cannot take a snapshot between the last full backup and your differential backup task. To simplify backups for SQL Server, customers therefore choose either built in backups in AWS, AWS Backup, or Clumio.

Built-in backups in AWS

AWS provides some built-in options for backing up SQL Server depending on the deployment model. These methods provide foundational data protection capabilities and are a good starting point for your backup strategy.

For SQL Server on Amazon RDS: AWS offers automated backups up to the size of the provisioned disk capacity, which should satisfy most of your operational backup requirements. Amazon RDS does this by taking a full daily snapshot in the user-specified time window, along with transaction logs that enable point in time recovery. You can specify a backup window, and AWS will take care of the rest. It provides a backup retention period of up to 35 days, enabling point-in-time recovery.

However, these built-in backups do have certain limitations. For example, they are tied to the life of the instance. If the Amazon RDS instance is deleted, all associated automated backups are also deleted. Therefore, they may not serve as a comprehensive ransomware or accidental deletion recovery solution. Additionally, the retention period of 35 days may not be enough for businesses with longer data retention requirements.

For SQL Server on Amazon EC2 instances: AWS provides the capability to create Amazon Machine Images (AMIs) and snapshots of Amazon EBS volumes. The combination of these is colloquially known as ‘Amazon EC2 snapshots’, and can be operated either manually through or with AWS Backup. They are volume level copies and do not allow database-level backup and restore, and typically do not provide point-in-time recovery.

For SQL Server on VMC on AWS: data protection and backup options are typically as native VMware snapshots, or by third-party solutions that can be found on the VMware Marketplace. VMware snapshots can impact VM performance, and third party applications can be costly and require additional storage.

Regardless of your SQL Server deployment model in AWS, it’s important to be aware of these built-in backup options, their benefits, and limitations. Fortunately, you can augment these with other backup methods to create a comprehensive backup strategy.

Snapshots

Manual snapshots are often used in combination with automated backups to provide additional coverage for specific business needs, such as backup before a significant change, for archival purposes, or to meet specific compliance requirements.

For SQL Server on Amazon RDS: AWS offers the capability to manually create database snapshots at any time. These user-initiated full snapshots enable you to periodically back up your SQL Server instance in a known state, and then restore to that state at any time. Unlike automated backups, manual DB snapshots are kept until explicitly deleted, providing a longer-term, durable backup solution beyond the maximum 35-day retention period of automated backups. However, these snapshots are not point-in-time recoverable, and incur additional storage fees.

For SQL Server on Amazon EC2: you can manually create Amazon EBS snapshots, as stated in the previous section. These snapshots are incremental, which means only the changed blocks since your last snapshot are stored. While these can be space-efficient for static or slow-changing databases, it is not recommended to use incremental snapshots for databases with high change rates, since it can raise costs unexpectedly.

For VMC on AWS: you can leverage the features of your VMware environment to perform manual backups. This could involve creating snapshots of your VMs, or manually exporting your databases.

Keep in mind that manual backup methods such as those laid out above require diligent monitoring and management. Also, in the event of a disaster, piecing together data from different manual backups can be time-consuming and complex.

AWS Backup

Some AWS customers use AWS Backup to manage backups and snapshots of their SQL Server databases.

For SQL Server on Amazon RDS: AWS Backup can take full snapshots of Amazon RDS SQL Server DB instances, and keep them for a user-specified retention period. AWS Backup allows you to define policies, including when to take backups, how long to retain them, and when to transition the backups to a cheaper storage class or delete them. The key limitation is that AWS Backup does not offer point in time recovery for Amazon RDS.

For SQL Server on Amazon EC2: AWS Backup creates a VSS-enabled snapshot of the Amazon EBS volume attached to your Amazon EC2 instance running the database, ensuring an application-consistent backup. AWS Backup also allows you to manage backups across multiple accounts and regions. However, as in the case with Amazon RDS, AWS Backup does not offer point in time recoveries for SQL Server on Amazon EC2.

For VMC on AWS: AWS Backup also supports backing up virtual machines in VMC on AWS that may be running SQL Server by creating an AWS gateway to connect to vSphere. There is no point in time recovery capability out of the box.

Clumio

Clumio is a serverless backup solution for data in AWS, including SQL Server. Clumio is architected with features that are uniquely advantageous for backing up SQL Server databases running on AWS, regardless of deployment model.

1. Air gap: Air gapping is a means of securing critical backed up data outside of the primary access control domain, ensuring that data is unreachable by malicious actors even if the enterprise security domain is breached. Clumio is air gapped, ensuring that your SQL backups remain secure and recoverable even if your primary environment is compromised.
2. Continuous backups and point in time recovery: By continuously backing up transaction logs, along with a combination of full and incremental VSS-enabled snapshots, Clumio can ensure granular point in time recovery for both Amazon RDS and self-managed distributions of SQL Server.
3. Compliance efficiency: In addition to operational backups, Clumio also offers an ultra low-cost long-term retention mode for SQL Server backups by automatically storing this data in Amazon S3. This is particularly useful for customers that need to adhere to compliance requirements in regulated industries.
4. Availability Group awareness: Clumio backups are Availability Group aware, ensuring that whenever specified by the user, backups are taken from a secondary replica, rather than the primary database serving the application, to minimize performance impact on the production environment. The user may specify the primary replica as their preferred choice for taking backups.
5. No maintenance: Clumio is a simple SaaS solution with no custom engineering requirements, and 24X7 human support built in. This helps customers free up their engineering staff to focus on revenue drivers.
6. No performance impact: With no proprietary agents to install and the ability to backup from a secondary replica, Clumio ensures backups won’t impact your database’s performance.
7. Granular record retrieval: Uniquely, Clumio offers Amazon RDS users the ability to simply query backups to retrieve granular records or datasets using SQL queries, rather than having to restore full databases and then look for the right data. This simplifies data retrieval, minimizes downtime, and eases compliance audits.
8. Industry leading RPO / RTO: Clumio’s serverless architecture enables low RTO and RPO by efficiently scaling resources and parallelizing operations. The platform uses Lambdas orchestrated into large workflows, ensuring predictable performance even at scale.

The next blog in this series will explore Clumio’s solution for backing up SQL Server databases in AWS.

For a more in-depth experience, download the complete ebook including diagrams & content not included here.