Microsoft SQL Server is one of the most popular enterprise database, a lot of enterprises run MS SQL Server for their mission-critical applications
SQL Server database management engine is fast and stable, available on both Linux and Windows platforms and can work on cloud-based servers and local servers simultaneously. It’s owned by Microsoft and is ideal for companies that work with a range of Microsoft products as it works well with many of them. It’s market share increased fast because Microsoft pushed SQL Server with their Windows Servers and also included AI over Azure.
SQL Server also has some great features including visualizations on mobiles and the ability to change and track performance levels which can potentially save time and money
As it can happen with any database, Microsoft SQL Server is susceptible to corruption, accidental deletion or even security threats such as ransomware attacks. Therefore enterprises use a traditional backup solution to help them with their data management and protection.
The traditional way of backing up the Microsoft SQL Server is not the ideal solution. The backing up of the production database to the disk or any backup server or to the NFS mount point uses the resources in the production server.
And if the admins write the scripts to take a full backup and differential backup, the backups will be in native format ( bak file ) but if an admin uses the backup tools most of the backup tools stores the data in the proprietary format, not in the native block format
Challenges in traditional backup
- Performance impact of the periodic full backup, Full backup increase the IOs and are network intensive on the production servers
- Large RPO because of the periodic full backup, you have to copy the backup file to the backup media through the network which takes time, users don’t get the desired SLAs for RPO
- Larget RTO, of course, the data backed up, has to move back to the production server from the backup media and the tool has to perform the full recovery.
- Large cloning time to test/dev/analytics use cases: Instant cloning is not possible because the data is in a proprietary format
An Alternate Approach: Snapshot based backup
The alternate way of overcoming the challenges associated with the traditional backup approach is to use the solution that backups the SQL server databases without taking much of the resources
Nimesa uses storage snapshot technologies to take the backup of the volumes, before creating the snapshot Nimesa makes sure the data is flushed to the disk in order to ensure that no new writes happen to disk and take the snapshot of the underlying disk through the Microsoft VSS Provider
The snapshot keeps tracks of the changed blocks, this solution takes the backup within seconds
In my lab, using Redgate SQL Data generator tool, generated 750GB of data on the adventureworks database in the SQL Server and using Nimesa I could able to take the backup of the whole 750GB of a database within 3 mins.
Benefits with Nimesa
Reduced performance impact: Storage based snapshots reduce the IO and network impact on the production server
Low RPO: Admins can achieve even 15 minutes RPO SLA, and if they configure the Log backup with Nimesa, they can further reduce the RPO to even 5 minutes ( depends on the data change rate and the LOG backup time )
Low RTO: Since the data are stored in the snapshot and most storage supports the instant cloning of volumes, with Nimesa, admins can quickly restore the database
Instant clones for test/dev: If the admins need the copy of the production database for their test and dev or analytics use cases, Nimesa can instantly create a clone and attach to any available SQL server
Granular recovery: Admins can recover the SQL server, individual database or even a table
Disclaimer: The scripts/code in this post are given as-is without any warranties. Please consider them as guidelines. Please don’t use them in your production environment until thoroughly testing them and making sure the processes work correctly.
Also published on Medium.
