Best Practices : The Truth of the Schema is In The Database Project

Wednesday is at ZoDD. This is the day for focusing on the Whys behind the Hows of using Visual Studio Team System for Database Professionals, and for trying to provide some direction for dealing with different issues regarding the Application Development Lifecycle, as it relates to our product.

I’m going to start this series out with a brief post on the basic principle behind DBPro – isolated, off-line development.

It is no accident that DBPro is part of the Visual Studio family. Its emphasis has been on SQL Development from its inception. This does not mean we have no interest in the operational aspects of a SQL environment, but that our first focus has been to serve the Database Development community.

In the world of databases, there are two fundamental “truths”. There is the Truth for Data, and the Truth for Schema. In the majority of SQL installations today, the source of both these truths resides in one location: the production database. If you really want to know what the structure is for that table, there’s only one place to go to find out: the production database. If you want to understand what a particular Stored Procedure is doing, you have to get access to the Production Database to make sure that you are seeing what is really going on. And good luck if you have more than one instance of the Production Database – now you have to make sure they’re always structurally in sync, and if one is different from another, you have to figure out which one is correct, and then figure out how to get the other(s) into alignment. It can make for some long nights!

So one of the fundamental principles we started with was to move the Truth for Schema off of the Production Database. The database remains the Truth for Data, but we take the database schema – and by this we mean both the structural models (Tables, Views, etc) and the programmability objects (Stored Procedures, Functions, etc.) – reverse engineer them out of the Production Database into individual script files, load them into a Visual Studio Database Project, and put that project under Source Control. Now you have your schema in a managed environment, and using DBPro, you can coerce your Production Databases to conform to a centralized Schema definition. You have all the benefits of Source Control in a Development environment: Labeling, Versioning, History, rollback to previous versions, and the ability to manage conflicting schema changes during checkin. Moreover, now each developer has everything he needs to create a “Sandbox” environment for development and testing, and assure that all changes are correct and consistent prior to ever exposing the Production Database to them. We’ll be going over how each of these pieces fit into the overall Database Development Lifecycle in upcoming posts.

In DBPro, the Database Project is The Truth for Schema, and our entire product is focused on providing support for this project, and effectively managing the Schema of the Production Database from there.

Next Time : Roles in the Database Development Lifecycle

Comments

• Anonymous
  August 22, 2007
  PingBack from http://msdnrss.thecoderblogs.com/2007/08/22/best-practices-the-truth-of-the-schema-is-in-the-database-project/

• Anonymous
  October 12, 2007
  Sounds useful. I'm looking for this kind of thing ... how do we keep our db schema (both the structural models and the programmability objects) under source control? In our case, we use SVN for source control. Database development is done on a shared development server, which presents somewhat different challenges, especially when it comes to tracking who made what change. Looking forward to your future articles on this topic. Lars