3

I have recently started a new job where they do most everything via Stored Procedures. We currently have a "deployment" folder under source control that contains timestamped folders with database change scripts (we actually have 3 folders under that - one for table modification scripts, one for views and functions, and one for sprocs), as well as a "Next" folder that has the new changes we are currently working on (it gets renamed when we do a deployment). We have three databases set up: a local copy on our workstation which is only accessible to the individual developer, a development DB and the live DB in production.

This means that we create and commit the .SQL files, and then have to manually run them on an almost daily basis (as new ones are added to source control, so pretty much every time we do an update we need to check what's changed in those folders and run them against our local copy of the DB), not to mention having to do the same thing on the dev and prod servers when we do deployments; also we have the databases named differently on each server to avoid accidentally running a change script in the wrong environment (this strikes me as odd as typically you have the DB instance named differently but the actual database named identical across all your servers) so we cannot include the USE statements in the scripts. This process seems very inefficient.

Is there a recommended best practice way of handling this kind of thing that I could suggest we start using instead?

Wayne Molina
  • 19,158
  • 26
  • 98
  • 163
  • 2
    Similar question here: http://stackoverflow.com/questions/2401229/database-structure-and-source-control-best-practice – Paddy Feb 10 '11 at 15:27
  • 2
    Not to sound trite but a best practice in these types of scenarios is very environment specific and can be very opinionated. Pick off inefficient points and improve the process iteratively. Don't try and provide a pie in the sky approach as it will most likely get canned. – Aaron McIver Feb 10 '11 at 15:29
  • @Aaron: Exactly, it took me 3 years to get from a hugely error prone manual process very like the one above to a slick, fully automated "SQL Diff" task that runs as part of our continious integration. It was 3 years of chipping away and continual minor process changes and improvements, building support tools one by one. P.s. yes I did get other billable work done in the mean time too :) – Binary Worrier Feb 10 '11 at 15:56

3 Answers3

1

I've been using a Visual Studio Database Project to handle this for about a year now. It's got a little learning curve, and it's not completely without hiccups, but it's definitely better than trying to manage this process manually.

As others have indicated, you should plan on factoring in a pretty heavy dose of "does it handle our situation" along with general best-practices. In my case, I've got multiple development branches that feed into an integration environment, along with some pretty restrictive DBA-type folks. Both of these have caused some complications along the way, and I expect you'll run into stuff like this, too.

D. Lambert
  • 1,304
  • 6
  • 12
0

Check out http://www.red-gate.com/products/sql-development/sql-compare/

You can use this tool to compares databases and create a script for the differences or synchronize the databases right aways

Ivo
  • 3,406
  • 4
  • 33
  • 56
0

SQL Source Control is the tool we'd recommend at Red Gate for you to share changes between developers. This integrates into SSMS, and works with your existing source control system. SQL Compare is best used for deployments, rather than frequent use cases such as sharing changes in a development environment. If SQL Source Control wouldn't work for you for whatever reason, we'd love to hear from you.

David Atkinson
  • 5,759
  • 2
  • 28
  • 35