Sql Server Compact Edition database deployment strategy

Question

Hello,

I have a question about the most appropriate way to deploy a SQL Server CE database with our client application. I understand we need to install the SQL Server CE prerequisites etc., so this isn't a question about getting it to work. It already does.

Right now the way we have it is that we just ship a copy of the .mdf file (containing no data) with the application, which gets copied to the appropriate location during installation. This works perfectly fine, but I was wondering if this is the "correct" method of deploying a database with an application or if the database should be either a.) generated during the installation (as a custom install action), or b.) generated at the first run of the application.

Any thoughts or suggestions are appreciated. I realize there probably isn't a solid answer to this question.

Answer 1

See this

Deploy a SQL Server Compact Edition Database with an Application

http://www.tomshardware.com/forum/18893-36-deploy-server

Bye.

Answer 2

This is talking about the *.mdf file, not how to ensure that the SqlServerCE dll is available.

How you should deploy the database application is strongly dependent on the following questions:

Is the database per user?

• If it isn't should it be?
  • Having the database in the applications directory is a bad idea on Vista onwards as it may trigger UAE requests when modifying the file.
• If it is per user then realistically you must generate a new one for each user, so some code will have to run for this so you can just as easily use any of the following methods.

If not then simply including a single initial file will work fine and is the simplest option.

Is the database empty to start with (i.e.e you could 'create the table structure in code and have no *.mdf file anywhere to base it off.

• This can make you testing cleaner but means you cannot interact with the 'initial' database with things like the SQL Sever tools.
• If the initial state is either schema only or very small embedding the 'template' in the dll will make your deployment more flexible and will mean that no one can accidentally 'break' the template database by removing it/changing it by accident.

If there is significant data then this almost certainly precludes the embedded option.

Is there significant data in the initial state of the database that you may want to change without changing the dll?

• Perhaps you want to customize it per client
• Perhaps you want to be able to create a custom deployment without generating a new build.

If so then you will likely want the file separate rather than as an embedded resource.