the comeback of migrations-based deployments - dan nolan & elizabeth ayer

The Comeback of

Migrations-based deployments

Dan Nolan & Elizabeth Ayer

Redgate

#SQLintheCityUK

Goals

• Examine migration’s poor cousin: the manual change script

• What is ReadyRoll? How does it fit in at Redgate?

• Address two key myths of migrations-based development

#SQLintheCityUK

#SQLintheCityUK

What is a migration?

#SQLintheCityUK

The manual change script:Migration’s poor cousin

JimDatabase

Developer

BobDBA

ChrisDevelopment

Manager

ElaineApplicationDeveloper

Jim

Change to Account table – ticket #42743

Hi Jim,

Could you add a new column to the Account table? See ticket #42743 for

the details of the change.

Let me know if you have any questions.

Regards

Elaine

ElaineApplicationDeveloper

20150912_Add_Column_42743.sql

JimDatabaseDeveloper

Elaine

Re: Change to Account table – ticket #42743

Hi Elaine,

I’ve attached the change script to accompany the code changes for ticket

#42743. It needs to checked in as part of the same commit.

Thanks!

Jim

Jim

Change to Account table – ticket #42743

Hi Jim,

Thanks. I’ll add the script to our next release.

Regards

Elaine ElaineApplicationDeveloper

Elaine; Jim; Bob

URGENT: Application not accepting new users

QA just told me that the application won’t let them add new users to the

system. We’re waiting on this release to go out the door ASAP. Can we

get to the bottom of this?

Chris

Development Manager

ChrisDevelopment

Manager

Chris; Jim; Bob

Re: URGENT: Application not accepting new users

Hi all,

Looks like the database change script didn’t get promoted alongside the

application change.

Bob: Could you run the attached script?

Elaine ElaineApplicationDeveloper

20150912_Add_Column_42743.sql

Chris; Jim; Bob

Re: URGENT: Application not accepting new users

Hi Elaine, Jim,

Looking through the script it all looks good except I don’t understand why

we’re dropping and adding an index as part of this change? Jim, could

you explain?

Cheers

BobBobDBA

JimDatabaseDeveloper

Bob; Elaine; Chris

Re: Change to Account table – ticket #42743

Hi Bob,

We need to do this for complicated technical reasons. I’ll come speak to

you in person…

Thanks

Jim

Chris; Jim; Bob

Re: URGENT: Application not accepting new users

Hi all,

Changed deployed. Ready for testing…

Cheers

Bob BobDBA

Elaine; Jim; Bob

URGENT: Application not accepting new users

Guys…!

Can we find some way of making this process less likely to screw up?!?

Regards

Chris

Development Manager ChrisDevelopment

Manager

#SQLintheCityUK

What is ReadyRoll?

A Visual Studio extension which makes it easy to develop and deploy SQL Server databases

Top Myths

Collaboration is difficult

“Migrations don’t work in agile teams.”

Releases are error-prone

“There’s always issues with scripts when we deploy to Prod.”

Team Collaboration Demo#SQLintheCityUK

SSMS Visual Studio

BobDBA

JimDeveloper

Visual Studio

Source Control

(Git)

ReadyRoll enables state-like deployment for stateless objects, and uses migrations for everything else.

Deployment Demo#SQLintheCityUK

Octopus Deploy

BobDBA

Source Control (Git) TeamCity

Through its build-once, deploy many timesapproach, ReadyRoll allows teams to achieve

consistent and predictable database releases.

Want to try the tools you’ve just seen?Head to the Hands-on Labs

#SQLintheCityUK

Get in touch

ready-roll.com/blog

Elizabeth.Ayer@red-gate.com

Daniel.Nolan@red-gate.com

@ElizabethAyer

@dnlnln

Elizabeth Ayer & Dan Nolan

#SQLintheCityUK