Data synchronization

  • I have two databases in the same LAN, one database is used for (insert/update/delete) data update, the other database is used for data query.

    normally, the size of data change everyday is about 1G, if I want to synchronize data of these two databases, how many solution can be used for this Data synchronization?

    and which one will be the best sollution ? thanks!

  • Are there lots of different tables being changed? If so, the answer is likely to be one of

    1. Replication
    2. Backup / Restore

    If only a handful of table are being modified, SSIS may be a viable solution, possibly along with Change Tracking to help you accurately identify the changes.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    Are there lots of different tables being changed? If so, the answer is likely to be one of

     

      <li style="list-style-type: none;">

    1. Replication

     

      <li style="list-style-type: none;">

    1. Backup / Restore

     

    If only a handful of table are being modified, SSIS may be a viable solution, possibly along with Change Tracking to help you accurately identify the changes.

    1. yes,  many tables have data change, such as, insert new record , update or delete existing record ,and  some table structure was changed or added some table or  some viewes or stored procedure was changed.
    2.  if only a few tables have data change , can we use SSIS to handle it ?

     

    1. Then replication or backup/restore would seem reasonable options.
    2. Probably. It would require some analysis and design work to decide on how best to handle all the changes, and in what order to execute. Whether that is a practical solution depends on the results of this analysis and then on the results of testing.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

     

      <li style="list-style-type: none;">

    1. Then replication or backup/restore would seem reasonable options.

     

      <li style="list-style-type: none;">

    1. Probably. It would require some analysis and design work to decide on how best to handle all the changes, and in what order to execute. Whether that is a practical solution depends on the results of this analysis and then on the results of testing.

     

    Thank you Phil Parkin!

    for the point 1, if we use backup and restore solution, maybe it is not good solution, as backup and restore database won't be done manually? if it right, there will be a big data delay between these 2 databases.

    about 3 years ago I did a test on replication,  if there tables or views  are changed, I'm not sure it can also be synchronized , when I have a time I'll do a testing for this, thanks!

  • You could automate the backup/restore process with a bit of scripting.

    If you have enough space on your 'data query' server to host two versions of the database, you could do this sort of thing, assuming your database is called X

    (source) Backup database X

    (target) Restore database X

    (source) Backup database X

    (target) Restore database X to database X1

    (target) Rename database X to database X2

    (target) Rename database X1 to database X

    and so on. This has the advantage of very little downtime, but the disadvantage of requiring a huge amount of space. It may also be impractical in your case if the RESTORE time is high.

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • Phil Parkin wrote:

    You could automate the backup/restore process with a bit of scripting.

    If you have enough space on your 'data query' server to host two versions of the database, you could do this sort of thing, assuming your database is called X

    (source) Backup database X

    (target) Restore database X

    (source) Backup database X

    (target) Restore database X to database X1

    (target) Rename database X to database X2

    (target) Rename database X1 to database X

    and so on. This has the advantage of very little downtime, but the disadvantage of requiring a huge amount of space. It may also be impractical in your case if the RESTORE time is high.

    well noted, thank you for kind help !

  • about this topic , do you have any other solution? thanks!

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply