question

Andy-9425 avatar image
0 Votes"
Andy-9425 asked ErlandSommarskog answered

Linked server, a SQL server in AWS

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-general
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Andy-9425, welcome to Microsoft Q&A! Dose the T-SQL include join syntax or Functions? If yes, it maybe will impact the performance. Please refer to Techniques to enhance Linked Server performance to get more information.


0 Votes 0 ·
TomPhillips-1744 avatar image
0 Votes"
TomPhillips-1744 answered

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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
0 Votes"
ErlandSommarskog answered

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?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.