Configuring a Readable Secondary Routing in SQL Server 2012 AlwaysOn

In my previous job in support I had a lot of questions from customers about High Availability and load balancing in SQL Server Databases, so when I saw the announcement of SQL Server 2012 AlwaysOn, it caught my attention and I decided to learn about it and how to use the Readable Secondary.

My idea is to use one of my servers as the primary doing all the read/write operations and take advantage of the secondary to perform all the read operations, in the applications that only read data I will change the connection string to use the new modifier that allows to specify the intent of the application as read-only.

To make it simple I configured two Windows Server machines in a Windows Cluster called WindowsCluster, and used the Management Studio Availability Group Wizard to create a SQL AlwaysOn Availability Group on top of my Windows Cluster called SQLAvailabilityGroup.

This Availability Group will group my SQL Server Instances MyServer1 and MyServer2 and will expose an endpoint called MyVirtualServer (my clients are supposed to connect to MyVirtualServer)

I used the defaults except for the Replicas and the Listener.

For the Replicas I specified Synchronous to ensure my data is consistent between failovers and Readable Secondary as Read-intent only to ensure I can connect to the secondary to read data.

For the Listener I allowed the wizard to create the endpoint for me, in this case will be MyVirtualServer with the default SQL Server port which is 1433.

When I finished the first thing I tried was to test the ReadOnly, so I used SQLCMD which now supports the –K keyword to specify the application intent

Failover

One thing that is true for any application that relies in a Cluster or any kind of Load Balancing or technology that mask a set of servers behind a Virtual Name is that the applications need to implement some kind of retry when a failover is in progress or at least handle gracefully the transient outage of the server, so I wanted to ensure my application keeps working after multiple failovers.

To be able to execute the failover command the session need to be in the Secondary,, to figure out which one is the Secondary I used the DMVs sys.dm_hadr_availability_replica_states and sys.availability_replicas.

Knowing the secondary, I can connect to it and execute the failover command (ALTER AVAILABILITY GROUP [SQLAvailabilityGroup] Failover), I did not find a clear way to do it in SQLCMD so I put together this PowerShell script to perform the failover each 900 seconds (15 minutes)

I ran my application during a long period of time and monitored it to ensure it was not leaking resources or stop to work because the multiple failovers I was introducing.