Configuring the SQL Monitor database
Published 08 March 2018
The SQL Monitor database (also called the data repository) is a SQL Server database that stores data about your SQL Servers. It must be on SQL Server 2012, 2014, 2016 or 2017.
You configure the SQL Monitor database after you choose to install the monitoring service on the Choose components page of the SQL Monitor installer:
For the SQL Monitor database, you can either:
Estimating the size of the database
The SQL Monitor database uses up to 150MB 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 10GB.
If the monitored servers, clusters or machines have lots objects, the SQL Monitor database will use significantly more storage space, potentially up to 450MB 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 virtual machines
If you host the SQL Monitor database on a virtual machine, make sure the SQL Monitor database is installed on a physical disk mapped to that virtual machine.
We don't recommend you store the SQL Monitor database on a virtual disk, as this will affect performance. Instead, store it on a physical disk.
Using an Azure SQL Database
If you host the SQL Monitor database on an Azure SQL Database you will need to use the "Use existing database" option.
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).
Please note that we only support SQL authentication for Azure SQL Databases - Azure AD authentication is not supported, for example.
SQL Monitor needs to save some connection details to connect to the SQL Monitor database. You specify these on the Installation options page in the SQL Monitor installer:
The account you specify details for must have administrator privileges (db_owner database role) on the SQL Monitor database.
You can connect using Windows authentication or SQL Server authentication.
Connecting with Windows authentication
SQL Monitor assumes the account is in the current domain. To use a different domain account, enter credentials in the format username@domain-name or domain-name\username
If the Windows account doesn't have Log on as Service permissions, SQL Monitor will automatically attempt to grant them to the account.
The Windows account you select will also be used to run the monitoring service. If the account doesn't have permissions to run a Windows service, it will be granted these permissions.
Connecting with SQL Server authentication
The monitoring service will run under the Local Service account. For more information, see the MSDN documentation.
Using an existing database
- On the second Installation options page, click Select database. Leave the default option selected and click Next:
- Specify the SQL Server and database:
Note that you need to choose either an empty database or a database that was created during a previous SQL Monitor installation.
- Click OK.
Using a database created by SQL Monitor
To create the SQL Monitor database, you need an account with Create Database permissions on the specified server. This account is only used to create this database; SQL Monitor doesn't save the credentials.
If you don't have a login for the SQL Server with the right permissions, use the
sp_addsrvrolemember stored procedure to assign a login to the dbcreator role. For more information about this stored procedure, see the MSDN documentation.
If you see error messages, see Error messages: creating a Data Repository database. If the error is about permissions, make sure you have Create Database permissions on the specified SQL Server.
- On the second Installation options page, click Select database and choose Create a new database. Then click Next:
- Specify the SQL Server where you want SQL Monitor to create the database, and specify the database name:
You can select a SQL Server from the drop-down menu, or type the instance name or IP address directly into the server box.
- Specify the credentials used to create the database.
If you specify a different Windows account, and this account fails, SQL Monitor will automatically attempt to create the database using your current credentials.
- To use the Windows account that you're currently logged into, select Use current credentials.
To use a different account, select Specify an account. If you select Windows authentication, SQL Monitor assumes the account is in the current domain. To use a different domain account, enter credentials in the format username@domain-name or domain-name\username.
- Click Create now.