Solved

SQL server AlwaysOn Availability Groups Read-Only Routing

Posted on 2016-07-21
2
143 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This tutorial will walk an individual through the process of transferring the five major, necessary Active Directory Roles, commonly referred to as the FSMO roles from a Windows Server 2008 domain controller to a Windows Server 2012 domain controlle…

830 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