Database Installer For Production

This article explains how to install a database to production and build a tool to do so.

Objective

Many times it is necessary to install a database to a production machine which is not accessible from the development environment.

This article explains how to install a database to production and build a tool do so.

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.


The tool (following image) uses these model files to install the database.




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.

    Process installProcess = new Process
    {
        StartInfo = new ProcessStartInfo
        {
            CreateNoWindow = true,
            WindowStyle = ProcessWindowStyle.Hidden,
            FileName = "VSDBCMD.exe",
            Arguments = @"/a:Deploy /dd /cs:""" + ConnStr + @""" /dsp:Sql /model:" + modelName + @".dbschema /p:TargetDatabase=" + DbName + @" /manifest:" + modelName + @".deploymanifest"
        }
    };
    installProcess.Start();
    installProcess.WaitForExit();

For uninstalling (deleting) the database, just enter the connection string and the name of the database.

This drops the database.

    SqlConnection conn = new SqlConnection(ConnStr);
    SqlCommand command = new SqlCommand("DROP DATABASE " + DbName, conn);
    conn.Open();
    command.ExecuteNonQuery(); 
    conn.Close();

You will see the installed database on the server (as shown below).



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.