SharePlex, The MVP of replication football

OK, so remember as a kid coming back to school after winter vacation and the first assignment that always seemed to be given was a 2-page essay on “What did you do for winter vacation." Yuck, how I hated those since most of the time all I did was watch TV or sleep in all day. Hard to fill two pages with that.

But now I’m older, TV is not so much interesting and sleep, well, I have better things to do then sleep the day away. In fact, I need to stay busy or I get very restless, shake your leg while sitting down restless.

And that’s exactly what happened to me during this years “Winter Vacation." Being off the week between Christmas and New Year’s, after a few days in inactivity, I was looking for something to do. I love playing with SharePlex and seeing how far I can go with it before it breaks…. and for the record I have yet to break it.

So, on this night, I was looking at my many SharePlex environments and decided to see if I could get them all to work as one. After an hour of work, I was able to get them all running together as one continuous stream of replication going to multiple different targets running in different environments. Looking at all the different types of targets and how I had replication working between them, reminded me of a football game with all the players running all over the place with the goal of getting the football to its ultimate destination for a touchdown.

SharePlex can be looked at as the Quarterback of your team with the goal of getting you data from one point to another. It can do this in many different ways and to many different targets. Being able to run these “plays” in such a simple and complete way is what makes SharePlex the MVP of replication football.

By the time I had was through for night, this is what I had accomplished.

The diagram and screenshots below are what the finished environment looked like and how the configuration files for each step are setup.

So, a quick overview of what we have. This is what the overall environment looks like with all the sources and targets and what’s going where. Below I will describe it in more detail

The orcl12c1 is an oracle database that is the master of it all. I have a script in the crontab that runs on the 5’s (05,15,25,35, etc…) of the hour that will insert 1000 rows into a table in the database called mike.origin. There is also another script setup that runs on the 0’s (00, 10, 20 ,30, etc...) of every hour that will delete all the data from the mike.origin table. These scripts run 24x7.

The mike.origin table looks like this. The run_time column puts a timestamp so I can keep track of the last inserts

SQL> desc mike.origin
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NOT NULL NUMBER
FNAME NOT NULL VARCHAR2(30)
LNAME NOT NULL VARCHAR2(30)
ADDRESS VARCHAR2(60)
PHONE VARCHAR2(12)
RUN_TIME TIMESTAMP(6)

Now the first “play” our SharePlex quarterback runs. It replicates to a SQL Server database and also to another Oracle database called orcl12c2 at the same time.

Here’s what the config file looks like on the orcl12c1 master

The highlighted lines show how the routes go to the orcl12c2 database and to the windows r.splexdb database

Ok, so heading south to the Windows servers, the second play our SharePlex quarterback runs

Once the data gets to the first SQL Server it then replicates it to the second SQL Server. And this part is like having a new play in your playbook. With the 9.0 release of SharePlex, you can use SQL Server as a Source database, Yes, you heard me right, you can use SQL Server as a source. The data goes in to a table called dbo.origin2 and then that gets replicated to a table called dbo.origin2tgt1

Here is what that config file looks like. The highlighted line shows the routing

And Touchdown! here’s our data in one of its final destinations the last SQL Server database.

OK, so lets go back to the master orcl12c1 database and head east

First stop is to the second Oracle orcl12c2 database and replicate into a table called mike.shard.

From there SharePlex will run 6 different plays all at once. three plays going to three different EDB PostgreSQL databases, one play to Apache Kafka, and the last two going to XML and SQL flat files.

Here is the config file for the orcl12c2 database. The first lines are for the PostgreSQL databases and the last lines are for the Kafka, SQL and XML files.

First we will head farther east where there are three EDB PostgreSQL databases running. The data at that point will be split using horizontal partitioning based on the LNAME column, each going into a table called splex.shard. You can see this below in the shareplex_partition table. It's this table that tells SharePlex how we want to partition the data.

And checking the three databases...

Touchdown again, again and again!!! here is our data neatly split by last name

Postgres1

  

Postgres3

  

Postgres2

                 

Finally, for our last three plays, we will head south from the orcl12c2 database to our final destination. On this last server we have Apache Kafka running at the same time we are creating SQL files and XML files

Looking at the server…

Touchdown Kafka!

Touchdown SQL!

Touchdown XML!

The ability of SharePlex to not only replicate to one target but multiple targets at the same time and able to replicate to different types of targets and use different sources, this is what makes SharePlex the MVP of replication football.

For more information on SharePlex and how it can meet your data replication requirements

                                                

Anonymous