Results 1 to 14 of 14
  1. #1
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10

    Unanswered: Synchronising Databases with Different Schemas

    Hey folks,

    I'm looking to gather ideas on this before I start the project; so what better way to start than to ask here?

    The situation is this: we are in the process of replacing a large data driven application with a new one. Before turning the old'un off (that's not for a minimum of 6 months), we need to migrate data between the two entirely different schemas, and I've been tasked with writing the interfaces.

    Now, here comes the kicker; they don't just want a "straight-forward data conversion", they want to run a period where they can still update both databases and any changes in the old one are to be pushed into the new db.

    Because the schemas are not the same, I believe replication is out of the question so I was looking for advice as to what possibilies I have ahead of me to achieve this.

    I fear I may not have expained this well enough; so as always if there are holes in the detail let me know and I'll do my best to fill them in

    Old db = SQL Server 2000
    New db = 2005

    Thanks,
    George
    George
    Home | Blog

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    they want to run a period where they can still update both databases and any changes in the old one are to be pushed into the new db.
    Do you need two way or one way synchronisation then? And how close to real time do these synchronisations need to be?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Hi, georgev.

    You can use replication, however you might need user triggers on the published tables on subscription and on pulication as well (for two way replication).
    The published tables will have the same schema, however, your user triggers will kick off on DML and from the code in the triggers; you can populate the destination tables(with different schema).

    eg!
    PUBLISHER:
    TABLE = EMPLOYEE (NAME, DEPT)
    SUBSCRIBER:
    TABLE = EMPLOYEE (NAME, DEPT)
    TABLE = EMPLOYEENEW (name, blah ,blah)

    Setup replication by publishing EMPLOYEE table.
    Then create user triggers for insert, update and delete on SUBSCRIBER.EMPLOYEE and write the code to do same dml in your query to populate the EMPLOYEENEW table.!
    However, 2000 to 2005 replication publishing is not allowed i belive, you might need to setup 2005 as publisher with 2000 as subscriber with merge or tran with updatable subscription.
    Last edited by thebeginner; 04-28-08 at 09:00.
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by pootle flump
    Do you need two way or one way synchronisation then? And how close to real time do these synchronisations need to be?
    One way I believe, and "real time would be preferred"
    George
    Home | Blog

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by thebeginner
    You can use replication, however you might need user triggers on the published tables on subscription and on pulication as well (for two way replication).
    The published tables will have the same schema, however, your user triggers will kick off on DML and from the code in the triggers; you can populate the destination tables(with different schema).
    I had thought about that; it's a good idea because it disconnects the triggers from the live environment.
    Quote Originally Posted by thebeginner
    However, 2000 to 2005 replication publishing is not allowed i belive, you might need to setup 2005 as publisher with 2000 as subscriber with merge or tran with updatable subscription.
    Damn, I was hoping I could do it that way round.

    Can you elaborate on this last point; I'm not too sure what you're getting at.

    Appreciate the input, thank you
    George
    Home | Blog

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by georgev
    One way I believe, and "real time would be preferred"
    If it is one way and real time is not strictly necessary then you could just write your migration routines and run them nightly. You would probably want to put some sort of row versioning in the source DB unless this is a tiddly thing that you can rebuild each night. The investment in time for this would be little more than the migration work you have to do anyway.

    Would someone have to make a decision on the ROI for putting something together for more regular updates? Near-to-real-time updating would, I would guestimate on the back of a postage stamp. extend the migration development hours by a factor of between 1 and 2. Also bear in mind the plug will be pulled on that work in 6 months anyway.

    Is the 6 month requirement just to run some reconcilliations?

    In your position I would put together some written plans for a few senarios, detailing what is possible for what time\ HW etc investment and get a decision off someone.

    $0.02. And no - I don't have any change.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, I'm not planning on starting anything just yet; gathering ideas for proposals, just finding out which proposal I should weight so that it's the one they pick
    Quote Originally Posted by pootle flump
    detailing what is possible for what time\ HW etc investment
    What's "HW"?
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    HW = hardware. There is probably no additional requirement for this in your senario options TBH.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Quote:
    Originally Posted by thebeginner
    However, 2000 to 2005 replication publishing is not allowed i belive, you might need to setup 2005 as publisher with 2000 as subscriber with merge or tran with updatable subscription.

    Originally Posted by georgev
    Damn, I was hoping I could do it that way round.
    SQL2k5 as publisher and sql2k as subscriber works, however vice versa has issues. You might need to setup distributor on sql2k5.
    Please check Publication Compatibility Level Behavior in SQL Server 2005:
    http://msdn2.microsoft.com/en-us/library/ms143241.aspx

    http://sql-server-performance.com/Co...s/t/26074.aspx
    Cheers!





    !Direction is More Important than Speed!

    http://talatnauman.blogspot.com/

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Heh heh - we are probably talking 6.5 replicating to 2k with George
    Testimonial:
    pootle flump
    ur codings are working excelent.

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    I was as shocked as you when they told me I'd be let loose on a brand new, shiny 2005 instance!
    George
    Home | Blog

  12. #12
    Join Date
    Jan 2004
    Location
    In a large office with bad lighting
    Posts
    1,040
    Quote Originally Posted by thebeginner
    However, 2000 to 2005 replication publishing is not allowed i belive, you might need to setup 2005 as publisher with 2000 as subscriber with merge or tran with updatable subscription.
    You are sadly mistaken. 2000 publisher and 2000 distributor will snapshot and transactional replicate to 2005 subscriber. Have not yet tried 2000 publisher, 2005 distributor, and 2005 subscriber.

    -- This is all just a Figment of my Imagination --

  13. #13
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    The usual way of replicating the changes from one to the other is to have all the users do 'double entries". So anything they put in one is put in the other. usually, there is some sort of report generated to show what was entered in one, and what was entered in the other, so the management can look important by comparing the two lists. This system has several benefits:

    1) Everyone gets involved and feel like they are "part of the solution"
    2) The users quickly get tired of doing double work, and loudly demand that the new system be made live with all haste, which reduces costs associated with Quality Assurance testing.
    3) Since management need to review reports looking for discrepancies, they are more than willing to allow the new system to go live, so they can get back to their usual three martini lunches...er...Proactively generating new revenue streams.

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    It's a great idea; and one that will be happening in 6 months. This initial set up is for the 20 or so chosen business users to use and for us lots in IT to do our "live" testing; or so I'm led to believe.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •