Many DBA’s are trying to get rid of the old SQL Server 2005, which is a good thing.
I’ve been working on some consolidation and migration projects and I’ve found a new and faster way to migrate SQL 2005 databases.
Most easy way to migrate a database is to backup the database, copy it to the new server and restore it. But, how can you make this process faster? Check out this blog post of Jes Schultz Borland (blog | Twitter). Compression of the backup file can make the migration go faster. The backup will be faster because SQL Server has to write less to disk. The copy will be faster because the backup files will be much smaller and the restore will be faster because SQL Server has to read less from the disk. However, there is one little problem… SQL Server 2005 doesn’t support backup compression. This is available from SQL Server 2008 Enterprise Edition or from SQL Server 2008 R2 Standard onwards.
But now, there is a way to compress your SQL 2005 backup files, without using any third party tool. Since a few months, Microsoft introduced a tool called “SQL Server Backup to Azure Tool”. They created it to have a consistent backup solution. In SQL Server 2014 the backup to Azure, encryption and compression are embedded in the engine, but these features are not available in older versions of SQL Server (except for compression of course). The Backup to Azure Tool allows you to use the same functionalities for older versions of SQL Server, starting from SQL 2005 onwards. The good thing about the tool is that you don’t need to use Azure functionality to use encryption or compression. This means that we can compress our on premise SQL 2005 databases and make our migration faster!
The tool can be downloaded from http://www.microsoft.com/en-us/download/details.aspx?id=40740 and is pretty straight forward to install. This tool is supported on SQL Server 2005 or later, and Operating System versions: Windows Server 2008 or later for Servers, and Windows 7 or later for Client Operating Systems.
Once you start up the tool you have to configure some rules.
Click the Add button and start by specifying the path where you are going to save you backup files and the file name pattern:
I don’t want to use the Azure functionality because I’m using the tool for migration purposes to another on premise server.
Select Use local Storage
Select the option “Enable compression”.
Now you have setup the rule that will be necessary to compress local backups.
I did a few tests to see how well the Backup To Azure Tool will compress my backup. I’ve used a 130 GB database and I did the tests on my laptop (16GB RAM and 8 CPU’s).
I’m using SQL Server 2012 for my tests because I wanted to compare the difference between normal compression and Backup To Azure Tool compression
|Test||Duration||MB/sec||Size Backup File||CPU usage|
|Full backup without compression and without Backup to Azure Tool compression||2696 sec.||34 MB/sec||89.3 GB||4 %|
|Full backup with compression and without Backup to Azure Tool compression||1502 sec||61 MB/sec||16.9 GB||10%|
|Full backup without compression and with Backup To Azure tool compression||2344 sec||39 MB/sec||13.5 GB||20%|
|Full backup with compression and with Backup To Azure tool compression||1403 sec||65 MB/sec||16.3 GB||15%|
I noticed that the size of the backup file is smaller when I’m using the Backup to Azure Tool compared to the normal backup compression.
On the other hand, the duration is taking almost the same time as a backup without compression. Also, the CPU is 10% higher than a normal backup compression.
But with these results, it will still makes sense to use the tool for faster migrations. You still have to copy less data and the restore will also be much faster because there is less data that has to be read from disk.