SSDT - How to: Incremental Updates?

2
votes

I'm using the Database project in Visual Studio.

In my scenario I have several customers using different versions of the database.

For example, a customer who is in version 5 and another in version 7.

I make available an update to version 8.

The client version 5 should run the "cumulative" scripts (that update to version 6 and 7) before running the script that updates to version 8.

The client version 7 should run just what upgrade to version 8.

Is there any automatic way to do that today with the SSDT?

3
sql-serverdatabaseversion-controlsql-server-data-tools
Are you looking for an automated Build or a Publish from within VS? - SAS
I'am looking for Publish, SAS - Vinicius Gonçalves
Great, then use the argument GetVersion (if version 8 is not the latest checked in your example). Deploy using msbuild, or using the dacpac from your build, as I described in my answer. - SAS

3 Answers

3
votes

The concept of deploying cumulative "versions" of a database goes away when using SSDT (in my opinion). The fundamental concept of SSDT that one must understand is that of declarative database development. My personal definition of declarative database development:

You define what state you want your database schema to be in, not how to get it into that state. The publishing engine within SSDT will take care of getting your database into that state.

Simple example, you currently have a table in your database called [Person] and you want to add a column called [Person].[MiddleName]. You define that new column in SSDT and when you publish, SSDT will issue something akin to the following:

ALTER TABLE [Person] ADD COLUMN [MiddleName]

The point is that you don't have to write that SQL statement, SSDT writes and executes it for you. In this scenario the concept of database versions goes away. You're only ever updating your database to the state as defined in your SSDT project. Hope that helps.

2
votes

With msbuild: /p:GetVersion=CXXXX

Form VS, get the specific version (right-click the Project in Solution Explorer) Before publishing.

You run this against the two different targets, and the publish scripts will be created for the specified target (one per target).

If you use a dacpac instead, the script is auto-generated at deploy-time from the dacpac, by comparing to the target. If yo want to deploy the same version you can use the same dacpac file on both targets, the generated script will be created specifically for each target.

1
votes

You should supply the dacpac which is the output to the build and then give them that to deploy.

You can deploy it manually through SSMS or you can give them a command line that runs sqlpackage.exe - this can either perform the update or just generate a script that upgrades their database.