软件开发培训班 >> 编程开发 >> SQL


详解SQL Server 2008复制分区清理数据

　　本文标签：SQL Server 2008

　　51CTO数据库频道向您推荐《SQL Server 2008深度应用》和《SQL Server入门到精通》两个专题让您更深刻的了解本文  。

　　场景：

　　某种特定业务下，我们的部分业务数据可能只会保留比较短的时间，用来做临时处理  。因为考虑高可用的特性，可能会利用 SQL Server的复制组件复制这种数据到另外的 类似前端，查询中心等数据库服务器，创建一个冗余副本  。复制组件标记事务日志，追踪所有的Update，Insert，Delete操作  。可是如果不定期清理这种表，那么在一个快速增长的环境下，表变的臃肿不堪，不仅仅浪费磁盘空间，而且给性能带来了负面影响  。

　　如：

　　A临时数据中心   ---同步-->B 查询/其他业务中心，副本

　　保证A不可用的时候，B的业务不依赖于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号的所有数据，可能上亿也可能更多  。

　　优化方案：因为考虑到存在带量的更新 删除 和插入操作，所以首先讲数据库设置成SIMPLE恢复模式，以最小化日志方式  。

　　由于DELETE单个语句是一个事务性的语句，要么全部成功，要么全部失败  。那么可想如果删除的是亿级别的数据，那么日志增长,IO负荷非常的大  。

　　所以 可能优化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这种环境下，利用VPN的连接，尤其浪费  。VPN带宽十分的渺小  。再者，在进行亿级的DELETE，时间耗费也是非常恐怖的，而且DELETE过程中，由于存在Range-X 范围排他锁，许多开发人员写的SQL Query没加上WITH(NOLOCK)或者需要事务处理的查询语句，都将被阻塞  。

　　二、删除复制，TRUNCATE表  。我们可以直接删除复制的发布和订阅，然后讲两边的表都直接TRUNCATE掉，这种删除方式非常的高效，只不过，要确保其他新增的数据不被误删，也存在一定的风险，构思如下：

　　A数据库 每天需要清理 前天数据，B服务器通过订阅PUSH获取同步数据  。

　　因此Job 的建立有点复杂，尤其 当12点突然宕机的时候，数据没有及时清理，或者12：00执行TRUNCATE的时候，下一天的00：00：01秒的数据被插入了，等等数据完整性问题，让我采用这个方案回避了  。

　　缺点：估计这个Job不怎么好写，首先是自动删除复制，重新创建复制，创建订阅，虽然利用Generate Script可以到处脚本，但是整个解决方案过于复杂了  。

　　新方案：进入主题了，在SQL Server 中分区表有一个非常 实用的语句ALTER TABLE …SWITCH，这个DDL可以快速的讲同文件组的表的某个分区迅速的转移到另外的表  。（很显然，应该是利用数据的位置偏移量的指针的转移到新表的方法来实现的），这种方案转移数据非常迅速，因为不存在大量的IO操作，所以一般都是秒杀级别任意大数据量（如果当前表的索引等比较多，维护操作还是需要点时间的）  。但是SQL Server 2005 是不支持在复制中使用SWITCH语句的（这点很郁闷，命名都可以跟踪DDL，为什么这个不能传送到订阅服务器，只要确认发布和订阅同时存在目标表和相同的分区方案不就好了，微软2005考虑不周啊）  。

　　但是前不久我在考虑我们每天亿级别的数据增长，清理数据方案变的十分迫切的时候，我发现SQL Server 2008支持这种操作  。下面就开始整个Solution吧：

　　开始方案前，大家其实根据自己的业务来建立分区方案  。我采用的是叠代追加 的方式来扩展以及清理分区的  。

　　当然我也看过类似 使用根据某列键列 建立计算列并且讲计算列PERSISTED 之后分区的  。（可以紧靠业务，自己处理，我在这里展示一种，根据ID自增方案分区扩张的方案）  。

　　脚本：

　　

  1. /*  
  2. Title:Rapid Delete In The Replication  
  3. Author:浪客  
  4. Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise  
  5. Description:请在非生产环境下测试  
  6. */  
  7.  
  8. USE [master]  
  9. GO 

　　
