Wednesday, August 22, 2012

SQL Server 2012 Database mirroring error (Microsoft SQL Server, Error: 1418)

While setting up mirroring between 2 servers I came across following error
The server network address TCP://<servername>.<domain>.com:<portnumber> can not 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)

After setting up database mirroring security configuration I started mirroring between 2 servers and immediately I got this error. The error however does not tell you much about the actual problem. I tried changing firewall setting, checked that the service account is correct on both primary and mirror. I also tried ping from either machines and it worked fine.

But after checking the BOL I found the issue. Before setting up mirroring you have full backup database on primary and restore it on mirror as “WITH NORECOVERY”.

NORECOVERY specifies that roll back not occur. This allows roll forward to continue with the next statement in the sequence. In this case, the restore sequence can restore other backups and roll them forward.

After restoring with NORECOVERY I did a log backup on primary and restored it on mirror. Once done mirroring worked as expected.

Please note that in SQL Server 2008 & R2 mirrored copy is not readable. In SQL Server 2012 with help of Always On availability groups you can make mirrored copy readable.

No comments:

Post a Comment