Data sync between old and new DB in real-time RRS feed

  • Question

  • We are currently running SQL Server 2014 with a 3-node AlwaysOn cluster. The main transactional database is going through an overall and 
    all systems connecting to it, is being re-designed. Once the new DB and systems are ready, it will go live at 1 of our clients as
    a pilot phase.
    But during this time, all changes made the current (old) DB must sync to the new DB AND visa versa. And this should happen real-time.
    The structures of the old and new DB will be different - name changes, column changes, table changes, etc.
    I was wondering what would be the best way to do this?
    The current DB is about 850 GB and is fairly busy, with 1000's of users connected at any time and a big volume of 
    transactions (reads, write, proc executions...) running all the time.
    So whatever means of synch I use, should not have a negative effect on the user experience. 

    Any suggestions would be appreciated!

    Friday, February 3, 2017 9:11 AM

All replies

  • I'm not sure it's possible to 'sync' two databases that have different schema's.

    You may have to build some custom SSIS ETL to accomplish what you're after.

    [Please mark answers that solve your problem]

    Hope that helps,

    Phil Streiff, MCDBA, MCITP, MCSA

    • Edited by philfactor Friday, February 3, 2017 2:20 PM
    Friday, February 3, 2017 1:09 PM
  • >You may have to build some custom SSIS ETL to accomplish what you're after.

    Yes.  I would recommend SSIS, possibly in combination with Change Data Capture to track changes on the old database and allow some of the SSIS packages to process incremental change sets.

    Another option it to put an AlwaysOn readable replica on the new server, to facilitate fast loads between databases.


    Microsoft Technology Center - Dallas

    My Blog

    Friday, February 3, 2017 1:15 PM