Rotating Header Image

I Love Fights That I Can’t Lose…. (Separated Environments and Security)

Part of your job as a DBA is to ensure the stability and integrity of your environment.  In assuming this role you’ve also probably had to have the fight about separated development (DEV), User Acceptance Testing (UAT), and Production (PROD) environments.  Now your environment might also include a Staging (STG) and maybe even a Training (TRN) but for the sake of this blog post we’re going to concentrate on the 3 environments that EVERYONE should have… DEV, UAT, and PROD.

So let’s first talk about a place… a savage place where there is ONLY PROD and developers have sysadmin access.


Scary huh?

Hopefully you don’t live in a place like this but if you do your first fight is a 2 round battle…

  • Round 1: Get a DEV environment established
  • Round 2: Remove all the security permissions from the developers in PROD

Now chances are if you had developers coding and making changes in PROD that you also don’t have any type of change management process… any type of process is going to seem like a pain in the butt for developers cause quite frankly once you’ve had the “keys to the kingdom” it’s hard to go back…. but you must.

Now the goal of this post is not to discuss the many different options for change management but if you were going to just leave it with a DEV and a PROD environment your change management process might look something like this:

  1. Developer Scripts out their proposed changes in DEV
  2. Developer Scripts out the PROD scripts of their changes
  3. Developer or DBA restores a copy of the PROD database into DEV
  4. Developer applies their change scripts from Step 1
  5. Developer rolls back their change using the PROD scripts from step 2
  6. Developer does a schema compare to make sure that their rollback scripts actually get them back to where PROD started
  7. Developer lather, rinses, and repeats until they are satisfied with the change
  8. Now the users can test in DEV(did anyone else get a cold shiver when I said that? lol)
  9. The users sign off on the change
  10. Developer creates an implementation plan, rollback plan, and a ticket to apply the changes.

So let’s go through and discuss why this is still not a world that I would want or feel comfortable in living in…

Step 1 and 2 are acceptable ( I wont go into a Source Control discussion here…. that’s a whole new blog post)

Step 3 is where the first problems start for me.  Restoring a copy of PROD over DEV could potentially have a negative impact if:

  • Developer didn’t script out ALL their changes…. and now they’re gone
  • You have other developers trying to work on their changes… which you’ve now just overwritten with a copy of PROD

Step 4 through 7 – As much as I love my developers… that is ALOT of trust that they are actually going to do these steps and not just skip to step 8 and have the users test.  Come to think of it… who knows if they’ve actually even restored a copy of PROD to DEV and reapplied their changes… they could of gone right to getting the users to test in DEV.

Step 10now becomes a run with an untested Implementation Plan, untested Rollback Plan, and at this point the only thing you’ve really done is make it so YOU (the DBA) are the one making the changes in PROD…. Great… so when things go horribly wrong I wonder who is going to get the call first. 

Trust me…. unless you’re Buck Woody and failure is as real as a unicorn… this is NOT where you want to be.

So now let’s talk a shiny happy place… or at least a place where you can go home at the end of the day and sleep at night.

@SQLCrotch …. this one is for you!


Ok let’s agree on some points right out of the gate:

  • Developers have no access in PROD (other then read-only)
  • Developers have the EXACT SAME permissions in UAT that they do in PROD… this is going to be a real sticking point but what you don’t want is developers promoting code to UAT and then when it doesn’t work… trying to fix it in UAT
  • Developers have Full Access in DEV

Now with that understanding in place…. let’s look at how change management could go and why this is my happy place.

Step 1 – Developer develop in DEV… sounds easy enough but if you can get here from where you were (developers developing in PROD) then this is a HUGE win already.  Developers can request a restore or at least a schema refresh depending on whether or not they are comfortable that DEV is an accurate representation of what is in PROD

Step 2 – Developer creates an implementation plan

Step 3 – Developer creates a rollback plan

Step 4 – Developer submits a ticket to promote their code changes into UAT

