Sql-server – Application/MS SQL Server Express Installer

automationinstallationsql serversql-server-2008-r2

We're developing a small(ish) application that collects and stores data. It's low-throughput, low-volume, but needs to be able to archive and retrieve data from (almost) arbitrarily earlier times, hence the decision to store the data in a MS SQL Server Express database. To the user, the database is invisible. The setup is:

  • Application – Visual Studio 10 (yes, 10) C#, .NET Framework 4.0
  • Database schema (tables, views, queries …)
  • MS SQL Server Express 2008 SP2
  • MS Visual Studio-generated Installer (not the Installshield one), which …
    • installs the application
    • installs .NET 4.0 (if needed)
    • installs MS SQL Server Express 2008 SP2
    • does not create the actual database

At this point, we can create the database using MS SQL Management Studio or Visual Studio. But for production deployment, the creation of the database must be automated, either by the installer or by a start-up functionality of the application: Starting out from a fresh Windows 7 or Windows 10 installation (the PC will not be availlable to us beforehand), running the installer and then starting the application (e. g. by our service personnel) must be enough. Also, the application should not depend on which user is logged onto the PC.

Question: How do I make the database, err … appear … on the target PC?

Options I've considered so far:

  1. Create the DB, make the DB files part of the installer, copy them into the right SQL Server directory, hope it recognizes it (will it?)
  2. Same as above, but install the file into the CommonAppDataFolder, attach it to SQL Server.
  3. Let the application test for the existence of the DB, and, if it does not yet exist, run a SQL script (generated by SSMS, modified) to create the database (currently implemented).

Which option would you recommend?

Best Answer

Create the DB, make the DB files part of the installer, copy them into the right SQL Server directory, hope it recognizes it (will it?)

It will not, you will have to attach the database to your server afterwards. You could add a sql script that will attach the files to your server though.

Let the application test for the existence of the DB, and, if it does not yet exist, run a SQL script (generated by SSMS, modified) to create the database (currently implemented).

This could be a good option. Perhaps even better if you could run the .sql file in your installation, that way your application doesn't have any delay when the users first run it.

I also think it depends on the size of the database files. If it's big you could go for the first option, if not i'd use the second option.

Related Question