Configuration of parameters and licenses in DB (SCRATCH)


Scratch is the name of a database used as information exchange space among replica nodes, as well as a place to store all the configuration information of one or several replica servers.

Formerly, the replicator service configuration was made by using the following data files:

  • replicator.ini file, with all the nodes of the configuration parameters.
  • The replica server license, in a file with .LIC extension.
  • The licenses of the devices that we are signing in the different projects.
    The names structure consists on a license name (8 characters) and one extension .000, .001, etc.

NOTE: All the configuration files mentioned are located in the folder C:\WINDOWS


These files use implies that each node or replica server must have its own files set, which are used as working area and therefore they must be supported regardless of the databases that are replicated.

The migration tool explained belos, “migrates” the current licenses of the binary files from the Windows folder to database (LDATA field in master_replica_slave of the different projects), besides filling up the SCRATCH tables with the values there are currently at the replicator.ini file.

Whenever new licenses are requested for a database, which will be written will be only a text file with a line in BASE64. The content of that file is the one that has to be put at the LDATA field of the SCRATCH_DATABASES table to activate the new licenses.


SCRATCH Advantages


  • It is not necessary to make backup anymore of the licenses files contained within the WINDOWS folder (file .LIC and the files.000, 001, etc).
    By making backup of the Databases, also is supported the configuration and replica licenses signed at the master_replica_slave of each project.


  • With this configuration is easier to know at all times the number of licenses in a project or knowing how many licenses are available, just by consulting the master_replica_slave table, the logs with the LDATA field filled up are considered occupied licenses, having the field PENDIENTE=1 or not.
    By deleting the slave row or by emptying the LDATA field of that row, it would remain free such license without sending commands to the replica server.


  • External tools can be used to manage the licenses, configure the server…
    It is much easier to write a tool that modifies a database than one which works with a .INI file, since the licenses cannot be administered with external tools because they were in binary files (.000,.001… from the Windows folder).
    From the moment in which the configuration in database is activated, as the partners as the clients can have its own tools to administer the replicator.

Migration toward the new format


To make the migration of the current licenses which already exist registered at the current system to the new system, we must have the following steps:



1.- Create DB XOneReplicator
It is necessary to create the structure of the tables of the DB of configuration to the replica server. This DB can have any name. The tables structure is a little further down this page. We just have to create the tables structure, the data will be automatically filled by the migration tool described at the step 3, which will be in charge of reading the parameters defined at the replicator.ini and register them at the appropriated tables.


2.- Field LDATA at MASTER_REPLICA_SLAVE
Create the LDATA field (varchar(512)) at the master_replica_slave table, in the different projects to which the replicator provides service. This field has a string with the license of each device (Which formerly was at the binary files .000, .001…etc from the Windows folder).


3.- Migration of current licenses to the new format
To make a migration of the license files to the new system, a tool is provided in charge of reading the configuration of the .000, .001 files, etc and register the licenses at the LDATA field of the master_replica_slave table of the different projects. To download this tool, just going to the web XOneSupport.

This utility also fixes the replicator.ini file to point to the scratch database.

This tool must be executed AS ADMINISTRATOR at the machine having the .000, .001 license files, etc at the WINDOWS folder.
At the Scratch field (1) is indicated the connection string with the database which is going to content the replicator configuration.
At the license field (2), the name of the .LIC file there is in the Windows folder (Replica server license).

The tool will collect the information of each one of the licenses there are currently at the replicator.ini file, it will fill the LDATA fields from the master_replica_slave table as well as the different tables of the replica configuration databases (SCRATCH) and it will configure the replicator.ini with the connection string indicated at the migration window. Previously a copy from the current replicator.ini file will be taken, so this migration is totally reversible.

After the migration process, a copy of the replicator.ini file will be made, and also, a replicator.migrated file will have generated, the replicator.ini file will remain unchanged.

To finish the migration, we need to rename the replicator.migrated file into replicator.ini and restart the replicator service in order to start working with the configuration specified at database.


Configuration


LDATA at Master_Replica_Slave


In all the projects we have, it is necessary to create a LDATA varchar(512) field at the Master_Replica_Slave table that is going to be filled up every time a new license of a device will be signed. As of we use the replica configuration in database, it is considered a license in use every record of the master_replica_slave having this field filled up, no matter the value of the PENDIENTE field. To release a license, we will delete the record from the master_replica_slave or we will empty the value of the LDATA field from the device we want to release its license.

Replicator.ini


To enable the configuration in database, at the replicator.ini only is indicated the connection string to the Scratch database and all the remaining configuration is in this database. 

[server]
Scratch=Provider=System.Data.OracleClient;Data Source=BDQUESEA;User ID=USUARIOQUESEA;Password=PASSQUESEA;Max Pool Size=100
Node=1
ScratchDatemask=dmy


