Cacti (home)ForumsDocumentation
Cacti: offical forums and support
It is currently Fri Dec 15, 2017 8:42 pm

All times are UTC - 5 hours




Post new topic Reply to topic  [ 124 posts ]  Go to page 1, 2, 3, 4, 5 ... 9  Next
Author Message
 Post subject: SQL Server 2005 Templates
PostPosted: Tue Jun 15, 2010 1:11 pm 
Offline

Joined: Fri Jun 11, 2010 10:33 am
Posts: 5
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 17, 2010 1:56 am 
Offline

Joined: Wed May 26, 2010 9:13 pm
Posts: 23
yeah I really need this too.please help.
I need to monitor process blocking on SQL server 2000 or 2005.


Top
 Profile  
 
 Post subject: SQL Server graphs
PostPosted: Thu Jun 17, 2010 12:55 pm 
Offline
Cacti User

Joined: Thu Nov 08, 2007 2:00 pm
Posts: 107
Location: Ottawa, Canada
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.


Attachments:
File comment: SQL 2005/2008 script to create a login and grant it the permissions needed for the graphs.
CHANGE USERNAME AND PASSWORD

sql_server_2005-2008.txt [433 Bytes]
Downloaded 2412 times
File comment: SQL 2000 script to create a login and grant it the permissions needed for the graphs.
CHANGE USERNAME AND PASSWORD

sql_server_2000.txt [373 Bytes]
Downloaded 976 times
File comment: Script Server script. Change the extension to 'php' and place in the scripts folder on your server.
CHANGE USERNAME AND PASSWORD

ss_win_mssql.txt [6.26 KiB]
Downloaded 2449 times
File comment: Host Template for the SQL Server. Exported from Cacti 0.8.7e.
cacti_host_template_windows_-_sql_server.xml [150.74 KiB]
Downloaded 2100 times
Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 17, 2010 1:03 pm 
Offline
Cacti User

Joined: Thu Nov 08, 2007 2:00 pm
Posts: 107
Location: Ottawa, Canada
Examples of the graphs the templates create.


Attachments:
File comment: Temporary Tables
Transactions

example_5.PNG
example_5.PNG [ 49.36 KiB | Viewed 20733 times ]
File comment: Procedure Cache
SQL Compliations
Table Scans

example_4.PNG
example_4.PNG [ 79.38 KiB | Viewed 20733 times ]
File comment: Memory
Page I/O
Page Splits

example_3.PNG
example_3.PNG [ 71.23 KiB | Viewed 20733 times ]
File comment: Log Cache
Log Flushes
Log Flush I/O

example_2.PNG
example_2.PNG [ 69.67 KiB | Viewed 20733 times ]
File comment: Backup/Restore I/O
Buffer Cache
Connections

example_1.PNG
example_1.PNG [ 66.63 KiB | Viewed 20733 times ]
Top
 Profile  
 
 Post subject: Re: SQL Server graphs
PostPosted: Thu Jun 17, 2010 9:51 pm 
Offline

Joined: Wed May 26, 2010 9:13 pm
Posts: 23
lints wrote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 17, 2010 10:42 pm 
Offline

Joined: Wed May 26, 2010 9:13 pm
Posts: 23
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.


Top
 Profile  
 
 Post subject: Re: SQL Server graphs
PostPosted: Thu Jun 17, 2010 11:44 pm 
Offline

Joined: Tue Feb 16, 2010 3:54 pm
Posts: 46
denyingthetruth wrote:

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


Top
 Profile  
 
 Post subject:
PostPosted: Thu Jun 17, 2010 11:46 pm 
Offline

Joined: Tue Feb 16, 2010 3:54 pm
Posts: 46
denyingthetruth wrote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 18, 2010 12:21 am 
Offline
Developer
User avatar

Joined: Thu Feb 20, 2003 10:16 am
Posts: 1637
Location: Canada
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

_________________
--
Live fast, die young
You're sucking up my bandwidth.

J.P. Pasnak,CD
CCNA, LPIC-1
http://www.warpedsystems.sk.ca


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 18, 2010 8:37 am 
Offline
Cacti User

Joined: Thu Nov 08, 2007 2:00 pm
Posts: 107
Location: Ottawa, Canada
dionvdc wrote:
denyingthetruth wrote:
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.


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 18, 2010 10:27 am 
Offline
Cacti User

Joined: Thu Nov 08, 2007 2:00 pm
Posts: 107
Location: Ottawa, Canada
Linegod wrote:
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


Last edited by lints on Fri Jun 18, 2010 10:28 am, edited 1 time in total.

Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 18, 2010 10:28 am 
Offline
Cacti Guru User

Joined: Mon Oct 16, 2006 5:57 am
Posts: 1876
Location: United Kingdom
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...

_________________
Quote:
Cacti Version 0.8.8b
Cacti OS Ubuntu LTS
RRDTool Version RRDTool 1.4.7
Poller Information
Type SPINE 0.8.8b


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 18, 2010 10:32 am 
Offline
Cacti User

Joined: Thu Nov 08, 2007 2:00 pm
Posts: 107
Location: Ottawa, Canada
Strange indeed.

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

Do you have the MSSQL module loaded in PHP?


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 18, 2010 11:06 am 
Offline
Cacti Guru User

Joined: Mon Oct 16, 2006 5:57 am
Posts: 1876
Location: United Kingdom
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 ?

_________________
Quote:
Cacti Version 0.8.8b
Cacti OS Ubuntu LTS
RRDTool Version RRDTool 1.4.7
Poller Information
Type SPINE 0.8.8b


Top
 Profile  
 
 Post subject:
PostPosted: Fri Jun 18, 2010 12:11 pm 
Offline
Cacti User

Joined: Thu Nov 08, 2007 2:00 pm
Posts: 107
Location: Ottawa, Canada
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


Top
 Profile  
 
Display posts from previous:  Sort by  
Post new topic Reply to topic  [ 124 posts ]  Go to page 1, 2, 3, 4, 5 ... 9  Next

All times are UTC - 5 hours


Who is online

Users browsing this forum: No registered users and 2 guests


You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot post attachments in this forum

Search for:
Jump to:  

Protected by Anti-Spam ACP Powered by phpBB © 2000, 2002, 2005, 2007 phpBB Group