I am a developer trying to work with a new Sql Server 2016 Always On cluster.
In reading documentation I am getting conflicting information.
- Some documentation tells me that I just need to add "failover partner=MySecondaryServer" to my connection string.
- 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.
Best Answer
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.