Post
Ask Question

Database Installer For Production

Objective

 
Many times it is necessary to install a database to a production machine that is not accessible from the development environment.
 
This article explains how to install a database for production and build a tool to do so.
 
The free source code of the tool is available for download.
 
Note: If your zip archiver is unable to open the download, please use 7 Zip (free) to do so.
 

Implementation

 
 The tool I have made uses VSDBCMD.exe for actually deploying the database from a model of the database.
 
The first step is to create a Database Project in Visual Studio for the database. This project should contain all the SQL for the tables, stored procedures, post-deployment script, etc.
 
Then build the database project. This creates model files in the sql\release folder of the project. The following image shows the files that are created by the build.
 
Buildfiles.gif
The tool (following image) uses these model files to install the database.
 
Modelfile.gif
 
Use the browse button to get the path to the model files.
 
The connection string contains the Data Source, User ID, and Password (or Integrated Security, if the database can be accessed by the logged-in user account).
 
The database name is the name of the database created from the model.
 
After entering this information, when you click install, VSDBCMD.exe is invoked in a Process. This installs the database on the server.
  1. Process installProcess = new Process {  
  2.     StartInfo = new ProcessStartInfo {  
  3.         CreateNoWindow = true,  
  4.             WindowStyle = ProcessWindowStyle.Hidden,  
  5.             FileName = "VSDBCMD.exe",  
  6.             Arguments = @ "/a:Deploy /dd /cs:"  
  7.         "" + ConnStr + @ ""  
  8.         " /dsp:Sql /model:" + modelName + @ ".dbschema /p:TargetDatabase=" + DbName + @ " /manifest:" + modelName + @ ".deploymanifest"  
  9.     }  
  10. };  
  11. installProcess.Start();  
  12. installProcess.WaitForExit(); 
For uninstalling (deleting) the database, just enter the connection string and the name of the database.
 
This drops the database.
  1. SqlConnection conn = new SqlConnection(ConnStr);  
  2. SqlCommand command = new SqlCommand("DROP DATABASE " + DbName, conn);  
  3. conn.Open();  
  4. command.ExecuteNonQuery();  
  5. conn.Close(); 
You will see the installed database on the server (as shown below).
 
sampledb.gif
 
So, you can copy the tool and the output of the database project (model) to the production machine and install the database using the tool.
 
There is also a Setup project which produces a Setup.exe. This can be used to install the tool on a computer.
 
Note: The tool needs .Net 4.0 Runtime to run. It can install models created in VS 2008 and VS 2010 database projects for SQL Server 2005/2008.


Similar Articles