I just read a great introductory article on the subject of putting a database under source control at
http://www.sqlservercentral.com/articles/DB+Source+Control/151587/ . Unfortunately for me, it raised a whole lot of questions for me and I could use some advice, please.
We have a bit of a crazy environment. Of course, we have multiple databases in each environment. Here comes the craziness...
Let's say that our production database is named MyDatabase_Prod. Yeah... I know. I don't make the rules. The same database in the staging environment is called MyDatabase_Staging. Of course, the development environment is even worse because everyone wants to have their own database for the own project. So on the dev box, we have several databases called (for example) MyDatabase_Dev01, MyDatabase_Devl,, MyDatabase_CI, and MyDatabase_QA2 and they also have synonyms to things like MyOtherDatabase_Dev01, MyOtherDatbase_Devl, etc, etc.
Here's comes the rub in all of this. Using the current mess we have (because I'm not sure that I can convince them to do otherwise), is there any way to keep from accidentally wiping out an important change when we get to prod. For example, if someone changes ProcA in MyDatabase_Dev01 and someone else makes a different change to the same proc in MyDatabase_Devl but it's in a project that needs to be promoted later, are we relegated to the extremely manual method of looking at the history of every proc we want to promote so that we don't experience such an accident?
I'm pretty sure the answer there would be "yeah... you're stuck" but I thought I'd ask. I suspect the best thing for me to do would be to brow beat people into having only one of each database in each environment and, perhaps, having them all be named the same (except for the QA version which has to live on the Dev box... again, not my idea). I'm open to recommendations and affirmations on that subject, as well,
In the old days, it was pretty simply. As in this article, we'd have a master copy of all objects. If someone was going to work on one, they'd check it out and lock it. If someone else wanted to work on the same object, they'd have to check with the person that had it locked and disasters were prevented. Is there a way to do that with the combination of RedGate and SVN or would it all just be SVN?
Maybe a simpler question would be, how are you using source control against a multiple environment like the one I have? I've simply not been able to find a good example of how to use source control to effectively manage promotions from Dev to QA to Staging to Production and need to do just exactly that.