One area where Red Gate's SQL Source Control needs a little more information is the Static Data Feature. It is very easy to run into problems with it. Don't get me wrong, Red Gate's SQL Source Control is a fantastic tool. Their goal is database under source control in under five minutes. And they nail that. But for some reason several teams have run into problems using the static data feature. This page is a great way to get started. The goal of this post is to augment that documentation.

Red Gate's SQL Source Control Static Data Overview

The static data feature in Red Gate's SQL Source Control gives you or someone on your team the ability to check data into your source control repository of choice. When used in conjunction with the rest of Red Gate's DLM (database lifecycle management) tools the static data can then be deployed to any environment.

The data truly is static. Any change in the data, say a millisecond is added to a date will show up as a pending change. The identities will be the same (it does an identity insert on deployments). Everything will be the exact same.

Matching Engine

The table must have a primary key. This shouldn't be a big deal because all tables should have a primary key. But it is worth mentioning just in case. This is how the matching engine matches the rows. The matching engine works like this:

1. If the primary key is there and the data is different between the package being deployed (source control) and the table then an update is triggered.
2. If the table contains a primary key not in the package being deployed then the data is deleted.
3. If the table does not contain a primary key found in the package then a insert will occur.

Usage Scenario #1 - Making Data Changes

Getting the data under source control is the easy part. Working with the data is when issues come up. The tool really wants the data to be as static as possible.

Here are some steps to make sure your update goes as easy as possible.

0. Notify your team. Email, quick talk, IM, Slack message, whatevs. It seems a little overkill, but when more than one developer works on the same table you can get...interesting results.
1. Pull down the latest changes.
2. Apply the latest changes to the database.
3. Make changes.
4. Check-in the changes as soon as reasonably possible. The longer you wait the greater the chance for source control conflicts.

Chance of merge conflicts: minimal, unless a person is changing the same row of data.

Usage Scenario #2 - Schema changes

Did I mention the tool really wants the data to be static? Adding or removing a column really throws things off. Because the tool stores all the data as insert statements in source control. Any schema change will change every line of stored in source control.

Follow the same steps in the above section.

1. Notify your team. This is not overkill and will prevent future headaches.
2. Pull down the latest changes.
3. Apply the latest changes to the database.
4. Make changes.
5. Check-in the changes as soon as reasonably possible. The longer you wait the greater the chance for source control conflicts. If you are adding a column it won't hurt anything to have it out there.

Chance of merge conflicts: large. If developer A is making changes to the schema and developer B is updating a row they both check-in within a short time of a one another a merge conflict will occur.

Usage Scenario #3 - Deleting static data

Deleting data can cause some interesting problems because chances are you have a foreign key back to this table.

Option #1: Migration Script to convert data over
You can write a migration script to change all the referencing tables. Migration scripts run at the beginning of the deployment. This will only work if the data you are switching to already exists.

Option #2: Migration script or cascading deletes enabled
You can write a migration script to delete the data if you don't have cascading deletes enabled. Or you can enable cascading deletes. This option works when you don't care about the data being deleted. Just don't let your DBA hear you don't care about data.

Option #3: Soft Delete
Some DBAs prefer you have a starting on and ending on dates. If you have that with your static data and the code supports this then it is very easy to deactivate the data instead of deleting it.

Troubleshooting Check-in

Data changes are not being picked up is the most common problem. There are a few things you can do to solve this.

1. Make sure monitoring data changes option is enabled in the setup menu. Some teams turn this off to boost performance. Just turn it back on.
2. Unlink and link the table. This is a bit heavy handed. But it seems to solve 99% of the problems.

Troubleshooting Deployments

Chances are you are going to get the dreaded 126 error code. More often than not this means something is wrong with the package trying to be deployed. Dive into the logs. If the logs don't help, which is actually shocking the logging is very good, then try doing a SQL Data Compare and see what the differences are. Last resort do a data deployment from SQL Data Compare and see what blows up.

Conclusion

I hope this unofficial documentation helps with your usage of Red Gate's SQL Source Control. 99.9% of the time it works great. If you follow these tips hopefully your overall experience is pleasant.

As I come across other quirks I will update this page.