0

I have two (linked) Microsoft SQL servers with basically the same setup but they differ in the content. Connection with servers is slow. Some tables contains lot of rows. Some records differ in the fields. Data on each servers match for 99%

I need to merge data from one server to other with minimum traffic transfer.

For example:

SERV1.DB1.dbo.Table:
| fname       | count |
----------------------
| 'file1.txt' | 10    |
| 'file2.txt' | 5     |
| 'file3.txt' | 35    |


SERV2.DB2.dbo.Table:
| fname       | count |
----------------------
| 'file1.txt' | 40    |
| 'file2.txt' | 150   |

And I want to update SERV2.DB2.dbo.Table to:

| fname       | count |
----------------------
| 'file1.txt' | 10    |
| 'file2.txt' | 5     |
| 'file3.txt' | 35    |

I can:

  1. Drop SERV2.DB2.dbo.Table and restore from backup SERV1.DB1.dbo.Table to SERV2

Plus: Simple

Minus: Slow for big databases

  1. Use JOIN\UPDATE\INSERT or MERGE\UPDATE\INSERT constructions with camparing each field

Plus: Simple

Minus: Slow for big tables

Minus: Lot of data transferring

  1. Add and calculate HASH column on each row using all fields and then MERGE\UPDATE\INSERT for update only difference rows

Plus: Transferring only hashes for all rows plus rows for update

Minus: Altering tables

Minus: Calculating

Minus: Much more work for lot of tables

  1. Use tablediff Utility

Plus\Minus: ??? Is is it useful in this case? How did it works with slow connection?

Is there any more useful way to merge data from one server to other with minimum traffic transfer?

UPD: slow connection channel is the reason to optimize traffic. For tables with lot of records compare each record in both tables or copy full table\database will take lot of time.

Timur Lemeshko
  • 2,747
  • 5
  • 27
  • 39
  • define "minimum traffic" - This question has a ton of potential answers - you need to provide an attempt at the solution so we can provide insight – Takarii Feb 10 '16 at 08:44
  • i have very slow channel and lot of records on both sides. Only few records of them really difference and should be updated. When i say "minimum traffic" i mean that check all records in table is too slow because of slow channel – Timur Lemeshko Feb 10 '16 at 09:20
  • You still need to post your attempt before we can even begin to help you streamline – Takarii Feb 10 '16 at 09:21
  • If you don't have any **change tracking/timestamp/CDC** in that table you will have to atleast transfer the table from server1 to server2 and compare all. If you know what records are new/updated you will only have to handle those records. – mxix Feb 10 '16 at 15:03

0 Answers0