SQL 如何將單租戶數據庫轉換為多租戶數據庫

我們有一個遺留應用程序。出於某種原因，有人決定為每個客戶創建一個數據庫。所以我們有 500 多個具有相同架構的數據庫。

我想轉換成一個多客戶端數據庫。原始客戶數據庫沒有 CustomerId。新的奇異數據庫可以。

我們正在考慮用視圖替換舊數據庫，因此遺留應用程序插入/更新仍然可以工作。

老客戶數據庫：

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

新報告數據庫具有 CustomerId：

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

用視圖替換舊數據庫

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

這失敗了：

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

如何在沒有觸發器的情況下讓最終語句成功？否則，我們將不得不利用某種觸發器來使其工作。也許沒有辦法輕鬆獲得統一的數據庫。

我們試圖避免更改舊的應用程式碼（插入和更新語句）。

sql-server-2016

這可以使用SESSION_CONTEXT（SQL Server 2016 中的新功能）來工作。在以前的版本CONTEXT_INFO中可以使用，但它不太健壯（不能是只讀的，每個會話只有一個插槽）。

例子

首先，DEFAULT在基礎表的CustomerId列上設置 a：

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,
);

然後定義視圖如下：

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;

每個會話都需要使用一次設置**CustomerID密鑰：sys.sp_set_session_context

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

如果密鑰在連接的生命週期內應該是不可變的（通常是真的），請使用以下@read_only選項：

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

這種安排允許您的插入查詢工作，並且還確保CustomerID在視圖用於插入或更新時始終匹配會話鍵，否則CHECK_OPTION返回錯誤。對於刪除，非法操作被忽略而不是引發錯誤。

這個答案解決了所提出的問題，但在實踐中，出於安全性、可管理性和可用性的原因，我幾乎總是更喜歡每個客戶一個數據庫。

展示： db<>fiddle

附加資訊

您還可以將此模式與行級安全性結合使用，以在沒有視圖的情況下強制執行客戶隔離。每個客戶的數據庫是軟體即服務公司（不是銀行和金融公司——客戶數據庫隔離的好處對許多其他行業沒有任何意義）最常用的模式。

• 具有彈性數據庫工具和行級安全性的多租戶應用程序- Microsoft Docs
• 雲中的 SQL Server 數據庫——單租戶還是多租戶？- Greg Low 博士
• 行級安全限制、性能和故障排除- Aaron Bertrand

引用自：https://dba.stackexchange.com/questions/216464