Accelerating the restore process – ApexSQL Restore – Product Review

Infographic: “Your Backups – Restored from the Dead”

To be a database administrator is to accept a life of stress and paranoia. We are charged with the responsibly of protecting one of a business’s most valuable resources, its data. We wield the power of gods in the form of the sysadmin server role and slash seconds from long running queries with a click of a key. With all of this power and responsibility there is also an expectation of perfection. One UPDATE statement without a WHERE clause and you might have to exercise one of your super powers; a restore from backup or a résumé submission.

It is because of these factors that we are overworked from quadruple checking every script and racking our brains thinking of any way that our precious data could be endangered. This is where tooling comes into play. You need tools to be efficient and make life as a DBA manageable.

Product Overview

ApexSQL Restore combines efficiency with business critical disaster recovery by accelerating the SQL Server database restore process of a static full or point-in-time backup set. It works by running a restore agent locally which will read any native SQL Server backup, whether compressed or uncompressed, and attach it to your SQL Server instance. You run through an extremely simple and intuitive user interface which allows you to easily select backups from your backup set and then define a location for your data files (mdf, ldf, ndf). All of this is completed in seconds while a standard backup restore could take several minutes to hours depending upon the size. In addition, the data files created are a fraction of the size of what a full restore would have been; reportedly less than 5% of the full size in most cases.

Once attached these backups act like any other live SQL Server database. All T-SQL commands are supported to include modifying data. Once in this state all of your changes are written to the temporary data files which were created and do not modify your backup files at all. Once you are done reading from the tables or performing test changes you can simply take the database offline through the ApexSQL Restore wizard and once brought back online the state of the database is reset to the state of the backup.

Pros

ApexSQL Restore has several positive uses, the most important in my eyes is the ability to recover data in a side-by-side method extremely rapidly. I have been in that situation where a bad T-SQL statement was executed and caused a loss of data integrity. I sat there while this 2 TB database was restoring a full backup, a differential backup, and hundreds of log backups for well over an hour. The entire time I had the pleasure of our CIO standing over my shoulder watching the percent complete status updates. ApexSQL Restore would have turned that hour into minutes or seconds and we would have been immediately extracting lost data and not writing a customer communication about the incident.

A close second use is the ability to implement lightweight reporting. If you store your compressed backups on a secondary server then you can easily attach these to a reporting instance effectively saving a large amount of disk space. You also are capable of implementing this system without complicated replication technologies. Further, there are many systems in existence which will perform data manipulation each time certain record sets are viewed. With the ability to support data manipulation without persisting it to disk you will be able to produce a READ-ONLY database without the constraint of actually disallowing DML commands.

Another great way to use ApexSQL Restore is to support your development team’s local development. It is essentially a replacement for SQL Server’s snapshot feature which is only available for enterprise edition (which is far more expensive than this tool) and can bring development teams onto the same page by having everyone mount the same backup copy. This will make sure that your development team is using the same version and test data for development and can easily be updated by simply taking a new backup from the primary server and pushing it out to your developers to mount.

Cons

UPDATED: See this, more recent, post outlining improvements that ApexSQL made in response to this review.

The first con of note is the need that the ApexSQL Restore agent run locally to the SQL Server instance. From a technical stand point I completely understand this. It would be nearly unreasonable to ask ApexSQL to act as a middle man between the backup file and the SQL Server without being local. However, it would have been nice to only have to pay for one license to support multiple servers. This won’t be a big problem for an organization with a few servers or a small number of servers that they categorize as critical but I’ve always been blessed with managing over 100 servers at all of my companies. With high server counts the cost of this tool quickly becomes greater than the benefit.

The second con is related to the disk storage benefit. From my testing the disk savings is exactly as advertised. This, however, becomes less realistic once you factor in the need for your backup files to be stored locally as well. You see, when calculating the storage savings you need to look at the sum of all space used and how expensive the storage devices are that you are using. Let’s say that you have a database server and you are using shared storage in a NAS or a SAN. If you store your backups on those same shared disks and need to do a side-by-side restore of your database then you save a lot of space using ApexSQL Restore. The problem here is that SAN space is expensive and most DBAs don’t store their backups locally. They might backup locally but then usually copy them to a network share with cheaper disk space. As you can see below, ApexSQL Restore doesn’t support network paths. Without this support you need to consider that backup file as part of the data which wouldn’t have been local if you didn’t need to do a restore. So, if you don’t use SQL Server’s native compression your space savings is almost completely negated. If you do use compression then you keep some savings but nowhere near the advertised 95%.

For example, I manage several databases which are 1.2 – 5 TBs in size. The smallest of these compressed full backups is around 350 GBs. With the backup alone on my local storage I’ve already used up over 30% of the original database size. This reduces the space savings from 95% to 70% right there.

70% is still a good space savings assuming you are using compression for your backups but, unfortunately, that isn’t the worst part of the lack of network path support. Given the cost of high-speed storage and size of very large databases I’m not capable of keeping these backups local on a daily basis which means that in a disaster where I would want the efficiency and space savings of this tool, I’d be forced to copy the backup locally which could take as long or longer than doing a native restore.

Test scenarios

Summary

RATING
PRICE	$749
RECOMMENDATION	ApexSQL Restore is a great tool when used within its strengths. It is also a tool of a type that is not commonly found. Give this app your time and evaluate the software, I’m sure you won’t be disappointed.