I have built a database project within SSDT for Visual Studio 2017. I have also created Publish profiles that succesfully deploy to our dev, QA, staging, and production databases with no issue, including proper Pre/PostDeployment scripts.

The issue that I now face is that I need to "dynamically" publish the database project to 300+ databases. By dynamically, I mean that each of the 300+ databases are relatively out of sync. For instance, I have objects that I want to drop in database ABC that might not necessarily be in XYZ. I'll also have objects in XYZ that I want to drop that aren't in ABC.

The objects I want to keep are all in source control, so I am using the Publish option to Drop objects in target but not in source. There might be table definitions that slightly differ from one database to the next, especially with regards to indexes and constraints, or temporary backup tables that were created during account specific investigations that should be dropped.

Because of these inconsistencies across the schemas, an approach to build the project once and deploy that build script to all databases won't work as it might miss objects that need to be dropped or recreated in database XYZ. The goal is to essentially sync all of these databases to the same schema.

I have read a few articles that delve into utilizing batch scripts, but they all seem to cover the case of building the database build script once and deploying that single script across all databases.

I have not yet found a method that will build and publish the project for each of the 300+ databases individually. I understand that this is likely to be a rather long deployment process, but given the inconsistencies throughout all the schemas due to mixed deployment procedures prior to my coming on with the company, we need to get the schema to a state that is consistent across the board.

We are using Visual Studio Team Services for our TFS repository, so I believe there may also be an option to create a Build Definition workflow in this scope. But alas, there seems to be relatively minimal information on this use case. PowerShell calls to SQLCMD might also be another option, but I have little to zero experience with this.

  • 2
    Have you looked at sqlpackage.exe? You should be able to sync with the dacpac as the source and physical db as the target. For many databases, you could orchestarate with Powershell. Note that refactorings, like renaming columns could result in data loss unless you handle that in your pre deployment script. – Dan Guzman Sep 27 '17 at 17:39
  • @DanGuzman I looked at it briefly before, but I took a plunge into it further. It led me to reading further into VSTS Build Definitions, and it appears as though I can create a pipeline for deployment to N number of databases that follow a pattern. I'll need to read more up on it, but it seems to be exactly what we need/intend for. And I've also got the refactoring considerations handled. Thanks for the tip! – PicoDeGallo Sep 28 '17 at 17:57
  • Glad my suggestion got you going down the right path, Please detail your VSTS solution as an answer, I've used VSTS only for straightforward DEV/QA database deployments but nothing beyond that. – Dan Guzman Sep 29 '17 at 10:58

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.