Chapter 9 - Integrating SQL Server with Other Tools

Microsoft SQL Server™ can be integrated with other tools to provide flexible communication, network monitoring, and high availability of SQL Server on a cluster.

  • SQL Mail provides flexible communication enabling you to receive e-mail messages generated by SQL Server. Messages can be triggered to provide you with the status of a job or a warning caused by an alert. SQL Mail can include a result set in a reply to e-mail messages that contain queries.

  • Simple Network Management Protocol (SNMP) provides the ability to monitor the status of SQL Server installations over multiple platforms. You can monitor performance information, access databases, and view server and database configuration parameters.

  • SQL Server Failover Support provides automatic recovery of database operations for high availability. High availability is accomplished by installing SQL Server on a cluster, where it creates a single SQL Server virtual server for user connections.

On This Page

SQL Mail

SNMP

SQL Server Failover Support

External Tools Support

SQL Mail

SQL Mail allows Microsoft SQL Server to send and receive e-mail by establishing a client connection with a mail server. Unlike SQL Server version 6.5, which used a single service to handle mail, SQL Server 7.0 uses two services to handle mail. MSSQLServer processes mail for all of the mail stored procedures. SQLServerAgent (formally SQLExecutive) does not use SQL Mail to send e-mail. Instead, SQLServerAgent uses its own mail capabilities that are configured and operated separately from SQL Mail.

The SQL Server Agent mail features will be referred to as SQLAgentMail to distinguish it from the SQL Mail features provided by MSSQLServer. SQL Mail establishes a simple MAPI connection with a mail host while SQLAgentMail establishes either a simple or extended MAPI connection. Both SQL Mail and SQLAgentMail can connect with Microsoft Exchange Server, Microsoft Windows NT Mail, or a Post Office Protocol 3 (POP3) server.

Using SQL Mail extended stored procedures, messages can be sent from either a trigger or a stored procedure. SQL Mail stored procedures can manipulate data, process queries received by e-mail and return the result set by creating a reply e-mail. SQLAgentMail can be configured to send an e-mail when an alert is triggered or when a scheduled task either succeeds or fails.

Using SQL Mail

SQL Mail requires a post office connection, a mail store (mailbox), a mail profile, and the Microsoft Windows NT domain user account used to log in to Microsoft SQL Server. SQL Mail consists of a number of stored procedures, which are used by SQL Server to do one of the following: process e-mail messages that are received in the designated SQL Mail account mailbox, or reply to e-mail messages generated by the stored procedure xp_sendmail.

SQL Mail must run using a mail profile created in the same domain account that is used to start SQL Server 7.0. Under the Support Services folder in SQL Server Enterprise Manager, you can see a graphical depiction of the SQL Mail Service and determine if the service is running. You can start SQL Mail automatically by clicking Autostart SQL Mail when SQL Server Starts. After SQL Mail starts, you can use the stored procedures to send and receive mail.

Processing an E-mail Request Received by SQL Server

To process e-mail automatically, you must create a regularly scheduled job that uses the stored procedure, sp_processmail. sp_processmail checks your SQL Mail mail profile, and then checks your mailbox for mail. sp_processmail uses xp_sendmail to execute query requests contained in the text of the e-mail, and then returns the result set to the original sender and any additional recipients. For example, a supplier may be allowed to execute a stored procedure that produces current inventory levels for all materials supplied by their organization.

Using SQLAgentMail

SQLAgentMail can use its own domain account and mail profile that is different from the one set up for SQL Mail. With Microsoft SQL Server version 7.0, you can configure SQLAgentMail e-mail messages to be sent when:

  • An alert is triggered.

    Alerts can be configured to send e-mail notification of specific events that occur without implementing SQL Mail. For example, alerts can be configured to notify an operator of a particular database event that may need immediate action.

    For more information about configuring Alerts, see "Defining Alerts" in this volume.

  • A scheduled task, such as a database backup or replication event, succeeds or fails.

    E-mail messages can be sent to a list of recipients informing them of the status of scheduled jobs for possible user action. You can expand the capabilities of jobs to include sending a result set by e-mail to a list of recipients. For example, a monthly inventory report could send SQLAgentMail notification to the designated operators and the result set to the purchasing manager and supplier.

Setting Up SQLAgentMail

SQL Mail and SQLAgentMail can use the same mail profile or they can use different mail profiles. If necessary, each mail profile can be configured within their own domain accounts.

Configuring a SQL Mail Profile

