June 25, 2003 at 7:04 pm
Hi,
We have a database externally that we need to update from an internal database over a 100MB connection.
There is only about 100,000 in the tables in question and there are minimal changes to the data each day that need to be copied out to the external database.
My thinking was down the line of one of the following:
1. snapshot replication, or
2. bulk insert
What are everyones thoughts?
Edited by - trharris@labyrinth.net.au on 06/25/2003 7:05:26 PM
June 25, 2003 at 8:48 pm
Hi
With the info provided, I would consider replication, simple enough and effective. Do you need to go back the other way or it will be 1-way replication only?
Another option is simply a bunch of "smart" triggers that do the same sort of thing, perhaps into a staging table and reapplies the DML back in order at the other end. We did this between oracle and sql server and very rarely failed (it was a super huge courts based app).
If you do go down the replication end, take the time to setup a test env and walk yourself through the recovery senarions.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
June 25, 2003 at 8:57 pm
Thanks Chris.
Yes, it is 1-way only.
As the data exterally is not system critical I thought to simplify the administration internally of creating a DTS package that wrapped up the following:
1. truncated the external table
2. execute insert into....select
Obviously this limits the recovery options but do you see this as being a bad solution? The reason I've looked down this path is that only a subset of data will be extracted now and I need to do joins on tables.
June 25, 2003 at 9:30 pm
Hi there
Yep, the KISS methodology is always the best option. Wrapper in and transaction perhaps just in case there are issues, or goto temp tables first then "cutover", its always better not to refresh and have a full db than a partially refreshed one with missing data.
Cheers
Ck
Chris Kempster
Author of "SQL Server 2k for the Oracle DBA"
Chris Kempster
www.chriskempster.com
Author of "SQL Server Backup, Recovery & Troubleshooting"
Author of "SQL Server 2k for the Oracle DBA"
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply