Monitor Internal MSSQL Database with Citrix Netscaler Advanced Monitor

Brian MurphySenior Information Technology Consultant
CERTIFIED EXPERT
Holistic technology infrastructure strategy, design, engineering and implementation that is highly scalable, secure, optimized, automated
Published:
Updated:
#Citrix #Netscaler #MSSQL #Load Balance
Citrix Netscaler Application Delivery Controller (ADC) is one of many underutilized data center technologies.  The monitor feature is often deployed with generic settings such as a simple ping or port listener check.  Monitors provide advanced validation of internal services.  For purposes of this article, I am using Firmware release 11.0.63.18.nc.
1-NETSCALER-VERSION.pngNetscaler monitors are used stand-alone, or with services, service groups and load balancing virtual servers. Netscaler provides standard monitors such as Ping using ICMP, and advanced monitors allowing for custom strings, expressions, and authentication for functionality testing. Here are the default monitors with Firmware 11.0.63.18.
NETSCALER-11-63-DEFAULT-MONITORS.pngI need to add a custom monitor for Microsoft SQL. The following is an example of how I use the advanced monitor on internal Microsoft SQL servers hosting my Citrix XenApp and XenDesktop database. I begin by logging on to Microsoft SQL server on my internal network.  

On the MS SQL server hosting my Citrix database I create an empty database...  
ADD-NEW-DATABASE.png...with the database name of "ns_sql_monitor."
MSSQL-DATABASE-NAME.pngI create a new table.
MSSQL-CREATE-NEW-TABLE.pngI name the column “nscheck” and data type is “nschar(10)”. I change the table name to “nsvalidate.”
ADD-COLUMN-TO-DATABASE.png
I created the database in context of "dbo" but prefer a new SQL ID for purpose of assigning to the Netscaler monitor. Under "Security" I right-click, choose "New" > "Login"
mssql-NEW-LOGIN-ID.pngNetscaler supports SQL authentication. I select "SQL Server authentication", set "Login name:" to ns_monitor, generate a random complex password.  
mssql-CREATE-NEW-USERID-PASSWORD.pngOn the left-hand side I validate the Server Role set to "Public".
SQL-USERID-SERVERROLE.pngI set the "User Mapping" to the database created in previous step (ns-sql-monitor) and check the box "db_datareader" in the Database role section. I now have the prerequisites required to create the MS SQL monitor on the Citrix Netscaler appliance.

Now I login to Netscaler to create a Database User. Configuration Tab, System, User Administration, Database Users > Click Add. I am using ‘sa’ account otherwise known as ‘dbo.' I assigned a password when I installed SQL 2014. I will use this same password for the ‘sa’ account created on Netscaler.
ADD-DB-USER.pngNow I create the MSSQL ECV Monitor to validate the SQL instance. This assumes your Netscaler can communicate with the SQL instance often requiring that new firewall rules be created prior. I create the monitor under Traffic Management > Load Balancing > Monitors > Add

I name the database “ns-SQL-monitor”. This is an empty database with the sole intent of authenticating using 'sa' or another account created for this purpose. I install SQL allowing for SQL and Windows authentication but this monitor requires SQL authentication.
NEW-DB-NAME-FOR-NETSCALER.pngUnder Create Monitor, I click the Special Parameters tab to expose the parameters needed. I call the monitor ‘xenapp-sql-check’ and choose the Type of ‘MSSQL-ECV’ user name ‘sa’ and the database ‘ns-sql-monitor’ from previous step. For the query I simply perform a “select * from nscheck” the column from the Table created in prior steps. Next, the expression “MSSQL.RES.ATLEAST_ROWS_COUNT(0)” and choose protocol version 2012.
CREATEMONITIOR.pngNow we have a monitor for our Load Balanced virtual server associated with the SQL servers defined as a Service then added to a Service Group bound to a load-balanced virtual server. This provides a higher level of monitoring than simple TCP port check.
 
This is one simple example of many combinations to leveraging monitors.
4
4,032 Views
Brian MurphySenior Information Technology Consultant
CERTIFIED EXPERT
Holistic technology infrastructure strategy, design, engineering and implementation that is highly scalable, secure, optimized, automated

Comments (0)

Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.