SQL AlwaysOn and other cluster types - failover client connections?

Hi
New to failover and clustering, but got a question -

If there are two SQL servers- SQL01 and SQL02, with alwayson set up for failover.  If/When the first server dies, how do clients connect to the second?  If an application is pointed to an ODBC connection that's pointing to SQL01, if that server goes down, although it's now running on SQL02, what's the procedure to have the client connections automatically point to the new server?

Again, please excuse the ignorance

Thanks!
Mystical_IceAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

rsmith1969Commented:
You should have your ODBC connection pointing to the failover cluster name.

Example:  SQL01 & SQL02 are setup in a Microsoft failover OS cluster and then SQL is installed on SQL01 as the primary and SQL is then installed on SQL02 as the failover node.  During the install you'll be asked for a SQL failover name and IP address for the SQL failover cluster.

Then when the OS or SQL fails it will failover to the other system and you ODBC won't be aware because it points to the cluster name.
1
Mystical_IceAuthor Commented:
OK that makes sense - then what does the "listener group" do and what's its value?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
On a SQL Server clustered instance you point to the MSSQL Instance Network Name.
On a SQL Server AlwaysOn Group you need to use the listener name instead of the instance name, so in case of a failover the listener name will answer from the secondary replica.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Mystical_IceAuthor Commented:
What happens if the listener goes down along with the primary SQL server?  can you have multiple listeners?
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
What happens if the listener goes down along with the primary SQL server?
Will be available in the secondary server. So no need to worry about it.

can you have multiple listeners?
Yes. With AlwaysOn is usual to have at least 2 listeners. One to point to the primary replica for read/write permissions and another listener that points to the secondary replica only for reporting purposes (read only).
0
Mystical_IceAuthor Commented:
SO the actual availability group is set up with windows file clustering - if there are two SQL servers, there will be a cluster with those two servers in them.  If there are 2 listeners, how are they set up?  Are they a member of the same cluster?
0
Mystical_IceAuthor Commented:
And it looks like I would need to create a listener in SQL AlwaysOn (via the SSMS) as well as a 'client access point' in the windows cluster manager?
0
Mystical_IceAuthor Commented:
In this page where it describes setting up alwayson, it walks through creating a Windows server file cluster node, and adding it to the cluster, but what does it do?  It's not a listener, it's just another part of the cluster and has no data replicated to it?

https://msdn.microsoft.com/en-us/library/azure/dn249504.aspx?f=255&MSPPError=-2147217396
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
So, you're working on Azure. I don't have experience on that but doesn't seems to me there's difference from a physical environment.

If there are 2 listeners, how are they set up?  Are they a member of the same cluster?
You need to forget about the Windows Cluster. It's only necessary for AG because of the Quorum. You can read more about it here.
The listeners are set for each AG. For Azure it may have some differences so if you want to read morea about it, check this article.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.