WEB开发网
开发学院数据库MSSQL Server 配置SQL SERVER 2005 分布式水平分片 阅读

配置SQL SERVER 2005 分布式水平分片

 2009-10-08 00:00:00 来源:WEB开发网   
核心提示: Server CHINA-E931FACA9:1CREATEDATABASESalesDB;2EXECUTEsp_serveroption@server='CHINA-E931FACA9\B',@optname='lazyschemavalidation',@op

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

上一页  1 2 

Tags:配置 SQL SERVER

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接