ALTER DATABASE (Transact-SQL) 数据库镜像ALTER DATABASE (Transact-SQL) Database Mirroring

适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions) 适用于:Applies to: 是SQL ServerSQL Server(所有支持的版本)yesSQL ServerSQL Server (all supported versions)

备注

后续版本的 Microsoft SQL Server 将删除该功能。This feature will be removed in a future version of Microsoft SQL Server. 请避免在新的开发工作中使用该功能,并着手修改当前还在使用该功能的应用程序。Avoid using this feature in new development work, and plan to modify applications that currently use this feature. 改为使用 Always On 可用性组Always On availability groupsUse Always On 可用性组Always On availability groups instead.

控制数据库的数据库镜像。Controls database mirroring for a database. 使用数据库镜像选项指定的值适用于数据库的副本以及整个数据库镜像会话。Values specified with the database mirroring options apply to both copies of the database and to the database mirroring session as a whole. 每个 ALTER DATABASE 语句只允许使用一个 <database_mirroring_option>。Only one <database_mirroring_option> is permitted per ALTER DATABASE statement.

备注

我们建议在非高峰时段配置数据库镜像,因为此配置会影响性能。We recommend that you configure database mirroring during off-peak hours because configuration can affect performance.

有关 ALTER DATABASE 选项,请参阅 ALTER DATABASEFor ALTER DATABASE options, see ALTER DATABASE. 有关 ALTER DATABASE SET 选项,请参阅 ALTER DATABASE SET 选项For ALTER DATABASE SET options, see ALTER DATABASE SET Options.

主题链接图标 Transact-SQL 语法约定Topic link icon Transact-SQL Syntax Conventions

语法Syntax


ALTER DATABASE database_name
SET { <partner_option> | <witness_option> }
  <partner_option> ::=
    PARTNER { = 'partner_server'
            | FAILOVER
            | FORCE_SERVICE_ALLOW_DATA_LOSS
            | OFF
            | RESUME
            | SAFETY { FULL | OFF }
            | SUSPEND
            | TIMEOUT integer
            }
  <witness_option> ::=
    WITNESS { = 'witness_server'
            | OFF
            }
  

备注

若要查看 SQL Server 2014 及更早版本的 Transact-SQL 语法,请参阅早期版本文档To view Transact-SQL syntax for SQL Server 2014 and earlier, see Previous versions documentation.

参数Arguments

重要

SET PARTNER 或 SET WITNESS 命令在输入时可以成功完成,但随后失败。A SET PARTNER or SET WITNESS command can complete successfully when entered, but fail later.

备注

ALTER DATABASE 数据库镜像选项对于包含数据库不可用。ALTER DATABASE database mirroring options are not available for a contained database.

database_name 要修改的数据库的名称。database_name Is the name of the database to be modified.

PARTNER <partner_option> 控制用于定义数据库镜像会话的故障转移伙伴及其行为的数据库属性。PARTNER <partner_option> Controls the database properties that define the failover partners of a database mirroring session and their behavior. 有些 SET PARTNER 选项可对任一伙伴进行设置;而其他选项则仅限于对主体服务器或镜像服务器进行设置。Some SET PARTNER options can be set on either partner; others are restricted to the principal server or to the mirror server. 有关详细信息,请参阅下文所述的各个 PARTNER 选项。For more information, see the individual PARTNER options that follow. 无论在哪个伙伴上指定 SET PARTNER 子句,该子句都会影响数据库的两个副本。A SET PARTNER clause affects both copies of the database, regardless of the partner on which it is specified.

若要执行 SET PARTNER 语句,必须将两个合作伙伴的端点的 STATE 都设置为 STARTED。To execute a SET PARTNER statement, the STATE of the endpoints of both partners must be set to STARTED. 另请注意,必须将每个伙伴服务器实例的数据库镜像端点的 ROLE 设置为 PARTNER 或 ALL。Note, also, that the ROLE of the database mirroring endpoint of each partner server instance must be set to either PARTNER or ALL. 有关如何指定终结点的信息,请参阅为 Windows 身份验证创建数据库镜像终结点For information about how to specify an endpoint, see Create a Database Mirroring Endpoint for Windows Authentication. 若要了解服务器实例的数据库镜像端点的角色和状态,请使用以下 Transact-SQLTransact-SQL 语句:To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQLTransact-SQL statement:

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

