[NETSCALER] - Configure Datastream - SQL 2012 AlWaysOn

En France au mois d'août il y a une règle ...... "OCCUPE toi comme tu peux"......

Alors pour cela il vous suffit de trouver :

- Un EXPERT SQL : PASCAL B. -> Profil LinkeDin

- Un EXPERT CITRIX : LUC P. -> Profil LinkeDin

- Un NetScaler 10.1 de Staging

- Des VM's 2K12 R2 et 2K8 R2 de Staging

Vous secouez le tout et les gars vous valident la solution :

DataStream couplée avec SQL 2K12 Always On.

Et comme ils sont généreux ils en profitent pour la partager avec vous par le biais de ce site......

Maintenant place à la solution validée :

SQL001 :

SQL002 :

Content Switch Vserver :

1. Project Purpose

The NetScaler® DataStream" feature provides an intelligent mechanism for request switching at the database layer by distributing requests based on the SQL query being sent.
When deployed in front of database servers, a NetScaler ensures optimal distribution of traffic from the application servers and Web servers. Administrators can segment traffic according to information in the SQL query and on the basis of database names, usernames, character sets, and packet size.
You can either configure load balancing to switch requests based on load balancing algorithms or elaborate the switching criteria by configuring content switching to make a decision based on an SQL query parameters. You can further configure monitors to track the state of database servers.

In this Page we will describe all the steps for implementing this feature.

2. Test Environment

For this lab we will use the Citrix database hosted on a Microsoft cluster. This Cluster is composed of 2 SQL server 2012 Enterprise in Standalone Mode, with AlwaysOn enabled.
So we will try to enable the datastream mechanism, all SQL Read request will be redirect on the Database in Slave mode (SECONDARY) , the Write request will be redirect on the master (PRIMARY) :

E-DOC Citrix

In Our Lab :

3. Configuration steps

In order to configure the feature we have to follow the steps describe by edocs here.


Configure Database User :

In order to connect to database we have to configure a user on both Netscaler and SQL database.


Clic for ZOOM

Of course password must be the same, on SQL side, the user has the permissions below :

Clic for ZOOM

Configure Monitors

The monitor are used for check the services availability, in our case we will create monitors for checking if the database is primary or secondary.

For that create a new monitor base on MSSQL-ECV Type, for example the monitor below will check if the database is primary.

Clic for ZOOM

In special parameters we will put the SQL request, the result will be parsed, if the request answer PRIMARY, the monitor will be GREEN, if not it'll be RED

SQL Request: select c.role_desc from sys.dm_hadr_availability_replica_states c inner join sys.dm_hadr_database_replica_states r ON c.replica_id=r.replica_id and c.is_local=1 inner join sys.availability_groups a on a.group_id=c.group_id where a.name='MonCluster2012'



Create the same monitor but for "SECONDARY", name SQL_ALWAYSON_SECONDARTY in our case.
Once the monitors are created we will create Service group in order to affect the monitor to the 2 SQL servers

Clic for ZOOM

Create Service Group :

For create service group go to Load Balancing / Service Group / click Add then give a name to your service group.

We will create 2 service group, one for PRIMARY monitor, the other One for SECONDARY.

This service group will check on both SQL Server, so.

Clic for ZOOM

In the capture above, we have create a service group on the 2 sql server, with the Secondary monitor, we can see on the first print screen the result of the monitor (SQLrequest), so we can see here the first server is in RED state, so it's not the SECONDARY, the second IP ( si in GREEN state, so this server is secondary the request will be redirect on it (when we will have a LB server of course)

Of course a second service group must be created with primary monitor.

Clic for ZOOM

Load Balancer Creation

So we can now identify which server is PRIMARY and SECONDARY, we have the service group, so we will now configure the 2 load balancer with the service group.
One Vserver LB for the READ request with the SECONDARY service group, and 1 Vserver LB for the WRITE request with the PRIMARY service group.

In the print screen above the Vserver for SECONDARY database (read request), this LB Vserver will redirect the MSSQL to the service in GREEN state (which is the SECONDARY database)
NB: The LB server not need to be directly addressable, they will be use the internal mechanism.

We have to create an other LB server for Master database with the PRIMARY service group.

Clic for ZOOM

Context switch Creation

We have now the 2 load balancer server, we have to create a context switch for load balance on this 2 Lb server depends of the type of request..

For that go to the netscaler GUI under Context Switching / Virtual Servers then clic ADD.

Clic for ZOOM

Then under Policies we will create a 2 policy, this policy will analyse the SQL request and redirect to the good LB server.

First policy for select request :

Clic for ZOOM

The second one will be a default policy, all the request without an "insert".

On the screenshot above we can see:

If a request contains insert, the request will be redirect on LB_VS_SQLSlave, else the request will be redirect to LB_Vs_SQLMaster.

Clic for ZOOM

Configure Fail over

On the step above, we have created a process for redirect SQL request on 2 SQL server, but we have now to configure in case of crash of one of the 2 SQL servers.

In this case the monitor which check the "SECONDARY" state will answer a bad state (on both server), because the SQL request will only answer "PRIMARY".

So if we stay like that, the select request will be in error. In order to keep the service running we will set a backup server on the LB_Vs_SQLSlave, that means if all the service are in RED state, it will switch to the backup server.

We will use the LB_Vs_SQLMaster as Backup server, because this one will have 1 service in green state because the request still answer PRIMARY to the SQL request Monitor.

Clic for ZOOM

Configure Authentication

If we keep th configuration like that, all the request will be done by the Netscaler_usr on the SQL server, it can be a problem for specific application which need an Kerberos authentication.

So we can implement this kind of authentication : Citrix Edoc. How to coming soon.


In this paragraph we will make some test in order to check if the solution works fine, and in order to explain a little bit deeper the datastream.

Functional Diagram

Clic for ZOOM

Add comment

Security code

Download Freewww.bigtheme.net/joomla Joomla Templates Responsive