When configured, mail profiles are specific to the Microsoft Windows NT user domain account that is activated when a user logs on to Windows NT successfully. SQL Mail must have a mail profile created in the same user domain account or context that is used to start SQL Server. When a mail stored procedure is executed, SQL Mail looks for the defined mail profile in the domain account that triggered it.

If you plan to use mail stored procedures you must:

  • Have a mail server that is simple MAPI-compliant.

  • Configure a mail profile for MSSQLServer to use to connect to your mail server.

Configuring a SQLAgentMail Profile

If you plan to send notifications to operators by e-mail or pager, you must:

  • Have a mail server that is simple or extended MAPI-compliant.

  • Configure a mail profile for SQL Server Agent to use to connect to your mail server.

  • Have a mail server that is able to communicate with your pagers.

A mail profile is required by SQLAgentMail to start a mail session and send notification by e-mail or pager. A SQL Server Agent mail session is started every time that the SQLServerAgent service is started. You can create the profile with a mail client, such as Microsoft Outlook, that is installed locally on the SQL Server computer.

Sharing a Profile with SQL Mail

SQL Mail and SQLAgentMail sessions may be configured to use the same Windows NT domain user account. When using the same domain account, SQL Mail and SQLAgentMail can share the same mail profile using a common mailbox.

Microsoft SQL Server uses two separate mail sessions:

  • The MSSQLServer service uses a mail session that is referred to as SQL Mail.

  • SQL Server uses this mail session when your database applications execute the xp_sendmail extended stored procedure to send a message or query result set to a recipient.

  • The SQLAgentMail uses a mail session that is exclusive to SQL Server Agent activities.

Creating Separate Profiles

You can configure separate mailboxes for SQL Mail and SQLAgentMail by creating separate mail profiles. There are two ways to accomplish this:

  1. Use separate domain accounts for each service. This requires you to configure a mail profile for each user account.

  2. Use the same domain account for each service and create multiple mail profiles.

For more information about setting up and configuring mail clients and mail profiles, see your Windows NT documentation.

Reviewing SQL Mail Stored Procedures

SQL Mail contains a number of stored procedures, which allow you to develop stored procedures, triggers, and applications. The stored procedures can then be used to manipulate mail, run queries, return a result sets to a list of recipients, or reply to an e-mail containing simple query or stored procedure.

The following table provides a brief description of the extended procedures and how they may be used.

SQL Mail procedures

Function

xp_startmail

Starts a mail client session. The mail client session must be started prior to using any of the other mail stored procedures.

xp_stopmail

Closes a Microsoft SQL Server mail client session.

xp_findnextmsg

Used with sp_processmail in order to process mail in the SQL Mail inbox by accepting a message ID for input and returns the message ID for output.

xp_readmail

Used by sp_processmail to read a mail message from the SQL Mail inbox.

xp_deletemail

Used by sp_processmail to delete a message from the SQL Mail inbox.

xp_sendmail

Used by sp_processmail or as part of a stored procedure, trigger, and used with alerts. Sends a message and a query result set attachment to the specified recipients

sp_processmail

Uses extended stored procedures (xp_findnextmessage, xp_readmail, and xp_deletemail) to process incoming mail messages (expected to be a single query only) and uses xp_sendmail to return the result set to the message sender. sp_processmail needs to be set up as a regularly scheduled job to check for mail received in the SQL Mail inbox.

SNMP

Simple Network Management Protocol (SNMP) is an application protocol that offers network management services. Using SNMP, you can monitor Microsoft SQL Server across different platforms (for example, Microsoft Windows NT, Microsoft Windows 95/98, and UNIX).

With SQL Server and the Microsoft SQL Server Management Information Base (MSSQL-MIB), you can use SNMP applications to monitor the status of SQL Server installations. You can monitor performance information, access databases, and view server and database configuration parameters.

SNMP Terminology

Simple Network Management Protocol (SNMP) terms are defined here for quick reference.

Term

Description

SNMP

Use SNMP applications to monitor the status and performance of Microsoft SQL Server installations, explore defined databases, and view server and database configuration parameters.

SNMP agent

SQL Server SNMP extension agent (Sqlsnmp.dll). Server software that extends the functionality of the SNMP service. The SNMP agent processes requests for data and data objects that reside on the local server.

Enabling SNMP Support on SQL Server

Microsoft SQL Server support of SNMP is enabled automatically if Microsoft Windows NT support of SNMP is installed on to the computer when you run SQL Server Setup. If SNMP is not installed on the computer when you run the Setup program, SQL Server support of SNMP is not enabled.

