Deploying a SQL Server 2012 DACPAC to mirrored database

Question

We are using TFS 2015 Release Management to deploy updates to various environments, and one of the release steps is to use the "SQL Server Database Deployment" task, with a Publish Profile, to apply a DACPAC to the target SQL Server 2012 database for the environment.

In each of our environments we have a mirrored pair of database servers, with one "primary", and the other "mirror", at any one time.

The GUI within Visual Studio for managing the Publish Profiles doesn't seem to provide for "failover partner", so I manually edited the XML file for the Publish Profile to add the "failover partner" element to the connection string.

However, it seems that the Publish Profile approach does not support mirrored database connection strings, so when our database is Primary on the node identified as the "failover partner" in the connection string, the DACPAC deployment (which uses sqlpackage.exe) fails with the error: "Cannot open database "DatabaseName" requested by the login". We have worked out that it is ignoring the "failover partner" and is simply trying to connect to the non-active node.

I have also tried running the deploy from within Visual Studio 2015, using the Publish Profile, and the same error occurred when the database was on the node identified as the failover partner.

So my question is, how can we use a DACPAC with a Publish Profile and sqlpackage.exe to update the appropriate database at deploy-time, where the database may be primary on either of the nodes in a mirrored pair?

Answer 1

It turns out the error message:

The operation cannot be performed on database "..." because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group

is due to attempts to alter database properties and not specifically related to dacpac deployments. Mismatched project settings that result in an ALTER DATABASE statement will cause that error during deployments. In our case, the option in the publish profile to Deploy database properties combined with a SQL project Recovery Database setting that differed from the target database resulted in an ALTER DATABASE [$(database)] SET RECOVERY SIMPLE... statement, which in turn would not deploy unless mirroring was disabled.

Try changing the SQL project properties to match the target database, or choose not to deploy database properties using the following flag: /p:ScriptDatabaseOptions=False or from the UI: