数据库镜像配置故障排除 (SQL Server)Troubleshoot Database Mirroring Configuration (SQL Server)

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

本主题提供有关信息以帮助您解决设置数据库镜像会话时遇到的问题。This topic provides information to help you troubleshoot problems in setting up a database mirroring session.

备注

请确保满足所有 数据库镜像的先决条件Ensure that you are meeting all the prerequisites for database mirroring.

问题Issue 总结Summary
错误消息 1418Error Message 1418 SQL ServerSQL Server 消息指示无法到达服务器网络地址或该地址不存在,同时建议您确认网络地址名称并重新发出命令。This SQL ServerSQL Server message indicates that the server network address cannot be reached or does not exist, and it suggests that you verify the network address name and reissue the command.
帐户Accounts 介绍了正确配置运行 SQL ServerSQL Server 所用的帐户的相关要求。Discusses requirements for correctly configuring the accounts under which SQL ServerSQL Server is running.
端点Endpoints 介绍了正确配置每个服务器实例的数据库镜像端点的要求。Discusses requirements for correctly configuring the database mirroring endpoint of each server instance.
SystemAddressSystemAddress 概述了在数据库镜像配置中指定服务器实例的系统名称的备选方法。Summarizes the alternatives for specifying the system name of a server instance in a database mirroring configuration.
网络访问Network access 记录了允许每个服务器实例通过 TCP 访问其他一个或多个服务器实例的端口的要求。Documents the requirement that each the server instance be able to access the ports of the other server instance or instances over TCP.
镜像数据库准备Mirror database preparation 概述了准备镜像数据库以开始镜像的要求。Summarizes the requirements for preparing the mirror database to enable mirroring to start.
失败的创建文件操作Failed create-file operation 说明如何响应失败的创建文件操作。Describes how to respond to a failed create-file operation.
使用 Transact-SQL 开始镜像Starting mirroring by Using Transact-SQL 说明 ALTER DATABASE database_name SET PARTNER =' partner_server ' 语句所需的顺序。Describes the required order for ALTER DATABASE database_name SET PARTNER ='partner_server' statements.
跨数据库事务Cross-Database Transactions 自动故障转移可能导致自动不正确地解决有疑问的事务。An automatic failover could lead to automatic and possibly incorrect resolution of in-doubt transactions. 因此,数据库镜像不支持跨数据库事务。For this reason database mirroring does not support cross-database transactions.

帐户Accounts

必须正确配置运行 SQL ServerSQL Server 所用的帐户。The accounts under which SQL ServerSQL Server is running must be correctly configured.

  1. 帐户是否具有正确的权限?Do the accounts have the correct permissions?

    1. 如果这些帐户在相同的域帐户中运行,则会减少配置错误的几率。If the accounts are running in the same domain accounts, the chances of misconfiguration are reduced.

    2. 如果这些帐户在不同的域中运行或不属于域帐户,则必须在其他计算机的 master 中创建一个登录帐户,并且必须授予该登录帐户端点的 CONNECT 权限。If the accounts are running in different domains or are not domain accounts, the login of one account must be created in master on the other computer, and that login must be granted CONNECT permissions on the endpoint. 有关详细信息,请参阅 当数据库在其他服务器实例上可用时管理元数据 (SQL Server)For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server). 这包括网络服务帐户。This includes the Network Service account.

  2. 如果使用本地系统帐户将 SQL ServerSQL Server 作为服务运行,则必须使用证书进行身份验证。If SQL ServerSQL Server is running as a service that is using the local system account, you must use certificates for authentication. 有关详细信息,请参阅使用数据库镜像终结点证书 (Transact-SQL)For more information, see Use Certificates for a Database Mirroring Endpoint (Transact-SQL).

EndpointsEndpoints

