Using the Team Edition of Visual Studio for Databases (VSDB)Â enables you to build a database out of source control. This means you can treat your database like code, as much as you can. The fact is, because of persistence, a database just isn’t code. Because you have to keep all the data previously entered, when you deploy a new version of your database to production, you don’t simply get to replace the database like you do with the code. You have to run scripts that alter that which can be altered, but preserve the existing data everywhere. That’s just how it is in production. You have to do the work necessary to protect your data.
Not so in Development. Development (and QA, Test, Financial Test, Performance Test) is the place where you can, for a time, treat your database like code. Do a full tear down and replace. I’m pushing this because I’m working with two extremes these days. Most of our new projects have been using tear down and rebuild from day one. Because of this, we know everything there is to know about the objects inside the database. There are no integrity issues, old code, missing keys, etc. We know this because every time we deploy, we get to rebuild the database. I also have a few older databases. These have not been rebuilt from scratch, ever. Ever.
I’ve just started going through and rebuilding one of them for the first time. There are broken stored procedures everywhere. These are procedures that got deployed to production years ago. Over time the structures which they referenced was changed or even dropped entirely, yet the procedures were left in place. Because they had already been stored by SQL Server and they’re not being referenced by application code (replaced completely by a deployment after all) they’re just sitting out there, waiting for the unwary. There are missing constraints on tables, found during data loads. There are all kinds of problems, usually identified quickly and easily when the database is rebuilt with each deployment.Â
It’s just reinforcing for me how useful tools like VSDB can become.