My local and remote servers were all using SQL Server 2008 R2 but the local ones were recently upgraded to SQL Server 2014 and the performance of Remote Queries has been crippled (literally from minutes to hours).
It appears that the problem is with sending data to the Remote Server so, as long as I am just reading data from the Remote Server, it performs fine except that, if the query is based on data at the local end, it seems to be effectively sending data to the remote server. I cannot setup the Linked Servers on the Remote Server end so I cannot make the remote server pull from the local one.
In most cases I can rewrite the queries to pull the data from the remote server into a local table, filter the local data, and then just send the relatively few updates to the remote server - Still slower than it was, but functional.
However, when doing anything involving lots of records, it is unuseable. The classic example is this query (originally it was doing a more complex query on the local server so I changed it to first populate a local copy of the final table so that I can do the simplest possible query for the INSERT);
INSERT INTO [REMOTE-DB].Databasename.dbo.Test_Table SELECT Person_Nbr, FullName, Birth FROM Test_Table WITH (nolock)
Even when I try OPENQUERY it seems no faster;
INSERT OPENQUERY ([REMOTE-DB], 'SELECT Person_Nbr, FullName, Birth FROM Databasename.dbo.Test_Table') SELECT Person_Nbr, FullName, Birth FROM Test_Table WITH (nolock)
If it weren't that this worked almost instantly on the older version of SQL Server, I would accept this as a fact of life but there is such a huge difference in performance that it seems that there must be some obvious fix that I am missing. I don't have control of the local servers but the DBA for them claims that the database configuration is consistent with the old setup.
Do you have any ideas what I can do to fix this or what I can point the DBA at?