linked server performance between servers in network vs outside (SQL 2012)

does it make a difference in performance (lot of updates) between two SQL Servers and the updates are happening through Linked Server.
 Right now both servers are in the same network environment.
 
 One is being proposed to be taken off-side (cloud environment-managed). In that scenario, does the performance expected as before when crossing over firewalls?
 
 would appreciate your thoughts on this.
 (one database is 12 GB and other 7GB data file size).
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
Is inherent due to the physical nature of the linked servers that they will degrade performance when cross queries are used. Think about it, SQL server does everything inside its memory after getting the data from the storage unit. As soon as you need to bring data over the network from a second server there will be extra processes involved and even more bottlenecks. If the 2 servers are not even in the same local network but at different sites then the lags will become even more evident and problematic.

Linked server are good, especially under the same local network, but I would say that they should never be used as a regular solution to run queries across servers. A better approach is to use replication to bring the data under the same hood and then run the queries.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
25112Author Commented:
thank you.. that is helpful.

>>bring the data under the same hood
you mean 'same instance', right? (meaning of hood, in this case)

can you please provide any/some msdn links on the above standards you mentioned, that will be helpful.
0
ZberteocCommented:
Yes. Replication is used to replicate data from one server/instance to another. Depending of what you need there are different kind of replications.

If you need data on the target only to be read then snapshot or transactional replication is needed. If you think you will modify data on both sides(servers) then a merge replication is needed. There are a lots of resources on the net in regards to replication. Here is a good article that can be a start:

http://www.codeproject.com/Articles/715550/SQL-Server-Replication-Step-by-Step
0
25112Author Commented:
thank you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.