Protect your SQL Server on Amazon EC2

Many businesses that migrate their storage, compute and database resources from on-premises to AWS want to continue using their same SQL Server database. Running SQL on EC2 allows for this simpler way of migrating, and allows for continued use of long-term licenses. It’s important to note that while this is one use case, some cloud-native users also choose SQL on EC2 out of all the available tools, seeking full control rather than use a managed database service like RDS.

Users frequently question why backup data when AWS’s infrastructure is so resilient. While it is true that AWS is highly resilient, it is still the users’ responsibility to secure and protect their data from loss due to ransomware, insider threats, accidental deletions and unintentional overwrites. Further, doing this may be an aspect of compliance with certain regulations. For these reasons, companies should be doing database backups, as well as implementing security best practices like frequently checking user permissions, using multi-factor authentication, encryption, and regularly training employees on security.

Backing up your SQL on EC2 database can come with some challenges, but many of them can be avoided by using the right solution.

• Performance impacts: SQL on EC2 databases are often critical components of applications, and it’s important to avoid operations that would impact their performance. One operation to consider is backup, because differences in the method can make a big difference in the outcome. Clumio is Availability Group aware, so your backup can be taken from a secondary replica to avoid impacting the primary database. Always On Availability Groups help not only with this function, but are also helpful in disaster recovery.
• Agent installation and maintenance: Many backup solutions for SQL on EC2 require installation of external backup agents to function. This adds an extra step in the setup process, and can require ongoing maintenance when agents require updating.
• Incomplete transactions: MSSQL databases run two-sided transactions, and trouble could occur if a snapshot or backup were to be taken before both sides had been completed. For that reason, Clumio utilizes Microsoft’s VSS (Volume Shadow Copy Service), which ensures that the system is paused at a time when transactions are complete, allowing the database backup job to process correctly.
• Excessive RPO: Databases change frequently, and if all those changes are not captured between backups, it can result in excessively long RPOs. For example, if snapshots are taken daily, this results in a 24 hour RPO (recovery point objective) which could result in excess data loss that doesn’t meet your SLAs. It’s important to not just schedule backups or snapshots, but ensure transaction logs are also backed up frequently. Clumio combines incremental forever backups with transaction log backups to provide users with an RPO as low as 15 minutes, for application-consistent point-in-time recovery.

There are a few typical ways data protection is handled for SQL on EC2:

• Snapshots: You can take snapshots within the AWS console. These snapshots are at the instance level, rather than at the level of databases or volumes, and do not include transaction logs. This means your RPO is limited to the snapshot frequency options offered by AWS. Snapshots can be stored in Amazon S3 to save cost, but that requires additional steps.
• Legacy backup solutions: You can run legacy backup solutions designed for on-premises infrastructure, but this limits the versions of MSSQL Server supported, carries a need to install agents, and generally aren’t designed for the cloud, leaving a lot of functionality on the table.
• Clumio is cloud-native and customer-centric, providing performant, application-consistent, secure backups for SQL on EC2. You can restore your backup database to any connected account and region. Clumio backups are held in air gapped, always encrypted, and immutable Amazon S3 buckets, keeping them cost-efficient.

• Server-level snapshots take full copies of the whole instance on which your database is running. This results in the snapshots being “heavy”. Because they contain a large amount of data, they can require a lot of compute bandwidth and time to complete. When restoring, point-in-time restore is not an option as transaction logs are not available.
• Database-level backups start with one full copy, and after that, scheduled incremental backups of the database are added. Additionally, transaction logs can be backed up, which enables point-in-time recovery. This overall process is much lighter weight and increases performance and retrievability.