Step 5 – DBA (or Release Manager depending on the size of your company) restores UAT with a copy of PROD

Step 6– DBA follows the Implementation Plan and releases the code into UAT

  • If the release fails then it goes back to the developer at Step 1… If the release is successful…

Step 8 – DBA runs the Rollback Plan in UAT

  • But it was successful??? Why do this?  There is nothing worse then having the false sense of security that just because you have a rollback plan that it will actually get you BACK to where you were.  Running the rollback plan will allow you to compare to PRODand make sure that you’re back to square one.

Step 9 – DBA runs a schema compare between UAT and PROD to ensure that the rollback steps will actually return the environment to where it was.

  • If the Schema Compare shows differences after running the rollback then it goes back to the developer at Step 1… If the release is successful…

Step 10– DBA follows the Implementation Plan and releases the code into UAT

Step 11 – User tests in UAT

  • Now if the user does NOT accept the changes… You don’t keep band-aiding UAT until the developer gets it right.  If the user fails the UAT test it goes ALL the way back to step 1. Now if you want to give SOME flexibility (due to timelines, etc) here you can.. and allow the developer to give you new scripts to apply, etc… but at the end of the day before you go to the next step you HAVE to go back to Step 1 to ensure a clean UAT run.

Step 12UAT PASSED… then you schedule the release to go into PROD

Step 13 – Release into PRODusing the exact same implimentation plan


Feel all warm and fuzzy yet?


Let’s discuss what we’ve gained…

  • Developers develop in DEV – fantastic
  • The Implimentation Plan is tested in UAT – fantastic
  • The Rollback Plan is tested in UAT – fantastic
  • The separation of duties adds a level of auditability and process that auditors will WAY MORE comfortable with – fantastic
  • The user does not have to test in an ever changing DEV environment or in PROD – fantastic
  • The change (and the process) that is being put into PROD has been tested one or many times to minimize downtime or potential problems – fantastic

Now this was a braindump of information for me and I’ll probably end up writing a multipart article on the value and process of established DEV, UAT, and PROD environments but hopefully until that time comes this will help some win their battles on why it is SO important to have separated environment and security.

At the end of the day this is a great fight to be in… cause you should NEVER lose


