SQL Database in Deployment Package

[daveofgv]

Hi Forum!

I have a quick and simple question (maybe), but I am not exactly sure of the answer.....

I have a SQL database that I created in SQL Management Studio 2012 and I want to add it to my Visual Studio 2010 project... I connected to the database just fine and can add / delete / update.... However, when I looked in the management studio - I see the updated info, however, I want to deploy my package and have the database in the deployment...

Question:

When I connect to a database that is in SQL Management Studio - how do I add it to my project for deployment instead of it always connected to the main database?
Or do I have to build the database directly inside Visual Studio server explorer?

Hope I am saying this correctly..

Thanks in advanced
daveofgv

 

[jmcilhinney]

There are two main ways to store and connect to a SQL Server database:

1. You create the database in Management Studio and then it is not strictly part of your application. The database is permanently attached to the SQL Server instance. That instance may or may not be on the same machine as the application. After deployment, multiple users can connect to the database.

2. You create the database in Visual Studio and then it is part of your application. The MDF data file is displayed in the Solution Explorer along with the rest of your source files. When you build, that MDF source file is copied into your output folder and the copy is attached to a SQL Server instance on the local machine on demand. Only that one client application can connect to that database.

If your application is intended to be used on a single machine with the SQL Server instance on the same machine then you should probably go for option 2, especially if the instance is SQL Server Express and you don't need access to the database via Management Studio after deployment. If the application will be run on multiple machines all connecting to the same database then you must use option 1.

If it is a multi-user application then installing the database is a separate operation to installing the application. The former is done once only while the latter is done multiple times. In that case, you might create a backup of your database and restore that as part of your deployment or you might use scripts to create the database.

If it's a single-user application with a local data file then that data file gets deployed with the EXE and attached on demand, so there's not really any extra step for you to perform unless you also need to ensure that each user has SQL Server / SQL Server Express installed on their system.