Linked server latency

Question

Linked server latency

Randy Rabin

SSCommitted

Points: 1512
More actions
September 11, 2003 at 12:58 pm

#76051

Hi,
I've noticed that when changing the datasource of a linked server, that there's a delay of several minutes before the change actually takes place.
Reason I want to do this is for failover: server A runs cross-server queries against server B, but if B fails then I want it to automatically hit C instead. (the real names are similar to "PServer1", "PServer2" with failover "PServerBak\PServer1", "PServerBak\PServer2", so I can't just flip IP addresses)
I do this now by updating master..sysservers (sandwiched between calls to sp_configure to allow direct updates; I ran Profiler and basically copied the SQL that EM generates). But the problem is that the update is not immediate, and that distributed queries hitting B continue to hit it minutes after it goes down even when sysservers points to C.
Any ideas on a way to make this happen faster?
Thanks!
Randy Rabin

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

rmattaway SSC Enthusiast Points: 167 More actions · Answer 1

Why not take a different route and add an additional linked server to the second failover server. Then instead of using the procedure to change the linked server use dynamic sql in the procedure that retrieves the data to get the data from any server.

Then if you bring 20 servers online you could failover instantly to any of them without having to update the linked servers via the sp_

I hope that helps.

Randy Rabin SSCommitted Points: 1512 More actions · Answer 2

rmattaway,

Good suggestion ... except that we also have views (over 80 of them) that would also need this logic. Not sure how a view could dynamically figure out which linked server to query from.

Randy

Eve Stelzner Old Hand Points: 382 More actions · Answer 3

You may have to clone the views (one set for one linked server) and (one set for the failover linked server) Then add logic to the

procedure to check if one server is up then call version 1 of the view and if the other server is up use version 2 of the views. You are correct, views are not like dynamic sql and can not be changed on the fly.

Good luck,

Eve

gljjr SSChampion Points: 10080 More actions · Answer 4

I would just drop the server and recreate it. I would think that would be the best route to go.



DECLARE @LinkedServerName     sysname

    , @provstr                nvarchar(4000)

    , @ServerName             sysname



-------------------------------------------------------------------------------

-- Change the following to the remote servername!

SET @ServerName = 'SERVERNAME'

-------------------------------------------------------------------------------



SELECT @LinkedServerName =  'ReplicationServer_' + @ServerName

    , @provstr = 'DRIVER={SQL Server};SERVER=' + @ServerName + ';UID=USERNAME;PWD=PASSWORD;'



-- Drop the server if it exists

IF EXISTS(SELECT * FROM master.dbo.sysservers WHERE srvname = @LinkedServerName)

    EXEC sp_dropserver @LinkedServerName



-- If we don't have the login for the LexMirrorUser create it.

IF NOT EXISTS(SELECT * FROM master.dbo.syslogins WHERE name = 'LexMirrorUser')

EXEC sp_addlogin 'LexMirrorUser', 'lexmirr0ruser', 'ReplJob'



-- Now Add the Linked Server

EXEC sp_addlinkedserver 

   @server = @LinkedServerName 

   , @provider = 'MSDASQL'

   , @provstr = @provstr

   , @srvproduct = 'any' 



-- Now you would need to set up the user permissions...

EXEC sp_addlinkedsrvlogin 'ServerName', 'false', 'RemoteUser', 'RmtUserName' , 'RmtUserPassword'

Gary Johnson

Microsoft Natural Language Group

DBA, Sr. DB Engineer

Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer

This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.

Randy Rabin SSCommitted Points: 1512 More actions · Answer 5

stelze and gljjr,

Thanks for the advice. Dropping and recreating the linked server works well (takes effect immediately) so I can go that route. I was hoping to not have to hardcode a password into the proc (although I have ways around that too...) but there appears to be no alternative.

Thanks again,