If you have a local > staging > production setup and work with database-driven content management systems, you have almost certainly encountered problems keeping databases in sync across the environments. To my knowledge (at the time of writing) there is still no ideal solution. However, various techniques can help.
In this blog post, I cover some of the techniques, I have explored for keeping databases in sync across multiple stages. This is by no means exhaustive or complete, but a reference to some of the options that do exist.
Database comparison tools
The most obvious solution is database comparison tools. There are both open source and commercial software that claim to be able to do this.
In theory, this would be the easiest solution. However in practice, it doesn’t work. The comparison tools I have played with can relatively quickly fail and cause problems within the database. With CMS systems like EE, the tools fail to take into account relational data and eventually something goes wrong. They can also be incredibly pricey pieces of software which freelancers probably can't afford, and agency devs will have trouble getting their bosses to sign off.
Record executed SQL commands in one environment, then run them again on another
Recording executed SQL commands is a solution to this problem that a couple of devs have suggested to me.
On basic WordPress sites, this should...in theory...work. It would require some bespoke coding to set up. I believe with further exploration this could be a viable and effective option.
Separate Database For content
Different databases for content is an excellent idea that works in theory, but practically can be difficult to implement. Most content management systems I have played with just do not support such functionality without making hacks to the CMS core and as such this completely rules out this option for me.
If you have your own bespoke CMS this might be somewhat easier to implement.
Force client to make data changes on staging
The idea is that you force the client to make amends on the staging website. You then set up a function to move the site from the staging server to the live server. This function could then be run at a set time using a Cron job or triggered by a button within the CMS.
This works great if you only have two stages but completely forgets about the local stage. So there is still room for conflicts in the database between local and staging.
Use a file based CMS
There has been a lot of talk about file based CMS and how they are about to rise. These CMS use a flat file system, no database involved. As such, completely negating all the issues discussed in this document. However these CMS are still in their infancy and don't have thriving support communities, that more traditional CMS have.
Database Ownership & Maintenance windows
Database ownership and maintenance windows are the technique I fall back to. While it is cumbersome and not ideal, it works and does not involve any advanced code, or CMS based hacks; Just co-ordination between the dev team and none devs who are working on the CMS.
Essentially there is one database and this database is owned by different groups at different times. The example below assumes you have a local, staging, prod server set up, and also assumes you have two groups of people working on the website; developers and content populaters.
Content team freeze working. Dev team locks the live admin area to prevent access to the CMS and pulls down the database to staging / local.
Dev team exclusively works on the local / staging site
Dev team push up to production and re-enable admin area
Content populators enter content on production as they please till the following morning
For this to work efficiently. You must have an automated deployment tool such as Capistrano in place. This means you will be able to pull the website down and push it back up again automatically. Removing room for human error in deployment and dramatically speeding up the dev process.
For Wordpress installations, I even have a Capistrano command that locks the admin area for me and then re-opens it when the website is pushed back up to live from staging.
The disadvantage to this technique is it means devs and CMS users can not work simultaneously. It also won't be viable on eCommerce builds where orders will always be coming in. The advantage is that it does circumnavigate all the other issues that come with moving a website between local staging and production, but only for simple websites.
Whats your technique?
Please do comment with any different techniques you have used to tackle the issue.