2

I am looking into the RedGate SQL Toolbelt to allow for full database CI and am comfortable with how the tools can facilitate my needs for schema and static data.

However, there will be a certain amount of data in other tables that I would like to source control and use within my CI. These tables may have a million records with only a handful of records needing to be in source control. The database design means that this data cannot go in a separate table that can then be source controlled.

A good example may be a Users table where you want to source control the Admin or System user so it can be part of your CI but want to exclude any real users.

RedGate have had a request for filtered static data around since mid-2011 and I was wondering if anyone had devised a sensible method for getting around this limitation?

Dan Def
  • 1,836
  • 2
  • 21
  • 39

2 Answers2

0

All I can think of would be:

  1. Have two tables
  2. One for casual users
  3. One for super users that you want to have as static data
  4. Commit just super users table data to source control
  5. Create a view that would UNION ALL both of these tables
  6. Use this view instead of actual tables in your code.

Maybe that's not the prettiest way to do it, but I think it should work for your use case.

Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
  • Thanks for your feedback. For several reasons that isn't feasible for my real life scenario. The most severe issue being that if you want a UserID Foreign Key in a table, you can only link it to one of the two tables. We really cannot use more than one table :(. – Dan Def Jan 18 '17 at 16:07
  • @DanDef I'm wondering whether it's possible to prepare a migration script that would be triggered on each CI build and ignore static-data. – Evaldas Buinauskas Jan 19 '17 at 11:39
  • yeah I was going to have a look at trying to use those this afternoon. Will have a look and report back... – Dan Def Jan 19 '17 at 16:18
0

I tried the solution involving Migration Scripts, but the order in which they are executed was still causing problems.

The solution I opted for in the end was to have a separate database that was linked to source control, with all tables that contain system data or mixed data to have their records source controlled.

I then develop in another database and push schema and data changes to the source controlled database to commit them. That way, the source controlled data tables never contain 'user' data.

I found the DLM Automation Cmdlets to be lacking in features and instead opted for running SQL Compare and SQL Data Compare using the command line to perform our CI. This hasn't been without the occasional hiccup, but these are solved using the aforementioned tools and manually pushing from SC into our CI database.

Dan Def
  • 1,836
  • 2
  • 21
  • 39