none
Failure of "Loopback" linked server while mirroring

    Pregunta

  • Hi,

    We have seen this issue now on two seperate client systems.

    We have a pair of SQL 2008 R2 servers with mirrored databases hosted on them in high safty mode and automatic failover with a witness server.

    We need cross database access for some objects in the databases, i.e. we have a shared database that is used by a number of others. To do this we have setup a linked server to connect to the shared databse using the SQLNCLI10 provider, I've included an example of the T-SQL to create these linked servers at the end. This is working well most of the time.

    Sometimes when the target database of the linked server fails over the linked server willl stop working, returning the following error...

    Login failed for user 'sa'.

    Cannot open database "<Database Name>" requested by the login. The login failed.

    OLE DB provider "SQLNCLI10" for linked server "<LinkedServerName>" returned message "Invalid connection string attribute". (Microsoft SQL Server, Error: 18456)

    Failing the target database over and back again fixes this issue but this is a manual task and our systems are partially offline until we have done this.

    Has anyone seen this behaviour before? Any suggestions on how to diagnose or fix it?

    Thanks in advance

    Paul Barrett

    EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServerName', @srvproduct=N'.', @provider=N'SQLNCLI10', @provstr=N'Server=<Server1\Instance>;Failover Partner=<Server2\Instance>;UID=a', @catalog=N'<Database Name>'
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServerName',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'collation compatible', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'rpc', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'rpc out', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'collation name', @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'use remote collation', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'LinkedServerName', @optname=N'remote proc transaction promotion', @optvalue=N'true'
    GO
    lunes, 20 de febrero de 2012 13:06

Todas las respuestas

  • does SA password and SIDs are same on both principal and mirroring db servers?

    http://uk.linkedin.com/in/ramjaddu

    lunes, 20 de febrero de 2012 18:16
  • Hi Paul Barrett,

    Could you please have a try to change ‘Failover Partner’ to ‘Failover_Partner’? In addition, please make sure the current login is existed with the same password on the secondary server in your scenario. Please have a look at this similar thread: Linked server conn to DB.

    TechNet Subscriber Support
    If you are TechNet Subscription user and have any feedback on our support quality, please send your feedback here.


    Stephanie Lv

    TechNet Community Support

    martes, 21 de febrero de 2012 3:33
  • Hi Guys,

    Thanks for your responses.

    I've check all the SQL users involved and they are correct and most of the time this setup work regardless of which SQL Server is hosting the principle of the databases.

    I've created a test linked server with the 'Failover_Partner' attribute and will monitor to see if this has made a difference. Although we can't identify what is triggering this failure, appart from after a failover but not all failovers, and can't reproduce it at will so will have to just monitor.

    I will update when I know the outcome of this test.

    Thanks

    Paul

    martes, 21 de febrero de 2012 8:40