September 11, 2006 at 7:19 am
Hi,
Can DTS be used to move only the changed data from one database to another?
Here is the scenario:
We currently have two SQL Servers, connected using a slow network. The main SQL Server is holding a 40 GB database, and is being used by the customer. On the secondary SQL Server we need to create the same database. The customer would like to have the changes that were done on the Main database to be implemented on the database residing on the secondary SQL server. We expect around 40 mb of data change per week. Can DTS be used to move only the changed data from the main database to the secondary database?
We would not want to do a back\restore of the 40 GB database every week.
Thanks
Ravi
September 11, 2006 at 7:33 am
That is possible but to do that you need to have a datetime stamp on the table that you want to move and an indicator in all the table to identify if and when the data has changed.
In your case automating back and restore would be ideal or have you considered replication on log shipping. Hope this points you in the right direction.
Thanks
Sreejith
September 11, 2006 at 7:59 am
Thanks Sreejith.
I kind of had a similiar thought of having a datetime stamp column, but having an indicator in all the tables to identify the changes would be a huge task. Also, the customer wanted to have a solution in next few days.
Our customer wanted to see if it could be done through DTS. I just wanted to double check before i respond to his questions.
I was vouching for a complete\differential backup\restore solution.
Thanks
Ravi
September 11, 2006 at 9:25 am
Ravi,
If you don't go with replication or log shipping, you could still use DTS if you have a table to use as a driver.
Rather than have a "change" indicator in each table, you could create an update history table and write to it when an update is made to one of the production tables. The history table would contain a timestamp and the key information for the table that was updated.
DTS would join the history table with the changed table and select the columns that need to be updated in the standby database.
Greg
Greg
September 11, 2006 at 10:31 am
Thanks Greg!
Ravi
September 14, 2006 at 8:34 am
You could use dts and some programming to do this, or just some programming.
I would use log shipping if I could as the first choice.
Transactional replication sounds like it might also be a possibility.
If none of these are allowable, then you will have to determine which records in which tables have been changes. To do this you can use the checksum() functioin to come up with a number for the record and compare the checksums for the rows in each table, copying the records where the checksum value is different. Depending on the speed of your connectioin you could generate the checksums ahead of time into temp tables on each server and compare at a later time, or you could do it dynamically and grab the rows that have changed into a temp table for import into the other site.
This can be done with a little programming and the use of ssis or just as a sp in a job.
John
September 14, 2006 at 3:51 pm
replication is good a choice, you can control how/when you want to do the move.
another way is, what about using timestamp datatype on source and varbinary(8) on destination. This is how I used to copy transactional data from source to staging database on a daily basis.
September 15, 2006 at 2:17 am
Hi All,
Thank you for all your advice.
I have implemented replication.
Ravi
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply