Where is the proper location for an application to store a large database on Windows?

Question

My application uses a sqlite database to store the user's content. This database can grow to be 1 GB+ for heavy users. The database is currently stored in ApplicationData (%appdata%), but the documentation for this indicates it is for roaming data which should be small, for example settings that should persist across sessions. Some other options:

• LocalApplicationData: documentation indicates this is for data that may be removed when the user logs off.

• MyDocuments/Personal: I believe this also roams.

• UserProfile: documentation says "Applications should not create files or folders at this level"

Some requirements for the storage location:

• User specific (no common areas)
• Non-administrative user must have read/write access
• Data should not roam (like AppData\Roaming)
• Data must not be deleted automatically

Many thanks for any suggestions for such a place on a Windows system.

Answer 1

If a user has a redirected ("roaming") application data folder and/or a redirected documents folder, it is likely that the user regularly uses multiple machines and expects his or her content to be available on whichever machine he or she is logged into. So most likely you should use either the roaming application data folder or the documents folder.

My initial instinct was to lean towards the former, because users generally don't like seeing mysterious and/or unexpected files in their documents folder, and may be inclined to delete them. On the other hand, the roaming application data folder is less likely to be backed up (we don't!) so, on the whole, I'd recommend the documents folder; to mitigate the other problem, put the database in a subfolder of Documents and give the subfolder a very clear name.

The local data folder is typically used for non-user-content such as caches and temporary files. I'm not aware of any professional product that stores user content there. IT folk are likely to take it for granted that it is safe to delete anything in these folders, and as already pointed out there are systems configured to do this automatically.

You certainly shouldn't use UserProfile; if the user profile is roaming, the content will be uploaded to the server every time the user logs out, and downloaded (if necessary) when the user logs in. (This is only true of UserProfile; the roaming application data folder and other redirected folders are accessed directly from the server and never sit on the local disk.)

I don't believe that the size of the file necessarily makes a redirected folder inappropriate. Mozilla Thunderbird, for example, stores the user's mail in the roaming application data folder, and this can get quite large. As far as I know, this has not caused any widespread problems.

However, there are a few Windows APIs that can't be used on a networked file, and it also won't work if any of the processes that will be accessing the file are running in a different security context (e.g., as a system service). You should check the documentation for sqlite to determine whether this is supported or not.

If you are not able to use a networked drive, I think the only good solution is to use the documents folder by default, but detect when it is on the network and ask the end user to select an alternative location.

In any event, you should provide either the system administrator or the end user (or both!) with a documented, supported way of overriding the default location.

Answer 2

For a per-user store, you need the AppData/Local folder. The environment variable is %localappdata%. The .NET enum is Environment.SpecialFolder.LocalApplicationData.

As for the "documentation indicates this is for data that may be removed when the user logs off."...

Yes. But it's rare. For example, it might happen for users on virtualized clients (think Citrix). In those cases, having a persistent 1GB SqlLite store will be a problem. For those of us on regular workstations, the data will probably not be removed.