August 4, 2016 at 9:06 am
Apologies for such a question...
We have a supplier of a piece of software that is made up of an Access front end with a SQL Server back end. It was previously on a Windows 2003 server, with a SQL Server 2005 back end database and it worked fine.
It has been moved to Windows 2012 and a SQL Server 2014 database, but still uses Access 2010 runtime. Our users are having terrible performance issues and the supplier is blaming our network. We have carried out extensive tests on the network, servers and database and produced numerous stats proving that there is nothing wrong with any of these elements. The other thing we have discovered is that the application works fine when only one user is in it. More than 1 user and it freezes up, produces many timeout and 'application defined error / object defined error' messages. Therefore, looking at this from a DBA perspective, I feel that perhaps there is a compatibility issue with the newer software, perhaps related to how Access connects to SQL Server (ACE etc).
The system was written many years ago and has not been re-written as far as I am aware.
Please can anyone advise if you have come across similar issues?
Many thanks,
Paula
August 4, 2016 at 9:30 am
Paula-196779 (8/4/2016)
Apologies for such a question...We have a supplier of a piece of software that is made up of an Access front end with a SQL Server back end. It was previously on a Windows 2003 server, with a SQL Server 2005 back end database and it worked fine.
It has been moved to Windows 2012 and a SQL Server 2014 database, but still uses Access 2010 runtime. Our users are having terrible performance issues and the supplier is blaming our network. We have carried out extensive tests on the network, servers and database and produced numerous stats proving that there is nothing wrong with any of these elements. The other thing we have discovered is that the application works fine when only one user is in it. More than 1 user and it freezes up, produces many timeout and 'application defined error / object defined error' messages. Therefore, looking at this from a DBA perspective, I feel that perhaps there is a compatibility issue with the newer software, perhaps related to how Access connects to SQL Server (ACE etc).
The system was written many years ago and has not been re-written as far as I am aware.
Please can anyone advise if you have come across similar issues?
Many thanks,
Paula
Have you ruled out any possible issues with SQL Server 2014 new Cardinality Estimator? That could be causing queries which previously executed quickly to execute more slowly.
You may want to experiment with this (in a test environment) by changing the database's compatibility level back to 110 or lower.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 4, 2016 at 9:38 am
Thanks for your reply, I will look into this.
August 5, 2016 at 2:26 am
and you may also consider moving to the a runtime
2013 - https://www.microsoft.com/en-us/download/details.aspx?id=39358
2016 - https://www.microsoft.com/en-US/download/details.aspx?id=50040
August 9, 2016 at 2:22 pm
You have my sympathies. I loathe anything to do with Microsoft Access.
August 10, 2016 at 2:54 am
Just to update... I tested in a lower compatibility mode but it made no difference. In the end the system froze for long enough for me to run a trace and actually get a result. We managed to pinpoint it to a stored procedure that was referencing a table that was missing a critical index! It is early days, but for now, I have added the index and the system is operating well. I need to observer for 7 days really, but hopefully this is the solution.
Thanks for all the responses.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply