locked
Real Time data repliation between Un-normalized Database to Normalized Database (Uni directional) using SSIS? RRS feed

  • Question

  • Hi Guys,

     I am working on mainframe to .net migration project(will go live in 2014 but both will run Parallel in QA and Dev for testing), need your guidance on how to handle real-time data replication between un-normalized database to normalized database.Both are in Sqlserver 2008. We get the Un-Normalized data from other sources(e.g mainframe system, they use 3rd party tool to migrate the mainframe data into SQLserver, row count: > 5 million) so we do not have any control on it.

    Problem with the Un-Normalized Database:
    1) No Date modified column exists


     So, Please suggest me the best way to handle real-time data replication and  How can i achieve this using SSIS? I have already created one SSIS package which runs on demand and compares all rows and takes the modified record(by comparing all columns with old copy DB all columns) to Normalized DB.

    Regards
    RAJYAM.

    Friday, April 13, 2012 2:53 PM

Answers

  • If you are running SQL Enterprise I would look at setting up Change Data Capture on the un-normalized database and then run an SSIS package that constantly loops and looks at the CDC tables.

    http://msdn.microsoft.com/en-us/library/bb522489.aspx

    Alternately you could use SQL Server replication with  customized replication procedures - but that can be tricky

    A low tech solution would be triggers on your non normalized tables - assuming your normalized database is in the same instance or you have  a linked server to your normalized DB


    Chuck


    • Edited by Chuck Pedretti Friday, April 13, 2012 2:59 PM
    • Proposed as answer by Eileen Zhao Monday, April 16, 2012 3:20 PM
    • Marked as answer by Eileen Zhao Thursday, April 19, 2012 7:48 AM
    Friday, April 13, 2012 2:58 PM