網(wǎng)(LieHuo.Net)教程 首先要做的是安裝sql server 2005 開發(fā)版或?qū)I(yè)版，可以用兩臺(tái)機(jī)器，也可以在一臺(tái)機(jī)器上裝兩個(gè)sql server instance(實(shí)例)，我這里是采用的兩臺(tái)機(jī)器進(jìn)行的，機(jī)器名分別為RENHU和CHINA-E931FACA9，在RENHU上裝了sql server instance A，在CHINA-E931FACA9上裝了B。安裝的時(shí)候注意開啟sql server和windows混合驗(yàn)證。
確保兩臺(tái)機(jī)器，通過機(jī)器名能夠ping 通(在CHINA-E931FACA9上能ping通RENHU，在RENHU上能ping通CHINA-E931FACA9)，否則修改host文件，確保兩臺(tái)機(jī)器能夠ping通。
運(yùn)行sql server Configureation Manager，使TCP/IP enable，如下圖：
兩臺(tái)機(jī)器都要能要做，然后重啟Sql server、Sql server Browser服務(wù)(這兩個(gè)服務(wù)一定要起來(lái))，然后關(guān)閉防火墻，或設(shè)置防火墻例外。
然后試著用一臺(tái)機(jī)器去連另外一臺(tái)機(jī)器的sql server(用sql server manage studio)，例如我這里用RENHU這臺(tái)機(jī)器連CHINA-E931FACA9B，如果能連成功，則說明一切ok，如圖：
配置ok，就要進(jìn)行分布式配置了，代碼如下：
Server RENHU：
| 以下為引用的內(nèi)容： 1-- 建立數(shù)據(jù)庫(kù)，設(shè)置數(shù)據(jù)的選項(xiàng) 2CREATE DATABASE SalesDB; 3EXECUTE sp_serveroption @server='RENHUA',@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-E931FACA9B', 'SQL Server' 16EXEC sp_addlinkedsrvlogin 'CHINA-E931FACA9B','false',NULL,'xqls','wisdom317' 17 18GO 19 20-- 創(chuàng)建表結(jié)構(gòu) 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-- 添加測(cè)試數(shù)據(jù) 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-- 分配權(quán)限 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-E931FACA9B].SalesDB.dbo.SalesHistory 90 91GO 92 93-- 測(cè)試視圖 94select * from dpv_SalesHistory |
Server CHINA-E931FACA9：
| 以下為引用的內(nèi)容： 1CREATE DATABASE SalesDB; 2EXECUTE sp_serveroption @server='CHINA-E931FACA9B',@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 'RENHUA', 'SQL Server' 14EXEC sp_addlinkedsrvlogin 'RENHUA','false',NULL,'xqls','wisdom317' 15 16GO 17 18-- 創(chuàng)建表結(jié)構(gòu) 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-- 添加測(cè)試數(shù)據(jù) 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-- 分配用戶權(quán)限 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 [RENHUA].SalesDB.dbo.SalesHistory 86 87GO 88 89-- 測(cè)試視圖 90select * from dpv_SalesHistory |
程序員人生，我編程，我富裕，記住wfuyu網(wǎng)，php教程，php學(xué)習(xí)，php手冊(cè)，CMS模版制作
聲明：本站大部分內(nèi)容是作者原創(chuàng)，少部分收集于互聯(lián)網(wǎng)供大家一起學(xué)習(xí)，原版權(quán)很多不明，如有侵權(quán)請(qǐng)聯(lián)系本站，謝謝！
粵ICP備14040726號(hào)-1?? 2015-2020 程序員人生 版權(quán)所有
