Caching of linked server data

Hi
I'm currently setting up a SQL Server 2000. My configuration is:
Instance MyCompany running on ServerA
Instance TheirCompany running on ServerB
TheirCompany is mapped as linked server into instance MyCompany
In my situation, the data in TheirCompany is only located on ServerB due to
legal restrictions. All data access happens through ServerA. Therefore, it
would be favourable if ServerA would keep the data of TheirCompany in its
cache and not query the remote server for every SELECT.
Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
not volatile, from cache or something).
Are there any other suggestions on how to optimize performance? I'm aware of
the query plans for joins on remote tables, I'm much more looking for a way
to keep the SQL Server from fetching the data every time, as it won't have
changed FOR SURE!
Thanks in advance
Simon
posted to: microsoft.public.sqlserver.setup;
microsoft.public.sqlserver.server; microsoft.public.sqlserver.clusteringSorry, but there is no way to cache remote server data on a local server...
If you wish the data to be local you'd have to make a copy in a local
database...
You might consider making a copy in tempdb, and sharing that among users. It
will automatically be deleted when the Server is re-booted.
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
> not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>|||Could you replicate the data from your source to the target server. As long
as the data is treated as Read-Only on the target server, this could work.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"Simon Niederberger" <dontreply@.hotmail.com> wrote in message
news:Ojd11uL8DHA.3348@.TK2MSFTNGP09.phx.gbl...
> Hi
> I'm currently setting up a SQL Server 2000. My configuration is:
> Instance MyCompany running on ServerA
> Instance TheirCompany running on ServerB
> TheirCompany is mapped as linked server into instance MyCompany
> In my situation, the data in TheirCompany is only located on ServerB due
to
> legal restrictions. All data access happens through ServerA. Therefore, it
> would be favourable if ServerA would keep the data of TheirCompany in its
> cache and not query the remote server for every SELECT.
> Can this somehow be configured. (Like saying TheirCompany.DataAccessType =
> not volatile, from cache or something).
> Are there any other suggestions on how to optimize performance? I'm aware
of
> the query plans for joins on remote tables, I'm much more looking for a
way
> to keep the SQL Server from fetching the data every time, as it won't have
> changed FOR SURE!
> Thanks in advance
> Simon
> posted to: microsoft.public.sqlserver.setup;
> microsoft.public.sqlserver.server; microsoft.public.sqlserver.clustering
>