SQL Monitor 4

Configuring the SQL Monitor database

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 2005, 2008, 2012 or 2014.

The SQL Monitor database is configured after you choose to install the monitoring service on the Choose components page of the installer:

For the SQL Monitor database, you can use:

  • an existing empty database
  • a database used with an earlier SQL Monitor installation

Alternatively, SQL Monitor can create a database for you (see below).

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.

If you want SQL Monitor to create the SQL Monitor database for you

Permissions

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.

  1. On the Installation options page, select Select database:
  2. In the Set up SQL Monitor database dialog box, select Create a new database and click Next:
     
  3. 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.
  4. Specify the credentials used to create the database.
    • 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.

    If you specify a different Windows account, and this account fails, SQL Monitor will automatically attempt to create the database using your current credentials.
  5. Click Create now.

Connection details

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.


Do you have any feedback on this documentation?

Let us know at sqlmonitorfeedback@red-gate.com


Didn't find what you were looking for?