Introduction to SQL Server Multi-Tenancy (Part 2)
In Introduction to SQL Server Multi-Tenancy (Part 1), I talked about some of the key considerations around designing a multi-tenant system using SQL Server. There are several ways to implement multi-tenancy, and, as is often the case, there is no single "best" way but rather a range of options that each offer different trade-offs. The approach that is right for you depends on your objectives and needs for your specific environment. It's important to consider which of these approaches best suit your requirements and goals based on the 3 core considerations from Introduction to SQL Server Multi-Tenancy (Part 1): security, maintainability (manageability), and scalability.
The following are the 4 approaches I will cover in this blog post:
- Single database, shared schema
- Single database, separate schema
- Database per tenant
- Multiple databases, multiple tenants per database, shared schema
Approach #1: Single Database, Shared Schema
- One database to hold the data for all tenants
- Every tenant's data is stored in the same set of tables
- Tables that contain tenant-specific data include a column to identify which tenant each row belongs to
Security
Risk of exposing one tenant's data to another tenant or updating the wrong tenant's data (e.g., if a developer misses a WHERE clause to filter on the tenant id)
- Mitigation: Row-Level Security (RLS) can be used to control access to rows in a table. Create an inline table-valued function to apply a filter on the tenant id and then create a security policy to apply that filter predicate automatically on the target tables. As long as you maintain that security policy with the full set of tables, queries/updates on those tables will then be automatically enforced. Developers don't need to remember to manually add the filter clause to every SQL statement.
No tenant isolation
Maintainability
️ One database schema to maintain and a simple schema update rollout process—it only needs to be applied once
️ Manage the High Availability/Disaster Recovery/maintenance operation/monitoring strategy for just one database
️ Limited development/application code complexity—single schema, single database to connect to
️ Adding new tenants is easy—no processes needed around database/schema provisioning or connection determination
Any query or data modification includes a predicate to restrict the operation to a specific tenant id
- Mitigation: Can use RLS policy
Must remember to update the RLS policy as new tables are added over time
Can't easily restore a single tenant's data
Scalability
Limited to scaling-up hardware, rather than scaling out
Risk of "noisy neighbors"—tenants can impact the performance of the system for all others due to a lack of isolation and all competing for the same resources
One-size-fits-all performance tuning and stability—tenants' data volumes and usage can vary dramatically, impacting things such as execution plans making it more difficult to optimize performance across every tenant
As the number of tenants and data per tenant grows, maintenance activities take longer, potentially impacting all tenants
Approach #2: Single Database, Separate Schema
- One database to hold the data for all tenants
- Separate tables for each tenant, each set under a tenant-specific schema
Security
️ Tenant data has some more isolation (but still within the same database)
️ No RLS needed; reduced risk of missing a WHERE clause to limit to specific tenant's data
- Either queries need to be dynamic to define the qualified table name or connect using a tenant-specific user account with the default schema set to that tenant's schema (preferred)
Still a risk of querying the incorrect schema (e.g., specifying the schema for an object when it should have instead come from the user account's default schema—usual best practice is include schema prefixes, which can feel unnatural)
Still limited data isolation
Maintainability
️ 1 database to manage High Availability/Disaster Recovery/maintenance operation/monitoring strategy for
️ Extra scope and control over some tenant-specific maintenance activities
Schema updates more involved, needing to be rolled out to n tenants
- Mitigation: Automate the process
Query complexity
Can't easily restore a single tenant's data (although it's a slightly better process than approach 1 due to isolation of tenant data)
Adding new tenants is more involved as new schemas/user accounts need to be created
- Mitigation: Automate the process
As the number of tenants grows, there will be a lot of database objects being created to manage and maintain
Scalability
️ Data is partitioned into smaller tables, with smaller indexes
️ Optimizations could be made at an individual tenant's schema level
- Caution: This could cause schema drift (e.g., custom indexes, which introduces a maintenance / source control process cost). Strong processes needed to track these.
Limited to scaling-up hardware, rather than scaling out
Risk of "noisy neighbors"—tenants can impact the performance of the system for all others due to limited level of isolation and all competing for the same resources
Approach #3: Database Per Tenant
- Each tenant has their own database
Security
️ Highest level of tenant isolation, supporting options for shared server and/or isolated servers
Potentially more servers to patch and keep secure
Maintainability
️ Maintenance jobs can be managed and customized per tenant
️ Can easily restore/relocate/clear down a tenant's data
️ No added query complexity
Adding new tenants is more involved, as new schemas need to be created
- Mitigation: Automate the process
As the number of tenants grows, there will be more databases being created to manage and maintain
Some added complexity to maintain a registry of tenant-db mappings/application code to determine which connection to use
Scalability
️ Scale-out and scale-up are both options—tenants can be spread over multiple servers
️ Choose to balance between cost (higher tenant density/fewer servers) and performance (lower tenant density/more servers)
️ Control over "noisy neighbor" risks
Approach #4 : Multiple Databases, Multiple Tenants Per Database, Shared Schema
- Hybrid of approach #1 and approach #3
- A pool of databases exist
- Tenants share a database and schema with other tenants, but are spread over multiple databases
Security
️ Some tenant isolation possible in general over approach #1
Tenants still share a database and schema with others (same RLS mitigation applies as approach #1)
Maintainability
Choose to balance between overhead of more databases to maintain (lower tenant density) versus fewer (higher tenant density)
Possible to relocate a tenant's data (although harder than approach #3)
More maintenance overhead than approach #1
Scalability
Scale-out and scale-up are both options—tenants can be spread over multiple servers
Choose to balance between cost (higher tenant density/fewer servers) and performance (lower tenant density/more servers)
Decisions, Decisions
When deciding which approach is best for your particular circumstances, consider what factors are most important to you and how you foresee growth of the product and client base. Are you going to have 10s, 100s, 1000s of clients? What would the data volume and workload look like for a typical tenant? What types of clients—might they have strong requirements for data isolation that would be a deal-breaker if you couldn't offer it?
Some examples of what considerations might lead to which approaches being most suitable include:
- If security and data isolation is your number one concern, approach #3 might be best for you.
- If you are expecting a larger number of tenants and want to strike a balance between ongoing management, security, and the ability to scale, approach #4 might be for you.
- If you are expecting a smaller number of tenants, smaller growth of data and scalability requirements, approach #1 or #2 might be for you, depending on your attitude toward data isolation and complexity around maintenance.
- If you want the greatest degree of scalability, approach #3 might be best for you.
One of the other important things to think about, which I touched on above, is automation. Automation is often key to mitigating the impact of otherwise costly, manual processes. Investing in automating the on-boarding process for a new tenant is crucial, not only to provide a smooth and speedy experience for the client but also for reducing your overhead for that process. You also want it to be reliable and consistent, which automation will help with. Thinking about automation up front will save you time, money, and pain later on. For example, it might seem OK to roll out schema updates manually when you have a couple of databases, but what about when you have 10, 100, etc.?
Adapting to Change
I've covered some of the main approaches to multi-tenancy with SQL Server. If you're starting to design a multi-tenant system, hopefully this blog post will provide the start of some good discussions within your team to work out what strategy makes most sense for you.
If you already have a multi-tenant system, it's common to experience pain points with the original strategy you (or a previous team) chose, especially as your business evolves over time. Often, this means performance and scalability issues for a variety of reasons. Perhaps the growth in the number of clients was massively above all expectations. Maybe the business saw an opportunity and pivoted to target clients in another sector that have stricter requirements or must larger data volumes. The challenge is, if you're in that situation with an existing system that you need to change to resolve the pain, how can you tackle that? In the final blog post in this series, we'll talk about some of the points to bear in mind and some strategies for iterating to a new approach.
Interested in learning more about SQL Sentry? Download a free trial today.