SQL Server 2017 Instant Object Recovery with NAKIVO Backup and Replication

Supporting business-critical applications is perhaps the most important tasks of enterprise IT. Perhaps the most used enterprise database engine that is commonly found in environments today is Microsoft SQL Server. Many may think about protecting “files and folders” but may not as readily think about business-critical databases that provides the backend environment for mission-critical applications. Providing efficient application aware backups of SQL Server and having the ability to restore data quickly and efficiently to SQL Server is a necessary business requirement when thinking about disaster recovery. The newest version of Microsoft SQL Server, 2017, provides the most robust database solution from Microsoft and organizations may be looking at upgrading or have already upgraded to SQL Server 2017. NAKIVO Backup & Replication v7.4 has introduced support for SQL Server 2017. The instant object recovery allows granular restores of the SQL Server 2017 databases. This can drastically reduce Restore Time Objectives when recovering databases in a true DR situation. Let’s take a look at SQL Server 2017 Instant Object Recovery with NAKIVO Backup and Replication.

NAKIVO Backup & Replication v7.4.1

Just a quick overview of NAKIVO Backup & Replication for those who may not be familiar.  NAKIVO has a very slick and efficient solution and perhaps THE quickest product from download to actually creating a backup that is on the market.  There are many things I like about NAKIVO, but below are what I feel are some of its strongest features:

• Extremely FAST solution stand up time (minutes from download to creating backups)
• Very quick HTML5 driven web interface
• Intuitive Interface and menus
• Very strong DR (Automated Failover and Replication plans and automatic network reconfiguration)
• Strong business application support (App-aware backups and replication for Microsoft SQL Server and Exchange along with log truncation built in).
• Multi-hypervisor support all in the same interface

NAKIVO Backup & Replication is certainly a major player in the data protection space with strong features and a quick, powerful interface.

SQL Server 2017 Instant Object Recovery with NAKIVO Backup and Replication

Recovering from a data disaster is always time sensitive.  You want to have your data back as current and as quickly as you possibly can.  Like files, folders, and other data, SQL databases can be accidentally deleted or modified and also corrupted by hardware malfunctions, viruses, or even malware, including ransomware.

With legacy virtual machine backup solutions, you have to restore the entire SQL Server virtual machine first, and then restore individual objects.  As you would imagine, this process is extremely inefficient and time consuming.  It can also lead to corrupted data if backups are not performed in an application aware state.

What is Application Aware backups?  Applications such as Microsoft SQL Server, Exchange Server, and others are transactionally sensitive.  All databases transactions are committed to the database consistently.  However, a problem for legacy backups is that modern database applications often have transactions living in memory and in pending disk I/O operations.  If legacy backup solutions only backup current files and data on the disk only and don’t properly flush memory and pending disk write operations, data as it is backed up can be corrupt or the database left in an inconsistent state.

Modern backup solutions like NAKIVO Backup & Replication allow creating “app-ware” backups that interact with special Microsoft Volume Shadow Copy Service components called VSS writers to ensure that all data is flushed from memory properly and all pending disk I/O operations are completed before data on disk is backed up using changed block tracking.

By utilizing the latest release of NAKIVO Backup & Replication v7.4 application aware backups, you are now able to perform instant object level recovery on Microsoft SQL Server 2017 servers.  To do that, make sure to enable App-aware mode on the backup job as below.

As a side note, on the job creation screen or job options, you can also select to Truncate SQL Server logs which prunes logs with successful backup operations.

After you have successfully performed a backup of the virtual machine housing Microsoft SQL Server 2017, you can instantly recover objects to the SQL Server directly.  To restore SQL Server 2017 objects, select Recover >> Microsoft SQL Server Objects to begin the application restore wizard.

This launches the Object Recovery Wizard for Microsoft SQL Server.

NAKIVO Backup & Replication Object Recovery Wizard for Microsoft SQL Server

The SQL Server 2017 object recovery wizard is a simple 5 step process (4 steps really) that is simple and intuitive.  On the first step, you select the backup you want to mount for recovery and also the restore point.  Note the Automatically locate application databases will automatically query the backup for supported application databases to restore objects.

The Recovery Server is where you can point the recovery directly to the server itself.  By default it will populate with the name of server.  You will need to populate the Windows credentials here to connect to the server for application restore.  Click the Connect button once you have the connection information populated.

After the Connect process completes, you will see the “blue checkmark” next to the Connect button.  Also, if all goes well, you should see the SQL instance populated.  If you have multiple instances, you will be able to select the instance to restore to.

On the Objects screen, choose the DB you want to restore.

On the Options screen, choose the Recovery Scope which tells the recovery process if you want to restore both the schema and the data or just the data.  The schema is the logical structure of data in the database.  Also, there are two additional options:

• Recovery type – Here you can choose if you want to restore to the original location, custom location, or export the data.
• Overwrite behavior – You can choose to rename recovered items if they already exist, skip recovered item data if it exists, or overwrite the original item if such item exists.

The final step is simply a Finish operation.  By this step, the object recovery starts and you are directed to check progress in the Activities area.

In the activities page, you can see the progress of the recovery operation.  As you can see below the operation completed.

With the instant object recovery operation, you can quickly and easily restore consistent, application aware backups back to the SQL Server 2017 server.  This ensures that data is consistent and isn’t corrupted.  This drastically lowers the RTO for business SLAs as it allows recovery operations to be a simple one-step process without additional log replaying or application specific restore operations in addition to the data restore.

Takeaways

NAKIVO Backup & Replication v7.4.1 is a powerful data protection solution that allows organizations the ability to not simply backup data but also applications which is at the heart of business-critical processes and day-to-day operations.  Consistent database backup operations require backups to interact correctly with the application specific VSS writers so data is properly flushed from memory and pending disk I/O operations are completed successfully before data on disk is backed up using changed block tracking.  SQL Server 2017 Instant Object Recovery with NAKIVO Backup and Replication is made possible by NAKIVO’s app-aware backups.  Check out NAKIVO Backup & Replication and download a free trial copy of the latest release allowing SQL Server 2017 instant object recovery and log truncation.