通过

Database Mirroring Configuration Failure scenarios

  • 项目

Hello all,

In this blog, I am covering few scenario's that I have encountered while configuring Database mirroring as a high availability solution for SQL Server database.

For demonstration purpose, I am using SQL 2012 instances on Windows 2012 R2 server.

Principal Server: SQLServer\SQL2012
Mirror Server: SQLServerDR\SQL2012
Witness Server: Witness\Witness
Mirrored Database: DBMirror
Mirroring Port : 5022
Service Account: SQLServiceAccount@contoso.lab

SCENARIO 1:

Before starting the database mirroring configuration, the mirror database must be created by restoring WITH NORECOVERY a recent full backup and, perhaps, log backups of the principal database onto the mirror server.

To start the Database mirroring configuration, I started the Database Mirroring Security Wizard on the Principal Server. While trying to connect to the Mirror Server instance from Principal Server, I was getting the below connectivity error:

Cannot connect to SQLSERVERDR\SQL2012.
ADDITIONAL INFORMATION:
Failed to connect to server SQLSERVERDR\SQL2012. (Microsoft.SqlServer.ConnectionInfo)
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

To check if the SQL Server Mirror instance TCP port and SQL Server browser UDP port is blocked or not, I used the tool PortqueryUI. The tool can be downloaded from: https://www.microsoft.com/en-us/download/details.aspx?id=24009

 

Figure 1. Portquery output for SQL Server Mirror Instance Port

 

 

Figure 2. Portquery output for SQL Server browser Port

 

PortQuery output indicated that the SQL Server Mirror Instance Port and SQL Server Browser port was blocked on the Mirror Server. I created Windows Firewall rules for 49450 (Mirror Instance Port) and 1434 (SQL browser UDP Port) and post which I was able to connect to the Mirror Instance and successfully configure Database Mirroring.

If you encounter any further issues with SQL Connectivity, please follow: https://blogs.msdn.com/b/docast/archive/2014/10/24/tips-and-tricks-to-fix-the-sql-connectivity-issues.aspx

 

SCENARIO 2:

In this scenario, I was able to create the database endpoints on the Principal and the Mirror Server. However, while starting Mirroring (below screenshot), I was getting errors:

 

Figure 3. Database Mirroring ‘start Mirroring’

 

 

Figure 4. Error while configuring mirroring

An error occurred while starting mirroring.
Additional information:
Alter failed for Database ‘DBMirror’. (Microsoft.SqlServer.Smo)
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The server network address “TCP://sqlserverdr.Contoso.lab:5022” cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error:1418)
 

This is a very common error message while configuring mirroring. Database endpoints are configured on Port 5022 by default. I used the PortQueryUI tool again to check if the mirroring port was blocked or not.

 

Figure 5. PortQuery output for Database Mirroring Port

 

PortQueryUI output showed “FILTERED”, which indicates that the mirroring Port was blocked. So I created a Windows Firewall rule for the SQL Mirroring Port. After creating the rule, I was able to configure mirroring successfully.

 

Figure 6: Successful configuration of Database Mirroring

 

SCENARIO 3:

Database mirroring is already configured, but the data synchronization from Principal to Mirror fails with the error:

No connection: cannot connect to the mirror server instance

 

Figure 7. Mirroring Synchronization failure

Looking at the Error log on the Principal Server:

Database Mirroring login attempt by user 'Node\SQLServiceAccount.' failed with error: 'Connection handshake failed. The login 'Node\SQLServiceAccount' does not have CONNECT permission on the endpoint. State 84.'. [CLIENT: 192.168.2.52]

The above error indicates that the SQL Service account doesn’t have connection permission on the mirroring endpoints:

I 4xecuted the below query which resets the CONNECT permission for SQL Service account on database mirroring endpoints which fixed the synchronization issue.

Grant connect on ENDPOINT::Mirroring to [Node\SQLServiceAccount]

 

SCENARIO 4:

