SQL Server 2005 Templates

[TeoriABiFo]

Hi!
Im running Cacti Version 0.8.7e under Red Hat Enterprise Linux Server release 5.4 (Tikanga).

Is there a Template to create graphics for SQL Server 2005?
I've read that Microsoft SQL Server 2005 doesnt have SNMP support anymore so i need to monitor it with WMI.
I've found only:
http://forums.cacti.net/about5225.html
http://forums.cacti.net/about5265.html

Is there something else? Cause it say there that those are for SQL Server 2000.

Thanks in advance.

Regards.

[denyingthetruth]

yeah I really need this too.please help.
I need to monitor process blocking on SQL server 2000 or 2005.

[lints]

I don't have a graph specifically for blocked processes, but it should be easy to modify my script to provide the right information, and then make the graph from there.

These graphs/data sources don't use WMI, but instead rely on the Script Server and PHP's mssql functions to connect directly to the SQL service and get the performance counters.

Since I don't use FQDNs as hostnames in my installation of Cacti, I connect to the IP and port of the SQL instance instead of hostname/instance name. The port is set to 1433 as the default, but it can be overwritten on a per-data source basis when you create the graph. I've also setup the script to have a hard-coded username/password, but like the port number, it can be overwritten on a per-graph basis.

The login that these graphs use can be either an SQL account, or an NT account with rights on the SQL server. Attached are a couple of SQL scripts (for the 2000, the other for 2005/2008) that will create an SQL login and grant the appropriate permissions to poll the performance counters.

The only other thing to note is that I'm using MemCache to speed up the polling process of the graphs for a given host. The first time the function runs, it fetches the data from the SQL server and stores it in the cache. Subsequent calls to the function will use the data in the cache if it exists. I'll admit that there's no 'lock' process to make sure that only 1 function call at a time polls the server, but I don't think it's needed. In any event, it should be easy to implement.

[lints]

Examples of the graphs the templates create.

[denyingthetruth]

I don't have a graph specifically for blocked processes, but it should be easy to modify my script to provide the right information, and then make the graph from there.

These graphs/data sources don't use WMI, but instead rely on the Script Server and PHP's mssql functions to connect directly to the SQL service and get the performance counters.

Since I don't use FQDNs as hostnames in my installation of Cacti, I connect to the IP and port of the SQL instance instead of hostname/instance name. The port is set to 1433 as the default, but it can be overwritten on a per-data source basis when you create the graph. I've also setup the script to have a hard-coded username/password, but like the port number, it can be overwritten on a per-graph basis.

The login that these graphs use can be either an SQL account, or an NT account with rights on the SQL server. Attached are a couple of SQL scripts (for the 2000, the other for 2005/2008) that will create an SQL login and grant the appropriate permissions to poll the performance counters.

The only other thing to note is that I'm using MemCache to speed up the polling process of the graphs for a given host. The first time the function runs, it fetches the data from the SQL server and stores it in the cache. Subsequent calls to the function will use the data in the cache if it exists. I'll admit that there's no 'lock' process to make sure that only 1 function call at a time polls the server, but I don't think it's needed. In any event, it should be easy to implement.

great job lints.huge thanks for making these scripts.
anyway,what "sql_server_2005-2008.txt" is for? is it a query that we must execute on ms sql server?
sorry for the stupid question.

[denyingthetruth]

also which username and password to change?
maybe you can make the fonts bold on which parts to change.

sorry i'm really new on cacti.

[dionvdc]

great job lints.huge thanks for making these scripts.
anyway,what "sql_server_2005-2008.txt" is for? is it a query that we must execute on ms sql server?
sorry for the stupid question.

that is a txt file with SQL command used to create an USER which is GRANTED right to the performance counters.

You need to import or execute those commands into every SQL server you want to monitor

[dionvdc]

also which username and password to change?
maybe you can make the fonts bold on which parts to change.

sorry i'm really new on cacti.

Well the first you could have figured out yourself

{snipped from the 2005/2008 txt file}
CREATE LOGIN [cactistats] WITH PASSWORD=N'CHANGEME', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

The second one is on line 21 & 22 ine ss_win_mssql_104 txt file.
{[snippet from mentioned file}
$password = ($password == NULL ? 'graphit' : $password);

Don't forget to change the extention to .php as its run by the scriptserver.

[Linegod]

If you get a chance, please post these to the templates area of the Docs site:

http://docs.cacti.net/templates
http://docs.cacti.net/templates.guidelines

[lints]

also which username and password to change?
maybe you can make the fonts bold on which parts to change.

sorry i'm really new on cacti.

Well the first you could have figured out yourself

{snipped from the 2005/2008 txt file}
CREATE LOGIN [cactistats] WITH PASSWORD=N'CHANGEME', DEFAULT_DATABASE=[master], DEFAULT_LANGUAGE=[us_english], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

The second one is on line 21 & 22 ine ss_win_mssql_104 txt file.
{[snippet from mentioned file}
$password = ($password == NULL ? 'graphit' : $password);

Don't forget to change the extention to .php as its run by the scriptserver.

Well it seems my work here is done . Thanks dionvdc.

[lints]

If you get a chance, please post these to the templates area of the Docs site:

http://docs.cacti.net/templates
http://docs.cacti.net/templates.guidelines

Done and done: http://docs.cacti.net/usertemplate:host ... :sqlserver

[mcutting]

Interesting thing for me is that I can't seem to get these to return any data. Even running the output from the poller (cache) directly into the command line returns no data ?

Odd...

[lints]

Strange indeed.

No data as in you get all 0's or nothing at all?

Do you have the MSSQL module loaded in PHP?

[mcutting]

Nothing at all. Don't think I have MSSQL support in PHP, as it's not supported directly in Ubuntu (I think)

Is this for Windows ?

[lints]

No, I'm running CentOS 5.4 x64. Besides, it should be platform independent as it relies on the PHP extension and not anything platform specific.

On my system it was just a matter of running 'yum install php-mssql', so it might be the same on Ubuntu (but with apt-get install) instead.

Edit: I just did a quick Google search for some ideas, and it looks like the 'php5-sybase' package has what you need: http://packages.ubuntu.com/lucid/php5-sybase