Professional Microsoft® SQL Server® 2008 Administration

10.5. Creating Change Scripts

Creating change scripts is never a fun task for a DBA, but few things are more satisfying than a perfect deployment to four different environments. The only way to achieve this "perfect" deployment is to either invest a lot of time in writing a good script, like the one shown shortly, or invest in a tool.

There are many tools on the market to help a DBA package and deploy changes. For example, Red-Gate (www.red-gate.com) can compare two databases (test and production, for example) and package the change scripts to move production up to the same level. This same type of tool is available through many other vendors — ApexSQL, Idera, and Quest, to name a few. None of these tools eliminates human intervention entirely. You must have some interaction with the program to ensure that a change not meant to be deployed is not sent to production. The following sections address some specific tools for creating change scripts and deploying changes: Data Dude, PowerShell, and version tables.

10.5.1. Data Dude

Worth mentioning here is Visual Studio Team System for Database Professionals, also known as Data Dude. This Microsoft product aids in the complete change cycle. Visual Studio 2003 used to do this very well, but the deployment feature was removed in Visual Studio 2005 until the Team System version was released. The tool enables change detection and simple deployment to multiple environments. This is especially important in database deployment situations ...