<partner_option> ::=<partner_option> ::=

备注

每个 SET PARTNER 子句只允许使用一个 <partner_option>。Only one <partner_option> is permitted per SET PARTNER clause.

' partner_server ' 指定在新数据库镜像会话中用作故障转移伙伴的 SQL ServerSQL Server 实例的服务器网络地址。' partner_server ' Specifies the server network address of an instance of SQL ServerSQL Server to act as a failover partner in a new database mirroring session. 每个会话需要两个伙伴:一个作为主体服务器启动,另一个作为镜像服务器启动。Each session requires two partners: one starts as the principal server, and the other starts as the mirror server. 建议这两个伙伴驻留在不同的计算机上。We recommend that these partners reside on different computers.

在每个伙伴上,为每个会话指定一次此选项。This option is specified one time per session on each partner. 启动数据库镜像会话需要两个 ALTER DATABASE database SET PARTNER ='partner_server' 语句。Initiating a database mirroring session requires two ALTER DATABASE database SET PARTNER ='partner_server' statements. 这两个语句的顺序非常重要。Their order is significant. 首先,连接到镜像服务器,并将主体服务器实例指定为 partner_server (SET PARTNER ='principal_server')。First, connect to the mirror server, and specify the principal server instance as partner_server (SET PARTNER ='principal_server'). 然后,连接到主体服务器,并将镜像服务器实例指定为 partner_server (SET PARTNER ='mirror_server');此操作会在这两个伙伴之间启动数据库镜像会话。Second, connect to the principal server, and specify the mirror server instance as partner_server (SET PARTNER ='mirror_server'); this starts a database mirroring session between these two partners. 有关详细信息,请参阅本主题后面的设置数据库镜像For more information, see Setting Up Database Mirroring.

partner_server 的值为服务器网络地址。The value of partner_server is a server network address. 其语法如下所示:This has the following syntax:

TCP://<system-address>:<port>TCP ://<system-address>:<port>

其中where

  • <system-address> 是一个字符串,例如系统名称、完全限定的域名或 IP 地址,它们明确标识了目标计算机系统。<system-address> is a string, such as a system name, a fully qualified domain name, or an IP address, that unambiguously identifies the destination computer system.
  • <port> 是与伙伴服务器实例的镜像终结点关联的端口号。<port> is a port number that is associated with the mirroring endpoint of the partner server instance.

有关详细信息,请参阅指定服务器网络地址For more information, see Specify a Server Network Address - Database Mirroring.

以下示例阐释了 SET PARTNER ='partner_server' 子句:The following example illustrates the SET PARTNER ='partner_server' clause:

'TCP://MYSERVER.mydomain.Adventure-Works.com:7777'

重要

如果会话是使用 ALTER DATABASE 语句而不是 SQL Server Management StudioSQL Server Management Studio 设置的,则默认情况下该会话将设置为完全事务安全(SAFETY 设置为 FULL),并在无自动故障转移功能的高安全模式下运行。If a session is set up by using the ALTER DATABASE statement instead of SQL Server Management StudioSQL Server Management Studio, the session is set to full transaction safety by default (SAFETY is set to FULL) and runs in high-safety mode without automatic failover. 若要允许自动故障转移,请配置见证服务器;若要在高性能模式下运行,请关闭事务安全性 (SAFETY OFF)。To allow automatic failover, configure a witness; to run in high-performance mode, turn off transaction safety (SAFETY OFF).

FAILOVER 手动将故障从主体服务器转移到镜像服务器。FAILOVER Manually fails over the principal server to the mirror server. 只能在主体服务器上指定 FAILOVER。You can specify FAILOVER only on the principal server. 此选项仅在 SAFETY 设置为 FULL(默认设置)时有效。This option is valid only when the SAFETY setting is FULL (the default).

FAILOVER 选项需要以 master 作为数据库上下文。The FAILOVER option requires master as the database context.

FORCE_SERVICE_ALLOW_DATA_LOSS 未发生自动故障转移时,在数据库处于不同步状态或处于同步状态的情况下当主体服务器失败后,强制数据库服务转向镜像数据库。FORCE_SERVICE_ALLOW_DATA_LOSS Forces database service to the mirror database after the principal server fails with the database in an unsynchronized state or in a synchronized state when automatic failover does not occur.

