Database Mirroring Needs FQDNs

A quick basic post, and one that I’ve forgotten. Since blogging is a good way to remind myself of things, here goes.

I was testing Database Mirroring (DBM) recently for an upgrade situation. I’ve set up it up in the past, but since it’s deprecated, I’ve moved on to working with Availability Groups (AG) for the most part. However, mirroring might still be in use for you, or you’re looking to perform a simple rolling upgrade, and DBM works well.

I restored a database on a new instance, opened the firewalls for 5022, and then went through the mirroring wizard. Once I was done, I enabled mirroring on the secondary database (the one restoring) and that worked fine.

Then I ran this on the primary:

ALTER DATABASE Baseball SET PARTNER = 'TCP://192.168.1.201:5022'

After a few minutes, I got an error:

Msg 1418, Level 16, State 1, Line 10
The server network address “TCP://192.168.1.201:5022” 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.

I tried all sorts of things, including shutting off firewalls, and disabling the rebuilding mirroring. My endpoints were fine, the domain accounts running the instances had access, but it wouldn’t work. I tore down mirroring and added it back, verifying each machine could see the other by name. I tried again.

ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02:5022'

I knew I had a problem when this took more than 5sec to respond. Again, an errror.

Msg 1418, Level 16, State 1, Line 10
The server network address “TCP://SQL02:5022” 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.

Finally I tore things down again, deleted endpoints, but this time I connected to the instance with an FQDN and configured things. I made I connected to the mirror with an FQDN as well. Finally things worked:

ALTER DATABASE Baseball SET PARTNER = 'TCP://SQL02.HOME.XXXXX.COM:5022'

I must have read the documentation numerous times, each time reading the FQDN, but somehow thinking that couldn’t be the issue in a small network.

Hopefully this blog will help me remember.

About way0utwest

Editor, SQLServerCentral
This entry was posted in Uncategorized and tagged , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s