Sql-server – SQL How to Convert Single Tenant Databases to a Multitenant Database

database-designsql serversql-server-2008sql-server-2012sql-server-2016

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).

Best Answer

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).

Related Question