In this blog, we will see different approaches an organization take to protect their SQL Server. We will look into the pros and cons of each approach. There are three different approaches.
- Native SQL Backup
- Cloud-native snapshot-based backup
- Backup software
Native SQL Backup
To protect SQL servers mostly database administrators write scripts periodically backup the critical databases. As the data grows to have a regular full backup is not feasible. The typical approach followed is to take a weekly full backup and daily differential backup along with logs backups based on the recovery model.
In this approach, backup is stored in the local drive and then it is uploaded to S3 and this increases the backup cost. Restore is similar to backup, first restore the full backup, then the differential backup and the transactional backups.
The challenge with the approach is it is very time consuming. As the size of the database increases both the backup and restore times are high. To avoid stress to the server admin needs to factor backup load while provisioning the instance and also additional local storage to hold the backup. This increases the cost of backup.
Cloud-native snapshot-based backup
Administrators take periodic AMI based backup or the EBS snapshot-based backup. They write scripts or use native AWS service for this. These are fast and incremental snapshots and there is no load to the SQL server.
The major issue with this approach is these backups are crash consistent. Only the complete machine or the EBS volume can be restored. DBA’s cannot restore the database to the transaction or time.
Backup software
Software like Nimesa integrates with native frameworks like VSS and EBS snapshots. This will help DBAs manage their backup strategy. The advantage of this approach is
- More aggressive backup as snapshots is incremental. All the backups are always full.
- RTO and RPO in minutes
- Database level restores and transaction-level recovery
- Leverage backups for copy data management use cases
About Nimesa
Nimesa is an enterprise-class Application Aware data protection, cost management & copy data management solution for applications running on AWS. It uses native AWS capabilities like EBS snapshots capabilities to automatically protect the environment. Nimesa provides simple policy-based lifecycle management of snapshots and clones of EC2 instances.