I've setup up a mirrored database on two servers Server1 and Server 2.
On Server3, I'm trying to setup a linked server that will work regardless of which server has the active database.
I'm using this to setup the linked server.
EXEC sp_addlinkedserver
@server = N'MyLinkedServer',
@srvproduct = N'',
@provider = N'SQLNCLI',
@provstr = N'Server=SERVER1;FailoverPartner=SERVER2;Network=dbmssocn',
@catalog = N'MyDatabase';
EXEC master.dbo.sp_serveroption
@server = N'MyLinkedServer',
@optname = N'data access',
@optvalue = N'true';
EXEC master.dbo.sp_serveroption
@server = N'MyLinkedServer',
@optname = N'rpc',
@optvalue = N'false';
EXEC master.dbo.sp_serveroption
@server = N'MyLinkedServer',
@optname = N'rpc out',
@optvalue = N'true';
EXEC master.dbo.sp_serveroption
@server = N'MyLinkedServer',
@optname = N'connect timeout',
@optvalue = N'60';
EXEC master.dbo.sp_addlinkedsrvlogin
@rmtsrvname = N'MyLinkedServer',
@locallogin = NULL,
@useself = N'False',
@rmtuser = N'sqluser',
@rmtpassword = N'sqlpassword';
As long as MyDatabase on Server1 is the principal, the linked server works. When I manually failover to Server2, the linked server fails.
The error messages are:
Msg 11001, Level 16, State 1, Line 0
TCP Provider: No such host is known.
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'sqluser'.
Msg 4060, Level 11, State 1, Line 1
Cannot open database "MyDatabase" requested by the login. The login failed.
The 18456 and 4060 error messages repeat until the query times out after 60 seconds.
On Server1, I can see repeated failures trying to login to "MyDatabase". It's failing because Server1 is the mirror. I see no attempts to connect to Server2 which is the principal.
If I recreate the linked server with Server2 as the Primary and Server1 as the Mirror, it will work until I fail back to Server1.
The mirrored servers are SQL Server 2012 and the box I'm trying to create a linked server on is SQL Server 2005, but I also have the same problem when creating the linked server on a SQL Server 2008 R2 box.
I've seen reports that it does work.
What am I missing?
Thank you!
Update
I've made progress. Yesterday, when I first set things up, I could have sworn it worked once. I could never reproduce it. Then, today, I got it to work once. I can failover from Server1 to Server2 (or vice versa) one time. The linked server will work after a failover, but if I failover back to the original server, it stop working. A restart of the SQL Server service on the server with the linked server will let it work again (for 1 failover). Letting things sit overnight must work too since it worked the first time I tried it today.
Creating a new linked server (with a different name) doesn't work, so I'm guessing that the SQL Native Client driver is caching something.
Another Update
I did get the linked server (as described above) to work correctly on a SQL Server 2012 Express instance. Then, I tried it on another SQL Server 2012 Standard instance. It did not work. One thing that might be relevant is that Server1 and Server2 are in a different untrusted domain than Server3. But, since I'm using sql server authentication, I wouldn't think it should. The Express instance that works fine is in the same untrusted domain.
