6

We have been using Visual Studio Database projects to maintain the current schema of the project which has worked well for us in regards to getting the database schema onto new development machines but we have not used it to upgrade environments. Previously we have used migration scripts that take you from the initial version to the next version and so on until you are at the current release but now we want to leverage the power of the database projects.

I have recently read the two posts by Barclay Hill

Managing data motion during your deployments (Part 1)
Managing data motion during your deployments (Part 2)

Which describes how to do pre and post deployment scripts when going from one version to another which we have used to great effect however I am now stuck on something which I cannot solve and feel that I have missed. We have two databases that are on different versions but the migration scripts do not work on the older of the two. The following is a simplified version of our scenario.

Scenario

Version 1

Table1
ColumnABC CHAR(1)

Version 2

Table1
ColumnXYZ INT

Data motion from version 1 to version 2

Pre deployment script checks what version the database is at and if it is at version 1 it puts the data from ColumnABC into a temp table.

Post deployment script checks that we are now at version 2 and checks for the existence of the temp table created in the pre deployment script and puts it into the new column ColumnXYZ after converting the char to an int.

Version 3

Table1
Column123 INT

When we upgrade a database from version 1 to version 2 and then to version 3 everything works fine. However if we have a database at version 1 and want to jump to version 3 the post deployment script fails because there is no ColumnXYZ as it is now Column123.

In the old method of migration this would not have been a problem as the deployment goes through each of the versions one by one but that is not how the database projects work. Has anyone else gone through this? How did you deal with it and have I missed something obvious?

Michael Myers
  • 188,989
  • 46
  • 291
  • 292
Bronumski
  • 14,009
  • 6
  • 49
  • 77

2 Answers2

0

Sorry to see you didn't get any answer here. Did you manage to work something out in the end?

I've just started researching 'data dude', and the whole data motions thing definitely seems to be the elephant in the room.

I've just come from reading the two blog posts you mention, and my understanding is that you would need to combine checking the version AND the expected schema conditions. So I'm guessing that would mean you'd need to do two different clauses in your post deployment script for Version 3.

(Please let me know. I'm still trying to decide whether to invest in this or just go the classic DIY route.)

Benjol
  • 63,995
  • 54
  • 186
  • 268
0

As I see it, you have at least two options:

1) Require upgrade path 1->2->3.

2) Change your ColumnABC-ColumnXYZ datamotion to the following:

  • Pre:
    • (if ColumnXYZ does not exist)
      • Add column ColumnXYZ (int)
      • Set ColumnXYZ = [some conversion of ColumnABC]
  • Install:
    • Adding ColumnXYZ is skipped because it is already there.
  • Post:
    • No action required
S Koppenol
  • 205
  • 1
  • 9