Configure Database Status Monitoring

Successful and dynamically developing start-ups often reach a point where problems with the database already exist, and there is still no individual to solve them. However, not everyone needs it. The HostTracker monitoring service offers the opportunity to automatically monitor key vital signs of the database without having to manually go to the servers every day. Below we will tell you how to catch popular mistakes, protect yourself from a sudden overflow of the base, be sure that there is always a fresh and successful backup and some more trivia.

How it works

Due to the specifics of the service , the tasks of monitoring the databases of sites are solved first of all, which, together with other methods of checks, can give a detailed diagnosis of the problems of the sites. But in general, you can use this tool for any databases that are accessible from the network.

First you need to connect to the database. We strongly recommend that you create a separate account for security and give it read -only rights and only those tables that you plan to check. Next, select the check interval - from 10 minutes to once a day. And enter the request that you plan to regularly execute, making it so that the result you need to check is one of two things:
')

• The first column of the first line of the server response. This can be any number format, a string, an array of bytes (image or audio that is stored in the database), GUID. For what can be used - see below.
• The number of affected rows.

Next - the most interesting. You can customize the test results. For example, look at whether a certain value has changed using the equal-not-equal conditions (here you can just check the GUID and the strings). For numerical values, there is also an additional condition: over / under, or if the value falls / does not fall within a certain interval.
For any request, you can configure alerts that will be sent via SMS or in another way , if the required value is exceeded.

If necessary, we add the specified addresses to the firewall.

Popular Applications

All this is configured once, it is necessary to return to the question only in case of problems. You just need to write the correct request. However, we will try to correct this: below you can find a list of requests that may be particularly useful for such monitoring. Everything is for MS SQL, but most queries are easily converted for other databases.

To improve security, you can create a procedure in which to include these requests and grant the rights to execute it to the specified login. Or, create an intermediate table in which to record the results of these queries, and already for it to grant read permissions for the Host-tracker.



Information about the latest successful backup:

SELECT TOP 1 database_name, backup_size, backup_start_date, backup_finish_date
FROM msdb..backupset bs
ORDER BY backup_set_id DESC

The number of connections to the database at this moment:

SELECT COUNT(*) AS connections FROM sys.dm_exec_connections

The time of the last launch of the database server, the number of CPUs and RAM

SELECT sqlserver_start_time, cpu_count, physical_memory_kb
FROM sys.dm_os_sys_info

File size and available space for current database:

SELECT name AS [File Name] , physical_name AS [Physical Name], size/128 AS [Total Size in MB],
size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS [Available Space In MB]
FROM sys.database_files;

How much space is available for the current database:

SELECT (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM sys.dm_db_file_space_usage

How much space is available for the TEMPDB database:

SELECT (SUM(unallocated_extent_page_count)*1.0/128) AS [free space in MB]
FROM tempdb.sys.dm_db_file_space_usage

Write if you think it is necessary to add something else to this list. Also, as always, we welcome your wishes and additions.