Appendix G. Advanced Monitoring configuration – Stand-Alone Monitoring database

Enabling the stand-alone Monitoring database feature splits the Sentinet data between two isolated databases.

  • The first database, Repository Database contains Sentinet configuration data that described entire Sentinet infrastructure, such as Repository folders structure, services registrations, access rules, etc. Repository Database is relatively small and typically does not require its size management, while its consistency and availability are essential for Sentinet operability. Periodic backups of this database are required for reliable disaster recoveries and possible relocations to different SQL Server instances.

  • The second database, Monitoring Database contains all monitoring data that Sentinet collects during its operation, such as monitoring transactions, recordings, aggregations, SLA violations, etc. This data is volatile, it is constantly collected by Sentinet Nodes and ultimately recorded in the database. As with any OLTP database, monitoring data can cause database to grow pretty quickly and can fill up the system resources, if it is left unattended. For this reason, must be periodically purged.

Two physically separated databases, Repository Database and Monitoring Database allow for greater management flexibility. For example, you may have different maintenance plans and backup schedules. You may even completely exclude monitoring data from your backups, because it is not essential for Sentinet successful operation. Hosting the databases on different servers will also improve performance.

The drawback of the two databases configuration is complexity. Some configuration data between these databases must be synchronized. For example, basic service information is needed in the Monitoring database for performing data aggregations and building reports. Also, alerts generated by the Monitoring sub-system should be merged with other alerts and handled in a consistent way. To achieve synchronization, Sentinet uses SQL Server data replication feature. Specifically, SQL Server transactional replication is used to copy shared configuration data from the Repository Database to the Monitoring Database, and SQL Server merge replication is used to copy monitoring alerts from the Monitoring Database to the Repository Database.

Important

Note that not all editions of the Microsoft SQL Server support replication, so you should plan your infrastructure accordingly. Azure SQL Databases do not support replications to the extent required by Sentinet.

Important

Configuring Sentinet with stand-alone Monitoring Database and SQL Server Replication, requires appropriate level of knowledge and experience with Microsoft SQL Server replication configurations.

The content of this chapter describes only those aspects that are relevant to the Sentinet configuration.

Sentinet Repository Configuration Wizard and sample database scripts shipped with the product help to enable stand-alone Monitoring Database with required data replications. Before running the wizard and scripts, specific prerequisites must be completed.

Prerequisites

  1. SQL Server Replication feature is installed and configured.

  2. SQL Server Agent Service is running.

  3. SQL Server Agent Service’s account has read/write permissions to the Snapshot Folder (typically %MSSQLSERVER%\repldata).

  4. Account which will be used for Publisher's replication job must be given db_owner permissions in Subscriber's database. This applies to both MS SQL servers, if different servers are used for Repository Database and Monitoring Database. If custom replication account is not specified, the SQL Server Agent account will be used by default.

Enabling stand-alone Monitoring Database

To enable stand-alone Monitoring database, check the Enable stand-alone Monitoring database checkbox. Sentinet configuration with stand-alone Monitoring database requires configuration of MS SQL Server(s) with SQL Server Replication.

Stand-alone Monitoring database
Figure. Enable stand-alone Monitoring database checkbox.
Note

Exclamation Sign icon, located next to this checkbox, is just a clickable reminder to consult with online version of this document‘s chapter.

Provide input data shown below this checkbox. The input data has the same meaning as described for the Repository Database, except that it applies to a new or existing stand-alone Monitoring Database.

In relation to the stand-alone Monitoring Database configuration scenario, the Repository Configuration Wizard executes the following tasks:

  1. creates two databases on the same or different servers;

  2. creates appropriate tables, views, and stored procedures in each of the databases;

  3. creates Publisher and Distributor on the Sentinet Repository database server; To use remote distributor, see Remote Distributor section in Advanced Settings chapter below.

  4. creates Transactional Publication of the shared configuration data and corresponding Push Subscription for the Monitoring Database;

  5. creates Publisher and Distributor on the Monitoring Database server, if Repository database server and Monitoring database servers are different; To use remote distributor, see Remote Distributor section in Advanced Settings chapter below.

  6. creates Merge Publication of the monitoring alerts and corresponding Push Subscription for the Sentinet Repository database.

Advanced Settings

Advanced settings are used when SQL Server and Sentinet data replication must be configured with non-default replication settings. You should use Advanced settings only if you create non-default replication settings and you create a new replication for Sentinet databases. Click Advanced button to provide advanced replication settings.

Advanced Replication Settings
Figure. Advanced Replication Settings button and dialog box.

Setup Replication checkbox controls if Sentinet will configure SQL Server replication (with default or custom SQL replication settings). When Setup Replication is unchecked, Sentinet will basses any configurations related to SQL replication assuming that Sentinet database administrator(s) will be responsible for setting up replication completely on their own. In this scenario (typically) modified Sentinet SQL “Create Replication…” scripts will be used to setup replication outside of the context of the Repository Configuration Wizard.

Publisher/Subscriber Account section configures “mirrored” credentials which will be used by publisher when connecting to subscriber, and by subscriber when connecting to publisher. Replication Job will always run under SQL Agent account. Mirrored credentials means that the same login/password is used for SQL Authentication, or the same Active Directory User account is used for Windows Integrated authentication.

Remote Distributor section configures new replication when using existing (pre-created) remote distributor. The Distributor Password will be used by the publishers when connecting to the remote distributor identified by the Distributor Name field.

Note

If customers want to create and use completely custom replication with remote distributor, they can use stand-alone SQL scripts to create databases and replications, and then run Configuration Wizard (interactive or command line version). The Wizard will skip configuring replication if it detects it already exists.

Before configuring the Sentinet Repository, you will need to prepare the remote distributor instance. Relevant Microsoft documentation can be found here, but in general you will need to follow these steps:

  1. Create the distributor: EXEC master.dbo.sp_adddistributor @distributor = N'{DISTRIBUTOR NAME}', @password = N'{DISTRIBUTOR PASSWORD}';

  2. Create the distribution DB: EXEC master.dbo.sp_adddistributiondb @database = N'distribution', @data_folder = NULL, @log_folder = NULL, @log_file_size = 2, @min_distretention = 0, @max_distretention = 72, @history_retention = 48, @security_mode = 1, @login = NULL, @password = NULL;

  3. Create distributor publishers: EXEC [distribution].sys.sp_adddistpublisher @publisher = N'{PUBLISHER NAME}', @distribution_db = N'distribution', @security_mode = 1, @login = NULL, @password = NULL, @working_directory = NULL, @trusted = N'false', @thirdparty_flag = 0, @publisher_type = N'MSSQLSERVER'; IMPORTANT: you will need to execute this step for EACH publisher. So, if Repository and Monitoring DBs are hosted on 2 different servers, run sp_adddistpublisher twice: first for the SQL instance that will host the Repository database and then for the SQL Server instance that will host the Monitoring database.

  4. Verify network connectivity between distributor and publishers. Distributor host name must be resolvable from publishers and vice versa.

  5. Run the Repository Configuration Tool. On the monitoring database step in Advanced Settings dialog specify the remote distributor name and password used in step 1.

Data Folder Path and Snapshot Folder Path allow to specify non-default paths for SQL Server Data folder and Snapshot folder. If Distribution database and Distributor already exist, then this section can be ignored.

Note

The meaning of this section’s input data is described in SQL Server documentation. For example, sp_adddistributiondb stored procedure explains @data_folder parameter, which is mapped to Data Folder Path field, while sp_adddistpublisher stored procedure explains @working_directory parameter, which is mapped to Snapshot Folder Path field.