在 SQL Server 中归档大量旧数据 [英] Archiving large amounts of old data in SQL Server

问题描述

很简单的问题.

我有一个 70GB 的大型数据库，其中包含五个表中的四个，每个表包含大约 5000 万行.这些表包含大约 6 年的数据.我们将数据库中的空间限制为 80GB，我们将在未来 6 个月左右的时间内迅速接近这一目标.

I have a large 70gb database that has four of five tables that contain about 50 million rows each. These tables contain about 6 years worth of data. We are limited to amount of space in our database to 80gb, and we are going to be quickly approaching that in the next 6 months or so.

我们只需要在实时数据库中保留大约两年的数据.在不使实时数据库脱机(它是 24/7 的数据库)的情况下存档旧数据的最佳方法是什么?

We only need to keep about two years worth of data in the live database. What is the best approach to archiving the older data WITHOUT taking the live database offline (it's 24/7 database)?

我们正在使用共享存储的主动-被动设置在群集环境中运行 SQL Server 2008 R2 Standard.

We are running SQL Server 2008 R2 Standard in a clustered environment using active-passive setup using shared storage.

谢谢.

推荐答案

当你说存档时，这意味着需要保留数据以备后用".

When you say archive, this implies "need to keep data for later use".

除此之外，如果数据现在不可用，那么如果您依赖磁带备份，就会面临丢失数据的风险.此外，您需要有磁盘空间以供日后恢复.

Amongst other things, if the data is not left available now, then you run the risk of losing it if you rely on tape backups. Also, you need to have disk space to restore it in future.

这些当然不是无法解决的问题，但除非事情很关键，否则我会保持数据在线，直到证明不是这样.我的经验表明，人们在您最不期望的时候想要存档数据......

These aren't insurmountable problems of course but unless things are critical, I'd keep the data on-line until proven otherwise. My experience shows that folk want archived data when you least expect...

一个选项(问题被标记为 SQL Server 2008)是 数据压缩.例如，您可以压缩在线存档表.

One option (the question is tagged SQL Server 2008) is compression of data. You can compress archive tables that are online for example.

创建存档表.

SELECT * INTO ANewArchiveTable
FROM CurrentTable
WHERE SomeDateColumn <= DATEADD(year, -2, GETDATE())

或者使用分区来实现一样

如果数据在线，您可以进一步减少当前"表，并且在压缩存档表/分区中也有早于 3 个月的数据

If the data is online you can reduce the "current" table further and have data older than, say, 3, months in a compressed archive table/partition too

这篇关于在 SQL Server 中归档大量旧数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！