How to distribute SQL databases (not compact editions)

[zachattack05]

OK so I have a decent database that I have set up with all the correct tables and relationships, but now I have another issue, and that is distributing it. I don't know how to achieve what I am after.

I know how to install SQL Server Express during my setup, or shortly after through the command prompt and in silent mode. That's not my issue. The issue is, there needs to be a "Server" and a "Client" version of the application (preferably the same, with just a customizable connection string).

The problem I am having is how to import the database I made into my project so that this is a possibility.

I have no problems importing the database file into the project so that I can use the data source in the project, but the problem is:

1. The database only "mounts" when the application is running
2. The connection string is stored in the applications settings file, and is marked as application scope (which is a pain to edit, but yes, I know it's possible)

And there are some unknowns that I was hoping someone might be able to help me with:

1. If the application mounts the database on a sql instance that is discoverable and accepts remote connections, will that database be accessable on the network? If not, how can I make it accessable?
2. Is there a way to install a new instance of SQL server express and have that instance be discoverable and accept remote connections AND automatically mount my database on system start up?
3. If, during the "Add a data connection..." wizard in VS, I tell the wizard to not store the connection string in the settings file, where IS it stored then?

Please, I'm lost here. Please tell me I didn't waste days putting together a database schema that I can't use except on a local installation.

[lolafuertes]

IMO, to do that, you need to migrate your database from SQL Server Express to SQL Server, install a SQL Server instance on the server and create the database once.

In this scenario, SQL server will be network reachable. There are several versions and licensing options, but probaby teh SQL server 2008 Standard will be enough for most of applications. See here for editions details.

You must have in mind that this will be a multi-user capable environment and, depending on how you programmed, will be necessary to migrate your application to forceably implement transacions to ensure that insert, update and delete commands execute properly isolated.

Hope this helps.

[zachattack05]

I actually have solved this issue.

Instead of adding a .mdf file to my project, I simply installed a new instance of SQL Server 2008 R2 Express on my development system. Configured it how I wanted, copied my database file I made, along with the log file to the SQL Data folder (where it will be installed when my application is installed) opened SSMS, logged into the new instance I created, "attached" my database, scripted the change and saved it so I can attach it when I'm installing my application.

Now when I develop my UX, all I need to do is create a "dataset" or whatever method I like, and get the data from my instance. This works great, the database starts up with the SQL service on system boot (even without a user logging in) and is fully accessible! All I need to do is "search" for the SQL instance over a network on first run, and when I find my instance, modify my connection strings in the dataset(s) and then use it. If I can't find the instance, the user can provide the server's name.

Piece of cake!

Only issue now is, how to create (if needed) Windows users so that other users can access the database with integrated security...I know it's possible, I just need to find out how :)

Best of all! The application will know each time it starts if it is a server or client! All it needs to do is find out if the sql instance is installed locally or on a remote machine :)

I will say a custom setup app is probably warranted here.