Sql-server – Managing static (or reference) data between different environments

migrationschemasql servert-sql

This is a very specific question to a scenario I am working on, but also likely a common problem among SQL developers/database administrators. I am lucky enough to be able to change the current database processes in my job, and I would greatly appreciate any input

Current Database Use: Our company's website is database driven and requires a large set of static data to be accurate on each environment (testing, staging, production) for the site to function correctly. The data in these tables determines page text, ability to save responses, and what portion of data is loaded onto the page.

Environment Setup: all of our developers use a shared database where they drop in, modify, and remove particular rows from tables holding static data. This allows their HTML/code changes to function properly.

Problem: I am then required to capture and "migrate" these changes to staging and production. I've currently been using Red Gate's Data/Schema Compare, but it has been increasingly difficult for me to identify which changes were meant to be migrated and what was test/experimentation. Thus our team decided to use Visual Studio Database Projects for schema, since developers can now commit their own changes

This still poses an issue for data. Red Gate data compare captures all of the changed data on the testing environment, but there is no confirmation whether that data is correct and if there are any data modifications that should not be pushed to staging. Each build cycle, there is around 400-600 INSERTS/UPDATES/DELETE rows (all coming from the same ~15 tables that the website uses to function).

Solution? I've looked into several means of managing these data changes:

  1. Continue using Red Gate data compare. This doesn't seem like a great option due to: a) being unable to determine which changes are correct and b) there isn't any database versioning since all data is being compiled and pushed between environments.
  2. Require each developer to commit data changes and sequence them in order of arrival in GIT source control (001_,002_, etc). During each build to the next environment, execute schema changes followed by the data changes in the order they were committed/created. Disadvantage: the scripts needs to be written well and should be able to run multiple times without affecting data adversely. Also, time consuming for developers and myself (reviewing each data script to make sure it will function correctly).
  3. Before a build, create MERGE statements for each of the ~15 tables. This will work similar to #1, except I would be able to store what the data looked like during X build (thus there would be some sort of database versioning). Disadvantage: no one is confirming changes, I am simply assuming all changes are accurate.
  4. ?

Best Answer

The way I do this is with SSDT projects and a post deployment script that MERGE-s from a VALUES list to the target table.

If it is necessary to store different values in different environments you can use sqlcmd variables defined in the project properties and store a publish profile for each different environment.

It does require a particular development approach though. Developers should know that the contents of these tables is part of the project and that they should be editing the post deployment script and re-publishing rather than editing the data in these tables directly.

You could potentially add triggers to these tables that roll back changes and raise an error as a reminder if you feel that this may be a problem. The triggers could check a CONTEXT_INFO value and allow the modification to succeed if some specific value to allow the post deploy script to succeed.

Related Question