For more than 20 years, many small and medium SaaS companies have built products with complicated business logic and a flexible user experience on the .NET stack using an architecture with a shared SQL Server database. This approach has kept the production environment relatively simple.

These companies have new incentives to move away from this architecture because agentic development lands new business rules on the shared database fast enough to painfully cut velocity and add significant risk to deployments.

Tenancy has taken one of two shapes in SQL Server, but often uses a shared database

Shared databases appear in two major forms:

Scale out: each tenant gets their own copy of the database, one database per tenant

A SQL Server database per tenant still shares every part of the product. For example, the tenant boundary does not separate case handling from billing. Inside each of those databases, multiple parts of the product read and write the same customer and account rows. For example, account history, case handling, scheduling, document generation, billing, a customer portal, email that includes those records, and reporting all read and write those same rows.

Scale up: the application uses a relatively small number of multi-tenant databases

The scale up model does not always use a single database. Metadata may be split out into its own database, or one type of data may be split apart from the rest. The rest of the product still shares the main tables. For example, case handling, billing, the customer portal, and reporting all read and write the same customer and account rows. Shared access to those tables stays high.

In either case, replicating the data to secondary datastores has been unnecessary, because every part of the product reads those rows as soon as they change.

That has been a simplicity win, and the right trade for a long time.

Serving OLTP and analytic workloads from a single SQL Server database has become common

SQL Server features have increasingly enabled shared databases to effectively serve transactional workloads and answer up-to-the-minute questions about activity and trends.

Columnstore indexes enable rapidly aggregating large sets of values across rows on the tables the product uses for transactions. Batch mode on rowstore enables processing up to 900 rows at a time, even on a normal b+ tree table, instead of one row at a time.

Microsoft shipped readable secondary replicas with Always On starting in SQL Server 2012. The secondary applies the log as it arrives. Lag is often a few seconds, so what users see stays up to the minute. Microsoft documents how to offload reads to a secondary replica to segment production workloads without having to modify database schema or replicate data.

This suite of features has helped organizations grow and scale fairly rapidly when using the shared database model.

The shared database is starting to cut velocity and add risk

Agentic development is now fast enough, and good enough, that new business rules land on the shared objects much faster than they used to. Each change may work well on its own, but together they raise the complexity inside the database. The next change has to work through all of those rules, and then the next has more to account for, and so on. Velocity drops, and the risk of each change goes up. This has always been the case, but now it occurs much more rapidly.

Reviewing a change for data quality, safety, and performance takes longer as more of those rules depend on the same objects.

For example, the customer portal and billing share the Orders table. At the start of the month, a change to that table has to be checked against placing an order, invoicing it, and showing it in the portal. Then the portal adds a cancellation, a renewal job, and a retry-payment button. Each one is another business rule on that table. The next change has to be validated against all three of those, plus invoicing, plus every portal page that reads the row. The set of things to validate grows with every pull request that lands, even when each pull request is correct on its own.

The same growth shows up when the change is in the schema. For example, a new feature adds a column and needs an index to support it. The table already has a lot of indexes, but perhaps we can get away with modifying an existing index instead of adding a new one? However, modifying an index can be risky. There is a chance that existing queries using the index may change their behavior. Validating this can’t be done effectively on sample data: the query optimizer takes many things into account, including data sizes and row distributions. Agents can help with this validation to reduce risk, but doing so adds time and cost.

The fads and fashions of microservices have largely faded: microservices are the other extreme end of the spectrum, and they introduce a lot of complexity. Most small to midsize SaaS companies don’t need this.

But what I think of as “a nice midsize service” with a private datastore and a service contract is becoming more attractive than ever. Billing keeps its own tables, and the portal keeps its own, instead of both writing the Orders table. Code changes can roll through as long as the service still meets the contract. Each new business rule for billing does not have to be checked against the portal’s rules or every index on a shared table, so new features land quickly with lower risk. Over the long term, this model allows higher code velocity, simpler and faster development and review cycles, and lower risk.

Tooling and agent guidance help the shared database model, but only to an extent

Teams can hold the shared database model together longer by building tooling and guiding agents. Automated review can be improved by better linting and building skills and guardrails for agents. For example, a contract names what the change has to satisfy; a proof is the evidence the agent has to produce; tests fail when the contract is missed. Agents may be required to perform data parity testing against realistic datasets for modification queries being added or altered, and performance testing against production-like databases for hot-path queries.

Every one of those steps adds time and expense to the pull request, both for tokens and for developer time to herd along the pull request across every hurdle. But none of these techniques separates the shared tables. And as the data grows, the team has more rows and more rules to validate on the same objects.

Migrating from a shared datastore to separate services with private datastores is not simple

Major architectural changes like moving from a shared database to services with private datastores usually bring up the metaphor of trying to rebuild an airplane while flying it. It ain’t easy.

The project risks stalling new features, or dragging on so long it never ends. A company has usually needed an urgent scalability problem, with a clear business payoff, before it takes a project like this on seriously.

The hard parts stay hard

Untangling dependencies, converting code, testing, and changing table schema and locations have been the most tricky parts of these migration projects, and they still are.

For example, analytics queries in a shared database model can join across the necessary tables, because they sit in one database. After the split, either that join has to move into the application code, or data must be replicated in some way to enable joining in a shared database.

Moving a join into the application code is not simple at all, because the application has to ask each service for its rows and match them itself: the larger the data, the greater the challenges with managing memory and CPU at the application level. There are also issues with transactional consistency across these separate reads from multiple datastores. This only scales so far, and it requires specialized techniques. Replicating data is often not a simple solution either, and production environments with overly complex data replication patterns can fall into the trap of high complexity and risk due to these patterns.

Identifying the required architecture and tools/platforms needed to support splitting out services and datastores from a shared database for a specific application is still hard work and requires investment of time and money.

Agents can do the grinding after the pattern exists, which is huge

Agentic tooling now takes on a lot of the slow work of researching and documenting dependencies, helping build and refine proofs of concept, and executing the desired split. Researching the dependencies means tracing which parts of the product read and write the same tables, and which queries join them. A proof of concept tries one split, including what happens to a query that can no longer join inside the database. Executing the split then carries that pattern through the code and the schema for the piece the team has accepted.

The team still decides where the product splits, and what the service contracts are. Once the hardest parts of this product have a working technique, the team builds tooling that repeats it. The tooling generates the next piece of the split, checks it against the technique that already worked, and keeps the migration moving. The team feeds learnings and improvements back into the tooling each time.

New features do not have to stop for cutover

Agents also help with the problem of migrations stalling product evolution. For example, if new features still ship into the shared database while the split is in progress, the new datastores go stale. But now agents can react to PRs merging and help keep the two sides in sync. For example, an agent replays a merged feature onto the new datastore, or flags the change when the new datastore is missing the new column or the code that writes it.

I did not see this six months ago

Y’all, we all know things are changing fast, but the rate of this is still surprising to me. Six months ago I did not see the era of the shared database ending for small to medium SaaS companies anytime very soon. But today, it seems more and more obvious that it’s the time for many companies to shift away from the pattern.