解决 SQL Server Compact Edition 数据库文件中的损坏问题
这不是一个查询。它总结了我们解决 SQL Compact 数据库文件损坏问题的解决方案，并且（几乎）取得了一定的成功。 SQLCE 损坏是一个非常常见的问题。我们从 StackOverflow 的早期帖子中获得了巨大的帮助，因此也有了这篇文章。
我们的产品是一个 3 层架构，服务器作为 Windows 服务运行，通过 .Net Remoting 连接到富客户端。我们的产品自 2006 年起就使用 SQLCE。我们已从 v3.1 迁移到 v3.5，现在又迁移到 v4.0。我们有一个定制的 OR 映射工具，可以满足一些非常具体的要求。我们在 v3.1 中遇到了有限的问题，在 v3.5 和 v4.0 中遇到了更多问题。
最初在 v3.5 中，我们实现了 SqlCeEngine.Repair。但它只会删除损坏的数据，并尝试重新创建稳定的数据库。我们发现受影响表的外键丢失。我们必须立即废除这个。我们开始通知用户有关数据库损坏的信息，并恢复最后的备份。这只是暂时的缓解；腐败问题依然存在。
今年，我们采用了v4.0。然而，我们的应用程序还引入了一些新功能，极大地增加了数据库调用的数量。 v4.0 开始时表现良好，但随着软件使用量的增加开始出现问题。应用程序运行时发生的损坏不是由 Windows 崩溃、异常关闭或磁盘问题引起的。数据库刚刚损坏。
下一篇文章将介绍我们针对此问题设计的解决方案：
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.
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.
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.
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:
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
[分离查询和解决方案]
以下是我们如何解决问题的：
A) 关闭/处置连接/命令/事务对象：
我们确保不存在未使用的、未关闭的连接、事务或命令对象。我们的 ORM 工具过去常常在调用事务提交后创建新对象，这些对象在某些情况下处于闲置状态。这几乎将腐败数量减少了 50%。
B) 禁用自动收缩：
在应用程序运行过程中发生的唯一我们无法控制的过程是自动收缩。应用程序启动时我们调用 SqlCeEngine.Compact。我们决定取消压缩和自动收缩。令我们惊讶的是，我们的腐败现象又减少了 48%。这是一次盲目的尝试，我们无法相信自动缩小会导致这样的问题。我们实际上通过该更新解决了该问题。
C) 同步数据库事务：
一些数据库损坏仍在发生。由于没有发现明确的原因，我们决定同步数据库事务！我知道很多数据库人员不会喜欢这个。我也不喜欢它。我们在中间层引入了锁，以确保一次只有一个调用正在修改数据库。我们最大的实施是 55 个客户同时使用我们的系统。同步数据库调用几乎不会导致任何明显的性能延迟。相反，同步允许我们定期实现对 SqlCeEngine.Compact 的计时器驱动调用。我们知道 Compact 不是罪魁祸首，并且我们认为 Compaction 是必要的调用，因为它会重新索引数据库（我们的解决方案执行大量插入和删除操作）。然而，它需要专门发挥作用；调用 Compact 时不会调用任何数据库。同步使我们能够在应用程序运行期间进行控制。自从我们这样做以来，我们还没有收到任何数据库损坏问题。到现在已经一个多月了。从一周内几乎 5 个客户，到一个月内零个客户。
导致我们提出想法 B 和 C 的基本推理是 SQLCE 是一个嵌入式数据库。损坏对于每个嵌入式数据库解决方案都很常见。全面的数据库解决方案独立工作，由管理连接和其他任务的 24x7 数据库服务器支持。嵌入式数据库系统不具备这样的支持系统。它处于活动状态的唯一阶段是连接打开时。
更多提示： 1) 我们使用 CommitMode.Immediate 实现提交，这使得 Flush-Interval 属性变得多余。 2) AutoShrink 设置为 100，这会完全禁用该过程 3) 我增加了连接超时，以允许同步数据库调用顺利运行。 4) Compact在应用程序启动时被调用。在客户根本不关闭机器的情况下，我们实现了每 24 小时调用 Compact 一次的计时器。
希望这篇文章能帮助解决问题。
[Separating the query and the solution]
Here goes how we solved the problem:
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) 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) 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.
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.
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 CE 4.0，则存在一个已知问题，该问题可能会阻止数据刷新到磁盘（完全）。 https://support.microsoft.com/en-us/kb/2979868 和修补程序 https://support.microsoft.com/en-us/kb/2960153
用他们自己的话说：
提供的解决方法是在您要处理的块周围使用
transaction.Commit(CommitMode.Immediate)想要确保被冲洗掉If using SQL Server CE 4.0 there is a known issue that may prevent data from being flushed to disk (AT ALL). https://support.microsoft.com/en-us/kb/2979868 and hotfix https://support.microsoft.com/en-us/kb/2960153
In their own words:
The workaround supplied is to use
transaction.Commit(CommitMode.Immediate)around the block you want to make sure gets flushed