July 22, 2008 at 8:36 am
I'm posting this question looking for assistance in finding a solution for a 3-tier replication issue we are having.
We have one SQL Server 2000 Enterprise edition database on Network A. In that server, there is 1 database we need to replicate data from to a SQL Server 2005 Enterprise edition on Network C.
Because Network A and Network C do not have direct communication, we have a middle tier, network B that is going to act as a middle man. SQL Server 2000 Enterprise edition is on Network B.
Here are the fine points. We only need data from about 10 tables within this database. We do not need the entire database. This is a daily transfer of data that needs to occur.
Does anyone have any thoughts on the best method to accomplish this? Log shipping from A to B then to C? Extract of 13 tables to flat files and then transfer? Database backup of entire database, zip and restore?
Total data right now is about 915,000KB and 325,000 rows. This is about 1 year of data so we're not talking a ton of data.
Thank you for any insight.
July 22, 2008 at 9:52 am
This may sound a bit random, but could you create a bcp file for the tables that are needed and copy from A to a network share on B and then create a job on SQL server C to pick up the file and apply the data?
That way you are not wasting time restoring the data on SQL B if it is not needed?
Regards,
Phil
July 22, 2008 at 9:55 am
I could but one detail I forgot to mention. Network A an B are in the same building but Network C is not. Transferring the entire table bulk from B to C could take a little long at 900 MB. That is certainly 1 option though if needed.
July 22, 2008 at 9:58 am
siverson (7/22/2008)
I could but one detail I forgot to mention. Network A an B are in the same building but Network C is not. Transferring the entire table bulk from B to C could take a little long at 900 MB. That is certainly 1 option though if needed.
Is the 900MB the full DB size or just the required tables?
Regards,
Phil
July 22, 2008 at 10:04 am
That is the amount I get when adding up the Data column from sp_spaceused tablename for each table.
That is, for each of the 10 tables I need.
July 23, 2008 at 2:34 am
Maybe you could use triggers to capture just the data changes (inserts, deletes and updates) only to another table and then bcp that file to the share?
Regards,
Phil
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply