Verifying your Database Deployment with Azure DevOps | DevOps Lab

Verifying your Database Deployment with Azure DevOps | DevOps Lab

>>Hi everyone, and welcome
back to the DevOps Lab, I’m Damian, and I am joined by Houssem. What are we talking about today?>>Today, we’ll see demos
for how you can add your database to the CI/CD pipeline, where you have the CI and CD
for your web application.>>Nice, sounds great [inaudible]. [MUSIC]>>Hi everyone, I’m Damian, and
welcome back to the DevOps Lab. We are here live at
Microsoft Ignite The Tour. The first event ever in Berlin. So I’m joined today by Houssem. We were talking before,
we have a session, played it today about database migrations as part of
the Azure DevOps pipeline, correct?>>Yeah.>>Cool. So we — this is
something that we’ve addressed, and shown before, but I
think it’s really important. It’s something that’s
worth stressing, and worth showing again. We were talking specifically
about the different ways to do your database changes
alongside their pay, but as well as that making changes in your pre-production environments
to make sure that it actually works when you
get to production, right?>>Yeah.>>Okay. Is this a problem that
you see in a lot of companies?>>Yeah, the problem here is that, when we need to apply
the migrations to the database then we
are sometimes afraid. If we do that on the
production environment, if things go bad then
it’s the production. So it’s too dangerous to the
data and the production.>>Right.>>So what do you do is
that you create another. We call it “Pre-Production
Environment” which is similar to the production, and at that production
we test all the changes. So if it work on
“Pre-Production Environment”. We are sure at 99% is going to
work also on the production. So it’s a more safe way to do
migrations for the database.>>We see a lot of the time people think for the database
is this special thing. I need to do those changes
manually then you haven’t tested, really have you, until
you get to production?>>Yes, so we’ll have — with that
we’ll have integration tests, and also unit tests for the database. Yes we can write unit tests. So those will make sure that
the changes we have created. It validates that change
to the database. So this means we are
sure our database will be — will have the schema
that we wanted to have.>>Yeah, that’s all right. Well let’s start with
that kind of started. When you get into the cities
a few different ways of deploying new database alongside
your application, and we talked about it
before in [inaudible]. Just to recap, do you have
— can you show me like a few of these different ways of
deploying a database alongside.>>Yeah, we have three main ways. The first tool are
using Entity Framework, because Entity Framework
it does — it has a tool for managing database migrations. So we can use that. So we can generate this
[inaudible] that do the migration than we run them
on “SQL Server” or “SQL Azure”.>>Okay.>>Then you have
another option which is more suited for database administrators, which is using the “SSDT” tools.>>Okay.>>So this one we have
the database project with our ASP net core application. That database project is the
one that will be used by the database administrators on order to generate the schema
for the database, and also, they will have
the option to change that scripts.>>Okay.>>So DBA will have more control on the scripts that will be
run it, and the database.>>Okay, can you show me, because this is one of these things. We do this changes in
these migration script. People still have to
run them manually. But you can absolutely do
this inside Azure DevOps.>>Exactly. That’s what why
Azure DevOps is easier to automate the manual tasks. So here we automate the migrations. So instead of the migration
run it manually by the DBA, now it will be run it,
and our CICD pipelines.>>Okay, perfect, can you show
us a couple of examples of this.>>Yeah, so here I have
an [inaudible] application. So here we have
this sample template for building the application
to generate the package. Until here it we have
nothing changed it.>>So we’ve got this is just
the dot net application.>>Yeah, this is
the dot net application, and to this app. I don’t have any special task
for the database, because here the first option using Entity Framework
is that with entity, we can let Entity Framework
manage all the migrations for us. It is just one line of code. We tell Entity context
dot database dot migrate, and with this simpler, and [inaudible] lane method, and Entity Framework will generate the
migration folder, and [inaudible] , and then we’ll run that glass, or that scripts, and
this CD pipeline.>>So this is already in the code. You haven’t had to do anything else?>>Exactly, this is and the code
using database dot migrate.>>Okay. All right.>>So this is the CI for it. Then for this CD, we take that package then we’ll deploy it to an Azure App
Service, for example.>>Okay. So this
Entity Framework Migration, it just does it all for you, ideally in this case you are
the same migrate in your carrier?>>Yes exactly. Then we let
Entity Framework manage all of that. You can rollback because
Entity Framework you can roll back. So we can’t do the same, if things doesn’t go well
then we can go back, and the advantage here of using Entity Framework in
this case is that, the scripts, or the
migrations have versions.>>Okay, yes, all right.>>So you will have all the history
of your migrations. You’ll have degenerated class
saying “This is for adding”, a second Columnum, another one for adding there’s Columnum
with the timestamp. So you can look at the table, and see all the migrations that
you have to apply to your table.>>[inaudible] .>>Exactly.>>Okay. Now let’s go.>>Yeah.>>So that’s one way of doing it, you mentioned [inaudible]
another one or another couple?>>Yeah, another one using
also Entity Framework.>>Okay.>>That one, the problem
with this approach is that Entity Framework
has lots — has all the control over
the scripts that would be around it on the CD pipeline. But if we want to have
a little bit of more control, then maybe I want to say, I want to see these scripts that was generated by Entity
Framework before running them.>>So this is where I’m saying, I want to see what’s
this script you’re doing. I don’t love the idea of
just putting occurring, and making decisions for me. I want to write
this script for myself.>>Yeah.>>So this is for me,
better for me [inaudible].>>Yeah, yeah, this way
we can validate it. So here at this second pipeline. I have took the same
first pipeline but I have just added
some additional task.>>Okay.>>It’s easier create the migrations.>>What?>>For Entity Framework. So we have this temporal line
of code right here, for example command, dotnet
ef migrations script. Using this script, when Entity Framework will
generate the migration. It will also generate this script. But we will be run it
later during the CD.>>Right. Okay.>>So here we’ll have
the file for that script. So in this case before
running the CD, we can go, and take a look at
this script that will be there “Drop” folder,
and the artifact. So we can validate it there.>>I thought next step
you’re going to be publishing business
and artifacts too.>>Exactly. That’s why
we are doing publishing. Then, during the CD, we’ll deploy our word up, that in addition to that we’ll go, and add a new task to deploy
that generated script. Generated SQL script. For that here, I have used the task Azure
SQL Database deployment with the connection to my database. And also, I think that here I want
to run a SQL script [inaudible].>>All right.>>Then I give it the script that
was generated during this year.>>Okay. That’s very cool. So, that gives you
a little bit more of control.>>A little bit more of control.>>I can see the scripts before
you decide to give us this?>>Yeah, exactly.>>So does not a scripts apply
to a specific environment, or is it a generic scripts that use Harrow apply to test environment, and staging environment in
a production environment even if those databases
different stages?>>Yeah. This state applies to SQL databases to SQL Server database, and in our case right here, and it applies also to SQL Azure. Of course, we need to take care of the differences
between SQL server, and SQL Azure because
of [inaudible] machine you will lead testing on SQL server, and you will be deploying SQL Azure.>>Okay.>>So some special cases the small differences you
need to take care about use.>>So Pentium, if you are deploying to your test
environment for example which is at a database
version to something. A schema that was a little bit out. Then when you deployed to that, and with this script, without still work even if you’re
deploying to version three, and more up-to-date database. Is this specific to a particular database version
like a particular schema version?>>Actually, this the with it works as Entity Framework managers
did the migration. So if you have V two, and your Azure database, and you have generated
this script for version four. Then, Entity Framework can go to see the table that was
generated from the database. To see you are on version two, and you want to migrate
to version four. So it will go, and apply the two migrations to missing
migrations to three, and four.>>Okay. All right. So, it really will
apply to whatever level.>>Yeah, exactly.>>Okay. That’s very cool. So,
we’ve got [inaudible] migration. You mentioned something about
doing a SQL database, SSDT.>>Yes. We said with this approach, we have a little bit of more
control than the first one, but if we want to get
more control like, 100 percent control of
this scripts that we’ve done, then at this stage we
need to use SSDT tools.>>Okay, cool.>>So, with SQL Server data tools, we’ll be using the database project.>>Okay.>>We can open that project in
Visual Studio or as DBA do, they open it in
SQL Server Management Studio.>>Okay, yeah.>>So, with that project if
you want to use that project, then here we need to build it first and also
generate the DACPAC file. The DACPAC file is a file that will contain the new schema
of our database.>>Yeah.>>That file, because it
contains all the scripts, all the SQL scripts, will
be deploying and that file instead of deploying
sample SQL scripts.>>Okay. All right. So, is this
just another way of doing it.>>Yeah. Another way and it’s
the most and the common, the most used actually.>>Okay.>>It’s more used than
Entity Framework because it offers more control to the DBA.>>Okay. Perfect.>>Yeah.>>So, this is a bit more
complicated than the other ones. You’ve got your [inaudible]
, and you’ve got a completely different set of tasks.>>Yeah. So, here first thing I
have done is that I have splitted the build for my Web App and they
have the build for my database.>>Okay.>>So, maybe I’m using multiple
agents to run them in parallel.>>Right.>>So, yeah. I might gain something. So, what we’ll be doing and they’re
planned for my ARM templates. We’ll go through this
at the last demo. So, for now to build
our database, so instead, we’ll go and build
the database project to generate the DACPAC file. Then we’ll go and copy it and
publish it to the [inaudible].>>Okay.>>Now, during the CD, we need to publish that DACPAC file. How we can do that, from
this CD pipeline right here. I’ll go and use the same task Azure SQL Database Deployment right here and I connect to my database, but with one difference is that here, I go and tell it to
deploy SQL DACPAC file, and here, I give it
the path to my DACPAC file.>>Which is the artifact that you apply to this part
of your compilation.>>Yeah, exactly.>>Right.>>Here you need to take care of some exceptions that
might arise for example, when the DACPAC starts running and at the middle
an error has occurred.>>Yes.>>Then it’s the worst case. You don’t have a plan to go back.>>Right.>>You need to figure
how to do that manually. So, this is one of the limitations.>>So, this is
the deployment process, you’ve shown us
the three different builds or three different ways of
creating these changes.>>Yeah.>>This is the deployment
process for it. That didn’t look too much different between the inter-framework
migration point. It’s just the first end of
the current rate migration where it handled it all for you
as just a line of code.>>Yeah.>>So, you using
the same task here for DACPAC and for the SQL
script. Is that right?>>Yeah.>>But one of them just did different drop-down at top in a different path. So, that this the process, right, but one of the other
things that we’re talking about was making sure that as thought of
those changes at will actually work in environments, say when you deploy your application, you deploy into a test environment, in a staging environment and
make sure that those work. But people quite often don’t
do that with a database. So, it’s very difficult to verify that it actually is going
to work in production.>>Yeah. As I mentioned the error
that might occur with DACPAC, we want to prevent this error. So, what do we do, is if now I can create another environment like
the production environment. We’ll call it pre-production, and we run this migration on
this pre-production environment.>>Okay.>>Because the two
environments are similar, so if it works on
this pre-production environment, then we are sure at 99 percent it will work also
on production environment.>>So, how do you make
that like production? So, a lot of the time
the problems come when my test drivers only has five customers and with
the five customers that I’ve created and there have three purchases
and it works there, why step away from
the production though?>>Yeah. The data here
is also important. So, what if I can have the same data as in
my production environment.>>Okay.>>So, what if I can import the production database from my pre-production and run
the disk test on that database. Of course I should make
sure that the data is we don’t have real names
and real credit card numbers->>Sanitized and things like that.>>Exactly, sanitized. How we can do that through here. Here, I have created a pre-production environment
in this CD pipeline. So, take a look at the pipeline and here we see
before running the production, we’ll go and run this pre-production.>>Okay.>>We have a condition to not go to production unless
the pre-production succeeds.>>Okay.>>What happens in
the pre-production? So, the pre-production we just
needed to validate the migrations. So, we can allow ourselves to create the production
random migrations. Check if everything is as expected then we go and destroy
that on [inaudible] tags. So, we save the base
on Azure resources.>>Okay. So, you’re building
a kind of on-demand.>>Yes, exactly. Temporary environment. For that I’m using here ARM
templates to create the environment. I’m creating SQL server and the database are
just SQL server because after I’ll be importing the database from the
production environment.>>Right. Okay.>>We have many options for
importing the database. We can only import
the data or whatever. Here, I’m importing all the database.>>Okay.>>You can choose
which solution fits your needs. Here, I’m importing
the whole database. So, I do that through a PowerShell,
Azure PowerShell script, which is here using
new Azure ARM SQL database copy.>>Okay. So, is it just something
you wrote or it’s in the Azure?>>It’s Azure PowerShell.>>Okay, that’s very cool.>>Yeah. So, that’s official
at Microsoft Command.>>So, there’s a question you were mentioning sanitation of
this data, which is great. But if you’re speeding
up in the environment to playing then copying the database across and
then testing that it works and then tearing
it all down afterwards. You could potentially do
this in a resource group that developed this and DBAs
don’t have access to at all. In which case, you might not even
need one to sanitize the data. Sanitization adds another.>>Yeah. When developers
can access that.>>Yeah. But if you just do this span of that nature
of work and tore it down, and nobody accesses that data anyway, maybe you can just use
the production data, like you not even
need to sanitize it. So, these exactly the same space.>>Oh, yeah. That would
be a good idea. Yeah.>>Just for me.>>Yeah, yeah, yeah.>>Yeah.>>We are already using
the ARM templates, so maybe we can add some other
section in the ARM template to say this resource group is only
visible by those because or.>>Yeah, maybe only visible
by Azure DevOps and so on.>>Yeah.>>The agents.>>Yeah.>>Okay. Just thinking about it.>>Yeah, yeah. Good idea
arises like this. Yeah, solve us problems. So here, we import. I will copy the database
from production. Then we’ll apply our DACPAC
by using the same path.>>Right. If MLD ends.>>At the end we’ll go and
delete our environment.>>You’re using it while you’re actually testing in
this kind of work.>>Exactly. If something bad
happens to the migration, then we get notification from Azure DevOps saying the
migrations doesn’t go well and you can take a look at the log and check where things was breaking.>>Yeah. But importantly, he haven’t gone to production.>>Yes. Here we are in this safe, let’s say Sandbox it environment
before going through production.>>So, that’s a pretty cool strategy. Can I just have a look very
quickly in that pipeline. So, you’re going to Dev to test and then your
pre-production is really just an environment to make sure your database migration is
actually going to work.>>Exactly.>>Yeah.>>Exactly, and more, we have also some other scenarios. So here, we are checking the
migrations to the database. We can also add unit tests and
integration tests to the database.>>Yeah.>>So for that, we might create another environment
that takes a copy of the production database then runs integration and unit test
is good environment.>>So, you’re really
putting the database into this pipeline to make
sure that it is going to work.>>Yeah. We are applying the same principles for
we applied for the app. We apply them for the database. Database also needs
to be unit tested. You just need to have
integration tests. Yeah, we can create, you can add the database as exactly the same way
we add the application.>>Right. Thank you for joining us as well. I can’t stress this enough. This is an example of exactly
how you can really test those things that people find very hard to include in the pipeline.>>Yeah.>>We have excuses for why
they can’t in the pipeline, but this scenario is something
that everybody encounters, and this is a fantastic
solution for it.>>Yeah.>>Cool. So, you are up a little bit later tomorrow.
Your sessions, is that right?>>Yes. Tomorrow is going to be
my session about how you can add your database to this CICD pipelines
using Azure DevOps.>>Excellent. So yeah, if we are still yet
to have the Azure, sorry, the Microsoft Ignite, The Tour event in your city, this is the kind of content
that you can definitely see at these events. Thank
you so much for joining me.>>Yeah. Thank you for having me.>>Awesome. Join us next
time for another DevOps Lab. [MUSIC]

Danny Hutson

3 thoughts on “Verifying your Database Deployment with Azure DevOps | DevOps Lab

  1. Wooow. Thank you so much Damian and Houssem. This is really a topic which is very often left out in talks. Actually I had the same idea a couple of weeks ago, but I was missing some proofs and/or backup from other resources. You really now gave me the starting shot to work on this. Again, thank you so much for the insights!!

Leave a Reply

Your email address will not be published. Required fields are marked *