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.
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:
- Developer Scripts out their proposed changes in DEV
- Developer Scripts out the PROD scripts of their changes
- Developer or DBA restores a copy of the PROD database into DEV
- Developer applies their change scripts from Step 1
- Developer rolls back their change using the PROD scripts from step 2
- Developer does a schema compare to make sure that their rollback scripts actually get them back to where PROD started
- Developer lather, rinses, and repeats until they are satisfied with the change
- Now the users can test in DEV(did anyone else get a cold shiver when I said that? lol)
- The users sign off on the change
- 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!
DEV -> UAT -> PROD
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 12 – UAT 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