SQL Monitor requires access to a SQL Server database to store monitoring data, and SQL Monitor configuration details. It requires SQL Server 2012 or later, or an Azure SQL Database.

The database needs to be robust and resilient (backed up), because if it fails, you won’t be able to access SQL Monitor easily. In an enterprise installation, the database is probably best hosted in an Availability Group or a SQL Server Failover Cluster instance.

Estimating the size of the database

The SQL Monitor database uses up to 150 MB per day to monitor a server that hosts a SQL Server. For example, monitoring ten servers for seven days will increase the SQL Monitor database by 10 GB.

If the monitored servers, clusters, or machines have a large number of objects, the SQL Monitor database will use significantly more storage space, potentially up to 450 MB a day.

We recommend you set fixed autogrowth and transaction log size relative to your database size. For more information, see: Manage the Size of the Transaction Log File (MSDN).

Using an Azure SQL Database or Azure Managed Instance

We currently only support SQL authentication for Azure SQL Databases or Azure Managed Instances – Azure AD authentication is not supported, for example.

Also, if you wish to host the SQL Monitor database on an Azure SQL Database or an Azure Managed Instance you will need to set up the database prior to installing SQL Monitor, as the installer is not able to create the required database for you. The database can either be blank (it will be populated) or an existing SQL Monitor database (it will be updated).

Using SQL Server Express (not recommended)

We don't recommend using SQL Server Express to host the SQL Monitor database due to its database size restrictions. If you use it, you will need to set short data retention windows for SQL Monitor, such as one week for data you want to view trends for, and three days for troubleshooting data. You have a practical limit with Express of 10–15 monitored machines.

SQL Server Performance Recommendations

For the most consistent performance, we suggest using SQL Server 2019 (or later) for the SQL Monitor repository. Not all of the following recommendations apply to previous versions of SQL Server.

SQL Server settings can be configured via the Data settings page in SQL Monitor:

Data Compression

The data compression feature enables page compression in the SQL Monitor data repository. Enabling compression reduces the disk space utilized by data at rest and allows SQL Server to keep more data in memory (data remains compressed in memory). Starting with SQL Server 2016 Service Pack 1, data compression is available across all editions of SQL Server.

Enabling data compression on a large SQL Monitor data repository will take some time. Additionally, enabling compression may cause an increase in CPU utilization for some workloads.

Estimating compression will fail on a contained database (SQL Server's compression estimation breaks database containment), however it is still possible to compress the SQL Monitor repository. Typical space savings is around 50%.

Delayed Durability

Delayed durability is a feature introduced in SQL Server 2014. Delayed durability allows for SQL Server to use asynchronous log writes. As the number of monitored SQL Servers increases, SQL Monitor will spend more time waiting on data to be inserted into the database. In our research, delayed durability increases monitoring performance.

Query Optimizer Hotfixes

The QUERY_OPTIMIZER_HOTIFXES database scoped configuration variable enables various query optimization hotfixes that were released after the RTM release of a specific version of SQL Server. (This is equivalent to enabling Trace Flag 4199 for a single database.)

Intelligent Query Processing Features

The intelligent query processing features in SQL Server allow SQL Server to take advantage of known good plans in the query store and apply these good plans when a performance regression is detected. Batch mode for rowstore allows SQL Monitor to use several query performance enhancements when running analytic queries over the repository.