Solved

SQL server AlwaysOn Availability Groups Read-Only Routing

Posted on 2016-07-21
2
245 Views
Last Modified: 2016-07-22
Hi Experts,

I would like to configure SQL Server 2014 AlwaysOn Availability Groups Read-Only Routing in my project for bank domain servers. How can we configure the secondary replicas to allow read-only workloads? And, how do we configure SQL Server to automatically redirect the read-only workloads after a failover. I am referring the below url . Also i am looking for any other supported documents, steps and reference urls. Please provide me. Thanks.

https://www.mssqltips.com/sqlservertip/2869/configure-sql-server-2012-alwayson-availability-groups-readonly-routing-using-tsql/

Regards,
Sreenivasa
0
Comment
Question by:tschary
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 27

Accepted Solution

by:
Zberteoc earned 500 total points
ID: 41724698
Read this:

https://blogs.msdn.microsoft.com/alwaysonpro/2013/07/01/end-to-end-using-a-listener-to-connect-to-a-secondary-replica-read-only-routing/

https://technet.microsoft.com/en-ca/library/hh710054(v=sql.110).aspx

It is really simple. There are those ALTER commands where you will specify what are the read only nodes, you will list all the secondary nodes, and than you set them as routing targets. You have to run the commands on all nodes that can take the role of primary and list what nodes would be as secondary if that node takes the role of primary. This way the application intent will know where to point to in the case of fail over, be it manually or automated.

In the application it is very simple to connect by adding only one parameter to the connection string: application intent=readonly. Here is a VBScript example that will test what server are you connecting to:

'connectionString = "Provider=SQLNCLI11;Data Source=ALWAYSON_LISTENER;Integrated Security=SSPI;initial catalog=RptaReportData"
connectionString = "Provider=SQLNCLI11;Data Source=ALWAYSON_LISTENER;Integrated Security=SSPI;initial catalog=RptaReportData;application intent=readonly"
Set conn = CreateObject("ADODB.Connection")
Set com = CreateObject("ADODB.Command")
'on error 
conn.Open connectionString
set com.ActiveConnection = conn
com.CommandText = "SELECT top 1 @@SERVERNAME+' - '+col from zb_test"
set rs = com.Execute
while not rs.eof
    msgbox CStr(rs.Fields(0))
    rs.movenext()
wend
conn.close 

Open in new window

First connection will take you to the primary while the second connection will take you to the secondary.

When it fails over the read_only will go to the first available online secondary node from the list that was provided, if you have multiple secondaries. If you have a configuration with only 2 nodes, one primary and one secondary, if the primary fails then the secondary will take over and become primary, in which case both connections will go to the primary the only active node left.
1
 

Author Closing Comment

by:tschary
ID: 41724740
I have gone through the urls and implemented. it is working fine. Thanks for support.
0

Featured Post

Does Powershell have you tied up in knots?

Managing Active Directory does not always have to be complicated.  If you are spending more time trying instead of doing, then it's time to look at something else. For nearly 20 years, AD admins around the world have used one tool for day-to-day AD management: Hyena. Discover why

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question