Adding SQL Servers to monitor

To monitor SQL Servers in SQL Response, you need to have installed and then connected to the SQL Response Alert Repository. If you are not connected to the Alert Repository, see Installing and connecting to the Alert Repository.

Selecting the SQL Servers to monitor

1. Click Add SQL Servers.

   The Add SQL Servers dialog box is displayed.

   

   The Add SQL Servers dialog box shows all the SQL Server instances that are:

   • in the same domain as the Alert Repository, and
   • in the same IP range as the Alert Repository, and
   • not yet added to SQL Response
2. In the Add column, select the check box next to each SQL Server instance you want to monitor.

   If the instance you want to monitor is not listed (for example if it is in a different domain or a sub-domain), click Add a SQL Server Not Listed and type the full path to the SQL Server instance. It is added to the top of the list and selected.

3. Before you click Add to start monitoring the selected SQL Servers, you can change the following settings for each SQL Server:
   • log-in credentials
   • whether SQL Profiler trace is enabled
   • configuration template
   • connection settings, such as connection time out value

     For more information about changing any of these settings, see the sections below.

4. Click the Add button to start monitoring all the selected SQL Servers.

   The list of monitored instances is displayed in the Servers to show area of the Filter pane on the left of the main application window:

   

   When you first add SQL Servers to monitor, the icon next to the SQL Server name indicates that SQL Response is attempting to connect and log in to that instance. If the connection or login is unsuccessful, the icon will be updated. For an explanation of SQL Server status icons, see Monitoring the status of SQL Servers .

Changing the credentials used to log in to a SQL Server instance

The account listed under Login credentials in the Add SQL Servers dialog box is, by default, the Alert Repository service account that was entered during the Alert Repository installation process. This account is used to connect to both the SQL Server (for collecting SQL-related data) and also to the computer hosting the SQL Server (for checking drive space and general server issues).

Changing the login credentials for a single SQL Server instance

1. Click the SQL Server to select it in the list.
2. Click Edit Server Properties.
3. In the Server Properties dialog box, under Login credentials, you can:
   • Specify a different Windows account to log in to the computer hosting the SQL Server (on the left side).

     If you have several SQL Servers running on the same computer, the same credentials will be used for each SQL Server.

   • Use SQL Server authentication instead of Windows authentication for the SQL Server login (on the right side)

Note: You cannot change the Windows authentication settings for SQL Server login in this dialog box. These credentials are specified when you install the Alert Repository, and are used for connecting to all monitored SQL Servers. To change the Alert Repository service account, you will need to edit the properties of the Alert Repository Windows service to run it using a different account.

Changing the login credentials for multiple SQL Server instances

You can change the credentials for multiple SQL Servers at the same time by selecting several SQL Servers in the list. The names of the SQL Servers you are editing are listed in the title bar of the Add SQL Servers dialog box.

Testing the login settings

Click Test Credentials to attempt to connect to the SQL Server(s) using the account settings you entered. SQL Response displays a message beneath the relevant account if it cannot connect using the specified details.

When editing the settings for multiple SQL Servers, SQL Response will stop testing the credentials after the first connection failure. The SQL Servers are tested in the order in which you selected them from the Add SQL Servers dialog box; this order is listed in the title bar.

Tip: When SQL Response returns an error message after the credentials test, you can view more information about the problem by moving your mouse pointer over the message to view a tooltip:

When you have changed the default credentials for the computer login or the SQL Server login, the entered credentials are shown separately in the Add SQL Servers dialog box:

Enabling SQL Profiler trace data

Enabling SQL Profiler trace data on a monitored SQL Server means that SQL Response continuously collects trace data from that server. When an alert on that SQL Server is raised, trace data for the period immediately before the alert is displayed as part of the alert details. This allows you to inspect in detail what was happening on that Server at the time the alert was raised, and can help you to diagnose the problem.

To enable the collection of SQL profiler trace data on a SQL Server:

1. In the Add SQL Servers dialog box, click to select the SQL Server in the list.
2. Click Edit Server Properties
3. In the Server properties dialog box, select Enable collection of trace data.

Note: We recommend that you enable trace on a maximum of eight to ten SQL Servers, as the continuous collection of trace data can lead to performance issues. SQL Response will still raise alerts on SQL Servers that do not have trace enabled, and performance data other than Profiler trace is provided as part of the alert details.

For more details, see Collecting SQL Profiler trace data.

Choosing a configuration template

Choosing a configuration template is an optional step. If you do not select a template, SQL Response will use the Default Template for added SQL Servers. The Default Template contains the settings and thresholds for each type of alert and recommendation that SQL Response raises.

Once you have added the SQL Servers to monitor, you can edit the Default Template or create new templates. See Working with templates.

Changing the connection settings

1. Select the required network protocol from the Network protocol list.

   The available client protocols are those configured in Microsoft SQL Server using the Client Network Configuration in Computer Management.

2. In Network packet size, type or select the size of the network packets to be sent.

   The default value is 4096 bytes.

3. In Connection time-out type or select the number of seconds to wait for a connection to be established before timing out.

   The default setting is 15 seconds.

4. In Execution time-out, type or select the number of seconds to wait before execution of a task is stopped.

   The default value is zero seconds (no time-out).

To force the connection to be encrypted, select the Encrypt connection check box.

Removing a SQL Server

To stop monitoring a SQL Server, right-click it in the Servers to show area of the filter pane, and click Remove SQL Server.

You can also press the Delete key to remove the currently selected SQL Server.

Select Delete existing Alert Repository files for this SQL Server to permanently remove all the alert files for this SQL Server stored on disk on the Alert Repository computer. This frees up space on the Alert Repository computer and can improve the performance of SQL Response.

Clear this option only if you intend to start monitoring this SQL Server again in the future, and want to preserve all the historical alerts.

Note: There may be a slight delay deleting the files from the Alert Repository data folder when the SQL Server is removed. It can take up to ten minutes before the files are deleted.

Select Deactivate Server license to deactivate SQL Response on this SQL Server instance, and on any other instances hosted on the same computer. If you are deactivating a SQL Server instance that is part of a cluster, you will be prompted to deactivate all the other nodes in the cluster.

For more information, see Deactivating your products.