偷偷摘套内射激情视频,久久精品99国产国产精,中文字幕无线乱码人妻,中文在线中文a,性爽19p

詳解SQL Server 2008復制分區(qū)清理數據

數據庫 SQL Server 數據庫運維
本文將介紹SQL Server 2008復制分區(qū)SWITCH清理數據,希望通過本文能讓大家對于復制分區(qū)提高效率有所了解。

51CTO數據庫頻道向您推薦《SQL Server 2008深度應用》和《SQL Server入門到精通》兩個專題讓您更深刻的了解本文。

場景:

某種特定業(yè)務下,我們的部分業(yè)務數據可能只會保留比較短的時間,用來做臨時處理。因為考慮高可用的特性,可能會利用 SQL Server的復制組件復制這種數據到另外的 類似前端,查詢中心等數據庫服務器,創(chuàng)建一個冗余副本。復制組件標記事務日志,追蹤所有的Update,Insert,Delete操作??墒侨绻欢ㄆ谇謇磉@種表,那么在一個快速增長的環(huán)境下,表變的臃腫不堪,不僅僅浪費磁盤空間,而且給性能帶來了負面影響。

如:

A臨時數據中心   ---同步-->B 查詢/其他業(yè)務中心,副本

保證A不可用的時候,B的業(yè)務不依賴于A服務器,利用A同步過來的數據B自己直接處理。

常見的解決方案:

一、很簡單的,我們可以想到,建立一個清理Job ,根據日期標識,確定刪除的范圍不出一點錯誤,比如我每天的晚上12:00以后清理昨天的數據,那么可能會構建這樣的查詢(朋友們,也許會說使用TRUNCATE ,準時的12:00來TRUNCATE掉,不過很不幸的是,SQL Server 復制是利用日志標記的方式來同步數據,而TRUNCATE語句不會被日志標記,所以TRUNCATE語句在復制表中是被禁用的)

 

  1. DELETE FROM dbo.t WHERE complete_time >= '2010-05-04' 
  2. AND complete_time < '2010-05-05' 

來刪除5月4號的所有數據,可能上億也可能更多。

優(yōu)化方案:因為考慮到存在帶量的更新 刪除 和插入操作,所以首先講數據庫設置成SIMPLE恢復模式,以最小化日志方式。

由于DELETE單個語句是一個事務性的語句,要么全部成功,要么全部失敗。那么可想如果刪除的是億級別的數據,那么日志增長,IO負荷非常的大。

所以 可能優(yōu)化DELETE 操作:

  1. WHILE 1=1  
  2. BEGIN 
  3.     DELETE TOP(5000) FROM dbo.t  
  4.     WHERE complete_time >= '2010-05-04' AND complete_time < '2010-05-05' 
  5.     IF @@ROWCOUNT<5000  
  6.          BREAK;  
  7. END 

將刪除操作,縮短成每5000行一批的數據來處理。

缺點:因為存在復制,那么很顯然,浪費了極多的帶寬,特別在Internet這種環(huán)境下,利用VPN的連接,尤其浪費。VPN帶寬十分的渺小。再者,在進行億級的DELETE,時間耗費也是非??植赖?,而且DELETE過程中,由于存在Range-X 范圍排他鎖,許多開發(fā)人員寫的SQL Query沒加上WITH(NOLOCK)或者需要事務處理的查詢語句,都將被阻塞。

二、刪除復制,TRUNCATE表。我們可以直接刪除復制的發(fā)布和訂閱,然后講兩邊的表都直接TRUNCATE掉,這種刪除方式非常的高效,只不過,要確保其他新增的數據不被誤刪,也存在一定的風險,構思如下:

A數據庫 每天需要清理 前天數據,B服務器通過訂閱PUSH獲取同步數據。

因此Job 的建立有點復雜,尤其 當12點突然宕機的時候,數據沒有及時清理,或者12:00執(zhí)行TRUNCATE的時候,下一天的00:00:01秒的數據被插入了,等等數據完整性問題,讓我采用這個方案回避了。

