Database design and data access patterns are crucial to developing database-backed software that offers reliability, good performance, and scalability. There are several things you need to take into account, including:
- What are the entities in the domain that I need to model?
- What are the data access patterns we need to support?
- Is it going to be read heavy or write heavy?
- What data projections and filtering requirements are needed to support the user requirements?
It’s important to get a solid understanding of the domain you are modeling. You need to understand what the user requirements are for getting data into and out of the system in a form that provides the value and efficiency that the business needs.
If you’re designing a system for one tenant, you can really focus on the problem domain at hand. But if the system will be used by multiple tenants (e.g., as a SaaS platform), then there are additional concerns you need to take into account on top of that core domain.
In this blog series, we will cover:
- An introduction to multi-tenancy
- Approaches to multi-tenancy with SQL Server (part 2)
- Strategies for migrating from one approach to another (part 3)
What Is Multi-Tenancy?
Wikipedia defines multi-tenancy as “a software architecture in which a single instance of software runs on a server and serves multiple tenants.” It means having a number of tenants (organizations, typically) all accessing a shared instance of the software, where they have the appearance that they are the only ones using it. Each tenant has their own data that only they can see and modify, so the context of a tenant establishes a data-access boundary. This is common among independent software vendors (ISVs) that offer SaaS products—they provide a solution for a problem domain on a shared platform and add customers to it over time. It’s cost-effective as you provision a set of resources to be utilized by multiple tenants, rather than provisioning a separate instance for each tenant (which is multi-instance architecture).
Key Multi-Tenant Database Considerations
When it comes to designing the database layer for a multi-tenant system, there are a core set of considerations you should take into account as you decide on the approach that best aligns with your SQL Server strategy.
- Security
- Maintainability
- Scalability
If you’ve only ever worked on single-tenant systems before, you might be thinking, “Sure, these things are important—nothing new there.” And you’re right; these things are always important. In a multi-tenant system, however, there’s an extra dimension. Not only are you going to be storing data for multiple tenants, but that number of tenants is also, hopefully, going to increase over time—so it’s not fixed.
Let’s drill into each of these points a bit more to cover what we mean in the context of multi-tenancy.
Consideration #1: Security
Data security is critical in all systems, but in multi-tenant systems you have the extra dimension of there being multiple tenants’ data present in the environment, not just a single, isolated tenant’s data. If you have a multi-tenant software offering, it’s a fundamental requirement that one tenant should not be able to see or interact with any other tenant’s data, and customers often look for assurances before signing up that their data is secure. If they can see other tenants’ data, then that’s a surefire way to lose customers and trust. That will cost you a lot. You could also open yourself up to fines for violating data regulations. (HIPAA and GDPR, for example, are just two regulations that could wield hefty penalties for violation of data protection and privacy rights.)
This boils down to the fundamental question:
How do I keep each tenant’s data isolated from each other?
I cannot overstate this enough—when choosing a strategy for implementing the database, it’s absolutely vital that security be at the forefront of your mind.
Consideration #2: Maintainability
You have a single-tenant system. You have a plan for taking backups of the database on a given schedule. You have some nightly jobs that perform maintenance tasks such as index rebuild/reorganization or data integrity checks to keep the system performing in its prime. You have a strategy for rolling out database updates. Maybe, just maybe, you even have a High Availability/Disaster Recovery solution set up. Awesome!
With a multi-tenant system, the approach you choose will have different implications for these processes. Typically, it comes down to the following question:
What is the impact on database maintenance as the number of tenants increases?
Although performing a particular maintenance process for 1 tenant might be relatively quick, what does that look like when you have 100 tenants? That maintenance process might soon become unwieldy and time-consuming.
Spoiler alert: if you’re doing things manually, find ways to automate.
Consideration #3: Scalability
In a single-tenant system, you might ask yourself, “As the data volume increases, how can I maintain the same level of performance?”
In a multi-tenant system, this consideration becomes:
As I add more tenants AND as each tenant’s data volume increases, how can I maintain the same level of performance?
There’s a clear difference between having 1 tenant with 10 million rows of data and having 1,000 tenants with 10 million rows each. That single month-end report is now going to run 1,000 times over. Knowing how many tenants you’ll have is also an unknown—as a business, you want as many as possible and to be able to add more tenants over time without negatively impacting the performance of the system. When you get your first tenant, it’s likely to be smiles all round. But what happens when you get your 100th or 1,000th? How can you plan a strategy for dealing with that growth? The more tenants you add, the greater potential there is for contention and performance degradation.
Summary
In this blog post, I covered the fundamental considerations that you should be thinking about when implementing a multi-tenant system. In the next blog post in this series, I’ll discuss different approaches for storing data in SQL Server to support multi-tenancy and the trade-offs those approaches might have for each of these considerations.
Adrian (@AdaTheDev) is a Lead Development Engineer at SentryOne, focusing on the development of the Data DevOps SaaS product portfolio – namely SentryOne Document and SentryOne Test. Based in the UK, he joined SentryOne from Pragmatic Works in 2018, with 18 years experience in software development that includes a background in developing SaaS platforms in the hospitality and digital marketing industries. Adrian is a fan of good (bad) puns, dad jokes, and proper British biscuits.
