Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Distributing SQL Server Databases

By Vasant Raj,

Distributing SQL Server database

Introduction

Most of the applications created today need to maintain a database. The data itself is so important these days and critical for customers, so maximum care has to be taken in creating and using the database.

Microsoft SQL Server is becoming very popular ever since the release of the SQL Server 2000 version. The major reasons for this are the ease with which the database server can be maintained and the flexibility in using various tools. In this article, we will look into various ways to deploy a SQL Server database for a specific application and also how to create SQL Server installation using Visual Studio in detail.

Methods of deployment

The methods for deploying a database can be classified into two categories: Manual and Automatic. In manual mode, we can:

  • Restore the backup files.
  • Run SQL script for database creation.
  • Create database manually at the time of application installation. (rarely done)
In automatic mode, we will create a separate application to:
  • Restore the backup files.
  • Execute the SQL Scripts.

In addition to the above two options, it is also possible to create an MSI/EXE installer to do the SQL Server installation. Various third party tools are available for creating an MSI/EXE file. If you already have a copy of Visual Studio installed then it would be very easy to create an installer without any additional cost.

The application presented here can be created in any .NET language of your choice.

Creating an MSI/EXE with Microsoft Visual Studio 2005

Follow the steps below for creating a simple installation package for SQL Server database.

  1. Open Visual Studio 2005 and create a New Project by selecting "Setup Project" in the Setup and Deployment section.

  2. A new project will be created. In Solution Explorer, right-click the solution and Add a New C# Class Library project. So now, the solution has two projects.

  3. Right click on the DBInstaller project and click "Add New Item". Select the new item as "Installer Class".

    The installer class is part of the "System.Configuration.Install" namespace. This is the base class for all the custom installers in .NET framework.

  4. Add the SQL Server script files in the DBInstaller project. Set the "Build Action" property of each SQL Script file to be Embedded Resource. By default, it will be Content.
  5. Open the code window for the Installer class and override the Install, Commit, Rollback, and Uninstall methods. It is not required to override all the methods. For example, if just the installation has to be done, then only override the Install method. If any specific task has to be performed during un-installation, override the Uninstall method.
  6. Write the logic for reading and executing the SQL Script files in the Install method. Reading of SQL script files can be done using various functions from System.IO namespace (check sample code attached). Execution of the SQL Files can be done using the ADO .NET classes. You can also check for error conditions and throw exceptions. These exceptions will be reflected in the installer MSI/EXE. (For more details look at the sample setup)
  7. Right-click the Setup Project MyDatabaseInstallation and add the DBInstallerClass project as the project output.
  8. Build the MyDatabaseInstallation project and if no errors, the MSI/EXE for SQL Server is ready for distribution.

Sample Setup

The sample setup will install a database ("MyTestDb") on the local instance of SQL server. Sample setup can be enhanced by adding SQL scripts for creating tables, stored procedures and other database objects.

Conclusion

Automating the deployment task will reduce the SQL Server configuration time and complexity involved. As it happens most of the time, people who perform installations do not have required knowledge about the configuration of the database. They need a step-by-step guide to get it done. This process can be replaced by a simple executable that will do all the application specific modifications to the SQL Server.

Resources:

SampleDBSetup.zip
Total article views: 5424 | Views in the last 30 days: 9
 
Related Articles
FORUM

Project Server 2007

Installing Project server 2007

FORUM

Creating an installer for a SQL database

want to create installer package for a database

FORUM

Fresh install no database server

Fresh install no database server

FORUM

Where is the SQL Server project template?

Where is the SQL Server project template? I need to create a CLR stored procedure!

FORUM

Need help for creation database project

Pleae post your view on how to created and maneger the database projects and changes.

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones