1

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. ?
  • Determining which data is correct seems like it will either require a human to intervene and make subjective decisions no matter what, or that you add columns to reference tables that indicate whether this row is ok to push live or not. If you're just going to assume all changes are accurate, I've built a fairly easy to implement mousetrap for that: part 1 | part 2. – Aaron Bertrand Oct 22 '16 at 14:15
  • Also, I would strongly caution against MERGE - virtually everything you can do with MERGE you could do before MERGE, and I find the older methods safer and more reliable.. – Aaron Bertrand Oct 22 '16 at 14:16
  • @AaronBertrand I read through your articles, and it appears that this method will not work when foreign key constraints exists on these tables? (95% of them are foreign keyed to user response related tables.) Also, the majority of these changes are UPDATE (modifying identifiers or labels) and INSERTS for new data, while DELETE statements are rare. Would this alter your stance? – EnterTheCode Oct 22 '16 at 22:53
  • Well it certainly wouldn't make me any more willing to use MERGE. Whether you could use a method like mine depends on whether blocking the tables is more disruptive than adding disabling/re-enabling foreign keys and using delete instead of truncate. – Aaron Bertrand Oct 23 '16 at 0:05
  • Are you also using Red-Gate SQL Source control? That would allow developers use Data Compare to commit their own data changes into source control that are ready for deployment to other environments since only they will know what's ready and what's not. You can then use source control as the source for your build/deploy process for both schema and data. – Dan Guzman Oct 23 '16 at 14:24
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.