SQL 2008 - Linked Server to Mirrored pair, post fall-over first connection attempt login failure issue.
1 เมษายน 2554 15:48Good Morning All,Grappling with a linked server “Login Failed” first attempt post fall over. The first call to the linked server of a mirrored database results in login failure for the user ‘x’ and does so rapidly (e.g. the login isn’t timing out). A second identical call to the linked server responds successfully. This presents a rather steep hurtle to overcome, from a coding prospective for one has to determine the nature of an error for EVERY CALL to the linked sever then try again, preferably only ONCE to mitigate this behavior.It is understandable for the host instance to report an issue. It would be advantageous for the databases to perform this double-kiss (work out the fall over in the background) then to customize the code to mitigate this issue when interacting with mirrors through linked server.Started checking for others that may have experienced this and didn’t mention of the issue. Any assistance is always appreciated.DetailsEstablished a witness mirror on SQL 2008 instances (v10.0.4000). From a third server added a linked server to the mirrored pair. On the mirrors created a login with the default database, something other then the mirror (in this case the master database), noting the SID is identical between the two instances for this user. To keep things really simple made the user a system admin.Added linked server to the mirror, tested to find the following one time error:Msg 18456, Level 14, State 1, Line 1Login failed for user 'lsTestUser'.Msg 4060, Level 11, State 1, Line 1Cannot open database "M01" requested by the login. The login failed.Second attempt is successful.Instigate a fall over via alter database (or the GUI).Re-execute select to find the same login failure message followed by second attempt being successful.Esoteric Details:
CREATE LOGIN [susrLS08] WITH PASSWORD=N'Password08' , SID=0x01002319A7BF4CDED0A1234BFC07C3BB , DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF; GO EXEC master..sp_addsrvrolemember @loginame = N'susrLS08' , @rolename = N'sysadmin'; GO exec sp_addlinkedserver @server = N'MirrorTest' , @srvproduct = N'SQLOLEDB' , @provider = N'SQLOLEDB' -- N'SQLNCLI' , @provstr = N'Server=m0801,10001; FailoverPartner=m0802,10002;' , @catalog = 'M01'; go exec sp_addlinkedsrvlogin @rmtsrvname = 'MirrorTest' , @useself = false , @locallogin = NULL , @rmtuser = 'susrLS08' , @rmtpassword = 'Password08'; go SELECT TOP 10 [fld01] , [fld02] FROM [MirrorTest].[M01].[dbo].[tblTestDS]; go
2 เมษายน 2554 9:09This is bit strage first time not connecting and second time working. Any specifc errors in sql / event error log. have you got any issues with mirror configuration.
4 เมษายน 2554 17:13
I may have missed something in the description above, but how quickly did you try to use the linked server after the failover? If you wait 1 minute (or perhaps longer) after the failover, does the linked server connection work first time?
Linked servers are external services, so I just wonder if it needs some time to finish establishing the link. What does your testing suggest?
14 เมษายน 2554 11:10Agree with Russell, you might wait atleast 10 sec to 1 min after failover kick off.
3 พฤษภาคม 2554 20:06We're having the exact same issue. Might-O, have you resolved this yet? I even waited for for over 3mins to ensure that failover was finished it's thing.
9 พฤษภาคม 2554 19:47Any updates please
6 มกราคม 2555 14:58
Sorry I let this thread slip so long.
I have not unearthed a solution to the issue. Current implementation is polling, which I despise because it is ugly...