SCRATCH tables structure


The structure of the tables in the replication configuration database is as follows:

TABLE DESCRIPTION
SCRATCH_CONFIGURATION Configuration table with the values there were early at the section of the [server] replicator.ini file.
SCRATCH_DATABASES Configuration table with the different Databases licenses to which will be supported by the replica server.
A DBID is associated to refer to them later from the SCRATCH_DBCONFIG table.
There is a LDATA field that will have a value or another one depending on the maximum number of licenses that the project allows.
SCRATCH_DBCONFIG Configuration table with the different values associated to a project, each value will be associated to a DBID.
It corresponds to the values that formerly were associated in the section [DBID-1] of the replicator.ini.
SCRATCH_KEYNAME Table merely informative with the different attributes that exist for the replicator.
The attributes of the type 0 are attributes of the server (SCRATCH_DATABASES table), the ones of the type 1 are associated to the own attributes of a database configuration (SCRATCH_DBCONFIG), the ones of the type 2 are the connections types, and the ones of the type 3 are the Provider ones.
SCRATCH_NODES Connection with the different nodes (if it were more than one in cluster)
SCRATCH_ROWID_POOL System table, nothing is configured.
SCRATCH_SESSIONS System table, nothing is configured.


Creation of tables 

CREATE TABLE SCRATCH_CONFIGURATION ( ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, NID INT NULL , KEYNAME VARCHAR(50) NULL , 
KEYVALUE VARCHAR(50) NULL , ROWID VARCHAR(60) NULL  ) engine=MYISAM;
 
CREATE TABLE SCRATCH_DATABASES ( ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, NID INT NULL , DBID INT NULL , LICENSE VARCHAR(50) NULL , 
SL INT NULL ,     IQ INT NULL , LZ INT NULL , MNAME VARCHAR(50) NULL , LDATA VARCHAR(255) NULL , ROWID VARCHAR(60) NULL  ) engine=MYISAM;
 
CREATE TABLE SCRATCH_DBCONFIG ( ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, DBID INT NULL , NID INT NULL , KEYNAME VARCHAR(50) NULL , 
KEYVALUE VARCHAR(150) NULL , ROWID VARCHAR(60) NULL  ) engine=MYISAM;
 
CREATE TABLE SCRATCH_KEYNAME ( ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, TIPO INT NULL , KEYNAME VARCHAR(50) NULL , KEYVALUE VARCHAR(50) NULL ,
DESCRIPTION VARCHAR(250) NULL , BAJA INT NULL , ROWID VARCHAR(60) NULL  ) engine=MYISAM;
 
CREATE TABLE SCRATCH_NODES ( ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, ACTIVE INT NULL , NID INT NULL , IP VARCHAR(50) NULL , LS INT NULL , 
MNAME VARCHAR(150) NULL , ROWID VARCHAR(60) NULL  ) engine=MYISAM;
 
CREATE TABLE SCRATCH_ROWID_POOL ( ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, NID INT NULL , DBID INT NULL , RID VARCHAR(150) NULL , 
TAG VARCHAR(50) NULL , TS datetime NULL , ROWID VARCHAR(60) NULL  ) engine=MYISAM;
 
CREATE TABLE SCRATCH_SESSIONS ( ID INT AUTO_INCREMENT PRIMARY KEY NOT NULL, TS datetime NULL , SID VARCHAR(150) NULL , DBID INT NULL , 
MID INT NULL , BC INT NULL , BS INT NULL , BV INT NULL , CK VARCHAR(150) NULL , CRHST INT NULL , PP INT NULL , SK VARCHAR(150) NULL , 
CRYPT INT NULL , ROWID VARCHAR(60) NULL  ) engine=MYISAM;


Tables view

SCRATCH_CONFIGURATION

Configuration of the parameters of the replica server


SCRATCH_DATABASES

Different projects (DBID), which the replicator provides service to


SCRATCH_DBCONFIG

Configuration of parameters for the different projects (DBID)


SCRATCH_KEYNAME

Informative table with the available parameters


SCRATCH_NODES


LDATA in SCRATCH_DATABASES


The DATA field from scratch_databases is the replica server license that formerly was stored at the .LIC file, combined with a resume of all the .000, .001 binary files etc, from the Windows folder.

NOT to confuse the LDATA fields from the master_replica_slave (individual license of each device) with the LDATA field from SCRATCH_DATABASES (Database license / Number of licenses available in a project)

Everytime new licenses are requested for a database, an only text file with a line in Base64 will be received. That file content is the one that has to be put it at the LDATA field of the SCRATCH_DATABASES table to activate the new licenses.