解决 SQL Server Compact Edition 数据库文件中的损坏问题 [英] resolving corruption in SQL Server Compact Edition database files

问题描述

这不是查询.它是我们解决 SQL Compact 数据库文件损坏问题的解决方案的摘要，(几乎)取得了成功.SQLCE 损坏是一个非常常见的问题.我们从 StackOverflow 中的早期帖子中得到了巨大的帮助，因此这篇帖子.

This is not a query. Its a summary of our solution to get around the problem of corruption in SQL Compact Database files with (almost) definite success. SQLCE Corruption is a very common problem. We've received tremendous help from earlier posts in StackOverflow, and hence this post.

我们的产品是一个 3 层架构，服务器作为 Windows 服务运行，通过 .Net Remoting 连接到富客户端.我们的产品从 2006 年开始使用 SQLCE.我们已经从 v3.1 迁移到 v3.5，现在是 v4.0.我们有一个定制的 OR-Mapping 工具来满足一些非常具体的要求.我们在 v3.1 中遇到了有限的问题，在 v3.5 和 v4.0 中遇到了更多问题.

Our product is a 3-tier architecture with the server running as a Windows Service connected to Rich Clients through .Net Remoting. Our product uses SQLCE since 2006. We have moved from v3.1, to v3.5 and now v4.0. We've a custom OR-Mapping tool for some very specific requirements. We've faced limited problems with v3.1, we've faced more with v3.5 and v4.0.

最初使用 v3.5，我们实现了 SqlCeEngine.Repair.但它只会删除损坏的数据，并尝试重新创建一个稳定的数据库.我们发现受影响表的外键丢失了.我们不得不立即取消这个.我们开始通知用户数据库损坏，并恢复上次备份.这只是暂时的缓解；腐败问题依然存在.

Initially with v3.5, we implemented SqlCeEngine.Repair. But it only drops the corrupted data, and tries to recreate a stable db. We found that Foreign Keys of affected tables went missing. We had to do away with this immediately. We started notifying users about db corruption, and restore the last backup. This only provided a temporary relief; the problem of corruptions still stood.

今年，我们采用了 v4.0.但是，我们的应用程序还引入了一些新功能，这些功能极大地增加了数据库调用的数量.v4.0 开始很好，但随着软件使用量的增加开始出现问题.应用程序运行时发生的损坏不是由 Windows 崩溃、异常关闭或磁盘问题引起的.数据库刚刚损坏.

This year, we adopted v4.0. However, our application also introduced several new features which tremendously increased the number of database calls. v4.0 began well, but started giving problems when the software usage increased. Corruptions happening while the application was running caused neither by Windows crashing, abnormal shutdowns or disk issues. The database just corrupted.

下一篇文章介绍了我们为这个问题设计的解决方案:

The next post covers the solution that we devised for this problem:

推荐答案

[分离查询和解决方案]

[Separating the query and the solution]

以下是我们解决问题的方法:

Here goes how we solved the problem:

A) 关闭/处理连接/命令/事务对象:我们确保没有未使用的、未关闭的连接、事务或命令对象.我们的 ORM 工具用于在对事务调用 commit 后创建新对象，这些对象在某些情况下处于空闲状态.这几乎将损坏的数量减少了 50%.

A) Closing/Disposing Connection/Command/Transaction objects: We ensured that there are no unused, unclosed connection, transaction or command objects. Our ORM tool used to create new objects after calling commit on the transaction, which were lying idle in some cases. This pretty much reduced the number of corruptions by 50%.

B) 禁用自动收缩:在应用程序运行过程中发生的唯一一个我们无法控制的过程是自动收缩.我们在应用程序启动时调用了 SqlCeEngine.Compact.我们决定取消压缩和自动收缩.令我们惊讶的是，我们又将腐败现象减少了 48%.这是在黑暗中拍摄，我们无法相信自动收缩会导致这样的问题.我们实际上通过该更新解决了这个问题.

B) Disabling Auto-Shrink: The only procedure occurring in the middle of an application run, over which we had no control was Auto-Shrink. We were calling SqlCeEngine.Compact when the application starts. We decided to do away with both Compacting and Auto-Shrinking. And to our surprise, we reduced corruptions by another 48%. It was a shot in the dark, and we could not believe that Auto-Shrinking could have caused such problems. We practically solved the problem with that update.

C) 同步数据库事务:一些数据库损坏仍在发生.在没有检测到明确原因的情况下，我们决定同步数据库事务！我知道很多数据库人不会喜欢这个.我也不喜欢.我们在中间层引入了锁，以确保一次只有一个调用修改数据库.我们最大的实施是 55 个客户同时使用我们的系统.同步数据库调用几乎不会导致任何明显的性能延迟.相反，同步允许我们定期执行对 SqlCeEngine.Compact 的计时器驱动调用.我们知道 Compact 不是罪魁祸首，我们认为 Compaction 是必要的调用，因为它重新索引了数据库(我们的解决方案执行了大量插入和删除操作).但是，它需要专门运行；调用 Compact 时没有数据库调用.同步允许我们在应用程序运行期间控制它.自从我们这样做以来，我们还没有收到一个数据库损坏问题.现在已经一个多月了.从一周的近 5 个客户，到一个月的零.

C) Synchronized Database Transactions: Some database corruptions still happening. With no clear reasons detected, we decided to synchronize database transactions! I know that a lot of database people are not going to like this. I don't like it either. We introduced locks in our middle tier to ensure that only one call is modifying the database at a time. Our largest implementation is 55 clients simultaneously using our system. Synchronizing the database calls hardly resulted in any visible performance delay. Rather, Synchronizing allowed us to implement a timer-driven call to SqlCeEngine.Compact at regular intervals. We knew that Compact was not the culprit, and we felt that Compaction is a necessary call as it reindexes the db (our solution does a lot of inserts and deletes). However, it needs to function exclusively; no database calls when you call Compact. Synchronizing allowed us to control that during an application run. Since we've done that, we've not received a single database corruption problem. Its been more than a month now. From almost 5 clients in a week, to zero in a month.

导致我们想到 B 和 C 的基本推理是 SQLCE 是一个嵌入式数据库.损坏在每个嵌入式数据库解决方案中都很常见.一个完整的数据库解决方案独立工作，由 24x7 的 db-server 管理连接和其他任务支持.嵌入式数据库系统没有这样的支持系统.它处于活动状态的唯一阶段是打开连接时.

The basic reasoning which led us to ideas B and C is that SQLCE is an Embedded Database. Corruptions are common to every embedded database solution. A full-scale database solutions works independently supported by a 24x7 db-server managing connections and other tasks. An embedded database system does not have such a support system. The only stage when it is alive is when a connection is opened.

一些更多的指针: 1) 我们使用 CommitMode.Immediate 实现提交，这使得 Flush-Interval 属性变得多余.2) AutoShrink 设置为 100，这将完全禁用该过程 3) 我增加了连接超时以允许同步的数据库调用顺利运行.4) Compact 在应用程序启动时被调用.如果客户根本不关闭他们的机器，我们会实施计时器每 24 小时调用一次 Compact.

Some more pointers: 1) We implement commit with CommitMode.Immediate, which makes the Flush-Interval property redundant. 2) AutoShrink is set to 100, which disables the procedure completely 3) I've increased the Connection timeout to allow the synchronized database calls to function smoothly. 4) Compact is called when the application starts. In cases where clients do not shutdown their machine at all, we implemented the timer to call Compact every 24-hours.

希望这篇文章能帮助解决问题.

Hope this post helps solve problems.

这篇关于解决 SQL Server Compact Edition 数据库文件中的损坏问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！