分区表数据归档

MS-SQL Server > 疑难问题
收藏 回复
[问题点数：100分，结帖人yangb0803]
更多帖子
私信 空间 博客
道玄希言
等级
本版专家分：6112
勋章
Blank
黄花 2015年10月 MS-SQL Server大版内专家分月排行榜第二
2015年8月 其他数据库开发大版内专家分月排行榜第二
2015年7月 其他数据库开发大版内专家分月排行榜第二
Blank
蓝花 2015年9月 其他数据库开发大版内专家分月排行榜第三
2015年7月 MS-SQL Server大版内专家分月排行榜第三
2015年6月 其他数据库开发大版内专家分月排行榜第三
结帖率 100%
我想向各位请教一下MSSQL数据归档的的问题.
具体场景:

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

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

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

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

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

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

(NOSQL等其他数据库方案不考虑, 目前项目采用的SQLSERVER存储的数据)
只看楼主 引用 举报 楼主 收起
展开阅读全文
更多帖子
私信 空间 博客
版主
卖水果的net
等级
本版专家分：57433
勋章
Blank
签到新秀 累计签到获取，不积跬步，无以至千里，继续坚持！
Blank
GitHub 绑定GitHub第三方账户获取
Blank
进士 2017年 总版技术专家分年内排行榜第八
Blank
优秀版主 2016年10月优秀大版主
优秀小版主
我想请教各位, 使用该方式进行数据归档,  是否可行? 


总体上是可以行，有以下几点要考虑进来 ：
1、分区规则是什么样的，是每天一个分区，还是每月一个分区。
2、是否有实时性的要求。
3、保留 6 个月数据，是要求 到天还是到月。
4、

其他的方案也有一些：
1、使用链接服务器每天晚上同步数据。
2、BCP 速度很快，可以考虑使用，看看你有没有 大数据类型，如果有的话，可能会有问题。
3、使用一些第三方的同步工具，如 sqoop 或 datax 这些。
只看TA 引用 举报 #1得分 30
更多帖子
私信 空间 博客
版主
吉普赛的歌
等级
本版专家分：61753
勋章
Blank
签到新秀 累计签到获取，不积跬步，无以至千里，继续坚持！
Blank
榜眼 2018年总版新获得的技术专家分排名第二
Blank
进士 2019年总版新获得的技术专家分排名前十
Blank
金牌 2018年10月 总版技术专家分月排行榜第一
2018年9月 总版技术专家分月排行榜第一
2018年8月 总版技术专家分月排行榜第一
数据库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步， 这个是分区的清空， 基本是秒级完成。

只看TA 引用 举报 #2得分 70
更多帖子
私信 空间 博客
道玄希言
等级
本版专家分：6112
勋章
Blank
黄花 2015年10月 MS-SQL Server大版内专家分月排行榜第二
2015年8月 其他数据库开发大版内专家分月排行榜第二
2015年7月 其他数据库开发大版内专家分月排行榜第二
Blank
蓝花 2015年9月 其他数据库开发大版内专家分月排行榜第三
2015年7月 MS-SQL Server大版内专家分月排行榜第三
2015年6月 其他数据库开发大版内专家分月排行榜第三
感谢两位大神的回复, 我们的业务场景有点特别;
主要是最新的数据有用, 时间长了, 数据基本就不会使用了, 只是需要保留存档以做特殊需求的查询;

因为数据量会比较大, 每天的数据目前在100G(1亿条)左右, 后面可能还有增长, 数据不要求精确, 只会有插入和查询操作, 无修改操作, 允许少量的数据丢失;
为节省磁盘空间, 我们只需要在数据库中保留半年的数据, 超过该时段的数据, 就可以直接归档后, 从服务器上拿下来, 以释放掉磁盘的空间;

所以方案我们不必考虑归档后的数据是否能够方便查询调用; 只要能顺利从磁盘移走, 然后在另外的环境中, 可以还原(一般情况下, 不会去还原)就可以.

