分区表数据归档

[道玄希言]

我想向各位请教一下MSSQL数据归档的的问题.
具体场景:

实时使用数据库A, 里面有表T, 该表每日数据量会有过亿条的记录; 数据库A的表T中的数据, 要求保留半年;
超过半年的数据, 要求迁移到另外的数据库服务器B, 在数据库B, 按照相同的分区架构也创建了一个表T;

我如果在数据库A创建好之后, 做一个全备, 然后在数据库B还原;

之后每天将数据库A上面的超过6个月的数据分区备份出来, 然后清理掉中间的数据, 再合并分区;
再将在数据库服务器A备份的数据, 拿到数据库服务器B, 进行还原;

我想请教各位, 使用该方式进行数据归档, 是否可行?

如果可行, 简单介绍下需要注意的事项;

如果此法不可行, 要达到这样的效果, 除了在两个数据库中, 通过 BCP工具或SQL语句查询插入等方式将数据从数据库服务器A迁移到数据库服务器B之外, 还有无其他方案?

(NOSQL等其他数据库方案不考虑, 目前项目采用的SQLSERVER存储的数据)

[卖水果的net]

我想请教各位, 使用该方式进行数据归档, 是否可行? 总体上是可以行，有以下几点要考虑进来 ： 1、分区规则是什么样的，是每天一个分区，还是每月一个分区。 2、是否有实时性的要求。 3、保留 6 个月数据，是要求 到天还是到月。 4、 其他的方案也有一些： 1、使用链接服务器每天晚上同步数据。 2、BCP 速度很快，可以考虑使用，看看你有没有 大数据类型，如果有的话，可能会有问题。 3、使用一些第三方的同步工具，如 sqoop 或 datax 这些。

[吉普赛的歌]

数据库A 为主数据库 数据库B 为备份数据库 是这样没错吧？ 你的数据量太大，同步比较容易出错。 建议使用 alwayson ， 同步 A , B 的数据。搭建步骤可以参考下面文章： https://blog.csdn.net/yenange/article/details/46495661 搭建好之后，A 与 B 的数据就是一致的了，只是 B 是只读的， A 是可以读写的。 在B的同一服务器上， 创建归档库B2, 用于专门存放历史数据。 想读取所有数据， 无非就做个视图， 关联一下 B, B2 两个表上的数据即可。 1. 每天定时将 B 上的历史数据（必须是一个整分区的记录）， 转移到 B2 上。 insert into b2.dbo.tableName(c1,c2) select c1,c2 from b.dbo.tableName where 时间 ?? 2. 第1步执行完, 在A中清空历史分区。 第1步， 因为在同一服务器上操作， 速度是比较快的。 第2步， 这个是分区的清空， 基本是秒级完成。

[道玄希言]

感谢两位大神的回复, 我们的业务场景有点特别; 主要是最新的数据有用, 时间长了, 数据基本就不会使用了, 只是需要保留存档以做特殊需求的查询; 因为数据量会比较大, 每天的数据目前在100G(1亿条)左右, 后面可能还有增长, 数据不要求精确, 只会有插入和查询操作, 无修改操作, 允许少量的数据丢失; 为节省磁盘空间, 我们只需要在数据库中保留半年的数据, 超过该时段的数据, 就可以直接归档后, 从服务器上拿下来, 以释放掉磁盘的空间; 所以方案我们不必考虑归档后的数据是否能够方便查询调用; 只要能顺利从磁盘移走, 然后在另外的环境中, 可以还原(一般情况下, 不会去还原)就可以. 因目前我们有一套方案, 是使用动态创建数据库的方式, 每个月创建一个相同结构的数据库, 以时间命名, 数据存储满6个月后, 直接将超过6个月时间的数据库分离了移走. 要使用时, 再挂上去. 该方式在研发这边会稍麻烦一点, 但在运维这里会非常简单了, 也无重复导出再插入数据到另外的库这个问题了. 也降低了服务器资源的使用. 现在是想不再采用动态创建数据库方式, 所以需要在归档这块找到相应解决方案. 我找了一圈现有的归档方案, 数据都是归档在本数据库内, 没法归档到不同的数据库(表, 分区结构相同). 所以我需要的是直接拷贝比如分区文件, 就能够将数据转移到另外一个数据的方法. 二楼版主大神的 alwayson 方案, 在从表动手归档数据, 但归档之后, 从表会越来越大, 归档数据保留一年没问题, 但如果是两年, 这个空间我们承受不住... 因为一年的数据量, 可能是几十T, 但可能这一年的数据, 根本就没人去用过... 再久点的数据存档要如何做? 我们现在是直接离线存储, 压缩后放在普通的机械硬盘上.

[道玄希言]

引用 1 楼 卖水果的net 的回复:

我想请教各位, 使用该方式进行数据归档, 是否可行? 总体上是可以行，有以下几点要考虑进来 ： 1、分区规则是什么样的，是每天一个分区，还是每月一个分区。 2、是否有实时性的要求。 3、保留 6 个月数据，是要求 到天还是到月。 4、 其他的方案也有一些： 1、使用链接服务器每天晚上同步数据。 2、BCP 速度很快，可以考虑使用，看看你有没有 大数据类型，如果有的话，可能会有问题。 3、使用一些第三方的同步工具，如 sqoop 或 datax 这些。

