With AlwaysOn availability group, we can create the readable secondary to offload the query load on the primary. This post is the walk through how to set up the readable seconday.
There were three options in the readable secondary configuration.
- NO: default, no readable secondary. The secondary database is not accessible.
- Read-intent Only: The secondary database is accessible when use keyword , Applicationintent=ReadOnly in the connection string.
- Yes: User can connect to the database without Applicationintent=ReadOnly in the connection string but only allow read only actions.
1: USE [master]
2: GO
3: ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo]
4: MODIFY REPLICA ON N'SGC8\SGC8SQL2014' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL))
5: GO
6: USE [master]
7: GO
8: ALTER AVAILABILITY GROUP [pandaAlwaysOnDemo]
9: MODIFY REPLICA ON N'SGC5\SGC5SQL2014' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY))
10: GO
The SGC8\SGC8SQL2014 instance is set to YES, therefore, we can directly connect to the database without issue.
However, SGC5\SGC5SQL2014 is still not accusable through the normal connection. Even we specify the READONLY in the application intent , we still end up with primary ( SGC4). In order to utilize the application intent, we have to configure the READ ONLY routing first.
Configure read only routing
The key point to configure the READ ONLY routing is READ_ONLY_ROUING_URL. If the port number is not configure properly, you will get all kind of strange error such as below:
This post has great script to identify the correct port number and below is the script to create the read only routing list.
-- configure routting URL
ALTER AVAILABILITY GROUP pandaAlwaysOnDemo MODIFY REPLICA ON N'SGC4\SGC4SQL2014'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://SGC4.stargate.com:8001'));
GO
ALTER AVAILABILITY GROUP pandaAlwaysOnDemo MODIFY REPLICA ON N'SGC5\SGC5SQL2014'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://SGC5.stargate.com:8001'));
GO
ALTER AVAILABILITY GROUP pandaAlwaysOnDemo MODIFY REPLICA ON N'SGC8\SGC8SQL2014'
WITH (SECONDARY_ROLE (READ_ONLY_ROUTING_URL='TCP://SGC8.stargate.com:8001'));
GO
--- configure routing list
ALTER AVAILABILITY GROUP pandaAlwaysOnDemo
MODIFY REPLICA ON N'SGC4\SGC4SQL2014'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SGC5\SGC5SQL2014','SGC8\SGC8SQL2014')));
GO
ALTER AVAILABILITY GROUP pandaAlwaysOnDemo
MODIFY REPLICA ON N'SGC5\SGC5SQL2014'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SGC8\SGC8SQL2014','SGC4\SGC4SQL2014')));
GO
ALTER AVAILABILITY GROUP pandaAlwaysOnDemo
MODIFY REPLICA ON N'SGC8\SGC8SQL2014'
WITH (PRIMARY_ROLE (READ_ONLY_ROUTING_LIST=('SGC5\SGC5SQL2014','SGC4\SGC4SQL2014')));
--- List routing list
select * from sys.availability_read_only_routing_lists
GO
SELECT ag.name as "Availability Group", ar.replica_server_name as "When Primary Replica Is",
rl.routing_priority as "Routing Priority", ar2.replica_server_name as "RO Routed To",
ar.secondary_role_allow_connections_desc, ar2.read_only_routing_url
FROM sys.availability_read_only_routing_lists rl
inner join sys.availability_replicas ar on rl.replica_id = ar.replica_id
inner join sys.availability_replicas ar2 on rl.read_only_replica_id = ar2.replica_id
inner join sys.availability_groups ag on ar.group_id = ag.group_id
ORDER BY ag.name, ar.replica_server_name, rl.routing_priority
GO

Test read only routing
From SSMS, we can add the connection parameter.
1: Server=tcp:pandasql1,8001;Database=AdventureWorks2014;Integrated Security=SSPI;ApplicationIntent=ReadOnly;
From sqlcmd, we can use –K readonly
Reference
http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson.aspx
Great article.
ReplyDeleteAtex-puhelin
kouluratsastus
Lemmikin tuhkaus
LVI-asennus Espoo
Kosmetologi Kuopio
Rasvaimu
Sähkömies
Isännöinti Espoo
talopaketti
varasto
Videotuotanto
työyhteisön kehittäminen
varainhankinta
Great Article Artificial Intelligence Projects
DeleteProject Center in Chennai
JavaScript Training in Chennai
JavaScript Training in Chennai
Are you looking to make cash from your visitors by running popunder ads?
ReplyDeleteIf so, have you tried using PopCash?
myTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance
ReplyDeletethis is very detailed and informational post. Thanks a lot. gunship battle mod temple run 2 mod mobile legends mod
ReplyDeleteHello! This is my first visit to your blog! We are a team of volunteers and starting a new initiative in a community in the same niche. Your blog provided us useful information to work on. You have done an outstanding job.
ReplyDeleteAWS Training in Bangalore | Amazon Web Services Training in Bangalore
AWS Training in Bangalore |Best AWS Training Institute in BTM ,Marathahalli
AWS Training in Rajaji Nagar | Amazon Web Services Training in Rajaji Nagar
I am really happy with your blog because your article is very unique and powerful for new reader.
ReplyDeleteClick here:
selenium training in chennai
selenium training in bangalore
selenium training in Pune
selenium training in pune
Selenium Online Training
I would like to thank you for your nicely written post, its informative and your writing style encouraged me to read it
ReplyDeleteCore Java interview questions and answers
Java training in Chennai | Java training in Tambaram
Java training in Chennai | Java training in Velachery
Java training in Chennai | Java training in Omr
This is good site and nice point of view.I learnt lots of useful information.
ReplyDeleteData Science training in rajaji nagar | Data Science Training in Bangalore
Data Science with Python training in chennai
Data Science training in electronic city
Data Science training in USA
Data science training in pune
This is most informative and also this post most user friendly and super navigation to all posts... Thank you so much for giving this information to me.
ReplyDeletebest rpa training in chennai
rpa training in chennai |
rpa online training
rpa course in bangalore
rpa training in pune
rpa interview questions and answers | blueprism interview questions and answers
Great post! I am actually getting ready to across this information, It’s very helpful for this blog.Also great with all of the valuable information you have Keep up the good work you are doing well.
ReplyDeletepython course in pune
python course in chennai
python course in Bangalore
The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
ReplyDeleteexcel advanced excel training in bangalore | Devops Training in Chennai
The knowledge of technology you have been sharing thorough this post is very much helpful to develop new idea. here by i also want to share this.
ReplyDeleteangularjs Training in marathahalli
angularjs interview questions and answers
angularjs Training in bangalore
angularjs Training in bangalore
angularjs Training in chennai
automation anywhere online Training
I am really happy with your blog because your article is very unique and powerful for new reader.
ReplyDeleteClick here:
selenium training in chennai | selenium course in chennai
selenium training in bangalore | selenium course in bangalore
selenium training in Pune | selenium course in pune | selenium class in pune
selenium training in Pune | selenium course in pune | selenium class in pune
selenium online training | selenium training online | online training on selenium
ReplyDeleteI feel very grateful that I read this. It is very helpful and very informative and I Python classes in pune really learned a lot from it.
I Got Job in my dream company with decent 12 Lacks Per Annum salary, I have learned this world most demanding course out there in the current IT Market from the python training in pune experts who helped me a lot to achieve my dreams comes true. Really worth trying.
ReplyDeleteI learned World's Trending Technology from certified experts for free of cost. I Got a job in decent Top MNC Company with handsome 14 LPA salary, I have learned the World's Trending Technology from hadoop training in btm experts who know advanced concepts which can help to solve any type of Real-time issues in the field of Hadoop. Really worth trying
ReplyDelete
ReplyDeleteغسيل خزانات بمكة افضل شركة غسيل خزانات بمكة
غسيل خزانات بجدة افضل شركة غسيل خزانات بجدة
غسيل خزانات بالدمام افضل شركة غسيل خزانات بالدمام
Stunning! Such an astonishing and supportive post this is. I incredibly love it. It's so acceptable thus wonderful. I am simply astounded.data science bootcamp malaysia
ReplyDeleteI looked at some very important and to maintain the length of the strength you are looking for on your website
ReplyDeletedata science course in Delhi
ReplyDeleteSimply the manner in which I have anticipated. Your site truly is intriguing.
hrdf training course
I was looking at a portion of your posts on this site and I consider this site is really enlightening! Keep setting up..
ReplyDelete360DigiTMG supply chain analytics training
ReplyDeleteAmazing Article ! I would like to thank you for the efforts you had made for writing this awesome article.
Thanks for sharing such a nice info.I hope you will share more information like this. please keep on sharing!
internship in chennai
internship in chennai for cse
internship for mba in chennai
internship in chennai for hr
internship in chennai for mba
companies for internship in chennai
internship in chennai for ece
paid internship in chennai
internship in chennai for biotechnology
internship in chennai for b.com students
ReplyDeleteI would prescribe my profile is critical to me, I welcome you to talk about this point... hrdf claimable
Amazing Article, Really useful information to all So, I hope you will share more information to be check and share here.
ReplyDeleteInplant Training for cse
Inplant Training for IT
Inplant Training for ECE Students
Inplant Training for EEE Students
Inplant Training for Mechanical Students
Inplant Training for CIVIL Students
Inplant Training for Aeronautical Engineering Students
Inplant Training for ICE Students
Inplant Training for BIOMEDICAL Engineering Students
Inplant Training for BBA Students
Hi to everybody, here everyone is sharing such knowledge, so it’s fastidious to see this site, and I used to visit this blog daily
ReplyDeletedata science training in hyderabad
"Thank you very much for your information.
ReplyDeleteFrom,
"data scientist courses in gurgaon
Thanks for Sharing This Article.It is very so much valuable content. I hope these Commenting lists will help to my website
ReplyDeleteartificial intelligence course in yelahanka