T-SQL Tuesday #90 – The Elephant’s Name is Database

Last Updated on May 10, 2017 by John Morehouse

T-SQL Tuesday is a monthly blog gathering for the SQL Server/Data Professional community  It is the brainchild of Adam Machanic (B|T) and is not limited to just things around the SQL Server database engine. Each month a blogger hosts the event and anybody who wants to contribute can write a post about that month’s topic. You can find a list of all topics at http://tsqltuesday.com/.  

This month’s T-SQL Tuesday topic is about continuous database deployments and Database Lifecycle Management (DLM).  It is being hosted by James Anderson (B|T).

The Elephant

In my experience, the database is the final thing to be thought of when it comes to continuous deployment. Nobody wants to tackle it and it can be hard to get right.

It is the elephant in the room.

I’m sure that we all have been a part of a deployment where the database wasn’t deployed successfully.  If it was a large deployment meaning lots of changes (both schema and data), fixing things on the fly probably (and did) suck.

Thankfully, available tools have come a long way regarding databases and how they are being deployed.  These tools, such as Red Gate, Octopus Deploy, Team City (just to name a few) have made amazing strides in helping organizations implement database lifecycle management into their environments.

I’m a Fan

While realizing that continuous database deployments can be difficult, I’m a huge fan of them.  I’ve been pushing my current employer to get them implemented for some time.

In a nutshell, there are several benefits from having continuous database deployments:

  1. Source Control.  Source Control.  Do I need to repeat this again?  The database IS code. It belongs in a source control system of your choice.  Yes, it can be difficult.  Do it anyway.  You’ll thank me later.  Note: Backups are NOT a form of source control.
  2. Make It Suck Less (MISL).  I’m borrowing this phrase from a former co-worker (thanks Matt Meyer!) and it’s one that I use often.  Database continuous delivery makes my life easier as a DBA.  If I have the appropriate tools and processes in place, deployments become a point and click event.  Heck, you can even SCHEDULE it.  See #4.
  3. Process consistency.  I’m a DBA that supports multiple application development teams.  Each team wants to deploy database changes differently.  Some use DACPAC’s others use scripts.  This causes a bottleneck issue simply because there is (currently) one of me and 40 developers.  By having DLM in place, EVERYBODY follows the same path to get things into Production.  As a side note, I’m also a believer that the DBA’s should follow the same path, even for their own administrative databases they control.  Just because you might have sysadmin rights to production doesn’t mean you get to go outside the process in a non-emergency situation.
  4. Trusting the tools.  I need to trust the tools to ensure that things go smoothly.  This is hard for DBA’s I think.  We want to see what is changing.  We want to touch it, examine it, play with it, throw it against the wall and see what sticks.  We become the bottleneck for Agile environments.  If we trust the tools that are in place, we remove the bottleneck from the process and we help our development teams to become even more Agile.
  5. Security.  I’m a stickler for permissions and maintaining security in all environments.  Continuous database delivery helps me to lock things down.  If the process is implemented and the tools are trusted, then developers don’t need modification permissions to anything in any environment.  Sure, give them read permissions, but the ability to change things outside of source control should be prevented.  If you don’t lock things down to the process itself, mass chaos can erupt.

All of these helps to address the elephant in the room.

The Switch

Ok, I’ll admit that I wasn’t always sold on database continuous delivery.  I was that skeptical database administrator that thought it was all hokum.  That all changed.

Several years ago I was a part of a project at Farm Credit Services of America to ease deployment pain.  At the time, we were deploying things daily  and the database was definitely a bottleneck.  It was all very manual.

Then I got to meet Alex Yates (B|T) and Sean Newham (T) both from Red Gate at the time.  We were able to construct an agreement with Red Gate to get them onsite (all the way from the UK!) to help us architect a continuous delivery process.

After spending 3 days in a room with Alex, Sean and a team of developers, I was completely sold.  Alex and Sean clearly and efficiently explained all of the parts of the process and how things should work.  It was VERY clear to me that database continuous delivery would deliver all the points I’ve listed above.  Simply put, it would make my job EASIER!!!

Easier.  Let that sink in for a moment.  Why wouldn’t you want a process that makes things suck less?

[Plug] Alex now runs DLM Consultants and I can’t recommend him enough.  If you want to implement database lifecycle management in your organization but don’t know where to start, he can definitely help you out.

Summary

I’m a die-hard operational DBA.  I love the internals of SQL Server.  That being said, I am completely sold on the value that database continuous delivery brings to the table.  By implementing it correctly, you can secure your environment, make things suck less, trust the process and remove the database as a potential bottleneck.  If you do not have it in place currently, I would urge you to at least take a look at it.

It’s time we as DBA’s kicked the elephant out of the room.

 

 

© 2017, John Morehouse. All rights reserved.

Hey you! Leave me a comment and start a discussion!

This site uses Akismet to reduce spam. Learn how your comment data is processed.