Azure DevOps - Multitenant database deployment

Question

We have Web application with one code and multitenant database behind and we are using Azure Devops CI/CD for code deployment. We are able to release the code to database perfectly, however we want to deploy it to multiple database without multiple release pipelines (number of tenants is dynamical and 100+ of them).

I am able to deploy the code to one database and then redeploy to each other using sqlpackage.exe and dacpac file publish in command line on target server. But I want to do so just in Azure devops, however there is no sqlpackage.exe on agent machine in command line task.

Is here someone who was dealing with similar issue with multi-tenant DB approach? I am open to whatever solution using Azure release pipelines.

score 0 · Accepted Answer · edited Dec 16 '21 at 13:07

I was able to resolve this by running Inline Powershell and SqlPackage.exe on Deployment group. You just need to have one "Template DB", get dacpac or bacpac file file from it and then publish it to all other DBs.

Example for a reference:

ForEach ($a in (Get-Content "E:\DatabaseSync\databaselist.txt"))
{
  SqlPackage.exe /a:Publish /sf:"E:\DatabaseSync\TemplateDB.dacpac" `
/tsn:localhost /tu:username /tp:password /tdn:$a `
/p:TreatVerificationErrorsAsWarnings=False /p:BlockOnPossibleDataLoss=True `
/p:AllowDropBlockingAssemblies=True /p:DropObjectsNotInSource=True `
/p:DropPermissionsNotInSource=False /p:IgnorePermissions=True `
/p:IgnoreRoleMembership=True /p:IgnoreUserSettingsObjects=True `
/p:DoNotDropObjectTypes=Users /p:ExcludeObjectTypes=Users
}

Azure DevOps - Multitenant database deployment

1 Answers1