从可用性组中删除辅助数据库 (SQL Server)Remove a Secondary Database from an Availability Group (SQL Server)

适用对象: yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

本主题说明如何通过在 SQL Server Management StudioSQL Server Management Studio中使用 Transact-SQLTransact-SQLSQL Server 2017SQL Server 2017或 PowerShell 从 AlwaysOn 可用性组中删除辅助数据库。This topic describes how to remove a secondary database from an Always On availability group by using SQL Server Management StudioSQL Server Management Studio, Transact-SQLTransact-SQL, or PowerShell in SQL Server 2017SQL Server 2017.

先决条件和限制Prerequisites and Restrictions

  • 只有辅助副本支持该任务。This task is supported only on secondary replicas. 您必须连接到承载要从中删除数据库的辅助副本的服务器实例。You must be connected to the server instance that hosts the secondary replica from which the database is to be removed.

权限Permissions

需要对数据库拥有 ALTER 权限。Requires ALTER permission on the database.

使用 SQL Server Management StudioUsing SQL Server Management Studio

从可用性组中删除辅助数据库To remove a secondary database from an availability group

  1. 在对象资源管理器中,连接到承载您要从中删除一个或多个辅助数据库的辅助副本的服务器实例,然后展开服务器树。In Object Explorer, connect to the server instance that hosts the secondary replica from which you want to remove one or more secondary databases, and expand the server tree.

  2. 依次展开“Always On 高可用性” 节点和“可用性组” 节点。Expand the Always On High Availability node and the Availability Groups node.

  3. 选择可用性组,然后展开 “可用性数据库” 节点。Select the availability group, and expand the Availability Databases node.

  4. 此步骤取决于您是要删除多个数据库组,还是只删除一个数据库,如下所示:This step depends on whether you want to remove multiple databases groups or only one database, as follows:

  5. 右键单击选定的一个或多个数据库,然后在命令菜单中选择“删除辅助数据库” 。Right-click the selected database or databases, and select Remove Secondary Database in the command menu.

  6. “从可用性组删除数据库” 对话框中,要删除所有列出的数据库,则单击 “确定”In the Remove Database from Availability Group dialog box, to remove all the listed databases, click OK. 如果您不想删除所有列出的数据库,请单击 “取消”If you do not want to remove all the listed databases, click Cancel.

使用 Transact-SQLUsing Transact-SQL

从可用性组中删除辅助数据库To remove a secondary database from an availability group

  1. 连接到承载辅助副本的服务器实例。Connect to the server instance that hosts the secondary replica.

  2. 使用 ALTER DATABASE 语句的 SET HADR 子句 ,如下所述:Use the SET HADR clause of the ALTER DATABASE statement, as follows:

    ALTER DATABASE database_name SET HADR OFFALTER DATABASE database_name SET HADR OFF

    其中, database_name 为要从其所属的可用性组中删除的辅助数据库的名称。where database_name is the name of a secondary database to be removed from the availability group to which it belongs.

    下面的示例将本地辅助数据库 MyDb2 从其可用性组中删除。The following example removes the local secondary database MyDb2 from its availability group.

    ALTER DATABASE MyDb2 SET HADR OFF;  
    GO  
    

使用 PowerShellUsing PowerShell

从可用性组中删除辅助数据库To remove a secondary database from an availability group

  1. 将目录 (cd) 更改为托管辅助副本的服务器实例。Change directory (cd) to the server instance that hosts the secondary replica.

  2. 使用 Remove-SqlAvailabilityDatabase cmdlet,指定要从可用性组中删除的可用性数据库的名称。Use the Remove-SqlAvailabilityDatabase cmdlet, specifying the name of the availability database to be removed from the availability group. 当您连接到承载辅助副本的服务器实例时,只能从可用性组中删除本地辅助数据库。When you are connected to a server instance that hosts a secondary replica, only the local secondary database is removed from the availability group.

    例如,下面的命令从名为 MyDb8 的服务器实例承载的次要副本中删除辅助数据库 SecondaryComputer\InstanceFor example, the following command removes the secondary database MyDb8 from the secondary replica hosted by the server instance named SecondaryComputer\Instance. 与已删除的辅助数据库的数据同步将停止。Data synchronization to the removed secondary databases ceases. 此命令将不会影响主数据库或任何其他辅助数据库。This command does not affect the primary database or any other secondary databases.

    Remove-SqlAvailabilityDatabase `  
    -Path SQLSERVER:\Sql\SecondaryComputer\InstanceName\AvailabilityGroups\MyAg\Databases\MyDb8  
    

    备注

    若要查看 cmdlet 的语法,请在 PowerShell 环境中使用 Get-Help SQL ServerSQL Server cmdlet。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL ServerSQL Server PowerShell environment. 有关详细信息,请参阅 Get Help SQL Server PowerShellFor more information, see Get Help SQL Server PowerShell.

设置和使用 SQL Server PowerShell 提供程序To set up and use the SQL Server PowerShell provider

跟进:从可用性组中删除辅助数据库之后Follow Up: After Removing a Secondary Database from an Availability Group

删除辅助数据库之后,它不再加入到可用性组中,有关删除的辅助数据库的所有信息都会被可用性组丢弃。When a secondary database is removed, it is no longer joined to the availability group and all information about the removed secondary database is discarded by the availability group. 删除的辅助数据库处于 RESTORING 状态。The removed secondary database is placed in the RESTORING state.

提示

在删除辅助数据库后的较短时间中,你可能能够通过将其重新联接到可用性组,在数据库上重新启动 AlwaysOn 数据同步。For a short time after removing a secondary database, you might be able to restart Always On data synchronization on the database by re-joining it to the availability group. 有关详细信息,请参阅 将辅助数据库联接到可用性组 (SQL Server)For more information, see Join a Secondary Database to an Availability Group (SQL Server).

此时,可以通过多种备选方法处理删除的辅助数据库:At this point there are alternative ways of dealing with a removed secondary database:

  • 如果不再需要该辅助数据库,则可以将其删除。If you no longer need the secondary database, you can drop it.

    有关详细信息,请参阅 DROP DATABASE (Transact SQL)删除数据库For more information, see DROP DATABASE (Transact-SQL) or Delete a Database.

  • 如果当辅助数据库已从可用性组中删除后要访问它,则可以恢复此数据库。If you want to access a removed secondary database after it has been removed from the availability group, you can recover the database. 但是,如果恢复删除的辅助数据库,则会有两个同名的、独立但不同的数据库处于联机状态。However, if you recover a removed secondary database, two divergent, independent databases that have the same name are online. 您必须确保客户端仅可访问当前主数据库。You must make sure that clients can access only the current primary database.

    有关详细信息,请参阅恢复数据库而不还原数据 (Transact-SQL)For more information, see Recover a Database Without Restoring Data (Transact-SQL).

另请参阅See Also

AlwaysOn 可用性组概述 (SQL Server) Overview of Always On Availability Groups (SQL Server)
将主数据库从可用性组删除 (SQL Server)Remove a Primary Database from an Availability Group (SQL Server)