I migrated my classic asp site from IIS6 to a much powerful server with Windows Server 2008 R2 and IIS7.5, but it actually runs even slower.
Every simple call to the MSAccess database is taking forever. Many times the request is dropped because of Session timeout (120 seconds).
Any idea what can cause the problem and how to solve it?
Thank You.
Before blaming Access and moving to SQL Server Express or another database, you need to make sure you know where the slowdown occurs.
From what you are motioning, it looks like at least some of the queries don't even work (IIS times out after 120s).
Access databases, especially if they are accessed locally by a handful of concurrent users, are fast.
Moving to another database may or may not solve the problem, but it will be probably be a lot more work than solving your issue with your current Access database.
That said, if your website needs to server lots of concurrent users (say more than 50 at a time) you may need to look into moving to a full database server like MySQL, SQL Server Express or PostgreSQL for instance.
A few things to make sure you double check:
Corrupted database. Make sure you use Compact and Repair regularly as a regular maintenance measure (make a backup first).
Incorrect filesystem rights.
Make sure the your IIS process has read/write rights to the folder where the database is located so that it is able to create the lock file (.ldb or .laccdb depending on whether you are using .mdb or the new .accdb database format).
A related issue is that the IIS process must be able to create temporary files in the temporary folder, for instance %SystemDrive%\Windows\ServiceProfiles\NetworkService\AppData\Local\Temp.
Bad queries. Open the database with Access and run the queries to check how long they really take and if they return any errors.
If there are data integrity issues, it could be that the query returns unexpected results that could have strange side-effect to the code in your asp page.
Check your IIS logs for errors. Also check the OS Event Log.
Make sure there are no other errors that could incorrectly cause the behaviour.
Make sure you profile your asp code to find out exactly which queries and parts of your code are slow and which are fine.
Once you have solved your issues. Improve performance by keeping the database open to avoid the lock file being create/deleted all the time (this can have a huge impact on performance).
A good reference with more detailed information on some of the topics above: Using Classic ASP with Microsoft Access Databases on IIS