因目前我们有一套方案, 是使用动态创建数据库的方式, 每个月创建一个相同结构的数据库, 以时间命名, 数据存储满6个月后, 直接将超过6个月时间的数据库分离了移走. 要使用时, 再挂上去. 该方式在研发这边会稍麻烦一点, 但在运维这里会非常简单了, 也无重复导出再插入数据到另外的库这个问题了. 也降低了服务器资源的使用.

现在是想不再采用动态创建数据库方式, 所以需要在归档这块找到相应解决方案. 

我找了一圈现有的归档方案, 数据都是归档在本数据库内, 没法归档到不同的数据库(表, 分区结构相同).

所以我需要的是直接拷贝比如分区文件, 就能够将数据转移到另外一个数据的方法.

二楼版主大神的 alwayson 方案, 在从表动手归档数据, 但归档之后, 从表会越来越大, 归档数据保留一年没问题, 但如果是两年, 这个空间我们承受不住... 因为一年的数据量, 可能是几十T, 但可能这一年的数据, 根本就没人去用过... 再久点的数据存档要如何做?  我们现在是直接离线存储, 压缩后放在普通的机械硬盘上.




只看TA 引用 举报 #3得分 0
更多帖子
私信 空间 博客
道玄希言
等级
本版专家分：6112
勋章
Blank
黄花 2015年10月 MS-SQL Server大版内专家分月排行榜第二
2015年8月 其他数据库开发大版内专家分月排行榜第二
2015年7月 其他数据库开发大版内专家分月排行榜第二
Blank
蓝花 2015年9月 其他数据库开发大版内专家分月排行榜第三
2015年7月 MS-SQL Server大版内专家分月排行榜第三
2015年6月 其他数据库开发大版内专家分月排行榜第三
引用 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个月, 就直接从数据库分离出来, 压缩了仍某移动硬盘上, 柜子里一丢完事了.  要数据就去拿出来解压了随便找个机器挂起来, 就可以查了.

这种方式在程序里面的查询稍微复杂一些, 需要在数据库中切换, 有时候还需要多个数据库查询的结果连接起来.




只看TA 引用 举报 #4得分 0
更多帖子
私信 空间 博客
道玄希言
等级
本版专家分：6112
勋章
Blank
黄花 2015年10月 MS-SQL Server大版内专家分月排行榜第二
2015年8月 其他数据库开发大版内专家分月排行榜第二
2015年7月 其他数据库开发大版内专家分月排行榜第二
Blank
蓝花 2015年9月 其他数据库开发大版内专家分月排行榜第三
2015年7月 MS-SQL Server大版内专家分月排行榜第三
2015年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的情况下, 能将数据归档备份出来的办法.
只看TA 引用 举报 #5得分 0
更多帖子
私信 空间 博客
版主
吉普赛的歌
等级
本版专家分：61753
勋章
Blank
签到新秀 累计签到获取，不积跬步，无以至千里，继续坚持！
Blank
榜眼 2018年总版新获得的技术专家分排名第二
Blank
进士 2019年总版新获得的技术专家分排名前十
Blank
金牌 2018年10月 总版技术专家分月排行榜第一
2018年9月 总版技术专家分月排行榜第一
2018年8月 总版技术专家分月排行榜第一
引用 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的情况下, 能将数据归档备份出来的办法.

sqlserver 的分区转移数据， 只能在本库内进行， 转其它库做不了。
我们一般是用sql作业定时将需要归档的数据 
select * 
into 归档库.dbo.表_日期
from 当前库.dbo.表
where  日期 ？？
插入到归档库，然后再迁移分区到同结构表，再drop同结构表。

如果你按我的， 可能就是
select *
into xxx
from 表
这一步的IO影响比较大， 其它还好。

你可以试一下， 一天的数据用这种方式插入到归档库需要多长时间？


只看TA 引用 举报 #6得分 0
收起
收藏 回复
道玄希言

等级：

