Linked server, a SQL server in AWS

Andy 1 Reputation point
2021-10-05T01:02:09.607+00:00

Hello

SQL Server A (2016): on premise

SQL Server B (2019): AWS (previously B was also on premise)

We created a linked server in A to B.

We are copying a table from B to A. There are over 5 millions rows in the table.

When B was on premise, the data copying job took a few mins. Now, B is in AWS, the copying job been running for more than 45 mins, it is still running.

Is it the way it is when one of the servers is in AWS? Or there is something we can do to improve the speed?

Thanks

Andy

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,776 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Tom Phillips 17,716 Reputation points
    2021-10-05T11:51:38.35+00:00

    The copy is going to be dependent on your network speed to the AWS server and the target server performance.

    0 comments
  2. Erland Sommarskog 101.4K Reputation points MVP
    2021-10-05T22:01:58.64+00:00

    Obviously, then the linked server is further away, you will take a toll for the network. You say a "few minutes". Say that that was five minutes, and now it takes 45 minutes. Well, if you network connection to AWS is nine times slower to what you had on-prem, you have the answer there.

    There can be more do it, if there is something that requires calls going forth and back, because then you have to pay for the latency across the network. (No matter what bandwitdh you have, you are always limited by the speed of light.)

    What does the actual query look like?

    How long time did it take for the operation complete, eventually?

    0 comments