必须正确配置端点。Endpoints must be correctly configured.

  1. 确保每个服务器实例(主体服务器、镜像服务器和见证服务器,如果有的话)都有数据库镜像端点。Make sure that each server instance (the principal server, mirror server, and witness, if any) has a database mirroring endpoint. 有关详细信息,请参阅 sys.database_mirroring_endpoints (Transact SQL),并根据身份验证的形式,参阅为 Windows 身份验证创建数据库镜像终结点 (Transact SQL)使用数据库镜像终结点证书 (Transact SQ)For more information, see sys.database_mirroring_endpoints (Transact-SQL) and, depending on the form of authentication, either Create a Database Mirroring Endpoint for Windows Authentication (Transact-SQL) or Use Certificates for a Database Mirroring Endpoint (Transact-SQL).

  2. 检查端口号是否正确。Check that the port numbers are correct.

    若要标识当前与服务器实例的数据库镜像终结点关联的端口,请使用 sys.database_mirroring_endpointssys.tcp_endpoints 目录视图。To identify the port currently associated with database mirroring endpoint of a server instance, use the sys.database_mirroring_endpoints and sys.tcp_endpoints catalog views.

  3. 对于难以解释的数据库镜像设置问题,建议您检查每个服务器实例以确定它是否正在侦听相应的端口。For database mirroring setup issues that are difficult to explain, we recommend that you inspect each server instance to determine whether it is listening on the correct ports.

  4. 确保已启动端点 (STATE = STARTED)。Make sure that the endpoints are started (STATE=STARTED). 对于各个服务器实例,使用以下 Transact-SQLTransact-SQL 语句。On each server instance, use the following Transact-SQLTransact-SQL statement.

    SELECT state_desc FROM sys.database_mirroring_endpoints  
    

    有关 state_desc 列的详细信息,请参阅 sys.database_mirroring_endpoints (Transact-SQL)For more information about the state_desc column, see sys.database_mirroring_endpoints (Transact-SQL).

    若要启动端点,请使用以下 Transact-SQLTransact-SQL 语句。To start an endpoint, use the following Transact-SQLTransact-SQL statement.

    ALTER ENDPOINT Endpoint_Mirroring   
    STATE = STARTED   
    AS TCP (LISTENER_PORT = <port_number>)  
    FOR database_mirroring (ROLE = ALL);  
    GO  
    

    有关详细信息,请参阅 ALTER ENDPOINT (Transact-SQL)For more information, see ALTER ENDPOINT (Transact-SQL).

  5. 检查 ROLE 是否正确。Check that the ROLE is correct. 对每个服务器实例使用以下 Transact-SQLTransact-SQL 语句。On each server instance use the following Transact-SQLTransact-SQL statement.

    SELECT role FROM sys.database_mirroring_endpoints;  
    GO  
    

    有关详细信息,请参阅 sys.database_mirroring_endpoints (Transact-SQL)For more information, see sys.database_mirroring_endpoints (Transact-SQL).

  6. 从其他服务器实例的服务帐户登录需要 CONNECT 权限。The login for the service account from the other server instance requires CONNECT permission. 确保其他服务器的登录帐户具有 CONNECT 权限。Make sure that the login from the other server has CONNECT permission. 若要确定哪个登录帐户拥有对端点的 CONNECT 权限,请对每个服务器实例使用以下 Transact-SQLTransact-SQL 语句。To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-SQLTransact-SQL statement.

    SELECT 'Metadata Check';  
    SELECT EP.name, SP.STATE,   
       CONVERT(nvarchar(38), suser_name(SP.grantor_principal_id))   
          AS GRANTOR,   
       SP.TYPE AS PERMISSION,  
       CONVERT(nvarchar(46),suser_name(SP.grantee_principal_id))   
          AS GRANTEE   
       FROM sys.server_permissions SP , sys.endpoints EP  
       WHERE SP.major_id = EP.endpoint_id  
       ORDER BY Permission,grantor, grantee;   
    GO  
    
    

系统地址System Address

对于数据库镜像配置中服务器实例的系统名称,可以使用明确标识系统的任何名称。For the system name of a server instance in a database mirroring configuration, you can use any name that unambiguously identifies the system. 服务器地址可以是系统名称(如果各系统都在同一个域中)、完全限定域名或 IP 地址(最好是静态 IP 地址)。The server address can be a system name (if the systems are in the same domain), a fully qualified domain name, or an IP address (preferably, a static IP address). 保证使用完全限定域名的有效性。Using the fully qualified domain name is guaranteed to work. 有关详细信息,请参阅 指定服务器网络地址(数据库镜像)For more information, see Specify a Server Network Address (Database Mirroring).