Enabling SQL Server Support of SNMP on Windows 95/98

You can monitor remote connections to computers running Microsoft Windows 95/98 if your network uses Simple Network Management Protocol (SNMP). For more information, see "SNMP Agent" in the Windows 95 Resource Kit (WIN95RK.HLP) or in the Windows 98 Resource Kit tools online help (WIN98RK.HLP).

SQL Server MIB

The database controlled by a Simple Network Management Protocol (SNMP) agent is known as SNMP Management Information Base (MIB). The values contained in an SNMP MIB can be shared with another application's SNMP MIB.

Microsoft SQL Server Management Information Base (MSSQL -MIB), stored in the Mssql.mib file, and the SQL Server SNMP extension agent (Sqlsnmp.dll) are copied to the system by SQL Server Setup and are enabled if SNMP is running at the time of installation. SNMP can be activated or deactivated at any time by selecting the Enable SNMP check box in the SQL Server Network Utility window.

For more information about SNMP, see your SNMP application documentation.

Copying the MSSQL-MIB to an SNMP Workstation

For Simple Network Management Protocol (SNMP) applications to monitor the status of a Microsoft SQL Server installation, a copy of the Microsoft SQL Server Management Information Base (MSSQL-MIB), stored in the Mssql.mib file, must be placed on the monitoring workstation and loaded into the SNMP application. The MSSQL-MIB enables the SNMP application to access the SQL Server SNMP extension agent on the computer running SQL Server, and monitor that SQL Server.

The Mssql.mib file is a text file that contains the definitions of objects available to SNMP workstations. The file consists of read-only variables for monitoring general performance counters, the status of SQL Server installation and databases, and limited discovery of configuration options and database files. Mssql.mib does not define any writable objects.

These tables are SNMP tables, not SQL Server tables.

SNMP table

Description

MssqlSrvTable

Contains a description of the SQL Server installation. Has a single row for each SQL Server installation (version 6.5 or earlier) or multiple rows for each SQL Server (version 7.0 or later) running on the server.

MssqlSrvInfoTable

Contains general information about the active SQL Server process, including performance counters.

MssqlSrvConfigParamTable

Lists SQL Server configuration parameters.

MssqlSrvDeviceTable

Contains an entry for each SQL Server database file defined on the system.

MssqlDbTable

Lists defined SQL Server databases. Contains a single row for each database.

MssqlDbInfoTable

Contains detailed information about each SQL Server database.

MssqlDbOptionTable

Lists database options set for each SQL Server database.

MssqlDbDeviceTable

Contains entries for each device fragment on which a particular SQL Server 6.5 database has been created or altered. For SQL Server 7.0 entries will be displayed for each database file found on the server.

For more information, see your SNMP application documentation.

SQL Server Failover Support

Microsoft Windows NT Clustering Service is a feature of Windows NT Server Enterprise Edition, which supports the connection of two servers, or nodes, into a cluster for manageability of data and applications. Clustering Service monitors both servers, and detects and recovers from server or application failures automatically.

SQL Server Failover Support is available only with the Enterprise Edition of SQL Server version 7.0 and is designed to work in conjunction with Clustering Service. Failover Support provides the ability to make SQL Server appear as a single SQL Server virtual server when installed on a cluster. Client connections see only the single SQL Server virtual server and are unaware which physical Windows NT Server node is processing their request. As a virtual server, SQL Server is managed through Cluster Administrator from either node of the cluster.

Clustering Service monitors the status of the primary and secondary nodes, the SQL Server application, and shared disk resources. Upon failure of the primary node, services will fail over automatically to the secondary node, rolling back uncommitted transactions in preparation for client reconnection to the database. Applications designed to work with Clustering Service can make the recovery of data and applications appear seamless to clients.

Supported Cluster Configurations

Providing SQL Server Failover Support requires that some key components and services work closely together. Clustering requires that two servers and a shared disk system be connected, configured, and managed. Cluster Administrator allows you to define a number of parameters, including failover/failback timing and services, and resource moves between servers.

Microsoft Windows NT Clustering Service architecture is designed to work with standard Windows NT Server storage drivers interfacing with SCSI devices, including SCSI over fiber. The storage device, typically a RAID system, must be connected to both nodes in the cluster for failover to work properly. Shared storage devices must be formatted using NTFS.

