How do you control changes to your database schema? Can you manage differences between your production, development, test, and staging databases? In early June, Microsoft announced a new addition to Visual Studio Team System called Visual Studio Team Edition for Database Professionals (VSDB Pro), in which Visual Studio integrates database professionals and their databases into the application lifecycle and provides a foundation for managing database change in an offline, source- and version-controlled environment.
Managing and controlling changes throughout the application lifecycle has long been a problem in the software world, so much so that an entire industry has grown up around solving it. The development community has tools that manage code changes during development, but managing change to database schema still requires human intervention. And it's difficult to roll back a change if that change is incorrect or causes problems in other areas.
Adding to the complexity of change management is the continuous change that happens after a database is in production. DBAs spend hours and days updating indexes and optimization plans to improve production systems' performance. Even with self-managing databases, the DBA's role is a full-time job, especially in large organizations. One source of the difficulty in database-change management is the idea that the schema of the production database is the "master" version or the “one version of truth.” With the DBAs' constant changes, and without procedures to control those changes, the production database can easily become out of sync with the development, test, and staging databases.
A Logical Separation
I propose that we take a slightly different view of the problem and separate the two distinct elements of a database: the constantly changing data and the schema that defines that data. I agree that the data's production version is the master copy, but does that necessarily mean that the same should be said of the schema's production version? If we think of the schema and the data as separate parts, we can treat the schema differently than we treat the data. Data operations rarely affect the schema, and because there's a logical separation between data and schema, wouldn’t we benefit from controlling database schema changes outside of the production database?
I admit that the concept of controlling database schema changes outside of production systems took a long time to sink in. The light bulbs came on only when I related the concept to recent changes in Web management. In the Internet's early days, a Web site's production version was the master version. Web administrators would make changes to the live version of the Web site because the site needed to be live 24x7, just like a typical database system. As Web sites' complexity grew, one developer's changes overrode a different developer's changes, and synchronizing development efforts with the production system became increasingly difficult. So the industry adopted source- and version-control systems in which the Web site's master version is the one maintained in the source-control system and against which all development is conducted. Emergency changes to the live system are always tracked and slipstreamed back into the source-controlled version.
As more processing and application logic move to the database tier and database systems become more complex, the database industry needs to move in the same direction that the Web has. The more complicated the database systems become, the harder they are for an individual to manage and the more important advanced automated systems become to help everyone in the lifecycle work together.
Integrate with the Team
VSDB Pro provides a set of tools to help integrate a database into the software lifecycle. The Project System lets database professionals take a SQL Server 2005 or 2000 database schema offline and put it under source control, and it eases change deployment to all system versions. Other tools include a refactoring (or cascading change) engine with which you can rename any object (e.g., table, column, stored procedure) in the database and reflect that change across all objects in the database; a full unit-testing infrastructure that creates repeatable tests for database objects; a Data Generator that lets you create meaningful test data for other tests to execute against; and SchemaCompare and DataCompare, which can compare two different versions of a database (with SchemaCompare you can compare the source-controlled project against a live database), and build scripts to update one version to match the other. Over the next months I'll look at all of these features in more detail and continue to discuss this shift in the industry and how it affects the way we all work.
For more details about VSDB Pro, go to http://msdn.microsoft.com/vstudio/teamsystem/products/DBPro and read Karen Forster's exclusive interview "Life Cycle Tools for the Data Dude," InstantDoc ID 50271, and think how the tool might change the way the industry works with databases.