Database mirroring is already configured, but the synchronization from Principal to Mirror fails with the error:

No connection: cannot connect to the mirror server instance

 

 Figure 8. Mirroring Synchronization Failure.           

Reviewing the SQL Server Error logs on the primary instance:

Database Mirroring login attempt failed with error: 'Connection handshake failed. There is no compatible encryption algorithm. State 22.'. [CLIENT: 192.168.2.52]

As per the error message “There is no compatible encryption algorithm”. ENCRYPTION is set to REQUIRED by default. This means that all connections to this endpoint must use encryption. However, you can disable encryption or make it optional on an endpoint.

I reviewed the encryption algorithm used:

On the Principal Server:

select encryption_algorithm_desc from sys.database_mirroring_endpoints

AES

 

On the Mirror Server :

select encryption_algorithm_desc from sys.database_mirroring_endpoints

RC4

 

Scripted out the Mirroring endpoint on the Principal and Mirror Servers:

Principal Server:

CREATE ENDPOINT[Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT= 5022,LISTENER_IP=ALL)

FOR DATA_MIRRORING (ROLE=PARTNER,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIRED ALGORITHM AES)

Mirror Server:

CREATE ENDPOINT[Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT= 5022,LISTENER_IP=ALL)

FOR DATA_MIRRORING (ROLE=PARTNER,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIRED ALGORITHM RC4)

This error usually occurs if the mirroring endpoints are pre-staged. To fix the issue, I had to alter the mirroring endpoint to use the same encryption algorithm.

 

I hope the guidelines address the four listed scenarios help you. 

Reference articles:

Please share your feedback, questions and/or suggestions.

Thanks,

Don Castelino | SQL Server Support Team | CSS – Microsoft

Disclaimer: All posts are provided AS IS with no warranties and confer no rights. Additionally, views expressed here are my own and not those of my employer, Microsoft.

Comments

  • Anonymous
    May 22, 2016
    Dear Don, Can you please clarify the 49450 (Mirror Instance Port) and 1434 (SQL browser UDP Port) as per my understanding are these ports are principal server sql instance port and mirror server sql server instance port .Regards Sandeep
    • Anonymous
      May 24, 2016
      Hi Sandeep,Port 49450 is the port number of the mirror instance and 1434 UDP port is the port of SQL browser. Mirror server should be accessible from principal and vice versa. Since the ports are non default (1433), browser port need to be allowed in firewall. You can use PortqueryUI tool if you encounter any issues with Connectivity between Principal and mirror instance.Regards,Don Rohan
      • Anonymous
        June 28, 2016
        In our case for 3-node AOAG, I am using sql tcp=7048 with sql browse disabled. The hadr endpoints are all using 5022 allowed thru FW OK, but when trying to join the third replica to the AG, last step fails with that state 84 related error mentioned above. grant connect to sql service account doen and verified, but behavior is like there was never a connect permission granted. Query for last-error for that endpoint, always the same. Any idea what I am missing ? SQL Srv 2014 Ent. Core -SP1 CU6(new)
  • Anonymous
    February 11, 2017
    I would like to add that if you have already started an endpoint from the wizard, you will need to run the following:PRIMARY-----------ALTER ENDPOINT[Mirroring]STATE=STARTEDAS TCP (LISTENER_PORT= 5022,LISTENER_IP=ALL)FOR DATA_MIRRORING (ROLE=PARTNER,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIRED ALGORITHM AES)MEMBER:------------ALTER ENDPOINT[Mirroring]STATE=STARTEDAS TCP (LISTENER_PORT= 5022,LISTENER_IP=ALL)FOR DATA_MIRRORING (ROLE=PARTNER,AUTHENTICATION=WINDOWS NEGOTIATE,ENCRYPTION=REQUIRED ALGORITHM RC4)
    • Anonymous
      February 13, 2017
      Hi Ryan,Yes. If the Mirroring endpoints are already created, they can be modified using ALTER script.