SVN and Managing Multiple Environments

  • Jeff Moden

    SSC Guru

    Points: 993897

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • Jeff Moden

    SSC Guru

    Points: 993897

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • Gail Shaw

    SSC Guru

    Points: 1004424

    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: 993897

    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.
    "If you think its expensive to hire a professional to do the job, wait until you hire an amateur."--Red Adair
    "Change is inevitable... change for the better is not."
    When you put the right degree of spin on it, the number 3|8 is also a glyph that describes the nature of a DBAs job. πŸ˜‰

    Helpful Links:
    How to post code problems

  • Thom A

    SSC Guru

    Points: 98212

    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.

  • ChrisM@Work

    SSC Guru

    Points: 186040

    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.

    [font="Arial"]β€œWrite the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw[/font]


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

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

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