The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Managing Dev/Test/Staging/Prod Databases

My company is having serious growing pains with managing our databases.  We have set up a dev, staging and production environment, but we're having a hard time keeping it pure.  Due to problems, we frequently make changes directly in the staging or even prod databases.  Also, we've managed to now create multiple staging DBs (they copied the prod once), so stuff is being changed all over the place.

I'd like to put a stop to it.

Ideally, we'd only make changes to dev.  Then, we'd update staging only using change scripts (DDL and SQL) generated from the Dev changes.  Those scripts would be clearly labeled by what project they are for.  Only one person would have access to run those scripts on Staging.  Then, after they are tested and approved, those same scripts could be run on production by our production guy.

I guess it would also be good to have a fourth server as well for Testing.  It would come between Staging and Dev and then we could make Staging look just like Production.

Does anyone have good experience with a combination of process and tools for this?
Dave Send private email
Tuesday, October 18, 2005
PeopleSoft has elaborate systems for doing just this. I haven't worked with that for a while, DEMO was the system just as it came from the vendor; DEV had one's custom development, then there were TEST and PROD. One installed updates into DEMO, evaluated the comparison reports against DEV, eliminated conflicts with one's custom work, and pushed it through.

Somebody in the PeopleSoft Fans mailing list could likely point you to documentation on this.
George Jansen Send private email
Tuesday, October 18, 2005
Sorry, I forgot to add that we are using SQL Server and pretty much just using Enterprise Manager as our IDE.

A few of the developers have Visual Studio .NET, but we don't manage any database projects with it.
Dave Send private email
Tuesday, October 18, 2005
We put everything in scripts in a common network share with the date_initials_BriefDesc.sql and then an Excel file where we enter the typical info plus what regions the script has been executed on (Test, Staging, Acceptance, Prod). 

We have a little in house app that goes through that list and creates a centralized script from the Excel file for each region.  We send the script to our DBA and he takes care of running the scripts on the correct servers.

Pretty simple and straight forward really. 

[off topic]You might want to look into using SQL Query Analyzer.  It doesn't do the quirky formatting that you get in Enterprise Manager.  Plus it is so much easier to create scripts and stored procs, too.  Or at least for me.

I might get in EM once or twice a month (for the occasional DTS or Backup\Recovery on (local)), but I use Query Analyzer every day.
Tim Shults Send private email
Tuesday, October 18, 2005

I'm not entirely sure what you mean by "staging" (we don't have that), but is there any reason why test and staging need to be a different machine?  Usually the setup of the test environment (but not necessarily the data) should mirror prod as close as possible, and you seem to be saying the same thing about staging.

Anyway, development making changes in prod is a big no-no, except of course when nobody else is capable (in which case, prod is missing some needed capability).  Stop that, and the rest begins to fall in place.  It helps if the sysadmin for the controlled environments is a different person or persons than the one you have for dev.

The change scripts idea is good.  Having a single designated person that can run them is also good.  Keeping development out of the controlled environments as much as possible is good.  :)
Matt Brown
Tuesday, October 18, 2005
The reason for my desire to split out Staging into Test and Staging is that we put code that we aren't yet sure about on Staging right now.  Our QA validates it before it goes to Production.

Since there is always new stuff there, it never accurately reflects what's in Production.  I'd rather have a Test server where developers can put their stuff when they are done.  QA would test on Test.  After they approve, changes could be made to Staging as a final test before they go to Prod.  Staging should look identical to Prod.

As for Query Analyzer, yes we use that too.  I was saying "Enterprise Manager" as a shortcut for the whole suite.
Dave Send private email
Tuesday, October 18, 2005
If you are running MS SQL Server - DBGhost

Say it again - DBGhost

Seriously, DBGhost is what you need.

Not affiliated but am on the testimonials page.
... Send private email
Tuesday, October 18, 2005
At first glance, DBGhost does look like what I'm looking for.  Thanks!  Can you tell me a bit more about how you use it - i.e. what processes you've built around the tool?

Does anyone have any experience with Red Gate Software's tools?  They look similar, but I only know what I read on their site.
Dave Send private email
Wednesday, October 19, 2005
Have used the Redgate toolset for years and will not work without it. Never had a problem with data corruption or inconsistance which is amazing. Best money you will ever spend.
mike Send private email
Wednesday, October 19, 2005
Mike, do you have any experience with DBGhost to compare with the Red Gate software?
Dave Send private email
Friday, October 21, 2005

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz