Getting Release Management Right for SQL Server
-
Upload
alex-yates -
Category
Technology
-
view
65 -
download
0
Transcript of Getting Release Management Right for SQL Server
@_AlexYates_#SqlSatIceland
Getting Release Management right for SQL Server Alex Yates
@_AlexYates_#SqlSatIceland
Sérstakar þakkir til styrktaraðila
SQL SATURDAY | #602 | REYKJAVIK 2017
@_AlexYates_#SqlSatIceland
DLM Consultant
workingwithdevs.com
Alex Yates
@_AlexYates_
@_AlexYates_#SqlSatIceland
@_AlexYates_ | #SqlSatIceland
@_AlexYates_#SqlSatIceland
AgileScrumLeanDevOpsIterativeContinuous Delivery
Developers
@_AlexYates_#SqlSatIceland
MonitoringDeployment
IntegrityPerformance
DBAs
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
Farm Credit Services of America (FCSA)
• 100 person IT team, 14 sub-teams
• Database version control inconsistent
• Deployment process manual
• Delivery was slow and unreliable
@_AlexYates_#SqlSatIceland
Farm Credit Services of America (FCSA)
• Growing test suite
• Delivery faster and more reliable by catching issues early
• Standardised source control process
• Automated builds/deploys from source control
@_AlexYates_#SqlSatIceland
The deployment pipeline
@_AlexYates_#SqlSatIceland
Databases are hard
• Schema changes vs existing data• Reference data vs production data• Teamwork and testing• Database drift (change outside
process, e.g. production hot-fixes)
@_AlexYates_#SqlSatIceland
The deployment pipeline
@_AlexYates_#SqlSatIceland
The deployment pipeline
@_AlexYates_#SqlSatIceland
The deployment pipeline
@_AlexYates_#SqlSatIceland
The deployment pipeline
@_AlexYates_#SqlSatIceland
The deployment pipeline
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
Version Control
@_AlexYates_#SqlSatIceland
Version control - LMGTFY
http://lmgtfy.com/?q=version+control
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
There’s more than one way to skin a cat
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
There’s more than one way to skin automatea cat
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
V1 V2
@_AlexYates_#SqlSatIceland
V1 V2
Migrations-based solutions
@_AlexYates_#SqlSatIceland
V1 V2
Model-based solutions
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
“There's nothing more reliable than keeping track of exactly the scripts you intend to run, and running them, without trying to compare model and guess.”
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
“There's nothing more reliable than keeping track of exactly the scripts you intend to run, and running them, without trying to compare model and guess.”
Paul Stovell, built Octopus Deploy
http://docs.octopusdeploy.com/display/OD/SQL+Server+databases
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
“As soon as you have multiple changes on a single aspect of an object, ordering and the ability to detect which change needs to be made gets very complicated.”
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
“As soon as you have multiple changes on a single aspect of an object, ordering and the ability to detect which change needs to be made gets very complicated.”
Gert Drapers, built DataDude
https://blogs.msdn.microsoft.com/gertd/2009/06/05/declarative-database-development/
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
Model vs migrations
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
@_AlexYates_#SqlSatIceland
Model vs migrations
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
@_AlexYates_#SqlSatIceland
Model vs migrations
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
@_AlexYates_#SqlSatIceland
Model vs migrations
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
@_AlexYates_#SqlSatIceland
Model vs migrations
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
@_AlexYates_#SqlSatIceland
Model vs migrations
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
@_AlexYates_#SqlSatIceland
Model vs migrations
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
@_AlexYates_#SqlSatIceland
Model Easier (less control) Better for
sprocs/functions Better for
large/distributed teams Better for frequent
changes Better for dependency
nightmares Drift: rolled back Better for development
Migrations More control
(harder/needs discipline) Better for data
migrations Better for small teams Better for infrequent
changes Better for simple data
stores Drift: ignored Better for automation
VS
@_AlexYates_#SqlSatIceland
DLMConsultants.com/model-vs-mig
@_AlexYates_#SqlSatIceland
Demo
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
Continuous Integration
@_AlexYates_#SqlSatIceland
What is CI?
@_AlexYates_#SqlSatIceland
What is CI?
Continuous Integration (CI) is a development practice that requires developers to integrate code into a shared repository several times a day. Each check-in is then verified by an automated build, allowing teams to detect problems early.
www.thoughtworks.com/continuous-integration
@_AlexYates_#SqlSatIceland
http://www.jamesshore.com/Blog/Continuous-Integration-on-a-Dollar-a-Day.html
@_AlexYates_#SqlSatIceland
Build servers
@_AlexYates_#SqlSatIceland
VCS Build Test Sync
Upgrade scriptDatabase package
Test results
The automated build process
@_AlexYates_#SqlSatIceland
Builds/Tests are run on CI agents
Builds/Tests are managed on
CI server
CI server
How do build servers work?
@_AlexYates_#SqlSatIceland
DLMConsultants.com/ci
@_AlexYates_#SqlSatIceland
Demo
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
What is CD?
Continuous Delivery (CD) is the natural extension of Continuous Integration: an approach in which teams ensure that every change to the system is releasable, and that we can release any version at the push of a button. Continuous Delivery aims to make releases boring, so we can deliver frequently and get fast feedback on what users care about.
www.thoughtworks.com/continuous-delivery
@_AlexYates_#SqlSatIceland
Deploying databases, a history lesson
SOURCE TARGET
Process:Manual scripting
@_AlexYates_#SqlSatIceland
Deploying databases, a history lessonProcess:Database comparison tool
SOURCE TARGET
@_AlexYates_#SqlSatIceland
Deploying databases, a history lesson
SOURCE TARGET
Process:Generating the script is separate from manually executing the script
@_AlexYates_#SqlSatIceland
Deploying databases, a history lesson
SOURCE
Problem:Human error
Doh!
HumanError
@_AlexYates_#SqlSatIceland
Deploying databases, a history lesson
SOURCE
Problem:Drift (unscheduled or unmanaged changes)
DriftDrift
@_AlexYates_#SqlSatIceland
What do you really need to deploy with confidence?
Replacing a single deployment script with a credible release
@_AlexYates_#SqlSatIceland
What do you really need to deploy with confidence?
1. Source model
Replacing a single deployment script with a credible release
@_AlexYates_#SqlSatIceland
What do you really need to deploy with confidence?
1. Source model
2. Target model
Replacing a single deployment script with a credible release
@_AlexYates_#SqlSatIceland
What do you really need to deploy with confidence?
1. Source model
2. Target model
3. Upgrade script
Replacing a single deployment script with a credible release
@_AlexYates_#SqlSatIceland
What do you really need to deploy with confidence?
1. Source model
2. Target model
3. Upgrade script
4. Change report
SELECT ContactsID,ContactFullName,Address1,CountryCode,
Replacing a single deployment script with a credible release
@_AlexYates_#SqlSatIceland
How should deployments work?
1. Does target match target model?2. Run script3. Does target match source model?
TARGET
@_AlexYates_#SqlSatIceland
How should deployments work?
1. Does target match target model?2. Run script3. Does target match source model?
TARGET
@_AlexYates_#SqlSatIceland
How should deployments work?
1. Does target match target model?2. Run script3. Does target match source model?
TARGET
@_AlexYates_#SqlSatIceland
How should deployments work?
1. Does target match target model?2. Run script3. Does target match source model?
TARGET
@_AlexYates_#SqlSatIceland
How should deployments work?
1. Does target match target model?2. Run script3. Does target match source model?
TARGET
@_AlexYates_#SqlSatIceland
What should happen if anything goes wrong?
1. If target doesn’t match target model (or source model) – error and abort
2. If script fails – roll back transaction
3. If target doesn’t match source model after script execution – do not roll back but send warning, then roll-forward
@_AlexYates_#SqlSatIceland
Demo
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
Release Management
@_AlexYates_#SqlSatIceland
Release Management Tools
@_AlexYates_#SqlSatIceland
How do release management tools work?
@_AlexYates_#SqlSatIceland
Deployments are run on RM agents
Deployments are managed on
RM server
RM server
How do release management tools work?
Builds/Tests are run on CI agents
Builds/Testsare managed on
CI server
CI server
Just like a CI server!
@_AlexYates_#SqlSatIceland
RM server
Test
Staging
Production
AppDB
AppDB
AppDB
Servers, environments, machines and agents
@_AlexYates_#SqlSatIceland
RM server
Server RM software responsible for managing your deployments
AppDB
AppDB
AppDB
Servers, environments, machines and agentsTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
App DB
App DB
App
Machines physical or virtual boxes
that you wish to deploy to
AppDB
AppDB
AppDB
Servers, environments, machines and agentsTest
Staging
Production
@_AlexYates_#SqlSatIceland
Environments groups of machines that you will deploy to at the
same time
RM server
App DB
App DB
App DB
AppDB
AppDB
AppDB
Servers, environments, machines and agentsTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
Agentsrelease management software that executes deployments on
machines
AppDB
AppDB
AppDB
Servers, environments, machines and agentsTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
AppDB
AppDB
How database deployments should workTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
AppDB
AppDB
DB server doesn’t actually need an agent
How database deployments should workTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
AppDB
AppDB
Install DB deployment tool and an RM agent on a machine
(wherever you like)
How database deployments should workTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
AppDB
AppDB
RM server sends instruction to RM agent
How database deployments should workTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
AppDB
AppDB
RM agent calls DB deployment tool on its
machine
How database deployments should workTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
AppDB
AppDB
DB deployment tool talks to target databases
How database deployments should workTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
AppDB
AppDB
Some people install an agent and DB deployment tool on the
RM server for simplicity
How database deployments should workTest
Staging
Production
@_AlexYates_#SqlSatIceland
RM server
AppDB
If environments are isolated by firewalls you may need several
DB deployment tool installations
Production
DB
DB
Test
Staging
App
App
How database deployments should work
@_AlexYates_#SqlSatIceland
In summary• Install the release management server on a test server or
build server etc• Install agents that will deploy applications on the application
servers themselves• Install agents that will deploy databases on a machine that
has access to your target databases• Install DB deployment tool on the same machine as the
agent that you will use to deploy your databases
@_AlexYates_#SqlSatIceland
Demo
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
Drift
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
“When making changes directly on production, you are making a decision that the delay due to poor cycle time is more expensive than the risk of making a mistake.”
@_AlexYates_#SqlSatIceland
“When making changes directly on production, you are making a decision that the delay due to poor cycle time is more expensive than the risk of making a mistake.”
Alex Yates, built this slide
@_AlexYates_#SqlSatIceland
The bad stuff:
• Accidental roll-backs (model)
• Failed deployments (migrations)
• Environment inconsistency (migrations)
@_AlexYates_#SqlSatIceland
DriftStrategies to help
Improve cycle time Strict security policies Monitor drift (play with DDL
triggers) Redgate DLM Dashboard
(free)https://www.simple-talk.com/sql/database-administration/database-deployment-the-bits-database-version-drift/
@_AlexYates_#SqlSatIceland
DriftStrategies to help
Improve cycle time Strict security policies Monitor drift (play with DDL
triggers) Redgate DLM Dashboard
(free)http://www.red-gate.com/products/dlm/dlm-dashboard/
@_AlexYates_#SqlSatIceland
Demo
@_AlexYates_#SqlSatIceland
AgendaVersion control
Model vs migrationsContinuous integration
Automating deploymentsRelease management
Drift
@_AlexYates_#SqlSatIceland
• Standardised VCS, CI and RM processes• FCSA delivering much more efficiently
• FCSA process model for sister organisations
• The team all have excellent CV’s
Farm Credit Services of America (FCSA)
@_AlexYates_#SqlSatIceland
• Standardised VCS, CI and RM processes• FCSA delivering much more efficiently
• FCSA process model for sister organisations
• The team all have excellent CV’s
Farm Credit Services of America (FCSA)
http://www.codeaperture.io/2016/09/13/how-redgate-helped-define-our-process/
@_AlexYates_#SqlSatIceland
Siloed sparrows suck at DLM.
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
Heated hippos are closed minded.
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
Model Easier (less control) Better for
sprocs/functions Better for
large/distributed teams Better for frequent
changes Better for dependency
nightmares Drift: rolled back Better for development
Migrations More control
(harder/needs discipline) Better for data
migrations Better for small teams Better for infrequent
changes Better for simple data
stores Drift: ignored Better for automation
VS
Clever people consider options …
@_AlexYates_#SqlSatIceland
… use appropriate tools …
@_AlexYates_#SqlSatIceland
… and make pain on his smug face!
@_AlexYates_#SqlSatIceland
This stuff makes a big difference.
http://www.codeaperture.io/2016/09/13/how-redgate-helped-define-our-process/
@_AlexYates_#SqlSatIceland
@_AlexYates_#SqlSatIceland
DLMConsultants.com/rm
@_AlexYates_#SqlSatIceland
Takk fyrir að koma á SQL Saturday IcelandSérstakar þakkir til styrktaraðila
SQL SATURDAY | #602 | REYKJAVIK 2017
http://www.sqlsaturday.com/602/Sessions/SessionEvaluation.aspx
@_AlexYates_#SqlSatIceland
Image sourcesAuthor Source Information
Chiltepinster Wikimedia Commons Mocking Bird Argument.jpg – Wikimedia Commons. This file is licensed under the Creative Commons Attribution-Share Alike 3.0 Unported license. Source on Wikimedia Commons: “Own work”
Bit Boy Flickr The elephant in the room – Flickr. This file is licensed under the Creative Commons Attribution 2.0 Generic license.
Nils Rinaldi Flickr Hippo fight 2/3 – Flickr. This file is licensed under the Creative Commons Attribution 2.0 Generic license.
My own collection Taken by/property of Alex Yates Kitten, “There’s more than one way to skin a cat!”
Memegenerator.net Memegenerator.net I don’t always edit database. Content designed to be shared and delivered with credit to memegenerator.net.
Ctrl.Alt.Design ctrla.lt Social Media share icons