Staging database good practices

Question

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 plan is to develop & test first locally, push simple changes (small bug fixes, HTML/CSS, JS, etc) direct to production, and for larger changes, push first to staging subdomain for thorough testing and then to production.

I don't think that I need to keep the staging and production databases in sync (occasional manual updating would do) but I'm wondering if there are any general good practices with regard to maintaing a staging environment in relation to a production environment, especially when it comes to databases.

Any general thoughts/advice/experience would be appreciated.

UPDATE:

Thanks for the comments, I get the gist. I guess it's worth taking some time to think about this. Accepted the popular answer.

Bogus

Answer 1

By bypassing staging and making changes in production is a recipe for disaster and disuse. As you make those changes the definition of minor begins to change. Secondly as the two environments depart (i.e. staging no longer matches production) things break and you lose confidence in the staging environment. To get the most from a staging server you should be doing automated deployments to it, fully testing and only then deploy (automated) to production (no matter how small the change). You should also make sure the complete environment are as similar as possible, and stay that way. This obviously includes the DB. I normally setup a sync either daily or hourly (depending on how often I am building the site or app) to maintain the DB, and will often run this as part of the build process.

Pinfold

+1. The entire purpose of a staging environment is to mimic what is about to go into production. If there are changes in production that are not reflected in the code you have staged, then why bother with a staging server? – Hillell

Can you please share some idea how do you sync the DB automatically? – Supposed

@Supposed that should be a separate question as it will depend on the specific DB, OS, where you are running it (virtual, in data center, cloud) etc. – Pinfold

@SteveRobillard - What about the costs? I have a big production database and to sync it daily and have automated tests, I need to have an equally big db for stage which increases my monthly costs by a lot. Can you suggest a workaround? – Culinary

@AkshatGoel You should already be paying this cost by backing up or running a replica set. Having said that, you could use a smaller data set, however, every shortcut you take is a bug you may not catch until production. Only you can make those tradeoffs between cost and safety. – Pinfold

Answer 2

As someone developing a software tool that helps with every step of the deployment process, I can say the best-practice when it comes to staging environments is to mirror your production environment exactly. This includes an identical database schema (data isn't relevant, the occasionally backup/refresh is fine), the same operating system version, updated service packs, web server settings, etc.

In an ideal world, functional or user-acceptance testing doesn't need to be done in staging as the purpose of a staging environment is only to test your deployment to production. In the practical world however, sometimes it is acceptable for your staging environment to also be your functional or UA testing environment.

Every time you change a setting or alter configuration on your production server you should change the setting on the staging server, this will ensure that if you can deploy your application to staging it will, with great probability, deploy to production without error.

Soule

I disagree that "data isn't relevant." Depending on the system, production data can reveal all kinds of unpredictable issues in your staging environment... Which is sort of the point :) – Palladic

@Palladic - when I say data I mean something like "Orders" or "Employees". If you're storing configuration of any kind in the database then you are correct in that it definitely needs to be identical. However, if plain data is breaking your application in some way then that should have been caught during QA testing. Of course, if your staging and testing environments are the same, then it probably is a good idea to refresh your staging database often ;) – Soule

In a perfect world, I would agree. But in my experience, real world users always find ways to generate data no one else expected. – Palladic

@Dolph: You mean "somedomain.com?id=1; CREATE TABLE unexpected_user_data"? :) ... No, I do agree, the users are a flood of barbarians. – Bogus

@Palladic Good point about "production data can reveal all kinds of unpredictable issues in your staging environment". There are commands to transform the production data into similar enough data in staging (e.g. anonymise by changing all first names to a random different first name, change the ages, etc.) to mitigate the risk of data breach. – Chervil