詳解SQL Server 2008復(fù)制分區(qū)清理數(shù)據(jù)
51CTO數(shù)據(jù)庫(kù)頻道向您推薦《SQL Server 2008深度應(yīng)用》和《SQL Server入門(mén)到精通》兩個(gè)專(zhuān)題讓您更深刻的了解本文。
場(chǎng)景：
某種特定業(yè)務(wù)下，我們的部分業(yè)務(wù)數(shù)據(jù)可能只會(huì)保留比較短的時(shí)間，用來(lái)做臨時(shí)處理。因?yàn)榭紤]高可用的特性，可能會(huì)利用 SQL Server的復(fù)制組件復(fù)制這種數(shù)據(jù)到另外的 類(lèi)似前端，查詢中心等數(shù)據(jù)庫(kù)服務(wù)器，創(chuàng)建一個(gè)冗余副本。復(fù)制組件標(biāo)記事務(wù)日志，追蹤所有的Update，Insert，Delete操作。可是如果不定期清理這種表，那么在一個(gè)快速增長(zhǎng)的環(huán)境下，表變的臃腫不堪，不僅僅浪費(fèi)磁盤(pán)空間，而且給性能帶來(lái)了負(fù)面影響。
如：
A臨時(shí)數(shù)據(jù)中心 ---同步-->B 查詢/其他業(yè)務(wù)中心，副本
保證A不可用的時(shí)候，B的業(yè)務(wù)不依賴于A服務(wù)器，利用A同步過(guò)來(lái)的數(shù)據(jù)B自己直接處理。
常見(jiàn)的解決方案：
一、很簡(jiǎn)單的，我們可以想到，建立一個(gè)清理Job ，根據(jù)日期標(biāo)識(shí)，確定刪除的范圍不出一點(diǎn)錯(cuò)誤，比如我每天的晚上12：00以后清理昨天的數(shù)據(jù)，那么可能會(huì)構(gòu)建這樣的查詢（朋友們，也許會(huì)說(shuō)使用TRUNCATE ，準(zhǔn)時(shí)的12：00來(lái)TRUNCATE掉，不過(guò)很不幸的是，SQL Server 復(fù)制是利用日志標(biāo)記的方式來(lái)同步數(shù)據(jù)，而TRUNCATE語(yǔ)句不會(huì)被日志標(biāo)記，所以TRUNCATE語(yǔ)句在復(fù)制表中是被禁用的）
- DELETE FROM dbo.t WHERE complete_time >= '2010-05-04'
- AND complete_time < '2010-05-05'
來(lái)刪除5月4號(hào)的所有數(shù)據(jù)，可能上億也可能更多。
優(yōu)化方案：因?yàn)榭紤]到存在帶量的更新 刪除 和插入操作，所以首先講數(shù)據(jù)庫(kù)設(shè)置成SIMPLE恢復(fù)模式，以最小化日志方式。
由于DELETE單個(gè)語(yǔ)句是一個(gè)事務(wù)性的語(yǔ)句，要么全部成功，要么全部失敗。那么可想如果刪除的是億級(jí)別的數(shù)據(jù)，那么日志增長(zhǎng),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ù)來(lái)處理。
缺點(diǎn)：因?yàn)榇嬖趶?fù)制，那么很顯然，浪費(fèi)了極多的帶寬，特別在Internet這種環(huán)境下，利用VPN的連接，尤其浪費(fèi)。VPN帶寬十分的渺小。再者，在進(jìn)行億級(jí)的DELETE，時(shí)間耗費(fèi)也是非常恐怖的，而且DELETE過(guò)程中，由于存在Range-X 范圍排他鎖，許多開(kāi)發(fā)人員寫(xiě)的SQL Query沒(méi)加上WITH(NOLOCK)或者需要事務(wù)處理的查詢語(yǔ)句，都將被阻塞。
二、刪除復(fù)制，TRUNCATE表。我們可以直接刪除復(fù)制的發(fā)布和訂閱，然后講兩邊的表都直接TRUNCATE掉，這種刪除方式非常的高效，只不過(guò)，要確保其他新增的數(shù)據(jù)不被誤刪，也存在一定的風(fēng)險(xiǎn)，構(gòu)思如下：
A數(shù)據(jù)庫(kù) 每天需要清理 前天數(shù)據(jù)，B服務(wù)器通過(guò)訂閱PUSH獲取同步數(shù)據(jù)。
因此Job 的建立有點(diǎn)復(fù)雜，尤其 當(dāng)12點(diǎn)突然宕機(jī)的時(shí)候，數(shù)據(jù)沒(méi)有及時(shí)清理，或者12：00執(zhí)行TRUNCATE的時(shí)候，下一天的00：00：01秒的數(shù)據(jù)被插入了，等等數(shù)據(jù)完整性問(wèn)題，讓我采用這個(gè)方案回避了。
缺點(diǎn)：估計(jì)這個(gè)Job不怎么好寫(xiě)，首先是自動(dòng)刪除復(fù)制，重新創(chuàng)建復(fù)制，創(chuàng)建訂閱，雖然利用Generate Script可以到處腳本，但是整個(gè)解決方案過(guò)于復(fù)雜了。
新方案：進(jìn)入主題了，在SQL Server 中分區(qū)表有一個(gè)非常 實(shí)用的語(yǔ)句ALTER TABLE …SWITCH，這個(gè)DDL可以快速的講同文件組的表的某個(gè)分區(qū)迅速的轉(zhuǎn)移到另外的表。（很顯然，應(yīng)該是利用數(shù)據(jù)的位置偏移量的指針的轉(zhuǎn)移到新表的方法來(lái)實(shí)現(xiàn)的），這種方案轉(zhuǎn)移數(shù)據(jù)非常迅速，因?yàn)椴淮嬖诖罅康腎O操作，所以一般都是秒殺級(jí)別任意大數(shù)據(jù)量（如果當(dāng)前表的索引等比較多，維護(hù)操作還是需要點(diǎn)時(shí)間的）。但是SQL Server 2005 是不支持在復(fù)制中使用SWITCH語(yǔ)句的（這點(diǎn)很郁悶，命名都可以跟蹤DDL，為什么這個(gè)不能傳送到訂閱服務(wù)器，只要確認(rèn)發(fā)布和訂閱同時(shí)存在目標(biāo)表和相同的分區(qū)方案不就好了，微軟2005考慮不周?。?/p>
但是前不久我在考慮我們每天億級(jí)別的數(shù)據(jù)增長(zhǎng)，清理數(shù)據(jù)方案變的十分迫切的時(shí)候，我發(fā)現(xiàn)SQL Server 2008支持這種操作。下面就開(kāi)始整個(gè)Solution吧：
開(kāi)始方案前，大家其實(shí)根據(jù)自己的業(yè)務(wù)來(lái)建立分區(qū)方案。我采用的是疊代追加 的方式來(lái)擴(kuò)展以及清理分區(qū)的。
當(dāng)然我也看過(guò)類(lèi)似 使用根據(jù)某列鍵列 建立計(jì)算列并且講計(jì)算列PERSISTED 之后分區(qū)的。（可以緊靠業(yè)務(wù)，自己處理，我在這里展示一種，根據(jù)ID自增方案分區(qū)擴(kuò)張的方案）。
腳本：
- /*
- Title:Rapid Delete In The Replication
- Author:浪客
- Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise
- Description:請(qǐng)?jiān)诜巧a(chǎn)環(huán)境下測(cè)試
- */
- USE [master]
- GO
--我們創(chuàng)建包含PRIMARY分區(qū)在內(nèi)一共3分區(qū)的數(shù)據(jù)庫(kù)
- 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)
--插入測(cè)試數(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é)果，每個(gè)分區(qū)100條數(shù)據(jù)，分區(qū)正確！
--PartitionNum PartitionRowCount
-------------- -----------------
--1 100
--2 100
--3 100
--現(xiàn)在開(kāi)始建立復(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)建好了，基本的測(cè)試環(huán)境，那么開(kāi)始建立復(fù)制。
在Object Explorer打開(kāi)連接的實(shí)例中的Replication（復(fù)制）文件夾à右鍵新建發(fā)布à在向?qū)е羞x擇Publication Database為db_encryption_testà選擇事務(wù)復(fù)制à在Article中選擇lovesql表->在右手邊的Article Properties設(shè)置表的發(fā)布屬性à配置如下：
下一步跳過(guò)Filter Table Rowsà在Snapshot Agent中，選中第一個(gè)復(fù)選框à在Agent Security中選擇自己設(shè)置的帳號(hào)吧，我這里選擇了Run under the SQL Server Agent services count（記得，如果你的Sql Agent對(duì)應(yīng)的Windows 帳號(hào)沒(méi)有源表以及distribution表的db_owner權(quán)限，以及沒(méi)有快照文件夾的權(quán)限，會(huì)出錯(cuò)的，具體的可以自己參看MSDN如何Security Settingà給Publications取一個(gè)名字，就叫l(wèi)ovesql吧。
接下來(lái)建立db_encryption_test 到 repl_db_encryption_test的訂閱：
在Object Explore中找到Replication/Local Publications文件夾下，找到你的發(fā)布，然后右鍵新建訂閱à一直下一步知道出現(xiàn)Subscribers，然后新添加一個(gè)訂閱 指向本地的repl_db_encryption_testà在安全中選擇Run under the sql server agent service count，然后一直下一步，直到成功。
如果3個(gè)代理都成功了（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:請(qǐng)?jiān)诜巧a(chǎn)環(huán)境下測(cè)試
- */
- USE db_partition_test;
- GO
--默認(rèn)發(fā)布是不會(huì)啟用 “分區(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ù)庫(kù)都存在一個(gè) 臨時(shí)表,而且必須是空數(shù)據(jù)的臨時(shí)表 用來(lái)快速切換分區(qū)使用
--確保分區(qū)的表，和切換分區(qū)的表使用的是同一個(gè)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
--同樣的在訂閱庫(kù)創(chuàng)建臨時(shí)表
- CREATE TABLE dbo.temp_lovesql_primary
- (
- ID INT PRIMARY KEY,
- Col Uniqueidentifier
- ) ON [PRIMARY]
-切換分區(qū)1到臨時(shí)比哦啊
- 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
--恭喜,測(cè)試成功,接下來(lái),對(duì)兩邊同時(shí)TRUNCATE TABLE 就好了
結(jié)束語(yǔ)：
希望大家能夠一次性測(cè)試通過(guò)。GL,GG。這里提供了一種分區(qū)的方案來(lái)刪除數(shù)據(jù)，其實(shí)非PRIMARY的文件組，也是使用的，只要在建立SCHEME的時(shí)候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/
【編輯推薦】
- SQL Server使用索引實(shí)現(xiàn)數(shù)據(jù)訪問(wèn)優(yōu)化
- SQL Server數(shù)據(jù)庫(kù)優(yōu)化經(jīng)驗(yàn)總結(jié)
- 如何使用SQLServer數(shù)據(jù)庫(kù)查詢累計(jì)值
- 淺析Oracle和SqlServer存儲(chǔ)過(guò)程的調(diào)試、出錯(cuò)處理
- 幾段SQLServer語(yǔ)句和存儲(chǔ)過(guò)程
- 50種方法優(yōu)化SQL Server數(shù)據(jù)庫(kù)查詢
