Azure SQL database copy with active replica

Question

I am experiencing an issue when making a copy of our Azure SQL Database. The database that we are copying has an online replica as part of Geo-Replication that's available and configured through the Azure Portal.

We make a copy of the primary data onto a different server using the Azure Portal copy button, but our database copy still thinks its part of a replica set.

For example, I run the following SQL:

ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

The response I get is:

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. Msg 5069, Level 16, State 1, Line 1 ALTER DATABASE statement failed.

I have tried to run:

ALTER DATABASE <DatabaseName> SET PARTNER OFF;

This is not supported as part of azure-sql so I get a response of:

Keyword or statement option 'PARTNER' is not supported in this version of SQL Server.

The Azure portal for the database copy says that "Geo-Replication is not configured"

Can anyone point me in the right direction of how to tell the database copy it no longer part of any replication?

David Browne - Microsoft David Browne - Microsoft · Accepted Answer · 2018-12-07T17:07:40

This

alter database current set single_user with rollback immediate

Always fails in Azure SQL Database with:

Msg 1468, Level 16, State 1, Line 3 The operation cannot be performed on database "xxxx" because it is involved in a database mirroring session or an availability group.

You'll see that single_user isn't in the supported syntax for Azure SQL Database's ALTER DATABASE. Instead you can set RESTRICTED_USER:

<db_update_option> ::=  
  { READ_ONLY | READ_WRITE }  

<db_user_access_option> ::=  
  { RESTRICTED_USER | MULTI_USER }

RESTRICTED_USER

RESTRICTED_USER allows for only members of the db_owner fixed database role and dbcreator and sysadmin fixed server roles to connect to the database, but does not limit their number. All connections to the database are disconnected in the timeframe specified by the termination clause of the ALTER DATABASE statement. After the database has transitioned to the RESTRICTED_USER state, connection attempts by unqualified users are refused. RESTRICTED_USER cannot be modified with SQL Database Managed instance.

Without the ability to connect to Master and see and possibly kill sessions connected to user databases, setting a database to SINGLE_USER could be bad.

Azure SQL database copy with active replica

3 Answers