Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
deepgeorge
Starting Member
6 Posts |
 Posted - 2008-05-27 : 01:55:29
|
| I have an issue related to data migration.We are developing a data migration tool that transfer data from the live sql server (used by customer) to another sql server. we use SSIS packages to perform the migration. we have a engine (developed in c#) that reads the SSIS package and executes it.Now, as the source sql server is live, it is certain to undergo changes during the data transfer operation. Changes would mostly be insert or update. After the data migration is completed the delta has to be taken care of.I am looking for an efficient mechanism to transfer the delta from the live sql server to the target sql server. |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-27 : 02:28:52
|
| Are you just talking about the deltas?If so it depends on the nature of the data as to the best way of doing it.Do you have anything that says when the data was changed?Why are you getting involved with c# to execute the package?I can think of little benefit from doing that but would be nice to know.Have you thought about log shiipping for this? Could solve a lot of your issues.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-05-27 : 02:30:20
|
| Do you have a date field in your source table? then you can always extract only those data that has date value greater than the maximum date value of destination server table. This will ensure you take only delta (those data that changed) after the last migration.Alternatively You also have a Slowly Changing Dimension task which compares the source and destination and takes only the delta changes. |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2008-05-27 : 02:32:27
|
| >> Do you have a date field in your source table? then you can always extract only those data that has date value greater than the maximum date value of destination server tableActually you can't. SQL Server is only accurate to about 3 ms so you could miss changes if you do this. If you do a greater than or equal and compare for changes then it should work thopugh.It's not a good idea to use the max date in the table but better to save the date of the last completed update in another table and use that.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
deepgeorge
Starting Member
6 Posts |
Posted - 2008-05-27 : 02:51:25
|
| This is about deltas. we are using c# to execute the packages because it provides greater customization. there are some post processing performed by our application after the data migration. hence it becomes mandatory that our tool/application is in control of the migration. hence we have created this engine to kick of the execution of ssis package. we also retrieve the status of execution and use it for post processing. Does Log shipping require manual interventation? if yes, we may not opt for it. Also, as i mentioned earlier the source sql server is live. dont know how effective log shipping would be. even replication service is out of the question as it may cause performance hits in the source sql server. most of the tables do not have a datetime field. i've heard that in oracle each table implicitly maintains a timestamp per row. do we have something similar in sql server? |
|
|
|
deepgeorge
Starting Member
6 Posts |
Posted - 2008-05-27 : 05:00:46
|
| Let me rephrase the question.Is there a way to find out the timestamp of inserted/updated and deleted records in a table ? |
|
|
|
|
|
|
Migrating data from an sql server to another.