It's quite easy to deploy a database from scratch. What always gets in the way is to upgrade databases, especially when you have an application of many different versions and where the data is in different states as well.
As a result of this I have been prompted to write a bit about database deployment strategies and to give some thoughts about what I believe is the way forward.
Clean / first-time deployment
The first time you deploy an application you can go for the "clean deployment" strategy. All you need to do is to create a SQL script (or scripts) that deploy your database and you're in business. A useful tool for this may be the SQL Publishing Wizard (http://www.microsoft.com/downloads/details.aspx?FamilyId=56E5B1C5-BF17-42E0-A410-371A838E570A&displaylang=en) which will script off your entire database so you can deploy it elsewhere.
One of the more difficult aspects of SQL deployment comes where the deployment will chenge depending on the target environment. A prime example of this is where you need to deploy tour .MDF and .LDF files into specific locations (e.g where your database server has SAN-attached storage) and so your create database script needs to specify this.
Another complication of environment-specific database installation is permissions and logins. Each environment will potentially have different logins.
The way that I usually get around this is as follows: I don't usually script off my database as one huge script. I usually break it down into sections (CreateDatabase.sql, CreateLogins.sql etc) and have a batch file run these scripts in order. I then use InstallShield to deploy the scripts onto the target server and I run the batch file as a custom action. Easy you might think. However, before the batch file runs I need to modify the create database script with the file locations and I need to modify the permissions script with the actual logins. This is soemthing again that InstallShield can do, or you can also perform some other sort of environment-specific replacement on your scripts before you deploy them.
Upgrading a database
Strategy #1 - Create a set of "delta" scripts
Using this strategy, you have an install for v1.0 (say). When time comes to make version 2.0 you create a script that assumes that your database is in the 1.0 state and then applies the changes. This may involve adding tables, adding columns to tables and adding data. You can even create a first step in your script that will raise an error if your database is not at the correct version.
Hint: Create a table in your database that includes the database version number. Your upgrade scripts can then update this value on each deployment, and it's a handy way of making sure that you don't run scripts incorrectly and a means of having a confidence check in your deployment.
The advantages of this method is that everything is above board. If you are installing an application v3.0, and your database is at 1.0, you need to install database v2.0 and then database v3.0 and you're in business. The downside of this is that if your database structure is out of kilter then everything breaks irretrievebly - but then again is that such a bad thing? Another downside is that you never get a "complete" view of your database objects as the scripts to create then become fragmented across many sql scripts as you progress through versions.
Strategy #2 - use a SQL differencing tool!
This is a simpler strategy to implement if you have already got the scripts to create your database from scratch. What you do here is firstly maintain a "reference" installation of each of the databases you have in your live environment. You then create your new database installation by maintaining your database build scripts. When it comes to release, you use some sort of SQL Comparison tool to detect the differences between the database installations and generate a script to upgrade from one to the other.
An advantage of this approach is that your developers only work with scripts to create the new database standard (from scratch) and the upgrade process becomes an issue of release management, which may be controlled in a different way. Also, if you need to support customers who have different database structures that were in service before you got control of the deployment process you can just take a reference version of their database and create a script for them.
The downside of this approach is that it often requires use of desktop tools and so it often does not lend itself to automatic build processes, so there is a human fators element in there and not a process automation. From a quality perspective, this is to be avoided.
Strategy #3 - use re-runnable scripts
One of the features of a database is that it is really only the tables that have to be retained when you upgrade a database. This means from the point of view of the deployment you can drop / recreate most of your database objects from views, stored procedures, indexes etc. The scripts might look something like this:
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[usp_HelloWorld]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[usp_HelloWorld]
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
-- Author: Andrew
-- Create date: 7/7/2009
-- Description: Hello World
CREATE PROCEDURE usp_HelloWorld
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
SELECT 'Hello World!' AS HelloWorld
The other thing with tables is that of course you can check to see if they are there, so your script for a table might look similar, but just wouldn't have the DROP statement at the top:
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MyTable]') AND type in (N'U'))
CREATE TABLE [dbo].[MyTable](
[ID] [uniqueidentifier] NOT NULL,
[SomeText] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY CLUSTERED
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
PRINT 'Table MyTable added.'
PRINT 'Table MyTable already exists.'
The other issue is where we upgrade tables by adding columns to them. The way that I would do this in my projects would be to have a script for each table, and the create table script will always create the table at the latest version. The for each upgrade I will add something like this in the same script but underneath the CREATE statement:
IF NOT EXISTS (SELECT * FROM syscolumns WHERE [name] = 'AnotherColumn' AND id = (SELECT id FROM sysobjects WHERE [name] = N'MyTable'))
ALTER TABLE dbo.MyTable ADD
AnotherColumn int NULL
PRINT 'Column AnotherColumn on table MyTable added.'
PRINT 'Column AnotherColumn on table MyTable already exists.'
Using this strategy, you can create a set of database installation scripts that can be run and re-run any number of times, and by runing the scripts you will upgrade the database to the latest version whether by install or upgrade. In my opinion, this is the safest way to write scripts for your database as you run the same set of scripts on all databases and can re-run them any number of times. It makes the process of patching easier as again you just need to run the full set of scripts.
As I said at the start, we've been doing this for years and yet time and again I see projects where this isn't going right. I think it is key for a project to decide on a database deployment and upgrade strategy and to stick with it. It won't go right if you leave it to chance and hope. Make sure the developers in your project team know what the strategy is and when creatign scripts make sure that all scripts run OK. Test deployment and upgrades thoroughly.