Troubleshoot Database Mirroring Configuration (SQL Server)

Článek
03/03/2023

Applies to: SQL Server

This topic provides information to help you troubleshoot problems in setting up a database mirroring session.

Note

Ensure that you are meeting all the prerequisites for database mirroring.

Issue	Summary
Error Message 1418	This SQL 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	Discusses requirements for correctly configuring the accounts under which SQL Server is running.
Endpoints	Discusses requirements for correctly configuring the database mirroring endpoint of each server instance.
SystemAddress	Summarizes the alternatives for specifying the system name of a server instance in a database mirroring configuration.
Network access	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.
Starting mirroring by Using Transact-SQL	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

The accounts under which SQL Server is running must be correctly configured.

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. 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. For more information, see Manage Metadata When Making a Database Available on Another Server Instance (SQL Server). This includes the Network Service account.
If SQL Server is running as a service that is using the local system account, you must use certificates for authentication. For more information, see Use Certificates for a Database Mirroring Endpoint (Transact-SQL).

Endpoints

Endpoints must be correctly configured.

Make sure that each server instance (the principal server, mirror server, and witness, if any) has a database mirroring endpoint. 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).
Check that the port numbers are correct.

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.
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.
Make sure that the endpoints are started (STATE=STARTED). On each server instance, use the following Transact-SQL statement.
```
SELECT state_desc FROM sys.database_mirroring_endpoints  
```
For more information about the state_desc column, see sys.database_mirroring_endpoints (Transact-SQL).

To start an endpoint, use the following Transact-SQL statement.
```
ALTER ENDPOINT Endpoint_Mirroring   
STATE = STARTED   
AS TCP (LISTENER_PORT = <port_number>)  
FOR database_mirroring (ROLE = ALL);  
GO  
```
For more information, see ALTER ENDPOINT (Transact-SQL).
Check that the ROLE is correct. On each server instance use the following Transact-SQL statement.
```
SELECT role FROM sys.database_mirroring_endpoints;  
GO  
```
For more information, see sys.database_mirroring_endpoints (Transact-SQL).

The login for the service account from the other server instance requires CONNECT permission. Make sure that the login from the other server has CONNECT permission. To determine who has CONNECT permission for an endpoint, on each server instance use the following Transact-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. 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 Access

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 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.

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. 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. 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.

Important

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.

For more information, see Prepare a Mirror Database for Mirroring (SQL Server).

Failed 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:

The database owner must remove the mirroring session and restore a full backup of the filegroup that contains the added file.
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.
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.

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).

Starting mirroring by Using Transact-SQL

The order in which the ALTER DATABASE database_name SET PARTNER ='partner_server' statements are issued is very important.

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.
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.

For more information, see ALTER DATABASE (Transact-SQL).

Note

For information about using SQL 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:

A transaction that is updating multiple databases in the same instance of SQL Server.
Transactions that use a Microsoft Distributed Transaction Coordinator (MS DTC).

For more information, see Cross-Database Transactions and Distributed Transactions for Always On Availability Groups and Database Mirroring (SQL Server).

Sdílet prostřednictvím