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:
- 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?)
- Same as above, but install the file into the CommonAppDataFolder, attach it to SQL Server.
- 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?