SQL server is one of the key components in the data center. Admins take different backup strategy based on the criticality of the database and the rate of change data. Below are the different types of backups of SQL supports.
Full Backup:
A full backup contains all the data including all the transaction at the time of backup. A full backup helps to restore the database to the time of backup. This kind of backup is time-consuming as the complete database is getting backed up.
Differential Backup:
To reduce the time taken for backup admin can take periodic differential backup. These are changes in data since the last backup. These backups cannot be independently restored first the full backup needs to be restored and subsequently each differential backups needs to be restored.
Transaction log Backup:
Transaction log backups are taken when databases are in the Full or Bulk-Logged recovery model. Frequent transaction log backup helps in restoring the database to point in time or to a transaction. Truncation of logs while taking log backup removes the old transaction from the active log file.
Protecting SQL databases with Nimesa
Nimesa by integrating with EBS snapshots and VSS framework provides instant protection of database irrespective of the size of the database. To protect EC2 instances with SQL databases following things needs to be done
- Application Policy needs to be created by opting for VSS aware processing and opting for SQL aware processing. In addition to this policy can also specify if log backup needs to be taken and at what frequency
- Download and Install Nimesa Windows agent
- Assign the Windows credentials to the Backup group. A user should be part of the local administrator group in the windows host and also should have sysadmin role in SQL database.
As part of backup Nimesa coordinated with the windows agent to take application aware VSS snapshot. Nimesa uses VSS system provider to take differential backups it coordinates with all the VSS writers in the host along with the volume to take application-consistent backup. Once the backup is complete it discovers all the SQL databases and SQL instances those are part of the EC2 instances. Since backups are snapshot based more frequent full backups can be taken and reduce the RTO and RPO to minutes. Post backup user can browse the backup to see the databases part of the backup, its recovery model and tables part of the database.
Operations Supported on the SQL Database backups:
Nimesa supports following operations on the SQL databases
- Cloning the database to same EC2 instance – This option can be used as side by side restore of the database
- Cloning the database to different EC2 instance – This option is used in use cases like Test and Dev, Analytics where periodic production data needs to be brought
- Restore of Database – Database can be restored to a point in time with the help of full and transaction log backup
- Table Restores – There are scenarios where a particular table needs to be restored and nimesa helps in restoring a particular set of tables
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