MSAccess Database slow between 2012 Hyper-V virtual machines.

I currently have a problem with an MS Access front end / backend databae between two servers running on a Hyper-V 2012 core server.

The database opens quickly but browsing the data running queries eventually caused Access to crash "Not Responding".

I know neither of the servers are at fault as if I acces the same backend data on a physical server it works as expected and if I use a physical PC or Server to run the front end with the backend virtual this works also.

The problem only exists between 2 virtual Hyper-V servers. All other applications work fine on the virtual machines and network speeds are stable with data copy speeds around 2.6Gb/s.

It appears only network related when both source and destination network adapters are virtual.

Any thoughts??
LVL 1
DClaydenAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hamed NasrRetired IT ProfessionalCommented:
Check the Memory size allocated for the Virtual Machine.
DClaydenAuthor Commented:
There are no issues with memory - neither are using anywhere near the maximum memory usage. Also the problem on exists virtual to virtual machine. Virtual to Physical or Physical to Virtual works as expected.
Luke ChungPresidentCommented:
Do you create and maintain a link to the backend database when the front end starts so the lock file doesn't get constantly created and deleted?

Here's our paper on the topic: http://www.fmsinc.com/MicrosoftAccess/Performance/LinkedDatabase.html

This is one of the recommendations from our Total Access Analyzer product which finds lots of other performance and design tips: http://www.fmsinc.com/MicrosoftAccess/Documentation/ErrorsList.html
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

Jim Dettman (EE MVE)President / OwnerCommented:
<<Any thoughts??>>

 What version of Access?  Jet has a "bug" that exists when running on multi-core CPUs under a 64 bit OS.

 On older versions, you can try doing:

 ine.SetOption "MaxBuffers", 65535

 and see if that speeds it up.

  If A2010, make sure you apply this hot fix:

http://support.microsoft.com/kb/2726928

 When you set maxbuffers to 65535.

Jim.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DClaydenAuthor Commented:
The version of Access is 2007 running a a 32bit OS. What I have noticed is that if I set the MSAccess.exe process affinity to use 1 CPU it performs as expected.

I am not the develpoer of the database so unsure how its been created only support the OS and network around this.

Where would I apply the MaxBuffers?
DClaydenAuthor Commented:
I made the change in the registry as per this article. And it appears to be working correctly now. This would explain why when i set the process to only use one CPU it worked correctly.

Thanks JDettman!

http://osskb.host4kb.com/article/AA-01449/0/How-to-view-or-change-the-Jet-Access-2000-2002-2003-or-ACE-Access-2007-2010-MaxBufferSize.html
Jim Dettman (EE MVE)President / OwnerCommented:
Part of my comment got chopped.

You can do it with the registry (which is then system wide), or on a case by case basis in the app doing:

dbEngine.SetOption "MaxBuffers", 65535

at startup.

Jim.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Virtual Server

From novice to tech pro — start learning today.