Optimizing backup and restore for SQL Server on Amazon EC2: Clumio vs. native SQL backups

Microsoft SQL Server is a long-standing gold standard in relational databases, even for cloud applications. But organizations that have migrated their SQL Server to the cloud are probably realizing that Native SQL Server Backup is better suited to its traditional on-premises deployment than the modern cloud-hosted instances. This blog explores how native SQL Server backups work, where they fall short, and how Clumio compares.

Native SQL Server backups are the built-in backup mechanisms that allow users to create backups of databases and transaction logs directly from within SQL Server. Since these built-in backup methods apply across versions and hosts, they are very popular.

SQL Server’s native backup scheme involves multiple different types of backups. To understand how the backups work, we must first understand SQL Server’s recovery models. These are database properties that control how transactions are logged, whether the transaction log requires and allows backups, and the available restore options.

SQL Server has three Recovery models: Simple, Full, and Bulk Logged. Users must select one for each SQL Server database, but it can be changed anytime.

• The Simple recovery model is the most basic option. It’s designed to minimize the database’s space requirements by continually overwriting the transaction log after transactions have been completed, which reduces backup and recovery functionality. Because the transaction logs are not maintained, they cannot be backed up, nor are they available to be rolled back to recover the database to a point-in-time. You can still take full and differential backups. (More details on this in the next section)
• The Full recovery model is the most complete option. It requires transaction log backups, and enables point-in-time recovery through a combination of full backups, optional differential backups, and the transaction log backups. Transaction logs are flushed only after they are backed up or the transaction log is truncated.
• The Bulk logged recovery model minimizes logging of most bulk operations (like BULK INSERT, CREATE INDEX, etc…) as a way to minimize the space used for transaction log backup, while still allowing for point-in-time recovery in certain cases. The catch is that for point-in-time recovery to work, the last transaction log backup cannot include any bulk operations. To maximize recoverability when using the bulk logged recovery model, the best practice is to perform bulk operations at a separate time from normal operations.

Equipped with an understanding of recovery models, we can now explore how they interact with the different types of SQL Server-native backups, and what this means for database backup and recovery.

• Full Backups take a backup of the entire database, and represent the database’s state at the time the backup finished (rather than when it started.) If using only full backups under the simple recovery model, these are the only recovery points. Under the full or bulk logged models, these would be combined with transaction log backups to enable point-in-time recovery. The downside of full backups is that because the whole database is being backed up, they take significant time and compute resources to complete.
• Differential Backups can be taken from a database under any of the three recovery models. They must be based on an initial full backup, and they capture only the changes since the last full backup. This allows each differential backup to be much lighter weight and faster than a full backup. Users should keep in mind that as time elapses between a base backup (a term used to refer to the latest full backup) and differential backup, the size of the differential backup increases. Typically, a new base backup should be taken weekly at an off-peak time. To restore a differential backup, its base backup must be restored first, increasing overall restore time.
• Transaction log backups are the key to enabling point-in-time recovery, or the ability to recover your DB down to the minute needed by recovering the full or differential backup, then rolling the transaction logs to the desired recovery point. They are only taken in Full or Bulk Logged recovery modes, and require at least one full backup to be taken initially. Transaction log backup frequency can be set to whatever your RPO requires, and more frequent backups have the added advantage of reducing your transaction log file size.

Clumio is a simple, automated solution for backing up your SQL Server on Amazon EC2. Air-gapped and immutable by design, Clumio helps organizations recover instantly from ransomware and operational disruptions, automate data retention and compliance, and optimize storage costs.

Clumio for SQL Server on EC2 reduces performance impact because it takes incremental backups. These differ from differential backups in that after one initial full backup, they continue building upon one another forever, making them faster and lighter weight. This additionally means that when restoring the database, there is no need to separately restore full and differential backups and roll back transaction logs–everything is restored together as one full backup. Clumio can also be set to take backups from the secondary replica in your Always On Availability Group, completely removing any performance impact on the primary database.

With Clumio, you can set when and how often incremental, database-level backups are taken, and how long they’re retained. You can set the frequency with which your transaction logs are backed up, down to every 15 minutes, and you can choose whether to have your backups taken from a replica in your Always On Availability Group. Once these choices have been made, your backup plan is automated, requiring no ongoing maintenance.

Here we explore Native SQL Server backups’ functional challenges and Clumio’s advantages:

• Performance Impact: Performing backups directly through SQL Server can potentially impact the performance of the database server. Backing up large databases can consume significant server resources, which may affect the overall performance of the database during the backup process.
  • Clumio’s agentless, availability group-aware backups avoid performance impact.
• Storage Requirements: SQL Server backups can consume a significant amount of storage space, especially for large or high change rate databases.
  • Clumio is optimized to reduce backup footprint, has no impact on your in-account storage, and offers simple, consumption based pricing.
• Incomplete Incident Protection: When running SQL Server on Amazon EC2, the native backups are stored in your AWS account. This means that if your account were to be compromised or experience an outage, the backup data might not be available.
  • Clumio backups are automatically air-gapped and stored in immutable format, remaining available even if your AWS account is compromised.

Many native SQL Server backup challenges stem from the fact that they have to be scripted and managed by the user. Here we compare these to Clumio’s benefits:

• Complexity: SQL Server db backups can be complex, with numerous options, settings and parameters. Configuring backup settings correctly can be challenging.
  • Clumio’s simple interface allows you to connect and protect in a few clicks.
• Potential for Human Error: As the backup process is manual, there is a higher chance of human error, for instance selecting incorrect backup options, overwriting existing backups unintentionally, improperly scheduling backups, or typos in code.
  • Clumio’s automation makes it easy to set a dependable backup plan.
• Lack of Centralized Management: If you have multiple SQL Server instances or need to manage backups across different servers, the built-in tools do not provide a centralized management solution. It can be time-consuming to manage and monitor backups individually on each server.
  • Clumio allows you to see and manage backup of all connected SQL Servers in one pane of glass, along with other AWS services.

While you could backup your SQL Server on EC2 using native SQL Server backups, there’s a better strategy for backing up SQL Server databases on Amazon EC2. Clumio is a simpler, more secure, and more performant backup and data recovery option. Learn more about Clumio for SQL Server on EC2 on the product page, or download The Essential Guide to backing up SQL Server DBs running in AWS.