Identically configured hardware is not required for each node of a cluster; however, there are two exceptions. First, you cannot mix Alpha and Intel architecture processors in the same cluster. Second, you may not be able to mix hardware from different vendors and, in some cases, different models from the same manufacturer. Before attempting to mix hardware, check the Hardware Compatibility List (HCL), found at www.microsoft.com/ntserver, a qualified systems integrator, or the hardware manufacturer.

Active/Passive Cluster Configuration

In this configuration, one node, the primary node, supports all clients and one instance of Microsoft SQL Server, while its companion node, the secondary node, remains idle. This configuration provides the maximum availability performance for your resources. The secondary node is a dedicated backup ready to be used if a failover occurs. If the configuration of the secondary node is identical to that of the primary node, no performance degradation is seen by clients after reconnection.

For some mission-critical virtual SQL Server applications, using all available resources on the primary node and having all the available resources on the secondary node ready in the event of a failover is the most appropriate configuration. There are two additional configurations that can be considered:

  • Applications on a primary node that do not failover

    Additional services that are not mission-critical may be running on the primary node. In the event of a primary node system failure, only those services configured to failover to the secondary node continue to operate. Clients using services installed on nonclustering shared drives lose access to those services until the node is brought back online. These services may include any application not considered to be mission-critical.

  • Applications on a secondary node that should be stopped when a failover occurs

    Applications that are not mission-critical may run on the secondary node. In the event of a failover, these critical services can be stopped as required to provide necessary resources for SQL Server.

Active/Active Cluster Configuration

In an active/active configuration, you are running two active/passive configurations simultaneously as mirrored configurations on the same hardware.

Installation of Microsoft SQL Server version 7.0 in an active/active configuration must be performed sequentially by completing the installation of the primary node first, restarting, and then performing the installation of the secondary node. The first configuration is installed as an active/passive configuration. The second configuration of SQL Server virtual server is installed in the same way but using the existing secondary node of the initial installation as the primary node and the existing primary node as the secondary node. Each instance of the SQL Server virtual server appears to be a mirror image of the other.

To install a second occurrence of a SQL Server virtual server, follow the steps outlined in How to install an active/passive configuration. Each node requires exclusive use of a logical disks set that can be shared with the remaining node for the installation. Each SQL Server virtual server needs a separate static IP address and SQL Server virtual server name. SQL Server virtual servers and shared disk resources can be allocated as needed using Cluster Administrator. For more information about configuring preferred servers, possible owners, failover, and failback parameters, see Microsoft Cluster Server Administrator's Guide.

Using SQL Server Failover Support

Before installing SQL Server Failover Support, ensure the servers are configured properly and are ready to be installed. The following sections discuss information that supports a smooth installation.

Selecting Cluster Hardware

Refer to your Microsoft Cluster Server Administrator's Guide for a list of supported hardware configurations and hardware configuration information. Also, to ensure high availability operation, select Microsoft Windows NT Clustering Service compatible hardware from the Hardware Compatibility List (HCL). The list contains approved hardware configurations where a manufacturer has been able to validate a specific cluster configuration, two servers, an interconnect, and a storage solution. This list of clustering compatible servers can be found at https://www.microsoft.com.

Preparing Servers for SQL Server Failover Support

Building your server configuration to use SQL Server Failover Support can be done in two stages. The first stage is to prepare both servers for Microsoft SQL Server version 7.0 installation. This stage requires selecting servers from the HCL and installing identical versions of Windows NT 4.0 Enterprise Edition, Microsoft Internet Explorer version 4.01 Service Pack 1, and Windows NT SP 4 on each server. After this is completed, you can install Clustering Service.

The second stage of preparation requires that Clustering Service be operational. Before installing SQL Server 7.0, ensure that a SQL Server resource group is identified and contains at least one shared disk resource. During the installation of SQL Server, be prepared to supply a static IP address and an SQL Server virtual server network name.

Naming Services and Assigning IP Addresses

Prior to installing Clustering Service and SQL Server Failover Support, ensure that you have all of the necessary IP addresses and subnet masks for your Windows Internet Name Service (WINS) and Domain Name Service (DNS) services, for proper name resolution. You are also required to provide a name for each node, each Clustering Service, and each instance of the SQL Server Failover Support service. Each server node can use Dynamic Host Configuration Protocol (DHCP) to obtain an IP address, but each SQL Server virtual server requires a separate static IP address for the virtual server.

Configuring New Installations of SQL Server 7.0 and MS DTC

Installing SQL Server 7.0 requires at least one clustering shared disk resource for Microsoft Distributed Transaction Coordinator (MS DTC). Before installing SQL Server, open Cluster Administrator and view a resource group that contains an IP address and a network name. If there is no disk resource in the group, drag a shared disk resource into the cluster group. If you have only one disk resource in your cluster, MS DTC and SQL Server can coexist on the same common disk resource.

