Mirror Module Configuration Guide

Overview

The Mirror Module enables you to export or “mirror” data that the Nectar RIG has collected to an external SQL database.

Currently, the Mirror Module supports the following databases:

• MS SQL
• MySQL
• PostgresSQL

Note

Accessing Mirror Module does not work when connected directly to CIP.

About this Guide

This guide provides instructions on how to configure and use the Mirror module, including:

Create a Database

Configure the RIG for Mirror Module

Configure Mirror Module

Audience

This guide is intended for system administrators or engineers who have system administration access and technical knowledge of deploying Nectar Foundation.

Supported Software Versions

• Nectar UCMP v8.1

Create a Database for Mirroring Data

Creating a new database for mirroring data includes the following tasks:

Create a Database

Enable SQL Server and Windows Authentication

Create a Local SQL Server User

Enable TCP Port

Note

This is a working example. Creating a secure user and database is outside the scope of this document. You should seek advice from your internal IT Support team.

Create a Database

Follow these steps to create a database to be used to mirror data from the internal database to an external database:

1. Navigate to and launch Microsoft SQL Server Management Studio. The SQL Server window appears.

1. Click Connect.

Figure 2-1 SQL Server

1. From the Object Explorer window, right-click on Databases and select New Database....

Figure 2-2 Database > New Database

The New Database window appears.

Figure 2-3 New Database

1. Enter the new Database Name, such as rigDB; then click OK.
2. Notice the new rigDB database appears in the Databases folder list.

Figure 2-4 rigDB

Enable SQL Server and Windows Authentication

Follow these steps to enable SQL Server and Windows Authentication:

1. Navigate to the Microsoft SQL Server Management Studio.
2. Right-click on the server and click Properties.

Figure 2-5 Properties

1. From the Server Properties window, select Security in the left pane.

Figure 2-6 Server Properties

1. Under Server Authentication in the right pane, select SQL Server and Windows Authentication Mode.
2. Click OK.
3. Restart SQL Services.

Create a Local SQL Server User

You need to create a local SQL server user with rights over the external Mirror database. You do not need to define the schema for the database. When the Mirror module is initiated, it will create the schema within the database.

Follow these steps to create a local SQL server user for the new database, so the RIG can connect to the database:

1. Open the Security folder.

Figure 2-7 Login > New Login

1. Right-click on Login and select New Login....

The Login - New window appears.

1. Enter the following parameters:
2. Click OK.

Figure 2-8 Login - New

Table 2-1 Properties

1. Open the Security folder.

1. Open the Logins folder.

Figure 2-9 Mirroruser > Properties

1. Right-click on the new user, mirroruser, and select Properties.

The Login Properties window appears for the new user.

Figure 2-10 Login Properties

1. Click on Securables in the left pane; then view the Permissions - Explicit tab on the right.
2. Select the check box in the Grant column for the following Permissions:

Alter any database

Create any database

View any database

Figure 2-11 Permissions

1. Click on Server Roles in the left pane.

Figure 2-12 Server Roles

1. Select the following Server roles:

dbcreator

public

1. Click OK.

Enable TCP Port

Follow these steps to enable the TCP port:

1. Navigate and launch SQL Server Configuration Manager. The SQL Server Configuration Manager window appears.

Navigate to SQL Server Network Configuration > Protocols for SQLEXPRESS.

1. Select TCP/IP in the right pane; then right-click and select Enable.

Figure 2-13 Enable TCP/IP

1. Select TCP/IP again; then right-click and select Properties.

Figure 2-14 SQL Server Configuration Manager

The TCP/IP Properties window appears.

Figure 2-15 TCP/IP Properties

1. Click the IP Addresses tab.
2. Locate IPAll.
3. Change the TCP Port to 1433.
4. Click OK.
5. Restart the SQL server:
   1. Navigate to the Services window.

Figure 2-16 Restart

• 
  
  1. Right-click on SQL Server and select Restart.

Configure the RIG for Mirror Module

This section explains how to configure the RIG for the Mirror Module, including:

Activate the Mirror Module

Edit server.properties File

Edit application.properties File

Restart RIG

Activate the Mirror Module

This section provides two different methods for activating the Mirror Module in the RIG server. Select Method 1 or Method 2 to activate the Mirror Module.

Method 1

Follow these steps to activate the Mirror Module:

1. Navigate to RIG > Module Configuration.

Figure 3-1 RIG > Module Configuration

The Module Configuration window appears.

1. Select Mirror; then click Apply.

Figure 3-2 Enable Mirror

Method 2

Follow these steps to add the Mirror Module to SQL server using the Maritime Terminal:

1. Navigate to RIG > Admin > Maritime Terminal.

Figure 3-3 RIG > Admin > Maritime Terminal

