Home / Repstance User Guide / DATABASE CONFIGURATION COMMANDS / Prepare Source Database

Prepare Source Database

The prepare database=source command makes a database ready to be the Source of a replication. In a SQL Server database it enables CDC and creates the schema repi with the tables and procedures of the Capture Process, a database trigger for DDL statements and the SQL Server Agent jobs. In an Oracle database it enables the supplemental logging and creates the user REPI with the tables that Repstance uses. In a MySQL database it creates the database repi with the tables of the Initial Load and of the Data Comparison, and in a PostgreSQL database it creates the schema repi with its tables and functions and the event triggers that record the DDL statements. What is created, and the privileges every step needs, are listed in Database Privileges. The rules that all the database commands share and the messages are on DATABASE CONFIGURATION COMMANDS.

SQL Server and Oracle can be a Source Database, and, since version 6, in Repstance Advanced Edition only, MySQL and PostgreSQL. The command can be run again: it creates what is missing and replaces the procedures. With generatesql=1 it creates nothing and returns the SQL script, so that an administrator can run it.

Command line (repcli)

repcli prepare database=source dbtype={mssql|oracle|mysql|postgresql} \
[connection parameters of the Source Database] \
[other parameters of the Source Database] \
generatesql={0|1}

Parameter	Description
database	source. Required.
dbtype	mssql, oracle, mysql or postgresql. Required.
generatesql	1 makes prepare return the SQL script and change nothing. The default is 0: prepare creates the objects. validate does not use the parameter, and remove removes the objects whatever its value.
server, port, user, password, dbname and the other connection parameters	How to reach the database. They are different for every type of database. For MySQL and PostgreSQL (since version 6, in Repstance Advanced Edition only) see MySQL as Source and PostgreSQL as Source; for SQL Server and for Oracle see SQL Server as Source and Oracle as Source.
The other parameters	The ones that belong to the type of the database: housekeeping for SQL Server, supplementallog, directlogmode and locallogdir for Oracle. MySQL and PostgreSQL have no other parameters.

Prepare a SQL Server database:

repcli prepare database=source dbtype=mssql server=db-src.example.com port=1433 dbname=testDB user=repuser password=Passw0rd123
[Completed]: Database [db-src.example.com:1433,testDB] has been prepared.

Return the script instead of running it. The script goes on for about 500 lines for a SQL Server database; an administrator runs it with the tool of the database:

repcli prepare database=source dbtype=mssql server=db-src.example.com port=1433 dbname=testDB user=repuser password=Passw0rd123 generatesql=1
[Completed]:
---- SQL ----
use [testDB]
go

if exists (select * from sys.triggers where parent_id=0 and name = N'repi_ddl_capture_tr')
 drop trigger [repi_ddl_capture_tr] on database
