Retrofit Vintage SQL 2005 Databases

Do you have a few old 2005 or 2008 SQL Server instances chugging along that you know will never ever be upgraded to SQL 2012, 2014 or 2016? For whatever reason – technical, political, or practical – there is no plan to version-up these servers and the databases will live the rest of their life as version 9 or 10, or 10.5? Let’s call them vintage DBs. It’s not a bad thing; vintage is hip these days and if the database still gets you where you’re going, well then, no big deal. But then again, the inherent limited functionality and special exceptions around administering vintage databases within an otherwise up-to-date environment can increase the complexity of management.

For instance, here are a couple of very handy backup features included in more recent versions of SQL that you don’t get with vintage DBs:

• Native SQL Backup Compression – built-in for SQL2008 and beyond, for the price of higher CPU usage SQL Server database backups can be compressed via the TSQL backup statement. Compression ratios depend on the type of data, but the decreased duration of backup operations and the storage space savings make WITH COMPRESSION a no-brainer in many cases.
• Native SQL Backup Encryption – Transparent Data Encryption (TDE) was introduced for SQL 2008 to encrypt data at rest and that includes the data files as well as the database backups. A few drawbacks though … TDE is Enterprise Edition only and when TDE is in use backups get a very poor compression ratio due to the encrypted data. Backup Encryption as a stand-alone feature was built into SQL 2014 and allows the DBA to encrypt (and compress effectively) the backup in Enterprise or Standard Edition.

There is plenty of great new functionality in the latest versions but I wanted to point these two backup-related features because there is in-fact a way to apply them to your vintage databases.

Take a look at the Microsoft SQL Server Backup to Windows Azure Tool. In summary, this tool runs as a service and will “watch” a local directory you specify. When a database backup file from any standard SQL database backup lands in the directory the service routes it to an Azure Storage account.

Backup to URL and Managed Backups are only available in recent versions of SQL Server so the primary idea behind this tool is to give pre SQL2012 SP1 CU2 instances the capability to backup to Azure Storage. That is pretty cool in and of itself. Additionally, this tool gives 2005 and 2008 instances the same backup compression and backup encryption capabilities as later versions of SQL Server. Not only can you send your SQL 2005 database backups to the cloud, you can encrypt them and compress them along the way…

And, what’s more, although sending those encrypted and\or compressed backup to Azure Storage is certainly an intriguing option, it’s not required. The tool also allows backups to local disk. So even if you’re not ready for cloud storage, the compression and encryption functionality might be welcome to save on-premise storage space, increase security, and maintain database backup plan standards across multiple SQL instances, including the vintage models.

Read through the document in the download link for all the details and caveats and let me know what you think. Meanwhile I’ll be retrofitting my ol’ 2005 clunker of a backup plan with some sweet 2014 database backup compression.