What impact on performances in SQL 2008 USE OF LINK SERVER in Data warehouse enviroment

Posted on 2012-09-06
Medium Priority
Last Modified: 2014-01-14

I recently architectoring and  developing in a large data warehouse project, for this project, I use link server in terms of data extraction from source to the Data warehouse envrioment, and also I use link server in the data warehouse staging and to the data warehouse production enviroment.these link server connections are referred in SSIS packages.

My question to you is; what impact in terms of use of link server in the data warehouse, does it cost performance issues on data transfer?

Question by:keplan
  • 3

Expert Comment

ID: 38374395
Recently I had to use linked servers to update millions of rows for a Tier 1 telco when they realized some of the billing information wasn't matching what should have been coming out of ETL.  It took several hours and we got it in 10 minutes before the billing deadline.  

Lesson learned.  Linked servers are slow as molasses, and worse, I couldn't use bulk logged operations.  I played around with "Service Broker" and that makes a lot of sence.  Service Broker is very fast and does queues up messages (or statement results).  It took a lot to wrap my head around it but it's a very nice alternative.

Author Comment

ID: 38374793
Dear MrAli,

Can you give me how "Service Broker" work,
in link server, we use <server_name>.<dbname>.<schema>.<tablename>,
sorry, Im not familier with Service Broker, can you shed the light on this please?

Accepted Solution

keplan earned 0 total points
ID: 38385141
any update on my request please, I'm happy to get any update

Author Closing Comment

ID: 39778803

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Suggested Courses

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question