<

[Webinar] Streamline your web hosting managementRegister Today

x

Monitor Internal MSSQL Database with Citrix Netscaler Advanced Monitor

Published on
5,624 Points
1,724 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
0 Comments

Join & Write a Comment

Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month