SVN and Managing Multiple Environments

Question

SVN and Managing Multiple Environments

Jeff Moden

SSC Guru

Points: 1004339
More actions
January 18, 2017 at 5:00 pm

#317554

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.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 1

I guess I rambled on about my problem a bit too much. I'm still interested in finding out "how are you using source control against a multiple environment like the one I have?"

Thanks folks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

Jeff Moden - Friday, January 20, 2017 6:14 AM
I guess I rambled on about my problem a bit too much. I'm still interested in finding out "how are you using source control against a multiple environment like the one I have?"

Ping Grant, and ask him to dive in. He works with a lot of the continual integration stuff from RedGate.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 3

Good idea. Thanks, Gail.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Thom A SSC Guru Points: 99130 More actions · Answer 4

Reading your main post seems like what's happening is exactly what our web dev is doing, and I have to agree; I really don't like it. We have a DEV server, however, as he has several projects on the go, he has several DEV databasesFor example Website_dev, Website_Motor, Website_Claims, website_Test. He then manages about 5 trunks on SVN for the website (excluding live) and I don't have any idea how he knows which one has the most up to date file(s) for different items. The databases all have synonyms inside, which means the SQL objects themselves are all "named" the same but it's awful to look at.

I had to update a query for him the other day on Dev, at which point he promptly told me it wasn't working. Thatw as because i hadn't applied the same update to his other 4 trunks, and he was using one of those. Considering that I wasn't even aware that the others had to be updated I was simply told "Can you update DEV to..." you'd think that that means "update website_dev" not "update website_dev, _test, and all the rest".

It's really not ideal, as you have to effectively micromanage it. Fortuataely, as we're a small company, he's our only WebDev, so he can do it, but add a couple more people and I can easily see things going wrong once (if we ever) get a second.

Personally, I think you need to brow beat until you get the solution you want (having one database per environment). It's definitely something I need to take into consideration if when are expand. Sorry that doesn't really give you an answer on how to deal with it, I'm more saying that I agree with you; your current set up is not the solution.

Thom~

Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Jeff Moden - Wednesday, January 18, 2017 5:00 PM
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.

No, but I'll be watching this thread. We're currently setting up SC using Git and Apex (an SSMS add-in, similar to Redgate's). Our model is simpler than yours: dev -> test -> UAT -> prod. Any good ideas (or bad ones) we have will find their way to you.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

daytonbrown2 Say Hey Kid Points: 679 More actions · Answer 6

I know this is an old post, but are there any updates that people want to share about what you are doing? We're mostly SSIS/ETL with some reporting etc. We're in the process of evaluating our next gen of ETL. We also definitely need some more rigor around source control on the database side.