Network AccessNetwork Access

必须允许每个服务器实例都能通过 TCP 访问其他一个或多个服务器实例的端口。Each server instance must be able to access the ports of the other server instance or instances over TCP. 当服务器实例位于相互不信任的不同域(不可信的域)中时,这尤为重要。This is especially important if the server instances are in different domains that do not trust each other (untrusted domains). 这会限制服务器实例之间大部分的通信。This restricts much of the communication between the server instances.

Mirror Database PreparationMirror Database Preparation

无论是首次开始镜像还是在删除镜像后再次开始镜像,都要验证镜像数据库是否可以进行镜像。Whether starting mirroring for the first time or starting it again after mirroring was removed, verify that the mirror database is prepared for mirroring.

在镜像服务器上创建镜像数据库时,请确保指定相同数据库名称 WITH NORECOVERY 来还原主体数据库备份。When you create the mirror database on the mirror server, make sure that you restore the backup of the principal database specifying the same database name WITH NORECOVERY. 此外,还必须再次使用 WITH NORECOVERY 应用进行该备份之后创建的所有日志备份。Also, all log backups created after that backup was taken must also be applied, again WITH NORECOVERY.

另外,我们建议,如有可能,镜像数据库的文件路径(包括驱动器号)应该与主体数据库的路径相同。Also, we recommend that, if it is possible, the file path (including the drive letter) of the mirror database be identical to the path of the principal database. 如果文件路径必须互不相同(例如,如果主体数据库位于“F:”驱动器上,但镜像系统没有“F:”驱动器),则必须在 RESTORE 语句中加入 MOVE 选项。If the file paths must differ, for example, if the principal database is on drive 'F:' but the mirror system lacks an F: drive, you must include the MOVE option in the RESTORE statement.

重要

如果在创建镜像数据库时移动了数据库文件,则可能导致以后不挂起镜像就无法向数据库添加文件。If you move the database files when you are creating the mirror database, you might be unable to add files to the database later without mirroring being suspended.

如果数据库镜像已经停止,则必须将对主体数据库执行的所有后续日志备份应用到镜像数据库中,然后才可以重新启动镜像。If database mirroring has been stopped, all subsequent log backups taken on the principal database must be applied to the mirror database before mirroring can be restarted.

有关详细信息,请参阅 为镜像准备镜像数据库 (SQL Server)的各版本中均未提供见证服务器实例。For more information, see Prepare a Mirror Database for Mirroring (SQL Server).

Failed Create-File OperationFailed Create-File Operation

在不影响镜像会话的情况下添加文件要求该文件路径同时存在于两个服务器上。Adding a file without impacting a mirroring session requires that the path of the file exist on both servers. 因此,如果在创建镜像数据库时移动了数据库文件,则随后在镜像数据库上的添加文件操作可能会失败,并可能会导致镜像挂起。Therefore, if you move the database files when creating the mirror database, a later add-file operation might fail on the mirror database and cause mirroring to be suspended.

修复此问题:To fix the problem:

  1. 数据库所有者必须删除此镜像会话,并还原包含所添加文件的文件组的完整备份。The database owner must remove the mirroring session and restore a full backup of the filegroup that contains the added file.

  2. 然后,所有者必须备份主体服务器上包含添加文件操作的日志,并使用 WITH NORECOVERY 和 WITH MOVE 选项在镜像数据库上手动还原此日志备份。The owner must then back up the log containing the add-file operation on the principal server and manually restore the log backup on the mirror database using the WITH NORECOVERY and WITH MOVE options. 执行此操作可在镜像服务器上创建指定的文件路径,并将相应的新文件还原到该位置。Doing this creates the specified file path on the mirror server and restores the new file to that location.

  3. 若要准备数据库以进行新的镜像会话,数据库所有者还必须使用 WITH NO RECOVERY 选项还原主体服务器上所有其他未完成的日志备份。To prepare the database for a new mirroring session, the owner must also restore WITH NO RECOVERY any other outstanding log backups from the principal server.

