Solved

SQL server AlwaysOn Availability Groups Read-Only Routing

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

Netscaler Common Configuration How To guides

If you use NetScaler you will want to see these guides. The NetScaler How To Guides show administrators how to get NetScaler up and configured by providing instructions for common scenarios and some not so common ones.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
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…
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 …

831 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