Death to SQL Server! Long live SQL Server! How containerized SQL Server makes development easier

alright hey everybody we are lives thank
you so much for sticking around we just
had minor technical difficulties which
is the theme of the day with Skype so Oh
Mike and you seem you are you there i
yes I'm here your video looks frozen but
that's ok you look like you're mad the
way I'm so mad so what are you speaking
to us today about Mike so today we're
gonna talk about using sequel in
containers to do some development work
and just some random tasks that you have
to do without having to install it on
your development machine awesome let's
get started okay go ensure you screen
whenever you're ready oh well see it's
not showing you should be showing oh ok
well switch over there we'll do that
hopefully so there it is I see it now
there it is I've got as much hair as the
dotnet bot perfect but my beards not
nearly as pretty as Mark Miller's
without amazing or what that is so good
at that
yes yes so so yeah we're gonna talk
about some fun stuff or some what I
think it's fun my threshold for fun is
pretty low
I'm Michael jolly and I'm happy to talk
about using containerized sequel server
as part of our development lifecycle so
one of the greatest things about doing
this is this is all our twitch family I
see so many twitch friends and chat it's
just this is fam right this is family
time so let's talk about it let me tell
you a little bit about my background
I've been a software developer and
architect for a nearly 20 years
primarily focused on mobile and web I'm
live coding on Twitch three days a week
as part of the live coders team with
c-sharp fritz Jeff Ritz and I'm an
office hero i ambassador I love talking
about security so feel free to hit me up
on that because you know that's fun
stuff and when I'm not running code I'm
probably spending time with my fan
like playing music or trying to exit
them it's a losing battle so one of the
best parts of my job is to find new
technologies and tools and processes
that help people in their developer
experience and I'm excited to share one
of those with you today I've been using
it for write four or five months and it
is legit it is too legit in fact to quit
yes said that so docker we don't need no
stinking Dockers well what is docker
let's just go really quick high-level
overview you will not know all the
intricacies of docker after this but
what is it the easiest and some would
say too simplistic way to describe
docker is to compare them to virtual
machines they are not virtual machines
but both provides similar resource
isolation and allocation benefits but
they're fundamentally different because
at score doc VM that virtualize is
hardware whereas containers virtualized
the OS and really that's kind of a
heavy-handed way to describe it
containers reply be better described as
a system service they're isolated and
don't really know about anything you
don't specifically tell them about for
instance they don't need to know about
networking they don't need to know about
peripherals is that a word well it is
now and so on you know it doesn't have
to know anything about that it's just a
service that's running if you need it to
have network access you tell it about
network access if it needs to know file
access you tell it about file access
well another another reason we want to
use docker is because man the containers
the images are so much smaller compared
to like a VM image or just even a whole
install of sequel server the images can
be in the tens to hundreds of megabytes
versus gigs of just the install for
sequel right nobody but sequel in a
container what are the benefits to
putting it in a container why would I
want to do that well
I'll tell you why I'm glad you asked
first is the reduced footprint on your
local machine so if you've ever
installed sequel you know that it's a
big install and then I've got to deal
with updates periodically and I don't
want to I don't want to I don't want to
fight that battle right I mean I only
need sequel server when I'm developing
or maybe having to do some kind of data
manipulation for a client something like
that
you know I don't I don't necessarily
need all that stuff all the time
second one of the benefits is we get
cross-platform support so all of our
friends on Linux and our friends on Macs
who are developing can all kind of share
in the joy that is developing against
sequel without having to you know
install that there right and the third
one is one less although massive attack
vector on our machine if you've ever
installed sequel server and watched your
era logs you know firsthand that sequel
server is one of the most attacked
pieces of software in the world it's
insane and I don't want to be exposed to
that I don't want to think about that
right so by running sequel server in a
container you get all the benefits of a
full install while being able to throw
it away when you're done working with no
impact to your local development machine
that's right
hashtag not on my machine so let's let's
well before we get it to the demos I do
want to say you probably shouldn't be
running sequel server in a container in
a production environment while only a
Sith deals in absolutes I will say it
would be a very unique use case that you
would need to run sequel in a container
in a production environment but we can
use it for day-to-day tasks and to
improve our development workflow so
let's see it in action
are you ready first I'm just gonna show
you some some code we're gonna run in
the terminal spinning it up in a
container basically we're gonna run a
command we have to have docker installed
right that makes sense and we're going
to run that command up there that's kind
of in the teal
and basically to break down what each of
those points are you're setting two
environment variables one is the SI
accounts password shoutout dotnet comp
we're also gonna accept a EULA you just
got to do it name is optional but
recommended because it names that
container that's running and in
otherwise you're gonna have to reference
that container by like the ID which is
god-awful
don't do it you'll love me if you if
you'll you'll love me the minute you do
it and remember a Mike said don't do
that and then finally the dash D we're
telling we're telling docker what image
we want to use to build that container
so in this case we're using MMS sequel
server 2017 and it's actually running in
Linux so that's pretty cool wait let's
do that
matter of fact let's go back and let me
escape out of that and why don't we do
some magic in PowerShell so let me go
what we're gonna run that command trust
me we're gonna run it it's gonna be so
cool okay so there's that command I'm
just copying and pasting it in hit
go-go-gadget run and there it is
so it gives me this gala T and
that's that image you never want to see
that string so with that running we
should see some dr. containers running
so if I well I mean not use my aliases
let me the long form it here
there's our docker container look at it
it's beautiful look at that sucker
running for 18 seconds named sequel oh
one well so let's go through a use case
right so I can tell you we have a client
who oftentimes sends us a backup of
their one of their production databases
and says hey we've made some changes we
want you to refresh the sequel database
that you have and you're developing
against to have our newest stuff so what
it'll do it what will we keep their
database in Azure so they send us a
backup well normally you can just import
a backup file to Azure but you can't
have custom security logins with that
and wouldn't you know what this client
has
custom security logins because you know
customers gun a customer so let's go
into that directory I've got a folder
here and I've actually got a backup
right and I'm going to let's say we've
got that container out there let's just
do this let's talk about maybe can we do
it so we've got our container running
that's what we that we we we executed
this to do it now let's talk about
copying files in and out so basically
this customer sent me a backup file and
I need to get that security login out so
I can upload it to asher so we're gonna
run that command there with docker copy
and we're going to copy in the first
parameter is the source file the second
parameter is the destination file and if
you're going to use that you're gonna
use a container as one of the source or
the destination you need to prefix it
like container name or ID colon the path
so sequel one is the name of that
container I spun up so let's run that
same command and I'm going to in this
sources data folder I'm gonna copy in
that backup client gonna client dot back
and I'll come over here to my command
line copy up that to debt container and
it just comes up now one of the cool
things I did in this when we executed
the command to started up I exposed the
port that's up here port 14 33 I told it
hey in that container you have 14 30 14
33 map that to my local hosts my host
machines 14 33 so I can come into sequel
and connect to that sequel server using
the password look in here there's no
databases we just spun it up right
there's no databases here but I can come
in and say hey restore a database
because I copied that back into the
container
I can now magically select it there and
that bet you'll notice the path VAR r ms
sequel data that it no windows path you
know okay well thank you open it up
let's say now i know they've got yep
Shawn Hasselhoff I knew they had a
custom login in there so I'm just gonna
remove him I don't need it I'm just want
to get this okay Sonia I need to do a
backup backup yeah yeah default stuff
yeah just do it make it so number one
okay disconnect from that so now in that
container
I've got a copy of a backup file in
there that has that security login or
moved so I'm just going to let me see
let me copy that command in it's
basically the same copy command but the
parameters are back are swapped so I'm
gonna copy from the container out to my
local machine and here I've got a backup
and matter of fact if I go ahead and
delete that to show that it did it guys
you know don't want to be cheating here
there you go there's that backup from
the container that doesn't have that
security login so now I can go in there
and have fun with it so it's cool stuff
you can map drives matter of fact we can
um let's see let me come in to the
slides again let's slide on up in here
yeah yeah yeah that's a little late port
forwarding and volume mapping so we just
showed you port forwarding because we
added that p14 33 33 and that's mapping
it from the host to the inside we can
also map a volume it mop basically
mapping a drive right so in this case I
would be saying 'die sources data which
is a directory on my machine map that in
too far up in a cichlid data that'll be
a great way to persist that data because
once the containers are gone let me get
rid of that thing sequel 1d sequel one
goodbye to you so now we don't have any
containers and I'm gonna run the same
command again to create a container but
this time I'm gonna tell it hey map that
D sources data which is the folder we're
in over here to that directory inside
the container which is where Microsoft
sequel server stores its data in this
container so just hit that it's going to
execute there I got a container you
should be run
now and then look over here in this
folder all of a sudden my master
database starts showing up my model my
temp TB all those files start showing up
so now I can log back into that again
like a boss I can come in here and
create a new database yo
this is cool hit OK and I've got the
database in SSMS just like normal but
I've also got the MDF and everything
over here so now when I disconnect from
that you just get out of it I'm gonna
come over here and say you know stop
that container a matter-of-fact heck I
hate it so bad just delete that
container it's no longer on my machine I
don't have sequel installed on my
machine now but because I mapped that
drive I've still got the database and
the data that can use other places
that's the best table name ever and yeah
I mean all up in your chat reading
your messages yeah so so that's pretty
cool so we can do everyday tasks like
that but let's take it a step further
are you ready ladies and gentlemen are
you ready let's go into visual studio
because you know developer Gunn develop
so in here I've got a very simple app it
is so simple in fact that it will amaze
you all I've got is a couple classes you
know client class what's you're gonna do
it's got an A and create we're gonna use
some entity framework core right because
that's how that's how we play here and
then I've got some things you know
normally my connection streams gonna be
some environment variables super-secret
connection string but if I'm in debug
I'm gonna tell it hey the server is
called sequel demo the database is
called oMG data and there's my username
password now of course that's just in
debug right so there's just some stuff
we're gonna we have a method to seed the
data if we need it we got our model this
is all just EF core stuff we're not
gonna get into that but trust me it's
amazing and then we have one database
migration that basically builds out the
database with the clients and the and
the address table and then I've got this
like
web app does dotnet core MVC or asp net
core MVC app and all it's doing is
spinning up and in that home controller
I get that context from that thing
pull in the clients and return it to the
index view and the index view just
basically does a TR builds a table with
the ID and the name now my C data method
is running every time in my startup I've
got it telling it hey oh and she context
migrate that data police so that we have
it so then when I run it nothing's gonna
happen it's gonna bomb because it's
gonna say that database doesn't exist
except I have a docker file here because
of course I'm gonna eventually deployed
this asp net core app in a docker
container so I've got a docker composed
and that docker compose I say spin up
that doc sequel docker web app and call
it sequel docker web you know and then
it depends on this sequel demo service
which is basically the sequel 2017 we're
gonna call it sequel demo and because
it's in that docker container it's gonna
put those on the same network just
amongst themselves I've exposed the port
here so that I can access it with SMS if
I wanted to and then I've also mapped
that drive so that we can see the data
later on so that container was gone so I
can delete those files and I'm gonna
debug this web app and if all of the
demo gods have been sufficient
sufficiently appeased today we should
see a browser open up and magically
it'll be just the default asp net web
site except it should list one client so
let's see database method basically
seeds one client in one address and
right now it's starting up it's
compiling it all it's gonna spin up an
asp net core container and it's gonna
spin up a sequel server and there it is
look at that I've got one record with
just the ID that's a grid that the
customers name and when I get done I can
close this thing down
and stop debugging get out of it all I
can go delete containers now look at
that it created my oMG data database so
I've still got that that I can play with
if I need to I can access it I can spin
up that container and connect to it
through SMS if I wanted to
matter of fact heck let's do it why not
do it live let that thing start back up
we'll connect to that sucker like a boss
let's see we want to connect we're going
to wait for it to finish loading okay I
got my web app up so proof in the
pudding the web app is running so the
sequel server should be running ah OMG
data OMG data there's my clients my
addresses it's got the migration history
because you know that's how we roll with
EF core and that awesome I've got my
data there I can sample it an SS man so
if I'm debugging I think there's
something wrong with the data I can
still play with it and then I can close
it down so this is like legit just a
data base for me and my development
environment and when I'm done with it I
can say get out of here sequel and
you're not on my machine because I named
it I can just say doctor why don't you
remove sequel demo and sequel docker dot
wept because I'm done oh I had to stop
them d CX sequel demo I apologize for
the d CX that's an alias which you can
get from the dev toolbox
PowerShell extension it's pretty cool
and it also works better if you type the
names right justice you know little
helper there for you if he's typed the
name of the container right it always
removes it better so now I'm done with
Visual Studio I'm done developing but
because I mapped that drive I've still
got my data there if I want to use it or
if I didn't map it it could just go away
too which is wonderful
it makes everybody happy if you're not
happy with that well I don't know what
to tell you call Mark Miller because
that presentation was amazing
so with that thanks everybody for
listening to me talk about this it's
it's always fun to hang out with all of
you my friends on Twitch and the
interwebs this is the Michael John
that's right Bobby Johnson you can find
my slides the the code that I use some
additional links to help you start doing
this at that bitly link there well you
can find me on twitter at michael jolly
I know it sounds weird but you can also
find me on the web at Michael jolly calm
I know it's hard to remember and then of
course I'm streaming three days a week
on Twitch I'd love to see you there and
hanging out with you and and that's it
yeah anybody got some questions yes cuz
I've got answers we've got some I can't
hear me okay
I can't hear sweet so there we go
so yeah we have some questions that
people been posting people mostly having
issues with oh there you go perfect
thank you for turning the camera on and
off that was perfect
that's how we do it in developer land
that's right and off so with regards to
a hey EF and whatever else but the one
that's here that I think it's gonna
drive some good conversation is molten
MC backers asking why wouldn't you use
docker and sequel server in a production
environment why is that an issue because
okay so so si think of docker as like a
a service right a system service that's
running if you have like a little
process web app something very specific
like that perfect for that scenario you
wouldn't want to try you know thinking
about like micro services really right
but you wouldn't go put s ap in a
container it's just too much I mean
you're gonna you're gonna you're gonna
shoot yourself to be honest you're
you're not gonna last a year you're
gonna drive yourself crazy
same way with sequel server it's a
little too big and then you get into
issues of scaling that right for
instance like I showed you how to mount
a drive to like the host machine for the
data store that data persists well that
will be the only way to do it because
the containers transient it goes away
and comes back and come goes away
comes back well if you don't mount that
to a drive the data is gonna go away
with the container that is bad news if
you're running in production but if you
did mount it and you tried to scale that
sequel instance you're gonna have issues
because you can't mount all those
containers pointing to that same data
file and and live if things are gonna go
squirrely real quick when all those
containers are trying to access the same
MDF yep but it's perfect in these little
small scenarios where you have just some
tasks to run where I don't have to deal
with like the full big install I don't
have to deal with managing updates and
all that kind of stuff and firewall
settings to make sure I'm not being
attacked matter of fact if you don't
forward the ports to your host machine
like I showed you
there's no exposure at all I was totally
available in that little docker Network
it's really cool perfect yeah I was just
I was looking at the chat here and
Moulton backers it's like good cool good
to know thank you for answering my
question but that was really great good
good you everybody don't you
everybody loved the presentation you did
an awesome job I knew you I knew you
would but it's on our tech check a
couple days ago so thank you again
Michael for taking the time to chat with
us and share your knowledge and your
passion absolutely your humor with us
what were you funny now I'm kidding okay
so we are gonna for everybody watching
the stream we're gonna get Michael off
the the Skype here and get Swee key who
is our next presenter we're gonna talk
about mixed reality and xamarin know
this so we are gonna be doing that here
so we're going straight into the slate
and going mute for a little bit we'll
talk to you guys in a little bit our use