Why is a good Deployment-Script for MS-SQL Databases so important and on the other hand so difficult to write?
Most of the time when we develop an application there is also some database in the backend and often we face the problem that we need to automate the deployment of it. One way to do that is for example to setup a tool chain with Bitbucket, TeamCity, Artifectory and Liquibase. We did this in one of our projects and we faced a lot of issues by that. Of course it’s policy conform but it’s from my point of few far too complex and we again and again faced hard to solve problems. So how could a better solution look like? After several different apprroaches I came up with a deployment script which is already written during the development and allows to easily manage different instances like DEV, UAT or also PROD.
How could such a script look like?
Well there are different possibilities. One would be to check for each and every element we would like to create, change or drop if it exists already. But that is not always so easy. Better from my point of view: bring all required steps into a logical order and store within the database the last successful performed step. Within the setup script you can then check which was the last step and which outstanding steps still need to be performed. This can look like the following:
IF NOT EXISTS ( SELECT *
FROM sysobjects
WHERE id = OBJECT_ID(N'dbo.tblBuild')
AND xtype='U')
BEGIN
CREATE TABLE [dbo].[tblBuild](
[Step] [int] NULL
) ON [PRIMARY]
END
GO
-- --------
-- step 001
-- --------
IF NOT EXISTS ( SELECT *
FROM dbo.tblBuild)
BEGIN
INSERT INTO tblBuild(Step) Values(1);
END
GO
So here we first check if the table tblBuild already exists. If not, then we create it. This will be used to store the last successful deployed step. As we already have done something we store that success already as step 1. For that we check if the table is still empty. This will only be the case if the table was created right now. So we add a row in that case and store the success of step 1 by that.
Now we can continue with step 2. Here maybe we would like to create a user table.
-- --------
-- step 002
-- --------
IF EXISTS ( SELECT *
FROM dbo.tblBuild
WHERE STEP = 1)
BEGIN
CREATE TABLE [dbo].[tblUser](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Email] [varchar](255) NULL,
[PasswordMD5] [varchar](255) NULL,
[NeedToChangePwd] [int] NULL,
CONSTRAINT [PK_tblUser] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
UPDATE dbo.tblBuild SET Step = 2 WHERE 1=1;
END
GO
So first we check, if the last successful deployed step is #1 and if so, we run the CREATE TABLE. Afterwards we update tblBuild and store the success of step #2. So next time we execute that script the check if the last successful step = 1 will fail and the table will not be created again.
By doing so step by step we can countinue to update all tables and related constraints. So in the next step we may want to add a constraint to the table that is adding a default value to a column
-- --------
-- step 003
-- --------
IF EXISTS ( SELECT *
FROM dbo.tblBuild
WHERE STEP = 2)
BEGIN
ALTER TABLE [dbo].[tblUser] ADD CONSTRAINT [DF_tblUser_NeedToChangePwd] DEFAULT ((0)) FOR [NeedToChangePwd]
UPDATE dbo.tblBuild SET Step = 3 WHERE 1=1;
END
GO
So we break down all relevant changes into atomic portions and run them one after the other. So let’s add one more table and create a relationship to the user table. These are two different steps so let’s break them also in the setup script.
-- --------
-- step 004
-- --------
IF EXISTS ( SELECT *
FROM dbo.tblBuild
WHERE STEP = 3)
BEGIN
CREATE TABLE [dbo].[tblSession](
[ID] [int] IDENTITY(1,1) NOT NULL,
[UserID] [int] NULL,
[GUID] [varchar](45) NULL,
[LastAction] [datetime] NULL,
CONSTRAINT [PK_tblSession] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
UPDATE dbo.tblBuild SET Step = 4 WHERE 1=1;
END
GO
-- --------
-- step 005
-- --------
IF EXISTS ( SELECT *
FROM dbo.tblBuild
WHERE STEP = 4)
BEGIN
ALTER TABLE [dbo].[tblSession] WITH CHECK ADD CONSTRAINT [FK_tblSession_tblUser] FOREIGN KEY([UserID])
REFERENCES [dbo].[tblUser] ([ID])
ON DELETE CASCADE
UPDATE dbo.tblBuild SET Step = 5 WHERE 1=1;
END
GO
The script we create that way can be executed as often as required. If an error occurs (let’s say we have a syntax error in step 4) then no subsequent step will be executed. The build table will always tell us what the last successful step was and we can start to bugfix the one which failed. Never the less the database admin who executes that script can put it into a transaction context and first doing a dry run by rolling back the transaction afterwards. And only if that dressrehersal runs thru successful he can execute the script again but commiting the script this time. The manual effort for that is similar to a TeamCity deployment but it offers much more possibilities in case something goes wrong.
How to handle StoredProcedures and Functions?
But of course – also this approach is not perfect. One of the key problems is the handling of StoredProcedures and functions. For those we need to change the approach a little bit. This objects can always be dropped and recreated without loosing any data. We use that fact and simply do so in the script
IF EXISTS (SELECT *
FROM sys.objects
WHERE type = 'P'
AND name = 'sCheckUserForProject')
BEGIN
DROP PROCEDURE dbo.sCheckUserForProject
END
GO
create procedure [dbo].sCheckUserForProject
@SessionID varchar(45),
@ProjectID int
as
begin
select u.*
from dbo.tblUser u
inner join dbo.tblSession s on u.ID = s.UserID
inner join dbo.tblProject_nm_User nm on u.ID = nm.UserID
where s.GUID = @SessionID
and nm.ProjectID = @ProjectID
end
GO
We only need to remember that we also GRANT EXECUTION if required afterwards to the StoredProcedures as they loose all security configuration when we drop it.
Conclusion
The combination of step by step approach for all table objects, schemas, roles etc. and the drop/recreate for everything else (Views, StoredProcedures, Functions) we can create a very stable and reliable Deployment-Script for MS-SQL Databases which will update the database always to the latest version – regardless of the version it currently is in. By that it’s pretty simple to manage different environments like DEV, SIT, UAT, PRE-PROD and PROD without too much effort. And we need to deal only with one file under version control and can control the composition of the file much better than we can do if we use LiquiBase or other tools for that. So from my point of view still the prefered way of deploying database updates as part of the release management.