Ensuring that an enterprise SQL Server environment remains healthy is the responsibility of an organization’s database administrators (DBAs).
At a minimum, SQL Server systems need to be available, protected against a potential disaster, and have the required capacity to meet user demands.
Support teams use a variety of monitoring strategies to confirm that the environment is operating as expected.
Tailored Monitoring of Complex SQL Server Environments
Microsoft’s SQL Server is one of the most popular relational database platforms in the world and is used extensively in many types of business, academic, and scientific implementations.
Environments range from small businesses with a single SQL Server to large organizations with hundreds of instances. Differences in the way systems are used can influence how they are monitored by a company’s DBAs.
A typical large SQL Server environment consists of multiple servers grouped in a hierarchy to denote that they are used for very different purposes.
An organization’s SQL Servers can be broadly categorized in the following way:
Business-critical systems
These are the SQL Server instances that are critical to an enterprise’s successful operation.
It is imperative that these databases are always available to users and exhibiting optimal performance. Customer-facing sales systems fall into this category of systems.
Business-related systems
A subset of systems may be important for a business to operate smoothly but their failure will not be crippling to the enterprise.
Back-office systems used for internal reporting can fall into this category.
Test and development systems
In large environments, there may be a substantial population of test and development systems. While developers may be inconvenienced by these instances being unavailable, there is no immediate impact on the business.
Database support teams must be aware of the health of all instances. In a large and diverse environment, not all SQL Servers need to be monitored with the same level of concern.
Test and development systems do not need the same amount of attention as the business-critical systems on which an enterprise depends.
This fact enables a database team to use multiple support tools strategically to maintain the environment without expending unnecessary financial or computing resources.
Health and Performance Monitoring
Health and performance monitoring are two related methods of keeping tabs on a computing environment. They concentrate on different metrics and can be used separately or in combination to address business requirements.
- Health monitoring looks at big-picture issues such as availability, looming capacity problems, and backup status to provide disaster recovery capabilities. It is less intrusive than performance monitoring.
- Performance monitoring is concerned with the elements that contribute to the responsiveness of databases and their associated applications. A performance monitoring tool looks at items like throughput and query execution to determine why a system is not performing up to expectations.
The category a particular server falls into determines whether it needs to be monitored by a comprehensive performance monitor or if health monitoring will suffice.
In many cases, only business-critical systems demand focused performance monitoring. Other systems can be managed effectively by concentrating on their health status and addressing any issues that are discovered.
Benefits of Health Monitoring with SQL Inventory Manager
IDERA’s SQL Inventory Manager provides SQL Server support teams with a versatile tool that can perform health monitoring in addition to keeping track of database instances throughout an environment.
It offers a cost-effective method of checking on the health of enterprise SQL Server environments.
SQL Inventory manager reports on several categories of metrics related to the health of the monitored servers.
- Availability – Teams are advised on the status of instances and databases. They are also alerted to systems demonstrating slow response time. Email alerts can be generated when thresholds are exceeded to alert the proper support teams of the issues.
- Disaster recovery preparedness – Four metrics are observed to report on the disaster recovery preparedness of the monitored systems. Teams can obtain information on if databases have been backed up, and if so, how recently a backup was performed. It also reports on whether CHECKDB integrity checks are being performed.
- Storage capacity – SQL Inventory Manager reports if storage or database capacity reaches 75%. As usage thresholds increase, email alerts are generated so proactive action can be taken to avoid outages.
- Configuration checks – Daily checks are run to verify that database auto shrink is enabled and that Tempdb files are the same size.
These health checks are incorporated into a comprehensive and versatile SQL Server inventory tool. The application can be used to group SQL Servers according to their importance to the business so the proper level of monitoring can be implemented.
Teams will need an additional performance monitoring tool to address their business-critical systems. Some of the specific benefits of using SQL Inventory Manager when possible to perform health checks rather than full-blown performance monitoring include:
Cost-savings – IDERA’s licensing scheme allows teams to health check as many instances of SQL Server as they want with a 10-pack of SQL Inventory Manager. More complete performance monitors are usually licensed on a per-instance basis and are more expensive. Substantial savings can be enjoyed by performing health checks when appropriate.
Reduced overhead – Health monitoring with SQL Inventory Manager is less intrusive, simpler to perform, and requires fewer system resources than performance monitoring.
SQL Inventory Manager gives teams the capability to run health checks on all SQL Servers in the environment. In many cases, this may provide a sufficient level of monitoring without the need for a dedicated performance monitor.
Try SQL Inventory Manager for free.
