从 Always On 可用性组中删除主数据库Remove a primary database from an Always On availability group

适用对象: 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 StudioTransact-SQLTransact-SQLSQL Server 2017SQL Server 2017中的 PowerShell 从 Always On 可用性组中删除主数据库和对应的辅助数据库。This topic describes how to remove both the primary database and the corresponding secondary database(s) 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 primary replicas. 您必须连接到承载主副本的服务器实例。You must be connected to the server instance that hosts the primary replica.

权限Permissions

对可用性组要求 ALTER AVAILABILITY GROUP 权限、CONTROL AVAILABILITY GROUP 权限、ALTER ANY AVAILABILITY GROUP 权限或 CONTROL SERVER 权限。Requires ALTER AVAILABILITY GROUP permission on the availability group, CONTROL AVAILABILITY GROUP permission, ALTER ANY AVAILABILITY GROUP permission, or CONTROL SERVER permission.

使用 SQL Server Management StudioUsing SQL Server Management Studio

删除可用性数据库To remove an availability database

  1. 在对象资源管理器中,连接到承载要删除的一个或多个数据库的主副本的服务器实例,然后展开服务器树。In Object Explorer, connect to the server instance that hosts the primary replica of the database or databases to be removed, 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 Database from Availability Group in the command menu.

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

使用 Transact-SQLUsing Transact-SQL

删除可用性数据库To remove an availability database

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

  2. 按如下所示使用 ALTER AVAILABILITY GROUP 语句:Use the ALTER AVAILABILITY GROUP statement, as follows:

    ALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_nameALTER AVAILABILITY GROUP group_name REMOVE DATABASE availability_database_name

    其中, group_name 是可用性组的名称, database_name 是要删除的数据库的名称。where group_name is the name of the availability group and database_name is the name of the database to be removed.

    下面的示例将从 Db6 可用性组中删除名为 MyAG 的数据库。The following example removes a databases named Db6 from the MyAG availability group.

    ALTER AVAILABILITY GROUP MyAG REMOVE DATABASE Db6;  
    

使用 PowerShellUsing PowerShell

删除可用性数据库To remove an availability database

  1. 将目录 (cd) 更改为托管主副本的服务器实例。Change directory (cd) to the server instance that hosts the primary 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 the server instance that hosts the primary replica, the primary database and its corresponding secondary databases are all removed from the availability group.

    例如,下面的命令从名为 MyDb9 的可用性组中删除可用性数据库 MyAgFor example, the following command removes the availability database MyDb9 from the availability group named MyAg. 因为此命令在承载主副本的服务器实例上执行,所以,主数据库及其对应的所有辅助数据库都将从可用性组中删除。Because the command is executed on the server instance that hosts the primary replica, the primary database and all its corresponding secondary databases are removed from the availability group. 在任何辅助副本上都不会出现针对此数据库的数据同步。Data synchronization will no longer occur for this database on any secondary replica.

    Remove-SqlAvailabilityDatabase `   
    -Path SQLSERVER:\Sql\PrimaryComputer\InstanceName\AvailabilityGroups\MyAg\AvailabilityDatabases\MyDb9
    

    备注

    若要查看 cmdlet 的语法,请在 PowerShell 环境中使用 Get-Help SQL Server 2017SQL Server 2017 cmdlet。To view the syntax of a cmdlet, use the Get-Help cmdlet in the SQL Server 2017SQL Server 2017 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 an Availability Database from an Availability Group

从其可用性组中删除可用性数据库后,将结束先前主数据库与对应的辅助数据库之间的数据同步。Removing an availability database from its availability group ends data synchronization between the former primary database and the corresponding secondary databases. 以前的主数据库保持联机状态。The former primary database remains online. 每个对应的辅助数据库都处于 RESTORING 状态。Every corresponding secondary database is placed in the RESTORING state.

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

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

    有关详细信息,请参阅 删除数据库For more information, see 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 one of them, typically the most recent 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 Secondary Database from an Availability Group (SQL Server)