G’day,

Well, it’s T-SQL Tuesday time again  -the blog party started by Adam Machanic (blog | twitter) and this month hosted by “The Database Avenger”  James Anderson. This month we’re talking about Shipping Database Changes – something I’ve done many times and mostly correctly – although we all seem to remember the few times it went wrong – rather than the many, many times it went smoothly.

While it’s good to learn from mistakes – it’s also good to learn what factors that contribute to successful deployments.

One of things I really try to avoid at all costs is dropping objects – of any sort. If I need to to drop an object for any reason then I’ll be asking myself why and could I do anything to avoid this. I’ll also be getting several different opinions of how other people would go about the same task.

I’ve been on different sides of the fence for many different deployments – from the database developer producing a script for the DBA, to the DBA receiving a script from the developer, to the person reviewing processes for deployments to the person wearing all of these hats at the same time.  Wearing many hats at the same time requires discipline and a strong character – as invariably, you’ll always get someone attempting to manipulate the process to suit their own needs, something that I’ve seen happen in small shops quite a bit.

This happens in large organizations too – occasionally. You’ll get people who will attempt to “influence” the process – due to factors such as time constraints. And if the culture in the team is not strong (and the process is susceptible to manipulation) then this can often over-ride the process (however, strong that process may be) and then you’ve got the start of a very serious problem coming.

This is where dev-ops can often help, reducing the amount of human interaction that is needed to push a change though multiple different environment.

But back to dropping objects – I see scripts like this far more often than  I do attempts to manipulate deployment processes.

Often this is due to tools that have been used to script changes and they have produced things such as “Drop Object” and then “Create Object”.

When I was an operational DBA I’d have checks in place to look for DROPs in all scripts and if they are present to request more information from the author. The script may have gone through a change management process – but often that is a rubber stamping exercise and will not pick up any inconsistencies or bad practices.

I was really glad to see statements such as CREATE OR ALTER come out. While there’s always been ways of doing similar things, the syntax is more obvious – and, in my opinion, more likely to be used. But, you’ll have to be on 2016 for that – it may seem a small factor to most people, but it’s one of my favorite editions to the product.

Have a great day.

Cheers

Martin.