Adding new table to SQL Server replication

Owned by Clive Otten (Unlicensed)
Jan 21, 2020
1 min read

How to add a table to running SQL Server replication? First two points are generic. The last point - data instantiation - depends on the preferred tool -  . 

For this KB article, I chose SSMA application. 


Steps Performed

  1. pause apply process in dbvrep console

  2. prepare a table in dbvrep console

  3. Load the table into SQL Server using SSMA application

  4. resume apply process

Point 1 and 2: 

dbvrep> pause apply
dbvrep> prepare table msi.test2 noddl rename to prod.dbo.test2

Copy the SCN number. You'll need it later on. 

 

Point 3:

a) Download SSMA from https://blogs.msdn.microsoft.com/ssma/.

b) Open the SSMA tool and connect to Oracle and SQL Server. Connection to Oracle is easy. To SQL Server you need to know a value for variable Database. It should be a database where you want to load a data. In my case, it's PROD database. 

c) Convert Schema (CTRL+R)

d) Synchronise with Database

e) Add migration settings if you don't have it yet.

SSMA can use Oracle SCN number, but you need to change default settings. To do that follow https://docs.microsoft.com/en-us/sql/ssma/oracle/data-migration-settings-oracletosql and https://docs.microsoft.com/en-us/sql/ssma/oracle/project-settings-migration-oracletosql. Or maybe better see the following picture:

f) Use customer query. Click on tab Data Migration Settings (the right one in the right panel).

Apply changes.

g) migrate data.

Point 4:

dbvrep> resume apply

 

And that's all for today. You can add more than one table for the moment. If you get different SCN number during prepare phase, set the query in SSMA for each table appropriately.