Best Practice for handling reference data RRS feed

  • Question

  • Hi, 

    We are using dacpacs through automated pipeline for schema changes but reference data changes are still going separably.

    The preferred way is to do all database changes through dacpac.

    I consider few options:

    For each release add new .sql file which will contain current timestamp and version in the name and scripts that should be deployed in this release, script will also check currently deployed dacpac version in database and only deploy if version is not older then...

    This way we can run this scripts for all future deployments as well, so this approach mimics migrations in some respect.

    Other option is to just have one post deployment script that is cleaned up after each deployment so it always contains changes for next release.

    Each of these are not tidy and clean solutions, but we don't want to use redgate tools or migrations (flyway) at the moment.

    I wonder if someone had success with getting around this problem, an can share solution.


    • Edited by RafalH Monday, December 11, 2017 6:44 PM
    Monday, December 11, 2017 6:41 PM

All replies

  • After doing this for a few years now, the keyword is "Idempotency".

    Every reference data script must result in the desired end state if run once or 100 times. This can be tedious as it amounts to...


    ...for every row. Its not terrible if you use can use generators that meet your needed pattern. Lately I've been using something like this

    DECLARE @new TABLE (Col1 INT, Col2 VARCHAR(20), Col3 VARCHAR(20), etc, etc, ExistingRecordId INT)
    -- this table here is easier to maintain than individual IF ELSE statements later
    INSERT INTO @New (Col1, Col2)
      VALUES (1, "one")
           , (2, "Two)
           , etc
    UPDATE @New
      SET ExistingRecordID = e.ID
    FROM @New n
      INNER JOIN ExistingTable e
       ON n.Col2 = e.Col2 --col 2 is the natural key, col1 is just a surrogate
    --From  here its a matter of insert update
    UPDATE ExistingTable
      SET Col3 = n.Col3
    FROM @New n
      INNER JOIN ExistingTable e
       ON n.ExistingRecordID = e.ID
    INSERT INTO ExistingTable
    SELECT (columns)
    FROM @New n
    WHERE n.ExiustingRecordID IS NULL
    Its hard, if not impossible to automate reference data scripting in a generic way, but this way does work as long as no one adds a script that isnt re-runnable.

    Sunday, December 17, 2017 10:37 PM
  • Why not use a merge statement? This will handle the overhead of Insert/Update/Delete For each table you can:

    1.  Create either a stored procedure with a MERGE statement and reference in post-deployment

    2.  Create a "not in build" script and reference in post-deployment.

    Tuesday, July 9, 2019 6:40 PM