After the good article “A short glance at Attunity replicate” from Franck Pachot, I will continue to explain our test on Attunity Replicate with datatype and how this tool do the mapping between Oracle and SQL Server for numeric datatypes.
Introduction
The first step is to find the mapping between Oracle and SQL Server.
I summarize this mapping in this table:
As you known (or not), in SQL Server, you have a replication with Oracle directly but this functionality is deprecated
In the msdn, you have a default data type mapping here.
For numeric datatype, the mapping is approximatively the same as above.
To complete this part, you can also specify alternative data type mapping here.
This functionally is deprecated and I decide to find an alternative. I find that Attunity replicate is a good way to replicate from Oracle to SQL Server near real time.
Attunity Replicate has a self-datatype table for the mapping.
In the user guide from Attunity Replicate, you have the mapping between Oracle and the Attunity Data types.
If I summarize my previous mapping, I have this mapping:
Then next step is to realize the mapping between the Attunity data type and the SQL Server data type, as explained in the user guide.
The mapping will give us this mapping:
Oops, I have some “no match” like BIT, TINYINT, BIGINT, DECIMAL, MONEY or SMALLMONEY due to the intermediate Attunity data type mapping
This is the theory, and now let’s go to the practice…
Create the source & destination connections
To create the task, follow the blog by Frank here.
In my case, I just change the destination database with a SQL Server connection
Add SQL Server image
One thing to not forget is to test the connection with the button on the bottom-left of the connection window
attunity_replicate07.png
Create the source table and destination table
With SQL Developer, I create in Oracle a table NUMERIC_DT in the schema OE to have all numerical data types from my example
CREATE TABLE OE.NUMERIC_DT ( NUMBER_BIT NUMBER(1), NUMBER_TINYINT NUMBER(3), NUMBER_SMALLINT NUMBER(5), NUMBER_INTEGER NUMBER(10), NUMBER_BIGINT NUMBER(19), BINARYFLOAT_REAL BINARY_FLOAT, BINARYDOUBLE_FLOAT BINARY_DOUBLE, FLOAT_FLOAT FLOAT, REAL_FLOAT REAL, NUMBER_NUMERIC NUMBER(30,20), NUMBER_DECIMAL NUMBER(20,10), NUMBER_MONEY NUMBER(19,4), NUMBER_SMALLMONEY NUMBER(10,4) )
With Attunity Replicate, I use the console to replicate my table through the web browser.
Then Select the schema OE in Oracle and now the table NUMERIC_DT.
Now, I start the replication with the Run button
The first action when you start the replication is a Full load.
In my case I just create the table without data so you have “Transferred count” value set to 0 in the monitoring
I start SSMS to see the table that Attunity created in the SQL Server destination
The result matches with my theorical table above.
To have a comparison, my colleague Vincent Matthey do the same test with the tool SQL Server Migration Assistant (SSMA) for Oracle
With SSMS, we can see the result
The result is a little strange… As you can see, all defined SQL Server integer datatype like TINYINT, SMALLINT, … are NUMERIC with a scale of 0 to have integer! For example, the NUMBER(1) becomes a numeric(1,0). It is not false but also not right at all!
Insert Data
I create a script to put some data in this table, to see if the replication works:
BEGIN for i in 1..15000 loop insert into OE.NUMERIC_DT (NUMBER_BIT,NUMBER_TINYINT,NUMBER_SMALLINT,NUMBER_INTEGER,NUMBER_BIGINT ,BINARYFLOAT_REAL,BINARYDOUBLE_FLOAT,FLOAT_FLOAT,REAL_FLOAT,NUMBER_NUMERIC ,NUMBER_DECIMAL,NUMBER_MONEY,NUMBER_SMALLMONEY) VALUES( TRUNC(dbms_random.value(0,1)), TRUNC(dbms_random.value(1,255)), TRUNC(dbms_random.value(1,32767)), TRUNC(dbms_random.value(1,2147483647)), TRUNC(dbms_random.value(1,9223372036854775807)), dbms_random.value(1,9999999999), dbms_random.value(1,9999999999), dbms_random.value(1,9223372036854775807), dbms_random.value(1,999999999999999999999999999999999999999999999999999999999999), dbms_random.value(1,9999999999), dbms_random.value(1,9999999999), dbms_random.value(1,999999999999999), dbms_random.value(1,999999)); end loop; commit; END;
In the monitoring tool from Attunity, you have a powerful graphical monitoring to see all operations during the replication
To be sure that the replication has correctly replicated the 15000 rows, I do a select in the SQL Server destination database and I see that I have all rows.
Update data
Just to see the power of this interface, I run an update on Oracle side of all rows with NUMBER_BIT equals 0:
UPDATE OE.NUMERIC_DT SET NUMBER_BIT=TRUNC(dbms_random.value(0,1)), NUMBER_TINYINT=TRUNC(dbms_random.value(0,255)), NUMBER_SMALLINT=TRUNC(dbms_random.value(1,32767)), NUMBER_INTEGER=TRUNC(dbms_random.value(1,2147483647)), NUMBER_BIGINT=TRUNC(dbms_random.value(1,9223372036854775807)), BINARYFLOAT_REAL=TRUNC(dbms_random.value(1,9223372036854775807)), BINARYDOUBLE_FLOAT=dbms_random.value(1,9999999999), FLOAT_FLOAT=dbms_random.value(1,9999999999), REAL_FLOAT=dbms_random.value(1,999999999999999999999999999999999999999999999999999999999999), NUMBER_NUMERIC=dbms_random.value(1,9999999999), NUMBER_DECIMAL=dbms_random.value(1,9999999999), NUMBER_MONEY=dbms_random.value(1,9999999999), NUMBER_SMALLMONEY=dbms_random.value(1,999999) WHERE NUMBER_BIT=0; COMMIT;
You can see that I have in the Oracle source 9986 rows
And the same on SQL Server destination
I apply the update…
In the Attunity monitoring, you can see in the changing Processing that I have 1 transaction in the incoming changes and I can see the latency. In my case, my lab is not set to have good performance…
When the transaction is applied on the destination, you can see that the incoming changes has 0 transaction and that the Applied Changes pie has now the update stats.
As you can see, the monitoring interface is very powerful!
And in live, it is even better 😉
Delete data
The last command to test is the delete command
DELETE FROM OE.NUMERIC_DT WHERE NUMBER_BIT=0; COMMIT;
After this command, with the monitoring interface, you can see the 3 commands:
And in the destination database, you can see that the delete was applied.
Conclusion
This tool is very good for a replication between heterogeneous database systems.
The creation of a staging database on the SQL Server destination from an Oracle source is very simple to deploy and use, as you can see.
Finally, if you are interested by this blog and see more, I invite you to come in our free Event “Replication road show” who Vincent Matthey, David Barbarin and I, will present the replication between Oracle and SQL Server with Attunity Replicate and complete this demo with also change on DDL command like change a data type on a column or add a calculated column, etc.