Is there a proper place for a staging database?

We have been storing our staging database on the production database server with the mindset that it makes sense to be as identical to production as possible.

Lately, some comments have made me question that idea. Since there is some remote chance that I will do something to production by mistake it may make sense to not put both on the same server.

Should my Staging database really live on the same server as my development database and not the same server as production?


In my book a staging environment should be independent because it lets you rehearse the roll-out procedures for a new release. If you are on the same box or same virtual machine you aren't getting the "full" experience of library updates and the like.

Personally I like virtual machines because I can pull production back to stage and then update it. This means that my update is very realistic, because all of the edge case data, libraries and such are being reproduced. This is a good thing... I can't count the number of times over the 9 year history of our major product that a library module wasn't included or some update script for the database hit edge cases that weren't detected in development and testing environments.

As far as touching the production environment... I would say never do this if there is an alternative. Update a shared library in staging that also impacts production and you will feel the pain. Update the code and cause your web server to go into a tizzy and you brought (at least part of) your live environment down.

If you have to fake it, I would recommend sharing with the development environments and just realize that updating production make cause unexpected downtime during the update as you validate everything works. We had to do that for the first few years for budgetary reasons and it can work as long as you don't just update production and walk away.

In summary

  • Production is sacrosanct: don't share any non-production aspects if you can avoid it.
  • Virtual machines are your friend: they let you clone working environments and update them with nearly zero risk (just copy the VM file over any botched update attempts).
  • Staging should be isolated from development to avoid overconfidence with your update routine.

Ideally you would want to have a separate staging environment that mirrors your live environment, but doesn't actually exist on it. However, $$$ doesn't always permit this, so the ideal isn't always followed.

This includes (but may not be limited to) the following:

  • Web servers
  • Database servers
  • Application servers

And anything on those machines (physical or virtual) should be isolated in their respective environments, so you shouldn't see staging code on a production server, and similarly you shouldn't see a staging database on a production database server. They should be separate.

Also, if you use a high amount of bandwidth internally you may want to even isolate the networks, to prevent the staging environment bandwidth usage from saturating the production environment's bandwidth.

Whichever solution you might choose in the end, I would say : keep your production server for production, and production only !

If you put some non-production on it, there is the risk of mistakes, of course, as you said... But there is also the risks of bugs : what if your application goes mad, and uses all the CPU of the server, for instance ? Your production might suffer from it.
And that's just an example, of course ;-)

In my opinion, the best solution would be to have another server for staging, with a setup that is as close as possible (a real "clone" would be the best) to the production setup.

Considering this might cost quite a bit for a machine used by only a few testers, it's often not that possible :-( An alternative I've seen is to use a Virtual Machine (hosted on your development server -- not the production one) : it acts like a "real" machine, on which you can do whatever you want, without impact eiter prod nor dev.

And, if necessary, you can use several Virtual Machine, if needed to be closest to your production settings.

Your staging DB should never be on the same server as production. I'd say its fine to have it on the same server as your dev server.

Are are a number of things that could go wrong,

Manipulating data on the wrong DB

Doing something that could actually

bring down the server. You may need

to reboot your DB server during development and testing.

As a rule I dont think developers should have access to the live environment. Only operations should have access.

As others have said, keeping non-production entities in your production environment should be avoided like the plague. There are too many possibilities for developers to mistakenly add or modify something upon which your production environment depends. Our production server is modified only during deployment. We track every file that's changed and have a mechanism in place to roll back changes with minimal effort.

Keep staging in your dev environment if you can't get dedicated hardware.

Having a staging database on the production server is risky. however, with a sufficiently strenuous debugging / testing stages, the actual risk to production is minimal. This is especially true if the load to staging is minimal.

If you don't have specific hardware for Development, Staging, and Production then having your Staging database on the Development SQL Server is a common solution. I

t's much safer than having your Staging database on the Production Server, trying to do something with the Staging Database, and taking down the Production SQL Server.

Category:sql server Time:2009-09-04 Views:0