Blank
黄花 2015年10月 MS-SQL Server大版内专家分月排行榜第二
2015年8月 其他数据库开发大版内专家分月排行榜第二
2015年7月 其他数据库开发大版内专家分月排行榜第二
Blank
蓝花 2015年9月 其他数据库开发大版内专家分月排行榜第三
2015年7月 MS-SQL Server大版内专家分月排行榜第三
2015年6月 其他数据库开发大版内专家分月排行榜第三
关注 私信 TA的帖子
实战:oracle 分区交换-归档数据

交换分区的操作步骤如下： 1. 创建分区表t1，假设有2个分区，P1，P2. 2. 创建基表t11存放P1规则的数据。 ...3. 创建基表t12 存放P2规则的数据。...4. 用基表t11和分区表T1的P1分区...未分区表和分区表中一个分区交换

RDS for MySQL 通过分区归档历史数据

RDS for MySQL 通过分区归档历史数据 原始表 分区 用于分区维护的存储过程 每月调用存储过程的事件 随着数据的积累，数据量的增加，越来越多的表体积变的庞大，不但影响查询的执行时间，而且使得管理...

oracle，分区表归档

oracle，分区表归档

Hive归档分区表

Hive可以把分区表的分区归档成HAR文件，也可以把归档完的分区进行解压。 以下步骤基于Hadoop2.8.5和Hive2.3.5 步骤 1、把hadoopHome/share/hadoop/tools/lib/hadoop-archives-2.8.5.jar放到hiveHome/lib目录下 2...

oracle 分区交换-归档数据

创建分区表t1，假设有2个分区，P1，P2.2.创建基表t11存放P1规则的数据。3.创建基表t12存放P2规则的数据。4.用基表t11和分区表T1的P1分区交换。把表t11的数据放到到P1分区5.用基表t12和分区表T1p2分区交换。把表t12的...

利用分区表归档历史数据

利用分区表归档历史数据主要利用exchange partition,下图是一个将普通表转换为分区表并对历史数据进行归档的示图. ...在将历史数据归档时使用了两次exchange partition,因为分区表不能直接和分...

DBA日常管理——数据归档（Archiving-Data）

原文出处：http://www.sqlnotes.cn/post/2013/09/05/DBA-Daily-Jobs-One%E2%80%94%E2%80%94-Archiving-Data 均为本人博客 ...每次查询可能需要查找更多的数据页，特别是当查询存在扫描操作时，会...

导入导出 Oracle 分区表数据

--****************************-- 导入导出 Oracle 分区表数据--**************************** 导入导入Oracle 分区表数据是Oracle DBA 经常完成的任务之一。分区表的导入导出同样普通表的导入导出方式，只不过...

oracle 闪回数据归档 分区表

闪回查询对撤销数据及...闪回数据归档的工作原理是将原本只能保存在UNDO表空间的撤销数据额外的以一种历史表的形式保存在指定的普通表空间（permanent类型的表空间）中。并且不像undo_retention参数那样是个影响整个数

使用导出导入(datapump)方式将普通表切换为分区表

有几种不同的方法来对此进行操作，诸如导出表数据，然后创建分区表再导入数据到分区表；使用EXCHANGE PARTITION方式来转换为分区表以及使用DBMS_REDEFINITION来在线重定义分区表。本文描述的是使用导出导入方式来...

不建议mysql分区表

缘起：业内进行一些技术交流的时候也更多的是自己分库分表，而不是使用分区表。解决什么问题？回答：当mysql单表的数据库过大时，数据库的访问速度会下降，“数据量大”问题的常见解决方案是“水平切分”。mysql常见...

RDS for MySQL 通过分区自动归档历史数据

RDS for MySQL 通过分区归档历史数据 原始表 分区 用于分区维护的存储过程 每月调用存储过程的事件 随着数据的积累，数据量的增加，越来越多的表体积变的庞大，不但影响查询的执行时间，而且使得管理工作...

MySQL 分区表以及操作

分表的一般 参考（只是参考)： 表体积大于2g，简单查询表数据超过1000w行，复杂查询表超过200w行。 两种分表的思路 * 横行分表 比较常见的是按时间切分 * 纵向分表 对于字段的冷热程度区分很明显的情况注意的点 ...

