SQL Server 2005/2008/2012中應用分散式資料分割檢視

標籤：ring   option   copyto   擷取   src   efi   環境   lin   width   

　　自2000版本起，SQL Server企業版中引入分散式資料分割檢視，允許你為分布在不同的SQL 執行個體的兩個或多個水平資料分割表建立視圖。

　　簡要步驟如下：根據Check約束中定義的一組值把大表分割成更小的一些表。Check約束確保每個小表儲存著不能儲存在其他表的唯一資料。然後使用Union All建立分散式資料分割檢視，把所有這些小表連接成單獨的結果集。

　　這樣對效能的改善是有益的，例如，如果視圖根據日期分區，並用查詢來返回僅儲存在一個分區表中的行，那麼SQL Server會智能地只搜尋一個分區而不是分散式資料分割檢視中的所有表。

　　我們假設一個情境，某公司成立上海和北京分公司，分別有各自的SQL Server執行個體來儲存網站資料，都希望用一個表跟蹤網網站擊。點擊量非常大。此時，需要一個視圖以在單個視圖中引用各自的表。公司希望能查詢任意一個伺服器，並且返回相同的資料或各自分公司的資料。

　　下面我們通過執行個體來示範這個情境的具體應用。假定有兩個執行個體：AP4\NET2012和AP4\NET2013（本文所有樣本均在SQL Server 2008環境下通過）。

一、建立連結的伺服器，當然也可以通過OpenRowset（http://msdn.microsoft.com/zh-cn/library/ms190312.aspx）而不建立連結的伺服器，並建立測試資料庫和表。

[sql] view plain copy 

1. /********* 建立一個分散式資料分割檢視 ***************/  
2. /********* [email protected] 邀月 ***************/  
3.   
4. USE master  
5. GO  
6. EXEC sp_addlinkedserver  
7. ‘AP4\NET2013‘,  
8. N‘SQL Server‘  
9. GO  
10.   
11. -- 跳過遠程執行個體架構表的檢查，以提升效能，邀月注  
12. EXEC sp_serveroption ‘AP4\NET2013‘, ‘lazy schema validation‘, ‘true‘  
13. GO  
14.   
15. --建立測試資料庫  
16. IF NOT EXISTS (SELECT name  
17. FROM sys.databases  
18. WHERE name = ‘RemoteViewTest2012‘)  
19. BEGIN  
20. CREATE DATABASE RemoteViewTest2012  
21. END  
22. GO  
23. --開啟測試庫  
24. Use RemoteViewTest2012  
25. GO  
26.   
27. --建立上海分公司的點擊表  
28. CREATE TABLE dbo.WebHits_ShangHai  
29. (WebHitID uniqueidentifier NOT NULL,  
30. WebSite varchar(20) NOT NULL ,  
31. HitDT datetime NOT NULL,  
32. CHECK (WebSite = ‘ShangHai‘),  
33. CONSTRAINT PK_WebHits PRIMARY KEY (WebHitID, WebSite))  

 

第二個執行個體：

[sql] view plain copy 

1. /***************　執行個體 AP4\NET2013(SQL Server 2008) *********/  
2. /********* [email protected] 邀月 ***************/  
3. USE master  
4. GO  
5. EXEC sp_addlinkedserver  
6. ‘AP4\NET2012‘,  
7. N‘SQL Server‘  
8. GO  
9.   
10. -- 跳過遠程執行個體架構表的檢查，以提升效能，邀月注  
11. EXEC sp_serveroption ‘AP4\NET2012‘, ‘lazy schema validation‘, ‘true‘  
12. GO  
13.   
14. IF NOT EXISTS (SELECT name  
15. FROM sys.databases  
16. WHERE name = ‘RemoteViewTest2012‘)  
17. BEGIN  
18. CREATE DATABASE RemoteViewTest2012  
19. END  
20. GO  
21.   
22. --開啟測試庫  
23. Use RemoteViewTest2012  
24. GO  
25.   
26. --建立北京分公司的點擊表  
27. CREATE TABLE dbo.WebHits_BeiJing  
28. (WebHitID uniqueidentifier NOT NULL,  
29. WebSite varchar(20) NOT NULL ,  
30. HitDT datetime NOT NULL,  
31. CHECK (WebSite = ‘BeiJing‘),  
32. CONSTRAINT PK_WebHits PRIMARY KEY (WebHitID, WebSite))  

 