go
declare @db nvarchar(255) =  DB_NAME()
if not exists (select 1 from sys.databases where name=@db and is_cdc_enabled = 1)
begin
if  db_id('rdsadmin') is not null
  exec(' declare @db nvarchar(255) =  DB_NAME()
        exec msdb.dbo.rds_cdc_enable_db @db')
else if object_id('msdb.dbo.gcloudsql_cdc_enable_db') is not null
  exec(' declare @db nvarchar(255) =  DB_NAME()
        exec msdb.dbo.gcloudsql_cdc_enable_db @db')
...

Prepare an Oracle database that the Capture Process reads with LogMiner (directlogmode=0, the default):

repcli prepare database=source dbtype=oracle connectiontype=ezconnect server=ora-src.example.com port=1521 servicename=ORA1 user=repuser password=Passw0rd123 supplementallog=ALL

Prepare a MySQL database (since version 6, in Repstance Advanced Edition only):

repcli prepare database=source dbtype=mysql server=mysql-src.example.com port=3306 dbname=sales user=repuser password=Passw0rd123
[Completed]: Database [mysql-src.example.com:3306,sales] has been prepared.

Prepare a PostgreSQL database (since version 6, in Repstance Advanced Edition only). The parameter dbname is required:

repcli prepare database=source dbtype=postgresql server=pg-src.example.com port=5432 dbname=salesdb user=repuser password=Passw0rd123
[Completed]: Database [pg-src.example.com:5432,salesdb] has been prepared.

The command is refused when a required parameter is missing, when the database type cannot be a Source Database or when a parameter does not exist:

repcli prepare database=source dbtype=mssql server=db-src.example.com
[Failed]: Missed port parameter
  port                   TCP port of the SQL Server. [1-65535; required]

repcli prepare database=source dbtype=s3 server=db-src.example.com port=1433 user=repuser password=Passw0rd123
[Failed]: An invalid value [s3] has been supplied for parameter [dbtype] with database=source.
  Valid values: oracle | mssql | mysql | postgresql

REST API

Endpoint: https://repstance_url/configure/database
Method: POST
Header: Content-Type: application/json and X-Token: token

The body of the request:

{
  "command": "prepare",
  "parameters": [
    ["database", "source"],
    ["dbtype", "mssql"],
    ["server", "db-src.example.com"],
    ["port", "1433"],
    ["user", "repuser"],
    ["password", "Passw0rd123"],
    ["dbname", "testDB"],
    ["housekeeping", "1"]
  ]
}

The body of the answer:

{
  "Status": "Completed",
  "Message": "Database [db-src.example.com:1433,testDB] has been prepared."
}

With generatesql=1 the Message is the script. A database that cannot be reached is answered with the status Failed and the error of the driver, and the HTTP code is 200:

{
  "Status": "Failed",
  "Message": "unable to open tcp connection with host '127.0.0.1:1': dial tcp 127.0.0.1:1: connect: connection refused"
}

HTTP code	Meaning
200	The request is valid and the command has been carried out. The body has the status Completed or Failed (prepare and remove) or Valid or Invalid (validate), and the message. A command that cannot do its work, for example because the database cannot be reached, is also answered with 200 and the status Failed or Invalid: always read the status.
401	The token is invalid.
422	The request cannot be carried out: the JSON, the command or a parameter is wrong. The body has the status Failed and the message, for example Missed port parameter.

What happens

SQL Server. The command works in the database that dbname names. It enables CDC for the database, creates the schema repi with its tables and procedures, creates the database trigger repi_ddl_capture_tr and, unless the server is a PaaS database, the SQL Server Agent job of the heartbeat and, with housekeeping=1 or 2, the job that cleans the CDC tables.
Oracle. The command switches on the supplemental logging that supplementallog names, creates the Oracle directories ONLINELOG_DIR and ARCHIVELOG_DIR when directlogmode=1 needs them, and creates the locked user REPI with the tables and the sequence of Repstance. With directlogmode=0 it also creates the global temporary table REPI.DML_CAPTURE_OBJECTS.
MySQL. The command creates the database repi with the tables ld_capture (the tables of the Initial Load) and ValidateTables (the tasks of the Data Comparison). The binary log of the server is not changed: the settings that it needs are checked by validate (see MySQL as Source).
PostgreSQL. The command works in the database that dbname names. It creates the schema repi with the tables ld_capture, ValidateTables, capture_scope and ddl_audit, the functions table_cols, log_ddl and log_drop and the event triggers repi_ddl and repi_drop that record the DDL statements. The slot and the publication of a Capture Process are made when the process runs for the first time, not by prepare (see PostgreSQL as Source).
What exists is left as it is, and the tables that are replicated are not changed.
The command creates no login and grants no privileges (see Database Privileges).

Web UI

Click “Add Capture” on the dashboard, enter the id and the name of the process and click the logo of the database:

Fill in the form “Database Settings” (the fields of MySQL and PostgreSQL, which are offered in Repstance Advanced Edition only, are described in MySQL as Source and PostgreSQL as Source, the others in SQL Server as Source and Oracle as Source) and click “Test connection”. The buttons “Prepare for Replication” and “Show Prepare SQL” appear. “Prepare for Replication” prepares the database, “Show Prepare SQL” returns the script instead:

Web UI, Database Settings: not prepared, Prepare for Replication and Show Prepare SQL highlighted

A confirmation window comes first. Click “Install & Proceed”:

Web UI, Prepare Source Database confirmation with the Install and Proceed button highlighted

The window of “Show Prepare SQL” shows the script. Click “Copy” to copy it, then run it in the database:

Web UI, Prepare SQL window with the script of the SQL Server Source and the Copy button highlighted

When the database has been prepared the dialog shows the message Completed. Click “Test connection” to check the database:

Web UI, Database Settings: database prepared message with the Test connection button highlighted

The whole procedure, with the Capture Process around it, is in Setup First Replication.

DATABASE CONFIGURATION COMMANDS

SQL Server as Source