Multi Tenant Database using Microsoft ACCESS!!! – Take your legacy apps to the masses.

In a recent project that was undertaken a challenge was presented. “We have an access database application that we currently install on end users machines, but we want to move it to a cloud based service”

The software was a split access database, usually only one organisation can access the relevant access database backend front a distributed frontend. There was a direct one-to-one relationship between the host windows machine and the database for the organisation… IE each organisation needed to have a dedicated machine – be it on premise or hosted service. This involved either ongoing maintenance and troubleshooting of dissimilar hardware/software environments or costly windows server hosted solutions.

This was before I.T. Discovery was involved…

By utilising MS Terminal Services and Active Directory, we were able to craft a server that is able to host MULTIPLE MS ACCESS databases. Each organisation has its own database and accesses the front end via Terminal Services Remote Desktop. Within this managed environment, the deployment of new organisations is a snap, adding new users via a scripting mechanism.

Here is the impressive part:-

• We have successfully hosted OVER 170+ Access Databases consisting of OVER 400 individual users.
• There is anywhere up to 15+ concurrent users accessing a single access database.
• The Access app is now “CLOUD BASED” allowing any RDP enabled device to connect.
• The TS server has been able to support up to 100 concurrent users.
• This has all been achieved ON A SINGLE SERVER 2008 R2 MACHINE.

Server Specs

6 core CPU

32GB RAM

Raid 5 Disk Array Currently 700GB

10m/bit internet link

The interesting thing noted about this deployment is that it is definitely not the cpu or hdd resources limiting more concurrent users, we hit the 32gb ram ceiling with around 110 concurrent users with this memory footprint per user. With more memory (Server Enterprise) this solution is SCALEABLE.

1. MSACCESS APP – 50-100mb
2. LIVE MAIL 150MB (since removed allowing MORE concurrent users)
3. SYNCDOCS 60MB

To achieve this with a SQL mutliple tenant environment, each instance of SQL server would have required an even very conservative 1gb, this limits the server to 32 organisations.

All in all the JET database engine is very efficient and fast when accessing data LOCALLY, the important this is keeping the front and back ends physically on the same machine and avoiding any network type access.