SQL SERVER 分区表的总结--分区表的维护和管理

SQL SERVER 分区表的总结--分区表的维护和管理 在依据需求建立好分区表之后，就要实现对分区表的管理维护。主要内容就是两点： 1. 利用滑动窗口方案(Sliding Window Scenario)，实现分区表与数据移动中间表的互...

TiDB 分区表优化实践

TiDB 分区表优化实践1.背景2.集群信息3.分区表简介3.1 分区类型3.2 兼容性4.遇到的问题5.解决办法6.测试6.1 手动测试6.2 性能测试6.3 上线后的效果7.总结 1.背景 用户行为浏览历史记录表是用于存放手机登录汽车之家 ...

MySQL分区表

1.确认Mysql服务是否支持分区表 命令： 2.Mysql分区表特点 1.在逻辑上为一个表，在物理上存储在多个文件中， 2.创建分区表语句： 注意：区别 生成的文件： 非分区表： 分区表： 3.按...

Oracle分区表管理

应对哪种表应用分区功能； 1.大于2GB的表 2.含有1000万条记录以上的表，表中含有的数据越多，SQL操作的执行速度就会越慢。 3.将会含有大量数据的表。 4.强行拆分后可利于并行操作的表。 5.含有需要定期...

SQL Server分区表应用场景

SQL Server分区表应用场景 ...《SQL Server 大数据管理——数据归档（主文件备份）》、《SQL Server 大数据管理——数据归档(段落备份)》介绍了分区在数据归档中的应用。但对什么样的表可以做分区，如何选择分区...

数据归档的方法

--数据归档的方法create or replace produced FW_JBPM_BACKUP_P is--保存每次归档的条数zc_date_number number;v_number number; begin select count(1) into v_number from user_tables where table_name = 'A'; ...

SQL Server 自动循环归档分区数据脚本

标签：SQL SERVER/MSSQL SERVER/数据库/DBA/表分区 概述 在很多业务场景下我们需要对...接下来分享一个自己编写的自动归档分区数据的脚本，原理是分区表和归档表使用相同的分区方案，循环利用当前的文件组。 ...

用EXPDP方法导出导入分区表

一、分区级别的导入导出 ... 对于已经存在数据的表，使用imp导入时需要使用参数IGNORE=y，而使用impdp，加table_exists_action=append | replace 参数。   示例：使用expdp/impdp   这种方法的步骤是： （1...

DB2分区表数据管理

一、检查分区 select datapartitionname,lowvalue,highvalue from syscat.partitions where tabname='XXX' and tabschema='XXX' order by ...二、分区剥离归档 1、db2 "ALTER TABLE ECIFAPP.CBM_CORP_DEP_DTL DE

闪回数据归档

Oracle Database 11g通过新的闪回数据归档后台进程FBDA自动跟踪和归档启用了闪回数据归档的表中的数据。使用此功能可满足超过还原保留期的长期保留要求。闪回数据归档可确保闪回查询能够获得对数据库对象版本的SQL ...

定期删除数据为空的分区表

在数据库日常管理中，对大表进行分区表是一个不错的策略，分区不仅可以优化表的性能还能对数据进行归档和易于管理。但是如果表的数据会定期删除，比如保留3-6个月的数据，那么会有很多数据保留在数据库中，需要进行...

Mysql分区表

Mysql分区表1 分区概念2 分区的优点3 分区类型4 子分区5 分区的查看和修改6 分区的影响7 各种存储引擎的分区 1 分区概念 分区针对不同的数据库，具有不同的特性。在这里专门针对MySQL数据库而言。在MySQL数据库里，...

相关热词 c# 解析ini c# 高德经纬度转墨卡托 c# 加法 泛型 c#裁切image c#onpaint c# 函数参数初始化 软件工程师考试题c# c# 多媒体定时器 c# 集合首字母排序 c#调用java程序