In my first post on Multi-Tenant Database Architectures, I outlined the various factors that must be considered when choosing a multi-tenant architecture. In my second post I examined the “Separate server” and “Shared server, separate database server process” architectures. In this third post, I will outline the “Shared database server process, Separate database” model.
I am compelled to reiterate that there is no “one-size-fits-all” approach to implementing a multi-tenant database, only trade-offs. You must choose the correct approach for your needs based on the specific requirements you have for your business.
Shared Database Server, Separate Database
This architecture builds on the separate server process model in that each tenant continues to use their own database, but there is only a single database server process. Several tenants share a single server machine, as well as a single database server process. This approach continues to allow each tenant to have their data isolated from other tenants. An improvement is that the single database server process can share resources such as database cache effectively between tenants. This allows for improved utilization of machine resources, at a modest reduction in performance predictability for each tenant.
Many of the factors to consider will remain similar to the previously described models in which each tenant has their own database:
- Development time – There is minimal additional development time when compared to an on-premise solution.
- Hardware cost – This architecture allows for improved utilization of machine memory resources, however still requires overhead for unique databases per tenant.
- Application and database performance – The performance of one tenant may be impacted by the activities of other tenants sharing the server machine.
- Security – Each tenant has their own database, completely isolating them from other tenants.
- Customization requirements – Each tenant has their own database, so it is easy to customize the database schema for the differing needs of each tenant.
- The number of tenants – Able to handle more tenants than the separate server approach, but still limited based on the need to administer and store separate database files for each tenant.
Similar to the previous model, migration of tenants is easily accomplished for tenants who require improved performance and isolation. Using SQL Anywhere, a tenant can be moved to their own machine simply by moving the database file. This relies on the self-contained and machine independent nature of SQL Anywhere database files.
Multi-Tenant Database - Shared DBMS Server, Separate Database
This model can be easily demonstrated using SQL Anywhere. Building on the first demonstration, this second demonstration uses the databases created, and demonstrates how to start them on the same database server.
2 responses so far ↓
1 Breck Carter // Oct 30, 2009 at 10:37 am
It should be noted that all SQL Anywhere schema objects are local to the individual database; there is no server-level “master” database as in SQL Server or ASE. This extends to user ids and passwords, and connections are made to a database, not a server. In a multi-database shared-engine scenario, each database is isolated from all other databases, from a security point of view. Backup and recovery is also simplified; there are no shared server-level objects to worry about.
2 Chris Kleisath // Oct 30, 2009 at 10:51 am
Excellent point Breck.
3 Challenges with Shared Schema Database Model for Multiple Tenants in the Cloud | The Invisible Database // Nov 22, 2011 at 9:12 am
[...] I described in Part 5 of my series on Multi-Tenant Database Architectures published in 2009 (1, 2, 3, 4, 5). The important thing I would like to ensure you realize is that there ARE other options. [...]
Leave a Comment
Note that all comments are currently being moderated until I have a better handle on spam, so your comment may not appear for a couple of hours