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?

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.

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

How did you map user in linked server? DId you get same error when run the query in query analyzer?

why cant you replicate the data that you needed to one server instead.

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?

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}.

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'?

Yes, you can.

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.