SQL Server 2008 复制 分区SWITCH清理数据 Solution

SQL Server 2008 复制 分区SWITCH清理数据

 场景:

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

如:

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

保证A不可用的时候,B的业务不依赖于A服务器,利用A同步过来的数据B自己直接处理。

常见的解决方案:

                一、很简单的,我们可以想到,建立一个清理Job ,根据日期标识,确定删除的范围不出一点错误,比如我每天的晚上1200以后清理昨天的数据,那么可能会构建这样的查询(朋友们,也许会说使用TRUNCATE ,准时的1200TRUNCATE掉,不过很不幸的是,SQL Server 复制是利用日志标记的方式来同步数据,而TRUNCATE语句不会被日志标记,所以TRUNCATE语句在复制表中是被禁用的)

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

来删除54号的所有数据,可能上亿也可能更多。

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

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

所以 可能优化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行一批的数据来处理。

缺点:因为存在复制,那么很显然,浪费了极多的带宽,特别在Internet这种环境下,利用VPN的连接,尤其浪费。VPN带宽十分的渺小。再者,在进行亿级的DELETE,时间耗费也是非常恐怖的,而且DELETE过程中,由于存在Range-X 范围排他锁,许多开发人员写的SQL Query没加上WITH(NOLOCK)或者需要事务处理的查询语句,都将被阻塞。

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

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

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

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

 

新方案:

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

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

 

 

 

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

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

脚本:

/*
Title:Rapid Delete In The Replication
Author:浪客
Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise
Description:请在非生产环境下测试
*/

USE [master]
GO

--我们创建包含PRIMARY分区在内一共3分区的数据库
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

--创建分区函数,分区范围为  id<=100 |  100<id<=200  | id>200
CREATE PARTITION FUNCTION [id_range_pf_1](intAS RANGE LEFT FOR VALUES (100200)
GO

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

--创建分区表
CREATE TABLE dbo.lovesql
(
    ID 
INT IDENTITY(1,1NOT FOR REPLICATION NOT NULL PRIMARY KEY,
    Col 
Uniqueidentifier DEFAULT(NEWID())
ON id_range_ps_1(ID)
 
--插入测试数据 300条
INSERT INTO dbo.lovesql DEFAULT VALUES
GO 300

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

--输入结果,每个分区100条数据,分区正确!
--
PartitionNum PartitionRowCount
--
------------ -----------------
--
1            100
--
2            100
--
3            100

--现在开始建立复制,首先建立目标数据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

 

 

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

Object Explorer打开连接的实例中的Replication(复制)文件夹à右键新建发布à在向导中选择Publication Databasedb_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 里面是否已经分区了。

脚本执行:

 /*

Title:Rapid Delete In The Replication
Author:浪客
Environment:Windows Server 2008 Enterprise + SQL Server 2008 Enterprise
Description:请在非生产环境下测试
*/
USE db_partition_test;
GO

--默认发布是不会启用 “分区切换”以及分区DDL的。所以修改发布的属性
EXEC  sp_changepublication @publication=N'lovesql',@property=N'allow_partition_switch',@value=N'true';

--启用SWITCH DDL 复制
EXEC  sp_changepublication @publication=N'lovesql',@property=N'replicate_partition_switch',@value=N'true';


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

--同样的在订阅库创建临时表
CREATE TABLE dbo.temp_lovesql_primary
(
    ID 
INT PRIMARY KEY,
    Col 
Uniqueidentifier
ON [PRIMARY]

--切换分区1到临时比哦啊
USE db_partition_test;
GO

ALTER TABLE dbo.lovesql SWITCH PARTITION 1 TO dbo.temp_lovesql_primary

--查看表分区以后的分区行数
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 中 表分区以后的分区行数
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 就好了

 

结束语:

            希望大家能够一次性测试通过。GL,GG。这里提供了一种分区的方案来删除数据,其实非PRIMARY的文件组,也是使用的,只要在建立SCHEME的时候ALL TO PRIMARY就成了。 

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

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

posted @ 2010-05-12 17:15 Sai~ 阅读(...) 评论(...) 编辑 收藏