Partager via

Configuring a Readable Secondary Routing in SQL Server 2012 AlwaysOn

  • Article

 

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.

I am using the RC0 version of SQL Server that is available here

I am not going through the details on how to configure a Availability Group , detailed steps are on MSDN “Get Started with AlwaysOn Availability Groups” or in some blogs in the Internet..

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.

image

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.

image

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

C:\Windows\system32>sqlcmd -S MyVirtualServer -K ReadOnly –d Northwind
1> select @@servername
2> go

--------------------------------------------------------------------------------
------------------------------------------------
MyServer1

(1 rows affected)
1> exit

C:\Windows\system32>sqlcmd -S MyVirtualServer –d Northwind
1> select @@servername
2> go

--------------------------------------------------------------------------------
------------------------------------------------
MyServer1

(1 rows affected)
1>

The output shows that both options return the same server (the primary), What is missing?

Configuring the routing options

After some research I found the Client Connectivity and Application Failover (AlwaysOn Availability Groups) documentation and it mentions in the “Connecting to a Read-Access Secondary Replica” section that four things are needed ( I rephrase those in a bullet format for this post)

  1. Use the modifier in the connection string (the –K with the ReadOnly I tried)
  2. Configure the Secondary as Read-Intent only (I did it in the wizard when I was creating the Availability Group)
  3. Configure the READ_ONLY_ROUTING_URL
  4. Configure the READ_ONLY_ROUTING_LIST.

Got it!!!, I was missing two steps

Configure the READ_ONLY_ROUTING_URL (T-SQL)

alter availability group SQLAvailabilityGroup MODIFY REPLICA ON N'MyServer1'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://MyServer1:1433'))

alter availability group SQLAvailabilityGroup MODIFY REPLICA ON N'MyServer2'
WITH (SECONDARY_ROLE(READ_ONLY_ROUTING_URL=N'TCP://MyServer2:1433'))

Configure the READ_ONLY_ROUTING_LIST (T-SQL)

alter availability group SQLAvailabilityGroup MODIFY REPLICA ON N'MyServer1'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'MyServer2', N'MyServer1')))

alter availability group SQLAvailabilityGroup MODIFY REPLICA ON N'MyServer2'
WITH (PRIMARY_ROLE(READ_ONLY_ROUTING_LIST=(N'MyServer1', N'MyServer2')))

Testing the Secondary Read

Now let run the SQLCMD command again

C:\Windows\system32>sqlcmd -S MyVirtualServer -K ReadOnly –d Northwind
1> select @@servername
2> go

--------------------------------------------------------------------------------
------------------------------------------------
MyServer2

(1 rows affected)
1> exit

C:\Windows\system32>sqlcmd -S MyVirtualServer –d Northwind
1> select @@servername
2> go

--------------------------------------------------------------------------------
------------------------------------------------
MyServer1

(1 rows affected)
1>

It works as I expected!!! now I can go and change the connection string in my read-only applications with this new one

Data Source=MyVirtualServer; Initial Catalog=Northwind;integrated security=true;ApplicationIntent=ReadOnly

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.

########################################################################################################
## Functions
########################################################################################################
## Function to perform the failover
function FailOver()
{
    $conString = "Data Source=MyVirtualServer; Initial Catalog=Master;integrated security=true"
   
    # Create SqlConnection object and define connection string
    $con = New-Object System.Data.SqlClient.SqlConnection
    $con.ConnectionString = $conString
    $con.open()

    # Create SqlCommand object, define command text, and set the connection
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.Connection = $con
    $cmd.CommandText = "select replica_server_name from sys.dm_hadr_availability_replica_states as sta inner join sys.availability_replicas as rep on (sta.replica_id=rep.replica_id) where role=2"
   

    #Query the Jobowner, jobid
    $cmd.CommandType = [System.Data.CommandType]::Text  
    $reader = $cmd.ExecuteReader();
    while ($reader.Read())
    {
        $secondary = $reader["replica_server_name"].ToString()
    }
   
    $con.close()

    $conString = "Data Source=$secondary; Initial Catalog=Master;integrated security=true"
    # Create SqlConnection object and define connection string
    $con = New-Object System.Data.SqlClient.SqlConnection
    $con.ConnectionString = $conString
    $con.open()

    Write-Host("Failing over to $secondary")
    # Create SqlCommand object, define command text, and set the connection
    $cmd = New-Object System.Data.SqlClient.SqlCommand
    $cmd.Connection = $con
    $cmd.CommandText = "ALTER AVAILABILITY GROUP [SQLAvailabilityGroup]  Failover"
    $cmd.CommandType = [System.Data.CommandType]::Text  
    $cmd.ExecuteNonQuery()
   

}

########################################################################################################
## Main Function
########################################################################################################
# Logging

    while($true)
    {
        try
        {
            FailOver
            $a= Get-Date
            Write-Host("Sleeping until next failover, current time $a")
            Start-Sleep -s 900
        }
        catch
        {
       
        }
    }

Comments

  • Anonymous
    December 16, 2011
    No need to create our own failover function as there's a already a built in cmdlet for failing over called Switch-SqlAvailabilityGroup. In fact Powershell coverage of AlwaysON is very good. I've blogged about it here: sev17.com/.../managing-alwayson-with-powershell

  • Anonymous
    December 27, 2011
    The comment has been removed

  • Anonymous
    August 15, 2012
    Great post, helped me solve the problem of setting up secondary replicas. Highly Appreciated. Thanks Kishore

  • Anonymous
    September 07, 2012
    I'm glad that was helpful -Jaime

  • Anonymous
    November 19, 2013
    Thank you for the great post, very clear and helpful