首先要做的是安装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,如下图:
![](https://images.cnblogs.com/cnblogs_com/youwang/1.JPG)
两台机器都要能要做,然后重启Sql server、Sql server Browser服务(这两个服务一定要起来),然后关闭防火墙,或设置防火墙例外。
然后试着用一台机器去连另外一台机器的sql server(用sql server manage studio),例如我这里用RENHU这台机器连CHINA-E931FACA9\B,如果能连成功,则说明一切ok,如图:
![](https://images.cnblogs.com/cnblogs_com/youwang/2.JPG)
配置ok,就要进行分布式配置了,代码如下:
Server RENHU:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
-- 建立数据库,设置数据的选项
2
CREATE DATABASE SalesDB;
3
EXECUTE sp_serveroption @server='RENHU\A',@optname='lazy schema validation',@optvalue='true'
4
CREATE LOGIN xqls WITH Password = 'wisdom317'
5![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
6
GO
7![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
8
-- 建立用户
9
USE SalesDB
10
CREATE USER xqls FROM LOGIN xqls
11![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
GO
13![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
14
-- 连接到B
15
EXECUTE sp_addlinkedserver 'CHINA-E931FACA9\B', 'SQL Server'
16
EXEC sp_addlinkedsrvlogin 'CHINA-E931FACA9\B','false',NULL,'xqls','wisdom317'
17![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
GO
19![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
20
-- 创建表结构
21
IF OBJECT_ID('SalesHistory','U') > 0
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
DROP TABLE SalesHistory
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
GO
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
27
CREATE TABLE SalesHistory
28![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
)
38![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39
GO
40![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
41
-- 添加测试数据
42
DECLARE @i SMALLINT, @Region VARCHAR(5)
43![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
44
SET @i = 1
45![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
46
SET @Region = 'West'
47![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
50
WHILE (@i <=6000)
51![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
52
BEGIN
53
INSERT INTO SalesHistory
54
(SaleID, Product, SaleDate, SalePrice, Region)
55
VALUES
56![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
END
71![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
72
GO
73![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
74
-- 分配权限
75
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SalesHistory TO xqls
76![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
77
GO
78![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
79
-- 建立视图
80
CREATE VIEW dpv_SalesHistory
81![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
82
AS
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
91
GO
92![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
93
-- 测试视图
94
select * from dpv_SalesHistoryServer CHINA-E931FACA9:
![](https://www.cnblogs.com/Images/OutliningIndicators/ContractedBlock.gif)
Code
1
CREATE DATABASE SalesDB;
2
EXECUTE sp_serveroption @server='CHINA-E931FACA9\B',@optname='lazy schema validation',@optvalue='true'
3
CREATE LOGIN xqls WITH Password = 'wisdom317'
4![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
5
GO
6![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
7
USE SalesDB
8
CREATE USER xqls FROM LOGIN xqls
9![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
10
GO
11![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
12
-- 连接到A
13
EXECUTE sp_addlinkedserver 'RENHU\A', 'SQL Server'
14
EXEC sp_addlinkedsrvlogin 'RENHU\A','false',NULL,'xqls','wisdom317'
15![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
16
GO
17![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
18
-- 创建表结构
19
IF OBJECT_ID('SalesHistory','U') > 0
20![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
21
DROP TABLE SalesHistory
22![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
23
GO
24![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
25
CREATE TABLE SalesHistory
26![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
35
)
36![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
37
GO
38![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
39![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
40
-- 添加测试数据
41
DECLARE @i SMALLINT, @Region VARCHAR(5)
42![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
43
SET @i = 20000
44![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
45
SET @Region = 'East'
46![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
47![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
48![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
49
WHILE (@i <=26000)
50![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
51
BEGIN
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
68
END
69![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
70
-- 分配用户权限
71
GRANT SELECT, INSERT, UPDATE, DELETE, VIEW DEFINITION ON SalesHistory TO xqls
72![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
73
GO
74![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
75
-- 建立视图
76
CREATE VIEW dpv_SalesHistory
77![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
78
AS
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![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
87
GO
88![](https://www.cnblogs.com/Images/OutliningIndicators/None.gif)
89
-- 测试视图
90
select * from dpv_SalesHistory具体参考:
http://www.builder.com.cn/2007/0406/385486.shtml