ssdt: versioning reference data with dacpac

Question

Dacpac is nice solution for versioning schema and we have to use pre/post deployment to amend the reference data.

Any better solution to do that?

Answer 1

The best way I have seen is to use merge statements, one table per file and import them into your post-deploy script using :r imports.

You get version history and easily comparable data and using sp_generate_merge makes it really simple.

Ed

Answer 2

If you're looking for a solution in SSDT to handle reference that does not involve the use of pre/post-deployment scripts, unfortunately there currently isn't one.

But it is currently one of the most requested features in SSDT so perhaps there's a chance it will get implemented some time in the future.

I am the maintainer of the sp_generate_merge OSS utility Ed mentioned, and at Redgate we recommend this approach to handling reference data in an offline way to our customers, in the following circumstances:

• If the data within the table is changing very frequently, as the one-file-per-table approach allows for branching/merging of concurrent changes from multiple developers
• If the data in the table contains environment-specific values, like application settings, as this method allows you to use SQLCMD variables and feed the values in from a deployment tool

Where the offline approach can be problematic:

• Non-determinism of the MERGE statement: before actually running the deployment against your target environment, it can be difficult to know what changes will be applied (if any). Worst case scenario, you could hit one of the documented issues in MERGE
• The workflow isn't necessarily the most natural way to edit data, as it requires running the utility proc and copying+pasting the output back into the original file. Editing the file directly is an alternative, but isn't the most user-friendly experience especially with large amounts of reference data
• Co-ordinating changes to both the schema and data within the reference table can be a challenge, given that SSDT is still responsible applying the schema changes. For example, if you want to add a new NOT NULL column without a default.

Another solution involves following an online approach, which is supported by our SSDT-alternative, ReadyRoll database projects. It allows the data to be edited directly in the database and subsequently imported into the project, with the sync script (i.e. containing INSERT, UPDATE, DELETE statements instead of MERGE) generated by its data comparison tool, alongside any schema changes.

You can read more about how the offline and online approaches differ in the ReadyRoll documentation.