Set up your SQL Server instance

Steps required to prepare the inatance for monitoring

QMonitor agents are monitoring your instances by running queries against multiple DMVs and system tables at regular intervals. Before you can add an instance to QMonitor, you will need to set up the instance in order to grant the required permissions and create additional objects such as the Extended Events session “QMonitor”.

The read access to those DMVs is granted either through sysadmin role membership or through grants on the individual objects.

QMonitor does not strictly require sysadmin role membership to collect the bare minimum information to populate the dashboards with performance metrics: if you decide not to grant sysadmin role membership to the QMonitor agent login, you can still monitor your instances by granting permissions on the individual DMVs and system tables. Additional permissions may be required to perform daily checks or to execute QMonitor jobs.

QMonitor offers a setup script that you can download from the Instances page: you can load the script in Management Studio to review the actions it performs and provide the parameters to set up the instance correctly.

At the very top of the script, you can provide the values for three required variables:

@LoginName: name of the login used by the QMonitor agent to connect to the SQL Server instance. This can be a Windows login, a SQL Server login or an Azure Managed Identity, depending on your setup. If the login is not present, it will be created.
@Password: please enter here the value of the password to use to authenticate SQL Server logins. If you leave this variable empty, it will be interpreted as indicating a Windows login or a Managed Identity. If you want to use an existing SQL Server login, you can enter any value for this variable and it will be ignored.
@Sysadmin: set this variable to ‘Y’ in order to grant sysadmin server role membership to the login indicated in the @LoginName variable. This is the easiest option, which requires the least maintenance on your side. If you want to avoid granting sysadmin role membership, set this variable to ‘N’ and the remainder of the script will take care of granting all the permissions required to collect performance metrics.

Once you provided values for all the three variables you can execute the script and review the results. If any error is returned from SQL Server, please review it, correct the cause and execute once again.

When the script executes successfully, the instance is ready to be added to QMonitor and the verification dialog will display all the checks as “Ok”. The check for sysadmin privileges may still display as “Ko” if you decided not to grant sysadmin role membership: you will still be able to proceed with the registration of the instance.