Troubleshoot Database Mirroring Configuration (SQL Server)
Published: May 17, 2016
Updated: May 17, 2016
Applies To: SQL Server 2016
This topic provides information to help you troubleshoot problems in setting up a database mirroring session.
|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 the MSSQLSERVER_1418 topic.|
|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.|
The accounts under which SQL Server is running must be correctly configured.
Do the accounts have the correct permissions?
If the accounts are running in the same domain accounts, the chances of misconfiguration are reduced.
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 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.
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.
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
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).
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.
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.
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).
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).
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).
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).