Demo NetScaler Datastream with SQL AlwaysOn

Is your database application not performing as well as it should? Is your SQL Server running low on resources? Is your application not written to take advantage of SQL AlwaysOn Availability Groups or database sharding? If you answered yes to any of those questions, then NetScaler Datastream to the rescue! Or, maybe you are looking to demo NetScaler Datastream to get a better idea of what capabilities it has in your SQL environment. If that is the case, look no further – in this post I’ll cover the basics of how to setup NetScaler Datastream to work with a SQL AlwaysOn cluster.

If you are not familiar with what NetScaler Datastream technology is, take a look at this blog post. It allows you to improve SQL Server performance in the same way that the NetScaler improves performance for web servers – SQL connection multiplexing is similar to TCP multiplexing.

How Datastream works with AlwaysOn

Let me explain. No – there is too much -let me sum up: the NetScaler speaks SQL. A content switching virtual server will be the target for clients connecting to the availability group. This allows the NetScaler to see all of the incoming requests and determine on the fly if they are read requests – meaning the query contains a ‘select’ statement (and not an update, delete, or insert). Once identified, these queries are forwarded to any available replica.

If the query is determined to be a write (it contains an update, delete or insert etc) query, meaning it modifies the database, it is sent to the AlwaysOn listener which is always running on the primary (read\write) replica.

This provides numerous benefits:

  • The client connection string does not need to be configured for read-only intent
  • All of the benefits of Datastream apply to all connections
  • Database scaling is vastly simpler

What you will need

I will be setting up this demo using 3 SQL nodes in an AlwaysOn availability group. I am not going to go over this procedure as it is fairly straight forward and is covered here and here and numerous other places on the internets. In my example, I’m using SQL Server 2012 running on Windows Server 2012R2.

You will be creating a cluster with non-shared storage as well as a listener, so you will need 5 IP addresses: 1 for each node, plus 1 for the cluster and 1 for the listener.

For the database itself, I am using AdventureWorks2012 available to download from here.

The NetScaler configuration includes Content Switching, Load Balancing, Datastream policies, and Integrated Caching – while IC is not required, it makes for vast improvements for any resource intensive read-only queries used more than once. IC is available with Platinum or as an add-on to Enterprise edition.

The Content Switch is the only required IP address on the NetScaler – all of the backing load balancers can be non-addressable.

For further reference, be familiar with the following:

datastream

 

Step 1: Get the SQL environment configured

I’m not going to spend much time here, as the links above should provide more than enough guidance on how to setup the nodes and the cluster. In the end, you should have the Availability group dashboard show as follows:

SQL_SSMS

Note that the secondary replicas will need to be set as read\only.

We also need to configure a database user on the NetScaler for it to use for backend connections. This user will need access to the AdventureWorks database as well as server rights as the SQL monitors will use this account as well – in this example, the user is nsDBUser.

DBUser

On the NetScaler, configure the user (System > User Administration > Database Users)

dbUser2

Step 2: Configure the servers and services on the NetScaler

We’ll start with defining each of the servers on the NetScaler – do this how ever you are most comfortable, but you will need four in this example: 3 nodes and 1 listener

servers

Then bind the services to these servers – stick with the default TCP monitors for now, we’ll build the custom SQL monitors shortly.

services

Next, we need to build the custom SQL monitors – there will be two in this case, one for read queries and one for write queries.

Read DB Monitor

Type: MSSQL-ECV

SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id WHERE b.role=2

Here is what it looks like configured:

mon_read

Write DB Monitor

Type: MSSQL-ECV

SELECT name FROM sys.databases a INNER JOIN sys.dm_hadr_availability_replica_states b ON a.replica_id=b.replica_id INNER JOIN sys.availability_group_listeners c ON b.group_id=c.group_id INNER JOIN sys.availability_group_listener_ip_addresses d ON c.listener_id=d.listener_id WHERE b.role=1 and d.ip_address like '10.1.1.120'

**NOTE: change the IP address in the query to match your listener IP address

Configured:

mon_write

Bind the write monitor to the listener service and the read monitor to the 3 nodes.

Step 3: Configure the load balancing vServers

For the LB vServers in this demo, we will need 2:

  • Read only LB vServer
    • Consists of all nodes of the AG cluster (including both the primary and read\only secondaries)
    • non-addressable
    • load balancing method: Token; Expression: MSSQL.CLIENT.USER

LBRead

LBReadSVC

  • Writable LB vServer
    • Consists of the AG listener service only
    • non-addressable
    • load balancing method: Token; Expression: MSSQL.CLIENT.DATABASE

