先要做的是安裝sql server 2005 開發版或專業版，可以用兩台機器，也可以在一台機器上裝兩個sql server instance（實例），我這裡是采用的兩台機器進行的，機器名分別為RENHU和CHINA-E931FACA9，在RENHU上裝了sql server instance A，在CHINA-E931FACA9上裝了B。安裝的時候注意開啟SQL Server和Windows混合驗證。
確保兩台機器，通過機器名能夠ping 通（在CHINA-E931FACA9上能ping通RENHU，在RENHU上能ping通CHINA-E931FACA9），否則修改host文件，確保兩台機器能夠ping通。
運行SQL Server Configureation Manager，使TCP/IP enable，如下圖：
兩台機器都要能要做，然後重啟Sql server、SQL Server Browser服務（這兩個服務一定要起來），然後關閉防火牆，或設置防火牆例外。
然後試著用一台機器去連另外一台機器的sql server（用SQL Server manage studio），例如我這裡用RENHU這台機器連CHINA-E931FACA9\B，如果能連成功，則說明一切ok，如圖：
配置ok，就要進行分布式配置了，代碼如下：
Server RENHU：
1
-- 建立數據庫，設置數據的選項
2CREATE DATABASE SalesDB;
3EXECUTE sp_serveroption @server='RENHU\A',@optname='lazy schema validation',@optvalue='true'
4CREATE LOGIN xqls WITH PassWord = 'wisdom317'
5
6GO
7
8-- 建立用戶
9USE SalesDB
10CREATE USER xqls FROM LOGIN xqls
11
12GO
13
14-- 連接到B
15EXECUTE sp_addlinkedserver 'CHINA-E931FACA9\B', 'SQL Server'
16EXEC sp_addlinkedsrvlogin 'CHINA-E931FACA9\B','false',NULL,'xqls','wisdom317'
17
18GO
19
20-- 創建表結構
21IF OBJECT_ID('SalesHistory','U') > 0
22
23DROP TABLE SalesHistory
24
25GO
26
27CREATE TABLE SalesHistory
28
29(
30 SaleID INT PRIMARY KEY,
31 Product VARCHAR(30) NOT NULL,
32 SaleDate DATETIME,
33 SalePrice MONEY,
34 Region VARCHAR(5) NOT NULL,
35 CONSTRAINT chk_Region CHECK (SaleID <20000)
36
37)
38
39GO
40
41-- 添加測試數據
42DECLARE @i SMALLINT, @Region VARCHAR(5)
43
44SET @i = 1
45
46SET @Region = 'West'
47
48
49
50WHILE (@i <=6000)
51
52BEGIN
53 INSERT INTO SalesHistory
54 (SaleID, Product, SaleDate, SalePrice, Region)
55 VALUES
56
57(@i, 'Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57), @Region )
58 SET @i = @i + 1
59 INSERT INTO SalesHistory
60 (SaleID, Product, SaleDate, SalePrice, Region)
61 VALUES
62 (@i, 'BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13), @Region)
63 SET @i = @i + 1
64 INSERT INTO SalesHistory
65 (SaleID, Product, SaleDate, SalePrice, Region)
66 VALUES
67 (@i, 'PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29), @Region )
68 SET @i = @i + 1
69
70END
71
72GO
73
74-- 分配權限
75GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SalesHistory TO xqls
76
77GO
78
79-- 建立視圖
80CREATE VIEW dpv_SalesHistory
81
82AS
83 SELECT
84 SaleID, Product, Region, SaleDate, SalePrice
85 FROM SalesDB.dbo.SalesHistory
86 UNION ALL
87 SELECT
88 SaleID, Product, Region, SaleDate, SalePrice
89 FROM [CHINA-E931FACA9\B].SalesDB.dbo.SalesHistory
90
91GO
92
93-- 測試視圖
94select * from dpv_SalesHistory
Server CHINA-E931FACA9：
1CREATE DATABASE SalesDB;
2EXECUTE sp_serveroption @server='CHINA-E931FACA9\B',@optname='lazy schema validation',@optvalue='true'
3CREATE LOGIN xqls WITH PassWord = 'wisdom317'
4
5GO
6
7USE SalesDB
8CREATE USER xqls FROM LOGIN xqls
9
10GO
11
12-- 連接到A
13EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server'
14EXEC sp_addlinkedsrvlogin 'RENHU\A','false',NULL,'xqls','wisdom317'
15
16GO
17
18-- 創建表結構
19IF OBJECT_ID('SalesHistory','U') > 0
20
21DROP TABLE SalesHistory
22
23GO
24
25CREATE TABLE SalesHistory
26
27(
28 SaleID INT PRIMARY KEY,
29 Product VARCHAR(30) NOT NULL,
30 SaleDate DATETIME,
31 SalePrice MONEY,
32 Region VARCHAR(5) NOT NULL,
33 CONSTRAINT chk_Region CHECK (SaleID >=20000)
34
35)
36
37GO
38
39
40-- 添加測試數據
41DECLARE @i SMALLINT, @Region VARCHAR(5)
42
43SET @i = 20000
44
45SET @Region = 'East'
46
47
48
49WHILE (@i <=26000)
50
51BEGIN
52 INSERT INTO SalesHistory
53 (SaleID, Product, SaleDate, SalePrice, Region)
54 VALUES
55 (@i, 'Computer', DATEADD(mm, @i, '3/11/1919'), DATEPART(ms, GETDATE()) + (@i + 57), @Region )
56 SET @i = @i + 1
57 INSERT INTO SalesHistory
58 (SaleID, Product, SaleDate, SalePrice, Region)
59 VALUES
60 (@i, 'BigScreen', DATEADD(mm, @i, '3/11/1927'), DATEPART(ms, GETDATE()) + (@i + 13), @Region)
61 SET @i = @i + 1
62 INSERT INTO SalesHistory
63 (SaleID, Product, SaleDate, SalePrice, Region)
64 VALUES
65 (@i, 'PoolTable', DATEADD(mm, @i, '3/11/1908'), DATEPART(ms, GETDATE()) + (@i + 29), @Region )
66 SET @i = @i + 1
67
68END
69
70-- 分配用戶權限
71GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SalesHistory TO xqls
72
73GO
74
75-- 建立視圖
76CREATE VIEW dpv_SalesHistory
77
78AS
79 SELECT
80 SaleID, Product, Region, SaleDate, SalePrice
81 FROM SalesDB.dbo.SalesHistory
82 UNION ALL
83 SELECT
84 SaleID, Product, Region, SaleDate, SalePrice
85 FROM [RENHU\A].SalesDB.dbo.SalesHistory
86
87GO
88
89-- 測試視圖
90select * from dpv_SalesHistory
