SQl Performance Monitoring

[Migration User]

I am having a problem with the default SQL monitor policies.

In my enviroment SQl is installed with an instance name not the default instance.
So when you look at perfmon each counter is MSSQL%Instance Name%:Counter.

For example on a SQL server named msp2-prdql06 with an Instance name of msp2prdsql06a the counter for locks would look like this.

MSSQL$msp2-prdsql06A:Locks

How do I monitor all my SQl servers without creating metric rules for every SQL server?
Having named SQL instances is a standard practice.

Thanks,

Steve

[Migration User]

Unfortunately I am not sure of the answer to help you, but I would also think you pose a great question to Microsoft no?  Perfmon and SQL are both Microsoft applications.  Sorry I do not have a good answer.  If you have a support agreement with Microsoft I would think they could probably answer that question quickly for you.

[Joseph_Carson]

Hello Steve,

This is a limitation with using Performance Counter metrics from windows.  If their is another way to gather the same values either via command line, WMI etc... this maybe a way to workaround this.  If you do have many instances you might be able to work directly with the XML to do this with copy and paste but if you are not familiar with this then you would have to use the UI which will take sometime but does work.

Best Regards,

[Migration User]

Hi Joseph,

I am looking at creating these alerts now and was looking at doing the copying and pasting within an XML.
Quick question.  What about the Guids?

This is what I would like to do.  ( I have over 100 SQL instances that I need to Monitor.)

1:  Create a standard SQL template with all the monitors I would like to see. (Services status, perf, etc)
2. Export the Default Policy
4. Open up the XML file in an editor and change the SQL Instance to the proper name.
5.  Import the Policy.
  Will this work?  Or do I need to clone the policy each time and then export it so I get unique Guids within the Policy.

Thanks,

Steve