有关详细信息,请参阅删除数据库镜像 (SQL Server)为镜像准备镜像数据库 (SQL Server)使用 Windows 身份验证建立数据库镜像会话 (Transact-SQL)使用数据库镜像端点证书 (Transact-SQL),或使用 Windows 身份验证建立数据库镜像会话 (SQL Server Management Studio)For more information, see Removing Database Mirroring (SQL Server), Prepare a Mirror Database for Mirroring (SQL Server), Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL), Use Certificates for a Database Mirroring Endpoint (Transact-SQL), or Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio).

使用 Transact-SQL 开始镜像Starting mirroring by Using Transact-SQL

发出 ALTER DATABASE database_name SET PARTNER =' partner_server ' 语句的顺序非常关键。The order in which the ALTER DATABASE database_name SET PARTNER ='partner_server' statements are issued is very important.

  1. 第一个语句必须在镜像服务器上运行。The first statement must be run on the mirror server. 发出此语句时,镜像服务器不会尝试联系任何其他服务器实例。When this statement is issued, the mirror server does not try to contact any other server instance. 相反,镜像服务器指示其数据库先进行等待,直到主体服务器与镜像服务器建立联系。Instead, the mirror server instructs its database to wait until the mirror server has been contacted by the principal server.

  2. 第二个 ALTER DATABASE 语句必须在主体服务器上运行。The second ALTER DATABASE statement must be run on the principal server. 此语句使主体服务器尝试连接到镜像服务器。This statement causes the principal server to try to connect to the mirror server. 创建此连接之后,镜像服务器随后将尝试通过其他连接与主体服务器建立连接。After that connection is created, the mirror then tries to connect to the principal server on another connection.

有关详细信息,请参阅 ALTER DATABASE (Transact-SQL)For more information, see ALTER DATABASE (Transact-SQL).

备注

有关使用 SQL Server Management StudioSQL Server Management Studio 启动镜像的信息,请参阅使用 Windows 身份验证建立数据库镜像会话 (SQL Server Management Studio)For information about using SQL Server Management StudioSQL Server Management Studio to start mirroring, see Establish a Database Mirroring Session Using Windows Authentication (SQL Server Management Studio).

跨数据库事务Cross-Database Transactions

在具有自动故障转移功能的高安全性模式下镜像数据库时,自动故障转移可能会导致自动解析并且可能错误解析有疑问的事务。When a database is being mirrored in high-safety mode with automatic failover, an automatic failover could lead to automatic and possibly incorrect resolution of in-doubt transactions. 如果提交跨数据库事务时在任一数据库中进行自动故障转移,则数据库之间可能发生逻辑上的不一致。If an automatic failover occurs on either database while a cross-database transaction is being committed, logical inconsistencies can occur between the databases.

自动故障转移可能影响的跨数据库事务类型包括:The types of cross-database transactions that can be affected by an automatic failover include the following:

  • 正在同一 SQL ServerSQL Server实例中更新多个数据库的事务。A transaction that is updating multiple databases in the same instance of SQL ServerSQL Server.

  • 使用 MicrosoftMicrosoft 分布式事务处理协调器 (MS DTC) 的事务。Transactions that use a MicrosoftMicrosoft Distributed Transaction Coordinator (MS DTC).

有关详细信息,请参阅用于 AlwaysOn 可用性组和数据库镜像的跨数据库事务和分布式事务 (SQL Server)For more information, see Cross-Database Transactions and Distributed Transactions for Always On Availability Groups and Database Mirroring (SQL Server).

另请参阅See Also

设置数据库镜像 (SQL Server) Setting Up Database Mirroring (SQL Server)
针对数据库镜像和 AlwaysOn 可用性组的传输安全性 (SQL Server)Transport Security for Database Mirroring and Always On Availability Groups (SQL Server)