Problems Configuring SQL Server Database Mirroring Due to Error 1418
SQL Server Database Mirroring was a popular feature before release of SQL Server 2012 and was widely used in many organizations for High Availability purposes or critical databases. Although most of the times it is easy and hassle free to configure database mirroring, but sometimes it becomes a big problem setting up database mirroring.
One of the most popular error receiving while configuring database mirroring is error 1418. Below is how the error looks like
Msg 1418, Level 16, State 1, Line 1
The server network address “TCP://servername.domain.com:5022” can not be reached or does not exist. Check the network address name and reissue the command.
The above error is the most generic error and can occur due to plenty of reasons. Below are some of the causes of this error and some suggestions to fix this error.
– Make sure that the Mirroring partner server has been prepared to be used as secondary for the mirroring. Example, you need to perform a full backup and a log backup of proposed principal database and restore both the full and log backups on proposed mirror server with NORECOVERY.
– Identify which account is being used to run the SQL Server services. It is recommended to use an domain account to run the SQL Server services. The domain account running SQL Server services should have proper access to the other SQL instance. Example: SQL Server account on proposed principal server need to have permissions on proposed mirror server and vice-versa. If you are using local accounts as SQL Server Service accounts, then you either need to use certificates or a local user on both servers with same password.
– Ensure that that SQL Server service accounts has connect permission over endpoints. Principal Server service account should have connect permission to ENdpoint on Mirror server and vice-versa.
– Make sure the mirroring ports used are not used by any other process on either servers.
– Make sure that the firewall or anti-virus does not block the mirroring ports.
– Make sure you are able to TELNET using the FQDN of the server name for both proposed principal and mirror servers.
– Verify if there is any SSL encryption enabled for any of the SQL Server instances.
– Verify that the mirroring EndPoints are in started state. Try stopping and starting them back.
– Check the SQL Server errorlog file on both principal and mirror server and make sure there are no errors. Most often or not you will find the real cause of the failure here. Example is login failed due to missing grant permission over mirroring endpoint to SQL Server service account as shown below.
Error: 1474, Severity: 16, State: 1
Database mirroring connection error 4 ‘An error occurred while receiving data: ‘10054(An existing connection was forcibly closed by the remote host.)’.’ for ‘TCP://mirrorserver.mydomain:5022’
Database Mirroring login attempt by user ‘NT AUTHORITY\ANONYMOUS LOGON.’ failed with error: ‘Connection handshake failed. The login ‘NT AUTHORITY\ANONYMOUS LOGON’ does not have CONNECT permission on the endpoint. State 84.’.
What is Database Mirroring?
Database mirroring is a solution for increasing the availability of a SQL Server database. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. Typically, these server instances reside on computers in different locations. Starting database mirroring on a database, initiates a relationship, known as a database mirroring session, between these server instances.
This is applicable on below versions of SQL Server
SQL Server 2005
SQL Server 2008 R2
SQL Server 2012
SQL Server 2014
Hope this was helpful.
In-Depth Blogs on SQL Server, Information about SQL Server Conferences and Events, SQL Server Frequently asked questions, SQL Server Trainings.