Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Administering a Development Environment Expand / Collapse
Author
Message
Posted Tuesday, August 2, 2005 1:55 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 2:46 PM
Points: 392, Visits: 82
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/jhall/administeringadevelopmentenvironment.asp
Post #206625
Posted Tuesday, August 9, 2005 2:39 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Wednesday, June 17, 2009 4:55 AM
Points: 83, Visits: 8
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
Post #208591
Posted Tuesday, August 9, 2005 6:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, October 11, 2007 7:27 AM
Points: 3, Visits: 3

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.
Post #208647
Posted Tuesday, August 9, 2005 6:49 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, August 10, 2005 6:36 AM
Points: 10, Visits: 1

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.)

Post #208668
Posted Tuesday, August 9, 2005 7:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, October 14, 2014 5:31 AM
Points: 159, Visits: 432
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
Post #208676
Posted Tuesday, August 9, 2005 8:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Tuesday, July 24, 2012 8:49 AM
Points: 9, Visits: 262

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.

 




Post #208727
Posted Tuesday, August 9, 2005 9:22 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, December 28, 2005 8:13 AM
Points: 7, Visits: 1

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

Post #208775
Posted Tuesday, August 9, 2005 9:53 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 18, 2007 11:44 AM
Points: 132, Visits: 1
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.


Post #208794
Posted Tuesday, August 9, 2005 10:00 AM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, October 17, 2014 10:29 AM
Points: 3,214, Visits: 2,343

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 !





Regards
Rudy Komacsar
Senior Database Administrator

"Ave Caesar! - Morituri te salutamus."
Post #208800
Posted Tuesday, August 9, 2005 4:11 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, October 2, 2013 2:46 PM
Points: 392, Visits: 82
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.
Post #208903
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse