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.
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.