CODEWITHSUNDEEP
sql-serverazureazure-sql-database
robsta
robsta

Reputation: 426

Azure SQL database copy with active replica

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?

Upvotes: 2

Views: 1741

Answers (3)

David Browne - Microsoft
David Browne - Microsoft

Reputation: 88852

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.

Upvotes: 2

Alberto Morillo
Alberto Morillo

Reputation: 15608

Adding to Conor answer, you can also try to export the database as a bacpac and then import it as a new database. Here you will find how to export the database to an Azure storage account.

Upvotes: 1

Conor Cunningham MSFT
Conor Cunningham MSFT

Reputation: 4481

I will ask the engineering team. It might be that during the copy it is conceptually part of the replica set until the copy completes.

Your alternative, which should work without any issues like what you are seeing to get you unblocked, is to restore a backup from "now" which will make a new copy of the database for you from the most recent backup (it does differential backups so you get all the changes up to the point in time you specify). Give that a try and see if that gets you what you need.

Upvotes: 0

Related Questions