Administering a Development Environment
-
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jhall/administeringadevelopmentenvironment.asp
-
A simple restore of the prod database to the dev server works fine until there are changes to the database design. The way we handle things is to unit test everything for a new version, including the database update scripts until we are happy with it all. At that point we pick up the latest prod database and test the full upgrade on a test server. Once sucessfully upgraded we do all our performance testing. If anything nasty comes up it goes back into development. Once we have been through the cycle satisfactorily we release the upgrade.
AndyM -
I was concerned by the blanket statement that development databases should be restored nightly. I understand where the author is coming from, but this idea only applies to development environments where the database never changes.
There are ways to work around this like having the developers apply their schema changes every morning before they start working, but that is an added step that most developers would rebel against.
Like all things I guess it depends on how many development databases you would have to maintain. Since my team works with more than 300 development databases this isn't even practical.
Just my 2 cents worth.
Lee Ragans
Database Administrator
Turner Broadcasting System Inc. -
One other tip: your development environment should mirror production as much as possible.
If production uses the Enterprise version, development should also.
If production is located 20 miles away, development should also. I once saw a situation where the developers couldn't optimize a system because development was in the next room and production was 200 miles away over a shared ISDN line.
If production is backed up nightly, so should development. (If they are not backed up nightly, watch out.)
-
This article is really too simplistic. As others have pointed out the idea of refreshing the development DB nightly is a non runner with true development as it means any development schema changes etc would be lost and need recreating nightly too - though it should be refreshed at theend of each project/rollout.
A key tip would be to use some schema comparison tool before rollout to compare the dev schema back to the live one (there are many tools advertised via this stie that can do the job but even a scripting out of all objects via Enterprise Manager and WinDiff should show you the changes - then these need to be checked against the schema change script the developers have produced to check they haven't missed anything).
Oh - and always use scripts for schema changes - GUI in enterprise manager is easy but not trackable in the same way as a source controlled set of change scripts is.
James Horsley
Workflow Consulting Limited -
While I think the article covered a minor part of maintaining a development environment, I disagree strongly about restoring production data to the development databases. I work in a highly regulated business. Our production databases contain both proprietary and product development data. They also have customer privacy data and employee privacy data. None of this should be in development databases which are open to many more people than the data in the production databases. The other more generic reason to not use production data is that production data seldom pushes the limits of of controlled fields. It tends much more towards the middle of value ranges. Both develpment and test data should reach and exceed the allowable limits of fields. This is needed to develop and test error routines. Performance testing is an entirely different database with multi user access emulation and large amounts of data.
-
It is normally my first "big splash" when I start a new contract, to insist the dev environment is refreshed at some regular interval (hopefully nightly!). The pain to re-apply the db development changes to the refreshed environment is a cost, but a cost worth paying, because...
if you can't systimatically apply your changes (from some source control repository) to a dev environment, how can you expect to roll them to production? this is the joint-top reason for refreshing dev - rollout testing *AND* a respectable prod-like environment to develop on.
I now how a handy c# app that rolls db changes out from one source tree to another and applies them against the db, so in effect I have a source tree in sync with the db. From this we can easily track changes when debugging, refresh dbs backwards (from prod through UAT & testing to dev) and roll forward new releases providing a rollback mechanism!
I have never met a developer who agreed with me initially, but after time and understanding both developers and managers have greatly appriecated the foresight, which gives me more support to enforce this at ever opportunity going forward.
Dan
-
And it's not just schema changes; this also applies to any code (stored procs, functions etc) that would also get reverted to production every night. If there was a way (DTS maybe??) to refresh the data only, not schema or code changes, then this might work. I'd love a way to do this simply, though.
-
I too was 'taken aback' by the blanket statement Refresh your development databases nightly. Also the mention of poorly performing queries (what ever happened to the QA/Perfromance Test environment (or database) that may be refreshed nightly (more like a production 'fix' environment). Multiple toered software development environments wer the standard once ... now with the ever increasing performance and decreasing cost of servers there is almost no reason why an organization cannot have a multi-tiered software development environment.
- Development
- Test
- QA/Performance Test
- Production 'fix'
- Production
After all if you can hire a enought developers to warrant software source code control system then you probably need a more segregated environment !
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
My statement regarding refreshing development databases was meant to suggest that the data that is being developed against is current. Assuming no schema changes have occured, a restore is appropriate to refresh this data. It accomplishes two things, gives you up to date data and goes a step further in testing your disaster recovery strategies. I was not implying to recommend that you overwrite development schema changes by restoring production data. Perhaps the statement was worded wrong, but I was just implying that its always a good idea to be developing against a "real-world" data set that closely matches production. This article was meant to be a very simplistic and theoretical view at development environments and if it sparks this much discussion I consider it a success.
-
On this topic, I agree with the concerns raised about "blanket refreshes", yet I do see the value of regular, simple refreshes. With that in mind, I did the following pretty soon after I arrived at my new company. Feel free to comment on it (the more I hear, the more I learn
)I proposed, and created, a "staging" server. This lives in the production world, and has very restricted access. The staging process follows the following simple paradigm, and does it on a daily basis:
1> receive production backups (Production Backups jobs last step is to copy nightly full backup file to specific location).
2> Restore backup
3> scrub all sensitive, private and security data (a seperate exercise that was done before we did the staging server, was to review the existing "scrubbing" process)
4> remove production users from databases
5> shrink logs
6> create new "scrubbed" backup in a date based folder
7> email success/failure
What we have the the "server" (a 35 step SQL Server Agent job) creating these scrubbed backups on a daily basis (35 databases, totalling +- 55 GB of databases and 55 GB of source backups). The process above takes 1h45 (and does a few extra's like taking a monthly backup and pushing it to a special directory so we can re-create month-end, and a annual directory as well). We kepp 2 days worth, so at any point in time, we have yesterdays full set, todays full set, last month ends full set and last years year-end full set.
We then have "client" jobs (SQL Server Agent jobs) on each of our development and test and training servers (we have 4 dev boxes, and 4 test boxes and 1 training box - 2 dev and 2 test boxes in a development stream, and 2 dev and 2 test boxes in a produciton support stream). These jobs fetch the backups from the date based folder, and restore the backups, apply environment specific settings and scrub's (like resetting emails to dev2@somewhere.outthere), adding environments users, and email success/failure. This can take anywhere betwenn 2 and 5 hours (given the different box specifications).
Typically our production support stream will have weekly refreshes (on a sunday), but they can, and have, gone through periods where nightly refreshes - expecially the first week after a new production rollout.
Development stream will typically get a refresh the first week of a new release, as does training.
Of course, any client an have an ad hoc refresh run as and when required, but the agent gives us the ability to make use of "down time" during the night (unless we are all working late, AGAIN
).Hmm - anything else to add? Oh yes - this might sound expensive - all these different servers etc.? Not really (relatively speaking), since all our dev and test and training and staging servers are all PC's, using IDE harddrives. Yes, that's unfortunate, since we cann get proper etsimation of production performance etc. but the business is still a startup, and can't afford 4 x 8 processor clusters for dev and test.
Thoughts, comments?
-
Dan. You said "I now how a handy c# app that rolls db changes out from one source tree to another and applies them against the db, so in effect I have a source tree in sync with the db. From this we can easily track changes when debugging, refresh dbs backwards (from prod through UAT & testing to dev) and roll forward new releases providing a rollback mechanism!"
I would very much like to look at this. I've been thinking of writing just such a beast. I had a good tool to do table on schema sync but it's in VB 6 and uses third party DLL's. I just have not had the time to move it to .Net. I have installed my old version at serveral customers. I send them an updated schema file, they run it against their production database and get a customized change script. This they run to appply the upgrade.
I want a tool that will do that but be in .Net so that I can just drop in the execuatable and current definition file but extend it to views, keys, and stored procedures. I could whip it out in VB.Net but I just don't have the two days that it would take right now.
ATBCharles Kincaid
-
I'm glad someone mentioned "remove sensitive data". I know many places simply test against copies of live but we should be resisting it and pushing for proper development data sets.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply