1

I am a developer trying to work with a new Sql Server 2016 Always On cluster.

In reading documentation I am getting conflicting information.

  1. Some documentation tells me that I just need to add "failover partner=MySecondaryServer" to my connection string.
  2. Other sites talk about using an "Listener". And in my Always On section in SSMS 2016 there is an Availability Group Listeners section.

What is the difference between these options?

Are these competing options (meaning you pick just one) or do they work together?

Notes: I have tried the "failover partner" and got limited success. When the primary rebooted, I had to restart my Entity Framework service before it "failed over" to the secondary server. I am thinking about setting up a Listener to see if it will work more dynamically.

Improve this question

1 Answer 1

Reset to default
4

Failover partner is for when you are using database mirroring, you should not use it for Availability Groups. While it, strictly speaking, will work, it will only do so when the primary server is offline, and is not designed for this kind of scenario.

Using a listener within an Availability Group is the way to go. This is a virtual network resource that will point you to the correct primary (and is required in the event that you want to use read-routing at any point in the future). Using a listener you should not have to perform an app restart to have client redirection, it should just work.

Improve this answer
2
  • Some of the terminology can be confusing, it's quite easy to get lost in connecton strings.
    – Nic
    Commented Apr 12, 2017 at 18:15
  • Failover Partner works ok with AGs not only when the primary server is offline but always. Not using a separate listener for each AG is common when there are thousands of DBs each with separate AG so you don't need thousands of IPs to assign to listeners. You just set Data Source to SQL\INSTANCE of the primary replica and Failover Partner to MIRROR\INSTANCE of the secondary replica and this is a valid use case when there are only two synchronous replicas -- a very useful HADR scenario that emulates db mirroring closely and is very cheap to setup and maintain.
    – wqw
    Commented Sep 29, 2020 at 10:56

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.