Database DevOps with Redgate ReadyRoll

hi welcome Visual Studio toolbox I'm
your host Robert Greene and joining me
today is Steve Jones from red gate hi
Steve is here to talk about database
DevOps that's right and we we chatted it
build I we did a show it build and we
covered some of this stuff then but
we're gonna do it in a bit of a more
deep dive and get a little bit more into
the meat behind it okay ah right yeah
absolutely
so we're gonna talk about two things
we're gonna talk about two different
ways of doing the database DevOps first
that we're going to cover today is
migrations and then the second one is
database compare right data compare have
you ever done migrations when you've
developed yes yep do you like migrations
do I like migrations it's a to doing
stuff gets done so I like I like stuff
that works but I don't have a passion
one way or another for it let's go that
way yeah I find a lot of people don't
either do it don't like migrations I
I've done a lot of migrations more in
the Oracle and db2 world where you know
large ERP systems often just track all
the scripts there hundreds of developers
right and then we run them all in order
and it always works but it can be really
slow sometimes okay so some people don't
like migrations but I prefer it you know
and I work for regular software we we
support both ways of doing database
development well so whatever works for
you that's fine and maybe you'll pick
one or the other after this but at least
I'll learn how they both work right the
exact wording it said at the end of
these two parts all right all right
migrations first so migrations first
let's back up a step okay we dive in
what are we doing so I'm a database
person I've been a sequel server a
person for most of my career I've
actually been working sequel server for
26 years now so more than half my life
has been spent working with databases
right so it's kind of amazing that
throughout that time I've developed
software in a number of different ways
as a DBA and developer and so I've tried
to work through these different ways of
efficiently getting database changes to
action making sure that I'm not
interrupting an application I'm not
causing downtime unnecessarily
certainly I'm not causing any data
integrity issues or losing anything
along the way right so you know you make
code changes all the time but you're
saying that how often do people make
database changes not as much as code
changes obviously not as much as code
changes right the database because we
have to maintain that state of our data
mmm like unlike an application right I
can drop a class I can add a method I
can make these changes and I kind of
just replace different versions of right
maxi's dll's assemblies resources
whatever it is I can do that with some
of my code in the database world my
views my procedures things like that but
my tables I have to keep around so I'm
always kind of evolving them okay and
because I have to do that especially as
my database grows larger I want to be
careful how often I make those changes
okay renaming a table is disruptive
because unlike renaming something in
Visual Studio the compiler can find all
the references or the IDE can find all
the references and do that for database
I am xions coming from various
places and I don't know what's happening
right so I think it depends on a lot of
times the maturity of your application
if you're starting out you may make more
database changes than later yeah right
okay as I grow I get a lot of technical
debt kind of in my database world
because of all those connections to
other applications reports ETL systems
all right so I'm hesitant to make this
changes okay now there are people that
do a lot of additive changes where they
just add things to the tables they add
systems to the objects to the database
so that they don't hopefully don't
disrupt those applications all right and
they can continue to add features and
functionality throughout the lifetime of
their application ok so one of the
things that you know I've been doing
really for most of my life is a lot of
things we call DevOps when I trying to
have lightweight processes that work
that helped me make changes in an
efficient manner without causing
problems and at last 3 or 4 years all of
a sudden we have a name we call it
Devil's we DevOps all the things now I
think that's Donovan Brown might say
yeah so so maybe I show you some things
here I work for red gate software and we
have ready role as included in Visual
Studio Enterprise yeah so that's part of
what's there and I have a little ready
role toolbar here that comes up and
that's a pain in Visual Studio and my
project in ready role is just like any
other project so if I were to create a
new project here and since we don't want
to run through everything ready role
appears just like my database project
would for SSD T or for a c-sharp or any
other type of application we have in
Visual Studio in this case the ready
role project builds off of a database
project which is slow to come up but you
can see that there's database projects
like we would do and for SSD teeth for
Daniel's or I've got ready role projects
here and the difference is the database
project works as tracking all the
changes and then it does a comparison
with sequel package and you'll deploy
those changes mm-hmm I kind of package
everything I'm a backpack and ready roll
we have what we call migrations
so in this existing projects I've made a
bunch of changes over time you can see
I've got all these scripts and each one
of these is just a change that I made to
my database as I was performing
development so in this case I'm altering
the table obviously I can't you wrote
the scripts and then saved them in the
project right yep I actually have a
couple ways of doing it I can import
those changes if I've made this in
management studio or another developer
made changes so I can
ensure that I'm capturing all the
changes or I could write the script
directly okay so ready roll what it does
is it takes the burden off of you of
trying to manually grab every script
make sure you've caught all the changes
giving it a name saving it off right
okay so I can do that so over time I
just collect these different changes
that could be DDL changes they could be
DML changes there's just a series of
things that I'm doing to my database I'm
creating objects I'm altering objects
whatever is appropriate for my
application so in this case this is a
common pattern that in the SSD T world
or in a state-based comparison world is
difficult to handle I've got to add a
notnot column I'm gonna I've got data in
my table right so I can't add it not no
column right I have to add a no column
update some data and then add a not null
right
so that's a complex change they still
haven't changed that that way forever
right yeah I mean there are ways around
there I could add a default I could do
some things but for a lot of systems
I've worked with a lot of developers
this is a problematic item because I
don't want to default value what I need
to do is populate that appropriately
with some value that matters for that
room okay so ready roll makes these
types of changes much more robust and
resilient so where's the migration ID
come from there so let me show you how
this is done will create a migration
script now so if I want to create a new
script I could certainly just right
click and add like anything else mm-hmm
I could certainly add a stored procedure
or a table or anything that I want to do
so when I do my development is the
migration ID that gets created when you
create the script that gets created a
great degree okay so it gives me a
unique way of identifying this script
and tracking whether it's been deployed
in the environment okay no so we do that
so let me create a little procedure here
that I want to do and I'll just give me
something that I can run okay and I can
certainly run this if I want I have a
variety of options like I would in any
other database development that I'm
doing that I can just mark my code run
it test it to everything that need to do
here yep this has been executed on a
local instance it actually exists there
so I can run any my application against
it I have all the ability to do the work
that I do normally as a database
developer mm-hm and then you know
periodically what I want to do is I want
to make sure that I've deployed
everything in my project so I can click
here and it'll do a build in Visual
Studio like anything else so I know that
popped quickly up there but I actually
have conflict and something failed
already an object name get 10 yeah yeah
okay cool so it thinks it's still there
I don't think that's the case let's just
check I'm more comfortable you gonna
believe Who am I gonna believe it does
exist
I think cuz I ran it and then I said it
was deployed let's deploy it again into
a build because I'm a developer I'm
human I make mistakes right things
happen as I'm going let's see it
deployed it succeeded everything
at all so I can kind of see what's going
on in my project as I'm performing
development because I have the code in
scripts I could certainly check the
database against this
I've got everything like I would
normally have this migration ID is is
how the framework will actually check if
this script has been run okay so inside
of my database we actually track that so
if I pop over here and let's just check
it from here there's migration log and
we can see here that this contains all
of the migrations that have been run so
there's this good that we have there's a
checksum make sure that the script I
think Iran is the correct script right I
haven't edited or changed it and I have
the file name and then various other
metadata that lets me know what's going
on so this ensures that my scripts in
essence will only run once so I can't do
something like add data in a script and
then worry about it being added again
right duplicate data recreating tables
that kind of thing so so far as I go
through database development it's
similar to what I might do in c-sharp or
any other language right right that I'm
just kind of working with my project and
make your changes and like any other
change this kind of appears as part of
my team Explorer right I've hooked up to
version control which is one of those
things that I find so many database
developers don't do right they don't
track their DDF right you've deployed
this to the database already my
development database all right so I'm
working with an in development database
and I've got this change out there
mm-hmm
okay and I and I can certainly make
other changes and let me show you
something else cuz do you find there's a
lot of developers that like to work in
managed studio still because it's a
comfortable way of working and it's
quick so let me add a piece of data here
to this table and if I look at what's in
here I've got some data in there so I
just add in row number five so one of
the things ready roll also allows to do
is track reference data or static data
that I might want to include as I deploy
this further mm-hmm so here's here's a
value that I want to include well once
I've done that and ready roll what I
want appear like to do is check if I as
anybody made any changes
Visual Studio certainly I could have
just done run a query on Visual Studio
as well that did that same insert we can
see here that I've actually detected
there's a data change here and in fact
this one row that was inserted so I can
add this as another script and this will
come in as another migration script
this is script 32 it's got a different
migration ID which you'll have to trust
me that's the case we won't compare the
height but Randy Rahal has built me an
insert statement it could build me an
update statement it could build me a
delete statement whatever would be
appropriate here and again that's here
inside of the change team explorers
whoo-hoo so all of this is my local
development as the database developer my
laptop my workstation I'm doing my thing
right so are you committing this to
version control as a way of keeping
track of the history because these you
changed the database right it's a little
bit different than writing code it's a
little bit isn't it it's a little
different right because certainly
anything I do to a table or to data has
to is kind of this evolutionary change
of thing that I'm moving from one state
to the next constantly I'm tracking it
in version control a because I make
mistakes sometimes I have to go
backwards okay just the way the world
sometimes I will misinterpret
requirement the business person will
tell me the wrong requirement and we
will write code and realize it doesn't
work we can roll back the database
changes well we can certainly find out
what we changed I can go back I can
certainly go to version control and grab
those changes
rolling back database changes is not
always easy yeah cause it's not like the
data's in source control the history the
data it's not go back to a snapshot
right now for stored procedures and
functions of use I can just grab that
previous cousin right cuz those are just
code those are just yes okay for tables
I haven't come across a tool yet Norway
I think would I trust my job to a tool
to roll back my date right change
exactly right because if that if there's
been data added to the table I need to
decide what to do with that day
right if I've dropped a column which is
a very dangerous thing I need to have
prepared for that advance because
nothing is rolling bringing me that
table change back right right okay so
those are different but as much as
possible I'm trying to treat all my
database code like application code mhm
okay and part of this is that I want to
use this code to move forward to do CI
and do CDJ like I would do with an
application okay so let me commit this
you'll see this out there actually
before I commit this let's just go look
I've got a project out here in visual
studio team services which i think is
fantastic by the way
mm-hm I think you're right it is this is
maybe the best developer tool I've seen
Microsoft ever build
now the visual studio team service is
just amazing really is and I use it all
the time so I've got you know a board
like I would have for anything else and
I can track all my database work yeah
and I've got my codes here as well and
I've got my project so I've got my main
database project my test project because
I want to test things you know in
various items and if I flipped down in
here and look at my migrations and my
last change was this morning right a
little while ago we were checking things
out let's go ahead and commit this
change so we'll say channel 9 Robert
I'll call it one let's commit and push
that so once I do that this will
actually push up into the Microsoft the
first time I looked closely at that
drop-down and saw that commit and push
you could do all the ones instead of
committing and then going and finding
push this that might be like the one
thing I wish existed instead of a
drop-down it would just give me the
option to remember that would that be
even better right yeah but if I let's
refresh this so that I see what's out
there
I should see we just pushed out two
changes okay so it tracked to new
scripts yep and now I have a history of
what's going on because in a migrations
world I'm tracking all these scripts
over time so that's an interesting best
practice you've got two changes in one
comment so given that you're all the
rest of your comments are pretty
descript
give would you then typically just
commit one script at a time it depends
on the work that I'm doing at that you
know III that's kind of a devil of a
developer philosophical question right
do I include multiple changes into one
commit mm-hmm I would have that option
just like I do with anything in Visual
Studio I could pair and choose what
commits in this case if I was if that
data change was associated with the
stored procedure change I would probably
commit them together with the work item
in a better description of Christ right
not a great comment but if they were
separate items as is the case sometimes
in database world mm-hmm I would make
two separate commits okay and and that's
one that's one of those things that
comes along with you being a developer
learning how to commit in batches or
singly as appropriate yeah that takes
your experience there okay so we can
make our changes here and commit them
but we want to go farther right because
this is a great safety net in version
control but it's not helping me build
software faster mm-hmm it's not help me
build software better right so I want to
make sure that I'm actually building
things as well and the build system
visual studio team services fantastic as
well I mean I've watched people build
all kinds of stuff here mmm that from
from Java to Python to c-sharp to
database everything right it's just
amazing and we can see here we've
actually just built a new building so
when you created that build was there a
built-in template that you got to select
or did you have to build it from scratch
I actually built it from scratch now
those of you haven't used Visual Studio
Team Services give it a try
but it's a very simple project yeah just
like you would do for c-sharp I'm doing
a visual studio build and this is just
the standard Visual Studio build tasks
so okay so you named that my name right
so I can I can change the name right and
right here in the display okay this is
the standard Visual Studio build task
okay so if there were if I was doing
c-sharp in the same solution right let's
not restart now that's not to go
so if I were doing a c-sharp project in
the same solution I could build it here
I could have two Visual Studio builds
this intended each project separately
whatever is appropriate for my system
but you know I'm doing the same solution
build you know I've specified the name
I've got the MS build arguments just
like I would normally have okay
ready roll wants to build against
another database a target database where
I'm going to deploy to so I give it a
parameter station where am I going to
deploy so here's a question so when you
wrote the script you deployed it so you
already changed the database correct now
you've got a continuous integration
build what is that doing so what it's
doing is is going through and
re-evaluating the entire script to make
sure that everything's correct it's
looking against a downstream environment
that I would want to send it to you to
evaluate which of these changes maybe
you just changed the like a development
database yes confirm that it all works
and now you're using the CI process to
update the production database or
further downstream database I'm doing a
downstream data so I never I always like
to work in multiple environments in
these days with containers of
virtualization and VMs I always try to
have at least three environments if not
more so in this case I've actually got
five so one of the things I want to do
is I'll make my changes but you want to
be able to see my changes right or we
want to see all of our code together
just like we would in a c-sharp project
and web app so in this build what I'm
actually doing is I'm building and I'm
running my tests mm-hmm so I'm seeing
I've got the Microsoft unit testing
project as part of this so I'm running
tests in there against my database so
i'm doing sequel server unit test and
I'm doing my artifacts and if I pop back
to the summary of what's going on and
we'll look at that there were actually
results of that build that just ran and
we'll find it you know it took a few
seconds my test ran successfully I could
see you know the commits that are going
against it any warnings anything that
I've got going on in this case because
it looked at that next environment over
here on the right I see which
migrations weren't applied to my next
database yeah okay so that gives me an
integration place and in a matter of
fact you can see that there it knows
which stored procedure was changed there
right so it's kind of read through some
of the metadata I could see what's going
on and as a further step out of this
build I'm actually trigger to release as
well and where do you specify what
database it's going to is that part of
the build definition that is part of the
build definition so when I pop over here
that is a parameter visual studio like I
would add any other parameter and you'll
see here that I've said this build
integration database that's where I'm
gonna talk I next got it so that's
giving me kind of intermediate
environment nicely and if I pop over to
my visual studio let's sorry my
management studio let's grab this query
right here let's copy this and this is
my integration database okay okay so we
didn't touch this database you know I've
got a different coloring on it but my
row number five has been added
and my stored procedure here should run
cool right so cool right now as I've
done the process that I would normally
do a c-sharp and that I take my code
like everybody else's code put it
together generate an artifact and we can
then see if that is what we actually
want so you your continuous integration
there is actually a deployment as well
it is to another environment because in
the database world I I kind of need a
place to go look at that right so I've
actually moved this from one environment
to the neck so let me zoom in slightly
here I started right here in this
development area so this is where I made
the change and this is environment two
right here we just sent that change
okay and what I want to do then is of
course I've got QA yeah I've got this
staging and I've got production as well
okay so I want to kind of make sure my
code flows obviously testing at each
point in time run an application against
it having other developers QA people etc
look at what's going on right leading
businesspeople
I want to make sure that my code is
being deployed so let me grab let's
grab all this code real quick and let me
just look at my Qi environment all right
so if I run this now that stored
procedure doesn't exist
yep right neither does that Rover
because your bill definition only sent
it to the first database yeah my build
definition then actually kicked off this
release process as part of continuous
delivery so this release process says
once I built if I built successfully
mm-hmm and if I passed my tests whatever
is appropriate for myself what I'm going
to do is I'm going to do supply this
package out to a database and in this
case I'm sending it to my integration
environment oh so the build doesn't
actually do the deploying no no in this
case oh okay let me pop to the older
this new editors gotten slightly odd for
me but when I look at this build process
I'm sorry this release process this is
set as the option where they moved go
back to the first here click on one
phase one task that's where it is yeah
yeah so here I'm actually having this
kick off as per once the build a
successful okay it triggers this release
okay yeah so just like I want to do is I
want to have kind of an organized
process that makes sense
mm-hmm outside of the path in and the
parameters we could set this up in a
couple of minutes okay we would have to
go through the path you make sure we
haven't made anything incorrect there
but I have the ability to do that but
I've also got other release processes in
place in this case I've got a downstream
process as well which will push it to
those other environments okay so I have
the ability to make releases in a
consistent reliable fashion in a way
that as a DBA various times my career
have struggled with because as somebody
sends me a script I have to look at it
have to hope all the objects are
included mm-hmm
I have to hope that I've actually
remembered to correct connect to the
correct server right send it down there
right all the minutiae that just slows
down development right it just becomes
complexity just like deploying an
asp.net website or c-sharp too thick
client you know there I have to make
sure I go through all the process
correctly I have to remember that I need
to copy to these folders to these
servers mm-hmm right those things are
just they're kind of tedious things that
we don't want in general to do so having
this stuff happen automatically in a
lightweight process like visual studio
team services makes it really easy to
move this changes forward yep and see
what works and so this release is
created it's probably run by now because
usually they run fairly quickly and if I
come back over here and let's rerun this
code actually works this nice I've moved
it to QA yeah we could certainly repeat
that a forward to the other environments
as well right so cool trying to build
some of that DevOps process in here's
that I I make the process a definition
that is always handled in the same way
so we execute the same manner and then
if I find issues or if they find new
requirements I can slowly modify that
but I'm not dependent on a human to do
right and it gives you the ability to
not necessarily be the only one making
changes to the database just like in
coding you're not the only one coding
right right but things about the
continuous integration is I make changes
on my machine I run my tests everything
works you make changes on your machine
run your tests everything works marry
the code together the stuff doesn't work
anymore exactly right the CI will do
that I check in my code you check in
your code all the unit tests run we're
told immediately you can apply the same
process here I make changes the database
works on my machine you make changes
works on your machine and then you marry
them together and it all then goes
downstream and hopefully works right but
if hold of course and if it doesn't then
in process breaks but we now you know
why right so I've got a cool visual
studio team services gives me all
instrumentation to say what was deploy
what's included
so I don't have to dig through the
entire database I know in this case
there's two files that broke so if my
code doesn't work with your code yeah we
know which files I committed to go look
at and this isn't that hard to learn how
to do it's pretty easy to set up it is
pretty easy to set up yeah even even if
the UI changes it is I mean visual
studio team service I was amazed how
easy it is to actually do a build after
working in something like Jenkins which
works great but it's not the easiest
thing we work with yeah so visual stereo
team services and all the extensions
have made this a smooth process very
cool all right all right we learned
something you like migrations and
database development now I love it yeah
right well I love the fact that that you
can that DevOps and see ICD continuous
integration continuous deployment
pipelines are for databases too it's not
just for code right and you can just as
easily set one up for your database
stuff as you can for your code I love
that yeah yeah and I think that's one of
those things that will really help
improve the quality of our database
applications yeah oh absolutely cool so
that's part 1 part 1 part 2 part 2 we'll
do this again but we'll do it
innocent in a comparison method where
we're not going to try to track every
change right figure it out later
okay cool we'll see you next time on
visual studio toolbox
[Music]