Visual Server Database Project data movement scenarios are executed in databases in different versions

Question

We used Visual Studio Database projects to maintain the current project schema, which worked for us in terms of getting the database schema on new development machines, but we did not use it to update the environments. Previously, we used migration scripts that transfer you from the initial version to the next version and so on, until you are in the current version, but now we want to use the capabilities of database projects.

I recently read two posts by Barclay Hill

Managing Data Movement During Deployment (Part 1)
Managing Data Movement During Deployment (Part 2)

That describes how to do scripts before and after deployment when moving from one version to another, which we used for a big effect, however now I am stuck on something that I can’t solve and feel like I missed. We have two databases that are on different versions, but the migration scripts do not work with 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 movement from version 1 to version 2

The pre-deployment script checks which version the database is in, and if it is in version 1, it puts the data from ColumnABC into a temporary table.

The mail deploy script verifies that we are in version 2 now, and checks for the existence of the temp table created in the script pre-deployment, and places it in the new ColumnXYZ column after converting char to int.

Version 3

Table1
Column123 INT

When we update the database from version 1 to version 2, and then to version 3, everything works fine. However, if we have a database in version 1 and you want to upgrade to version 3, the post-deployment script fails because there is no ColumnXYZ column, because now it is Column123.

In the old migration method, this would not be a problem, since the deployment goes through each version one by one, but this is not how database projects work. Has anyone else gone through this? How did you deal with this and I missed something obvious?

Answer 1

Sorry for not receiving any response here. Did you manage to do something at the end?

I was just starting to learn the “data dude,” and all the data movements certainly seem like elephants in the room.

I just read the two blog posts you mentioned, and I understand that you will need to combine version checking and expected schema conditions. Therefore, I assume that this would mean that you need to make two different sentences in deploying a post script for Version 3.

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

Answer 2

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

1) Requires update path 1-> 2-> 3.

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

• Pre:
  • (if ColumnXYZ does not exist)
    • Add ColumnXYZ Column (int)
    • Set ColumnXYZ = [some ColumnABC conversion]
• Installation:
  • Adding an XYZ column is skipped because it already exists.
• Message:
  • No action required