In DataStream, the NetScaler is placed in-line between the application and/or web servers and the database servers. On the NetScaler appliance, the database servers are represented by services.

Figure 1. DataStream Entity Model

As shown in the preceding figure, a DataStream configuration can consist of an optional content switching virtual server (CS), a load balancing setup consisting of load balancing virtual servers (LB1 and LB2) and services (Svc1, Svc2, Svc3, and Svc4), and content switching policies (optional).

The clients (application or web servers) send requests to the IP address of a content switching virtual server configured on the NetScaler appliance. The NetScaler, then, authenticates the clients using the database user credentials configured on the NetScaler appliance. The content switching virtual server applies the associated content switching policies to the requests. After evaluating the policies, the content switching virtual server routes the requests to the appropriate load balancing virtual servers, which then distributes the requests to the appropriate database servers (represented by services on the NetScaler) based on the load balancing algorithm. The NetScaler uses the same database user credentials to authenticate the connection with the database server.

If a content switching virtual server is not configured on the NetScaler, the clients (application or web servers) send their requests to the IP address of a load balancing virtual server configured on the NetScaler appliance. The NetScaler authenticates the client by using the database user credentials configured on the NetScaler appliance, and then uses the same credentials to authenticate the connection with the database server. The load balancing virtual server distributes the requests to the database servers according to the load balancing algorithm. The most effective load balancing algorithm for database switching is the least connection method.

You need to configure the database username and password on the NetScaler. For example, if you have a user John configured on the database, you need to configure the user John on the NetScaler too.

Microsoft SQL 2012 AlwaysOn Availability Group

An availability group supports a failover environment for a discrete set of user databases, known as availability databases that fail over together. An availability group listener is a virtual network name to which clients can connect in order to access a database in a primary or secondary replica of an AlwaysOn availability group. An availability group listener enables a client to connect to an availability replica without knowing the name of the physical instance of SQL Server to which the client is connecting. If read-only routing is configured for one or more readable secondary replicas, read-intent client connections to the primary replica are redirected to a readable secondary replica.

Configuration for Single Availability Group

AG1 = { Node1(N1-PRIMARY), Node2(N2), Node3(N3), SQL Listener(L1)}

Figure 2: DataStream for Microsoft SQL 2012

Add a Database User Through NetScaler Command Line

At the NetScaler command prompt, type:
add db user <username> -password <password>

For example:
>  add db user nsdbuser -password dd260427edf

Add Microsoft SQL Service

For AlwaysOn Availability groups, the name of the service should match the DNS name of the server:
add service  <servicename> <IP address> MSSQL <port>

For example:
>  add service node1.sql2012.com 10.217.28.194 MSSQL 1433
>  add service node2.sql2012.com 10.217.28.195 MSSQL 1433
>  add service node3.sql2012.com 10.217.28.196 MSSQL 1433
>  add service listener 10.217.28.200 MSSQL 1433

Microsoft SQL Load Balancing Monitor

To track the state of each load balanced database server in real time, you need to bind a monitor to each service. The monitor is configured to test the service by sending periodic probes to the service. For AlwaysOn availability group, you need to configure the monitor SQL query to verify if the node is a secondary replica. If the monitor probe succeeds, service is marked as UP. Thus, for a primary replica, the monitor probe fails marking the service as DOWN.

Add Microsoft SQL Monitor

add monitor <monitor name> MSSQL-ECV -sqlQuery "select role, role_desc from sys.dm_hadr_availability_replica_states as A, sys.dm_hadr_availability_replica_cluster_states as B where (A.replica_id = B.replica_id and B.replica_server_name = '<NODE NAME>') and A.group_id in (select ag_id from sys.dm_hadr_name_id_map where ag_name = '<AG NAME>');" -evalRule "MSSQL.RES.ROW(0).NUM_ELEM(0).eq(2)" –username <db_username>

For example:
>  add monitor read_replica1 MSSQL-ECV –sqlQuery " select role, role_desc from sys.dm_hadr_availability_replica_states as A, sys.dm_hadr_availability_replica_cluster_states as B where (A.replica_id = B.replica_id and B.replica_server_name = ‘Node1’) and A group_id in (select ag_id from sys.dm_hadr_name_id_map where ag_name = ‘AG1’) " –evalRule "MSSQL.RES.ROW(0).NUM_ELEM(0).EQ(2) " –username root

Bind Microsoft SQL ECV Monitor to Service

bind service <servicename> –monitorName <monitorname>

For example
>  bind service node1.sql2012.com -monitorName read_replica1
>  bind service node2.sql2012.com -monitorName read_replica2
>  bind service node3.sql2012.com -monitorName read_replica3

Load Balancing

Before configuring a load balancing setup, you must enable the load balancing feature. With the services configured, you are ready to create a load balancing virtual server and bind the services to the virtual server.

Add Microsoft SQL Load Balancing Virtual Server

add lb vserver  <vservername> MSSQL <IP address> <port>

For example:
>  add lb vserver LB1 MSSQL 10.217.201.17 1433
>  add lb vserver LB2 MSSQL 10.217.201.18 1433

Set Microsoft SQL Server Version on Load Balancing Virtual Server

set lb vserver  <vservername> -mssqlServerVersion <serverversion>

For example:
> set lb vserver LB1 -mssqlServerVersion 2012
> set lb vserver LB2 -mssqlServerVersion 2012  

Bind Service to Microsoft SQL Load Balancing Virtual Server

bind lb vserver  <vservername> <servicename>

For example:
> bind lb vserver LB1 listener
> bind lb vserver LB2 node1.sql2012.com
> bind lb vserver LB2 node2.sql2012.com
> bind lb vserver LB2 node3.sql2012.com

Content Switching

For Microsoft SQL 2012, you need to configure a content switching policy to identify the read-only intent in the SQL request from a client. This enables the content switching virtual server to route the read and write requests to different load balancing virtual server. You can configure content switching policies with default syntax expressions to switch content based on connection properties, such as username and database name, command parameters, and the SQL query to select the server.

Add Microsoft SQL Content Switching Virtual Server

add cs vserver  <vservername> MSSQL <IP address> <port>

For example
> add cs vserver MSSQL_CS MSSQL 10.217.201.19 1433

Add Content Switching Policy

add cs policy  <policyname> -rule <expression>

For example
> add cs policy CS_ReadOnly –rule "MSSQL.CLIENT.TYPEFLAGS.BITAND(32).eq(32) "

The above content switching policy rule looks for the read-only intent flag in the request.

Bind Content Switching Policy to Content Switching Virtual Server

bind cs vserver  <vservername> -policy <policyname> -targetLBVserver <lbvservername> -priority <number>

For example
> bind cs vserver MSSQL_CS –policy CS_ReadOnly –targetLBVserver LB2 –priority 1
> bind cs vserver MSSQL_CS LB1