0

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?

  • 2
    Regarding #1 and 2, you need to attach the database for the database to be available. I would personally opt for option #3 or do the same in the installer using a custom action. That would support both new installs and upgrade scenarios while retaining existing data. – Dan Guzman Aug 17 at 22:27
  • Extended support for Sql Server 2008 (R2) has ended as of July 9 2019. I would advise to go for a higher version. Source – Randi Vertongen Aug 19 at 12:17
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?)

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.

  • The app always starts out with an empty database, i. e. all tables are empty. So the third option it is. Thank you. – doppelfish Aug 20 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.