Malleable Source Control

Git has proved to be a better fit to the needs and workflow of a database development team than anything that came before. Git is valuable because it encourages branching and merging, giving more choice in the way that your team can work. Due to the ease with which you can adapt Git, there is no obvious best approach to using Git with SQL Server databases. So much depends on the way that the team works best, the nature of the database, and of the applications that use it.

The biggest problem with databases, I've found, is working out what is the 'source'. With procedural code there is nothing to debate, it is simple. You can't 'migrate' an application. You build it from source. With databases, it all gets tricky. For a start we don't major on doing builds. For a decade or so, none of us have done an offline release where we built a production server from source on every release and then imported the production data. Instead, we migrate it from the existing version to the new release whilst preserving its data. We certainly have some code in databases that looks very much like conventional procedural source. Modules or routines, by which I mean code-based objects such as procedures, views, functions, triggers and so on, fall easily into that category. Under the covers, such code gets replaced even if the object containing the code is only modified. Not so with tables, constraints, relationships and indexes. The source of a table isn't maintained as metadata, though it can be represented or generated in script-form in a rich variety of ways that all generate the same database object. In other words, a table can be represented by several different sorts of scripts that all look different; you've lost the one-to-one correspondence between the script and the created object. This makes source control, which works out what changed by comparing scripts, trickier because you can get 'false positives' just by using a different way of generating a script. To add to the head-scratching, how do we deal with data? Obviously, only the enumerations can go into source control but where do you draw the line?

Fine, you might say, a migrations approach is surely better because it reflects the natural way of developing, where you are generally altering database objects. Your comments aren't trashed, and your code builds the database, migration by migration. True, but you tend lose the narrative at the object level, when you need answers to the questions 'who changed what in this table, when and why?'. You also need to rebuild from scratch occasionally, to make sure that there are no uncontrolled changes. If your databases always migrate, all sorts of anomalies such as disabled constraints can creep into the picture and can get into the canonical source if you are careless in the way you consolidate excessive migrations.

I suspect that there will always be diverse techniques for source control in the database, because of the enormous differences in their size, nature and requirements. We will, I suspect, make best progress in refining database development techniques with tools that encourage diverse ways of working, such as Git.

Phil Factor

Join the debate, and respond to the editorial on the forums

During development you need a fast, automated way to build multiple copies of a database on any development or test server, with each database at the right version. This article provides a PowerShell automation script for Flyway that will do the job.

How to create a batch file that executes any number of database migration tasks across a range of servers and databases, using Flyway.

A frequent question among those learning about DevOps is "How do we do code reviews in this automated process?". Grant Fritchey shares three options - and a word of caution - to ensure you can appropriately understand the changes happening in your system

Last week I was reviewing an article and found myself needing information on the use of NOCOUNT as a standard...

One story I hear over and over goes like this: I t...

I’ve been doing SQLskills training recently, and Paul Randal (blog | Twitter) reminded our class that zeroing out a transaction log file does not use zeroes (0x00). Well, not...

Have you ever run across an issue that made you take a step back and scratch your head? Recently, I had this exact situation hit my Inbox. One of... The...

Edit: Paul has pointed out some significant gaps, so I will be reworking this post in the future. Locks are accurate for this particular scenario, but apparently there are...

Total: 1 Average: 5 What is a Transaction Log? There is a requirement in relational database systems that transactions must be durable. This is “D” in the ACID properties...

Virtual Log Files (VLFs) are part of the SQL Server log file. When space is allocated in the log due to growth, that new chunk of log is broken...

The ability to code is always a plus, but there ar...

I’m pleased to announce that I have selected an ...

This is now the third post in a series on Azure tags...

Join Microsoft's Rohan Kumar, and a full team of Azure SQL experts, for Innovate Today with Azure SQL. This free digital event from Microsoft lets you choose the sessions and technical demos that are most interesting to you and focus on the capabilities and possibilities that you want to learn more about.

In this demo webinar, discover how Redgate's end-to-end Compliant Database DevOps framework enables your organization to Standardize, Automate, Monitor & Protect it's databases.

Oracle sequences can be used to create artificial ...

Total: 1 Average: 5 Introduction to Bit Data type The data type represents an attribute that stores integer, character, data time, binary data. Suppose you have a web application...

DevOps brings operations and development closer together, but with GitOps that whole process can be accelerated.

The “greater than” operator returns TRUE when the first argument is greater than the second argument.

So if I can’t modify or add indexes and I can’t change code, how do I get my query times to drop?..

Cache Rules The post Detecting Cache Pressure With...

Paul White takes a deep dive into the way parallel...

In all the testing I’ve done recently with impor...

With Microsoft once again ranked as a Leader in th...

Navigation between reports is the hallmark of an interactive reporting solution, enabling the ability to drill-through and see relevant details and contextual filtered information in a target report. Power...

We've seen people comment that Power BI is SLOW. B...

This book aims to use Query Store to improve your SQL Server queries, how does it fare?

I started to add a daily coping tip to the SQLServ...

In this post we look at Transparent Data Encryptio...

SQL Server offers a lot of flexibility when working with objects like stored procedures and temporary tables...

If you are reading this article, most probably, you are already familiar with SQL. You know how to write basic SQL queries...

I’ve never used the FETCH or OFFSET commands for...

A couple of years ago I wrote a post on how to generate a random number in SQL Server. https://sqlundercover.com/2017/06/22/generating-random-numbers-for-each-row-over-a-specified-range-and-other-funky-stuff-you-can-do-with-them/ It’s ok and it works, it’s also a method...

In this article, Edward Pollack explains the benefits of a SQL Server data dictionary and how to build one.… The post Building a SQL Server data dictionary appeared first on...