缺點:估計這個Job不怎么好寫,首先是自動刪除復制,重新創(chuàng)建復制,創(chuàng)建訂閱,雖然利用Generate Script可以到處腳本,但是整個解決方案過于復雜了。

新方案:進入主題了,在SQL Server 中分區(qū)表有一個非常 實用的語句ALTER TABLE …SWITCH,這個DDL可以快速的講同文件組的表的某個分區(qū)迅速的轉移到另外的表。(很顯然,應該是利用數據的位置偏移量的指針的轉移到新表的方法來實現的),這種方案轉移數據非常迅速,因為不存在大量的IO操作,所以一般都是秒殺級別任意大數據量(如果當前表的索引等比較多,維護操作還是需要點時間的)。但是SQL Server 2005 是不支持在復制中使用SWITCH語句的(這點很郁悶,命名都可以跟蹤DDL,為什么這個不能傳送到訂閱服務器,只要確認發(fā)布和訂閱同時存在目標表和相同的分區(qū)方案不就好了,微軟2005考慮不周啊)。

但是前不久我在考慮我們每天億級別的數據增長,清理數據方案變的十分迫切的時候,我發(fā)現SQL Server 2008支持這種操作。下面就開始整個Solution吧:

開始方案前,大家其實根據自己的業(yè)務來建立分區(qū)方案。我采用的是疊代追加 的方式來擴展以及清理分區(qū)的。

當然我也看過類似 使用根據某列鍵列 建立計算列并且講計算列PERSISTED 之后分區(qū)的。(可以緊靠業(yè)務,自己處理,我在這里展示一種,根據ID自增方案分區(qū)擴張的方案)。

腳本:

 

  1. /*  
  2. Title:Rapid Delete In The Replication  
  3. Author:浪客  
  4. Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise  
  5. Description:請在非生產環(huán)境下測試  
  6. */  
  7.  
  8. USE [master]  
  9. GO 


--我們創(chuàng)建包含PRIMARY分區(qū)在內一共3分區(qū)的數據庫
 

  1. CREATE DATABASE [db_partition_test]   
  2. ON  PRIMARY   
  3. (   
  4.     NAME = N'db_partition_test',   
  5.     FILENAME = N'C:\db_partition_test.mdf' 
  6. ),   
  7. FILEGROUP [FG1]   
  8. (   
  9.     NAME = N'db_partition_test_fg1_1',   
  10.     FILENAME = N'C:\db_partition_test_fg1_1.ndf' 
  11. ),   
  12.  FILEGROUP [FG2]   
  13. (   
  14.     NAME = N'db_partition_test_fg2_1',   
  15.     FILENAME = N'C:\db_partition_test_fg2_1.ndf' 
  16. )  
  17.  LOG ON   
  18. (   
  19.     NAME = N'db_partition_test_log',   
  20.     FILENAME = N'C:\db_partition_test_log.ldf' 
  21. )  
  22. GO  
  23.  
  24. USE [db_partition_test]  
  25. GO 


