<

Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x

Monitor Internal MSSQL Database with Citrix Netscaler Advanced Monitor

Published on
5,431 Points
1,531 Views
4 Endorsements
Last Modified:
Brian Murphy
Technology Infrastructure Architect, with an unusually unique combination of skills honed over more than 20 years.
#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
Comment
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
0 Comments

Join & Write a Comment

After creating this article (http://www.experts-exchange.com/articles/23699/Setup-Mikrotik-routers-with-OSPF.html), I decided to make a video (no audio) to show you how to configure the routers and run some trace routes and pings between the 7 sites…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month