Redgate Monitor requires access to a SQL Server database to store monitoring data, and Redgate Monitor configuration details. It requires SQL Server 2012 or later, or an Azure SQL Database, Azure Managed Instance or Amazon RDS for Microsoft SQL Server.

The database needs to be robust and resilient (backed up), because if it fails, you won’t be able to access Redgate 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 Redgate 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 Redgate Monitor database by 10 GB.

If the monitored servers, clusters, or machines have a large number of objects, the Redgate 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 Redgate Monitor database on an Azure SQL Database or an Azure Managed Instance you will need to set up the database prior to installing Redgate 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 Redgate Monitor database (it will be updated).

Using an Amazon RDS SQL Server

We support both SQL authentication and Windows authentication.

The installer is capable of setting up the database but it must be done with an account that has permissions to create databases on an Amazon RDS SQL Server. To grant the required permission for the installer, run this SQL:

GRANT CREATE ANY DATABASE TO [<LOGIN>];

You also have the option of creating the database yourself. In such a case, the GRANT CREATE ANY DATABASE query is not required.

Using SQL Server Express (not recommended)

We don't recommend using SQL Server Express to host the Redgate Monitor database due to its database size restrictions. If you use it, you will need to set short data retention windows for Redgate 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 Redgate 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 Redgate Monitor:

Data Compression

The data compression feature enables page compression in the Redgate 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 Redgate 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 Redgate 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, Redgate 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 Redgate Monitor to use several query performance enhancements when running analytic queries over the repository.