--創(chuàng)建分區(qū)函數,分區(qū)范圍為  id<=100 |  100<id<=200  | id>200
 

  1. CREATE PARTITION FUNCTION [id_range_pf_1](intAS RANGE LEFT FOR VALUES (100, 200)  
  2. GO 


--創(chuàng)建分區(qū)方案 ,分區(qū)一到PRIMARY,分區(qū)二到FG1,分區(qū)三到FG2
 
  1. CREATE PARTITION SCHEME [id_range_ps_1] AS PARTITION [id_range_pf_1] TO ([PRIMARY],FG1,FG2);  
  2. GO 

--創(chuàng)建分區(qū)表

  1. CREATE TABLE dbo.lovesql  
  2. (  
  3.     ID INT IDENTITY(1,1) NOT FOR REPLICATION NOT NULL PRIMARY KEY,  
  4.     Col Uniqueidentifier DEFAULT(NEWID())  
  5. ON id_range_ps_1(ID) 

--插入測試數據 300條

  1. INSERT INTO dbo.lovesql DEFAULT VALUES 
  2. GO 300 

--查看表分區(qū)以后的分區(qū)行數

  1. SELECT $PARTITION.id_range_pf_1(ID) [PartitionNum],COUNT(1) [PartitionRowCount]  
  2. FROM dbo.lovesql  
  3. GROUP BY $PARTITION.id_range_pf_1(ID)  
  4. ORDER BY [PartitionNum] 


-輸入結果,每個分區(qū)100條數據,分區(qū)正確!
--PartitionNum PartitionRowCount
-------------- -----------------
--1            100
--2            100
--3            100

--現在開始建立復制,首先建立目標數據repl_db_partition_test
 

  1. USE [master];  
  2. GO  
  3.  
  4. CREATE DATABASE [repl_db_partition_test]   
  5. ON  PRIMARY   
  6. (   
  7.     NAME = N'repl_db_partition_test',   
  8.     FILENAME = N'C:\repl_db_partition_test.mdf' 
  9. ),   
  10. FILEGROUP [FG1]   
  11. (   
  12.     NAME = N'repl_db_partition_test_fg1_1',   
  13.     FILENAME = N'C:\repl_db_partition_test_fg1_1.ndf' 
  14. ),   
  15.  FILEGROUP [FG2]   
  16. (   
  17.     NAME = N'repl_db_partition_test_fg2_1',   
  18.     FILENAME = N'C:\repl_db_partition_test_fg2_1.ndf' 
  19. )  
  20.  LOG ON   
  21. (   
  22.     NAME = N'repl_db_partition_test_log',   
  23.     FILENAME = N'C:\repl_db_partition_test_log.ldf' 
  24. )  
  25. GO 

上面已經創(chuàng)建好了,基本的測試環(huán)境,那么開始建立復制。

在Object Explorer打開連接的實例中的Replication(復制)文件夾à右鍵新建發(fā)布à在向導中選擇Publication Database為db_encryption_testà選擇事務復制à在Article中選擇lovesql表->在右手邊的Article Properties設置表的發(fā)布屬性à配置如下:

下一步跳過Filter Table Rowsà在Snapshot Agent中,選中第一個復選框à在Agent Security中選擇自己設置的帳號吧,我這里選擇了Run under the SQL Server Agent services count(記得,如果你的Sql Agent對應的Windows 帳號沒有源表以及distribution表的db_owner權限,以及沒有快照文件夾的權限,會出錯的,具體的可以自己參看MSDN如何Security Settingà給Publications取一個名字,就叫l(wèi)ovesql吧。

接下來建立db_encryption_test 到 repl_db_encryption_test的訂閱:

在Object Explore中找到Replication/Local Publications文件夾下,找到你的發(fā)布,然后右鍵新建訂閱à一直下一步知道出現Subscribers,然后新添加一個訂閱 指向本地的repl_db_encryption_testà在安全中選擇Run under the sql server agent service count,然后一直下一步,直到成功。

如果3個代理都成功了(Snapshot代理,Log Read代理,Distribute代理),那么看看repl_db_encryption_test下的表lovesql是否存在,右鍵屬性查看 他的Storage 里面是否已經分區(qū)了

腳本執(zhí)行:

 

  1.  /*  
  2. Title:Rapid Delete In The Replication  
  3. Author:浪客  
  4. Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise  
  5. Description:請在非生產環(huán)境下測試  
  6. */  
  7. USE db_partition_test;  
  8. GO 



--默認發(fā)布是不會啟用 “分區(qū)切換”以及分區(qū)DDL的。所以修改發(fā)布的屬性

  1. EXEC  sp_changepublication @publication=N'lovesql',@property=N'allow_partition_switch',@value=N'true'

--啟用SWITCH DDL 復制

  1. EXEC  sp_changepublication @publication=N'lovesql',@property=N'replicate_partition_switch',@value=N'true'

--確保發(fā)布和訂閱兩邊的數據庫都存在一個 臨時表,而且必須是空數據的臨時表 用來快速切換分區(qū)使用
--確保分區(qū)的表,和切換分區(qū)的表使用的是同一個FILEGROUP
--

  1. USE db_partition_test;  
  2. GO  
  3.  
  4. CREATE TABLE dbo.temp_lovesql_primary  
  5. (  
  6.     ID INT PRIMARY KEY,  
  7.     Col Uniqueidentifier  
  8. ON [PRIMARY]  
  9. USE repl_db_partition_test;  
  10. GO 

--同樣的在訂閱庫創(chuàng)建臨時表

  1. CREATE TABLE dbo.temp_lovesql_primary  
  2. (  
  3.     ID INT PRIMARY KEY,  
  4.     Col Uniqueidentifier  
  5. ON [PRIMARY

-切換分區(qū)1到臨時比哦啊

  1. USE db_partition_test;  
  2. GO  
  3. ALTER TABLE dbo.lovesql SWITCH PARTITION 1 TO dbo.temp_lovesql_primary 

--查看表分區(qū)以后的分區(qū)行數

  1. SELECT $PARTITION.id_range_pf_1(ID) [PartitionNum],COUNT(1) [PartitionRowCount]  
  2. FROM dbo.lovesql  
  3. GROUP BY $PARTITION.id_range_pf_1(ID)  
  4. ORDER BY [PartitionNum]  
  5.  
  6. --PartitionNum PartitionRowCount  
  7. -------------- -----------------  
  8. --2            100  
  9. --3            100  
  10.  
  11. USE repl_db_partition_test;  
  12. GO 

--查看repl_db_partition_test 中 表分區(qū)以后的分區(qū)行數 

  1. SELECT $PARTITION.id_range_pf_1(ID) [PartitionNum],COUNT(1) [PartitionRowCount]  
  2. FROM dbo.lovesql  
  3. GROUP BY $PARTITION.id_range_pf_1(ID)  
  4. ORDER BY [PartitionNum] 

--PartitionNum PartitionRowCount
-------------- -----------------
--2            100
--3            100

--恭喜,測試成功,接下來,對兩邊同時TRUNCATE TABLE 就好了

結束語:

希望大家能夠一次性測試通過。GL,GG。這里提供了一種分區(qū)的方案來刪除數據,其實非PRIMARY的文件組,也是使用的,只要在建立SCHEME的時候ALL TO PRIMARY就成了。 

腳本一:/Files/bhtfg538/MSSQL/Replication/1.txt

腳本二: /Files/bhtfg538/MSSQL/Replication/2.txt

原文標題:SQL Server 2008 復制 分區(qū)SWITCH清理數據 Solution

鏈接:http://www.cnblogs.com/bhtfg538/

【編輯推薦】

  1. SQL Server使用索引實現數據訪問優(yōu)化
  2. SQL Server數據庫優(yōu)化經驗總結
  3. 如何使用SQLServer數據庫查詢累計值
  4. 淺析Oracle和SqlServer存儲過程的調試、出錯處理
  5. 幾段SQLServer語句和存儲過程
  6. 50種方法優(yōu)化SQL Server數據庫查詢 

 

責任編輯:彭凡 來源: 博客園
相關推薦

2013-05-08 10:01:55

SQL Server 數據備份備份與還原

2011-08-25 13:41:50

SQL Server 變更跟蹤

2011-09-01 10:46:56

SQL Server 快速清理日志文件

2009-03-17 13:25:13

查詢遷移SQL Server

2009-04-16 17:55:55

SQL Server 空間數據.NET

2010-08-27 09:59:51

SQL Server

2009-04-08 09:29:34

SQL Server新特性Resource Go

2010-10-14 09:32:52

SQL Server

2011-08-19 11:00:54

SQL Server WaitFor命令

2011-08-19 10:40:27

SQL Server Merge命令

2011-07-28 18:08:51

SQL Server MySQL

2011-08-01 10:09:57

SSAS數據庫

2011-09-01 15:24:22

SQL Server 存儲過程調試

2009-04-27 14:48:44

2009-04-16 18:25:55

2011-03-21 10:13:31

Sql Server數分區(qū)

2009-02-16 13:21:25

數據挖掘SQL Server SQL Server

2013-03-13 09:53:50

SQL Server

2011-08-25 18:09:36

SQL Server創(chuàng)建數據倉庫已分區(qū)表

2010-09-13 11:09:34

SQL SERVER分
點贊
收藏

51CTO技術棧公眾號