I am working with a SQL 2005 database server that contains multiple databases and tables that are replicated from various sources. No one but administrators have access to this server.
There is a SQL 2008 R2 database server that exposes all of the tables from the 2005 databases using views (and only views) within a single database. There is no row-level filtering done on these views, they contain the same data as the original tables.
Regardless of why this architecture was chosen, it is causing three specific problems:
In SQL Server Management Studio, when browsing the views, there is no way to determine the indexes or primary keys on the base table.
When using tool such as SSRS to build a drag-and-drop type report, it does not automatically generate the relationship between the views (probably linked to #1).
Running an execution plan against a query written against the views is virtually useless as many of the nodes end up being "Remote Query"
I have done a little research into Indexed Views. The idea would be to basically replace the existing views with indexed views that have the same unique index as the base table. However, I don't think this is possible due to the following:
All base tables referenced by the view must be in the same database as the view and have the same owner as the view. http://msdn.microsoft.com/en-us/library/ms191432.aspx
Does anyone have any thoughts on how to alleviate the above three problems?