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?
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:
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.
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.
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
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.
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.