@rob_rich

Get Your Database Under Control

by Rob Richardson

@rob_rich

http://robrich.org/

July 21, 2014

About Me

Rob Richardson is a software craftsman building web properties in ASP.NET and Node. He's a frequent speaker at conferences, user groups, and community events, and a diligent teacher and student of high quality software development. You can find this and other talks on http://robrich.org/presentations and follow him on twitter at @rob_rich.

Continuous Integration is
part of a larger vision

"The goal is
to get from backlog
to customer value faster"
-me ... right now

Larger vision


Continuous Deployment
Team
Interaction		 Continuous
Integration		 Deployment

Goals

Team
Collaboration		 Continuous
Integration		 Deployment
  • Sandbox for speed
  • Collaborate when I'm ready
  • Consistency
  • Validate parts work together
  • Protecting customer assets

Tools

Team
Collaboration		 Continuous
Integration		 Deployment
  • Jira
  • Trello
  • Axosoft
  • MS Project
  • Visual Studio Data Tools
  • Red Gate
  • Entity Framework Migrations
  • NUnit
  • Selenium

On Premise:

  • Octopus Deploy
  • Red Gate

Cloud PaaS:

  • Azure Websites
  • Amazon Elastic Beanstalk
  • AppHarbor

Tools

Team
Collaboration		 Continuous
Integration		 Deployment

T

F

S
 

How do we get there?

Phases of Automation

  • Source Control
  • "The Build"
  • Automate all the things:
    gain confidence
  • Automate deploying
    and monitoring

 

source: building an automated deployment pipeline, slides 17-21

Source Control

Goal: Store all the things

  • Application source code
  • Database details
  • Configuration data
  • Test tools
  • Deployment scripts

"The Build"

Goal: A neutral environment

  • Command-line compile
  • Run a test
  • Collect results
  • exit code:
    • 0 = success
    • everything else = fail

Automate all the Things

Goal: Gain confidence in development efforts

  • Run unit tests
  • Run integration tests
  • Deploy to a test server
  • What do we do a lot?
  • What is hard?

Automate Deployment

Goal: A single button push sends it to production

  • Application content
  • Secure configuration store
  • Production machine list

... but databases are hard.

 
 

Database development
can learn a lot from
Application development ...

Phases of
Database Automation

  • Source Control
  • "The Build"
  • Automate all the things:
    gain confidence
  • Automate deploying
    and monitoring

 

source: building an automated deployment pipeline, slides 17-21

Databases are not Different

The only difference between application development
and database development is we need to
care for the data during state changes

Databases are not Different

  • Apps can blow it all away and start over
  • Databases need to preserve the data
  • Databases need to safely transition data between states

Step 1: Version Control

The Scripts

  • Create scripts:
    Store sql to create each object, update it over time
  • Transition scripts:
    Store sql to transition from old state to new state

The scripts

Type Pros Cons
Create scripts
  • Audit: VCS's blame
  • Generate easily
  • Hard to transition existing database
Transition scripts
  • Easy to run
  • Diligently save development efforts
  • Deployment may not take a direct route

The scripts

Type Typical Use-case
Create scripts
  • When team collaboration is key
  • Use tools to infer and run transitions
Transition scripts
  • When budget is key
  • Developer precision is critical
 

DEMO:
Visual Studio Data Tools

 

DEMO:
SQL Source Control

Step 2: The Build

The Build

  • Dovetail with the application build
  • Validate sql scripts
  • Run stored procedures

The Build Resources

Step 3: Automate all the Things

Automate Database Deployments

  • Migration scripts
  • Restore production backup
  • tSQLt

Tools

Team
Collaboration		 Continuous
Integration		 Deployment
  • Jira
  • Trello
  • Axosoft
  • MS Project
  • Visual Studio Data Tools
  • Red Gate
  • Entity Framework Migrations
  • NUnit
  • Selenium

On Premise:

  • Octopus Deploy
  • Red Gate

Cloud PaaS:

  • Azure Websites
  • Amazon Elastic Beanstalk
  • AppHarbor

SQL Server Source Control Basics

Download eBook | Buy on Amazon

For efficient team-based database development, and reliable and repeatable database deployments, going without source control isn't really optional any more. Quite apart from making it easier to work together, roll back, and share changes, it's the cornerstone of better change management and continuous integration.

But how do you get started? Database development traditionally hasn't used source control at all, and there's a bit of ground to cover before jumping into version control.

This book provides just the right combination of theory and practical example to get you started quickly.

Resources

Questions?

@rob_rich

http://robrich.org/