SSDT implementation: Alter table insteed of Create

Question

We just trying to implement SSDT in our project.

We have lots of clients for one of our products which is built on a single DB (DBDB) with tables and stored procedures only.

We created one SSDT project for database DBDB (using VS 2012 > SQL Server object Browser > right click on project > New Project).

Once we build that project it creates one .sql file.

Problem: if we run that file on client's DBDB - it creates all the tables again & it deletes all records in it [this fulfills the requirements but deletes the existing records :-( ]

What we need: only the update which is not present on the client's DBDB should get update with new changes.

Note : we have no direct access to client's DBDB database for comparing with our latest DBDB. We only can send them some magic script file which will update their DBDB to the latest state.

Answer 1

It sounds like you may not be pushing the changes correctly. You have a couple of options if you've built a SQL Project.

1. Give them the dacpac and have them use SQLPackage to update their own database.
2. Generate an update script against your customer's "current" version and give that to them.

In any case, it sounds like your publish option might be set to drop and recreate the database each time. I've written quite a few articles on SSDT SQL Projects and getting started that might be helpful here: http://schottsql.blogspot.com/2013/10/all-ssdt-articles.html

Answer 2

The only way to update the Client's DB is to compare the DB schemas and then apply the delta. Any way you do it, you will need some way to get a hold on the schema thats running at the client:

1. IF you ship a versioned product, it is easiest to deploy version N-1 of that to your development server and compare that to the version N you are going to ship. This way, SSDT can generate the migration script you need to ship to the client to pull that DB up to the current schema.

2. IF you don't have a versioned product, or your client might have altered the schema or you will need to find a way to extract the schema data on site (maybe using SSDT there) and then let SSDT create the delta.

3. Option: You can skip using the compare feature of SSDT altogether. But then you need to write your migration script yourself. For each modification to the schema, you need to write the DDL statements yourself and wrap them in if clauses that check for the old state so the changes will only be made once and if the old state exists. This way, it doesnt really matter from wich state to wich state you are going as the script will determine for each step if and what to do.

The last is the most flexible, but requires deep testing in its own and of course should have started way before the situation you are in now, where you don't know what the changes have been anymore. But it can help for next time.

This only applies to schema changes on the tables, because you can always fall back to just drop and recreate ALL stored procedures since there is nothing lost in dropping them.