Results 1 to 10 of 10

Thread: Linked Server, Views and Efficiency

  1. #1
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187

    Linked Server, Views and Efficiency

    Hi,

    I have two servers that i want to create a SQL RS report on.
    On one server there is an HR database with our staff details, on the other server there is a database of assets.

    In order to report on the assets assigned to each user i am thinking that i will have to :
    1) link the servers
    2) create a view in the HR database exposing the fields needed
    3) create a view in the assets database exposing the assets information joined to the view from the other server
    4) create my reports on the view on the assets server.

    is this right or am i barking up the wrong tree?

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    It is doable, but be careful with linked servers as they can be performance hog.

    The goal should be to minimize the amount of data pulled from the linked server by making remote queries as selective as possible.

  3. #3
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    OK, how should i do it?

    i am having big problems with getting the linked servers set up as there are issues that appear to relate to the extra hop from the reporting services server to the hr server. it is complaining about a failure to authenticate NT Anonymous ... so i am not precious about keeping that method!


    what would you recommend? this is my first step into this area so learining how to do it properly will be well worth while.

    thanks

  4. #4
    Join Date
    Sep 2002
    Posts
    5,938
    How did you map user in linked server? DId you get same error when run the query in query analyzer?

  5. #5
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    why cant you replicate the data that you needed to one server instead.

  6. #6
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    RMIAO: Not sure, havent had that much time on the job yet, also does that really matter if we are better off taking a different approach?

    MAK: Would you really recommend replication for this? I would have thought, in my ignorance, that replication is a bit of a sledge hammer to crack a nut?

  7. #7
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    My Approach is to separate OLTP, OLAP and DSS. I dont mess anything with OLTP.

    So I move all the data to a DSS server and do all reporting against that server. Latency between two servers are minimum {Usually 10 seconds depending on the load}.
    Last edited by MAK; 05-09-2005 at 04:14 AM.

  8. #8
    Join Date
    Nov 2002
    Location
    cornwall
    Posts
    187
    OK, can see the logic in that but, are you working on 'grown-up' databases? We have maybe 8 people in HR and only 2 on the assets database system so there isnt a great load on any server!!! I can still see your argument so that isnt an issue.

    A query about replication. I have already created the view on the HR database, would you just replicate that and a view of the Asset data to a 'reporting database'?

  9. #9
    Join Date
    Sep 2002
    Posts
    5,938
    Yes, you can.

  10. #10
    Join Date
    Feb 2003
    Posts
    1,048
    I would replicate just the tables and then manually create any view(s) you need on the new server. The problem with replicating views is if they refer to objects that haven't been created by replication yet on the subscriber, your replication snapshot will fail.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •