"Even the wishes of an ant reach to heaven."

Database without dba

An Agile/DevOps Database approach for (very) little teams.

Adrien Sales/Ville de Noumea

DevOps

Goals

• Increase Application uptime
• Reduce overall IT spent on Infrastructure
• Increase release velocities : faster and more often

What is DevOps

in a few words

• Collab between IT professionals
• Culture of (operational) continuous improvement : facts rather than philosophy
• Not a prescribed set of practices, but rather high depending on teams, technologies and processes which make up organization

Communication

The core

• Educate each other
• Understand what matters most to their counterparts
• Dev understanding how underlying systems are configured/working
• Knowledge of impact of changes between dev and prod

Communication

• Empathy between the different IT stakeholders
• As empathy grows in the IT Org. teams begin naturally come together to collaborate
• Egocentric vs. holistic view of the system and collaboration to optimize the value stream

Shifting Left

• Bringing production concerns further left in the develop/test/deploy cycle
• Making note of specific concerns that will need to be addressed for the push to production
• Working on issues before they become issues
• Reduce time and resource spent in troubleshooting

Cost of a Bugfix

Not automating every process, but rather only those tasks that are manual, rote, tedious aspects of the job and consume a great deal of your time.

Free up time for IT pros. to think about more strategic issues, and spend more time collaborating with other groups to drive additional value through innovation in their products and services.

Benefits

• Environments more in sync
• More testing environments (and easier to reproduce/fix bugs), see previous slide
• Accelerates delivery of applications
• Increased uptime
• More frequent release of innovative features

DBA and and DevOps

Great, but ... what can we do NOW to save resources and focus on the real deal ?

Human values

• Have empathy for people working with you
• Respect and rely on people expertises, and valorize them
• Never stop to improve yourself as well as the system

shintoism

"The followers of Shintoism believe that spiritual powers exist in the natural world. They believe that "spirits" called kami live in natural places such as in animals, plants, stones, mountains, rivers, people and even the dead."

IT shintoism

Loving softwares and your system

• Have empathy for systems, code and software : consider them as living things
• Create a place where they will be living together comfortable
• Respect them, use them where they are good at.

Now, let's deal with real life.

The sentences that triggered everything

"We are not comfortable with database schema upgrades."

"We don't have time to read the sqls you provide : we do pass them as provided without checking potential design issues as we don't necessary have the required knowledge."

Still remembering that day...

...and finding opportunities.

Agility, flexibiliy

• From weakness to opportunities
• Automate
• Version/tag anything
• Manage database like any other system component
• Manage databases as (and by) code
• Push changes to databases without granting human access

Motivations

• No production DBA
• Architect/developer who was previously Oracle/PgSql production DBA
• Focus on automation for all
• Motivation to deploy faster/more with no specific database knowledge

Motivations

• Wish to version everything
• No resource/time to test database design quality in the production team but focus on these aspects on the dev side
• All Linux users and commited in open source development and tools

Team

Dev and Ops

• David Majorel/opt.nc (sysadmin/architect/ansible)
• Adrien Sales/Noumea City (architect/dev/dba knowledge)
• Michele Barre/Noumea City (java/j2ee developer)
• Melanie Gault/Noumea City (sysadmin/architect/puppet)
• Sualeh Fatehi/Schemacrawler (schemacrawler lead developer)
• J2EE Developers involved in continuous improvement and giving feedback.

Roadmap

Achieved today

1. Deploy & upgrade databases schemas easier
2. Document database schema in an automated way
3. Keep in mind that we could one day have a dba and hence provide him the tools to review the sql dev team send in input
4. Check and report database design issues

Tools

• Liquibase : "source control for your database"
• Schemacrawler : "schema discovery and comprehension tool"

Key concepts

• Changes are described in a simple, readable format
• Everything you need to know about the why of a database change is tied to the change itself
• It is easy for DBAs to review changes before deployment

Security

• We don't want apps to upgrade database themselves
• Apps are connected to db with dedicated accounts that can only execute DML
• Apps cannot run DDL (add column/drop table...)
• Table privileges are fine grained

Upgrading databases

From ...

1. Get the sql
2. Get, install and maintain the dedicated db client
3. Connect to the right db on the right env
4. Check that you don't have already played this sql
5. Run the sql
6. Check that all required sql have been passed in the right order

... and some nice questions

1. Did I really already run this sql ?
2. What are the pending sql on this schema ?
3. I'm not sure if everything is fine : can I re-run the sql ?... to be sure ?
4. Something's wrong on the prod db vs dev one : are you sure you ran the same sqls on both ? If not, what is the delta ?

to.

Puppetized Liquibase install

class profiles::liquibase {
  $version = hiera('liquibase::version')
  class {'liquibase':
  version => $version,
  }
}
                    

to.

Schema upgrade

export TARGET_VERSION=1.04.00
cd liquibase
# Switch to tag
git checkout tags/${TARGET_VERSION}
# reviewing pending upgrades
liquibase dbDoc ./dbDoc
# perform upgrade
liquibase update
                    

Key concepts

• the "changelog"
• the "changeset"
• databasechangelog : id, author, dateexecuted, orderexecuted, md5sum, description, comments, ...

The hash*@%! ? WHAT ?

"I can't apply my databasechangelog : liquibase is complaining that the code that has already been deployed on my db ?!!"

Yep buddy, that's normal and it's pretty cool, just add a new changeset to patch your previous one.

databasechangelog overview

         id      |  author |        dateexecuted        | orderexecuted |               md5sum               |   description  | liquibase
 ----------------+---------+----------------------------+---------------+------------------------------------+----------------+----------
 1417670162732-1 | salad74 | 2015-07-21 10:24:54.721108 |             1 | 7:1d454f92543dfc2f97501ba16669c6d7 | createSequence | 3.3.2
 1417670162732-2 | salad74 | 2015-07-21 10:24:54.739246 |             2 | 7:820672cebd4d2c7adf8de021c9304788 | createSequence | 3.3.2
 1417670162732-3 | salad74 | 2015-07-21 10:24:54.752848 |             3 | 7:28470b521a8be561e0cbde2053257007 | createSequence | 3.3.2
 1417670162732-4 | salad74 | 2015-07-21 10:24:54.764076 |             4 | 7:c86dbe6244a166da8335b66c17a1a16a | createSequence | 3.3.2
                    

Liquibase

Used features

• Code branching and merging
• Multiple developers (on git)
• Multiple database types
• Generate Database change documentation
• Generate Database "diffs" : check if ERP changes db schema

Liquibase

Used features

• Automatically generate SQL scripts for DBA code review
• Open Source: Apache 2.0 License : code donation, public roadmap
• Java APIs for executing and embedding
• Generate database Changelog
• Extensions : db2i for AS400->PgSql Migrations

Liquibase

Not (yet) used features

• Tags
• Contexts
• Context-dependent logic
• Rollback

Real life scenarios

• Standard db schema upgrades
• Database schema creation (liquibase for dbas)
• Parameter tables feeding
• Database migration (Oracle to PostgreSql) and data patching : final live migration (with datas) operated within less than 3 minutes !
• Databases migrations (DB2/400 to PostgreSql)

The "liquibase dilemma"

"Should I recreate a non "liquified" database to get the whole db changelog or should I construct liquibase on legacy created tables ?"

Liquibase Code donations

• deb and rpm installers, merged in base source code, now part of official release
• Puppet module (published on the puppet forge) (> 540 downloads within less than 2 weeks)
• Some bug fixes (with related unit tests)

Liquibase Code donations

Links and resources

• Production automation
• The DBA's Role in Devops by Datical (Enterprise Liquibase Edition and much more)
• Why you should pay employees for free-time Open Source contributions
• "NO DEPLOY FRIDAY" is a sign of maturity
• What we learned from 3 years of sciencing the crap out of DevOps
• Database as a Code
• The DBA is NOT Infallible or How I Learned to Stop Worrying and Love Automation
• Elevate yourself with side projects

Tools

Schemacrawler

Who uses

Side goals

• Giving a developer open source development experience
• Getting control on our tools
• Opportunities to learn by practice (continuous build, SaaS services, collaboration)
• Taking part in open source community
• Having fun and develop cool tools

Side goals

• Contribute to production automation
• Prototype

Schemacrawler donations/collaborations

• deb and rpm installers
• Fully crossplatform installer, Izpack based
• Bug reports
• Additional lints
• First usage in continuous integration on Travis

Why using Schemacrawler lints ?

• Database quality and design control
• Ability to define our own requirements by creating our own lints
• Reports
• Final goal : automate controls as part of continuous build.

Schemacrawler native lints

• Evaluating the quality and design of a database schema
• check table without PK
• check that FK has the same jdbc type as its referenced PK
• check table with all nullable column
• check empty table
• and many others...
• Helpfull but do not fit all our needs.

Contribute to the Schemacrawler project, and propose our own lints based on personnal experience and best practices.

SchemaCrawler Additional Lints project

Michèle Barré/Ville de Noumea

Schemacrawler additional lints

Goals

1. Improve database controls
2. Contribute to Open Source
3. Be owner of an Open Source project

Schemacrawler additional lints

• check content vs column data type
• check on not normalized content
• check column size : defined vs used
• check table/column remarks
• check PK data type
• check table/column case
• ...

Native lints & Additional lints

Collaboration

• Often in contact with Sualeh thanks to gitter, github issue tracker
• Some of our lints have been integrated to the native lints
• Bug reports
• Pull requests
• Tests
• New feature request : make lint part of continuous build

Schemacrawler and CI

The ticket that made it possible

"We are using continuous quality check and integration on an always increasing number of projects, but this essentially applies to code (Java code). This is done by Travis."

sualeh/SchemaCrawler#39

"What I begin to think about and would save a lot of time to me is : to run lint on commit event on gh, on Travis and call a lint execution that would return an error status code when two many high are triggered, or based on certain lint filtering conditions."

adriens, Schemacrawler #30

sualeh/SchemaCrawler#39

"I love the way you guys are using SchemaCrawler!" - @sualeh

ROI on time invested in open source dev.

• Vital for you to make your collaborators work on high value added and challenging tasks
• Do not make them work on things you don't want to work on because they are boring
• Why you should pay employees for free-time Open Source contributions

Manager/coworker feedback

Developer feedback

• 2 free IntelliJ IDE licences (1 licence cost : 200$)
• One more free AquadataStudio license (1 licence cost : 499$)
• Time gain on checking
• automatically done in a continuous way
• fully transparency
• Increase developer independence and responsiveness
• Deliver better quality applications
• Trusted production deployments.

Links and resources

Schemacrawler ecosystem

• Schemacrawler
• Schemacrawler Debian package builder
• Schemacrawler Multi Platform package builder
• Schemacrawler RPM package builder
• Schemacrawler Additional Lints
• Schemacrawler DB Lint Report

Links and resources

• Travis
• Coveralls
• VersionEye
• Slack
• Thymeleaf, d3js

Tools used to produce this slideshow

• Reveal.js
• Github
• Any text editor : Kate, Atom, vim
• Git
• Web browser

"Sincerity is the mother of knowledge."