November 2, 2020 at 1:31 am
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!
November 2, 2020 at 2:10 pm
Are there lots of different tables being changed? If so, the answer is likely to be one of
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 2, 2020 at 3:06 pm
Are there lots of different tables being changed? If so, the answer is likely to be one of
<li style="list-style-type: none;">
- Replication
<li style="list-style-type: none;">
- 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.
November 2, 2020 at 3:58 pm
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 3, 2020 at 12:35 am
<li style="list-style-type: none;">
- Then replication or backup/restore would seem reasonable options.
<li style="list-style-type: none;">
- 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!
November 3, 2020 at 9:09 pm
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 4, 2020 at 12:35 am
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 !
November 9, 2020 at 6:48 am
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