Operation cannot be performed on database because it is involved in a database mirroring session or an availability group (Azure)

sql-server azure azure-sql-database database-mirroring availability
ASH picture ASH · Dec 22, 2016 · Viewed 14.7k times · Source

I am experiencing an intermittent issue with SQL Azure. I am copying a readonly SQL Azure database (V12) from one elastic pool into another. Once the database is copied, I set it to read/write using:

$"ALTER DATABASE [{databaseName}] SET READ_WRITE"

This sometimes fails with the error:

The operation cannot be performed on database *** because it is involved in a database mirroring session or an availability group

I have tried implementing retry logic with backoff but that doesn't work. I have failed to find any documentation relating to this error or to database mirroring in Azure.

Any advice would be greatly appreciated!

Answer

ASH picture ASH · Feb 3, 2017

I've managed to rectify this issue and it was indeed related to geo-replication. The database I was copying originally had a geo replica but I was dropping the secondary database (using DROP DATABASE) prior to copying the primary. The fix was to first drop the replication link between the primary and secondary before dropping the secondary database (using ALTER DATABASE [dbName] REMOVE SECONDARY ON SERVER [serverName]).

Related questions

Optimal way to concatenate/aggregate strings

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR …

Read More
How to view the roles and permissions granted to any database user in Azure SQL server instance?

Could you guide me on how to view the current roles/permissions granted to any database user in Azure SQL Database or in general for a MSSQL Server instance? I have this below query: SELECT r.name role_principal_name, …

Read More
How do I create a new user in a SQL Azure database?

I am trying to use the following template: -- ================================================= -- Create User as DBO template for SQL Azure Database -- ================================================= -- For login <login_name, sysname, login_name>, create a user in the database CREATE USER <user_…

Read More