I recently learned two interesting things about SQL Server backups from the esteemed Victoria Yudin.
FIRST, I'm embarrassed to say that I didn't know that SQL Server 2008 added a native Backup Compression option. Admittedly, I'm not a full time SQL DBA and I don't spend much (any?) time on SQL maintenance or backups for the TWO databases on my development servers, but I probably should have picked up on the feature some time in the last SEVEN years, you would think.
Anyway, now that Victoria schooled me, I found that there are two places where you can set backup compression. The first is at the SQL Server instance level, under Properties -> Database Settings. Setting this option causes all database backups to compress by default.
The second location where you can select backup compression is under the database backup window on the Options page.
Here you can use the default server setting, or choose to manually enable to disable the backup compression.
I'm now going to enable the option for all of my SQL Servers.
Even if you didn't know about this option, if you have done SQL Server backups, you should already know that the SQL bak files are HIGHLY compressible. In my humble opinion, you should always compress SQL backup files because of the massive reduction in disk space usage that it provides.
I previously have had customers use WinZip or WinRAR or 7-Zip to compress backups if they needed to send me a copy, and the difference in file size is astounding. (If you are dealing with really large files, 7-Zip offers the best compression, in my experience) Another thing I've done is set the SQL backup folder to use Windows folder compression. That works well for backups that only need to sit on disk. But having SQL Server automatically compress the backup files is the most convenient option, as it also makes file copying or archiving much faster.
SECOND, Victoria and I learned a very interesting lesson about 32-bit vs. 64-bit SQL Server. She has a customer with a GP 10 install that is running SQL Server on a 64-bit version of Windows. The server has 24 GB of RAM and solid state drives, so you would think it should be pretty speedy. But Victoria noticed that the SQL backups for a 20 GB database took about an hour. Again, since I don't do much DBA work and all of my test databases are small, I didn't really have any idea of how long it should take to backup a 20 GB database, but Victoria assured me that there was something wrong.
After a few calls with the customer, we learned that the GP 10 SQL Server was running a 32-bit version of SQL 2005. Obviously the 32-bit version can't begin to utilize the 24 GB of RAM, but we weren't sure how backups were affected by 32-bit vs. 64-bit SQL Server--it has been so long since I've used 32-bit SQL that I no longer have any virtual machines with it installed. Fortunately, the client had a second test server with identical specs as production, so he installed the 64-bit version of SQL 2005 on that server, restored a copy of the 20 GB production database and then ran a backup.
It took 2 minutes. TWO MINUTES. From one hour to two minutes for a 20 GB backup.
Stunning. I expected some improvement, but wow. So that told us that the server hardware was just fine--it was the limited memory of 32-bit SQL Server that was causing the long backups.
And I assume that the 64-bit version will also produce some performance improvement for GP users. They may not have realized that processes were slow, but hopefully they benefit from an upgrade to 64-bit.
So there ya go--there's always something new to learn.
Steve Endow is a Microsoft MVP for Dynamics GP and a Dynamics
GP Certified IT Professional in Los Angeles. He is the owner of Precipio
Services, which provides Dynamics GP integrations, customizations, and
automation solutions.
No comments:
Post a Comment