Halcyon PrimeAlert (R) for Microsoft SQL Server
Version 1.0.3
For use with Sun Management Center
README.txt
-----------------------------------------------------------------------
Included Components
===================
This distribution includes a single PrimeAlert module:
PrimeAlert for Microsoft SQL Server
Nomenclature
============
- 'Sun Management Center' was formerly called 'Sun Enterprise SyMON'.
This product only supports Sun Management Center 2.1 or higher.
- Halcyon has developed 'PrimeAlert Agents' for a number of platforms
(Windows NT 4 and 2000, HP-UX, and Linux) that allow non-Solaris
hosts to be monitored by Sun Management Center.
- <INSTALLDIR> is a user-selected directory for installing PrimeAlert
agent and console files on a Windows host. The default value is
C:\Program Files\Halcyon\PrimeAlert.
Supported Platforms
===================
The following table summarizes the platform support for the various
components contained within this distribution:
Operating Base Agent Server Console
System Product Layer Layer Layer
----------------- ----------------- ----- ------ -------
Solaris 2.6-2.8 SunMC 2.1.x/3.0 x x
Windows NT 4/2000 PrimeAlert Agent x
for Windows
Windows NT 4/2000 SunMC 2.1.x/3.0 x
Informational Text Files
========================
A number of useful informational text files are included with this
distribution:
- README.txt
- INSTALL.txt
- TroubleShooting.txt
- ReleaseNotes.txt
- LICENSE.txt
- COPYRIGHT.txt
These files are located in the base directory of the distribution
tarball, and after installation, in the directory:
After installation on a Solaris host -
/var/opt/SUNWsymon/install/HALSQLServer
After installation on a Windows host -
<INSTALLDIR>\docs\HALWinDBMSAlertMicrosoft
Installation and Uninstallation Instructions
============================================
This module supports both versions 6.5 and 7.0 of Microsoft SQL Server.
For installation or uninstallation instructions, refer to the file
INSTALL.txt (see the above section 'Informational Text Files').
Troubleshooting
===============
For troubleshooting information, refer to the TroubleShooting.txt file
(see the above section 'Informational Text Files').
The release notes file, ReleaseNotes.txt, which is located in the same
places as the TroubleShooting.txt file, is also a valuable reference.
It contains information about bug fixes, enhancements, known problems,
and upgrade strategy for the current release.
HTML Help Documentation
=======================
To view the Help documentation for PrimeAlert for Microsoft SQL Server,
see the following file in the "doc" subdirectory of the
HALSQLServer_1.0.3 directory in the tar file HALSQLServer_1.0.3.tar:
./doc/SQLServer/HALWinDBMSAlertMicrosoft-h.html
This documentation is also available by selecting "Help" from the
module's pop-up menu in the console's Details window.
License
=======
Please read the license agreement in the file LICENSE.txt, which is
located in this directory.
Without purchasing a license, PrimeAlert for Microsoft SQL Server will
only operate for a trial period of 30 days.
If you wish to purchase a license to use the PrimeAlert for Microsoft
SQL Server, please contact us at:
Halcyon Inc.
2300 Yonge Street
Suite 1801, Box 2419
Toronto, Ontario M4P 1E4
Canada
http://www.HalcyonInc.com
mailto:info@HalcyonInc.com
Tel: 416-932-4647
Fax: 416-932-4711
Overview
========
PrimeAlert for Microsoft SQL Server is an add-on module for the
PrimeAlert Agent for Windows. It monitors the health indicators for
Microsoft SQL Server versions 6.5 and 7, and allows database resources
to be viewed and monitored from a Sun Management Center console.
Loading an Instance of PrimeAlert for Microsoft SQL Server
==========================================================
1. Launch the Sun Management Center console application, and select a
domain that contains the host where PrimeAlert for Microsoft SQL
Server will be loaded.
2. In a Host Details window, select "Load Module..." from the Module
Menu (or, in the Main console window, select "Load Module..." from a
host's Pop-up Menu).
3. Select PrimeAlert for Microsoft SQL Server from the Module Picklist
and click on OK. This launches the Module Loader in which you are
required to enter the following information (under the Module and
Database Parameters tabs):
Module
The name of the module assigned to files as part of the
module file naming convention. This field is read-only.
Module Name
The actual name of the module. This field is read-only.
Module Description
The short description of the module. This field is
read-only.
Version
The version number of the module. This field is
read-only.
Enterprise
The Enterprise where this module will be loaded in the
Agent's MIB. This field is read-only.
Module Type
The module's type, which determines where this module
will appear in the module hierarchy. This field is
read-only.
Instance
Enter a short, alphanumeric description (15 characters or
less, no spaces or special characters) that has not been
used previously for other instances of PrimeAlert for
Microsoft SQL Server currently loaded on the same agent;
this unique description is used to differentiate the
various instances of PrimeAlert for Microsoft SQL Server
loaded on the same agent. The description you enter here
is appended to the internal Module name described above.
For example, if the description "mytest" is entered
by the user, this instance is appended to the internal
module name "HALWinDBMSAlertMicrosoft" creating the
following unique instance name:
HALWinDBMSAlertMicrosoft+mytest
This instance name is useful in specifying alarm actions
using PrimeAlert EventAction.
Description
Enter a short formal description of the module. This
description is used to identify the module in the console
window.
The short description you enter in this field will be
appended with the Module Name described above.
Database Parameters
Server (ODBC Data Source Name)
The name of the ODBC System Data Source Name (DSN)
through which database access will be obtained. This
should match the 'Name' field which the Microsoft SQL
Server DSN Configuration Data Source Wizard states you
will 'use to refer to the data source'. This should have
been created automatically during the module install, or
you may have configured it manually after the module
install (see the INSTALL.txt file).
Server Directory
Installation directory of Microsoft SQL Server, within
which the MS SQL Server log subfolder exists. Please use
the '/' character instead of '\' as pathname delimiters.
Account Name
The user name with which to connect to the database.
Please read the Security and Access Issues section below.
Account Password
The password with which to connect to the database.
Please read the Security and Access Issues section below.
Databases To Monitor
A space separated list of database names which are on the
MS SQL Server accessible through the ODBC DSN. All
databases which you intend to monitor should be listed.
If you use MS SQL Server's scheduling, a key database to
monitor is msdb.
4. Once you have entered the required information, click on the OK
button to load the module.
Security and Access Issues
==========================
It is recommended that a system DSN that uses Integrated Security be
used, and the Halcyon PrimeAlert Agent service be run under a user that
has administrative authority on the MS SQL Server.
When integrated security is used, a dummy user name and password should
be specified when loading the module.
If the system DSN is not using Integrated Security or the PrimeAlert
agent service is not running under a user who has administrative
privileges, and you wish to be able to see the input buffer of each
running process in the User Activity table (very useful), you will have
to specify the SA user and password when loading the module.
IMPORTANT: Ensure that access to the PrimeAlert cfg folder is highly
restricted. It should have already been configured during the agent
install, but this is a good time to review the settings and ensure that
it meets your security policy.
You cannot use a username which is merely aliased to the SA account in
the master database. The command which is used to get inputbuffer for
each running process is non-transferable.
If you decide not to utilize the above options, and wish to use another
internal MS SQL Server user with un-integrated security on the DSN, the
following permissions must be granted to the internal database user
that you do use:
- In the master database execute privilege on sp_server_info,
sp_helpdb, sp_spaceused, and select privilege on the spt_values
table.
- The user must be a valid user in the msdb database. When
monitoring MS SQL Server 6.5, the user must have execute permission
on sp_sysbackuphistory. When monitoring MS SQL Server 7.0, the
user must have select permission on the table backupset.
- Do not attempt to 'revoke' or 'grant' specific permissions in the
model database through the Enterprise Manager. The results are
undefined. If you wish to monitor the model database give the user
access to this database.
- The user must be a valid user of any other databases if you wish to
monitor them, however no specific permissions need be granted.
- You can test your intended user's access by doing a select from the
specified table and executing the stored procedures specified above
through wisql (do not use integrated security) using the user and
password you intend to use in the module, as well as a select from
the above-mentioned tables.
Configuration
=============
Monitoring of the SQL Server process and it's related processes is
done through the Server Processes section of the module. For MS SQL
Server 6.5, the only related process is the SQL Executive process. When
MS SQL Server 7 is being monitored, there are also the snapshot agent,
log reader agent, distribution agent, merge agent, and the SQL Server
agent.
By default, none of the related processes (those other than the main
sqlservr.exe process) have alarms configured for them. If any of these
other processes are mission critical for you and should be running for
specific periods of time, you should configure alarms on those items
appropriately.
The SQL Executive process is the local administrative agent responsible
for scheduling tasks (including replication), handling alerts, and
'monitoring' SQL Server as can be configured within the Enterprise
manager. If none of these are relevant to you and/or you are not
running the SQL Executive service, you should remove the alarm
threshold that is set on the SQL Executive process row in the Server
Processes section of the module to prevent annoyance alarms. Right
click on the Count field, open the Attribute Editor, select the Alarms
tab, and change the alarm threshold to zero.
For descriptions of the MS SQL Server 7 related processes, consult
the 'MS SQL Server Books Online'.
If you are monitoring MS SQL Server 7, it is important that you review
the alarm thresholds set on the 'Total Locks' (total locks in use)
item in the Locks table of the Resources folder.
This is due to the fact that SQL Server 7 dynamically allocates locks
and does not run out of locks until it runs out of free physical
memory, so the 'Maximum Locks' and '% Locks Used' fields are reported
as 'n/a'.
We suggest that you monitor your locks usage over the course of a week
or month by turning on that nodes 'history' function and graphing it,
using that data to estimate what your maximum locks usage will be in
the medium term future, and set an alarm on the lock usage that is
above that level. Thus you will be informed when lock usage is
unusually high.
Similarly, if you are monitoring MS SQL Server 7, it is important that
you review the alarm thresholds set on 'Total' current user
connections, within the User Connections table in the User Management
sub-section. Alarm thresholds should be set on this in a similar manner
as noted above for locks.
Again this is due to the fact that SQL Server 7 dynamically allocates
connections, and does not run out of connections until it's hardcoded
maximum limit (~32,000) is reached or memory runs out. As each
connection takes ~40 kilobytes, this means that in order to reach the
hardcoded maximum limit, 1.2 GB of free memory would be taken up. As
this is unlikely, SQL Server 7 will likely deny connections before it
reaches the hard coded limit, and thus before the '% Used' figure in
the module reaches 100%.
Next, review all of the initial errors being reported and the related
pre-set thresholds causing the alarms.
Some alarm thresholds are set to give you an initial warning on the
setup of your MS SQL Server or Windows Server. For example, you might
receive a warning that the NT 4 Service pack is not 5 or greater (as
recommended for our product), or that your MS SQL Server has fewer than
20 allowed connections or 20,000 locks (limits that will prevent
un-interrupted use of anything but demo servers with no real
databases).
If you decide to not change your SQL or Windows server configuration as
recommended, you should change these pre-set alarm thresholds to
prevent further annoyance alarms.
If you decide that an alarm is indicative of something which you should
fix and deal with at the source, you should acknowledge the alarm from
the Alarms tab of the console. Thus the pre-set alarm thresholds will
remain for any future re-occurrences of the problems.
Finally, review the thresholds on all of the nodes within the module,
starting with the ones you find most important.
You should adjust the thresholds to values you feel will give you more
appropriate warnings of unacceptable states given the conditions of
your SQL server and its usage, and yet not create annoyance alarms.
Update these settings a few more times in the near future.
Notes
=====
1. Note that the Total number of user connections can exceed the
Maximum by four, and thus there can be a %-Used value greater than
100%.
This is because Microsoft does not make a distinction in most places
between the 4 main system processes and all the other processes
running on the database, with the exception of the comparison
against the maximum number of user connections configured.
In other words, MS-SQL Server will allow N user processes to exist
as well as the 4 system processes, for a total of N+4 processes in
the sysprocesses table, where N is the maximum configured number of
user connections.
Our total user connections will match that reported by perfmon, and
that obtained by counting the number of processes within the
master..sysprocesses table.
We did not want to differ from these values so as to cause confusion
100% of the time. We also did not want to do 'hidden' math, whereby
we could have been reporting 54 connections out of 100 maximum with
a 50% used value. Instead we thought it better to provide you with a
little more breathing room than you thought was actually there.
2. Note that certain data items will not be available if running
'remotely'. By 'remotely' we mean loading the module on a Windows
system with the DSN pointing to an MS SQL server on another host.
Specifically, the 'Server Processes' subsection monitoring the
related Windows server processes and the 'Error Log' subsection
monitoring the MS SQL server logfile.
3. A transaction log which has a total size of zero is actually 'shared
with data'. In other words, there is no transaction log device per
se, transaction data is stored in the database device itself with
data.
4. Note that the 'Last Dump' item within the 'Transaction Logs' table
may indicate that a transaction dump has been taken, when in reality
there was no transaction dump on that date.
For example, if the database in question has never been dumped, it
is impossible for a transaction dump to have ever occurred.
This apparent dump date is due to the fact that MS-SQL Server uses
the 'last dump' information internally to help track the interval
over which a future transaction dump would be valid. I.e.: In this
circumstance were a transaction dump to be taken now, the date that
was held in the 'last dump' field denotes the 'start date' of the
range of transactional information that would be written.
You may have already noticed some 'peculiarities' about how
Enterprise Manager displays 'date ranges' and 'dump sizes' when you
have been loading previous database and transaction dumps. This is
a manifestation of that.
5. Note that in the User Activity table, SPID #2, the 'lazy writer',
does not have any alarm thresholds set. This is due to the fact that
the process never 'finishes' from the time that the MS-SQL Server
starts it, so it keeps accumulating runtime in small increments.
Thus, it would inevitably exceed any threshold, and since it
alternates quite regularly between 'sleeping' and 'runnable', it
would generate many false alarms.
This explanation also applies to SPIDs 3 and 4, and on MS SQL Server
7, SPIDs 5 and 6. Therefore, these specific processes are excluded
from the default alarms on this table.
Furthermore the 'last command' data for these same SPIDs is also not
collected, as it is not applicable to server processes like these.
6. The 'instance' module parameter has a limit of 15 characters. This
parameter is only specified by the user when manually loading the
module. It is used internally in the module and is otherwise not
normally observed by the user.
7. Uninstalling the module does not remove the ODBC DSN entries created
by the automatic configuration during the install.
This is necessary to prevent accidental removal of a DSN that could
be put to use by other applications (should the administrator chose
to do so).
It is also necessary for a trouble-free module 'upgrade'. To upgrade
a module, the agent is stopped, the module is un-installed (but the
configuration files that define what was loaded and how it was
configured will still remain), the newer module is installed, and
the agent is re-started. As the module was never 'unloaded', the
agent loads it back in with it's previously configured state, but
using the newly upgraded module. For this to work without manual
tweaks or intervention, the old ODBC DSN must still exist with the
same name.
8. If MS SQL Server 7 is being monitored, the 'Data Cache' and 'Proc
Cache' items within the Memory Usage table of the Resources sub-
section will be 'n/a', and the 'Total Memory Used' item will reflect
the actual current memory usage of MS SQL Server. These differences
are due to MS SQL Server 7's dynamic memory allocation. Setting an
alarm threshold on the Total Memory Used item is not likely to be
productive due to MS SQL Server 7's dynamic memory management
algorithms.
9. If you upgrade from MS SQL Server 6.5 to 7, you should manually
remove the alarm thresholds that have been set on SPIDs 5 and 6
after the upgrade.
All other alarm thresholds should be again reviewed after the
upgrade, as MS SQL 7 is capable of handling larger databases and
more connections and activity.
10. Attempts to add a row to the tables within the 'Server Processes'
or 'Error Log' sections will fail if the 'add row' is invoked
by right clicking on the table's header. An empty window will
appear instead of the 'add row' window. Instead, right click on an
existing row and select 'add row'.
11. A database may be shown as greater than 100 percent full when this
is not actually the case. This can be corrected by dumping your
transaction log and running your standard series of dbcc and
updateusage commands in single user mode. Refer to the
TroubleShooting.txt file for further details.
---//---