Need help on Configure linked server with Failover partner

  • Hi,

    Firstly, I don't to it is possible to create Linked server with Failover partner option. I can query when primary server and getting the error when I set the DB Fail over. I have tried with following script and also gone through different sources, but failed. Please see the script and error below.

    Please help on this.

    EXEC master.dbo.sp_addlinkedserver

    @server = N'MIRRORLink',

    @srvproduct=N'',

    @provider=N'SQLOLEDB',

    @provstr=N'Server=primary;FailoverPartner=mirror;network=dbmssocn;',

    @catalog='mydb'

    go

    --EXEC master.dbo.sp_serveroption @server=N'MIRRORLink', @optname=N'connect timeout', @optvalue=N'120'

    go

    EXEC master.dbo.sp_addlinkedsrvlogin

    @rmtsrvname='MIRRORLink',

    @useself='False',

    @rmtuser='user',

    @rmtpassword='password'

    select * from MIRRORLink.mydb.dbo.tablename

    OLE DB provider "SQLNCLI11" for linked server "mirrorlink" returned message "Login timeout expired".

    OLE DB provider "SQLNCLI11" for linked server "mirrorlink" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.".

    Msg 258, Level 16, State 1, Line 0

    TCP Provider: The wait operation timed out.

    Msg 18456, Level 14, State 1, Line 1

    Login failed for user 'user'.

    Msg 4060, Level 11, State 1, Line 1

    Cannot open database "mydb" requested by the login. The login failed.

    🙂

  • You could try to wrap the code in a TRY...CATCH block. In the TRY part you define the linked server to the primary instance. If this fails it will jump to the CATCH block. And in the CATCH part you can specify the linked server to the partner instance.

    The downside is that it will first try the primary instance and will wait untill the timeout message before it continues to the partner instance.

    ** Don't mistake the ‘stupidity of the crowd’ for the ‘wisdom of the group’! **
  • This is nice solution. But form my point I am using this in Lots of SPs and functions and need to rewrite every thing. Is it not possible to give failover connection in Linked Server? If not possible, do we have any other simplest solution to solve this even the the given solution is the Best I felt.

    🙂

Viewing 3 posts - 1 through 2 (of 2 total)

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