Chapter 18: Database Deployment

Overview

The problem that you will face relatively often is that, while you develop your database (and application) in a development environment, you must deploy the database first in a test environment and then in a production environment. Initially, you need to deploy the complete database, but later you will have to update the database with design changes and hot fixes.

I will present solutions using two different approaches. One approach is for developers who have Visual Studio .NET (and who are probably doing both database and application development). The other, more traditional approach is geared toward SQL Server specialists who are working with traditional database development tools. I will use the tools that are delivered with SQL Server, as well as tools that I have developed, to automate some processes.

Traditionally, RDBMS installation is perceived as complicated, and your customer will understand that you need to send a database administrator (or a couple of them) to set up the environment. When you work with a file-based database system such as Access, database deployment is not a big issue. You usually create a setup program for your client application, and your database (.mdb) file is just one more file that must be installed on the computer. When you are working in a client/server environment with SQL Server, you first have to install and configure the database server and then install databases.

Fortunately, installation of SQL Server has been simplified significantly. Almost anyone can perform it and there are usually no problems. In fact, SQL Server can be configured to be installed unattended. Microsoft SQL Server 2005 Express Edition is designed to be deployed on client computers using a special set of setup files that can be included in your setup program.

Some early versions of SQL Server required that all dependent objects be present on the server before a new object could be created. Administrators had to use many tricks to transfer a database from one server to another. The introduction of Deferred Name Resolution has reduced the complexity of database deployment in the SQL Server environment. For example, a stored procedure can be created even if it references a stored procedure that is not yet on the server. Unfortunately, it is not perfect yet. For example, it is not possible to create a foreign key that references a table that is not yet in the database.

The methods for database deployment can be divided into two groups:

• Deployment of a complete database

• Deployment of individual objects