SQL How to Convert Single Tenant Databases to a Multitenant Database

Question

We have a legacy application. For some reason, someone decided to create a database for each customer. So we have 500+ databases with the same schema.

I would like to convert into One multi-client database. The Original customer database does not have CustomerId. New Singular database does.

We are thinking of replacing old databases with Views, so the legacy application insert/update will still work.

Old Customer Database:

CREATE TABLE [CustomerOne].[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [QuantityBought] [int] NULL,
)

New Reporting Database has CustomerId:

CREATE TABLE ReportingDB.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL,
    [QuantityBought] [int] NULL,
)

Replace Old Database with Views

create view [CustomerOne].[dbo].[CustomerTransaction]
as
select 
    [CustomerTransactionid]
    ,1 as [CustomerId]
    ,[quantitybought]
from ReportingDB.[dbo].[CustomerTransaction]
where Customerid = 1
with check option

This Fails:

-- Attempt inserting into View
insert into CustomerOne.dbo.Customertransaction 
(Quantitybought)
values (4)

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'customerid', table 'ReportingDB.dbo.customertransaction'; column does not allow nulls. INSERT fails.
The statement has been terminated.

How do I get the final statement to succeeed without a Trigger? Otherwise we will have to utilize some kind trigger to get it working. Maybe there is no way to easily obtain unified database.

We are trying to avoid changing the old application code (insert and update statements).

Answer 1

sql-server-2016

This can be made to work using SESSION_CONTEXT (new in SQL Server 2016). In previous versions CONTEXT_INFO could be used, but it is less robust (cannot be read-only, only one slot per session).

Example

First, set a DEFAULT on the underlying table's CustomerId column:

CREATE TABLE ReportingDB.[dbo].[CustomerTransaction]
(
    [CustomerTransactionid] [int] identity(1,1) primary key NOT NULL,
    [Customerid] [int] NOT NULL
        DEFAULT TRY_CONVERT(integer, SESSION_CONTEXT(N'CustomerID')),
    [QuantityBought] [int] NULL,
);

Then define the view as follows:

CREATE OR ALTER VIEW dbo.CustomerOneTransaction
AS
    SELECT
        CT.CustomerTransactionid,
        CT.Customerid,
        CT.QuantityBought
    FROM ReportingDB.dbo.CustomerTransaction AS CT
    WHERE
        CT.Customerid = 1
        AND TRY_CONVERT(integer, SESSION_CONTEXT(N'CustomerID')) = 1
    WITH CHECK OPTION;

Each session would need to set the CustomerID key once using sys.sp_set_session_context:

EXECUTE sys.sp_set_session_context
    @key = N'CustomerID',
    @value = 1;

If the key should be immutable for the life of the connection (typically true), use the @read_only option:

EXECUTE sys.sp_set_session_context
    @key = N'CustomerID',
    @value = 1,
    @read_only = 1;

This arrangement allows your insert query to work, and also ensures that CustomerID always matches the session key when the view is used to insert or update, otherwise a CHECK_OPTION error is returned. For deletion, illegal operations are ignored rather than raising an error.

This answer addresses the question asked, but in practice I would almost always prefer a database per customer, for security, manageability, and availability reasons.

Demo: db<>fiddle

Additional Information

You can also use this pattern with Row-Level Security to enforce customer isolation without views. Database-per-customer is the pattern used most for Software-as-a-Service companies (not banks and finance companies - the benefits of customer database isolation don't really make any sense for many other industries).

• Multi-tenant applications with elastic database tools and row-level security - Microsoft Docs
• SQL Server Databases in the Cloud – Single Tenant or Multi Tenant? - Dr Greg Low
• Row Level Security Limitations, Performance and Troubleshooting - Aaron Bertrand

Answer 2

For some reason, someone decided to create a database for each customer. So we have 100+ databases with the same schema.

There are many very good reasons to do that.

• Per customer data isolation
• Per customer query plans and plan cache
• Per customer backup/restore
• Per customer ad-hoc reporting
• Per customer patches and upgrades
• Per customer resource governance and scalability

This is a better pattern for a multi-tenant schema:

CREATE TABLE ReportingDB.[dbo].[CustomerTransaction] (
    [Customerid] [int] NOT NULL,
    [CustomerTransactionid] [int] identity(1,1) NOT NULL,
    constraint pk_CustomerTransaction primary key (CustomerID,CustomerTransactionid),
    [QuantityBought] [int] NULL, . . . )

That way all the rows for a tenant are grouped together.

How do I get the final statement to succeeed without a Trigger?

You don't. You would need an INSTEAD OF trigger to make that work.

Answer 3

From Paul White Answer below

For SQL Server2000-SQL 2012 and using CONTEXT_INFO

Please code review:

View:

CREATE view dbo.CustomerOneTransaction
AS
    SELECT
        CT.CustomerTransactionid,
        CT.Customerid,
        CT.QuantityBought
    FROM ReportingDB.dbo.CustomerTransaction AS CT
    WHERE
        CT.Customerid = 1
        AND TRY_CONVERT(integer, convert(int,CONTEXT_INFO())) = 1
    WITH CHECK OPTION;

For Each Session:

DECLARE @BinVar varbinary(128);  
SET @BinVar = CONVERT(binary(128),1)
SET CONTEXT_INFO @BinVar;