5

I am excited to make the leap to using DAC Fx and declarative database development. The major obstacle for me is how to handle complex data migrations across several different versions of the schema. In the old world, we can simply run all of our upgrade scripts in order, which guarantees that the schema is in the correct state at the time of data migration. How does this work when the upgrade path is dynamic?

For example, suppose there are several versions of my schema (DACPAC1-4) on existing instances:

  • DACPAC1: tableA exists and has valuable client data
  • DACPAC2: tableA is deprecated and replaced by tableB and normalized tableC; adds new tableD
    • post-deploy script: move data from tableA to new tableB and tableC; drop tableA
  • DACPAC3: tableC has a new, nullable columnX
    • post-deploy script: populates nullable column based on tableD
  • DACPAC4: tableC.columnX is non-nullable

If I need to be able to support upgrading DACPAC1-3 servers to the latest DACPAC4, I now have to write my pre- and post- deploy scripts in a clever enough way to detect which DACPAC is currently on the target and correctly handle data migration steps in order. Furthermore, I cannot simply re-use the naive post-deploy scripts I originally wrote, since they depend on intermediate versions of the schema.

Thanks in advance for any advice!

Loki Kriasus
  • 1,282
  • 10
  • 22
lu.s.benjamin
  • 51
  • 2

1 Answers1

0

What I typically do is the following:

  1. Create a System table that includes a property called SchemaVersion. All upgrade scripts are programmed to first check for the current version and then decide whether to execute its contents or not. After execution, it sets the SchemaVersion to the latest version stored in the script.

  2. I also typically include another property called MinAppVersion (minimum version that is compatible with the current schema). When the application attempt connecting to the database, it compare its current assembly version to the MinAppVersion stored in the database. If the version is equal or higher than the MinAppVersion then the connection is established, otherwise an exception is thrown.

I hope this helps. Kind Regards,

Sam Anwar
  • 669
  • 5
  • 8