Related post

  • Staging database predicament 2009-01-16

    Suppose that there are 3 databases for Production Staging Dev As far as I know, Staging database need to be in sync with Production database But, When we are developing, we can do whatever we want with Dev database and change schema. Now here comes t

  • Using cucumber with staging database without truncation and transactions 2010-10-20

    We have a Ruby on Rails 2.3.8 project, where data are almost exclusively read only. We would like to write acceptance tests which use staging database (copy of the production database) So we do not want to use transactions or truncation of the databa

  • Zend Framework: Proper way to interact with database? 2009-01-15

    I'm fairly new to the Zend Framework and MVC and I'm a bit confused by Zend_DB and the proper way to interact with the database. I'm using the PDO MySQL adapter and have created some classes to extend the abstract classes: class Users extends Zend_Db

  • Are staging tables / staging databases an anti-pattern? 2009-03-11

    Are staging tables an anti-pattern that is used when rpc (such as Java RMI or some kind of Web Service call) or messaging queue (such as JMS) would be a better solution, or are there problems better served by staging tables? To clarify: By staging ta

  • What is the proper way to move a database from one drive to another in SQL Server 2005? 2009-07-13

    I'm not looking to relocate the database to another server entirely, but just move the data file(s) and log file to another drive with more space. I've seen conflicting directions on how to do this, so I'm looking for the recommended proper way of do

  • TG2.1: Proper location to store a database session instance? 2010-02-21

    I am using a custom database (MongoDB) with TG 2.1 and i am wondering where the proper place to store the PyMongo connection/database instances would be? Eg, at the moment they are getting created inside of my inherited instance of AppConfig. Is ther

  • Staging database good practices 2010-05-19

    I'm about to deploy to production a fairly complex site and for the first time need a staging environment where I can test things in a more realistic environment, especially with regard to some external services that cannot be run locally. My general

  • Whats the proper way of accessing a database through an assembly? 2010-04-06

    I have a ASP.NET MVC application which is build up as an assembly that queries the database and a frontend that references this assembly and this assembly abstracts the underlying database. This means that my Assembly contains a app.config fi

  • What is the proper location for a sqlite3 database file? 2010-05-09

    Everyone: I'm using a sqlite3 database to store app's data. Instead of building a database within program, I introduced an existing db file: 'abc.sqlite' into my project and put it under my 'Resources' folder. So, I think this db file should be insid

  • Proper way to call a database function from Django? 2010-08-12

    i'm triyng to make a full text search with postgresql and django So I've created a function search_client(text) which returns a list of clients. To call it from the DB i use something like this: SELECT * FROM search_client('something') and i'm not re

  • MYSQL script (with conditions) for appending data to staging database 2011-12-09

    I have a very large database (with new information being added everyday) that a little cakePHP app was built for searching the database online. Because it is too big and slow,I want to stage the data in a new database with: - only the last 12 months

  • What is the proper way to split a database and share it across a Citrix Server? 2012-01-29

    I've developed a simple database that I need shared amongst several users. I'm a mere end-user with just enough skills to pull off the development of the database and not too much more. I work for a small company and we use Citrix to log into the par

  • Structure within staging area of data warehouse 2009-05-14

    We are working on a datawarehouse for a bank and have pretty much followed the standard Kimball model of staging tables, a star schema and an ETL to pull the data through the process. Kimball talks about using the staging area for import, cleaning, p

  • Rails 3: HEROKU staging and production repo managment 2011-06-25

    I've setup my app to run on Heroku with a staging and production environment as according to their documents. It seems pretty straightforward to manage with the staging app, push entire deplo

  • Git Staged - It have file history? 2011-09-02

    I am newbie in Git. And I really HATE use the command line, so I am using SmartGit. I have some newbie question about the stage of git. The correct work-flow is : stage -> commit -> synchronize. With local commit I have the history of the file.

  • Best Practices for a Web App Staging Server (on a budget) 2009-02-19

    I'd like to set up a staging server for a Rails app. I use git & github, Cap, and have a VPS with Apache/Passenger. I'm curious as to the best practices for a staging setup, as far as both the configuration of the staging server as well as the pr

  • SQL Inserting data from staging table to two other tables 2009-03-26

    I have a situation in which a CSV is uploaded to my application and each line essentially needs to be put into the database. I read each line and build a data table and then SqlBulkCopy that table up to a staging table. The staging table looks like t

  • Django: Conflict Between Live & Staging Sites on Same Server 2009-06-19

    I've recently pushed a Django app live. We built the app in a staging subdomain on the server. When I went live, I copied the files of the staging subdomain to the main site, created a staging database and pointed the old staging site at the new stag

  • copy production database to staging capistrano 2010-11-03

    I am using rails and capistrano with a staging and production server. I need to be able to copy the production database to the staging database when I deploy to staging. Is there an easy way to accomplish this? I thought about doing this with mysql a

Copyright (C), All Rights Reserved.

processed in 0.529 (s). 13 q(s)