TugaIT 2016 – Road to database automation

tugaitThe TugaIT 2016, May 21, was so far the biggest event that I participated was a speaker. The logistic challenge was characterized by 9 tracks, each track with 6 sessions, making a total of 64 sessions. At the end of the event two combined words remain in my mind: monstrously amazing.

My participation on the TugaIT event started on the day before, Friday May 20, with the workshop “Deep walkthrough of some of the most popular/innovative features in SQL server storage engine” by Sunil Agarwal (@S_u_n_e_e_l). In addition to get to know very interesting features in SQL server 2016 edition, Sunil Agarwal can explain how they work in a very simple and easy way to understand. In fact when you listen him talking everything in SQL server seems easy and simple. The day ended with the speaker’s dinner where I had the opportunity to know and socialize with other speakers.

Next day, Saturday May 21, I made my session “Road to database automation”. This session addressed the challenges of the first step of the database automation process: database source control. Despite being part of the last sessions of the day, I had a good assistance and very interactive. I was glad to know the more people is doing database source control.


PortoData 20 April 2016 – Database source control: Migrations vs State

My second presentation at Porto.Data (April 20) was about the two approaches, migrations and state, for database source control. During the presentation I explored the advantages and disadvantages of each approach. For migrations approach I used the tool Flyway and for state approach I used Redgate SQL Source Control.

Besides presenting the pros and cons of each approach my goal was also to show that two approaches can be needed in different parts of the system, or at different times in the development process. Size and complexity of the databases, team’s capabilities or preferences and development processes will be factors that will influence the adoption or the variation between the two approaches.

Here’s some “sensations” collected from the audience:

  • This presentation is especially useful for those who’s starting to implement database source control and want to know the available options/approaches;
  • The meaning of “introduction changes in the database” or his effects/implications are not very familiar concepts to the audience;
  • The management/articulation of changes between databases and applications is not a clear process or necessity for the audience. There remains different/separated views for databases and applications (next presentation/challenge: show how to deploy an application and a database together). 


  

 

 

Database Source Control – How?

In this post I describe some decision that you need to make when putting your database development under source control.

“Commit a SQL script to a local repository and the push to a central repository” sounds easy. In fact the all process should be easy.sqlscript

The SQL script is the fundamental resource of the database source control process.

The script can represent two perspectives:

  • The desired definition of the database object (table, view, stored procedure, function…), a.k.a state-based approach;
  • The desired modification to be applied on the database object, a.k.a the migrations-based approach;

About this two approaches I like the definition presented by Alex Yates:

  • State: Your source of truth is how the database should be.
  • Migrations: Your source of truth is how the database should change.

So, this is your first big decision: state-based approach or migrations-based approach? Can I use both approaches? (The discussion of which one it’s the better approach will be explored on a future post).

The correct answer it’s directly related to… your budget. Each one of the solutions (state, migrations or even the combination of the two) will do the work and will cost time and money.

In your budget you should consider the following tools:

  • For state-based approach
    • Redgate SQL Source Control (+info)
    • SQL Server Data Tools (SSDT) (+info)
    • DBSourceTools (+info)
  • For migrations-based approach

The next decision is to choose the source control system. All the tools above identified support or can be used with the following source control systems:

  • Mercurial
    • Is a distributed source control management tool (+info)
  • Git
    • Distributed version control system designed to handle everything from small to very large projects with speed and efficiency (+info)
  • TFVC
    • Team Foundation Version Control (TFVC) is a centralized version control that lets you apply granular permission and restrict access down to a file level (+info)

As “normal” user, meaning that I do not use APIs to work with this systems, my preference was built through the use of GUIs:sourcecontrolsystems

  1. Mercurial ->  TortoiseHg (+info)
  2. Git -> SourceTree (+info)
  3. TFVC -> Visual Studio (+info)

 

Currently my database repositories are Mercurial or Git because I prefer distribute systems. Then, once I use the migrations-based approach my first choice is Mercurial/TortoiseHg because in my opinion have a better branch representation. It´s less powerful than Git making it easier to use and teach (I taught a telecommunications team use TortoiseHg with great success).

Summing up: to answer the question “How to put database under source control?” you need to make two main decisions:

  1. SQL scripting strategy
    • State-based or migrations-based approach
  2. Source Control System
    • Centralized or distributed system

 

Database Source Control – Why?

Nowadays the question “Why put database under source control?” no longer makes sense. Probably “How to put database under source control?” is now the correct question.

But before answering the second question let me answer the first identifying the reasons that led me/us team to invest on database source control:

  •   Is the first step of database automation
    • In other words, if you want to assemble a database deployment pipeline you should put your database changes into a source control system;
  • Keep history of each change
    • Allows traceability:
      • Everything went well: just do it and forget it;
      • Something was broken: you will need to know what changed, when was changed, why it was changed and who changed it;
  • Your best communication system
    • Allows to share code (SQL scripts);
    • In team context a source control system helps to manage possible conflict of interest;
  • Enforce standards (my reference was Sjors Takes)
    • Standards, preferably combined with automation, help to reduce/eliminate conflicts.

database

How to put database under source control? is the question that follows.