Solved

SQL server AlwaysOn Availability Groups Read-Only Routing

Posted on 2016-07-21
2
95 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
2 Comments
 
LVL 26

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Resolve DNS query failed errors for Exchange
This tutorial will walk an individual through the process of configuring their Windows Server 2012 domain controller to synchronize its time with a trusted, external resource. Use Google, Bing, or other preferred search engine to locate trusted NTP …
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

762 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now