I've migrated a legacy (XP) SQL Server 2005 application to new hardware which it supposedly much faster than the previous implementation. Indeed, GUI response is clearly faster. However, SQL Server responses continually timeout.
The system was, and is running on XP with SQL Server 2005. The application is Access/VBA with SQL Server 2005 backend. The Access/VBA apps (.ade) can run locally on the XP, but more commonly they run from remote workstations and connect via network to the SQL Server host. Remote workstations are generally Windows 7.
As this is a legacy application, pretty much all of the tables accessed really reside on an SQL Server 2014 host where the new app replacing this legacy one resides. The tables are accessed via views on the 2005 server. For example:
CREATE view [dbo].[tblPaLaborClass] as
select memberType as laborClass, description, null as ts from [dbserver\MEMBERSLINK].hprs.dbo.memberTypes
The new platform was created by using Acronis Restore to copy an exact image from the old system to the new platform, so the XP version, SQL Server version, etc are supposedly identical. Even though the new system is apparently faster, yet queries via the Access/VBA app always timeout, whether local of remote. A remote query with Java takes 50 seconds to return -- which is too long for the Access/VBA app which simply gives a "Timeout expired" message after 30 seconds.
How can I figure out where the bottleneck is? I've been working on this for days.