How to maintain application configuration data in the database across multiple environments?

Question

The company I work for has attempted to maintain configuration data for our application across multiple environments, but syncing that data has always been problematic and we've never come up with a good solution.

To help clarify, we (developers or business) might change some configuration using our admin interface on the Staging environment, test it, and then want to copy those changes to our Production environment without having to redo all the changes in the Production environment. We've also typically wanted to sync these changes between all of our environments (dev, staging, & production), again without having to make the changes individually on each environment.

Preferably we don't want to use any low level tools, as asking the business to use something like RedGate's SQL Data Compare and copying individual rows wouldn't work. It would need to be something intuitive enough so the not-so-technical could use it and not overwhelm them.

How do we maintain this configuration data across the different environments while still providing the business with the ability to test their changes before applying it to the live environment?

Answer 1

What level of technical know-how will the users have? As product manager at Red Gate I can give you our perspective. Although we're not considering support for data in our v1 release of SQL Source Control (currently under development), it will inevitably follow. However, this would still require those who wish to edit static data to do so in SSMS, although they could of course use edit the values using SSMS's graphical designers. Or is this still less intuitive than you'd like? They would be changing the data on a dev or staging database and would be expected to verify that the changes are correct and function as expected. These would then be committed to source control via our tool.

To deploy it would be a question of launching SQL Data Compare, although we plan to provide simple shortcuts from SSMS, rather than requiring users to negotiate their way around a completely separate tool. We haven't nailed down designs for this functionality so I'd encourage you to participate in our Early Access Program and state your case. More details of the Program can be found here:

http://www.red-gate.com/Products/SQL_Source_Control/index.htm