强烈建议仅在主体服务器不再运行时强制运行该服务。We strongly recommend that you force service only if the principal server is no longer running. 否则,某些客户端可能会继续访问原始主体数据库而不是新的主体数据库。Otherwise, some clients might continue to access the original principal database instead of the new principal database. FORCE_SERVICE_ALLOW_DATA_LOSS 仅在镜像服务器上可用,且下列条件必须全部成立:FORCE_SERVICE_ALLOW_DATA_LOSS is available only on the mirror server and only under all the following conditions:

  • 主体服务器已关闭。The principal server is down.
  • WITNESS 设置为 OFF,或者将见证服务器连接到镜像服务器。WITNESS is set to OFF or the witness is connected to the mirror server.

仅当您愿意为立即还原数据库服务而承担部分数据丢失的风险时,才能强制服务运行。Force service only if you are willing to risk losing some data in order to restore service to the database immediately.

强制服务会挂起会话,并暂时将所有数据保留在原始的主体数据库中。Forcing service suspends the session, temporarily preserving all the data in the original principal database. 一旦原始主体服务器进入服务状态并且能够与新的主体服务器通信时,数据库管理员就可以恢复服务。Once the original principal is in service and able to communicate with the new principal server, the database administrator can resume service. 如果会话恢复,则所有未发送的日志记录和对应的更新都会丢失。When the session resumes, any unsent log records and the corresponding updates are lost.

OFF 删除数据库镜像会话,并从数据库中删除镜像。OFF Removes a database mirroring session and removes mirroring from the database. 可以在任一伙伴上指定 OFF。You can specify OFF on either partner. 有关删除镜像的影响的信息,请参阅删除数据库镜像For information, see about the impact of removing mirroring, see Removing Database Mirroring.

RESUME 恢复挂起的数据库镜像会话。RESUME Resumes a suspended database mirroring session. 只能在主体服务器上指定 RESUME。You can specify RESUME only on the principal server.

SAFETY { FULL | OFF } 设置事务安全的级别。SAFETY { FULL | OFF } Sets the level of transaction safety. 只能在主体服务器上指定 SAFETY。You can specify SAFETY only on the principal server.

默认值为 FULL。The default is FULL. 使用 Full 安全性的情况下,数据库镜像会话将同步运行(在“高安全模式”下)。With full safety, the database mirroring session runs synchronously (in high-safety mode). 如果将 SAFETY 设置为 OFF,则数据库镜像会话将异步运行(在“高性能模式”下)。If SAFETY is set to OFF, the database mirroring session runs asynchronously (in high-performance mode).

高安全模式的行为部分取决于见证服务器,如下所示:The behavior of high-safety mode depends partly on the witness, as follows:

  • 当安全性设置为 FULL 并且已为该会话设置了见证服务器时,会话将在高安全模式下运行,并且具有自动故障转移功能。When safety is set to FULL and a witness is set for the session, the session runs in high-safety mode with automatic failover. 失去主体服务器时,如果数据库被同步并且镜像服务器实例和见证服务器仍然相互连接(即它们有仲裁),则会话将自动进行故障转移。When the principal server is lost, the session automatically fails over if the database is synchronized and the mirror server instance and witness are still connected to each other (that is, they have quorum). 有关详细信息,请参阅仲裁:见证服务器如何影响数据库可用性 - 数据库镜像For more information, see Quorum: How a Witness Affects Database Availability - Database Mirroring.

    如果为会话设置了见证服务器,但是当前已断开见证服务器的连接,则镜像服务器的丢失会导致主体服务器出现故障。If a witness is set for the session but is currently disconnected, the loss of the mirror server causes the principal server to go down.

  • 当安全性设置为 FULL 并且已将见证服务器设置为 OFF 时,会话将在高安全模式下运行,但没有自动故障转移功能。When safety is set to FULL and the witness is set to OFF, the session runs in high-safety mode without automatic failover. 如果镜像服务器实例出现故障,则不会影响主体服务器实例。If the mirror server instance goes down, the principal server instance is unaffected. 如果主体服务器实例出现故障,则可以将服务(可能丢失数据)强制转到镜像服务器实例。If the principal server instance goes down, you can force service (with possible data loss) to the mirror server instance.

  • 如果将 SAFETY 设置为 OFF,则会话将在高性能模式下运行,并且不支持自动故障转移和手动故障转移。If SAFETY is set to OFF, the session runs in high-performance mode, and automatic failover and manual failover are not supported. 但是,镜像服务器的问题不会影响主体服务器,如果主体服务器实例出现故障,必要时可以将服务(可能丢失数据)强制转到镜像服务器实例(如果 WITNESS 设置为 OFF,或者见证服务器当前与镜像服务器连接)。However, problems on the mirror do not affect the principal, and if the principal server instance goes down, you can, if necessary, force service (with possible data loss) to the mirror server instance-if WITNESS is set to OFF or the witness is currently connected to the mirror. 有关强制服务的详细信息,请参阅这一部分前面的 "FORCE_SERVICE_ALLOW_DATA_LOSS"。For more information on forcing service, see "FORCE_SERVICE_ALLOW_DATA_LOSS" earlier in this section.

