U4-2642 - Make SQL CE more resilient to server crashes

Created by Sebastiaan Janssen 19 Aug 2013, 10:08:21 Updated by Sebastiaan Janssen 30 Sep 2013, 12:51:02

Currently, SQL CE only writes new data to the database every 10 seconds (default value). This is not necessary and can lead to data loss when the server crashes.

So far, we've only observed data loss when running a site in WebMatrix. The IISExpress server seems to crash more often then when running IISExpress from Visual Studio. Obviously on a normal IIS server there are far fewer crashes but an unfortunate application pool recycle COULD lead to loss of data (I've been unable to reproduce this though, seems like IIS has some way of telling the application that it's being shutting down and SQL CE flushes it's data to the database file).

We can set the flush interval to 1 second to improve this and in numerous Umbraco trainings this has proven to be very effective.

An additional tip from the SQL CE team suggests to add the following parameter to the connection string as well: File Access Retry Timeout=10. This ensures that there is a few retries in case the database file is temporarily locked (by a virus scanner) or when there's an IO issue.

Comments

Sebastiaan Janssen 19 Aug 2013, 10:27:20

Fixed in rev: 65db0e69a97e74c1a0b8fb9aa45742ea961f53a5


Priority: Normal

Type: Bug

State: Fixed

Assignee:

Difficulty: Normal

Category:

Backwards Compatible: True

Fix Submitted:

Affected versions:

Due in version: 6.1.4

Sprint:

Story Points:

Cycle: