SQL Server 2005 Templates

[peterclo]

I tried that but I get the same exact error message. I've also tried using the sa account to connect but to no avail. Just in case, I'm using SQL Server 2005 SP2 on the default port on a Windows 2003 R2 Enterprise server.

[aleu]

I tried that but I get the same exact error message. I've also tried using the sa account to connect but to no avail. Just in case, I'm using SQL Server 2005 SP2 on the default port on a Windows 2003 R2 Enterprise server.

I got the same behavior as before (same error message), as well.

[lints]

I tried that but I get the same exact error message. I've also tried using the sa account to connect but to no avail. Just in case, I'm using SQL Server 2005 SP2 on the default port on a Windows 2003 R2 Enterprise server.

I got the same behavior as before (same error message), as well.

Well I'm not sure what the problem is. It might be caused by the SSL issue if you're forcing encryption, but otherwise, it has to be a wrong IP/port combination or something network related (firewall, routing, NAT, etc..)

[peterclo]

That's weird, I don't use SSL and both my cacti server and my SQL Server are on the same network, so no NAT and no firewall in the middle.

I've tried doing the same with another SQL Server (also on the local network) and I get the same error.

[aleu]

Well I'm not sure what the problem is. It might be caused by the SSL issue if you're forcing encryption, but otherwise, it has to be a wrong IP/port combination or something network related (firewall, routing, NAT, etc..)

Finally, I was allowed to disable encryption on one of our SQL servers and can confirm that this script works perfectly when no encryption is in place.
Now, I need to figure out whether there is anything that can be done to make it work with forced encryption. Thanks for your help guys.

[lints]

That's weird, I don't use SSL and both my cacti server and my SQL Server are on the same network, so no NAT and no firewall in the middle.

I've tried doing the same with another SQL Server (also on the local network) and I get the same error.

Perhaps there is a firewall on the Windows server?

Also, it's possible that you don't have the TCP/IP protocol enabled for the instance, which I believe is what PHP's libraries require.

[Axilla]

i've been trying all day to get this working. I follwed your instructions from start to finish. The server is showing as UP in cacti, but my graphs are returning all NANS..

i have php-mssql and memcached installed..

ss_win_mssql.php is chown to cactiuser.

I get the following error when in debug poller on all my graphs.

07/27/2010 04:10:02 PM - CMDPHP: Poller[0] Host[18] DS[267] SERVER: /var/www/html/cacti/scripts/ss_win_mssql.php ss_win_mssql 192.168.x.x: transactions , output: U

the associated RRD files are full of NAN

1280262000: nan
1280262300: nan
1280262600: nan
1280262900: nan
1280263200: nan
1280263500: nan
1280263800: nan
1280264100: nan

ds[mssql_bckrst_bytes].index = 0
ds[mssql_bckrst_bytes].type = "COUNTER"
ds[mssql_bckrst_bytes].minimal_heartbeat = 600
ds[mssql_bckrst_bytes].min = 0.0000000000e+00
ds[mssql_bckrst_bytes].max = NaN
ds[mssql_bckrst_bytes].last_ds = "U"
ds[mssql_bckrst_bytes].value = NaN
ds[mssql_bckrst_bytes].unknown_sec = 2

any tips appreciated.

When I try to run the .php script i get the following errors with errors turned on.



PHP Notice: Undefined offset: 1 in /var/www/html/cacti/scripts/ss_win_mssql.php on line 17
PHP Fatal error: Class 'Memcache' not found in /var/www/html/cacti/scripts/ss_win_mssql.php on line 27

[Axilla]

Think my issue has been solved.. Nan's have gone away on the graph after i installed some dependencies that memcached.org doesn't talk about, graphs have started updating

awesome

[lints]

Happy you were able to fix it.

I guess I forgot to mention that you'll need the PHP Memcached extensions installed as well.

[Axilla]

Happy you were able to fix it.

I guess I forgot to mention that you'll need the PHP Memcached extensions installed as well.

