Load Balancing Microsoft SQL Server 2012 AlwaysON Databases with Netscaler

Lately I was involved in a project where they required to load balance their MSSQL databases (reads and writes), the project included utilizing MSSQL 2012 AlwaysON.

In this blog I will be concentrating on the configuration required to setup Citrix Netscaler to load balance database connections, on the other hands if you’re not a fan of layer 7 load balancing you can always delve deep into MSSQL Server 2012 AlwaysON Read-Only Routing.

Its good to mention that till the moment Citrix doesn’t support load balancing AlwaysON cluster YET, but as per the recent news the newest release named Tagma will support it fully.

• Microsoft SQL Server Side Configuration:

• Microsoft SQL Server 2012 AlwaysON:

So, first make sure you’ve setup the AlwaysON cluster taking into consideration the hereunder (I don’t think this is supported by Citrix but it will get the job done):

1. Do not create a listener.
2. Go to SQL Server configuration on both nodes:
   1. SQL Server Network Configuration -> Protocols -> TCP/IP -> IP Addresses tab
   2. Scroll down and remove the TCP Dynamic Ports leaving it blank and put 1433 in the TCP Port IP Address.
3. Your secondary server must be configured to be a readable secondary.

• MSSQL Server service monitoring databases:

To verify that the MSSQL server service is up and running on the Netscaler appliance we will need to later on create a monitor which will connect to the MSSQL server and perform a query on a certain database and if the results are valid then the service is up, otherwise the service will be down.

I have generated an MSSQL script which will create a database called ‘mssql-ecv‘ and it will create an MSSQL user named ‘dbuser‘ with datareader permissions, you will need to run this script on both MSSQL server nodes also change the password to whatever you’d like as long as its identical on both servers also keep a keen eye on the password policies in terms of strength and expiry because you might no want this user’s password to expire as it will cause the service to fail on the Netscaler.

USE [master]
GO
/****** Object:  Database [mssql-ecv]    Script Date: 5/24/2014 10:04:25 AM ******/
CREATE DATABASE [mssql-ecv]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'mssql-ecv', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mssql-ecv.mdf' , SIZE = 4096KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
 LOG ON
( NAME = N'mssql-ecv_log', FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\mssql-ecv_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO
ALTER DATABASE [mssql-ecv] SET COMPATIBILITY_LEVEL = 110
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [mssql-ecv].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
EXEC sys.sp_db_vardecimal_storage_format N'mssql-ecv', N'ON'
GO
USE [mssql-ecv]
GO
/****** Object:  User [dbuser]    Script Date: 5/24/2014 10:04:25 AM ******/
CREATE USER [dbuser] FOR LOGIN [dbuser] WITH DEFAULT_SCHEMA=[dbo]
GO
ALTER ROLE [db_datareader] ADD MEMBER [dbuser]
GO
/****** Object:  Table [dbo].[monitor]    Script Date: 5/24/2014 10:04:26 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[monitor](
    [moncol] [nchar](10) NULL
) ON [PRIMARY]
GO
USE [master]
GO
ALTER DATABASE [mssql-ecv] SET  READ_WRITE
GO
USE [mssql-ecv]
GO
INSERT INTO [dbo].[monitor] ([moncol]) VALUES (1)
GO

• Database Users

Sadly I haven’t figured out how to integrate LDAP users when dealing with database connections because on the Netscaler appliance you can only add SQL authentication database users (for now I presume).

So you will have to create to MSSQL server users and assign them with the necessary read/write permissions.

Netscaler Configuration

Here I am using Netscaler VPX NS10.1: Build 126.12.nc on an ESXi 5.5 host,  I will not go through setting up the VPX virtual appliance but you can head here if you’re not familiar with it.

• Basic Configuration:
  • System -> Settings -> Configure basic features -> make sure Load Balancing and Content Switching are checked.
  • System -> NTP Servers -> Add your NTP server.
  • System -> User Administration -> Database Users -> Add the MSSQL server users including dbuser as well.
  • Traffic Management -> DNS -> Name Servers -> Add your DNS servers.
• Creating load balancing virtual servers:
  • Monitors:
    • Traffic Management -> Load Balancing -> Monitors -> Add
    • Name MSSQL-ECV-MON
    • Type: MSSQL-ECV
    • Standard Parameters tab keep everything at default.
    • Special Parameters:
      • Database: mssql-ecv
      • Query: select moncol from monitor
      • User Name: dbuser
      • Rule: MSSQL.RES.ATLEAST_ROWS_COUNT(1)
      • Protocol Version: 2012
      • Click create.
  • Services (this should be done per MSSQL server):
    • Traffic Management -> Load Balancing -> Services -> Add
      • Service Name: MSSQLServerName-svc
      • Server: IP/Address or FQDN.
      • Protocol: MSSQL
      • Port: 1433
      • Health Monitoring: Checked
      • Appflow Logging: Checked
      • Monitors: MSSQL-ECV-MON
      • All other options keep at default.

• Virtual Servers (You will need to create a virtual server per MSSQL server):
  • Traffic Management -> Load Balancing -> Virtual Servers -> Add
    • Name: A-Name-Relevant-To-The-MSSQL-Server-Name-lbvs
    • Protocol: MSSQL
    • IP Address: A new IP address
    • Port: 1433
    • Service: Check the service created for that MSSQL server
      
• Creating the content switching virtual servers:
  • Policies:
    • Traffic Management -> Content Switching -> Policies -> Add:
      • Read Policy:
        • Name: ReadPolicy
        • Expression: MSSQL.REQ.QUERY.COMMAND.CONTAINS(“select”)
      • Write Policy:
        • Name: WritePolicy
        • Expression: MSSQL.REQ.QUERY.COMMAND.CONTAINS(“insert”) or update or whatever you want.

• Virtual Server:
  • Traffic Management -> Content Switching -> Virtual Servers -> Add:
    • Name: MSSQLCSVS
    • Protocol: MSSQL
    • IP Address: New IP Address < This is the one your application/users will be connecting to.
    • Port: 1433
    • Policies:
      • Priority 100 / Name WritePolicy / target mssqlserver-primary-lbvs
      • Priority 110 / Name ReadPolicy / target mssqlserver-secondary-lbvs
    • Keep the rest of the tabs/configurations at default.

And you’re done :-), now for testing I used SQL Load Generator which is a very good tool that I used to generate loads of reads and writes where I kept monitoring the content switching virtual server to check out the hits.

I will make sure this blog is updated once Tagma is released or maybe it will be a different configuration and a new blog ;-).

(Abdullah)^2