An aid for monitoring the status and performance of SQL Server is the Simple Network Management Protocol (SNMP). This service is widely used across the industry for enterprise-wide management, using its cross-platform capabilities, monitoring, and event-processing features.
Ideally, you will have SNMP support installed for Windows before you install SQL Server. The SQL Setup program will then copy the files MSSQL.MIB and SQLSNMP.DLL. If SNMP is not installed before you install SQL Server, you can turn this feature on after you have installed the service. The SNMP files for SQL Server will already have been copied into the MSSQL\BINN directory, and all you need do is open the Server Network utility and select the Enable SNMP check box at the bottom of the dialog box.
SNMP's architecture can be broken into two main pieces: the SNMP network management machine and the SNMP agent (the system being monitored, which in this case is SQL Server). The SNMP agent responds to requests from the management machine for information, but can also trap and notify when certain critical events occur.
Under Windows, the data being accessed by SNMP is held in the Registry, and the agent service makes this information available in a form readable by SNMP monitors. The events that elicit SNMP traps are not defined in an ad hoc way by users, but rather through a Management Information Base (MIB). The MIB is a data file that has all the details about the objects available to be managed via SNMP. Different services provide different MIBs; for example, Internet Information Server (IIS) has an INETSRV.MIB file that is implemented using the IIS.DLL file.
Security for SNMP is implemented using a shared password (the community name) so that only systems with the correct name can manage associated agents. To further increase the security of your SNMP installation, you can configure an SNMP packet filter so that control packets are accepted only from certain host systems.
When the SNMP service is started on the SQL Server machine, the SQLSNMP.DLL file is loaded. This process contains a trap mechanism for raising SQL Server alerts and is what coordinates SQL Server with the NT-based SNMP service.
On the Windows NT Server Resource Kit CD-ROM, you will find a number of SNMP utilities, one of which will allow you to carry out simple management tasks (snmputil). Using other utilities, you can compile new MIBs to allow monitoring of other performance counters.
snmputil allows you to easily test your installation, but you will need a full management tool for use in your production environment, such as HP OpenView, Unicenter TNG, SunNet Manager, Advent, or Intraspection. Each of these provides a different variety of graphical tools, add-ons, and foundation objects for developing tools in Visual Basic, Java, and many other development languages.