SQL Server 2005 分散式水平分片實作：連結伺服器 + 檢查約束 + 分散式視圖

架構規劃

• 兩台實體機器：RENHU\A、CHINA-E931FACA9\B
• 各自建立 SalesDB、登入 xqls、資料表 SalesHistory
• 依 SaleID 範圍分片：
  • A 節點：SaleID < 20000，Region = 'West'
  • B 節點：SaleID >= 20000，Region = 'East'
• 雙向建立連結伺服器，各節點建立 dpv_SalesHistory 分散式視圖（UNION ALL）

前置設定

1. 安裝 Developer/Enterprise 版，啟用 TCP/IP，開啟 SQL Server / Browser 服務，關閉防火牆
2. 雙向 Ping 通（必要時修改 hosts）
3. SSMS 測試跨實例連線成功

節點 A (RENHU\A) 完整腳本

-- 1. 建立資料庫與選項
CREATE DATABASE SalesDB;
EXECUTE sp_serveroption @server='RENHU\A', @optname='lazy schema validation', @optvalue='true';

-- 2. 登入與使用者
CREATE LOGIN xqls WITH PASSWORD = 'wisdom317';
GO
USE SalesDB;
CREATE USER xqls FROM LOGIN xqls;
GO

-- 3. 連結到 B
EXECUTE sp_addlinkedserver 'CHINA-E931FACA9\B', 'SQL Server';
EXEC sp_addlinkedsrvlogin 'CHINA-E931FACA9\B', 'false', NULL, 'xqls', 'wisdom317';
GO

-- 4. 建立分片表（含 CHECK 約束）
IF OBJECT_ID('SalesHistory','U') > 0 DROP TABLE SalesHistory;
GO
CREATE TABLE SalesHistory (
    SaleID INT PRIMARY KEY,
    Product VARCHAR(30) NOT NULL,
    SaleDate DATETIME,
    SalePrice MONEY,
    Region VARCHAR(5) NOT NULL,
    CONSTRAINT chk_Region CHECK (SaleID < 20000)
);
GO

-- 5. 測試資料（West，ID 1~6000）
DECLARE @i SMALLINT, @Region VARCHAR(5);
SET @i = 1; SET @Region = 'West';
WHILE @i <= 6000
BEGIN
    INSERT INTO SalesHistory VALUES (@i, 'Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i+57), @Region); SET @i += 1;
    INSERT INTO SalesHistory VALUES (@i, 'BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i+13), @Region); SET @i += 1;
    INSERT INTO SalesHistory VALUES (@i, 'PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i+29), @Region); SET @i += 1;
END
GO

-- 6. 權限
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SalesHistory TO xqls;
GO

-- 7. 分散式視圖
CREATE VIEW dpv_SalesHistory AS
SELECT SaleID, Product, Region, SaleDate, SalePrice FROM SalesDB.dbo.SalesHistory
UNION ALL
SELECT SaleID, Product, Region, SaleDate, SalePrice FROM [CHINA-E931FACA9\B].SalesDB.dbo.SalesHistory;
GO

-- 8. 測試
SELECT * FROM dpv_SalesHistory;

節點 B (CHINA-E931FACA9\B) 完整腳本

CREATE DATABASE SalesDB;
EXECUTE sp_serveroption @server='CHINA-E931FACA9\B', @optname='lazy schema validation', @optvalue='true';
CREATE LOGIN xqls WITH PASSWORD = 'wisdom317';
GO
USE SalesDB;
CREATE USER xqls FROM LOGIN xqls;
GO
EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server';
EXEC sp_addlinkedsrvlogin 'RENHU\A', 'false', NULL, 'xqls', 'wisdom317';
GO

IF OBJECT_ID('SalesHistory','U') > 0 DROP TABLE SalesHistory;
GO
CREATE TABLE SalesHistory (
    SaleID INT PRIMARY KEY,
    Product VARCHAR(30) NOT NULL,
    SaleDate DATETIME,
    SalePrice MONEY,
    Region VARCHAR(5) NOT NULL,
    CONSTRAINT chk_Region CHECK (SaleID >= 20000)
);
GO

DECLARE @i SMALLINT, @Region VARCHAR(5);
SET @i = 20000; SET @Region = 'East';
WHILE @i <= 26000
BEGIN
    INSERT INTO SalesHistory VALUES (@i, 'Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i+57), @Region); SET @i += 1;
    INSERT INTO SalesHistory VALUES (@i, 'BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i+13), @Region); SET @i += 1;
    INSERT INTO SalesHistory VALUES (@i, 'PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i+29), @Region); SET @i += 1;
END
GO

GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SalesHistory TO xqls;
GO

CREATE VIEW dpv_SalesHistory AS
SELECT SaleID, Product, Region, SaleDate, SalePrice FROM SalesDB.dbo.SalesHistory
UNION ALL
SELECT SaleID, Product, Region, SaleDate, SalePrice FROM [RENHU\A].SalesDB.dbo.SalesHistory;
GO

SELECT * FROM dpv_SalesHistory;

關鍵技術點

• lazy schema validation：延遲結構描述驗證，提升分散式查詢效能
• CHECK 約束：查詢最佳化器利用約束進行分區裁剪，只存取相關節點
• UNION ALL 視圖：提供單一查詢入口，應用層無感知
• 雙向連結伺服器：支援就地更新（需啟用分散式交易 MSDTC）