1. Install SharePlex with 9.0 binaries on source and target servers.
2. Create ODBC DSN on source and target host for SharePlex to connect to SQL Server.
3. 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
4. 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 |
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.
C:\jess\prod90\bin>mss_setup -p9900
...
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.
Example from my source server
Connections.yaml file
r.jess:
database: jess
dsn: jessRDS
dstype: sqlserver
password: xxxxxxxxxxxx
plugin: sqlserver
plugin_direction: target
plugin_version: 1
user: splex9900
r.jess2:
database: jess2
dsn: jess2
dstype: sqlserver
password: xxxxxxxxxxxxxxxx
plugin: sqlserver
plugin_direction: both
plugin_version: 1
user: splex9900
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 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 example,
My source Sql Server database is jess2 and I created schema jessica and a test table called SQL_TEST
Create the same table on target but use the target database jess65 or aujess instead
USE [jess2]
GO
create schema jessica;
GO
CREATE TABLE [jessica].[SQL_TEST](
[ID] [numeric](38, 0) NOT NULL,
[NAME] [varchar](50) NULL,
[PHONE] [numeric](38, 0) NULL,
[MYDATE] [datetime2](7) NULL,
PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
create SQL_TEST on Oracle target as well
SQL> desc SQL_TEST
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(50)
PHONE NUMBER
MYDATE TIMESTAMP(6)
Start SharePlex cop services using sputil icon on your desktop, choose the correct port# and click start service.
Click on sp_ctrl icon to launch sp_ctrl which is the command line interface.
Sp_ctrl> create config
It will open a text editor window and enter below example (r.database, this is not DSN name)
In my example, I used source host as the as my target is RDS which I don’t have access to install SharePlex. If you target host is on premise or if you use an intermediary server for RDS target, you can enter the target or intermediary host name.
Below is an example of my config file with named post queue for each of my target.
sp_ctrl (sourcehost) > view config
datasource:r.jess2
jessica."SQL_TEST" jessica."SQL_TEST" :rds@r.jess ----> my RDS target
jessica."SQL_TEST" "JESSICA"."SQL_TEST" :po@o.ORA11GR2 -----> my Oracle target
jessica."SQL_TEST" jessica."SQL_TEST" :sql@r.jess65 -----> my SQL server target
Note: sql server owner and table name 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
Now you should see capture, reader and post process running.
I created a simple procedure on my source SQL Server like below to insert records into my test table SQL_TEST.
USE [jess2]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE procedure [jessica].[test]
AS
Declare @x int;
set @x=1;
while @x
begin
begin transaction t1;
insert jessica.SQL_TEST (ID, NAME, PHONE, MYDATE)
values (@x,'test','123',getdate());
commit transaction t1;
set @x=@x+1;
END
GO
Then execute the stored procedure
Use jess2;
Execute Jessica.test;
Go
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 captured 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.
To check the speed on replication
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
Check var dir/log/*trace* file for output information
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
Clean up Replication
1. Shutdown sp_cop services with sputil icon on both source and target, check TaskMgr to make sure all SharePlex processes are gone.
2. Run mss_cleansp.exe on source and target DB from prod dir/bin
mss_cleansp.exe –p //
Example:
mss_cleansp.exe -p 9900 jess2/splex9900/splex9900
© ALL RIGHTS RESERVED. Termini di utilizzo Privacy Cookie Preference Center