THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Adam Machanic

Adam Machanic, Boston-based SQL Server developer, shares his experiences with programming, monitoring, and performance tuning SQL Server. And the occasional battle with the query optimizer.

Database Mirroring: FQDNs are Your Friends!

On a recent project for a customer, I learned an imporant Database Mirroring lesson: fully-qualified domain names (FQDNs) are essential!

Both Books Online and the mirroring wizard indicate that it's OK to specify the participating servers as IP addresses--so that's what I did.  The witness came up fine, and the principal came up fine.  Mirroring started, and I did a few manual failovers.  Great!

But now I added the witness server and suddenly things started breaking down. The mirror instance couldn't connect with the witness, and the witness was throwing strange errors like:

Database mirroring connection error 4 'An error occurred while receiving data: '64(The specified network name is no longer available.)'.' for 'TCP://Server2:7024'.

...where "Server2" in this example is the mirror. Notice that the error doesn't include the IP address, but rather the server's name?

After banging my head against the table for a day or so, I wrote to about 50 people asking for help. No answers, and I thought I was out of luck until Don Vilen was kind enough to reply. Turns out, the problem is simple to fix: Don't use IP addresses, ever.  Always use FQDNs!  Thanks, Don!!  Once the FQDNs were used, instead of the IP addresses, everything came up as expected and automatic failovers started working perfectly.

To find your server's FQDN, you can use "ipconfig" from the command prompt, and append the server name to the connection-specific DNS suffix. 

I hope this post helps someone else avoid the frustration I went through. It was a rough couple of days trying to debug this problem and having to tell a customer that their planned HA solution might not work is not a fun situation to be in.

Published Wednesday, June 13, 2007 9:09 PM by Adam Machanic
Filed under:

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

roman said:

good timing, I am about to start on a DB mirroring implementation

June 14, 2007 9:30 AM
 

Sean Fitzgerald said:

This seems to be an issue that I am now having, and have been troubleshooting for a couple of weeks now (passively).  I've been assured that this is a network latency issue though, and have been approaching it from that direction (with no resolution, obviously).  Did Don Vilen elaborate on why IP Addresses are "bad"?  I'm interested.

December 14, 2008 8:35 AM
 

Sean Fitzgerald said:

Sorry - one last thing.  My mirroring session including a witness has been running fine since its inception in 2007.  Only recently, after one or more Windows 2003 OS patches to my WITNESS server did I start receiving these errors.

December 14, 2008 8:37 AM
 

KV said:

Thank you Adam for your post. Turned out to be a life saver.

I struggled with this for a week and would like to post my experience for everyone. My servers were AWS instances.

After struggling with the issue for several days, I was able to fix it as follows:

The key piece seemed to be that when you create an AWS instance, it automatically creates a DNS for the instance that looks like this IP-xxyyzzaa where xx,yy,zz,aa are the hex values for the ip v4 address when you do an ipconfig on the machine. So, on the primary, mirror and witness I changed the computer name to match the scheme. To verify that it worked, I did an nslookup IP-xxyyzzaa. It should return back the ip address of the machine and also the name IP-xxyyzzaa.ec2.internal. In the Configure Security Wizard, I used the name IP-xxyyzzaa.ec2.internal to connect to each machine. At the end of it Start Mirroring worked like a charm. To make sure that this was not a fluke, I repeated the procedure on 3 new machines and it worked without a hitch.

One thing to keep in mind is that it is good to have an elastic ip tied to these machines so the ip address does not change. Right now I do not have Elastic ip tied to these, so I will have to reconfigure mirroring when that happens.

Hope this helps. Please post questions, comments as you see fit. I will be checking back periodically.

Thanks

KV

March 29, 2013 2:48 PM

Leave a Comment

(required) 
(required) 
Submit

About Adam Machanic

Adam Machanic is a Boston-based SQL Server developer, writer, and speaker. He focuses on large-scale data warehouse performance and development, and is author of the award-winning SQL Server monitoring stored procedure, sp_WhoIsActive. Adam has written for numerous web sites and magazines, including SQLblog, Simple Talk, Search SQL Server, SQL Server Professional, CoDe, and VSJ. He has also contributed to several books on SQL Server, including "SQL Server 2008 Internals" (Microsoft Press, 2009) and "Expert SQL Server 2005 Development" (Apress, 2007). Adam regularly speaks at conferences and training events on a variety of SQL Server topics. He is a Microsoft Most Valuable Professional (MVP) for SQL Server, a Microsoft Certified IT Professional (MCITP), and an alumnus of the INETA North American Speakers Bureau.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement