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

Posted on 2012-09-06
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
    LVL 7

    Expert Comment

    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

    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

    any update on my request please, I'm happy to get any update

    Author Closing Comment


    Featured Post

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    745 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

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now