The Telnet Maritime Terminal window appears.

Figure 3-4 Telnet Maritime Terminal

1. Enter the following command:

Ahoy> tools moretools addmodule mirror mssql

The following message appears:

[Module Added]

Edit server.properties File

Follow these steps to edit the server.properties file.

1. Navigate to RIG > File Manager. The File Manager window appears.
2. Look for the Remote File System pane on the right; then navigate to

\etc\server.properties configuration file in the RIG installation directory.

1. Right-click on the server.properties file and select Download.
2. Save the server.properties file to your local destination.
3. Navigate to the server.properties file.
4. Right-click on the server.properties file and select Open With.
5. Open using Notepad.
6. View the following entries that the previous Maritime command created:

module.##.name=mirror module.##.type=mirror

1. Modify the following entry by changing derby to mssql:

module.##.data=mssql

Note

This module.##.data=mssql entry is specific to the database you are trying to mirror to.

The ## correspond to the entry numbers in the file.

1. Navigate to File > Save to save any changes.
2. Navigate to RIG > File Manager.

The File Manager window appears.

1. Navigate to the \etc\server.properties configuration file in the RIG installation directory.
2. Right-click on the server.properties file and select Upload.
3. Browse to the updated server.properties file, select, and click Open. The Upload File window appears.
4. Click Upload.

Edit application.properties File

Follow these steps to edit the application.properties file.

1. Navigate to RIG > File Manager. The File Manager window appears.
2. Navigate to application.properties file.
3. Right-click on the application.properties file and select Download.
4. Save the application.properties file to the local destination.
5. Navigate to the application.properties file.
6. Right-click on the application.properties file and select Open With.
7. Open using Notepad.
8. Locate the Spring Application Settings.
9. Add the following text to the end of spring.profiles.active = derby line:

,sqlserver

1. Locate SQL Server section.

Figure 3-5 Spring Application Settings

Figure 3-6 SQL Server

1. Uncomment the SQL Server section.
2. Specify the following values for the SQL Server:

SQL Server Name

Port

Database Name

Username

Password

1. Navigate to File > Save to save any changes.
2. Navigate to RIG > File Manager. The File Manager window appears.
3. Navigate to application.properties file.
4. Right-click on the application.properties file and select Upload.
5. Browse to the application.properties file, select, and click Open. The Upload File window appears.
6. Click Upload.

Restart RIG

Follow these steps to restart the RIG and apply the changes:

1. Navigate to RIG > Admin > Restart.
2. When the following message appears, click Yes.

Figure 3-7 Restart

Configure Mirror Module

Configuring the Mirror Module includes the following tasks:

Set Up Mirror Module

Verify the Mirrored Data

Set Up Mirror Module

Follow these steps to set up the Mirror Module:

1. After the RIG is restarted, navigate to the log file to verify the Mirror Module successfully started.

Figure 4-1 Log

1. To set up the mirror, navigate to Reports > Data Mirrors.

Figure 4-2 Reports > Data Mirrors

The Data Mirrors window appears.

1. Click Add.

Figure 4-3 Reports > Data Mirrors

The Add Mirror window appears.

Figure 4-4 Add Mirror

1. Select the RIG you want to mirror using the drop-down.
2. Select the Module you want to mirror using the drop-down.
3. Select the Table you want to mirror using the drop-down
4. Select the fields you want to mirror; then click OK.

The Data Mirrors window appears with the newly created entry.

Figure 4-5 Enable

1. Select the new entry; then click Enable.

The Enabled column changes from No to Yes.

Figure 4-6 Enable

Verify the Mirrored Data

Follow these steps to verify the data is being mirrored:

1. Navigate to Microsoft SQL Server Management Studio.

Figure 4-7 rigDB > Tables > Mirror Entries

1. Open the rigDB folder.
2. Open the Tables folder.
3. Notice the two Mirror entries, such as:

qatest2.mirror_MIRRORS

qatest2.mirror_PERMISSION_USERS

1. Right-click on the first entry (qatest2.mirror_MIRRORS) and Select top 1000 rows.

Figure 4-8 Verify Mirror Entry 1

1. Locate the entry in the SQL database to verify it matches the entry created in the RIG.

Figure 4-9 SQL Database

1. Right-click on the second entry, qatest2.mirror_PERMISSION_USERS, and Select top 1000 rows.

Figure 4-10 Verify Mirror Entry 2

1. Locate the entry in the SQL database to verify it matches the entry created in the RIG.

Figure 4-11 SQL Database

1. To verify the SQL server entries match the Permissions/Users in the RIG, navigate to RIG > Permissions.

The Permissions - Users window appears.

1. Notice the server entries match.

Figure 4-12 SQL Database