重要

高性能模式并非旨在使用见证服务器。High-performance mode is not intended to use a witness. 但是,我们强烈建议您只要将 SAFETY 设置为 OFF,就应确保也将 WITNESS 设置为 OFF。However, whenever you set SAFETY to OFF, we strongly recommend that you ensure that WITNESS is set to OFF.

SUSPEND 暂停数据库镜像会话。SUSPEND Pauses a database mirroring session.

可以在任一伙伴上指定 SUSPEND。You can specify SUSPEND on either partner.

TIMEOUT integer 以秒为单位指定超时期限。TIMEOUT integer Specifies the time-out period in seconds. 超时期限是指镜像会话中一个服务器实例在考虑断开另一实例的连接之前,等待接收来自该实例的 PING 消息的最长时间。The time-out period is the maximum time that a server instance waits to receive a PING message from another instance in the mirroring session before considering that other instance to be disconnected.

只能在主体服务器上指定 TIMEOUT 选项。You can specify the TIMEOUT option only on the principal server. 如果不指定此选项,则在默认情况下,超时期限为 10 秒。If you do not specify this option, by default, the time period is 10 seconds. 如果指定 5 或更高,则超时期限将设置为指定的秒数。If you specify 5 or greater, the time-out period is set to the specified number of seconds. 如果将超时值指定为 0 到 4 秒之间,则超时期限将自动设置为 5 秒。If you specify a time-out value of 0 to 4 seconds, the time-out period is automatically set to 5 seconds.

重要

我们建议您将超时期限保持为 10 秒或更长。We recommend that you keep the time-out period at 10 seconds or greater. 如果将值设置为低于 10 秒,则可能使高负荷系统丢失 PING 并声明错误故障。Setting the value to less than 10 seconds creates the possibility of a heavily loaded system missing PINGs and declaring a false failure.

有关详细信息,请参阅 Possible Failures During Database MirroringFor more information, see Possible Failures During Database Mirroring.

WITNESS <witness_option> 控制定义数据库镜像见证服务器的数据库属性。WITNESS <witness_option> Controls the database properties that define a database mirroring witness. SET WITNESS 子句会影响数据库的两个副本,但只能在主体服务器上指定 SET WITNESS。A SET WITNESS clause affects both copies of the database, but you can specify SET WITNESS only on the principal server. 如果为会话设置了见证,则无论安全设置如何,都需要仲裁来为数据库提供服务;有关更多信息,请参阅仲裁:见证服务器如何影响数据库可用性 - 数据库镜像If a witness is set for a session, quorum is required to serve the database, regardless of the SAFETY setting; for more information, see Quorum: How a Witness Affects Database Availability - Database Mirroring.

建议使见证服务器和故障转移伙伴驻留在单独服务器上。We recommend that the witness and failover partners reside on separate computers. 有关见证的信息,请参阅数据库镜像见证服务器For information about the witness, see Database Mirroring Witness.

若要执行 SET WITNESS 语句,必须将主体服务器和见证服务器实例端点的 STATE 都设置为 STARTED。To execute a SET WITNESS statement, the STATE of the endpoints of both the principal and witness server instances must be set to STARTED. 另请注意,必须将见证服务器实例的数据库镜像端点的 ROLE 设置为 WITNESS 或 ALL。Note, also, that the ROLE of the database mirroring endpoint of a witness server instance must be set to either WITNESS or ALL. 有关指定终结点的信息,请参阅数据库镜像终结点For information about specifying an endpoint, see The Database Mirroring Endpoint.

