Database DevOps with Redgate Data Tools

hi welcome Visual Studio toolbox I'm
your host Robert Greene and joining me
today from bill that the beautiful
Washington State Convention Center is
Steve Jones from red gate thank you and
today special guest co-host mr. Scott
Klein renko's remember we are going to
be talking about data today and
specifically database DevOps yes red
gate provides some database DevOps
tools inside Visual Studio Enterprise so
we're going to talk about those you're
going to do some demos yes but we're
going to start off by explaining this
concept of database DevOps
all right we talked about DevOps all the
time continuous integration continuous
deployment pipelines fast cadence you
know lots of quick releases and we
talked about it from code but apparently
it applies to databases as well no we
don't you know ready to spend a lot of
time in the last five six seven years
evolving our tools to try to support
more rapid database development you know
as much as we've studied the DevOps
movement that continuous delivery stuff
the things that Mountain Martin Fowler
talked about they always leave the
database out because the database is a
challenge the database has to maintain
state from time to time to time right
unlike code where we could just replace
a DLL or an XE in the database I can't
just drop those tables I mean my life
would be easy if I could write life
would be great but you can't just invent
a new engine to dry it out a B testing
did people actually want a person class
in this application well some do some
don't so we'll we'll cut that out it's
easy in code right it's been yeah for
developers but for the database people
it's more difficult and I think that's
led to the hesitation from database
people to move faster but certainly all
the application developers you've got to
move fast you've got to deploy code I've
got to deploy enhancements and then
often means I need a place to store some
of that data so Red Gate is working to
try to get the database to move just as
fast as the application so give us an
example a real-world example of what
that might look like how is it database
changing so here's a great example we
all know security is a big deal right
never seen data breaches every week
we've got all these problems all the
time
there are plenty of customers that are
starting to do things like implement
encryption into their system so I want
to implement always encrypted on to a
database platform so over you know
PayPal whatever ecommerce I'm doing so
I've got to field that story and perhaps
credit card numbers or emails or
something else and I want to implement
that quickly well it takes time it's
difficult to do encryption so one of the
things I might do is I might add a new
column that will support that encryption
so I will do encrypted data there but my
application has to run 24/7 right so
what I do is I build a view on top of
this table that contains the old column
that's still there and I put a case
statement in there some type of switch
that says if I have encrypted data we'll
send that back and the client will deal
with the encryption and always encrypted
but if I haven't got encryption because
one of my applications isn't there let's
send the old column back that isn't
encrypted okay so in a 24/7 environment
I can evolve my database quickly using a
devops process keep it running at the
same time meet the new requirements that
I have for security for anything else
cool so it's not like you're inventing
entirely new tables changing the basic
structure but it's more incremental
changes to support incremental
functionality your needs exactly you
know in the database DBAs have been
hesitant to make changes because I've
been a DBA in my career and we're
responsible for stability and
availability and performance and so we
get nervous about changes not that and
you don't write good code but every once
in a while it's a little freaky so we
get hesitant which means we try to delay
deployments we try to stack things
together get lots of changes right which
increases my risk yeah and it means I've
got to make a bunch of changes at once
and that's the opposite what we want to
do in DevOps right you developers we
want to deploy often I want to be able
to make a new branch write some code do
a pull request have somebody prove it
deploy that next week or deploy that
tomorrow and we want the database to
follow along and so I've done that a lot
of my career and Red Gate has really
spent a lot of time to try to help do
that as well mm-hmm could we back up
just a second because you and I were
asking about this I'm like if DevOps is
understood I think primarily by
developers right right but if I'm a
sequel person you know what is this very
very simple what is
and what does that mean to me for no
good because I really how many how many
sequel people really understand what is
what it means yeah what about
I mean DevOps is kind of amorphous like
the cloud there isn't necessarily this
definite definition what it is it's
adhering to these principles that I
followed for most of my career when I've
done Six Sigma or ISO Tiger software I
want to learn and I want to become
better every time I want to keep my risk
to a minimum and I want to adapt process
around what I do all right a lot of what
we do in DevOps when we do continuous
integration continuous delivery we do
instrumentation telemetry is we're
adding process but we're doing in a very
lightweight non intrusive way so we're
trying to do a database as well so that
you can make your changes you can deploy
them out in smaller increments and have
them work with less risk now there's no
magic right there's no magic to making
database changes all we're doing with
DevOps is we're really automating the
things you would normally do yep in
trying to wrap them in a process so
they're reliable and repeatable because
ultimately we as humans we're not
reliable repeatable we're very creative
we get paid to solve problems and
compose solutions but we have to do the
same thing over and over and over again
okay we're bad and what we're trying to
do with DevOps is try to implement some
process that makes it repeatable and
reliable yeah cool all right yeah we see
some things happen absolutely demo time
demo time so I've got Visual Studio
Enterprise on my machine and in the box
Red Gate is shifting is shipping a ready
roll core product and ready roll is one
way of performing database development
and we built it as a project that's a
first-class project just like the
database projects that are in SSD T and
so when you implement this it's very
similar to that project now ready roll
works in what's called the migrations
based development framework where we
track every changes so if I look over
here I've got all these migration
scripts and each one of these is just a
different set of data scripts that I
would run in development as a developer
database developer or a full-stack
developer I would just kind of make some
of these changes and what ready roll is
doing is kind of tracking them as we go
along and so that when we deploy them we
execute them the same way mm-hmm now
it's just it's it's kind of
philosophically different from SSD T or
some of the other Red Gate products and
that those work in a state based world
where we can
the state of the database the state of
the development at and we come up with
the script yeah right so this is kind of
tracking one by one yeah let's spend a
little bit more time on that because
there are the sequel server database
tools yeah the Redgate tools which ship
in the box because although you do they
don't just install by default but you
can go to the individual components and
get out the work there they are
so talk to us a little bit more about
this state versus migration approach in
the state but I complementary or they
you don't know the other they're kind of
opposite you know Redgate got started
with sequel compare which is a schema
comparison that tool that is very
similar to what's an SSD to and in the
state based world we make a bunch of
changes just like I might do in c-sharp
or Java or something else and then I
take those changes and I compare those
changes with what my production database
looks okay and then I develop a script
kind of in real time a dynamic script so
kind of like with things we would do an
application program it's kind of like
run time versus design time okay so in
the state based world you do that and
that's fine and it works great for so
many people people use read gates to
compare to do that they use SSD to do
that and it's great the problem is
there's a domain of problems that you
can't solve with the state-based
comparison so if I rename something how
do I know I renamed it is it a drop-in
ad or a rename I'd if I add a not know
column there's data manipulation
involved I can't solve that problem so
there's a class of problems that
state-based comparison doesn't solve
right in SSD T people use pre post
scripts to get around them with red gate
OU's will use one called migration
scripts to get around it but it's just
one way of development again in my past
I've worked in Oracle db2 world and a
lot of those larger systems older
systems have worked in migrations world
where they go every script you write
every change you make the database we
track that so when I go to upgrade I run
script one and then two and then three
and then four and then 56 or 57 to 58
and it's a bulletproof way of doing
deployment but it can be cumbersome and
time consuming because the other thing
is if I add a column to a table drop a
comment table add a different column all
three of those things run in production
on my development database with ten rows
data who care
my production database with the billion
Reza data slightly problematic yeah so
neither one is perfect they both have
pros and cons they're just different
ways of working at the world and
throughout my career I've been working a
sequencer for 25 years now what I find
is that people fundamentally fall into
one of two camps either like state-based
like SS PT and sequel compare or they
liked migrations like ready roll or
liquid-based Flyway DB DB up various
frameworks antenna is this a kind of a
thought shift as we you know you've been
talking about database compare you know
schema compare an SSD teen things like
that I is this a shift and how I think
about working with databases now from a
you know because because we have all
these people like yep no schema compare
I know SSD T but now we're you know
ready roll and I'm like how do we get
people you know start thinking down
those I think just philosophically you
decide you I'm going to develop one way
or the other
okay it's kind of like saying you know
do i mix functional programming an
object-oriented I don't I kind of choose
one or the other and I have either some
philosophical leaning or I have some
requirement that says we should go one
way the other in the database world it's
the same thing it's just neither one is
better than the other one they both have
pros and cons they both have workaround
yep and but it is one more of a best
practice type of thing no I mean there's
advantage so one of the things we love
when I've worked in like Oracle
financials with large ERP systems is
that when I do migrations based of LMA I
can take any birds in the database and
upgrade it to any other version because
I have all the scripts obviously all
right so if I have clients at version 2
and version 3 and version 4 I can give
them the same set of scripts and get to
version 5 and that's easy in the SSD tea
world I need custom scripts for every
version right ok so there's pros and
cons the other side is I'm tracking all
these scraps right ok
some developers hate this correct and
some some don't like the state based
thing because they're not confident that
will actually develop all the changes
employed right so it it's not really a
shift it's an option so ready roles are
giving us an option for people that
prefer this it's how we actually think
about things you got actually okay all
right so let me make a couple changes so
we were in Visual Studio ready roll is a
part of Visual Studio it ships in the
box
for enterprise there's various things
you might want to do some people like to
work in Visual Studio so no it doesn't
like that let's see if it'll work this
time
let's forget let's do it this way
because I'm a code guy so I don't mind
writing code so let's create a new table
we'll call it channel 9 and I'm here
with Robert
he's an inn in Scott he's a would make
you a masquerade of Archer yeah but back
to your max all right so I can I have a
reliable type if I can run it very kind
of on a path and you're just fly deeper
yes so you're there I can't anyway I'm
getting from this you know I've got this
I've got this table here that's got a
feeds in it let's add a new feed so it
feeds into one of the things we also
subdivisions to enterprises is single
prompt core which is our intellisense
version and it's pretty good so we'll
add another feed in there and then
because we know some people like to work
in management studio we can always work
in management studio as well so I can
pick up changes here so let's pick
something in my development database
here and let's pick some stored
procedure in here and let's just pick
this one so here's a stored procedure
and here's one of the other things about
sequel code that makes it life difficult
is this code is fundamentally different
from the code I used to build a sore
procedure right because I would actually
have to have a create here to build the
stored procedure but instead I need an
alter mm-hmm right when I build a class
in c-sharp when I say you know public
void class yeah that code always looks
like that I mean yeah right whether I
add or subtract the code fundamentally
the same in here it's different but in
this case let's change something let's
actually do a best practice and we'll
just pick up some of these columns and
rather than the select star we'll do
this and I'll execute this so I've made
a bunch of changes here now so in ready
roll one of the things we do is we've
added a pane here and what this does is
allows me to kind of
for any of the changes that I've made
and once I've done them you'll see it's
detected number of changes and when I
import these it's actually going to add
them to the project and so what we'll
actually get I'll refresh it to make
sure everything is added we've added
some different scripts here so those
migrations are records it's a record of
changes that you have made in the
database that are ultimately going to
need to be propagated or published or
whatever look exactly - so this script
a17 here is my insert right we've added
that in there and my script 16 is that
well either table I think was somewhere
else maybe it's in here stole but I've
added these scripts in here and so they
exist as part of my project they're
under source control so exactly and so
we're trying to do is treat this exactly
like our code so in this case I've
actually just got new code here that
I've added right so I can like anything
else I can add it so added channel them
and I can do what I want to do just like
I might do in Visual Studio is I've got
my board here and visuals media where
I'm tracking work so I can I can use
this pound syntax to actually add commit
and push my changes so part of what
we're trying to do is ready roll is
implement the database just like we did
any other code and actually track this
stuff and I think that's that sort of
answers the question that we asked at
the beginning what does it mean to be
database DevOps is it just as you make
changes to an application in code and
you want to be able to manage those
efficiently you make changes to the
database right I want to treat that the
same ways right so we want to add those
in there and so one of the things I've
also done here is that we're actually
running a build in the background so
I've actually got a Visual Studio agent
running on its machine it's actually
running a build right now so just like
we would do with our c-sharp code or
vb.net code when we submit it in we want
visual studio team services to actually
be running and if I look here I should
see my build just succeeded I think
and I've got all the information in here
that looks at like what I would expect
I've got test coverage running and you
can zoom ibly break the build because
you made database changes that are that
are violating some rule or didn't quite
work out or something right I mean one
of the things we want to do with DevOps
is we're trying to pull all of our code
together and determine very quickly if
we've made a mistake right because
that's a risk issue so here I can pull
this in I can run my build and if I look
at the definition this is just a visual
studio build task there's nothing
special about what this is right
I could I could have this building
c-sharp as well if I was doing it and
then I run tests in this case I'm using
the Microsoft test framework to actually
run tests like a what does the building
test actually do didn't like take those
scripts to a like a sample database and
run those and say yep that's exactly
what the old will actually rerun those
scripts against a temporary database and
determine if they're valid sequel all my
object reference I resolve all the
things like that the test is just a test
like you would run for anything else
right there's the written T sequel but
but I set up a test and I say you know
check this value run this query see if
the results match right and go from
there
and so once I do that I just output an
artifact and just like any other code
I've done that okay in this case I've
actually I'm committing out to a release
process as well so we're doing
continuous delivery so one of the things
that we want to do here and I want to
say my build it succeeded a minute ago
so I should see up here a release to
integration that just ran and so if I
were to actually go back over here and
let's change the integration database
and let's say I've got that feed that we
just added and I didn't change that in
this database or I didn't do anything
else and as a matter of fact let me go
further let's pop over here to QA and
let's look at QA now if that doesn't
exist yet but let's make it exist so
here I have the ability I have these
downstream releases as well and Scott
will let you Scott watch me so I'm not
going to use the keyboard as well all
right we'll put the keyboard away and
the creative release
and I'm going to say release this and so
without using the keyboard at all we're
going to see this database change deploy
to another database right look ma no
hands nothing out of my videos if in a
second this will be run yeah and as a
matter of fact let's come over here
we'll execute this
I think it's deploying right now and we
should see that data changed actually in
here and we would see the other change
as well if we wanted to go through that
right click we're in the process there
it is so here we are in the QA database
right you saw me I didn't touch the
keyboard a topaz at all and we've
deployed this in the same way that we do
all our code and again when I look at
this release definition this looks like
I would deploy a asp.net web app to
Azure or a mobile application anything
else in this case I've got multiple
environments that I'm deploying to I've
got these tasks that I put in here in
these case these are tasks from red
gates that allow you to do that and just
deploy all my code what's the what's the
set up time for if I wanted to set
everything you showed what's the setup
time that would take me to you know
properly set up a DevOps environment
properly so that would benefit yep so to
set up the entire environment
technically from the technical
standpoint we could do it in less than
an hour as a matter of fact I do talks
all the time where we'll take we'll go
from scratch and we'll import a database
into a project and we'll set up C I will
commit it to version control setup CD
and we'll do it the cultural changes are
hard to write because everybody has to
get used to understand so I'll discuss
from the technical standpoint I think
the the other aspect you know the now
getting me to use this process is the
not the hour it's right it's every month
because we don't realize I think
sometimes all the little steps that we
make as humans when we're trying to
deploy code okay and we see this when
I'm trying to deploy to Azure if we take
the sample apps and I deploy asp.net
j''r now I can use yeoman or something
and be done in ten minutes right but in
terms of getting all my code out there
and realizing well I really need to make
sure that I've setup firewalling and
that I'm actually deploying this extra
bit of code that's side to my project
and I've got to config that all those
little
changes are what take time but that's
part of DevOps because we're learning as
we go and so we start to make those
changes right right so where would
somebody go to learn more about database
DevOps so we do a lot of work at Red
Gate I run sequel server central we do
some work there where we're publishing
information and then I'm working on a
course for MS learning right now and
hopefully we'll be done in June on my
travel schedule but at open EDX
Microsoft com
Microsoft is publishing a huge series on
DevOps and AD and all sorts of languages
platforms different things and we hope
in June have a database DevOps course
out there that will give you more
information about how you can work with
state-based with migration based with
open source platforms or sequel server
with whatever you want okay so the the
tooling that ships in Visual Studio
Enterprise is it a trial is it a no it's
a harder version it's a real deal
yeah it's an untimed version what we
have there is a Visual Studio Enterprise
contains ready role core and sequel
prompt core and sequel source 3 red gate
products ok the sequel prompt and the
ready role cores have a few less
features than we have in our full
product and but you can upgrade out
there they're not trials they're full
versions that will allow you to do this
deployment and development you know as
much as you want so can't visual studio
enterprise and then what is sequel
search so sequel search is just a full
search product that lets you go
throughout your database and find
objects in a more intuitive and flexible
way than what ships in management studio
ok all right I'll be honest how many
people know that these products are in
because I had no idea sounds to connect
so if you were paying attention
thanks for calling that out Robert well
it's amazing visuals did you have 2017
that there's all these different
workloads and you don't realize
sometimes that you know if you don't get
that mobile workload you don't get
xamarin in there and if yeah the data
workload you don't get ready roll and
and there's various workloads so you
should definitely look at the visual
studio installer and look at all the
different workloads that route 58 vision
mm 15 and 17 just 17 just say okay
that's dozens that's why because I
haven't looked in 17 yet and 15 okay so
17 only said Isis has changed okay I
feel redeemed now yeah one last thing I
just wanted to talk to you about briefly
one of the things that developers always
want to do is we we want more data we
want to work on real data right because
it helps us do an application and often
if we just use the ten rows of data that
I feel like typing that day yeah I don't
get a good feel for whether my
application works with the data is in
production or its scale or anything else
so red gates been working on a product
that we just released called sequel
clone that allows me to make copies of
full-size databases and use very little
space so if I jump over to my screen
real quick one of the things we'll see
is I've got a production database here
and it's got three hundred Meg's so it's
not big but you know for my laptop
that's decent size but I built these
clones and these are real copies of that
300 Meg but each one is only using 48
megabytes of disk space and that 48
megabytes is fairly constant even if
this was a terabyte database yeah and
and I have the ability to quickly create
a clone so on this machine I can create
a clone and I'll say we'll call this
Scott because Scott's my friend and I'm
going to create that 300 megabyte mark
here in an eye out five six seven
seconds I've got a copy database and if
I look it over here in my management
studio we've got the Scott database how
big how big was it 40 Meg's so I've got
all my rows actually let's take that top
out of there I try not to break things
too often but so I've got thousand rows
in here yeah and if we look at this
Scott it is a full-blown database full
database so if I look at the properties
database this says it's 270 Meg and 60
Meg okay but on disk this has only taken
48 Meg of space Wow Wow
so I could we can do right now at the
two terabytes because we're using VHD
files but very soon we'll have up to the
64 terabytes VHDX files but we can make
copies of those databases with you know
roughly 50 70 Meg on your laptop and
allow you to get access to that full
size database and then what are you
impressing that how you getting there
know is that the secret sauce you can't
there's definitely a secret sauce it's a
little bit magic and unicorn dust on
there but really what we do is we take a
point in time snapshot of our database
and then we have a central copy of that
and we're all reading from ok and so
it's a similar data virtualization
technology company - ok but Redgate has
brought it to the sequel server platform
at a very low cost and it allows you to
give every developer copies of the real
database and if I don't like this
database I can delete it in seconds and
I can create a new one now if I've
updated the data and I want to do
something else I can do a new one in
about where we say 7 seconds and we'll
call this one Robert and I'll build
another database on seconds and then I
can just go ahead and work with that and
be ready to go that is very cool cool
well thanks so much for coming on thank
you very much for having me so we've
seen a good overview of what I need a
base DevOps is I've learned a lot this
tool that you can that shipping
Enterprise go to go back into the
Installer and go into the individual
components tab and find them there there
they are playing around with it and
learn yeah that's your homework
assignment everybody yeah give it a try
yep thanks hope you enjoyed that and we
will see you next time on visual studio
toolbox