New Chapter: SharePlex 9.0 SQL Server Capture

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.

https://support.quest.com/technical-documents/shareplex/9.0/release-notes/

Setup

  1. Install SharePlex with 9.0 binaries on source and target servers.
  1. Create ODBC DSN on source and target host for SharePlex to connect to SQL Server.
    • Make sure you choose SQL server non-native client odbc driver when creating DSN.
    • If you are replicating to open target cloud (PaaS such as SQL Server RDS on AWS or Microsoft Azure SQL database), set up both source and target DSN on source server or on an intermediary server if needed.  Use AWS Endpoint name as server name for RDS target DSN.  Set up security group in AWS to allow inbound TCP traffic on SQL server port 1433 to your target AWS server.
    • RDS or Microsoft Azure SQL database can not be used as source as publishing is not available.
  1. Run mss_setup.exe under the prod dir/bin on source and target. 

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#>

  1. mss_setup.exe will create SharePlex internal user and tables inside the existing SQL Server database you provide or it will create a new database if one does not exist.

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.  

Source DSN RDS Target DSN SQL Server Target DSN Oracle Target
jess2 jess RDS jess65  
Source Database RDS Target Database SQL Server Target Database Oracle Target Database
jess2 jess jess65 ORA11GR2


C:\jess\prod90\bin>mss_setup -p9900
Run 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 [] : jess2
Enter the Microsoft SQL Server Administrator name : admin
Enter the password for the Administrator account :
Enter the database name : jess2
...
Will this database be used as a source? [n] : y

Target:

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 [] : jessRDS
Enter the Microsoft SQL Server Administrator name : shareplex
Enter the password for the Administrator account :
NOTE: User 'shareplex' does not have the superuser role.
Do you want to continue? [y] : y
Enter the database name : jess
Database name 'jess' does not exist.
Would you like to create it? [y] : y
Database '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.jess2
jessica."SQL_TEST" jessica."SQL_TEST" <sourcehost>:rds@r.jess            ----> RDS target, use source host
jessica."SQL_TEST" "JESSICA"."SQL_TEST" <targethost1>:po@o.ORA11GR2     -----> Oracle target
jessica."SQL_TEST"  jessica."SQL_TEST" <targethost2>:sql@r.jess65        -----> 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.

Troubleshooting

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

  1. Shutdown sp_cop services with sputil icon on both source and target, check TaskMgr tab to make sure all shareplex related processes are gone.
  2. Run mss_cleansp.exe on source and target for each DB involved in replication from prod dir/bin

mss_cleansp.exe –p <port#> <database_name>/<shareplex user>/<shareplex user password>

Example:

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.

https://support.quest.com/kb/230379

Anonymous