David Haycox
asked on
Access database slow after move to Server 2008 R2
We have an access database around 50MB in size, which worked fine when hosted on Server 2003. We moved it to SBS2011 (Server 2008 R2) and now it's fine for a single user, but as soon as anyone else uses it as the same time, it becomes slow, taking at least 5 seconds to open a simply record which previously happened almost instantaneously.
As a test the data was moved to a Server 2003 virtual machine, and the problem was resolved. However we would like to host the data on the SBS2011 box natively, which I would expect to respond more quickly, especially from Windows 7 clients (it certainly is very fast for regular file access).
How can we improve the performance of the database while hosting it on Server 2008 R2 (SBS2011)? The database has been compacted already.
Thanks in advance.
As a test the data was moved to a Server 2003 virtual machine, and the problem was resolved. However we would like to host the data on the SBS2011 box natively, which I would expect to respond more quickly, especially from Windows 7 clients (it certainly is very fast for regular file access).
How can we improve the performance of the database while hosting it on Server 2008 R2 (SBS2011)? The database has been compacted already.
Thanks in advance.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
MadShiva: the front-end and the table were already separate. We've had the tables re-linked to a UNC path, but that hasn't made a difference.
JDettman: We just tried the MaxBuffer setting but it made no difference. I'm about to disabled SMB 2.0.
The users report that the first person to log on seems to run with decent performance, at least to start off with. Later on it can switch around, but there's usually one person working ok with the rest looking at the blue circle of boredom. Could this be something to do with the way Server 2008 deals with locks on the files?
JDettman: We just tried the MaxBuffer setting but it made no difference. I'm about to disabled SMB 2.0.
The users report that the first person to log on seems to run with decent performance, at least to start off with. Later on it can switch around, but there's usually one person working ok with the rest looking at the blue circle of boredom. Could this be something to do with the way Server 2008 deals with locks on the files?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
That setting deals with OPLOCKS. If you can't get them off because of SMB 2.0. modify the application to open a connection to the back end and keep it open for the life of the app.
This can be as simple as opening a recordset on a table in the BE, a from bound to a table, etc.
Jim.
This can be as simple as opening a recordset on a table in the BE, a from bound to a table, etc.
Jim.
ASKER
Good advice from experts, pointed in the right direction but ultimately solved it myself.
ASKER
We'll try the buffer setting shortly.
There are still two XP clients, the rest are Windows 7 though. If it needs be done then we can switch off SMB 2.0 but of course this will reduce performance for regular file shares so I'd prefer to avoid it if possible.