October 16, 2006 at 9:41 pm
Hi,
I have read only permission in the source (OLTP) database. The source database is running in SQL Server 2000 and the size is more than 200 GB. I need to pull data from source and load target which is running in SQL server 2005.
Following are the objectives I want to achieve.
Data should be loaded on incremental basis.
Whatever changes take place (Update/Delete) in source, that should be replicated to already uploaded data.
Here I want to mention that, the source database does not have any identification key or timestamp column like Updated_Date by which I can filter the data which are recently inserted or updated into the source and upload the same. The source does not maintain any history data also. So I do not have any track of deleted record also.
I don’t have any scope to change the schema in the source. In this scenario can anybody suggest me the best approach to achieve the above mentioned objectives?
Can I retrieve only the recent updated or inserted date form transaction log back up. Can log shipping solve the give the solution?
One more question. Say I have a table and I am exporting/importing all the data from/to my target table using SSIS or DTS. In this scenario does using query or using directly the table affects the performance?
Regards
Sudripta Rakshit
October 18, 2006 at 1:30 am
Log shipping should be able to help you here. If you are transfering data over a bandwidth sensitive link you might want to have a look at somthing like Red Gate's SQL Backup which will compress (and encrypt) your data for you. It also contains a handy wizard which will do all the hard work of setting up the log shipping for you - you can download a 14 day free trail from http://www.red-gate.com/
Otherwise you might want to look into some sort of replication scheme, it all depends on how in-sync your 2005 server needs to be with your 2000 server - is accurate to the last 10 mins OK or does it need to be to the transaction level?
Hope this helps,
- James
--
James Moore
Red Gate Software Ltd
October 18, 2006 at 6:17 am
I think the first question to be asked is, why are you considering SSIS for this task? The second question is, what do you want to use the destination for?
If the answer to the second question is Disaster Recovery, then Data Mirroring or Log Shipping are your best bets. Data Mirroring doesn't give you a readable DB at the end of the process, though, so if you're looking to get a report server or test server, it's not the best option.
BTW, in response to James' comment about replication, I have to tell you that replication will NOT work for you if the table(s) in question have no primary keys. In order to implement Rep, you'll have to get with the administrator of the Source DB and talk about some schema changes before you can do anything else.
Anyway, back to my original question: Why SSIS? Is there something specific you're doing other than moving data in general?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply