SSIS vs. Oracle GG

This is the third in a series of product identifications between Oracle and Microsoft to assist those attempting to understand the similarities and differences between these essential parts of any data platform environments.

In this post, I’m going to describe the similarities and differences between Microsoft SQL Server Integration Services and Oracle Golden Gate. Hang on, it’s a bit of a bumpy ride. as neither service is out there on its own, (other’s piggy back off of them) and there’s definitely some new products on the Microsoft side that aren’t taken into consideration.

The first thing to know is that this is not taking all the detailed features into each product into account. Both are essentially self-service products, so if someone wants to build something in the product, even if it wasn’t originally designed for it, they most likely will be able to. I’m looking high level at the original purpose for the product and how I can create, (as close as possible) apple to apple comparison environment for the two.

The second is that the diagrams should help you visualize how they are different and the fundamental architecture for each.

Microsoft SSIS

SQL Server Integration Services, (SSIS) has been around a LONG time, first shining in the SQL world as Data Transformation Services, (DTS) back in SQL Server 7. The product uses a SQL Server repository to store integration processes in groups, called packages and are a bedrock of many ETL processing in the SQL world. With the introduction of Azure cloud, Azure Data Factory, (ADF) includes the ability to integrate SSIS packages into it’s offering, creating an SSISDB repository database, making the most of customers existing integration packages and helping them move into newer capabilities with ADF data flows and pipelines.

To create something that does appear to fulfill the same feature overlap as Oracle GG, I added in log shipping, as Oracle GG does handle replication better, (IMHO) than SSIS.

SSIS with Log Shipping

Oracle Golden Gate

Oracle Golden Gate also grew out of a predecessor product, first known as Change Data Capture, (CDC) which was a set of stored procedures and packages inside the Oracle database, then Oracle Streams, taking it one step further, till it evolved into the Oracle Golden Gate product.

To create a similar diagram to the SSIS one, I’ve incorporated a secondary Oracle product, Data Integrator, to help grant us an apple to apple view, as I feel SSIS does a better job at integration of data than Oracle GG does.

Oracle GG with ODI

The Gist

Both products use a database repository to store and perform essential processing, but as Oracle GG sourced from the need to move data, especially data migrations to engineered systems, (which it was almost essential to ease this migration scenario) it is very easy to install, easy to use and has little impact to the database, as it uses the log data to perform the process, including updating transactional processing post the initial load. In the SQL world, log shipping is the tool of choice to perform this type of processing, (even more so than replication, just as Oracle GG has replaced older forms of replication with its own version.)

SSIS is a product that has stood the test of time, packages being valuable enough that they’ve integrated the product into Azure Data Factory. The ease of use for those that have been using it for a couple decades makes it simpler for companies to move to the cloud without fear of having to migrate all their ETL work over from scratch.

Hopefully the scenario I’ve created in the diagrams help understand how the products overlap and how they are different, adding in the two secondary products to help balance them out.

With that, I’m going to bed- it’s a busy time of the year here in Microsoft land. 🙂

Diagram

The two products do

Author: dbakevlar

http://about.me/dbakevlar