Note: If the MS DTC installation fails with no Compatible resource groups found, cancel the install, check that the SQL resource group contains an IP address and a shared disk resource, and then restart the installation process.

Configuring MS DTC for use with SQL Server 7.0 in a Clustered Configuration

In a single disk resource configuration, upgrading to SQL Server 7.0 causes SQL Server and MS DTC to be installed to the same cluster resource group. This configuration requires MS DTC and SQL Server 7.0 to share the same resources assigned to the cluster resource group including, the IP address, network name, and shared disk.

If there is a need to use the version switch to revert one or both nodes to SQL Server 6.5, you need to uncluster the node first. Using the version switch to revert to SQL Server 6.5 does not remove MS DTC. SQL Server 6.5 has a restriction that the cluster resource group it belongs to cannot host more than one service and cannot have more than one IP address. This restriction prevents any node reverted to SQL Server 6.5 from reclustering.

The same qualifications apply to a multiple shared disk configuration except that MS DTC and SQL Server 7.0 may be placed on separate disks in the group.

Configuring SQL Server Memory for a Cluster

SQL Server requires operating system resources to manage its databases. The most important of these resources is memory. To ensure adequate response times to clients during a failover and to ensure SQL Server Failover Support functions as expected, a review of memory requirements and anticipated server usage must be performed.

SQL Server allocates memory dynamically if the sp_configure option min server memory is set to 0. Using 0 as your min server memory value allows SQL Server to reallocate memory usage during a failover dynamically. Dynamic allocation of memory is important during a failover because it allows SQL Server to adjust to its new environment by optimizing its memory usage. In an active/active configuration, both servers running SQL Server will optimize their memory usage automatically during a failover.

If you are running other services on your cluster nodes or you are not using the automatic configuration option to manage SQL Server memory requirements, you may want to review and manage SQL Server virtual server resources during a failover. This can be done by reviewing Object Memory statistics in Windows NT Performance Monitor. If Windows NT Performance Monitor shows a large number of object memory page faults, consider configuring less memory for one or both SQL Server virtual servers, or stopping other programs or processes.

For example, assume that the SQL Server virtual server on node 1 uses 60 percent of the available memory. On node 2, the operating system requires 20 percent and two additional services each require 15 percent of the systems available memory continuously. The SQL Server virtual server on node 2 has the remaining 50 percent of the memory available for its use. The total memory usage of all of these services on node 2 is 100 percent.

If the SQL Server virtual server on node 1 fails over to node 2, the physical memory requirements for all services on node 2 will now be 160 percent. Both SQL Servers virtual servers will reallocate their memory requirements dynamically to the available memory, which is 50 percent, the amount of memory originally available to SQL Server. By viewing Windows NT Performance Monitor, you may decide to stop one or both of the additional services gaining 15 percent more memory for each service that is stopped.

Identifying SQL Server Failover Support Configuration Parameters

You need to identify which server will be the primary node and which server will be the secondary node for each installation of SQL Server. In an active/passive configuration, you will install only a single instance of the SQL Server virtual server. In an active/active configuration, you will install two instances of the SQL Server virtual server. When creating the SQL Server virtual server, you must identify these parameters for your installation.

Parameter

Example

SQL Server virtual server name

SS1

 

SS2

IP address (a separate static address is required for each SQL Server virtual server)

192.68.10.10
192.68.10.11

Subnet mask

255.255.255.0

Drives on which SQL Server will be installed

SS1
E, F

 

SS2
G, H

Disk drive to be moved into the cluster group for MS DTC

I

Validating the Failover Installation

Each vendor has their own method for managing RAID systems. It is best to test your configuration completely by ensuring that you are able to failover and then fail back each resource in the cluster. As part of the installation process, ensure that you have identified each cluster resource and the preferred server the resource should default to when the cluster is restarted. These preferences are set using Cluster Administrator.

In an active/active configuration for example, each server runs one instance of the Microsoft SQL Server virtual server service as a primary node while using the remaining node as a secondary node. Preferences for these services and the shared RAID disk services may be set individually to ensure proper allocation of resources when the cluster is started. You need to restart both servers after setting these parameters to validate that everything will work as expected.

External Tools Support

You can launch any Microsoft Windows NT application from SQL Server Enterprise Manager. External applications can be added to, and run from, the Tools menu.