User goals and features
The BMC PATROL for Microsoft SQL Server product operates with a PATROL Agent and one or more of the PATROL consoles to monitor Microsoft SQL Server products. PATROL for Microsoft SQL Server contains knowledge in the form of applications, commands, parameters, and recovery actions. BMC PATROL uses this knowledge to monitor, analyze, and manage Microsoft SQL Servers.
PATROL for Microsoft SQL Server was created for use by Microsoft SQL Server database administrators, system administrators, and PATROL administrators in the daily administration and management of the Microsoft SQL Server products. If you have any questions regarding the operation or management of Microsoft SQL Server, refer to the Microsoft SQL Server manuals and website.
Features
- PATROL for Microsoft SQL Server discovers SQL Servers automatically
- Provides global channel access to SQL Servers
- Provides commands that allow you to perform administrative functions directly from the PATROL consoles
PATROL for Microsoft SQL Server also helps you to monitor and manage the following key features:
Support for High Availability solution, SQL Server AlwaysOn
Discovering SQL Servers automatically
Providing global channel access
Supporting and monitoring clustered environments
Monitoring files and file groups
Monitoring SQL Server mirrored database
Updates in version 4.5.10
The following updates are incorporated in BMC PATROL for Microsoft SQL Server:
- Support has been added for Microsoft SQL Server 2012.
- All the queries in BMC PATROL for Microsoft SQL Server Knowledge Module are executed with NO_LOCK flag and READ UNCOMMITTED isolation level, by default. This update prevents the blocking that was caused in execution of database space-related queries and stored procedures. If you want to execute all the queries without the NO_LOCK flag, ensure that you add the following entry in pconfig:
/MSSQLServer/SQLServerInstanceName/NO_LOCK = 0
where, SQLServerInstanceName is the name of the SQL instance in which this option will be used. - When the ErrorLogUpdates parameter is in alert state, the annotation now displays the string that caused the alert.
AlwaysOn High Availability
Microsoft SQL Server 2012 has introduced a new high availability solution that improves the availability of servers or databases. This masks the effects of a hardware or software failure and maintains the availability of applications, so the downtime that the application or clients experience during failure is minimized. AlwaysOn Failure Cluster Instances and AlwaysOn Availability Groups are provided by the SQL Server for creating high availability for a server or database. For more information about AlwaysOn High Availability feature, see Monitoring-AlwaysOn-High-Availability.
Support for High Availability solution, SQL Server AlwaysOn
Microsoft SQL Server 2012 has integrated the high availability and disaster recovery solution, SQL Server AlwaysOn. This high availability solution reduces the downtime for users by masking the effects of a hardware or software failure and maintaining the availability of the applications. Thus, improving the availability of servers or databases. Microsoft SQL Server provides the following new options for creating high availability for a server or database:
- AlwaysOn Failure Cluster Instances
- AlwaysOn Availability Groups
AlwaysOn Availability Groups provides options to improve the database availability and to enable improved resource usage. An availability group supports a failover environment for a discrete set of user databases, known as availability databases, which fail over together. BMC PATROL for Microsoft SQL Server supports monitoring of AlwaysOn Availability Groups. For more information, see AlwaysOn High Availability section in the BMC PATROL Getting Started for Microsoft SQL Server guide.
For more information on the high availability options of Microsoft SQL Server, see http://msdn.microsoft.com/en-us/library/ms190202.aspx
Discovering SQL Servers automatically
The MSSQL_SERVER (Server) application automatically discovers the SQL Servers in your environment and creates an icon for each discovered server. For these SQL Servers to be automatically discovered, you must have access rights and permissions to the files, directories, and registry keys set as described in Preparing-for-instance-discovery.
Providing global channel access
PATROL for Microsoft SQL Server establishes a global channel to each SQL Server that it discovers in your environment. Through this global channel, PATROL for Microsoft SQL Server gathers information that BMC PATROL uses to monitor those SQL Servers. To help maintain the global channel, PATROL for Microsoft SQL Server provides the following features:
- Locks that synchronize access to the global channel
- Automatic close and reopen feature to recover from global channel problems
- Status of the global channel
- Method for resetting the global channel
Supporting and monitoring clustered environments
PATROL for Microsoft SQL Server monitors SQL Servers in a clustered environment. See Setting-up-PATROL-in-a-clustered-environment for more information.
Monitoring cache-hit ratio
PATROL for Microsoft SQL Server monitors the cache-hit ratio, which provides an important benchmark about data throughput and overall database performance. The cache-hit ratio expresses the relationship between the number of requests for data that are read from cache and the total number of requests for data. A drop in this ratio indicates an increase in the number of requests that the SQL Server must read from the physical disk (I/O) because the pages containing the requested data are not stored in cache memory. When this ratio drops below a predetermined low, PATROL for Microsoft SQL Server triggers alarms and warnings. Once notified, administrators can improve database performance by adjusting the available cache or the amount of physical RAM used by SQL Server to optimize availability and throughput.
Monitoring page read
PATROL for Microsoft SQL Server tracks how long it takes a SQL Server to read a page of data from the physical disk into cache memory or from memory to the physical disk. With this information, PATROL for Microsoft SQL Server can identify the areas where excessive I/O waits are taking place.
Monitoring free buffers
PATROL for Microsoft SQL Server monitors the percentage of free buffers (or database pages) available in memory (see the NumberofFreeBuffers parameter). If this number is too low, excessive waits, disk read, and disk write times result. If SQL Server’s memory size is greater than physical RAM, paging can occur, which degrades database performance. Administrators can use this information to maintain an optimum number of free buffers for peak database performance, and to keep page faults to a minimum. When demand exceeds availability during normal or subnormal periods of activity, this parameter can also indicate a need for additional resource tuning or an increase in capacity to maintain user productivity.
Monitoring space used
PATROL for Microsoft SQL Server monitors the percentage of database space used (SpaceUsedPCT) and the megabytes of database space used (SpaceUsedMB). Administrators can use these values to gauge and maintain database space.
Monitoring blocking locks
PATROL for Microsoft SQL Server monitors the total number of blocking locks. A high number of blocking locks could indicate an increase in the number of processes currently waiting for a specific page or table that is exclusively locked by another process. Using this information, administrators can more accurately develop capacity plans to determine whether additional resource
Monitoring row-level locking
PATROL for Microsoft SQL Server monitors row-level locking. The following parameters provide information on row-level locking:
- RIDLockAvgWaitTime provides the average amount of wait time (in milliseconds) that was caused by row ID lock requests.
- RIDLockRequestsPerSec provides the number of new row ID locks and lock conversions that the lock manager requested per second.
- RIDLockRequestWaitPercent provides the percentage of row ID lock requests that required a caller to wait.
Monitoring user connection
By tracking the number of user connections to SQL Server, PATROL for Microsoft SQL Server provides an important indicator of ongoing demand versus capacity. When the SQL Server is running low on available user connections, the UserConnections parameter goes into an alarm state. Because each connection requires memory, an increase in the number of user connections can coincide with a decrease in performance, which indicates the need to restrict user connections, lower the number of worker threads, or increase memory.
Monitoring log space
When the transaction log becomes completely full, SQL Server cannot run because it cannot record modifications to data (update, modify, delete, or add) and, therefore, you cannot dump the transaction log. PATROL for Microsoft SQL Server tracks the percentage of log space that is used with the LogSpaceUsedPCT parameter and the megabytes of log space used with the LogSpaceUsedMB parameter. The values provided by these parameters help you manage transaction log space.
Monitoring files and file groups
SQL Server stores data and log files in files and file groups. A database can have one or more file groups, and each file group can have one or more files that are associated with it. PATROL for Microsoft SQL Server can monitor files and file groups, but this feature is turned off by default. For details about this feature, see Monitoring-files-and-file-groups.
Monitoring error logs
The MSSQL_SERVER_ERRORLOG (Error Log) application class provides menu commands, parameters, and InfoBox items specifically designed to help you monitor the SQL Server and SQL Server Agent error logs for the error log strings and problems you want to monitor. For details about this feature, see Monitoring-error-logs.
Monitoring SQL Server mirrored database
The MSSQL_SERVER_DB_MIRRORING (Database Mirroring) application class provides parameters specifically designed to help you monitor the SQL Server mirrored databases. For details about this feature, see Monitoring-SQL-Server-mirrored-databases.
Monitoring log shipping
The MSSQL_SERVER_LOG_SHIPPING (Log Shipping) application class provides parameters specifically designed to help you monitor the SQL Server log shipped. These parameters help you monitor the status of the restoration operations and backup operations performed during the log shipping. For details about this feature, see Monitoring-SQL-Server-log-shipping.
By default, PATROL for Microsoft SQL Server does not automatically monitor the following features and functions:
For information about enabling these features, see Disabling-and-enabling-the-monitoring-of-some-features.