Monday, October 27, 2014

2 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

2 comments:

 

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