none
Linked server with alias name is working on certain database server only, linked server connection string included a parameter failoverPartner RRS feed

  • Dotaz

  • Primary DB Server: ServerA
    Mirrored DB Server: ServerB
    Database: MyDatabase
    others DB server: ServerC, ServerD, ServerE and ServerF
    Login Type: SQL authentication - SQLuserAdded primay and Mirrored DB server as a linked server on DB server ServerC, ServerD, ServerE and ServerF

    The SeverA and ServerB is setup as a linked server on ServerC, ServerD, ServerE and ServerF. These servers will pulling data from linked server MyDatabase. I included a parameter failoverPartner in linked server connection string. The value which pass in to failoverPartner is an alias name from SQL Native client configuration.

    Here is the test result of failover.
    1. ServerC - working (no matter how many times MyDatabase failover)
    2. ServerD - working (no matter how many times MyDatabase failover)
    3. ServerE - failed (it only works when MyDatabase failover on first time but after that, it failed to connect)
    4. ServerF - failed (it only works when MyDatabase failover on first time but after that, it failed to connect)

    I am using scripting as below to applied on server ServerC, ServerD, ServerE and ServerF.

    EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'', @provider=N'SQLNCLI', @datasrc=N'ServerA', @provstr=N'Server=ServerA;FailoverPartner=ServerB;User ID=SQLuser;', @catalog=N'MyDatabase'


    I tried to troubleshoot as below

    1. "Test Connection" on linked server and error message as below:


    2. SQL Server Log from Primary Server - ServerA, it shown that SQLuser was trying to connect when MyDatabase failover to Mirrored Server: ServerB.

    I am run out of idea to find the root cause and solve this issue. Anyone has idea to solve it?

    pátek 5. července 2019 7:22

Všechny reakce