The DataStream feature provides us with an intelligent mechanism for load balancing SQL traffic to backend endpoints. This might for instance be based upon READ and WRITE requests, where READ requests are forwarded to some dedicated backend SQL servers while WRITE requests are dedicated to a master cluster.
This can also be performed down to a database level, where we can load-balance specific databases, or can even be based upon other attributes such as usernames, packet size, and so on.
For MySQL, DataStream supports versions 4.1, 5, 5.1, 5.4, 5.5, and 5.6 using native MySQL authentication. For MS SQL it supports 2005, 2008, 2008 R2, 2012, and 2014 (only frontend) using both SQL authentication and NTLM or Kerberos using the TDS protocol.
This book will only focus on SQL authentication against MS SQL.
In order to set up the DataStream feature, there are some prerequisites that need to be in place first.
First we need a database user in place. This is a user that the NetScaler uses to connect to the backend servers. For instance, to use with a monitor to verify that the database is working, NetScaler also uses the database user to authenticate the clients, which are then redirected to the backend servers.
In order to set up a database user, go into System | User Administration | Database User, and enter a username and password for a database user. It is important to note that usernames are case-sensitive. If you update the password for the SQL user in MS SQL, you will also need to update the database user on NetScaler.
Next we should configure a database profile, this profile is used with for instance setting up different editions of MS SQL served as a frontend.
Now we need to create a MS SQL monitor; this can be done under Traffic Management | Load Balancing | Monitors, then click Add. From here choose type MSSQL-ECV, then go into special parameters. Here we need to type the username of the database user we created earlier. It is important that this user has database reader access to the particular database we want to test against, because it is used to run a SQL query against a database to ensure that the database is operational.
Next type the name of the database we want to monitor against, then type a query that is then used against that particular database. This might for instance be a select statement such as SELECT * FROM TABLE. Then under expression we need to enter an expression that defines how NetScaler will verify whether the SQL server is up or not. In our example, it is MSSQL.RES.ATLEAST_ROWS_COUNT(0), which means that, when NetScaler runs the query against the database, it should return zero rows from that particular table.
There are also some other parameters that we can define here, such as:
- Protocol version: Defines the backend SQL server version that is running
- KCD account: If we are using Windows-based authentication, we can define a Kerberos constrained delegation account that can be used to access the databases
- Store DB: This feature is used to store all databases retrieved from the backend database servers, in specific database load balancing
Now, after we have created an MSSQL monitor, we can set up a load balanced SQL deployment by creating a vServer and attaching the needed backend services. When setting up the load-balanced vServer it is important to remember to select MSSQL as the protocol and that the port number is set to 1433 as a default by MSSQL.
We can use NetScaler to proxy against different versions of MSSQL; even if the backend servers are version 2014, we can specify that the virtual server should represent itself as a 2012 vServer for compatibility.
This is done under the MsSql pane by choosing the Server Version, as shown in the following screenshot:
It is also here that we define whether we want to have database-specific load balancing, which will be covered a bit later in this chapter.
This deployment uses an optional content-switching vServer to be able to load balance reads and writes between different backend virtual servers.
Here we use a content switch policy to forward SELECT statements to some servers and redirect WRITE requests to a master server.
This is shown in the following diagram:
Here we have a simple action policy attached to the content switching vServer that redirects select statements to a particular slave server for read requests. All other requests are sent to the master server that in most cases are WRITE requests.
In order to set this up we need to predefine the load balancing vServers and then create a content-switching vServer. Go into Traffic Management | Content Switching | Virtual Servers then click on Add. Enter the necessary info and be sure to choose the MSSQL protocol and port 1433 (in the case of MSSQL).
We also have the same options on the content switching VServer to define MSSQL server versions.
Next we need to create a Content Switching Policy, and click on the + sign to create a new policy. Under the Expression tab write MSSQL.REQ.QUERY.COMMAND.contains("select").
Then we need to define an action for if someone matches the expression. By the Action pane click the + sign; then, under target load balancing virtual server, choose the virtual server that represents the servers handling READ requests.
When adding the policy to the vServer, give it a low priority. This is because then the content-switching vServer will evaluate requests first by checking against the expression; if it does not fulfill the statement, it will be sent to the other server.
Next we just need to define a default load balancing virtual server that will be the one that represents the master servers (WRITE requests).