Cacti (home)ForumsDocumentation

Microsoft SQL Server

type:
user_template_host
usertemplate:
sqlserver
author:
lints
description:
Microsoft SQL Server 2000/2005/2008/2008 R2
Cacti:
0.8.7e
date:
Jul-28-2010
includes:
yes
templates:
microsoft,
sql,
windows

Download

Installation

  1. Install PHP's MSSQL libraries
    • If using Red Hat based systems (RHEL, CentOS, Fedora, etc..), run yum install php-mssql
    • If using Ubuntu based systems, run sudo apt-get install php5-sysbase
    • If using FreeBSD based systems, run:
      1. cd /usr/ports/databases/php5-mssql
      2. make install
      3. cp /usr/local/etc/freetds.conf.dist /usr/local/etc/freetds.conf
  2. Un-tar the archive
  3. For each SQL Server 2000 instance you want to graph:
    1. Launch the Enterprise Manager, open the file sql_server_2000.sql from under the sql scripts folder
    2. Update line 4 with the username/password combination you want to use
      • If you changed the username on line 4, update the @loginname on lines 6, 8 & 10 to match
    3. Run the script
  4. For each SQL 2005/2008/2008 R2 instance you want to graph:
    1. Launch the SQL Management Studio, open the file sql_server_2005-2008.sql from under the sql scripts folder
    2. Update line 4 with the username/password combination you want to use
      • If you changed the username on line 4, update the @loginname on lines 6, 8 & 10 to match
    3. Run the script
  5. In your preferred text editor open ss_win_mssql.php from under the scripts folder
  6. If you plan to use the same username/password combination on all your SQL Instances, update lines 19 & 20 with those you chose.
  7. I use MemCached to speed up the polling process so the code is setup to use it. If you choose not to, comment out (or delete) lines 24-29 and 72-73
    • You'll need to install the MemCached service as well as the PHP libraries which should be available through PECL
  8. Upload ss_win_mssql.php to ./scripts
  9. Import cacti_host_template_windows_-_sql_server.xml via the console
  10. Assign the Windows - SQL Server host template to a device
  11. Create the graphs
    • For each graph you'll be prompted for a port username and password. As mentioned in step 6, you can leave these blank if all the instances are setup the same. If they're not, fill in these fields.
    • The default TCP port for the instance is setup to 1433. If that's not correct for the instance, specify that here as well.

Compatability

These templates have been tested with:

  1. SQL Server 2000, 2005, 2008 and 2008 R2
  2. Windows 2003, 2008 & 2008 R2

Screenshots

Total backup/restore throughput of the instance

Buffer Cache Hit ratio (calculated from server start-up)

Connections

Log Cache Hit ratio (calculated from server start-up)

Log Flush I/O traffic (bytes/sec)

Log Flushes (per sec)

SQL Server Memory

Page I/O (per sec)

Page Splits (per sec)

Procedure Cache Hit ratio (calculated from server start-up)

SQL Compliations (per sec)

Temporary Tables

Transactions






Personal Tools