Geeks With Blogs

News Dave's Mug View David Oliver's profile on LinkedIn Add to Technorati Favorites Blog Directory for Guildford, Surrey
Dave Oliver's Blog Enterprise Technology Thought Leadership in a FTSE 100

Finally after spending hours building 3 servers (to do the job of the primary, mirror and witness) and installing SQL Server 2005 Enterprise with SP2 all sitting in the same domain. I was ready to try out Database Mirroring which arguably is one of the main reason people can justify the cost of purchasing the Enterprise edition.

It should be easy, fill in the wizard and bob's your uncle, your trusty database is ready for instant uninterrupted failover goodness. In my case it all fell over with the mysterious, misleading and unhelpful

'Msg 1418, Level 16, State 1, Line 1
The server network address "TCP://[server][domain address]:5022" can not be reached or does not exist. Check the network address name and reissue the command.'

So here is my advice if you also experience this error,

1) Follow the Microsoft SQL Server 2005 Database Mirroring Setup Trouble-shooter.

This trouble shooter covers a great deal of setup issues and allow to examine many parts of your setup, most of them 'school-boy' errors but the 1418 error itself is lightly cover by a small section with a link to a less than helpful page that tells you about the error but amazingly not how to fix it!

2) Jose Barreto's honest and succinct blog post on this error covers a good proportion of instances of this error however it didn't with my particular instance.

3) This next link is the 'Daddy' of sites on the 1418 error from Alan 328 and is a gem of a find.

However my instance of the problem was actually down to permissions, let me explain.  I setup the databases according to BOL (SQL Server 2005 Books Online), one of them restored with 'NO RECOVERY' selected so good to go. Then persistently kept on getting this error for twos days worth of trying after scouring the Internet for a solutions, hence finding and following all the links above.

I used Telnet to understand if I could get onto each server via the port. Netstat to understand if the port were listening and Wireshark to watch the traffic. All from which I worked out that the different servers could infact see each other and talk to each other.

After some experimenting with accounts with various domain level privileges, I deduced that  each SQL Server must have enough privileges and have authorization to talk to each other across Endpoints and this solved my problem. The answer was here in SQL Server 2005 Security Best Practices - Operational and Administrative Tasks document on page 10.

There is a myriad of things that cause this error meaning that there isn't one solution, the only common feature is the error message which just seems to be a misleading catch all error message. Obviously Database Mirroring had a difficult birth having to wait till SP1 to have a supported working version so perhaps some of the niceties of helpful error messages was de-scoped to get the product out the door. I do hope the story in SQL Server 2008 is much better because there is always Oracle's Dataguard.

Posted on Saturday, May 17, 2008 11:10 AM Main | Back to top


Comments on this post: SQL Server 2005 Database Mirroring Setup 1418 Error

Comments are closed.
Comments have been closed on this topic.
Copyright © Dave Oliver | Powered by: GeeksWithBlogs.net