sql-server - 具有事件副本的 Azure SQL 数据库副本

Question

我在复制 Azure SQL 数据库时遇到问题。我们正在复制的数据库有一个在线副本，作为异地复制的一部分，可以通过 Azure 门户进行访问和配置。

我们使用 Azure 门户复制按钮将主数据复制到不同的服务器上，但我们的数据库副本仍然认为它是副本集的一部分。

例如，我运行以下 SQL:

ALTER DATABASE <DatabaseName> SET SINGLE_USER WITH ROLLBACK IMMEDIATE

我得到的回复是:

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.

我尝试过运行:

ALTER DATABASE <DatabaseName> SET PARTNER OFF; 

这作为 azure-sql 的一部分不受支持，因此我得到的响应是:

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

数据库副本的 Azure 门户显示“异地复制未配置”

任何人都可以为我指明如何告诉数据库副本它不再是任何复制的一部分的正确方向吗？

Answer 1

这个

alter database current set single_user with rollback immediate

在 Azure SQL 数据库中始终失败:

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.

您将看到 Azure SQL 数据库 ALTER DATABASE 支持的语法中不包含 single_user 。相反，您可以设置 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.

如果无法连接到 Master 并查看并可能终止连接到用户数据库的 session ，将数据库设置为 SINGLE_USER 可能会不好。