二、在兩個執行個體中分別建立視圖

[sql] view plain copy 

1. /***************　執行個體 AP4\NET2012(SQL Server 2008) *********/  
2. /********* [email protected] 邀月 ***************/  
3.   
4. --開啟測試庫  
5. Use RemoteViewTest2012  
6. GO  
7.   
8. --建立分區視圖  
9. CREATE VIEW dbo.v_WebHits AS  
10. SELECT WebHitID,  
11. WebSite,  
12. HitDT  
13. FROM RemoteViewTest2012.dbo.WebHits_ShangHai  
14. UNION ALL  
15. SELECT WebHitID,  
16. WebSite,  
17. HitDT  
18. FROM [AP4\NET2013].RemoteViewTest2012.dbo.WebHits_BeiJing  
19. GO  
20.   
21. /***************　執行個體 AP4\NET2013(SQL Server 2008) *********/  
22. /********* [email protected] 邀月 ***************/  
23.   
24. --開啟測試庫  
25. Use RemoteViewTest2012  
26. GO  
27.   
28. --建立分區視圖  
29. CREATE VIEW dbo.v_WebHits AS  
30. SELECT WebHitID,  
31. WebSite,  
32. HitDT  
33. FROM RemoteViewTest2012.dbo.WebHits_BeiJing  
34. UNION ALL  
35. SELECT WebHitID,  
36. WebSite,  
37. HitDT  
38. FROM [AP4\NET2012].RemoteViewTest2012.dbo.WebHits_ShangHai  
39. GO  

 

三、插入測試資料

我們可以選擇任意一個執行個體中插入，下面我們選擇AP4\NET2013

 

[sql] view plain copy 

1. /***************　執行個體 AP4\NET2013(SQL Server 2008) *********/  
2. /********* [email protected] 邀月 ***************/  
3. ----要保證插入，必須開啟XACT_ABORT開關，並開啟分散式交易協調器，邀月注  
4.   
5. --開啟測試庫  
6. Use RemoteViewTest2012  
7. GO  
8.   
9. SET XACT_ABORT ON  
10. INSERT dbo.v_WebHits  
11. (WebHitID, WebSite, HitDT)  
12. VALUES(NEWID(), ‘ShangHai‘, GETDATE())  
13.   
14. INSERT dbo.v_WebHits  
15. (WebHitID, WebSite, HitDT)  
16. VALUES(NEWID(), ‘BeiJing‘, GETDATE())  
17.   
18.    

　　注意，如果該執行個體所在的伺服器上沒有啟用MSDTC（Microsoft 分散式交易協調器），會拋出一個錯誤：

 

　　此時在命令列中輸入Net start msdtc以啟用該服務。

　　如果還是不能正常啟動MSDTC，請查閱MSDN（http://msdn.microsoft.com/zh-cn/library/aa561924%28BTS.10%29.aspx）以擷取協助。

 

四、進行分散式查詢

　　此時，我們在任意一個執行個體查詢的結果都是一致的，也正是我們想要的。

[sql] view plain copy 

1. /***************　執行個體 AP4\NET2013(SQL Server 2008) *********/  
2. /********* [email protected] 邀月 ***************/  
3. /***** 分散式查詢 　**************/  
4.   
5. ----AP4\NET2013上查詢  
6. --開啟測試庫  
7. Use RemoteViewTest2012  
8. GO  
9. SET XACT_ABORT ON  
10.   
11. SELECT WebHitID, WebSite, HitDT  
12. FROM dbo.v_WebHits  
13.   
14. SELECT WebHitID, WebSite, HitDT  
15. FROM [AP4\NET2012].RemoteViewTest2012.dbo.WebHits_ShangHai   

[sql] view plain copy 

1. ----AP4\N ET2012上查詢  
2. --開啟測試庫  
3. Use RemoteViewTest2012  
4. GO  
5.   
6. SET XACT_ABORT ON  
7.   
8. SELECT WebHitID, WebSite, HitDT  
9. FROM dbo.v_WebHits  
10.   
11. SELECT WebHitID, WebSite, HitDT  
12. FROM [AP4\NET2013].RemoteViewTest2012.dbo.WebHits_BeiJing  

SQL Server 2005/2008/2012中應用分散式資料分割檢視