SQL Server - All Database Mirroring Errors, Troubleshooting and Resolution

Error: When you configure mirroring you might receive the below errror, One or more of the server network addresses lacks a fully qualified domain name (FQDN). Specify the FQDN for each server, and click start mirroring again

Solution: Assume that your principal server is A and mirror server is B and you have configured mirroring for Adventure Works database. The fully qualified computer name of each server can be found running the command prompt:  

IPCONFIG /ALL

Concatenate the "Host Name" and "Primary DNS Suffix". If you see something like:-

Host Name . . . : A            Primary Dns Suffix . . . .  . : corp.mycompany.com

Then the computer name is just A.corp.mycompany.com. Prefix 'TCP://' and append ':' and you then have the partner name.

-- Specify the partner from the mirror server

ALTER DATABASE [AdventureWorks] SET PARTNER = TCP://A.corp.mycompany.com:5022';

-- Specify the partner from the principal server

ALTER DATABASE [AdventureWorks] SET PARTNER = N'TCP://B.corp.mycompany.com:5022';

Error: 1412: Occurs if we have not restored T.Log backup along with FULL backup in mirror server before configuring mirroring.

Error: 1416, Database is not configured for database mirroring

You need to restore the Full backup from principal server using With NoRecovery option and also one T.log backup from principal server using With NoRecovery option and then start configuring mirroring.

Error: 1418(Can’t be reached) :Occurs due to the communication problems b/w the end points. Possible reasons are TCP/IP for the instance was not enabled, Duplicate end points exist, and Login was not created in their instance to mirroring.

Error: 1475: Occurs if any bulk operations are made in production server before enabling mirroring. To resolve this take T.Log backup from principal and restore in mirror then enable mirroring.

Error: 1478: Occurs If all T.Log Backups are not restored before starting mirroring

Error: 1498 This is a common error & everyone is known to this error. Database mirroring is officially supported from SQL Server 2005 SP1, hence in the RTM version database mirroring is disabled by default. You can use TRACE 1400 to enable in RTM version or else update your SQL Server with latest service pack.

Error: - The witness instance name must be distinct from both server instance that manage the database. The alter database set witness command failed (Microsoft sql server error:1429) 

Error: - If we select witness SQL Server instance in single machine. It does not allow to configure Witness server. Both Mirror and Witness server take same port as 5023 and the below error will come.

Error:- Occur when mirror DB is in In Recovery Mode. To Fix it Restore full backup and T. Log back up on Mirror Server.

Error: - After patching principal moved in suspended mode. And not coming in synchronized mode.

While patching patch, Mirroring failover must have done by SQL server Service account only. If DBA do failover by his/her own account before patching Mirroring will not move in suspended mode and will not come in synchronized mode. Then only reconfiguration is the option to run mirroring.

Error: principal moved in suspended mode and not move in synchronized mode.

If the log file size is full mirroring gets failed. To Fix it, release log file size.

Error: 1418- After configuring a mirroring. It asks to start mirroring. While starting Mirroring 1418 error occurs.

Bellow 3 instances are installed and all instance should run with same Service account (Not user account)

 Error: In My principal server while I start Mirror The server network address "TCP://NABONIDUS.infrasoftcorp.com: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. SQL Server 2008 - 1418.   

The solution to the above problem is very simple and as follows.

Fix/WorkAround/Solution: Try all the suggestions one by one.

Suggestion 1: Make sure that on Mirror Server the database is restored with NO RECOVERY option (This is the most common problem).

Suggestion 2: Make sure that from Principal the latest LOG backup is restored to mirror server. (Attempt this one more time even though the full backup has been restored recently).

Suggestion 3: Check if you can telnet to your ports using command TELNET ServerName Ports like “telnet SQLServerName 5023″.

Suggestion 4: Make sure your firewall is turned off.

Suggestion 5: Verify that the endpoints are started on the partners by using the state or state_desc column the of the sys.database_mirroring_endpoints catalog view. You can start end point by executing an ALTER ENDPOINT statement.

Suggestion 6: Try the following command as one of the last options.

GRANT CONNECT ON ENDPOINT::Mirroring TO ALL

Suggestion 7: Delete the end points and recreate them.

Please comment if you know or faced any error of Mirroring.- Jainendra Verma