2

I'll preface this question with the fact I know about This question but unfortunately it was no help to me as "don't run the database over the network" is just not an option.

Last week we migrated our file server from Server 2000 to a Server 2008 R2 Standard x64 Virtual Machine. We have a database that provides contact information for stakeholders and business partners. This is a proprietary database written in house by our database analyst. Since we migrated the server, this database has been running much slower than normal.

That said, I've been looking for the cause for the past week as this is an important database for us. We are moving toward using another system, but I'd like this issue resolved with this sooner rather than later.

Relevant Information

  • Linked tables have been re-linked to reflect their new UNC address and the database compacted

  • There is currently no anti-virus running on the server

  • All client anti-viruses are set to not scan network drives

  • We have made an antivirus exception for msaccess.exe for testing purposes with no luck

  • I tried dropping the firewall on the file server with no luck

  • I haven't noticed any issues with file access (in fact, most of my employees have said they've noticed an increase)

I would love to hear any suggestions as to why people would think that Server 2008 R2 is slowing down the database.

Community
  • 1
DKNUCKLES
  • 4,028
  • 9
  • 45
  • 60
  • 1
    I don't envy your position of having to deal with a "database analyst" that chooses to develop in MS Access. What an abomination of a product. – EEAA Sep 30 '11 at 18:40
  • possible duplicate of [Database access slow after upgrade from Server 2003 to 2008 R2](http://serverfault.com/questions/255304/database-access-slow-after-upgrade-from-server-2003-to-2008-r2) – mailq Sep 30 '11 at 18:41
  • I think that Jet/ACE data files don't play well with virtualized servers. Secondly, virtualized shared servers are very, very often vastly underprovisioned in comparison to a standalone machine. Move the back end off the virtualized server and my bet is that the problem will go away. And, no, I don't think there's actually any solution if you insist on storing the data on the virtualized server. – David W. Fenton Sep 30 '11 at 18:48
  • 2
    @DavidW.Fenton - That's a ridiculous comment. It's just as likely that a physical server would be just as under-provisioned as a virtualized one. You have *zero* data with which to back up your comment. – EEAA Sep 30 '11 at 18:50
  • 1
    @mailq The first sentence states that I'm aware of a similar question, however that question was no help hence why I asked the question with the steps I had taken to attempt to troubleshoot. – DKNUCKLES Sep 30 '11 at 19:07
  • The data I have on this is that I've observed people make exactly this complaint over and over again when their Access databases got moved to a virtual server. IT DOESN'T WORK WELL. This is a frequent problem, in fact (though I can't cite any examples, as I'm too lazy). I would think that anybody who understood the way Jet/ACE works would be very wary of virtualization, in fact... – David W. Fenton Oct 05 '11 at 17:42

2 Answers2

3

Plain and simple, MS Access is a horrible product, something that MS should have never developed or sold. That said, for whatever reason, you're stuck using it (for now). You don't want to hear this, but the real answer is to:

  1. Stop using the database over the network.
  2. Stop using MS Access and use a real database server.

Migration paths between Access and MSSQL are available, and are not all that difficult if your dba knows what he's doing. You can even still use MS Access as the front end, connecting to ODBC sources on the SQL server.

EEAA
  • 108,414
  • 18
  • 172
  • 242
  • Thank you Erik for your feedback. We are currently in the process of upgrading to a newer version of SQL server and putting this database on it will be one of the first orders of business when the upgrade is complete. I appreciate your constructive feedback. – DKNUCKLES Oct 03 '11 at 15:18
2

At the risk of being tabu and answering my own question (and being reprimanded for continuing to use MS Access over the network) , we have been successful in troubleshooting the issue.

The issue was resolved by doing the following :

1) All tables were re-linked to be the UNC path and NOT a path relative to the mapped drive (ie \\server\share\database.mdb and NOT T:\database.mdb)

2) Code of the database was recompiled

After completing the above we noticed a dramatic speed increase in the database lookups and functionality.

DKNUCKLES
  • 4,028
  • 9
  • 45
  • 60
  • "At the risk of being tabu and answering my own question" Not at all, there's nothing wrong with that. – JohnD Oct 03 '11 at 15:27