locked
How could a linked server "server object" that is at the instance level use a failover partner that is at the database level? RRS feed

  • Question

  •      I had someone ask for a linked server the other day and I thought it would be simple enough until I drilled down to the details. The server that is being linked to has multiple databases that have failover partners; and they are different failover partners.

         So I started to set up the linked server, which is a "server object", and I gave it a @catalog like one would expect and when I connected, I didn't see just that one "catalog" ie database, but all the databases the login has access to.

         So that got me thinking, this is a server object, not a database object.  So how on earth would a server level object deal with two databases that have different failover partners?  I don't think it can.

    So here are my questions:

    1. Can a "linked server" take advantage of a database level failover partner?  PLEASE only answer if you've done this.  I don't need a theoretical answer.  My question is more theoretical than the actual code to set the partner string.

    2. How would I enter a second, unique failover partner for a second database?

    Thanks,

    Keith

    Tuesday, September 8, 2015 5:07 PM

Answers

  • The not so simple answer here is: A linked server is a server level object, that's true. But from a logical point of view it really is like a connection string on an application. While it physically connects you to a server (and not a database) it is really meant to primarily get you to a database. So if a server does have multiple DBs that you have access to the connection will show all of them. BUT if you want to connect to different DBs over on the other server what you really should do is have one LinkedServer for each DB you need. That (and only that) will allow you to specify multiple different FailoverPartner attributes.

    You of course can reduce it to have one LinkedServer for each Server+FailoverPartner pair... But I would strongly suggest to have one per DB, otherwise chances are very high that you will at some point have useless rewrites in your queries.

    Tuesday, September 8, 2015 6:26 PM

All replies

  • 1. Can a "linked server" take advantage of a database level failover partner? 

    For mirroring, external linked servers needs to connect to the right primary server. When primary node failover to secondary node, linked server should linked to the correct active partner. Failover partner clause enables this.

    2. How would I enter a second, unique failover partner for a second database?

    https://technet.microsoft.com/en-us/library/cc917713.aspx#EEAA


    Hope this helps ! ------------------------------------------------------Please Mark This As Answer if it solved your issue. Please Vote This As Helpful if it helps to solve your issue

    Tuesday, September 8, 2015 5:29 PM
  • I don't know that I get your answers.

    1.  Why would a server level object even use a database level object of a failover partner?

    2.  Your #2 wasn't an answer it was a link on Mirroring.  I need info on linked servers in a mirroring construct, not general mirroring info.

    3.  I'm really looking for a "I've done this and it works or doesn't work" perspective rather than what the text books say.

    I can't fathom the textbooks being correct; a server level object can't fathom two children having different failover partners.  Please prove me wrong!!!

    Tuesday, September 8, 2015 6:12 PM
  • The not so simple answer here is: A linked server is a server level object, that's true. But from a logical point of view it really is like a connection string on an application. While it physically connects you to a server (and not a database) it is really meant to primarily get you to a database. So if a server does have multiple DBs that you have access to the connection will show all of them. BUT if you want to connect to different DBs over on the other server what you really should do is have one LinkedServer for each DB you need. That (and only that) will allow you to specify multiple different FailoverPartner attributes.

    You of course can reduce it to have one LinkedServer for each Server+FailoverPartner pair... But I would strongly suggest to have one per DB, otherwise chances are very high that you will at some point have useless rewrites in your queries.

    Tuesday, September 8, 2015 6:26 PM