--我们创建包含PRIMARY分区在内一共3分区的数据库
 

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

　　
--创建分区函数，分区范围为  id<=100 |  100200
 

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


--创建分区方案 ,分区一到PRIMARY,分区二到FG1,分区三到FG2
 
  1. CREATE PARTITION SCHEME [id_range_ps_1] AS PARTITION [id_range_pf_1] TO ([PRIMARY],FG1,FG2);  
  2. GO 

　　--创建分区表

  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 

　　--查看表分区以后的分区行数

  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] 

　　
-输入结果，每个分区100条数据，分区正确！
--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 = Nrepl_db_partition_test,   
  8.     FILENAME = NC:\repl_db_partition_test.mdf 
  9. ),   
  10. FILEGROUP [FG1]   
  11. (   
  12.     NAME = Nrepl_db_partition_test_fg1_1,   
  13.     FILENAME = NC:\repl_db_partition_test_fg1_1.ndf 
  14. ),   
  15.  FILEGROUP [FG2]   
  16. (   
  17.     NAME = Nrepl_db_partition_test_fg2_1,   
  18.     FILENAME = NC:\repl_db_partition_test_fg2_1.ndf 
  19. )  
  20.  LOG ON   
  21. (   
  22.     NAME = Nrepl_db_partition_test_log,   
  23.     FILENAME = NC:\repl_db_partition_test_log.ldf 
  24. )  
  25. GO 

　　上面已经创建好了，基本的测试环境，那么开始建立复制  。

　　在Object Explorer打开连接的实例中的Replication（复制）文件夹à右键新建发布à在向导中选择Publication Database为db_encryption_testà选择事务复制à在Article中选择lovesql表->在右手边的Article Properties设置表的发布属性à配置如下：

　　

　　下一步跳过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取一个名字，就叫lovesql吧  。

　　接下来建立db_encryption_test 到 repl_db_encryption_test的订阅：

　　在Object Explore中找到Replication/Local Publications文件夹下，找到你的发布，然后右键新建订阅à一直下一步知道出现Subscribers，然后新添加一个订阅 指向本地的repl_db_encryption_testà在安全中选择Run under the sql server agent service count，然后一直下一步，直到成功  。

　　如果3个代理都成功了（Snapshot代理，Log Read代理，Distribute代理），那么看看repl_db_encryption_test下的表lovesql是否存在，右键属性查看 他的Storage 里面是否已经分区了

　　

　　脚本执行：

　　

  1.  /*  
  2. Title:Rapid Delete In The Replication  
  3. Author:浪客  
  4. Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise  
  5. Description:请在非生产环境下测试  
  6. */  
  7. USE db_partition_test;  
  8. GO 

　　

--默认发布是不会启用 “分区切换”以及分区DDL的  。所以修改发布的属性

  1. EXEC  sp_changepublication @publication=Nlovesql,@property=Nallow_partition_switch,@value=Ntrue; 

　　--启用SWITCH DDL 复制

  1. EXEC  sp_changepublication @publication=Nlovesql,@property=Nreplicate_partition_switch,@value=Ntrue; 

　　--确保发布和订阅两边的数据库都存在一个 临时表,而且必须是空数据的临时表 用来快速切换分区使用
--确保分区的表，和切换分区的表使用的是同一个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 

　　--同样的在订阅库创建临时表

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

　　-切换分区1到临时比哦啊

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

　　--查看表分区以后的分区行数

  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 中 表分区以后的分区行数 

  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  。这里提供了一种分区的方案来删除数据，其实非PRIMARY的文件组，也是使用的，只要在建立SCHEME的时候ALL TO PRIMARY就成了  。 

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

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

　　原文标题：SQL Server 2008 复制 分区SWITCH清理数据 Solution

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

【编辑推荐】

  1. SQL Server使用索引实现数据访问优化
  2. SQL Server数据库优化经验总结
  3. 如何使用SQLServer数据库查询累计值
  4. 浅析Oracle和SqlServer存储过程的调试、出错处理
  5. 几段SQLServer语句和存储过程
  6. 50种方法优化SQL Server数据库查询 
【责任编辑：彭凡 TEL：（010）68476606】