LBWrite

LBWriteSVC

Notes:

  • The Token load balancing method implies persistence, therefore, do NOT configure a persistence method when using token load balancing
  • The expression used should match your scenario

Step 4: Configure the content switching vServer

First, we will create the content switching policies – we need two in this case, one for read and one for write.

The write policy contains expressions to match queries that modify the database, so there are several SQL commands at play here:

MSSQL.REQ.QUERY.TEXT.CONTAINS("insert") || MSSQL.REQ.QUERY.TEXT.CONTAINS("update") || MSSQL.REQ.QUERY.TEXT.CONTAINS("delete") || MSSQL.REQ.QUERY.TEXT.CONTAINS("drop") || MSSQL.REQ.QUERY.TEXT.CONTAINS("create") || MSSQL.REQ.QUERY.TEXT.CONTAINS("alter")

writePol

Next, the read policy needs to look for a query containing the select keyword.

MSSQL.REQ.QUERY.TEXT.CONTAINS("select")

Now we create the CS vServer – this will be an IP address vServer of type MSSQL on port 1433. The two previously created policies are bound – with target LB vServer set accordingly.

CS_pol

CSVserver

 

Step 5: Deploy a demo web application

I could not find any demo applications for the AdventureWorks database – I’m sure there are some out there, but I also wanted the ability to make a few changes and configure the app to my liking. That being said, I took some time to create a simple .NET web app that allows you to test and demonstrate the capabilities of the NetScaler Datastream configuration. My goals were:

  • A simple, effective web app
  • Allowed for a few query types (simple select, insert, and long running queries)
  • Needed to be edit-able

So that said, here’s the web app in action – see below for the source code:

DSDemo3

DSDemo1

DSDemo2

If you are interested in the source code for this, it can be found here: DemoApp1.zip

You will need to modify the two connection strings in the web.config to point to the CS vServer as well as the SQL Listener IP address

BONUS Step 6: Configure integrated caching

As an added bonus, you should also configure the integrated caching feature on the NetScaler to allow for frequently used read queries to have their results cached so that the backend SQL servers do not have to use additional resources on duplicate requests. You’re probably thinking that the data in a database changes from time to time – that’s OK, because the NetScaler is constantly watching the queries being issued such that when a write (insert, update, delete, etc) is made to the database, the contents of the cache are flushed. Don’t forget to enable the Integrated Caching feature if you have not done so already – we will also need to tune the memory available to the SQL DB content group once everything is configured.

First, configure the two cache selectors (Optimization > Integrated Caching > Cache Selectors)

invalidator_db1:

MSSQL.REQ.QUERY.TEXT.AFTER_STR("from").BEFORE_STR(";") ALT MSSQL.REQ.QUERY.TEXT.AFTER_STR("into").BEFORE_STR(" ")

selector_db1

MSSQL.REQ.QUERY.TEXT

selectors

Next, configure content group for MSSQL:

ContentGroup1

Add the selectors we created previously:

ContentGroup2

Save the content group for now. We need to create the cache and invalidate policies (Optimization > Integrated Caching > Policies) – we’ll start with the write cache policy that will invalidate the contents when a modification is made to the database. The expression will be:

MSSQL.REQ.QUERY.COMMAND.CONTAINS("INSERT") || MSSQL.REQ.QUERY.COMMAND.CONTAINS("DELETE") || MSSQL.REQ.QUERY.COMMAND.CONTAINS("UPDATE") || MSSQL.REQ.QUERY.COMMAND.CONTAINS("ALTER")

cachePolWrite

Next the read, or ‘cachable’ policy. Expression:

MSSQL.REQ.QUERY.COMMAND.CONTAINS("select")

cachePolRead

With these policies created, we will add them to the content group.

ContentGroup3

Finally, bind the cache policies to the previously created content switching vServer – ensuring that the invalidating policy has a lower priority.

cachePolBind

Note: if caching was not previously enabled on the NetScaler, you will need to tune the amount of memory available to be used for cache objects – this will require a reboot of the appliance. Please follow this article if you are not familiar with the procedure: http://support.citrix.com/article/CTX124553

Once you start sending queries to the CS vServer, you should see policy hits as well as cache objects start showing up.

cacheObjects

Oh, and that query that previously took 29 seconds, now took 15ms as it was served up from cache.

advQuery2

Summary

NetScaler Datastream can help scale and optimize the performance of your SQL database applications in the same ways that it improves web server performance.

 

1 thought on “Demo NetScaler Datastream with SQL AlwaysOn

Leave a Reply