So far in my “Multi-Tenancy with SQL Server” series, I’ve covered an introduction to multi-tenancy and approaches for implementing it with SQL Server. In an ideal world, we have a blank canvas to start from and a crystal ball to see into the future. We know everything about the system we’re building, how many tenants we’re going to have, what their data/access usage patterns look like, and there are no unforeseen changes to that information. In that world, we can design the database strategy for multi-tenancy by comparing approaches to find which is best to meet our needs and live happily ever after.
Meanwhile, in the real world, this often is not the case.
Change Is Inevitable
What you knew a few years ago might differ significantly from the reality today, whether you started off with a single tenant system that you pivoted quickly to support multiple tenants, or you envisioned 10s of tenants and ended up with 1000s. Whatever that reality is, when you experience pain points around the 3 considerations I covered in part 1 of this series (security, maintainability, and scalability), it can lead to a need to change the multi-tenancy approach you’re using. Often, the biggest driver for change is around performance and scalability and typically tends to be related to a need to move from a less-isolated multi-tenancy approach (single database) to a more-isolated approach that supports the scaling out of workloads (multiple databases).
In the steps below, I’ll cover the general path you can follow to successfully make an architectural change like this to the database layer. As a provider of database performance monitoring and DataOps tools, SentryOne offers some tools that can come in handy along the way, so I’ll call those out as we go.
Step 1: Benchmark Where You Currently Are
The first step is to gather some key metrics/KPIs on the current system. To effect change in a positive way, you need to know what position you are starting from so that you can have a clear understanding of what problem(s) you are trying to solve and to have a baseline against which you can compare changes. After all, if you don’t really know why you think you need to implement a change, then should you really be making it at all?
Start by thinking about what the key things are that have indicated there is a problem. Maybe a particular process is taking longer and longer to run as more tenants have been added to the system—how long is that taking and how has that changed over time? Or maybe there has been a steady increase in tenants raising support cases that relate to database contention at peak times.
Step 2: Quick Wins and Optimizations
It’s always worth investigating if any optimizations can be made to the existing system. If there’s a relatively low-effort, low-risk change you can make, then it’s certainly worth it. Doing so can alleviate the problems and at least buy you some time while the more involved changes are planned and implemented. This could be a case of optimizing a particular stored procedure—in which case, SentryOne Plan Explorer can help you identify what is going on with a problematic query. Scaling up is also a common option for a quick win. Upgrading the server hardware can yield good improvements and can switch the effort from the engineering team to IT—this can definitely be a factor when there are competing priorities for a team’s focus, but be aware this could just be a sticky plaster to delay the inevitable.
Step 3: Plan
Just like if you were starting with a clean slate, walk through the considerations and approaches I covered in parts 1 and part 2 of this series. Decide which approach is most appropriate to solve the pain points you are now experiencing and identify which components will be impacted, such as:
- Applications
- Reports
- Maintenance jobs
- HA/DR strategies
This can be a big undertaking in itself, as it’s all too common that documentation is incomplete (or non-existent) and knowledge of the whole system is fragmented. This is one of the challenges we’ve tried to help solve with SentryOne Document—in particular with its lineage functionality that can track dependencies across various technologies, as shown in the example below.
The recently added Environment Map functionality, which is an integration with our SQL Sentry monitoring solution, is also handy, as it will document the machines, users, and processes that are connecting to the monitored instance.
We also need to consider a plan for migrating the data to the new structure. This should be an automated process to ensure it’s repeatable and reliable. Also, plan how to roll out to client-facing environments—it might be more effort to support both database strategies and migrate tenants over gradually, but it could be beneficial to reduce risk by staggering the transition over a period of time versus performing a “Big Bang” move.
Step 4: Engineering Effort
It’s important to be able to have a separate environment to develop and test on during this process and to isolate any source code changes away from your main branch in source control. The world doesn’t stop while you are on these re-architecting journeys and, chances are, there are other priorities that need to be delivered in parallel. Keeping them isolated gives clear separation and prevents impediments to making changes to the current system while this work is in progress. This also paves the way for testing side-by-side with the existing system to ensure data quality.
The data migration process is a key part of this effort. Although you’ll still be storing the same data overall, you’re likely to have some degree of schema change. For example, switching from a single tenant, shared schema database to a database-per-tenant model would make the presence of a TenantId field redundant. Having that process automated is crucial and can serve a dual-purpose—it’s not only useful for the initial migration of tenant data to the new structure but also for tenant onboarding workflows moving forward (i.e., when a new tenant joins, a new database needs to be provisioned and added to a tenant-database registry).
Step 5: Test
This step really goes hand in hand as part of the engineering effort—creating unit/integration/E2E test coverage during development. But it warrants a section in its own right to highlight its importance. The integrity of a tenant’s data is critical. When migrating to a new multi-tenancy database architecture, the data quality needs to be maintained. If a tenant sees different data after the migration, that’s a surefire way to lose confidence and trust.
As engineers, we’re usually pretty good at writing unit tests and such for application code. There’s a great sense of warm-and-fuzzy feelings from having high code coverage of our .NET code. But where it often falls short is testing of the data stack. In this situation in particular, you want confidence in the data migration process—does the new system provide the same data as the old system? If I do X on the old system, does X on the new system produce the same outcome? This was a challenge we wanted to help provide better tooling for, which led to SentryOne Test. Particularly relevant here is the ability to do the following:
- Reconcile the data for a tenant in the source database with the data in the destination database by querying both sources and comparing the results (via direct query, stored procedures, etc.).
- Compare the results returned by your product’s REST API when using the original database versus when using the new database.
- Validate ETL flows.
In addition to testing for validity and integrity of the data, you should test performance with realistic data volumes.
Step 6: Rollout
Have a rollout plan documented up front, with everyone who is involved clear on what’s happening and when. Communicate early to the clients. It’s a good idea to have a few trial run-throughs of the plan before the main rollout to iron out any kinks and have confidence in the full process. It’s worth considering an option that would allow you to rollout the change in a phased approach—start with a small subset of tenants, then gradually migrate more over. This migration approach could involve extra engineering effort to support running both models side-by-side for a period of time but gives you the benefit of being able to reduce risk by avoiding a Big Bang approach of a full migration all in one go.
Step 7: Monitor
Now you need to monitor the system, especially in regard to the key metrics you identified in Step 1. You want to see how those metrics look as the system beds in and identify any tweaks or optimizations where needed.
Summary
Multi-tenancy in SQL Server is a broad topic, and your specific environment, requirements, and goals all come in to play when deciding which path to take. Hopefully, this series provides some of the main points to think about as you identify what is important for your specific scenario, build out an implementation to suit those goals, and determine how to go about implementing a change of direction should you find yourself in that situation.
