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:
ALTERDATABASE 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
[4290 byte] By [
Pamb] at [2008-2-7]
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
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
