The new SSIS CDC Data Flow Task is one the best and little known features for 2012. If you have a data source that is either SQL or Oracle now you have no excuse not to do CDC. Along with CDC I would highly recommend that you give the ETL the ability to do good old fashion RBAR (row-by-row) delta detection. I have already built an example SQL 2008 CDC Framework.
The following post from Rakesh Paria greatly helped me understand how to configure the Oracle server for SQL 2012 CDC:
Before creating the service, you must ensure that the following configuration steps have been performed on your source Oracle database:
1. The database is set to ARCHIVELOG and is OPEN
2. The user that is going to connect to the Oracle database has DBA privileges on the database (since you will look up the log miner data from the database)
You should also install the Oracle client and configure the service names on the machine where you will be installing the CDC services on.
Installing the CDC Service Configuration and Designer MMCs
The MMCs are not installed by default when you install your SQL Server instance. You must install them separately by installing them using their MSIs.
The MSI installer for CDC Service Configuration MMC is named AttunityOracleCdcService.msi and the installer for CDC Designer is named AttunityOracleCdcDesigner.msi. They can be found in your installation media under the folder \Tools\AttunityCDCOracle. In the folder you would find x64 or x86 folders which correspond to the different processor architectures. Within the x86 or x64 folder you would find folders for various locales including 1033 which corresponds to English (US).
Thus, to install the English (US) x64 version you must go to \Tools\AttunityCDCOracle\x64\1033 folder.
Once installed, you would find a shortcut created under the start menu at Start>Program>Attunity à Change Data Capture for Oracle>CDC Service Configuration
Note You must install the same bit version of the MSI as the Oracle client installation you have on the local system. Thus, if you have installed the x86 version of Oracle client then you must also install x86 version of the Oracle CDC service.
Setting up your service
You can set up a new CDC Service using the CDC Service configuration MMC. But before you create a new service, you will have to prepare a local SQL Server instance. You can do so by using the actions pane in the MMC as shown below.
Once the local SQL server is prepared you can use the New Service action to create a new CDC service as shown below.
Couple of things to note here:
1. The service account you use should have logon as service policy assigned to it
2. The master password will be used to create a asymmetric key which will be used to encrypt the Oracle credentials which will be provided later
Once the service is created you can see it under the services node.
Creating a CDC instance
You can create a CDC instance using the CDC designer configuration MMC which you installed earlier. A CDC instance is defined as an object consisting of:
· The source Oracle database connection information
· Credentials and properties to connect to the source Oracle databases
· Source tables for which you need to capture CDC. You can even configure the instance to capture only a subset of the columns of each table.
· Mirror SQL Server database name which will contain the mirror tables for the source tables/columns defined in the previous step
You can create the instance as shown in the following steps
1. First you should connect to the SQL Server instance on which you created the service
2. Now you can see all the services available for the instance
3. Now you can create a new instance using the right click context menu for a service
4. The Oracle CDC Instance wizard opens up and the first pane allows you to define a service name. It will also ask you the name for a mirror database that would be created on the same SQL Server instance as step 1. You can rename the mirror database as well.
5. The next steps will ask you to input the Oracle connection string as well as the user credentials for connecting to the Oracle database. Note that it takes the Oracle connection string in the following format: host[:port][/service name]
Note that you should have installed the Oracle client and setup the service naming before this step.
6. Now you can select the tables you want to include in your CDC instance.
7. You can also edit the instance and select specific columns as well as specify a CDC role to be used when creating the CDC instance on the local SQL server database
8. In the next steps you would asked to run a script on your source Oracle database which essentially sets up Supplemental logging
9. You can click through the wizard to Generate the Oracle CDC instance
Operating a CDC instance
Once you have created the CDC instance you can now start and monitor the CDC instance using the designer MMC. By default the Service would be in INITIAL (stopped) state. You can start it as shown in the figure below.
The service will be started and the status would inform you of the current state of the service, transactions’ SCN for Oracle and the number of transactions being processed at that instance as shown below.
You would notice that a database would be created on the SQL Server instance you specified and the mirrored table would be setup. CDC would have also been enabled on the table and a corresponding _CT table would have been created capturing the changes from the mirrored table as shown below.
This post has been an introduction on how to get up and running with the new CDC for Oracle features introduced in SQL Server 2012."