创建应用程序数据的快照 - 最佳实践

问题描述

我们有一个使用 .NET FW 4.0 用 C# 开发的文本处理应用程序，管理员可以在其中定义各种设置.所有这些设置"数据都驻留在大约 50 个带有外键关系和 Identity 主键的表中(我认为这个会变得很棘手).整个数据库不超过10万条记录，平均一张表大约有6个短列.系统基于MS SQL 2008 R2 Express数据库.

We have a text processing application developed in C# using .NET FW 4.0 where the Administrator can define various settings. All this 'settings' data reside in about 50 tables with foreign key relations and Identity primary keys (this one will make it tricky, I think). The entire database is no more than 100K records, with the average table having about 6 short columns. The system is based on MS SQL 2008 R2 Express database.

我们需要为所有这些数据创建一个快照，以便系统管理员可以在他搞砸的任何时候回滚到其中一个快照.我们只需要保留最后 5 个快照.快照的创建必须从应用程序 GUI 开始，因此必须在需要时回滚到任何快照(不允许使用 SSMS，因为直接访问数据库被拒绝).该系统仍在开发中(我们真的完成了吗?)这意味着新的表和列被添加了很多次.因此，我们需要一个健壮的方法来自动处理更改(插入/更改列后挖掘代码是我们想要避免的，除非没有其他方法).最好的方法是告诉我想创建名称以 'Admin' 开头的所有表的快照".显然，这是一个数据库密集型的任务，但由于它只会在紧急情况下使用，所以我不介意.我也不介意是否发生表锁定，因为在创建或回滚快照的过程中没有任何东西会尝试使用这些表.

We face a requirement to create a snapshot of all this data so that the administrator of the system could roll back to one of the snapshots anytime he screws up something. We need to keep the last 5 snapshots only. Creation of the snapshot must be commenced from the application GUI and so must be the rollback to any of the snapshots if needed (use SSMS will not be allowed as direct access to the DB is denied). The system is still in development (are we ever really finished?) which means that new tables and columns are added many times. Thus we need a robust method that can take care of changes automatically (digging code after inserting/changing columns is something we want to avoid unless there's no other way). The best way would be to tell that "I want to create a snapshot of all tables where the name begins with 'Admin'". Obviously, this is quite a DB-intensive task, but due to the fact that it will be used in emergency situations only, this is something that I do not mind. I also do not mind if table locks happen as nothing will try to use these tables while the creation or rollback of the snapshot is in progress.

问题可以分为两部分:

1. 创建快照
2. 回滚到快照

关于问题#1.我们可能有两种选择:

Regarding problem #1. we may have two options:

1. 将数据导出为 XML(文件或数据库列)
2. 将 SQL 中的数据复制到相同或不同的表中(例如再次创建相同的表结构，并使用与原始表相同的名称并以备份"为前缀).

关于问题#2.我看到的最大问题是如何将所有数据重新导入到使用 IDENTITY 列生成 PK 的外键相关表中.我需要从所有受影响的表中删除所有数据，然后重新导入所有内容，同时暂时放宽 FK 约束并关闭身份生成.加载数据后，我应该检查 FK 约束是否仍然正常.

Regarding problem #2. the biggest issue I see is how to re-import all data into foreign key related tables which use IDENTITY columns for PK generation. I need to delete all data from all affected tables then re-import everything while temporarily relaxing FK constraints and switching off Identity generation. Once data is loaded I should check if FK constraints are still OK.

或者也许我应该找到一种合乎逻辑的方法来加载表，以便在加载时约束检查可以保持原位(因为我们没有无法管理的表数量，这可能是一个可行的解决方案).当然，出于显而易见的原因，我需要在单个事务中完成所有删除和重新加载.

Or perhaps I should find a logical way to load tables so that constraint checking can remain in place while loading (as we do not have an unmanageable number of tables this could be a viable solution). Of course I need to do all deletion and re-loading in a single transaction, for obvious reasons.

我怀疑可能没有纯基于 SQL 的解决方案，尽管 SQL CLR 可能有助于避免将数据移出 SQL Server.

I suspect there may be no pure SQL-based solution for this, although SQL CLR might be of help to avoid moving data out of SQL Server.

有没有人和我们遇到同样的问题?也许有人成功解决了这样的问题?

Is there anyone out there with the same problem we face? Maybe someone who successfully solved such problem?

我不希望有一步一步的指导.关于从哪里开始、采取哪些路线(导出到 RAW XML 或将快照保存在 DB 中或两者兼有)的任何帮助、优点/缺点都会非常有帮助.

I do not expect a step by step instruction. Any help on where to start, which routes to take (export to RAW XML or keep snapshot inside the DB or both), pros/cons would be really helpful.

感谢您的帮助和时间.

丹尼尔

推荐答案

一种按日期范围进行版本控制是企业应用程序中记录的常用方法.例如，我们有一个商业实体(我们)或公司(英国)的表格，我们将当前的官方名称保存在另一个表格中，如下所示:

A kind of versioning by date ranges is a common method for records in Enterprise applications. As an example we have a table for business entities (us) or companies (uk) and we keep the current official name in another table as follows:

CompanyID    Name            ValidFrom     ValidTo
12           Business Lld    2000-01-01    2008-09-23
12           Business Inc    2008-09-23    NULL

最后一条记录中的空值表示这是当前记录.您可以使用上述逻辑并可能添加更多列以获得更多控制.这样就没有重复，您可以将历史记录保持在任何级别，并轻松地跨表同步当前值.最后的表现会很棒.

The null in the last record means that this is current one. You may use the above logic and possibly add more columns to gain more control. This way there are no duplicates, you can keep the history up to any level and synchronize the current values across tables easily. Finally the performance will be great.

这篇关于创建应用程序数据的快照 - 最佳实践的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！