I’m stuck.
I’ve been advocating that our company use composite projects for our deployments using the VSTSDBE GDR (Visual Studio Team System Database Edition, General Distribution Release for those not instantly geeky). In a nutshell, VSTSDBE offers two mechanisms for deployment across multiple environments. Both of these work wonderfully well for automation when you are doing a full tear-down and rebuild. When you’re doing incremental deployments, they both fail.
Option 1: Use SQL Command variables to set environment specific variables such as file location, etc., and post-deployment scripts to set security. This works. It’s the method we used prior to the GDR. Unfortunately, security and other environment specific information is hidden inside scripts rather than visible to a given configuration directly within the VS interface.
Option 2: Create a composite project. It stores the common objects, the stuff on it’s way to production, in one project, and the environment specific stuff, such as security, in a second project. Some of the Micrsoft guys are even suggesting this approach. You can then store everything inside of configurations and in project objects, visible to the VS gui, easy to maintain, easy to build…
Ah, but there’s the rub. Easy to build when you’re rebuilding every time. Incremental changes require a database comparison between the project and the database. Ah, but which project. In a composite environment, it has to deploy each project independently. What happens when a change requires a data loss? You have to create that script manually. Fine, but how do you now get it into the rest of the build in an automated fashion?
I’m stuck.
I finally punted and posted it on the MSDN Forums.
I wrote and use this – http://www.codeplex.com/dbbuild
I stopped caring about maintaining a clean set of scripts that represent the schema, I just keep a starting point and a bunch of incremental scripts that are ran on each system using the dbbuild tool, which keeps track of what scripts are ran within the DB. I’m sure MSBUILD can do what the dbbuild tool can do, but I like simple syntax and well purposed tools.
I do need to get it working with SQL 2008.
There are a billion tools out there to script out the DB, but managing upgrades is where things need to keep running smoothly for the developers and the deployment from SQA to production.
Nice tool. We have one that’s somewhat similar, developed in-house by Bill Sulcius.
I still find having that pefect schema precisely synched with the developer’s code to be invaluable. It’s also absolutely necessary in some situations such as continuous integration.
So, while Data Dude is a fantastic development and code maintenance application, it’s only about 1/2 way there on deployments. Tools like yours or our in-house tool are going to be necessary.