Database Mirroring: Cannot set the Mirror Server instance as the partner on Principle server

Hi,

I’m trying to get database mirroring working but having problem when principal and mirror are not on the Domain. (I got it working once if both principle and mirror servers are on the domain.) Both servers have fully qualified domain names. Both have Windows server 2003 SP1 and SQL Server 2005 June CTP.

I used Local System Acct/Mixed mode and follow the Setting up Database Mirroring using Certificates article in the BOL: ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/df489ecd-deee-465c-a26a-6d1bef6d7b66.htm

I kept getting error when trying to set the Mirror Server instance as the partner on Principle server with this command:

ALTER DATABASE Testdb

SET PARTNER = 'TCP://<mirror server.fqdomainname.com>:5022';

GO

Here is the error I kept getting:

Msg 1460, Level 16, State 2, Line 1

The partner for database "Testdb" is not defined or is different from the one attempting to establish database

How can I troubleshoot this further? Could anyone please point me in the right direction?

Thanks,
Pamb

so, i went to the devloper and asked "What is the 1460 error?"

he looked around and he finally said "We don't throw that error any more. Go tell the customer that, in fact, they are not seeing this error. Use the Force. :)"

OK, then i offered to buy him a beer.

two minutes later, it turns out that the error occurs when comparing the server names AND there are different collations for the two different servers. the code was changed recently to compare the server names differently.

so, make sure that the collations on the two servers are the same.

hope that helps!!

thanks,
mark|||Hi Mark,

Thanks for your response. Unfortunately, I have a few more questions...

I could check the Collation setting on the Principal database and it is set to "SQL_Latin1_General_CP1_CI_AS" but since the Mirror Database is in the restoring mode, I can't seem to check it's setting. Well, is this the right collation setting first of all?

Thanks in advance again for your help.

Pam|||Hi Mark,

I managed to check the collation setting at the Server-level and Database-level on both Principle and Mirror databases and the Collation is set to "SQL_Latin1_General_CP1_CI_AS".

What's next? Tequila? :)

Thanks again,
Pam

|||Pam,

I have the same error ... how did you finally manage?
Thanks
Jerome|||We shouldn't be breakin' out the Tequila yet.

From the developer:

--
you can get the 1460 error if:

1) your _servers_ (not the dbs themselves) have different collations

2) you are not specifying the name of the partner correctly (use netstat on the boxes to see what the box thinks it's called)

3) you are trying to establish on a secondary network
1. i know that you said you checked the server collations, but make sure.
2. make sure that you are specifying the correct FQDN.
3. this isn't a security problem. it is failing at the direct string compares. so, what you provided at the setup for the string compare is not what the server thinks that it is called.

are there multiple NICs on your machines?

thanks,
mark

|||Hi Mark,

What do you mean by a Secondary Network?

Yes, there are two nics on both machines. So, we just tried setting them up using just one IP address and issued the set partner query again but got same error.

I then started from scratch by uninstalling server/data and followed the same mirroring process but still got the same error.

[We also noticed that the TCP/IP protocols (Server IP and Localhost IP) are disabled under the Server Configuration Manager so I enabled them.]
Which table does this query issue against?
ALTER DATABASE Scout
SET PARTNER = 'TCP://<FQDN:5022';
Thanks,
Pam

|||

Hi Mark,

We have finally figured this out and would actually like to report what seems to be the software problem.

Here are the steps that we took...

1. We modified the Hosts file (under Windows32\System\Drivers\etc) on both machines so they could resolve each others fully qualified domain names:

principal.foo.com 10.0.0.132
mirror.foo.com 10.0.0.133

2. In the Advanced TCP/IP settings for DNS, we set the "DNS suffix for this connection" to foo.com.

3. We then issued the set partner queries using FQD name:

Alter database set PARTNER = 'TCP://principal.foo.com:5022'; [on the Mirror server]

Alter database set PARTNER = 'TCP://mirror.foo.com:5022'; [on the Principal server]

This failed the same 1460 error

4. We then tried it without the domain names:

Alter database set PARTNER = 'TCP://principal:5022'; [on the Mirror server]
Alter database set PARTNER = 'TCP://mirror:5022'; [on the Principal server]

*Success!*
What gave us a clue was running the Database Mirroring Properties Wizard on the Principal server. We noticed that the Wizard shows a non-FQDN as the Principal server, i.e. ‘TCP://principal:5022’.

It’s not clear to us how SQL is generating the principal name that has to be later string matched against name issued from the mirror server. This is a pretty fragile mechanism. We’d like to suggest that the TCP://[hostname] be resolved in the typical way, i.e. convert hostnames to IP’s as necessary and compare the IP addresses.

Thanks for all your help! [you owe us a beer ;-)]

Pam