February 29, 2012 at 9:20 am
I have an Access Application that currently links to a Sql Server Table. This Sql Server is moving to a new company and the old company will not allow the Access App to directly connect to Sql Server in the new company. Therefore we came up with an idea to use a sql server in the old company that has a linked server to the database over in the new company. I will create a synonym for the table name in the new database in the sql server in the old company.
My question is, what resources will the a select query from Access be using on the sql server in the old company which links to the new server at the new company? Will tempdb or buffer pools be affected, or will it simply be just network resources that are affected? The only query Access will use is a select query.
February 29, 2012 at 9:58 am
Performance will be awful, expecially if the query contains JOINs.
I would talk to the DBA and convince him/her to open the connection from Access.
-- Gianluca Sartori
February 29, 2012 at 10:18 am
no joins, simple select query. Company policy unfortunately.
However, I really want to know what is going on behind the scenes in Sql Server in this situation, specifically the server with the linked server/table synonym.
February 29, 2012 at 10:33 am
If you have no joins, it could work.
If you have an alias to a remote table, the query will be executed against the remote table. The buffer pool would be used on the remote machine and not on the local server.
Depending on the complexity of the query, SQL Server could decide to download the whole remote table locally and then filter data. Make sure you don't fall into this trap.
-- Gianluca Sartori
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply