Solved

SQL server AlwaysOn Availability Groups Read-Only Routing

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

Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

Question has a verified solution.

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

A procedure for exporting installed hotfix details of remote computers using powershell
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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