Post to Twitter Post to Delicious Post to Digg Post to StumbleUpon


  1. Wendy says:

    I’m all good with the ‘fantastic!’ but I was really hoping for: FANTASTICAL!

  2. I am actually fighting this fight right now. Developers really don’t like when you take their access away. It is always good to have Executive sponsor support as well.

  3. Lori Edwards says:

    So, we’re in the middle of this fight now – could you come out to Tucson and be in my corner? ha ha ha

  4. […] environment.  Colin over at Benchmark IT Consulting has written a great article on this topic.  I Love Fights That I Can’t Lose…. (Separated Environments and Security).  I encourage everyone to check it […]

  5. I would love to go anywhere to fight this one cause like I said… it’s a fight that you just can’t lose 🙂

    Just let me know where to send the bill for my plane ticket and time and I’m there 😀 hahaha

  6. Looks like I struck a chord with a few people on this subject… I’m going to be working on a podcast to help better illustrate everything I said above

    Thanks everyone!!

  7. Meredith Ryan-Smith says:

    Lori – I will be there in Feb – if you are still fighting this one I will be happy to help!

    We’ve gotten around this one by putting our dev’s on the hook for support once the new code goes into prod. They have to be on site for support as soon as we open for business the day after a push. Once they have had a few bad deployments they are much more careful about following the dev/test/prod process. It’s not perfect, but they get the importance now.

  8. Meredith – as an end user I don’t think I’d care who has to “clean up the mess” but I’d be way more concerned with how the mess got into PROD in the first place.

    Having the developer on the hook definitely makes the DBA’s job easier but if the end goal is successful and accurate migrations (which as a user I think I just want the system to work) then we have to keep their grubby little hands out of our PROD world 🙂

    Sounds like you’re definitely heading in the right direction and the more accountantable you make the developers have the more they will be willing to follow process but I’d still push very hard for that next level of separation of duties.

  9. Developer develops in DEV and only have read-only access to PROD while DBA can develop in PROD and full access to PROD?

  10. Who said DBAs develop in PROD?

    Depending on the size of your company you may have a release manager or team who do all the releases.

    If you don’t have this luxury then a good work-around is to have your DBAs “normal” account have read-only type of access and to have a different “admin” active directory account that is audited for when they do releases.

    ie) BENCHMARK\ColinStasiuk – read-only
    BENCHMARK\aColinStasiuk – admin account for release management which is also audited.


  11. @SQLCrotch says:

    Great Job man! I love it!!!! Now, next plan or post. How to automate this with PowerShell 🙂 I may be able to help on that one. LOL Great job buddy and thank you!!!

  12. Rafal says:

    Hey Colin, have you ever looked at creating recipes with Capistrano? It is mostly Linux based, but there might be a Windows port or something.

    After that… User with the right access goes
    >cap deploy:migrate

    If something goes really bad, they go
    >cap deploy:rollback

    This is assuming you also have the proper source control implementation 🙂


  13. Can’t say I have sir… never heard of it.

    but agree 100%… the use of source control is key and the word that makes your comment golden is “PROPER”.

    You’ll have to buy me a beer and tell me about Capistrano…. 🙂

  14. Rafal says:

    How about I show you over lunch or coffee one day? Capistrano has a bit of stuff built in, yet you can also write your own “recipes” to make some other stuff happen.. Neat little tool

    Check it here

  15. Mike says:


    Nice article and I agree with your thoughts. The problem is really with management. If the dba does not have the backing of management you are never going to get the developers out of prod. I work for a major entertainment company that owns some of the biggest web properties on the internet and each one is a bit of a silo. We have some environments that are really buttoned up, but generally they have PCI/PII data. In most cases management doesn’t want to pay for the resouces it would take to implement your plan above.

  16. Mike

    yeah money is always a big factor and if you’ve tried to explain the need for the separation of environments and it’s fallen on deaf management ears then really what can you do?

    I would make sure that you have something in your back pocket that shows that you’ve explained the need, the risk, etc and that management said no cause if you’re working with some of the biggest web properties on the internet you can guarantee that if something bad happens that heads are going to roll and you want to be sure that it’s NOT yours.

    …it’s like driving without insurance… sure you’ll get away with it for a while.. but something is bound to eventually happen 😀 hahaha

  17. AjarnMark says:

    Colin, great article and I could not agree more! I have had this argument in the past and it is always worth it. You are so very right that in the ugly scenario of 1 environment only, the DBA will be on the hook, not the developers. After all, it is the DBA’s responsibility to protect the database, not the developer’s. One tactic I have used in the argument in the past is to argue that with only one environment, you only have DEV, you don’t have PROD, and you are exposing your clients to the vagaries of DEV, such as make-believe data. Fortunately, in our situation now, we just have debates about whether we are big enough to require a separate TRN environment or whether to just reuse the UAT one. By the way, we have a separate TEST environment where the IS/IT QA testers test the release before it gets shown to the users in UAT.

    Mike, it doesn’t necessarily take a lot of money to do the separation. We have our developers developing on their local boxes with SQL Server Developer Edition (you could also use SQL Express for DEV). Or, if you are really crunched, then consider creating a DEV Database on your prod server, or a DEV named instance on the PROD server. Neither of those require the cost of an extra machine or licensing, but they do run the risk that DEV efforts will hamper the performance of PROD…not ideal, but it’s still better than DEV occurring IN PROD.

  18. If you run into the same issue, don’t delete your files as Apple suggests. Install through iTunes to avoid the storage problems. That goes for iOS 8.0.1 or any iOS update.

Leave a Reply

Twitter links powered by Tweet This v1.6.1, a WordPress plugin for Twitter.