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.
What am I missing?
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.
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.