database version control and deployment - model or migration scripts
TRANSCRIPT
![Page 1: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/1.jpg)
@_AlexYates_
#SQLRelay
State-Based or Migrations-Based Database Development?
A Review of the Pros and Cons…Alex Yates
![Page 3: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/3.jpg)
@_AlexYates_
#SQLRelay
@_AlexYates_ | #SQLRelay
![Page 4: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/4.jpg)
@_AlexYates_
#SQLRelay
AgileScrumLeanIterativeContinuous Delivery
Developers
![Page 5: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/5.jpg)
@_AlexYates_
#SQLRelay
MonitoringDeployment
IntegrityPerformance
DBAs
![Page 7: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/7.jpg)
@_AlexYates_
#SQLRelay
Woah! Deployment fail?
![Page 8: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/8.jpg)
@_AlexYates_
#SQLRelay
#worksOnMyMachine
Woah! Deployment fail?
![Page 9: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/9.jpg)
@_AlexYates_
#SQLRelay
A cursor?! You just CAN’T do that?!
![Page 10: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/10.jpg)
@_AlexYates_
#SQLRelay
#worksOnMyMachine
A cursor?! You just CAN’T do that?!
![Page 11: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/11.jpg)
@_AlexYates_
#SQLRelay
Hey, you just dropped my hot-fix!
![Page 12: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/12.jpg)
@_AlexYates_
#SQLRelay
#worksOnMyMachine
Hey, you just dropped my hot-fix!
![Page 13: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/13.jpg)
@_AlexYates_
#SQLRelay
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
![Page 14: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/14.jpg)
@_AlexYates_
#SQLRelay
Farm Credit Services of America (FCSA)
• Deployments easier to review
• Delivery faster and more reliable
• Standardised source control and delivery process
• Automated manual deployment tasks
![Page 15: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/15.jpg)
@_AlexYates_
#SQLRelay
The automated deployment pipeline
![Page 16: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/16.jpg)
@_AlexYates_
#SQLRelay
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)
![Page 17: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/17.jpg)
@_AlexYates_
#SQLRelay
There’s more than one way to skin a cat
![Page 18: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/18.jpg)
@_AlexYates_
#SQLRelay
There’s more than one way to skin automatea cat
![Page 19: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/19.jpg)
@_AlexYates_
#SQLRelay
V1 V2
![Page 20: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/20.jpg)
@_AlexYates_
#SQLRelay
V1 V2
Migrations-based solutions
![Page 21: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/21.jpg)
@_AlexYates_
#SQLRelay
V1 V2
State-based solutions
![Page 22: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/22.jpg)
@_AlexYates_
#SQLRelay
![Page 23: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/23.jpg)
@_AlexYates_
#SQLRelay
“There's nothing more reliable than keeping track of exactly the scripts you intend to run, and running them, without trying to compare state and guess.”
![Page 24: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/24.jpg)
@_AlexYates_
#SQLRelay
“There's nothing more reliable than keeping track of exactly the scripts you intend to run, and running them, without trying to compare state and guess.”
Paul Stovell, Octopus Deploy
http://docs.octopusdeploy.com/display/OD/SQL+Server+databases
![Page 25: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/25.jpg)
@_AlexYates_
#SQLRelay
“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.”
![Page 26: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/26.jpg)
@_AlexYates_
#SQLRelay
“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/
![Page 27: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/27.jpg)
@_AlexYates_
#SQLRelay
Migrations vs state
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
![Page 28: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/28.jpg)
@_AlexYates_
#SQLRelay
Migrations vs state
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
![Page 29: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/29.jpg)
@_AlexYates_
#SQLRelay
Migrations vs state
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
![Page 30: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/30.jpg)
@_AlexYates_
#SQLRelay
Migrations vs state
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
![Page 31: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/31.jpg)
@_AlexYates_
#SQLRelay
Migrations vs state
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
![Page 32: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/32.jpg)
@_AlexYates_
#SQLRelay
Migrations vs state
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
![Page 33: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/33.jpg)
@_AlexYates_
#SQLRelay
Migrations vs state
http://workingwithdevs.com/delivering-databases-migrations-vs-state/
![Page 34: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/34.jpg)
@_AlexYates_
#SQLRelay
11
2 Create view: kittenTrainersNrLdn
Selects only: kittenTrainers.FullName
Edit table: kittenTrainersAdd column: AcceptsTigerCubs BIT
Both work!Same changes, versioned and deployed two ways
![Page 35: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/35.jpg)
@_AlexYates_
#SQLRelay
Demo
![Page 36: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/36.jpg)
@_AlexYates_
#SQLRelay
11
2 Create view: kittenTrainersNrLdn
Selects only: kittenTrainers.FullName
Edit table: kittenTrainersAdd column: AcceptsTigerCubs BIT
Both work!Same changes, versioned and deployed two ways
![Page 37: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/37.jpg)
@_AlexYates_
#SQLRelay
11Edit table: kittenTrainersSplit column trainerFullName
into trainerFirstName and trainerLastName
But both suck.A tale of two work items…
2
Edit view: kittenTrainersNrLdnAdd column: AcceptsTigerCubs
![Page 38: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/38.jpg)
@_AlexYates_
#SQLRelay
![Page 39: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/39.jpg)
@_AlexYates_
#SQLRelay
0124_edit_view.sqlALTER VIEW kittenTrainersNrLdnAS SELECT fullName, acceptsTigerCubs FROM kittenTrainers
![Page 40: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/40.jpg)
@_AlexYates_
#SQLRelay
0124_edit_view.sql 0125_edit_table.sqlALTER VIEW kittenTrainersNrLdnAS SELECT fullName, acceptsTigerCubs FROM kittenTrainers
ALTER TABLE kittenTrainers
*FUN WITH STRINGS* END
ALTER VIEW kittenTrainersNrLdnAS SELECT firstName, lastName FROM kittenTrainers
![Page 41: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/41.jpg)
@_AlexYates_
#SQLRelay
ALTER TABLE kittenTrainers
*FUN WITH STRINGS* END
ALTER VIEW kittenTrainersNrLdnAS SELECT firstName, lastName FROM kittenTrainers
0124_edit_view.sql 0125_edit_table.sql
!
ALTER VIEW kittenTrainersNrLdnAS SELECT fullName, acceptsTigerCubs FROM kittenTrainers
![Page 42: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/42.jpg)
@_AlexYates_
#SQLRelay
Conflicts easily missedChanges overwritten, hard to spot
Order mattersLast script wins
The “winding path” problem
How do you fix a bug that has been deployed to some environments but not others?
The problem with migrations
![Page 43: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/43.jpg)
@_AlexYates_
#SQLRelay
V123kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT fullName FROM kittenTrainers
![Page 44: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/44.jpg)
@_AlexYates_
#SQLRelay
V124kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT fullName, acceptsTigerCubs FROM kittenTrainers
V123kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT fullName FROM kittenTrainers
![Page 45: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/45.jpg)
@_AlexYates_
#SQLRelay
V125kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT firstName, lastName FROM kittenTrainers
V124kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT fullName, acceptsTigerCubs FROM kittenTrainers
V123kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT fullName FROM kittenTrainers
![Page 46: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/46.jpg)
@_AlexYates_
#SQLRelay
V125kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT firstName, lastName FROM kittenTrainers
V124kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT fullName, acceptsTigerCubs FROM kittenTrainers
V123kittenTrainersNrLdn.sqlCREATE VIEW kittenTrai…AS SELECT fullName FROM kittenTrainers
!
![Page 47: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/47.jpg)
@_AlexYates_
#SQLRelay
![Page 48: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/48.jpg)
@_AlexYates_
#SQLRelay
REVISION 123 REVISION 124kittenTrainers.sqlCREATE TABLE kittenTrainers(fullName,
acceptsTigerCubs)kittenTrainersNrLdn.sqlCREATE VIEW kittenTrainersN…AS SELECT fullName
FROM kittenTrainers
kittenTrainers.sqlCREATE TABLE kittenTrainers(firstName, lastName, acceptsTigetCubs)
kittenTrainersNrLdn.sqlCREATE VIEW kittenTrainersN…AS SELECT firstName, lastName, acceptsTigerCubs FROM kittenTrainers
![Page 49: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/49.jpg)
@_AlexYates_
#SQLRelay
Diff script:
DROP COLUMN fullName
ALTER TABLE kittenTrainers ADD FirstName NVARCHAR(50), LastName NVARCHAR(50)
ALTER VIEW kittenTrainersNrLdn AS …
![Page 50: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/50.jpg)
@_AlexYates_
#SQLRelay
Diff script:
DROP COLUMN fullName
ALTER TABLE kittenTrainers ADD FirstName NVARCHAR(50), LastName NVARCHAR(50)
ALTER VIEW kittenTrainersNrLdn AS …
!
![Page 51: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/51.jpg)
@_AlexYates_
#SQLRelay
Script:
DROP COLUMN fullName
ALTER TABLE kittenTrai… ADD FirstName, LastName
ALTER VIEW kittenTrain… AS …
Need to understand your tool
It should be obvious to you that your tool won’t work
What is the Plan B / override?
Because one day you’ll need it
Test for data loss Automatically (naturally)
The problem with state
![Page 52: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/52.jpg)
@_AlexYates_
#SQLRelay
So what is better?
State MigrationsVS
![Page 53: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/53.jpg)
@_AlexYates_
#SQLRelay
State Easier (less control) Better for
sprocs/functions Better for
large/distributed teams Better for frequent
changes Better for dependency
nightmares Drift: rolled back
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
VS
![Page 54: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/54.jpg)
@_AlexYates_
#SQLRelay
State 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
![Page 55: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/55.jpg)
@_AlexYates_
#SQLRelay
Pick appropriate tooling
State
DevArt SchemaCompare
DB Maestro
Migrations
DbUp
Flyway
Hybrid
![Page 56: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/56.jpg)
@_AlexYates_
#SQLRelay
Let’s talk about:
https://msdn.microsoft.com/en-us/library/mt204009.aspx
![Page 57: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/57.jpg)
@_AlexYates_
#SQLRelay
How does SSDT work?
Most scripts do not require manual scripting
Manual scripting causes problems
![Page 58: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/58.jpg)
@_AlexYates_
#SQLRelay
How does SQL Source Control work?
Most scripts do not require manual scripting
Manual scripting causes problems
Perhaps just these ones?So only write these ones
![Page 59: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/59.jpg)
@_AlexYates_
#SQLRelay
How does SSDT work?https://msdn.microsoft.com/en-US/library/hh272704(v=vs.103).aspx
https://msdn.microsoft.com/en-us/library/jj889461(v=vs.103).aspx
<< RefactorLog
Pre/Post-Deploy scripts >>
![Page 60: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/60.jpg)
@_AlexYates_
#SQLRelay
Let’s talk about:
http://www.red-gate.com/sql-source-control/
![Page 61: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/61.jpg)
@_AlexYates_
#SQLRelay
How does SQL Source Control work?
(This diagram again)We still only need a few upgrade scripts
![Page 62: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/62.jpg)
@_AlexYates_
#SQLRelay
How does SQL Source Control work?
![Page 63: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/63.jpg)
@_AlexYates_
#SQLRelay
How does SQL Source Control work?
https://documentation.red-gate.com/display/SOC5/How+migration+scripts+work c
![Page 64: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/64.jpg)
@_AlexYates_
#SQLRelay
Let’s talk about:
http://www.red-gate.com/readyroll/
![Page 65: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/65.jpg)
@_AlexYates_
#SQLRelay
State Easier (less control) Better for
sprocs/functions Better for
large/distributed teams Better for frequent
changes Better for dependency
nightmares Drift: rolled back
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 sprocs/functions Better data migrations
How does ReadyRoll work?
![Page 66: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/66.jpg)
@_AlexYates_
#SQLRelay
How does ReadyRoll work?Programmable objects
(Views, stored procedures etc)V125myproc.sqlEXEC sp_rename 'table_foo', 'table_bar‘
Migrations(Tables, reference data etc)
![Page 67: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/67.jpg)
@_AlexYates_
#SQLRelay
How does ReadyRoll work?Programmable objects
(Views, stored procedures etc)V125myproc.sqlEXEC sp_rename 'table_foo', 'table_bar‘
Migrations(Tables, reference data etc)
![Page 68: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/68.jpg)
@_AlexYates_
#SQLRelay
How does ReadyRoll work?Programmable objects
(Views, stored procedures etc)V125myproc.sqlEXEC sp_rename 'table_foo', 'table_bar‘
Migrations(Tables, reference data etc)
https://documentation.red-gate.com/display/RR1/Programmable+Objects
![Page 69: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/69.jpg)
@_AlexYates_
#SQLRelay
But haven’t we been doing hybrid for years?
![Page 70: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/70.jpg)
@_AlexYates_
#SQLRelay
ProdTestDev
![Page 71: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/71.jpg)
@_AlexYates_
#SQLRelay
ProdTestDev
![Page 72: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/72.jpg)
@_AlexYates_
#SQLRelay
ProdTestDev S0
![Page 73: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/73.jpg)
@_AlexYates_
#SQLRelay
ProdTestDev S0
S0
![Page 74: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/74.jpg)
@_AlexYates_
#SQLRelay
ProdTestDev S0
S0
State Migrations
![Page 75: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/75.jpg)
@_AlexYates_
#SQLRelay
ProdTest
DevS0
“Late migrations”
S1
ProdTest
Dev S0
“Early migrations”
![Page 76: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/76.jpg)
@_AlexYates_
#SQLRelay
Early vs Late
• Test deploy script early
• Easier to automate
• Developers own deployments (typically)
• Optimised deployment scripts (no winding path)
• Easier to intervene
• DBAs own deployments (typically)
![Page 77: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/77.jpg)
@_AlexYates_
#SQLRelay
Drift
![Page 78: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/78.jpg)
@_AlexYates_
#SQLRelay
![Page 79: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/79.jpg)
@_AlexYates_
#SQLRelay
“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.”
![Page 80: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/80.jpg)
@_AlexYates_
#SQLRelay
“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
![Page 81: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/81.jpg)
@_AlexYates_
#SQLRelay
The bad stuff:
• Accidental roll-backs (state)
• Failed deployments (migrations)
• Environment inconsistency (migrations)
![Page 82: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/82.jpg)
@_AlexYates_
#SQLRelay
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/
![Page 83: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/83.jpg)
@_AlexYates_
#SQLRelay
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/
![Page 84: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/84.jpg)
@_AlexYates_
#SQLRelay
Demo
![Page 85: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/85.jpg)
@_AlexYates_
#SQLRelay
Farm Credit Services of America (FCSA)
• Standardised DLM processes• FCSA delivering much more efficiently
• The FCSA model being rolled out at FCMA (sister org)
• The team all have excellent CV’s
![Page 86: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/86.jpg)
@_AlexYates_
#SQLRelay
![Page 87: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/87.jpg)
@_AlexYates_
#SQLRelay
Siloed sparrows suck at DLM
![Page 88: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/88.jpg)
@_AlexYates_
#SQLRelay
Heated hippos are closed minded
![Page 89: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/89.jpg)
@_AlexYates_
#SQLRelay
State 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
![Page 90: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/90.jpg)
@_AlexYates_
#SQLRelay
State
DevArt SchemaCompare
DB Maestro
Migrations
DbUp
Flyway
Hybrid
Hybrid tools are awesomeTools are pretty good
![Page 91: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/91.jpg)
@_AlexYates_
#SQLRelay
ProdTest
DevS0
“Late migrations”
S1
ProdTest
Dev S0
“Early migrations”
It’s a balance, not a choice
![Page 92: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/92.jpg)
@_AlexYates_
#SQLRelay
Hunt his smug face - make pain on it
![Page 93: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/93.jpg)
@_AlexYates_
#SQLRelay
This stuff makes a big difference
http://www.codeaperture.io/2016/09/13/how-redgate-helped-define-our-process/
![Page 94: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/94.jpg)
@_AlexYates_
#SQLRelay
Questions?
![Page 96: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/96.jpg)
![Page 97: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/97.jpg)
@_AlexYates_
#SQLRelay
PREMIER SPONSOR
GOLD SPONSORS
SILVER SPONSORS
BRONZE SPONSORS
SUPPORTERS
![Page 98: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/98.jpg)
@_AlexYates_
#SQLRelay
Please give us your feedback:
sqlrelay.co.uk/feedbackThank you
![Page 99: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/99.jpg)
![Page 100: Database version control and deployment - model or migration scripts](https://reader036.vdocuments.us/reader036/viewer/2022062522/588710521a28abf2228b5fdf/html5/thumbnails/100.jpg)
@_AlexYates_
#SQLRelay
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