Having worked with SharePlex for the last seventeen years, it is exciting to see SharePlex capture can now replicate data from non-Oracle database SQL Server for the first time. It is very easy to set up and looks and feels just like SharePlex for Oracle.
The current 9.0 release supports SQL Server 2012, SQL Server 2014 and SQL Server 2016 on both source and target running on windows 2012. You can replicate from on premise SQL Server host to either on premise SQL Server or Cloud (AWS EC2, RDS, or Microsoft Azure).
The SQL Server capture feature supports the following:
- Only DML operations are supported. DDL replication including truncate is not supported from a SQL Server source.
- SQL Server source and target tables must have a primary key.
- Only committed transactions are processed.
- Restarting replication from a previous point in time is not supported.
- SQL Server source tables must be quiesced for the activation process.
- SharePlex does not support replication from SQL Server tables to other tables within the same SQL Server database.
- You can have both a SQL Server source and an Oracle source replicating data to the same target.
- You can activate two or more databases in a SQL Server source all going to the same, or different, targets.
Please refer to SharePlex 9.0 release notes for additional information on what’s supported.
If you currently do not have SQL Server Replication set up on source, mss_setup.exe will create the publisher and configure the source server as a local distributor. A publisher for the source database will be created after the setup.
If you currently already have SQL Server Replication set up, then make sure the existing SQL Server replication is turned off and log reader is disabled. As only one log reader is allowed per database, SharePlex capture will replace log reader agent to read transactions from transaction log to replicate data to target. SQL Server Distribution and Subscribers are not used.
If mss_setup.exe has problem configuring the source as local distributor, you can manually configure the distribution through SSMS. Go to SQL Server Management Studio and right click on replication to configure publication and distribution. You need to choose your source server to act as its own distributor and create a local distribution database then rerun mss_setup.exe
mss_setup.exe -p <port#>
See below example, I am setting up replication from on premise SQL Server 2012 host to three target.
One target is SQL Server 2016 RDS on AWS, another is on prem SQL Server 2012 and the third target is Oracle.
C:\jess\prod90\bin>mss_setup -p9900Run mss_setup on source for source DB and RDS Target. You will be asked whether this host is going to be source and enter Y if this is the source server.
...Enter the Microsoft SQL Server DSN name or connection string  : jess2Enter the Microsoft SQL Server Administrator name : adminEnter the password for the Administrator account :Enter the database name : jess2...Will this database be used as a source? [n] : y
Run mss_setup.exe for RDS target on the source server if target is RDS or Microsoft Azure SQL database (PaaS).
Run mss_setup.exe on target server if it is on prem or on EC2 or Azure (IaaS).
You can either let mss_setup to create a new target DB or use an existing target DB. Use the master user account and password provided by AWS for RDS.
Below example, I let mss_setup create my target database jess using DSN jessRDS.
C:\jess\prod90\bin>mss_setup -p 9900...To create these items, we must connect to the database as an Administrator.Enter the Microsoft SQL Server DSN name or connection string  : jessRDSEnter the Microsoft SQL Server Administrator name : shareplexEnter the password for the Administrator account :NOTE: User 'shareplex' does not have the superuser role.Do you want to continue? [y] : yEnter the database name : jessDatabase name 'jess' does not exist.Would you like to create it? [y] : yDatabase 'jess' created.
Will this database be used as a source? [n] : n....
You can check the connection information under var dir/data/connections.yaml file which will have the database, DSN and SharePlex user login information.
Logon to SSMS, locate your source database and under replication expand trees for publisher and you should see the publisher for your source database has been created and ready for replication.
Run mss_setup on the second SQL Server target and run ora_setup on the third oracle target.
Now set up is done, we need to activate config and start testing
Establish Replication and Testing
Create your test table on both source target. Please note sql server replication requires tables to have primary key before you put it in replication. You won’t be able to activate config on table without PK. For supported data types, please refer to release notes for details.
Below is an example of my config file with named queue for each target.
sp_ctrl (sourcehost) > create config <filename>sp_ctrl(sourcehost) > view config <filename>datasource:r.jess2jessica."SQL_TEST" jessica."SQL_TEST" <sourcehost>:firstname.lastname@example.org ----> RDS target, use source hostjessica."SQL_TEST" "JESSICA"."SQL_TEST" <targethost1>:po@o.ORA11GR2 -----> Oracle targetjessica."SQL_TEST" jessica."SQL_TEST" <targethost2>:email@example.com -----> SQL server target
Note: Source and target can not have the same database name in the same config file and r.xxx is the database name and not DSN. SQL server owner and table names are case sensitive. You should use double quote around owner and table name if they are case sensitive. You need to use “OWNER”.”TABLE_NAME” in upper case for oracle target tables.
Make sure the source table is quiet and activate the config file
sp_ctrl> activate config <filename>
Now you should see capture, reader and post process running from sp_ctrl icon.
Run insert, update, delete on the source table and you can see data being replicated through SharePlex.
Run ‘show capture detail’, ‘show read detail’, ‘show post detail’, ‘show post sessions detail’ to see which log and what operations are being processed. Please note the timestamp shown is the time capture sees the data not exactly when the transaction occurs.
Note: You won’t be able to truncate the source table once activation is done.
truncate table jessica.SQL_TEST;
Msg 4711, Level 16, State 1, Line 1
Cannot truncate table 'jess2.jessica.SQL_TEST' because it is published for replication or enabled for Change Data Capture.
For monitoring activities, install Spotlight for SQL Server to help monitor source and target SQL activities and troubleshoot replication issues. You can use Toad as well.
Run trace post or set SP_OPX_UTILIZATION_TIMERS to 1 to find out the posting speed and where it is spending time on.
Sp_ctrl> trace post <mins>
Check var dir/log/*trace* file for output information
It provides the average transaction rate, the timing break down and the list of top tables with operation type and average operations per second.
Sp_ctrl> set param SP_OPX_UTILIZATION_TIMERS 1
To turn off utilization timers after a period of time
Sp_ctrl> reset param SP_OPX_UTILIZATION_TIMERS
Check var dir/log/*csv file for output information, it gives timing break down on post modules.
Clean up Replication
mss_cleansp.exe –p <port#> <database_name>/<shareplex user>/<shareplex user password>
mss_cleansp.exe -p 9900 jess2/splex9900/splex9900
For more information on how to set up replication from SQL Server, please see below KB article on www.quest.com.