36 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.

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