Online Documentation for SQL Administrator for SQL Server

SQL Administrator Repository

SQL Administrator uses a repository database on the server to store the service information.

The repository stores:

1. The last alarm values.
2. Alarm log (for alarms that support logs).
3. Statistics collection parameters for all registered servers.
4. Current (detailed) statistics of the server productivity.
5. Average statistics of the server productivity.

Use the Server | SQL Administrator Repository... main menu item to open the dialog window to set repository parameters.

Specify the following settings for the repository:

SQL server name

Type in or use the drop-down list to select the host/instance name where the repository database should reside.

Note: If Microsoft® SQL Server™ is installed as a named instance the server name must be specified in the following format: computer_name\sqlserver_instance_name or ip_address\sqlserver_instance_name (e.g. "MYCOMPUTER\SQLEXPRESS", "127.0.0.1\SQLEXPRESS").

Authentication

Specify the type of SQL Server authentication to be used for the connection: SQL Server authentication or Windows authentication. It is strongly recommended to avoid using SQL Server authentication with "sa" as the login.

If SQL Server has been selected as the authentication type, you should also provide authorization settings: Login and Password.

Database name (new or existent)

Type in or use the drop-down list to select the database.

If you have specified a new repository database then the following window appears.

It allows you to specify servers which system information will be stored in the repository database and therefore which statistics will be collected via the Statistics Collector. For your convenience the Select All and Unselect All functions are implemented in the context menu of the servers list.

To apply new statistics collector service parameters you need to reinstall the service.

In what follows you can define whether to collect or not statistics for the specific server by checking or unchecking the

Enable Statistics collection option in the Setting statistics collection parameters section of the Server Registration Properties

It is strongly recommended not to store a repository on a production server to avoid problems with a productivity.

Along with a repository creation the following database maintenance tasks are also created:

• avg_stats - averages current statistics to reduce the place occupied with statistics of server productivity;
• cleanup_stats - drops current statistics in compliance with storage time of the current and average statistics;
• cleanup_alarm_log - drops old alarm units in compliance with storage time of the alarm summary.

These tasks have schedules to be run automatically. You have to check the execution state of these tasks if they are not executed due to some reasons.

The execution state of these tasks must be checked because otherwise, if these tasks are not executed due to some reasons, repository database may greatly increase in size, and some problems may occur in SQL Administrator when accessing to repository data.