若要了解服务器实例的数据库镜像端点的角色和状态,请使用以下 Transact-SQLTransact-SQL 语句:To learn the role and state of the database mirroring endpoint of a server instance, on that instance, use the following Transact-SQLTransact-SQL statement:

SELECT role_desc, state_desc FROM sys.database_mirroring_endpoints

备注

不能在见证服务器上设置数据库属性。Database properties cannot be set on the witness.

<witness_option> ::=<witness_option> ::=

备注

每个 SET WITNESS 子句只允许使用一个 <witness_option>。Only one <witness_option> is permitted per SET WITNESS clause.

' witness_server ' 指定一个 数据库引擎Database Engine 实例,作为数据库镜像会话的见证服务器。' witness_server ' Specifies an instance of the 数据库引擎Database Engine to act as the witness server for a database mirroring session. 只能在主体服务器上指定 SET WITNESS 语句。You can specify SET WITNESS statements only on the principal server.

在 SET WITNESS ='witness_server' 语句中,witness_server 的语法与 partner_server 的语法相同 。In a SET WITNESS ='witness_server' statement, the syntax of witness_server is the same as the syntax of partner_server.

OFF 从数据库镜像会话中删除见证服务器。OFF Removes the witness from a database mirroring session. 将见证服务器设置为 OFF 会禁用自动故障转移。Setting the witness to OFF disables automatic failover. 如果数据库设置为 FULL SAFETY 并且见证服务器设置为 OFF,则镜像服务器上的故障会导致主体服务器使该数据库不可用。If the database is set to FULL SAFETY and the witness is set to OFF, a failure on the mirror server causes the principal server to make the database unavailable.

备注Remarks

示例Examples

A.A. 创建具有见证服务器的数据库镜像会话Creating a database mirroring session with a witness

设置具有见证服务器的数据库镜像会话需要配置安全性并准备镜像数据库,还需要使用 ALTER DATABASE 设置伙伴。Setting up database mirroring with a witness requires configuring security and preparing the mirror database, and also using ALTER DATABASE to set the partners. 有关完成设置过程的示例,请参阅设置数据库镜像For an example of the complete setup process, see Setting Up Database Mirroring.

B.B. 对数据库镜像会话进行手动故障转移Manually failing over a database mirroring session

可从任一数据库镜像伙伴启动手动故障转移。Manual failover can be initiated from either database mirroring partner. 进行故障转移之前,应验证您认为是当前主体服务器的服务器是否确实是主体服务器。Before failing over, you should verify that the server you believe to be the current principal server actually is the principal server. 例如,对于 AdventureWorks2012AdventureWorks2012 数据库,请在您认为是当前主体服务器的服务器实例上执行以下查询:For example, for the AdventureWorks2012AdventureWorks2012 database, on that server instance that you think is the current principal server, execute the following query:

SELECT db.name, m.mirroring_role_desc
FROM sys.database_mirroring m
JOIN sys.databases db
ON db.database_id = m.database_id
WHERE db.name = N'AdventureWorks2012';
GO

如果该服务器实例确实是主体,则 mirroring_role_desc 的值为 PrincipalIf the server instance is in fact the principal, the value of mirroring_role_desc is Principal. 如果此服务器实例是镜像服务器,则 SELECT 语句将返回 MirrorIf this server instance were the mirror server, the SELECT statement would return Mirror.

下面的示例假定该服务器是当前主体。The following example assumes that the server is the current principal.

  1. 将故障手动转移到数据库镜像伙伴:Manually fail over to the database mirroring partner:

    ALTER DATABASE AdventureWorks2012 SET PARTNER FAILOVER;
    GO
    
  2. 若要验证新镜像上的故障转移的结果,请执行以下查询:To verify the results of the failover on the new mirror, execute the following query:

    SELECT db.name, m.mirroring_role_desc
    FROM sys.database_mirroring m
    JOIN sys.databases db
    ON db.database_id = m.database_id
    WHERE db.name = N'AdventureWorks2012';
    GO
    

现在,mirroring_role_desc 的当前值为 MirrorThe current value of mirroring_role_desc is now Mirror.

另请参阅See Also