Refresh, Part Deux, or: Synchronizing Database Changes Between Development/Staging and Live Production Servers

Now, onto the geek...One of the rooms at DCC was dedicated to Birds of a Feather (BOF) sessions, where groups of people interested in the same topic could just run an ad-hoc session.

I took advantage and announced a BOF session around the topic of the difficulty of merging development and staging code and database updates into (ever-changing) production database environments.

An example of this issue: our client, MichBio, has a site in production. It's changing daily: new visitors, users, content, etc. We have an active development copy of this database, but as soon as we took that snapshot, it was immediately out of date. We do our work on it and make many changes, both to code and configuration settings. After finishing our Quality Assurance process on that code, we need to merge everything with production, which has been changing constantly since we took our initial snapshot. This is a tricky (and not new) problem.

So a few of us got together in my BOF session and put our heads together to come up with some kind of recipe for this process. We determined that there are a number of Drupal modules that can help, but no single solution. We also determined that if your site simply CAN'T come down, even for a few minutes, then we have more work to do. We also determined that your best bet is to keep as much configuration data in the code as possible. This keeps your update pains to a minimum. This can be fairly simply done with Views, CCK types, and Panels. You're going to want to use the great Drush module to push code changes out remotely to your production server.

We came up with a basic set of steps to try to get this working:

1. Split the keyspace on live & staging/dev by even/odd keys (hacking core probably the best way to do this). More information here: http://codebaboon.com/method-syncronising-database-changes and here: http://drupal.org/node/181128. One thing to watch for here are tables that don't have primary keys!
2. Get a good copy of live database for dev.
3. Deploy the 'Journal' module (http://drupal.org/project/journal) to keep track of database changes on dev.
4. During development: Keep as much in code as possible so not directly modifying the database!
5. Take your site down (or refuse/block update/inserts to database)
6. Get a new 'good' copy of the live database on stage.
7. Replay the Journal entries (with the split keys) from dev to stage. This should maintain all refererential integrity.
8. Use the Deploy module (http://drupal.org/project/deploy) to move content from dev to stage.
9. Sanity check / QA? (Keep in mind your site is down right now)
10. Copy all tables on stage to live (minus Watchdog (?) and cache tables).
11. Dump all cache tables on live.
12. Dump/rebuild all external cache mechanisms.
13. Put live site back up!

We're going to give this a go on some of our sites and try to work out the kinks. Hopefully we can report on progress and present at another camp later in the year.

Problems like this are discussed at the groups.drupal.org site in the Change Management Systems group and the Packaging and Deployment group.

Some additional resources that I've found:

• This is a great post outlining how to split the keyspace of your database between servers and even is in a Drupal context: http://codebaboon.com/method-syncronising-database-changes. It references...
• An article by some of the France24 developers on a Drupal staging solution that they came up with (and use!). They have a presentation (video in French, but IRC transcript done in English) that they gave at Drupal Bar Camp Paris in 2008: http://www.mikiane.com/node/2008/04/18/drupalcamp-le-19-avril-paris and http://barcamp.pbworks.com/DrupalCampParisStaging. A great PDF presentation can be found here (linked from the IRC transcript) can be found here: http://www.mikiane.com/files/synchroStaging.pdf.
• Another instance of a solution for this problem: http://www.dave-cohen.com/node/1779. The original description of the method is here: http://www.dave-cohen.com/node/1066. The associated code can be found here: http://cvs.drupal.org/viewvc.py/drupal/contributions/sandbox/yogadex/mod.... The associated presentation at DrupalCon Boston 2008 can be found here: http://boston2008.drupalcon.org/session/updating-and-upgrading-live-sites.
• There is a list of drupal deployment resources here
• A good video presentation from Drupal Camp Seattle 2008 on the subject by Greg Dunlap of Palintir.net (blog post here)
• Another module worth looking at: Database Scripts (only 5.X version outside of dev version)
• The beginnings of another module available for problems along these lines: http://drupal.org/project/replication. Only 5.X supported (and it looks pretty quiet), but maybe it will give some ideas.
• Additional Drupal discussion on this topic: staging content to production servers. and maintaining live and development versions of large, dynamic sites.
• Additional Drupal discussion on management of CMS configuration data worth reading as well.

Add feedback if you've got it... I'd love to know what others are doing in their shop.