Data Duplication Services
Objective
The duplication service is a Windows Service type application which makes it possible to copy data from a Pervasive Maestro database to an SQL Server database.
The duplicated data is kept updated in near real time, when modifications are made to the original database.
This service links the saved data of Pervasive files on the disk (for example: PROJET.IDX, BTRAV.IDX) and creates a copy of those saved files in a flat SQL Server database table.
|
The duplicated database in the SQL Server cannot be used by a Maestro instance. It must be used for consultation purposes only. |
Summary
Installation
The duplication service is installed through the maestro*MOBILE / maestro*SERVER installation kit. Duplication is automatically installed with maestro*SERVER (whether it is needed or not), but is deactivated by default. It must be activated in maestro*'s configurations.
However, this service is not installed upon the installation of a version of maestro* which already works with an SQL Server database.
Once installed, this service is displayed in Windows services list, under the name of Maestro Duplication.
This service is usually installed on the server where the Pervasive database can be found. It needs direct access to the physical data files on the disk.
It is possible to install this service on another server and then give it access to the files through a network path, but this option is not recommended if a minimum amount of performance is required.
The SQL Server server can be local or on a remote server. In the case of a remote server, the Log On As account cannot be Local System since this integrated account has no access to remote resources.
This service is installed on the following location of the disk be default: %PROGRAMFILES(X86)%\Maestro Technologies\Server\
Following the installation, the duplication service should be visible in the Windows service list:
The files should be installed in the "Program Files (x86)" directory:
Configuration
|
maestro* > Maintenance > General Maintenance > Processing > General Settings > General Settings tab > Data Duplication sub-tab
maestro* > Toolbar Configuration icon > General Settings > General Settings tab > Data Duplication sub-tab |
|
To have access to the configurations on the screen, Data Duplication must be activated in the installation options. |
|
|
Description |
|---|---|
|
Duplication enabled |
Duplication is deactivated by default upon an installation. This box must be checked for the duplication to work. |
|
Target server name |
Name or address of the SQL Server destination. Can include the name of an instance. |
|
User Name and Password |
Username and Password used by the duplication to connect itself to SQL Server. The user in question must be an SQL Server user (SQL Authentication) and not a user of the domain (Windows Authentification). If both fields are left empty, the duplication service will connect to the SQL Server using the Windows Authentification mode, with the user which has been defined for the execution of the Windows service. NOTE: It is not possible to specify a Windows user (domain) in this field. The user under which the execution of the duplication will take place must have the following authoizations in SQL Server:
|
|
Total Processes |
Corresponds to the number of table which will be duplicated in parallel. |
|
Inactive Schedules |
Time range during which the duplication will not be in operation. |
|
Companies |
Database to duplicate (master and company databases). |
|
Tables
|
Tables to duplicate in the chosen database. NOTE: It is recommended to restrict the number of selected tables to the essential ones, since a greater number of tables can cause a slowdown. |
|
It is not possible to activate the duplication for certain prefixes only, nor is it possible to put a restriction on which data from a table to duplicate. A simple a complete copy of the data from a Pervasive table to an SQL Server table will be done. |
|
The chosen configurations are automatically taken into account by the duplication service upon saving (a couple seconds or minutes delay is possible). The reboot of the Windows service is unnecessary. |
Operation
- The duplication execution is continuous. All changes made in the source database are automatically detected and applied to the desination database in near real time.
- The destination database is created if it does not already exist.
- For a company database, the name of the database created in the SQL Server always corresponds to the Pervasive source database name.
- For a master databse, the name is always "maestroddf".
- If the structure of a table changes (during a maestro* update, for example), the destination table is deleted and the data duplication process sarts anew.
|
If triggers, indexes, or other had been manually created for this table, the will be lost. |
Monitoring of the Duplication Status
During its execution, the service logs its actions (which table is in the process of duplication, start/end time, etc.) in a log table in the destination database, found on the SQL Server.
Name of the log table: "...log".
Example of a request allowing the consultation of the last duplicated tables and the tables currently in the process of duplication:
SELECT * FROM [dbo].[_log] ORDER BY Id DESC
If it seems the duplication is not creating any database, or if no action is reported in the ...log table, the error log should be consulted (see Error Log).
|
|
Description |
|---|---|
|
ID |
Unique sequential number for each table duplication. |
|
TableName |
Name of the source table. |
|
ReplicationType |
Always set to 1. Currently has no important signification for implantation. |
|
DateSource |
Date of the source file's (Pervasive) latest modification. |
|
DateStart |
Start date and time of the table duplication. |
|
DateEnd |
End date and time of the table duplication. If this column is "null", the duplication is still in process. |
|
Duration |
Duration of the table duplicaiton. |
|
CountRecord |
Number of records in the source table upon the execution of the duplication process. |
|
CountCopy |
Number of copied records (this column is used upon the initial table duplication only. Afterwards, all new records are indicated in CountInsert). |
|
CountInsert |
Number of new records. |
|
CountUpdate |
Number of updated records. |
|
CountDelete |
Number of deleted records. |
|
Success |
If the duplication is successful 1, if not 0. |
Error Log
The duplication service logs information in a file on start-up, shutdowns, and when errors happen. This log file can be viewed in the %PROGRAMDATA%\Maestro Technologies\Server repertory. The name of the file is MaestroDuplication.log.
When the file becomes too big, it is renamed and preserved under another name (MaestroDuplication.####.log) and a new file is created.
This log file should be viewed whenever there is a failing in the service, for example to diagnose server connexion problems, problems with SQL requests, etc.
