I have table A that is on server 1 and table B that is on server 2. Table contain around 1.5 million rows. What would be the fastest way to copy table A to server B? On nightly basis.
Or what would be the fastest way to bring only records that changed in table A and bring it to table B?
So far I tried MERGE along with HASHBYTES function to only capture records that changed. It works perfectly if target and source tables are on the same server. (takes approx 1 min).
But if target is on server B but the source is on server A - than it takes more than 15 min.
What is on your opinion the best and fastest technique for such operations?
Some sorts of replications? Or maybe SSIS would be the best for that?
My 2 cents. Since you qualified your question with "On nightly basis", I'd say do this in SSIS.