In my blog post here https://the.agilesql.club/2019/06/what-steps-are-there-to-move-to-safe-automated-database-deployments/ I described the steps you need to go through so you can build up your confidence that you are capable of deploying databases using automation. I mean, afterall, knowing that it is possible to automate your deployments and having confidence that they will succeed are two very different things.

Even with the best tooling in the world, automated database deployments are still a struggle and there is one key thing that you can do, no matter what tools you choose and that is to make the deployments re-runnable. (Insert discussion here on the word idempotent and how it means re-runnable but sounds far cooler and intellectual). If you make your deployments re-runnable then you can, by their very definiton, re-run them.

What does this mean?

It means where we write scripts ourselves we need to add checks and guards against doing things that we have already done. There are two main approaches to this, you can either verify if the change needs to be made OR you can record the fact that you have made the change. I prefer the first option but both are valid.

Option 1 (the correct option in my opinion, but there are other opinions)

Imaging you have a table with this definition:

create table abc
(
    id int identity(1,1) primary key clustered,
    first_name varchar(max) not null,
    elephant_dob datetime2 not null
)

You realise, midway through your sprint that actually the elephant_dob column should be on the elephant table rather than on the abc table so you script it up:

    select id, elephant_dbo into elephant from abc;
    alter table abc drop column elephant_dob;

You deploy the script and it is amazing, then for some reason you need to re-run the script, you know what happens? Errors, a sea of errors.

If you re-write it so it looks like this:

    if not exists(select * from sys.tables where name = 'elephant')
    begin
        select id, elephant_dbo into elephant from abc;
    end

    if exists(select * from sys.columns where object_id = object_id('abc') and name = 'elephant_dob')
    begin
        alter table abc drop column elephant_dob;
    end

Now when you re-run it, it works perfectly. The things to note are that I check each object I change so if, for some reason the first part succeeds but the second doesn’t, I can carry on from where I left off.

Now, what if I later decide that abc was exactly the right place for the elephant_dob? Well I need to keep on top of my change scripts and delete them every now and again.

Option 2 (not my favourite but if you do this I won’t cry)

The second option is to keep a log table of deployments and check whether you need to run, something like:

if not exists (select * from deployment_log where id = 'some_id_maybe_a_jira_or_ticket_number')
begin

    select id, elephant_dbo into elephant from abc;
    alter table abc drop column elephant_dob;

    insert into deployment_log(id)
    select 'some_id_maybe_a_jira_or_ticket_number'
end

or for bonus points

if not exists (select * from deployment_log where id = 'some_id_maybe_a_jira_or_ticket_number.1')
begin

    alter table abc drop column elephant_dob;

    insert into deployment_log(id)
    select 'some_id_maybe_a_jira_or_ticket_number.1'
end

if not exists (select * from deployment_log where id = 'some_id_maybe_a_jira_or_ticket_number.2')
begin

    alter table abc drop column elephant_dob;

    insert into deployment_log(id)
    select 'some_id_maybe_a_jira_or_ticket_number.2'
end

This will work mostly, just make sure you don’t mess up your deployment log table.

Final advice

Thinking up front about your logical model, rather than rushing to the physical design would have probably saved you having to move your elephant_dob column at all but where we need to make changes, knowing that all your scripts are always re-runnable is a literal god send.