The automatic SQL Server health check monitoring must collect crucial metrics to diagnose memory, CPU, I/O issues that are needed for proper SQL Server performance. It also has to have a good set of reliable, accurate configurable alerts that will tell us if the failure occurs. Of course, you can create your own set of scripts and run them every time you need to check the working state of your SQL server. But the easiest way is to use the SQL Server health check from SqlBak. It will make all crucial checks for you, collect the statistics, and, if something goes down, will send the email notifications immediately.

Why do we need a SQL Server Health Check?

Here we are going to highlight two crucial point capacity planning and reaction times. Now let’s dig deeper and say a few words about each of them.

Capacity Planning

It’s crucial to know the capacity of our server and their limits, such as Memory, storage space, I/O capacity, and others. It’s important to check all these components and be ready to react in advance to the approaching threat. If we have proper automatic SQL Server health check, we’ll be able to foresee the coming disaster and take necessary measures.

Reaction Time

Here is the second point – reaction time. Reaction time as important as capacity planning. If we tune all alerting and notification of our automatic SQL Server health check correctly, we’ll able to prevent server crash. The good thing about the automatic health check is that based on the statistics, we detect threats before they can damage our server.

Automatic SQL Server Health Check – Key Points

Monitoring your SQL server from a different server. It will tell you if something goes wrong and your server goes down. If you use automatic health check on the same computer or server where SQL Server is installed, then probably, if the server is down or if something wrong with your computer monitoring process will fail too. In such a case, you will not receive any notification.

Create a separate database to store monitoring information. Automatic SQL Server health check needs space to store and analyze the data. It’s known that some metrics are gathered once a day while others are collecting every 5-10 minutes. Also, you need to consider how long it is necessary to keep historical data. It is recommended to store them for a few months.

Testing from time to time, your health check queries and scripts. Since your SQL Server health check runs automatically, it’s crucial to be sure that everything works correctly.

Start from the small set of alerts. From the very beginning, it’s recommended to start from a few alerts and expand gradually.

Some Crucial Steps to Effective Automatic SQL Server Health Check

Below we are going to cover some points or steps which are crucial in your SQL Server monitoring process. So let’s start from the first one:

Learn your servers

If you are just starting to work with SQL Server monitoring, you definitely need to do one thing. It’s to learn and add all your servers to an automatic health check. Here we recommend you to use Automatic SQL Server Health Check from SqlBak. This tool will automatically monitor all your servers.

Maine Database and Server Documentation

We need to collect the complete documentation of all our instances, servers, and databases. It’s crucial if many people work together and use the same databases. For example, we, as DBA, have to know if someone turns off or turns on the “Auto Shrink” operation. In other words, we need to know all about our instances, servers, and databases, who, when, and which changes have been made.

Backups or Backup Jobs

We need to check to be sure that all our backups (full, differential, and transaction log backups) are running in the proper way and storing in the selected place. If you run the backup job, it’s important to monitor the following information:

For our backups, we need to be sure that all required backups (full and differential database and log backups) are running and the storage location. For our backup jobs, and any other SQL Server Agent jobs, we need to monitor three key factors:

• Jobs which failed
• Jobs whose duration is too long
• Jobs which don’t start as scheduled

Of course, it’s not really convenient to do it manually, but you can use SqlBak, which will do all work for you.

Capacity Planning

When you start to works with a database, first of all, check the disk size and free space on it, and also, you need to know a database’s size. It’s good practice to set up an alert when a disk is almost full; then, you will have enough time to increase the disk space.

Memory, I/O, and CPU Monitoring

It’s good practice to monitor the following parameters during the time:

• I/O, Memory, and Server-wide CPU
• I/O, Memory and SQL Server-specific CPU
• User connections, batch requests/sec and other performance/activity metrics

It is a good practice to collect and manage the crucial resource data like CPU, I/O, and memory. These data can be collected from different sources like Dynamic Management Views, PerfMon, and other system tables and views. The frequency with which we should collect all these data depends on server activity.

Tune the monitoring strategy

From your monitoring or SQL Server, the health check strategy depends on how quickly you will receive the warning notification and can react on it. You need to check all your alert settings and do not accept duplicates or any false alarms. All of this may increase the response time.