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

場景：

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

如：

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

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

常見的解決方案：

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

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

來刪除5月4號的所有數(shù)據(jù)，可能上億也可能更多。

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

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

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

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

將刪除操作，縮短成每5000行一批的數(shù)據(jù)來處理。

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

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

A數(shù)據(jù)庫 每天需要清理 前天數(shù)據(jù)，B服務(wù)器通過訂閱PUSH獲取同步數(shù)據(jù)。

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

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

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

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

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

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

腳本：

/*  
Title:Rapid Delete In The Replication  
Author:浪客  
Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise  
Description:請在非生產(chǎn)環(huán)境下測試  
*/  
 
USE [master]  
GO 

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

CREATE DATABASE [db_partition_test]   
ON  PRIMARY   
(   
    NAME = N'db_partition_test',   
    FILENAME = N'C:\db_partition_test.mdf' 
),   
FILEGROUP [FG1]   
(   
    NAME = N'db_partition_test_fg1_1',   
    FILENAME = N'C:\db_partition_test_fg1_1.ndf' 
),   
 FILEGROUP [FG2]   
(   
    NAME = N'db_partition_test_fg2_1',   
    FILENAME = N'C:\db_partition_test_fg2_1.ndf' 
)  
 LOG ON   
(   
    NAME = N'db_partition_test_log',   
    FILENAME = N'C:\db_partition_test_log.ldf' 
)  
GO  
 
USE [db_partition_test]  
GO 

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

CREATE PARTITION FUNCTION [id_range_pf_1](int) AS RANGE LEFT FOR VALUES (100, 200)  
GO 


--創(chuàng)建分區(qū)方案 ,分區(qū)一到PRIMARY,分區(qū)二到FG1,分區(qū)三到FG2
 

CREATE PARTITION SCHEME [id_range_ps_1] AS PARTITION [id_range_pf_1] TO ([PRIMARY],FG1,FG2);  
GO 

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

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

--插入測試數(shù)據(jù) 300條

INSERT INTO dbo.lovesql DEFAULT VALUES 
GO 300 

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

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

-輸入結(jié)果，每個分區(qū)100條數(shù)據(jù)，分區(qū)正確！
--PartitionNum PartitionRowCount
-------------- -----------------
--1            100
--2            100
--3            100

--現(xiàn)在開始建立復(fù)制，首先建立目標(biāo)數(shù)據(jù)repl_db_partition_test
 

USE [master];  
GO  
 
CREATE DATABASE [repl_db_partition_test]   
ON  PRIMARY   
(   
    NAME = N'repl_db_partition_test',   
    FILENAME = N'C:\repl_db_partition_test.mdf' 
),   
FILEGROUP [FG1]   
(   
    NAME = N'repl_db_partition_test_fg1_1',   
    FILENAME = N'C:\repl_db_partition_test_fg1_1.ndf' 
),   
 FILEGROUP [FG2]   
(   
    NAME = N'repl_db_partition_test_fg2_1',   
    FILENAME = N'C:\repl_db_partition_test_fg2_1.ndf' 
)  
 LOG ON   
(   
    NAME = N'repl_db_partition_test_log',   
    FILENAME = N'C:\repl_db_partition_test_log.ldf' 
)  
GO 

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

在Object Explorer打開連接的實例中的Replication（復(fù)制）文件夾à右鍵新建發(fā)布à在向?qū)е羞x擇Publication Database為db_encryption_testà選擇事務(wù)復(fù)制à在Article中選擇lovesql表->在右手邊的Article Properties設(shè)置表的發(fā)布屬性à配置如下：

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

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

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

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

腳本執(zhí)行：

 /*  
Title:Rapid Delete In The Replication  
Author:浪客  
Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise  
Description:請在非生產(chǎn)環(huán)境下測試  
*/  
USE db_partition_test;  
GO 

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

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

--啟用SWITCH DDL 復(fù)制

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

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

USE db_partition_test;  
GO  
 
CREATE TABLE dbo.temp_lovesql_primary  
(  
    ID INT PRIMARY KEY,  
    Col Uniqueidentifier  
) ON [PRIMARY]  
USE repl_db_partition_test;  
GO 

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

CREATE TABLE dbo.temp_lovesql_primary  
(  
    ID INT PRIMARY KEY,  
    Col Uniqueidentifier  
) ON [PRIMARY] 

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

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

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

SELECT $PARTITION.id_range_pf_1(ID) [PartitionNum],COUNT(1) [PartitionRowCount]  
FROM dbo.lovesql  
GROUP BY $PARTITION.id_range_pf_1(ID)  
ORDER BY [PartitionNum]  
 
--PartitionNum PartitionRowCount  
-------------- -----------------  
--2            100  
--3            100  
 
USE repl_db_partition_test;  
GO 

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

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

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

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

結(jié)束語：

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

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

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

原文標(biāo)題：SQL Server 2008 復(fù)制 分區(qū)SWITCH清理數(shù)據(jù) Solution

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

【編輯推薦】

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