What is the easiest way to publish a SQL project from VS to multiple dbs

Question

Multitenant system with several dbs sharing the same schema. Dev and test environments have multiple example tenants. Db schema maintained in SSDT/SQL Project in VS2k13.

What would be the easiest way of publishing to all DBs from within VS automatically?

Answer 1

The easiest is to create a batch file or powershell script that calls sqlpackage.exe using the /Action:Script and the dacpac which is the output from your project as the /SourceFile and each database as the /TargetConnectionString

Have one line in the batch for each database.

The recommended (my recommendation anyway) is to have a CI server like TeamCity or Tfs which monitors for check-ins and then when a check-in happens:

• Build the project
• Use sqlpackage.exe to compare the dacpac to each database / environment
• Depending on the database / environment either push all the changes out or generate a deployment script for each database.

If you publish from visual studio you don't have the strict process of:

• Develop code locally, test locally
• Check in code - merge code
• Ideally push changes to a CI database and run the unit tests / integration tests
• Generate / deploy to specific databases

Ed