1. 因每天的数据量都比较大, (实际运行环境每天数据量大约会有1亿记录左右), 且数据有很强的时效性. 离当前时间越近的数据, 查询会越频繁, 久远的数据, 基本就没什么查询了. 2. 数据入库基本上是按照产生时间来, 不存在再修改; 3. 目前是一天一个分区, 查询语句已经规范其条件一定会带分区字段; 4. 超过一定时段(比如半年)的数据需要归档, 归档数据无实时性要求, 能存起来就行. 但实时的业务库不能断; 5. 超6个月数据, 不管事到天还是到月都可以; 6. 方案最好是不要因为数据的迁移备份而消耗系统IO. 您上面提到的BCP, 同步, 都需要重新将数据读出来写一遍. 我们之前是使用每个月一个数据库的模式, 表依然按天分区; 这个月过完了, 就创建一个新的数据库, 继续存储, 这种方式数据维护简单; 只需要在创建好库时做一个全备, 然后每天一增量; 30 天后 ,备份可以全不要了, 直接将整个库备份; 超6个月, 就直接从数据库分离出来, 压缩了仍某移动硬盘上, 柜子里一丢完事了. 要数据就去拿出来解压了随便找个机器挂起来, 就可以查了. 这种方式在程序里面的查询稍微复杂一些, 需要在数据库中切换, 有时候还需要多个数据库查询的结果连接起来.

[道玄希言]

引用 2 楼 吉普赛的歌 的回复:

数据库A 为主数据库 数据库B 为备份数据库 是这样没错吧？ 你的数据量太大，同步比较容易出错。 建议使用 alwayson ， 同步 A , B 的数据。搭建步骤可以参考下面文章： https://blog.csdn.net/yenange/article/details/46495661 搭建好之后，A 与 B 的数据就是一致的了，只是 B 是只读的， A 是可以读写的。 在B的同一服务器上， 创建归档库B2, 用于专门存放历史数据。 想读取所有数据， 无非就做个视图， 关联一下 B, B2 两个表上的数据即可。 1. 每天定时将 B 上的历史数据（必须是一个整分区的记录）， 转移到 B2 上。 insert into b2.dbo.tableName(c1,c2) select c1,c2 from b.dbo.tableName where 时间 ?? 2. 第1步执行完, 在A中清空历史分区。 第1步， 因为在同一服务器上操作， 速度是比较快的。 第2步， 这个是分区的清空， 基本是秒级完成。

alwayson 我们已有搭建, 原来的方式是按月分库, 按天分区, slwayson 就每个月需要去整一下, 两边挂上去, 有点麻烦... 因为是按月分库了, 所以过了一定时间, 我们就直接将库摘下来, 释放磁盘空间. 毕竟, 一个月好几T的数据, 半年几十T, 自建服务器, 柜子空间装不了太多数据, 且数据超过一定时间, 可以不出来, 只要偶尔(几年可能会有一次)有需要时, 能有数据可查就可以. 所以现在想找一个两全其美的办法, 主库直接入数据, 但如何在少使用磁盘I/O的情况下, 能将数据归档备份出来的办法.

[吉普赛的歌]

引用 5 楼 道玄希言 的回复:

[quote=引用 2 楼 吉普赛的歌 的回复:] 数据库A 为主数据库 数据库B 为备份数据库 是这样没错吧？ 你的数据量太大，同步比较容易出错。 建议使用 alwayson ， 同步 A , B 的数据。搭建步骤可以参考下面文章： https://blog.csdn.net/yenange/article/details/46495661 搭建好之后，A 与 B 的数据就是一致的了，只是 B 是只读的， A 是可以读写的。 在B的同一服务器上， 创建归档库B2, 用于专门存放历史数据。 想读取所有数据， 无非就做个视图， 关联一下 B, B2 两个表上的数据即可。 1. 每天定时将 B 上的历史数据（必须是一个整分区的记录）， 转移到 B2 上。 insert into b2.dbo.tableName(c1,c2) select c1,c2 from b.dbo.tableName where 时间 ?? 2. 第1步执行完, 在A中清空历史分区。 第1步， 因为在同一服务器上操作， 速度是比较快的。 第2步， 这个是分区的清空， 基本是秒级完成。

alwayson 我们已有搭建, 原来的方式是按月分库, 按天分区, alwayson 就每个月需要去整一下, 两边挂上去, 有点麻烦... 因为是按月分库了, 所以过了一定时间, 我们就直接将库摘下来, 释放磁盘空间. 毕竟, 一个月好几T的数据, 半年几十T, 自建服务器, 柜子空间装不了太多数据, 且数据超过一定时间, 可以不出来, 只要偶尔(几年可能会有一次)有需要时, 能有数据可查就可以. 所以现在想找一个两全其美的办法, 主库直接入数据, 但如何在少使用磁盘I/O的情况下, 能将数据归档备份出来的办法. [/quote] sqlserver 的分区转移数据， 只能在本库内进行， 转其它库做不了。 我们一般是用sql作业定时将需要归档的数据 select * into 归档库.dbo.表_日期 from 当前库.dbo.表 where 日期 ？？ 插入到归档库，然后再迁移分区到同结构表，再drop同结构表。 如果你按我的， 可能就是 select * into xxx from 表 这一步的IO影响比较大， 其它还好。 你可以试一下， 一天的数据用这种方式插入到归档库需要多长时间？