Data transfer betweenm two sql servers

Question

Data transfer betweenm two sql servers

soumyagt

Old Hand

Points: 383
More actions
March 14, 2006 at 2:06 pm

#113746

hi,
I have 2 servers(windows2003 - Sql server2000 developer edition) at 2 different locations.I Want to transer the data between these two sql servers( transactional data).what is the best apprach and how to do this?
it is to be done automatically without user intervention.

thanks
Sree

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

John Rowan SSC Guru Points: 56440 More actions · Answer 1

How often, every day, every hour, real time? How much data? Can you give a more detailed description of what you want to accomplish?

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

soumyagt Old Hand Points: 383 More actions · Answer 2

for every six hours, 2kb - 10 mb

I have a website where I have my Sqlserver on a dedicated server and I have a local Sql server for internal purpose. I want to transfer the data between these two sql servers.

thanks

Sree

John Rowan SSC Guru Points: 56440 More actions · Answer 3

Depending on how many tables you are talking about and how complex the data, I would look into creating a DTS package that can do your data pump and schedule the package to run every 6 hours.

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

Wayne-153714 Hall of Fame Points: 3536 More actions · Answer 4

For simple transfer, DTS is often the best solution but I recommend creating the destination tables first (with primary keys). Don't use the DTS option to delete the contents from the table, rather use a TRUNCATE TABLE command in a seperate step.

For a high availability system, if you need to update the destination table with data from the source table, use DTS to copy the source table to an existing source_staging table which you have truncated in the DTS. In the DTS, add a step to either insert new rows (using LEFT OUTER JOIN and checking for NULL) or update existing rows (useing INNER JOIN) where data has changed on the _staging table. This solution can result in high workload, but will prevent your users from hitting an empty detination table while the data is copying. Try not to copy the entire source table every time, only new records.

When in doubt - test, test, test!

Wayne

Vee Hall of Fame Points: 3028 More actions · Answer 5

I would have done this way.

If implemented simple recovery model, ( with full and differential )

Take a full back and restore at destination and and

Implement differential backup for every six hours and restore the same at destination.

No need to think of primary keys, joins, table creation.

schedule both backs and restore like how logshipping does ..

soumyagt Old Hand Points: 383 More actions · Answer 6

Veeresh,

Can we do differential backups/recovery if the database 'A' ( at remote location) is a subset (few tables) of Database 'B' (local) .

and

I need to transfer data from DB 'A' to DB 'B' and also From DB 'B' to DB 'A' for every six hours.This is becuase some transactions will be done in DB 'A' and in DB 'B'

eg: Table 'qualitycontrol' in DB 'B' may not be in DB 'A'

Thanks

Sree

Scott Coleman One Orange Chip Points: 27452 More actions · Answer 7

You could also try replication. You can replicate a subset of the tables in a database, even a subset of the columns in the chosen tables. It works very well, but imposes some limitations. Identity fields can cause problems, and there are restrictions on table design changes (dropping a replicated column requires stopping replication and starting from scratch).

I have used transactional replication with non-updatable subscribers. It is possible to do two-way transactional replication, but I've never done it. Merge replication is another option that I've never used.

SQL 2005 provides peer-to-peer replication, which may be even better for your application.