Monday, October 27, 2014

13 SQL2014:AlwaysOn High availability group-Readable secondary

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.

image

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.

image

   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.

image

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.

image

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:

image

image

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
image

Test read only routing

From SSMS, we can add the connection parameter.

   1: Server=tcp:pandasql1,8001;Database=AdventureWorks2014;Integrated Security=SSPI;ApplicationIntent=ReadOnly;

image

From sqlcmd, we can use –K readonly

image

Reference

http://blogs.msdn.com/b/mattn/archive/2012/04/25/calculating-read-only-routing-url-for-alwayson.aspx

13 comments:

  1. Are you looking to make cash from your visitors by running popunder ads?
    If so, have you tried using PopCash?

    ReplyDelete
  2. myTectra Placement Portal is a Web based portal brings Potentials Employers and myTectra Candidates on a common platform for placement assistance

    ReplyDelete
  3. Hello! 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.



    AWS 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

    ReplyDelete
  4. 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.
    python course in pune
    python course in chennai
    python course in Bangalore

    ReplyDelete
  5. 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.
    excel advanced excel training in bangalore | Devops Training in Chennai

    ReplyDelete

 

SQL Panda Copyright © 2011 - |- Template created by O Pregador - |- Powered by Blogger Templates