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

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

Also, if you wish to host the SQL Monitor database on an Azure SQL Database you will need to set up the database prior to installing SQL Monitor, as the installer is not able to create the required Azure SQL 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.

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.

Enable delayed durability (on SQL Server 2014 and newer) with the following T-SQL:

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.)

Enable query optimizer hotfixes (on SQL Server 2016 and newer) with the following T-SQL:

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.

Enable automatic query tuning and batch mode for rowstore (on SQL Server 2019 and newer, Enterprise Edition only) with the following T-SQL: