Duncan Pryde (3/10/2011)
Jamie Thomson (3/10/2011)
The big benefits as far as I can see them are:
-Development-time error checking (i.e. find out about errors before you actually run the code - so you wont get caught by deferred name resolution)
-Declarative development. (i.e. You define what the database state should be and the tool works out how to get it to that state, as opposed to you having to author all of the ALTER statements)
-Code analysis (i.e. it highlights bad coding practices)
The second one does look like it might swing it. Up to now, we've tended to hand-write upgrade and rollback scripts for each release. It works, but it's time-consuming and quite clunky. I imagine this would be an improvement on that approach.
That's the biggie... My deployment routine is to restore a copy of the target locally and get VS to generate the deployment scripts for me... some checking is necessary of course, and some small edits have to be made, but it works, and it gets the target database in line with your model time and time again. It takes me less than a day all included to recreate the live environment locally and on test for four databases, generate scripts, tidy them up and deploy to test. Where I used to work, a database team or 4, serving a similar sized development team, took 2 or 3 times as long to put together a deployment script.
For our last release, however, another developer did the release, and while he started with a generated deployment script he couldn't be bothered to follow my established procedure (one script per DB into test, and a second after bug-fixes, so you have a part 1 and a part 2 script to run on production eventually - maybe a part 3 if the bug fixes needed further fixing) and instead hand-cranked extra items into it rather than generating a catch-up script from the testing phase... we've spent a few days tracking down and fixing the errors he introduced! Missed procedures, one procedure that was created with a rogue "drop procedure..." statement after it so every time it ran it dropped another procedure, missing static data... all manner of issues!