Evolutionary Database Design

tg_dev3

Hi!

It's my first time in this forum! But I need your help and if you can help me I would really appreciate it.

I am trying to start building a new version control (migration based - Flyway) and database CI/CD system in my company.

Currently I think we don't work in the right way, i.e. we just develop in DEV (database that all SQL Developers access) and then do releases for UAT and PROD with a package using sqlcmd (.bat file).

Lately I've been reading about: "Evolutionary Database Design" (https://martinfowler.com/articles/evodb.html) and I find it quite interesting but I needed help on some questions.

This case would be to apply in existing and huge databases that have a lot of complexity so I don't think it will be that easy to change and I need your help.

In all the documentation I see they do tests on dockers and so on and in the "Evolutionary Database Design" model they say that: "Everybody gets their own database instance" and this is one of the points that makes me confused, I always worked in a DEV database (as well as other SQL Developers) and developed there and then we would test and move to UAT and only then to PROD.

I have seen something like vagrant and have VM with SQL Server, but I am a bit lost in this part. How can I get self-contained ("sandbox") (docker, vagrant, etc.) SQL Server to achieve: "Everybody gets their own database instance"?

Peter_Laws

Hello tg_dev3,

Thank you for the fantastic question!

That article encapsulates well many of the issues teams are facing, even more so in the scenario you're describing where the implementations are complex, as they tend to be the most change aversed for (sometimes justifiable) fear of unintended consequences.

The development configuration normally needs to go hand in hand with a dev or branching strategy, in your description you're looking at moving from the proverbial 'single source of truth' to potentially many and that can make people apprehensive.

This article discussed some of the advantages to dedicated development environments.
https://documentation.red-gate.com/fd/development-database-environment-configuration

I think the most pertinent, due to your cited complexity is increased accuracy of version control alignment.

As for how, the most common approach broadly is as follows.

• The main trunk / branch / safe and current version, is checked into a shared repo
• Dev starts a new task, branches off main
• Pulls down / clones repo locally*
• Develops locally and checks into said branch
• Once work is complete, tests passed etc, the branch can be merged back into main

Should they wish to collaborate with colleagues, they can just checkout their peers branch for as long as they need it. Much like the containers you were mentioning, a large part of this is disposability, due to being smaller, faster and simpler. The underpinning principles of DevOps is that you can build, little and often and that makes things commensurately easier to track and iterate on.

As a somewhat simplistic example, historically rollbacks have been a critical component of deployment strategies, but they don't really align with the DevOps ethos (as always, there are exceptions). The DevOps preference is to roll forwards, if there was an unintended consequence, don't undo, but rather do again, with a fix. However in times past when releases were less frequent, the updates were large, unpicking what went wrong was as a result more complex, slower. Rolling back was typically more reliable, yes you may lose some data, but you knew it was a safe state.With the advent of 'little and often' rolling forward without loss is now considerably more viable because there's a lot less to analyse, the actual deployment itself will likely be faster too.

On the topic of productivity, I'll drop back to the personal dev vs shared briefly, there's also potential gains for the protection afforded by local dev, the probability for disruption in a shared space is much higher, naturally care and defensive coding practices are important, but devs will also be sinking time into ensuring their changes don't directly impact others work and you may never see return on that investment if all goes according to plan. The loss of productivity in a shared environment is a constant risk that has to be managed.

Most of this has been conceptual, so I'll dedicate a portion to a practical implementation of how does everyone get their own SQL Server instance?

These recommendations are somewhat governed by your environment, development practices, dev count regarding scalability etc.

• At the most basic level, every dev could simply have a test db running on their machine, the simplicity has its merits and the setup complexity is low. It's robust, not particularly resource intensive and has minimal requirement on 3rd party elements. This can help keep the complexity of your security considerations down.

• Infrastructure as code - this is your Docker'esk territory, a lightweight script language defines setup behaviour, you run the code and it implements the image / application / infrastructure you defined. This has a higher initial setup complexity, but once created, you can be confident absolutely everyone who uses it, get the same thing. Depending on the scope of your implementation that could be client and server side, in fact you could create an image that encapsulated everything the a dev is permitted if you were so inclined! That last part could be a bit restrictive, I have seen some companies do that were they have extremely strict security practices, a whole development environment container is provisioned per person per project and then disposed of after, most people probably don't need to go that far, but it is possible.

• There may also be cloud based offering that provide a comfortable middle ground, for example Azure provide a subscription based SQL Server, they take care of all the infrastructure, you just connect to it with your prefered IDE and continue just as you did when it was on-prem.

The actual implementation is what best fits your companies many needs, be they security, resourcing, speed, and you'll know that better than we will. However I will say you're asking all the right questions, the direction you're looking will make your development and deployments faster, more robust and once your devs are accustomed to it, it should even be easier too!

Thanks again for asking, it's this curiosity that we're looking to encourage in the community, please don't hesitate to come back to us if you have follow up queries.

*there are many tools to achieve this, we have some nice ones. The degree of rigor, complexity and speed you require for your devs should guide this however.