yea, but you also need some lib dependencies for the memcached install that even their site doesn't mention so its a bit tricky. When I went to their site to install it just said to run yum install memcached and thats it.. when there are some lib's associated with it that you need as well besides the php-memcache

no big deal.. figured it out. Thanks for a great script!

Question, if I wanted to go about graphing replications for MSSQL.. Any tips?

[lints]

Yeah, but it depends on what you want to graph.

I just checked out what's exposed through the performance counters view and it looks like you're limited to the local publications of any given instance.

Any of the counters here:
http://msdn.microsoft.com/en-us/library/ms151754.aspx
can be graphed pretty easily.

Do you want individual replication stats or general for the instance?

[Axilla]

General for the instance would be fine, but stats would be GREAT

[lints]

Well, if you want general replication stats, it should be possible to modify the current script to pull those counters and aggregate them (since there's no _Total counter built-in).

If you want, I might be able to try and build an SQL query that will do it for ya. From there, it should be just a simple matter of updating the script and building the graph template.

On the other hand, if you want individual publication stats, you'll have to build a new script (maybe using the existing one as a base) and data query.

The SQL query you're going to want would look something like:

Code: Select all

SELECT * FROM sys.dm_os_performance_counters 
WHERE [object_name] LIKE '%Replication%' AND object_name NOT LIKE '%Agents%'

You wouldn't be able to properly aggregate the publication graphs unless you embedded a unique identifier in the name of each one. I suppose the name of the DB would work, but I'm not 100% sure.

[dust2k]

Hi Guys, after I install the template and scripts, I found I couldn't get the PHP connect to the SQL server 2005, there is no error reported at all, and I've test the connection from SQL management console without any problem, but PHP just simply quit without give anything at all. anybody have idea what the possible reason?

I have also run the php script individually in cmd window, it's same just as in poller.

Following is my configuration:
===============================
Cacti Version 0.8.7g
Cacti OS win32
SNMP Version NET-SNMP version: 5.4.2.1
RRDTool Version RRDTool 1.2.x
Hosts 27
Graphs 315
Data Sources Script/Command: 16
SNMP: 84
SNMP Query: 232
Script - Script Server (PHP): 30
Script Query - Script Server: 28
Total: 390
Poller Information
Interval 300
Type cmd.php
Items Action[0]: 1084
Action[1]: 16
Action[2]: 78
Total: 1178
Concurrent Processes 1
Max Threads 1
PHP Servers 1
Script Timeout 25
Max OID 10
Last Run Statistics Time:298.8840 Method:cmd.php Processes:1 Threads:N/A Hosts:28 HostsPerProcess:28 DataSources:1178 RRDsProcessed:343
PHP Information
PHP Version 5.2.3
PHP OS WINNT
PHP uname Windows NT PJ1BJ0DARMONI01 5.2 build 3790
PHP SNMP Installed
max_execution_time 30
memory_limit 128M

My Php configuration
=========================
mssql
--------------------------------------------------------------------------------

MSSQL Support enabled
Active Persistent Links 0
Active Links 0
Library version 7.0

Directive Local Value Master Value
mssql.allow_persistent On On
mssql.batchsize 0 0
mssql.compatability_mode Off Off
mssql.connect_timeout 5 5
mssql.datetimeconvert On On
mssql.max_links Unlimited Unlimited
mssql.max_persistent Unlimited Unlimited
mssql.max_procs Unlimited Unlimited
mssql.min_error_severity 10 10
mssql.min_message_severity 10 10
mssql.secure_connection Off Off
mssql.textlimit Server default Server default
mssql.textsize Server default Server default
mssql.timeout 60 60

[dust2k]

fixed the problem, I believe the ss_win_mssql.php need to be modified for Windows OS, mssql_connect() function shouldn't use port in windows environment.

so for windows OS user, please change

Code: Select all

if (! $link = mssql_connect($host.':'.$port, $username, $password) )

to

Code: Select all

if (! $link = mssql_connect($host, $username, $password) )

, otherwise the connection won't be successful.