归档时间 - SQL Server
我正在研究我们的数据库应用程序的归档选项（高度规范化的模式），并且希望得到专家的反馈。我们正在使用 Sql Server 2005，但如果某些功能只能在 2008 R2 中运行，那么这可能是我们的一个选择。
归档的主要原因是每年删除旧数据。确定哪些对象可以存档的标准并不简单（即：不仅按日期过滤，还涉及更多考虑因素）。
归档基本上需要是应用程序上的一个按钮（即：不是由数据库服务器上的实际 DBA 执行）。
数据应该是可检索的，但也许是根据特殊要求。也许可以搜索一个对象及其所有相关部分并将其带回到当前数据库中？ （同样，通过应用程序接口。）
另一个重要要求是维护相关数据的完整性。如果存档对象与非存档对象相关，我想确保无法通过界面删除非存档对象。目前，我们有许多检查来确保您无法删除正在使用的项目，并且我犹豫是否要更改所有这些检查以加入 _archive 表或使用新视图。还有别的办法吗？
我读过有关表/索引分区的内容，虽然它很有趣，但考虑到我们使用了多少存储过程、视图、索引等，这听起来可能需要做很多工作。
I am researching archiving options for our database application (highly normalized schema) and would appreciate expert feedback. We are using Sql Server 2005, but if something works only in 2008 R2 that may be an option for us.
Primary reason for archiving is to remove old data on an annual basis. The criteria to determine which objects can be archived will not be straightforward (ie: not just filtering by a date, but many more considerations involved).
Archiving needs to be basically a push button on the application (ie: not by actual DBA on the database server).
Data should be retrievable, but perhaps by special request. Perhaps an object and all its related pieces could be searched for and brought back into the current database? (Again, via the application interface.)
Another important requirement is to maintain integrity of related data. If an archived object is related to a non-archived object, I want to ensure the non-archived object can't be deleted through the interface. Currently we have many checks in place to ensure you can't delete items if they're in use, and I hesitate to alter all of those checks to join an _archive table or use a new view. Is there another way?
I have read about table/index partitioning and although it is interesting, it sounds like perhaps a LOT of work considering how many stored procedures, views, indexes, etc that we use.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
您归档的动机是什么？
您提到您想要“删除旧数据”，但由于您需要它始终可用，所以这没有任何意义。
在您的情况下，最容易做的事情是“软”归档，您可以在所有表中添加一个
Archived位字段，以指示行是否处于活动状态。然后，所有现有的引用检查都保持不变，但您需要在视图或查询中的该位上添加过滤器，并将其添加到大多数索引中。您实际上不需要进行卸载，因为无论如何您都无法将数据移出服务器。
What is your motivation for archiving?
You mention you want to "remove old data" but since you need it to be constantly available that doesn't make any sense.
The easiest thing to do in your situation will be a "soft" archive, where you add an
Archivedbit field to all your tables that indicates if a row is active or not. Then all your existing referential checks stay in place, but you need to add a filter on that bit in your views or queries, and add it to most of your indexes.You don't really need to do an offload since you can't move the data off the server anyways.