Database Projects
The VS Data Team has a new blog on Database Projects. I've written before about things I'd like to see made better in the database projects and Visual Data Tools, but here is how I'm leveraging them now to manage our projects now.
The projects I've been working on for the last couple of years were intra net based systems that were developed with a view toward deploying on a client's intra net. We needed to be able to manage the versioning of the database schema as tightly as we managed the versioning of the application. Database projects are the way we did that. In this type of application, we have total control over the database schema, and it is tightly bound to our specific suite of applications for use.
My approach:
I have 3 folders in my database projects: Create Scripts, Change Scripts, Shared Scripts. I use the Visual Data Designer generate my initial schema for a new database. When I'm done, I create several files under Change Scripts--one each for Security, Tables, Functions, Views, and Stored Procedures.
All change to the database tables happen in the Change Scripts folder. I usually prefix my change scripts with a two digit sequence number, the version number of the database under development, and finally the name of the object being modified. Rule 1 is "one script file per table being modified." All modifications to a particular table are done in that table's script file, and those steps must be sequenced correctly. Rule 2 is that every change script should be able to be run against the same database multiple times without breaking. This means that all operations should be encapsulated in the appropriate "IF [NOT] EXISTS(...)" statements.
Security, Functions, Views, and Stored Procedures all go in Shared scripts. The reasoning here is that these objects are easily destroyed and rebuilt without impacting the underlying data. To keep database bloat to a minimum, it's easiest to simply drop all of these objects (except the security objects) with each revision of the database, and recreate the ones needed by the application. I usually start with a "00 1.x.x DROP OBSOLETE OBJECTS.sql" script that just reads the sysobjects table removing any lightweight objects it finds. Then I recreate functions, views, and finally stored procedures. I usually have a single script for each type of object.
The change scripts can be run by any team-member on any local or shared copy of the database by any co-developer at any time, and be assured that s/he is coding against the latest schema. The scripts are source-controlled, so changes are immediately available and implicit in any database context you happen to be working in while developing.
When it comes time to release, the change scripts are bundled up into our deployment programs (we use the built-in .NET setup projects, as well as InstallShield) and executed against our customer's current systems. As part of the release cycle, the Create Scripts are updated to reflect the schema of the database at the time of the release--thus the change scripts are "posted" to the create scripts, archived, and a new set of change scripts are started for the next release. If we may encounter problem data that has to be corrected prior to upgrade, we'll generate a stored procedure that identifies the problem data, where to find it, and what to do about it for the customer. We make this available to them via SQL Server Reporting Services. When that report is empty, they're ready for upgrade.
As far as versioning goes, I haven't found a great way to handle this in SQL Server yet. Our system is a suite of programs with one "master" program that governs the whole system. The best I've been able to come up with so far is to create a table called tblModule consisting of a ModuleID, Name, Description, Major, Minor, Build, and Revision columns. Basically, as new modules for the system go into development, they are assigned a hard-coded ModuleID at the system level. The master module is always 1. The master module must exist in the system before any other child module can be added. Each module has an independent version number that must be at least equal to that of the master module.
These are the things that I've thought of and implemented for our team over the last year. I'm curious, what do you think I could be doing differently or better? Do you think any of the stuff I've written here would be helpful to you in your projects?