Database Design Discipline

Change management for the database schema is a feature that's always been MIA for SQL Server. Although third-party products can fill the void, database schema change management isn't on the radar screen for most organizations that use SQL Server. Many small-to-midsized businesses (SMBs) tend to use a development process that doesn't typically incorporate formalized database change-management processes. Maybe many organizations that use SQL Server don't employ formal change management because SQL Server doesn't have built-in change-management features. Or maybe it's because the majority of SQL Server installations are SMBs.

In most organizations that don't use formal version and database schema control, the production version of the database is regarded as the current version. Development databases are usually created by taking a point-in-time copy of the production database. Subsequent application and database updates are developed and tested against the snapshot copy of the production database.

This process has several inherent problems.One problem is the difficulty in tracking the changes in each new code rollout, especially if problems arise in the new version. Examining the T-SQL update scripts isn't the most efficient way to track and identify schema changes. Also, problems arise when a company has multiple installations of the same database. For example, each branch-office installation can potentially have unique changes or database updates. In such a case, it can be difficult to know which version of the database is really the correct version.

Microsoft's newVisual Studio Team Edition for Database Professionals (Team Data) product is designed to address these problems.The Community Technology Preview for Team Data is now available, and the release to manufacturing is scheduled for the end of 2006. Team Data provides database schema change management for SQL Server 2005 and 2000.And Team Data uses Visual Studio's project-oriented IDE to provide database schema version control, moving the "production" or "master" copy of the database schema from within the production database to theVisual Studio?based version-control system.When you modify a database schema, Team Data generates a script that you can use to deploy the changes to your production servers. In addition to basic version control and deployment,Team Data lets DBAs rename database objects, compare the schema or data between two databases, and optionally synchronize the schema and data.

The tools that Team Data provides perform several vital database-management functions. However, the product probably won't be a silver bullet for database schema change management in most SQL Server organizations because Team Data is still a separate product from SQL Server. Being part of the Visual Studio Team System product line makes Team Data a tool that's more attractive and affordable to large organizations than SMBs. Perhaps more important is the fact that using Team Data for database schema change management requires businesses that currently use an informal process to change the way they do things. Changing present behaviors can be more difficult than learning and implementing new technologies. For more information about Team Data see Microsoft's Visual Studio Team Edition for Database Professionals Web page at: http://msdn.microsoft.com/vstudio/teamsystem/products/dbpro/default.aspx.