Share via

  • Article

Troubleshooting Database Mirroring Deployment

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

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.

System address

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 (Transact-SQL)

Describes the required order for ALTER DATABASE database_name SET PARTNER ='partner_server' statements.

Accounts

The accounts under which SQL 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. 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 Managing Metadata When Making a Database Available on Another Server Instance. This includes the Network Service account.

  2. 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 Using Certificates for Database Mirroring. 

Endpoints

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. For more information, see sys.database_mirroring_endpoints (Transact-SQL) and, depending on the form of authentication, either How to: Create a Mirroring Endpoint for Windows Authentication (Transact-SQL) or How to: Allow Database Mirroring to Use Certificates for Outbound Connections (Transact-SQL).

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

  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. For information about verifying port availability, see MSSQLSERVER_1418.

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

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

  6. 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 Specifying 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 path (including the drive letter) of the mirror database be identical to the path of the principal database. If the file layouts 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 How to: Prepare a Mirror Database for Mirroring (Transact-SQL).

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:

  1. The database owner must remove the mirroring session and restore a full backup of the filegroup that contains the added file.

  2. 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. 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, How to: Prepare a Mirror Database for Mirroring (Transact-SQL), How to: Establish a Database Mirroring Session Using Windows Authentication (Transact-SQL), Using Certificates for Database Mirroring, or How to: Configure a Database Mirroring Session (SQL Server Management Studio).

Starting Mirroring (Transact-SQL)

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

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. For more information, see MSSQLSERVER_1418.

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 Database Mirroring and Cross-Database Transactions.