SQL Server Replication to Oracle

Wow, it’s been 4 months since I posted.  It’s been crazy though, with kids back at school, wife having knee surgery, and tons of work to do in the office.  As a matter of fact the only reason I have time to write this is because my connection to my PC at work got lost due to some network issues.  Wroking from home can have som advantages! ; )
 
So I thought I’d write a little about what I’ve been working on.  We are upgrading the back end of our POS system from SQL Server 2000 x86 (YIKES!) to SQL Server 2008 x64 (YIKES!).  Since we send data to Oracle from this system it is going to require several changes.  If you didn’t know Replication Publications are no longer transformable.  This was a great feature for us because our app uses a column with a name that is over 30 characters long.  This is a no-no in Oracle so we had a DTS packaged that matched the two different column names during the replication process.  The new solution is… Indexed Views!  Please hop on over to MSDN for the juicy details on Indexed View creation.
 
So here’s the basics.  You need to create a view of the table in question and then create a clustered index on the view.  So there are some special settings you want to keep in mind.  Make sure setting below are set correctly
 
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;

 

Then create your view using WITH SCHEMABINDING.  This piece is extremely important and required for publishing.

CREATE VIEW [vTable1]
WITH SCHEMABINDING
AS
SELECT
   Store_Loc_ID,
   Cust_ID,
   Cust_Name,
   Cust_Column_With_A_Really_Long_Name as Cust_Column_Short
FROM
   [Table1]
 
Now we set up the index on the view
CREATE UNIQUE CLUSTERED INDEX IDX_vTable1
   ON vTable1 (Store_Loc_ID, Cust_ID)
 
Then on to the really fun part, creating the publication.  Walk through the process selecting your publishing database and type of replication.  When selecting the article you should see Indexed Views.  Select you view vTable1 then modify the article destination to be the Oracle table name then continue to the end, but choose to script the publication to a query editor window, not create it.  Go to the end and look for "exec sp_addarticle".
 

EXEC sp_addarticle @publication = N’Indexed_View_Test’, @article = N’vTable1′, @source_owner = N’dbo’, @source_object = N’vTable1′, @type = N’indexed view logbased’, @description = N”, @creation_script = N”, @pre_creation_cmd = N’none’, @schema_option = 0x0000000000000001, @destination_table = N’Table1′, @destination_owner = N’sa’, @status = 16

 
The @type variable will be set to ‘indexed view schema only’.  Change it to ‘indexed view logbased’ then run the script to create your publication. 
 
All that’s left to do is add your the subscriber and your ready to go!
 
I’m also working on a another few posts that I hope to have out soon covering HTA’s, SQL cursors, and using .NET CLR in SQL Server 2005/2008.
 
Hope everyone is having a happy holiday season!
 
 
 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s