I’m looking to use Azure DevOps to deploy a database via dacpac file to an Azure SQL Managed Instance. We have multiple of the same databases all hosted on the same SQL MI, but for different environemnts. E.g. we could have two environments (Env1 & Env2), and two databases (DB1 & DB2) – in the same SQL MI, we then have the following Managed SQL DB’s; Env1_DB1, Env1_DB2, Env2_DB1 & Env2_DB2.

The issue is that inside one of the databases, there is a stored proc which references the other db. So when we deploy the database using the dacpac file, we need to use some sort of custom variable to pass into the pipeline, which would then be used to update all references to other db’s. E.g. following on from the above example, we could do with passing in a variable of either ‘Env1’ or ‘Env2’, which then gets used during (or post deployment) of the dacpac to update the stored proc reference to the correct environemnt.

We’re using SQLPackage to do the dacpac deployment in AZDO, and i can see from the documentation that you can pass in ‘/Variables’ to add custom variables – although im not sure how these could then get used in the dacpac deployment to change the db name prefix (or if thats even the right approach??) https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish?view=sql-server-ver16#parameters-for-the-publish-action

Update
Under my dacpac project, i have a ‘Stored Procedures’ folder – in there, let’s call the procedure ‘myStoredProc.sql’. Now this dacpac project is for a database called ‘Users’ (when in production), and so ‘myStoredProc’ is part of this database. However, ‘myStoredProc’ has a reference to another db called ‘Logs’, something like..

Insert [Logs].[dbo].[Audit] Select ... 

In production, this if fine, as the ‘Logs’ db is always called ‘Logs’, however in our development environment, we have multiple ‘Logs’ databases for each environment (and the same for this ‘Users’ db too), on the same Azure SQL MI. e.g. we have ‘env1_Logs’, ‘env2_Logs’, … as well as ‘env1_Users’, ‘env2_Users’, …

In our Azure DevOps pipeline to deploy the dacpac, we have the following

$(Build.SourcesDirectory)/sqlpackage/sqlpackage /Action:Publish /SourceFile:"$(Build.SourcesDirectory)/sqlpackage/${{ variables.LOCAL_FILE_PATH }}" /TargetConnectionString:"Server=${{ parameters.azureSqlManagedInstance }};Initial Catalog=${{ parameters.initialCatalog }};Persist Security Info=False;User ID=$(AZURE-DATABASE-USER-DEV);Password=$(PASSWORD);MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=True;Connection Timeout=30;"

Question: How can I make my dacpac deployment dynamic for the references to the ‘Logs’ db? i.e. pass in some parameter (like ‘env1_’ or ‘env2_’) so that it updates the stored proc referenece to ‘Logs’ db – such that, if i passed in the parameter ‘env1_’, my stored proc reference to ‘Logs’ db would become

Insert [env1_Logs].[dbo].[Audit] Select ...