Real Time data repliation between Un-normalized Database to Normalized Database (Uni directional)

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 modifed column exists


 So, Please suggest me the best way to handle real-time data replication.

I have suggested few Approaches which may be correct or not.  Please help me in this regard.

Option 1: write a trigger on Un-Normalized tables and execute Procedure to Insert/Update Normalized Database respective tables

Option 2: Use 3rd party tool to compare all 5 million rows data once in a day and update Normalized Data

[can not say real- time, always 1 day delay]

Option 3: Create a Date Modified column in the Un-Normalized database tables , Create Trigger on tables and update Date modifed and Create jobs in the New database and update the tables based on DateModified

[management not agreed with this option]

Option 4: Modify MainFrame application with date modified column

[which is ruled out, management not agreed with this option]


Regards
RAJYAM.

Answer 1

Replication is used to transfer the same data between instances, so you cannot replicate the un-normalized data to a normalized form. What you are looking at is more of an ETL task. Try asking in the SSIS forum.

About (transactional) replication, you do not need a date modified column. The log reader agent will know which records are new and need to updated on the subscribers.

---

Stanley Johns K. MCTS/MCITP in SQL server 2008.

Answer 2

Thank you Stanley. Sure i will post my requirement in SSIS forum.

What do you suggest if i want to implement real time replication on UN-normalized data to a normalized form? My aim is to achieve the real-time replication.

You said, "you do not need a date modified column. The log reader agent will know which records are new and need to updated on the subscribers." Can it possible to do customized transactional replication(like through SSIS or By executing my own procedure)? I did not find any good article on it. Please help me.

Answer 3

Hi R.A.Y.Y.A.M,

Regarding to your description, seems you have issue about m Moving un-normalized SQL data source to normalized SQL data destination . Please try to to use the unpivot component: http://msdn.microsoft.com/en-us/library/ms141723(v=sql.100).aspx

Meanwhile you might also have a look at Jamie Thomson's SSIS Normalizer component. http://consultingblogs.emc.com/jamiethomson/archive/2009/04/23/normaliser-component-v2-ssis.aspx

There is another two threads related to un-normalized SQL data source to normalized SQL data destination
1. http://social.msdn.microsoft.com/Forums/en-US/sqlintegrationservices/thread/2bc12792-1833-4263-b92d-1ae2f6abdaf2
2. http://database.itags.org/sql-server/327167/

For further help, please link to SSIS forum.

---

Regards, Amber zhang

Answer 4

You should be able to read the change journal to discover which rows have changed. Then it is a matter of moving them to the SQL Server databases.

There are products out there that do this Attunity and Data Mirror.

You can also roll your own log reader using a code sample provided with the SQL 2000 code samples.

---

looking for a book on SQL Server 2008 Administration? http://www.amazon.com/Microsoft-Server-2008-Management-Administration/dp/067233044X looking for a book on SQL Server 2008 Full-Text Search? http://www.amazon.com/Pro-Full-Text-Search-Server-2008/dp/1430215941