SQL Cluster Monitoring

I was able to get SQL cluster monitoring setup using several posts, add physical nodes and virtual nodes. Apply SQL templates to them with suitable modifications. For a default instance the templates work fine. For named instance I was able to get a modfied WMI template working but the SQL template just will not work no matter what I did to modify it.

Anyone have any luck with named instances in a cluster with the SQL template. I can get it working in a non-clustered anmed instance by modifying it but the cluster instance fails to connect and dies.

Thanks

Find more posts tagged with

sql_2008

sql_clusters_apm

sql_2005

named_instance

cluster_monitoring

Accepted answers

bpocooks

I tried a general query to a DB on a named instance and it did work when I added it as an additional monitor to the template for the named instance. You should not have to worry about the port but if it is failing make sure that sql browser service is turned on on the physical cluster machines then try it, it will route the request to the correct port.

If you examine the queries in the template you will see they reference the DB master in the intial catalog field. Change this to the DB you want to access. In your query be sure to use fully qualified DB names, DB.SCHEMA.TABLE in the FROM syntax of the query.

All comments

adeimel

The SQL is not compatible with named instances as far as I've found. You will need to update it...

As I'm sure you found with service and perfmon checks, named instances are not a whole lot of fun to get setup.

Questionario

hmm?

I am a bit confused, you are talking about the SQL user experience monitor in order to make a SQL query?

Because that monitor has an option called "SQL Server Instance", if you enter the name of the named instance there it works just fine for us

bpocooks

Update it to what. Is there a new template that will handle it better. I am thinking it is in the code where it fails, cannot work with the virtual server cluster instance that runs the SQL instance. Works with default instance. Looked all through the template and found no xml relating to it.

bpocooks

I did try on a different cluster and it worked for the 3 named instances. The issue was the name of the instance on the one cluster. It is the same as our domain name (bad idea) and that appears to possibly be the issue. So it does appear to work in SQL monitoring template. The only issue left is the port check in WMI template, for named instances is dynamic so will have to remove it from the copied templates.

mdriskell

We are running into this right now....What we are going to try and accomplish is to create a template with all the proper queries for one of our named instances...then export that template....Edit it and do a find/replace for the instance names and then reimport.

We have dozens of instance names so otherwise this would take hours of editing within APM to accomplish

bpocooks

That is what I did and it worked fine. The SQL template has queries that you will need to edit as well, along the lines of

object_name = 'Sqlserver:General Statistics' to object_name like '%:General Statistics%' and not all of them need to be edited. You could test against a server using SSMS. Then just change the name of the instance using replace in notepad. Save the new template and use it as copy.

The WMI template has just replaces, instance name and the values >MSSQLSERVER:Buffer Manager to >MSSQL$DBS08:Buffer Manager then save it ans use it as new template.

Hope that helps

Questionario

I was actually talking about the component monitor.

With the User Experience Monitor we do a sql query which checks if all databases are up and running, can't get any better than that to check if SQL is working, can it?

Oh and yes, we also edited performance monitors to reflect the instance like the post above states

Dave_Curnow

I have been reading this and other threads around moitoring SQL clusters and am somewhat confused. I am currently trying to set up monitoring of a SQL 2008 R2 cluster containing 3 nodes and having 2 instances containing multiple databases. All of the physical hardware is monitored by NPM and Windows is monitored by APM, I have tried to monitor SQL via APM but as the cluster uses dynamic ports (actually listens on 1434 UDP) I can not use the SQL template, I have tried using WMI to monitor the 3 physical database servers within the cluster but as each node could host any instance I end up monitoring 6 instances of which only 2 are active, which seems a waste.

I would also like to use SQL Server User Experience Monitor to test various functions within the databases, the only way I can see this working is via the clustered instance, bu this is not using port 1433 so I can not get it to work.

Dave

bpocooks

Dave,

you have to assign the monitor to the virtual cluster object. So if you have a cluster with default instances you assign to the name of the sql cluster object, named instances, i.e. slqlclust\namedinstance you would still assign to the sqlclust virtual object. No you cannot monitor the port in the WMI template unless you are using a default instance. If you have named instances then you have to turn that off in the WMI template. Unless you assign fixed ports to the names instances which you can do. In the SQL template you will need to alter the queries as I mentioned previously to allow it to still find the information in the dmvs, etc. The default SQL template uses SQLSERVER you have to change it to remove that since a named instance will have MSSQL$namedinstance for the performance counter

SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name = 'SQLServer:General Statistics' AND counter_name = 'User Connections'

SELECT cntr_value FROM sys.dm_os_performance_counters WHERE object_name like '%:General Statistics%' AND counter_name = 'User Connections'

the use of a Like compare versus equality is needed. This has to be done on all the monitors in the sql query and you have to add the instance name in the required field. So create a copy edit it and then export it to get the xml export file, then you can edit that to change the instance name using find replace, then change the name tag attribute in the bottom of the xml file. Import it back in and it is a template for named instance( leave instance name blank for default instance). There is a field for instance in the monitor and you leave it blank for a default instance but have to put the instance name in, so slqlclust\namedinstance would have "namedinstance" in that field. The xml file can be edited and then imported back in for each default or named cluster instance.

For the WMI template if it is default instance cluster object it will work. For named instance you have to go into each monitor and change it

SQLServer:Buffer Manager

MSSQL$SQL2005:Buffer Manager

to reflect the named instance name. Do this for all monitors, add the named instance name in the instance field. Export the xml and then edit and re-import as needed, not forgoting the name tag attribute at the bottom of the xml file.

bpocooks

Dave_Curnow

Many thanks I will pass this information on to the DBA who I will be working with and hopefully we will be able to monitor the databases correctly.

Dave