Up next in 10
Streamline Database Deployments by lizabeth Noble || Lightup Conference
Nov 16, 2023
This session is for developers, database administrators, database engineers, and data architects that want to automate their database deployments. Particularly for those individuals that have hit roadblocks along that way. We will discuss the differences between migration and stated-based deployment methods. We will also discuss options with branching and merging. At the end of the session, attendees will know how to select the best deployment method for their environment. In addition, attendees will be familiar with branching and merging strategies that can be used as part of their continuous integration strategy. Conference Website: https://www.2020twenty.net/lightup #lightup #2020twenty
View Video Transcript
0:00
Awesome. Okay, well, welcome everybody to the light up virtual conference supporting UNICEF
0:12
We are here learning 24 hours of Microsoft technologies and learning. This is all to help
0:18
fundraising for COVID-19. Most of us are, you know, our lives have been changed in some way
0:24
by COVID-19, and we're all trying to do what we can to help. So this is a great event. Feel free
0:29
to tell your friends about this event and you know just try to help help us kind of figure out
0:36
where we're headed in terms of getting this this COVID-19 issue hopefully headed in the right
0:42
direction so feel free to use the hashtag light up and this event is powered by the c-sharp corner
0:49
and the tech platform so thanks to them as well for getting this event together you are here
0:56
hopefully to hear a presentation about how to streamline your database deployments
1:00
My name is Elizabeth Noble. I am a director of database development for a retail company
1:07
And if you want to find me, I am on Twitter at SQLZelda
1:15
So we've got a barcode here. You can go ahead and scan that QR code. And, you know, once again
1:22
just give your support for UNICEF and for trying to make some some progress with COVID-19. I also
1:30
want to thank our sponsors. We've got several great sponsors here so feel free check them out
1:39
reach out to their their resources, you know this event wouldn't be possible without all of their
1:44
help so we are very grateful for them we've also got some feedback qr codes we've got a speaker
1:53
feedback qr code as well as an event feedback qr code in addition i will be speaking for the first
2:00
45 minutes or so we do have a chat channel open for attendees to ask questions so feel free to put
2:07
put your questions in there and about 15 till the hour we'll go ahead and get those questions answered so feel free to send your
2:21
questions all that good stuff so we're here to talk about database deployments one of my great
2:28
passions and kind of how i came into the world of database deployments was you know first of all i
2:35
I played MMOs and I saw the deployment release notes and thought it was super cool and exciting
2:40
And then I got into IT. I started as a database administrator, thought it was awesome
2:48
And then I did my first deployment. And that was pretty cool. Like I was excited about deployments
2:52
But throughout time, what I found was my deployments were getting trickier and trickier
2:58
And so I had several deployments where, you know, I would be up all night working on a deployment
3:04
or there would be 40 different manual scripts that I had to run
3:08
I might have grabbed the wrong script or somebody may have uploaded the wrong script
3:12
And so when we came in the next morning, we found out the wrong code was deployed
3:17
Or sometimes at a previous company, I had experiences where you'd push a bug fix out
3:23
Next thing you knew, the bug fix was rolled back because that change had gotten overwritten
3:27
And so this desire for database deployments came from a passion of basically how do we get out of this role, right? Because nobody wants to
3:38
wake up after being up all night, having to triage what's happening, having a war room. It's like I
3:44
got in the habit after deployments. I'd wake up after deployment. I'd immediately look at my phone
3:50
and check to make sure that I didn't have 16 messages, even before I got out of bed
3:55
Things like rollbacks and bug fixes. None of that's fun. Nobody likes the stress of having
4:00
a deployment go sideways. And whatever we can do to improve that deployment process
4:06
I'm all about. Because I really want to get rid of the stress in my life and the life of those
4:12
around me. I want to not worry about letting people down because that is a huge deal for me
4:18
I tend to worry quite a bit about whether I'm performing to my own expectations and the
4:24
expectations of those around me. And of course, IT is prone to burnout. So I also didn't want to
4:30
end up in a situation where I was burning out or I was burning other people out
4:35
And to give a little bit of an example, so I started about a couple years ago, we deployed
4:44
a new piece of software and we decided to do a phased rollout
4:48
And part of that phased rollout was we deployed to one store and then the next week we deployed
4:56
to two more stores. What we ended up finding is we ended up finding bugs and, you know, code changes and feature changes
5:04
And I was manually managing all of this code. And it was a nightmare
5:11
I mean, I was doing three deployments a week, three to four hours a night, at least probably six hours a night, to be honest
5:18
It was a lot of work. And I kind of got to the point that I was just like, there has to be a better way
5:25
And so I really took that opportunity of having lots of deployments and late nights and having to figure out which code to deploy manually and having to come in the next day and fix things to want to start moving forward to something where the deployments were easier, that they weren't hard
5:44
They just worked, and I didn't have to spend a lot of time thinking about it because, as I said, we're a retail company where I'm at, so most of our deployments happen 9.30 p.m., which isn't super late, but it's late enough that if things go sideways, you're up until the middle of the night trying to make sure that, you know, the retail stores open the next morning
6:02
So definitely something I didn't want to deal with. So to kind of take a closer look into where I started my process, I had everything was manual, right? So for me, everything was Jira user stories and script files, and they were uploaded on a release page
6:26
I found out that the other data team, the people on my team were scared of automation
6:33
They were scared that, you know, I mean, the database is important. The database has all the data
6:37
I mean, for my company, it has all the data in it that is used to run our retail stores
6:42
keep track of our sales. And so automating, deploying a database can be very difficult or fear inducing because
6:51
if one script goes out wrong, everything can go sideways. So you want to make sure or you want to have those guardrails to make sure that when things happen, you're not just overriding everything that's out there
7:05
And in a lot of ways, there was a little bit of a fear of losing control
7:10
So we had a lot of conversations when we started about how to handle some of this
7:16
How do we design our deployment process so that we know what happening so we feel comfortable with what happening and so we can get rid of those manual steps And that kind of led us to come up with some standards for things So part of what we focused on is how do we want to deploy a database So one of the benefits I touted
7:41
in terms of moving towards database deployments is, for instance, having databases and source
7:46
control. And one of the benefits of having a database and source control is that you can
7:52
And when you do a code review, you don't have to guess what code changed
7:57
Source control really helps you figure out what code changed. And so that kind of helped some of that overall consistency with managing the database code
8:07
And then deploying things automatically. You know, where I was at, one of the challenges I had when we did all these deployments
8:14
and I was up late at night is my development team, their application was in a CICD pipeline
8:22
continuous integration, continuous delivery pipeline. And so they had bundled packages and they knew exactly what package to deploy
8:30
And on deployment night, they hit one button and it went everywhere, you know
8:35
or they hit two buttons because they had a beta version and a regular version, but they only had to do like two things
8:40
whereas I was doing like 40 of them. So I wanted to get to that automatic way
8:45
of handling things, not to mention having the database in source control
8:50
provided some more checks in things. So when we first put the database in source control
8:56
we found out that having it in source control, if we tried to build it
9:01
we found stored procedures that referenced columns that didn't exist anymore. So we had to go fix those
9:08
We had to go get that resolved, but it actually kind of helped make us more comfortable
9:12
with what was in our database. So one of the issues that can happen
9:19
you know manually and honestly when you start setting up a pipeline some of the issues to try
9:26
to work through are what's happening with the code and so later on in this session we'll talk about
9:33
branching and merging strategies and how those will help you try to figure out or how to make
9:39
sure that you're not overwriting previous changes that you've already deployed or that you
9:44
can control exactly what is being deployed. Similar to code changes
9:55
one of the things that you have to work out when you start trying to streamline your database deployments
10:00
is defining an overall process. And that's not only a process of how do you check in the code
10:06
but also, you know, how do you, what is your deployment method
10:12
Which is one other thing we'll talk about as part of this session is your deployment method options later on
10:19
which will help you understand what's happening at the end. At the same time, there's other things in process
10:25
that you kind of need to think and have conversations about in terms of your development teams
10:32
When we first designed our deployment process, my company had everybody together in one unified team
10:41
Now, we had little, you know, sub departments in that team, but we all worked together. We all had the same sprint cycle. We all released everything on the same sprint. And so that made the conversation very different than when where we are now, where we have two development teams, one database team, and the database team and one of the development teams are on the same sprint cycle, but everybody's using the same databases
11:10
So we've got a long-term development team that has a completely different release cadence than the rest of us
11:16
And so we had to figure out how do we manage deploying database code when we have multiple different teams with multiple different ways they're deploying things
11:27
And this kind of led to, really led to where this presentation came from, is that when I originally designed our release process with the development team in QA, we had one process and it worked great
11:42
And basically, everybody checked everything in and then the DBAs reviewed it
11:46
And then if the DBAs approved it, they merged it into the main branch and then they deployed from the main branch
11:52
It was very simple. well what ended up happening was first of all that got a little confusing so we ended up just
12:00
deploying from basically the development branch and then to make matters more confusing as we
12:07
kind of changed our sprint cadence we started deploying everything except we didn't want to
12:15
deploy everything so then we were creating manual scripts to roll back changes that we didn't want
12:19
to deploy. And at the time, the perspective kind of started being, maybe we shouldn't be doing
12:26
automated database deployments. Maybe this is too messy. Maybe this is too new. And my take on it
12:32
really was that the way we managed our teams, our organizational structure had changed. And so one
12:39
of the things that we wanted to handle as part of that is to redesign what our process was
12:45
for deploying things. Now, one of the things I hear in almost every presentation having to deal
12:53
with deploying database changes is handling data modifications, ad hoc queries. They're kind of
13:03
in any way, shape, or form, they generally tend to be one of those things that we're all kind of
13:08
hush-hush about and don't want to talk about, but they're there. They're there and they happen
13:12
And so there are some ways to handle manual data modifications. And we'll go over kind of some of the pros and cons, or at least, depending on what deployment method you choose
13:25
we'll kind of determine which one is easier to handle those manual data modifications
13:32
So the whole goal of this is to kind of get out of this world where we feel like we don't know what's happening
13:40
you do a deployment, you come in the next day and you're like, oh, my goodness, everything's
13:45
broken and I don't know why. Or to me, it feels like there's gremlins in your system. You just
13:51
start not trusting anything. And my goal ultimately is to get out of that world, get out of the manual
13:57
world, get out of the world where I don't trust what's happening and design an environment where
14:02
I'm comfortable and I'm happy with how things are happening. And I feel confident that when I deploy
14:07
things at night I know what they're going to look like in the morning. So that kind of brings us to
14:18
why are we doing it this way and one of one of the reasons is that in my world usually when the
14:26
problem solving happens you want to get to the root cause but a lot of times you don't have time
14:31
to figure it out. And so my overall strategy is, how do we make this all more proactive? How do we
14:37
improve the overall kind of structure? So we're going to walk through some of, we're going to go
14:45
kind of in a reverse order from what you would expect. And that's partly to me because how you're
14:51
going to deploy the code is going to determine some other things about what tools you going to use and it doesn really control your branching and merging strategy but it kind of better to have the end in mind
15:07
before you start at the beginning. So we've got two really different ways of managing the code
15:18
And for me, the easiest way to think about what these two methods are is you have your source code
15:27
and you have your database. And so your code goes from your source code
15:33
to your database. You can essentially control exactly what code is getting deployed
15:39
or you can control exactly what your database will look like at the end
15:47
You can't control both though. So you can't pick exactly what you're going to deploy
15:52
and necessarily know you're going to end up with exactly what you're expecting. And that's partly because
15:57
I'm sure as some of you were aware, there are things like hot fixes that happen. You know, something, you know
16:03
a report might be running that's slow and it's causing everything else to fail
16:07
and somebody goes into the system and creates an index. Well, if they create the index on the database
16:12
and don't put it into source control, that can cause issues. And we'll walk through some demos of what that looks like
16:18
So this method where you control everything that you're deploying is called the migration-based method
16:27
And this is where you're controlling exactly what you're deploying. So you basically save individual script files
16:33
of what you're deploying. And I've got a tool set up in Visual Studio
16:38
that we can look at the code in a second. But I kind of wanted to walk through what this looks like from a database perspective
16:45
So you've got your migration-based and you're controlling what you're deploying. So this side over here with the green is your source code
16:55
This first database is basically like your development database. This is some like outside of source control
17:02
The second one that's kind of gray or black is your code that would be your hot fix that you might not have put into source or you did not put into source control and it would be deployed to production
17:12
So what you can do is when you deploy your source code to your development environment
17:20
it essentially gets, you take that one file and that one file then goes into the database
17:26
That's exactly how it is. If I then, so I'm in development, I've got my script there
17:32
If I go and I manually fix something and I put that in production, my databases are in
17:39
two different states now. So they're not consistent. So there's something different, say a new index on the database on the right that isn't in the database on the left
17:51
And it's okay that the code that's in the development database isn't in production because we haven't deployed to production yet
17:57
But there's still a difference in terms of that index that is going to get missed
18:02
And so if I then try to deploy to production, what's going to happen is, let me try that demo again
18:18
It goes into production. And then you see how we have the two databases are slightly different
18:25
So it's got the new development code, but it doesn't actually, it still has the new index as well. And so if you took the database schema from development and compared it to production, they would not match. And that's where I say that when you're working on a migration-based deployment, you can control what you're deploying, but you can't necessarily control exactly what your database is going to look like at the end
18:52
Now, you know, this is a simple enough example that we can we can all understand, you know, hey, I put an index out there and I know the index is out there and I know that index is going to get different, be different
19:03
But there are times where what can happen is. You have lots of different changes, right, or maybe somebody has elevated permissions and they just kind of get in the habit of changing things
19:15
And so one of the gotchas to this is particularly from my perspective, and I've worked at places where, like, for instance, everybody had SA, right
19:24
So if everybody has the SA login, people can go and change things in production and may not necessarily communicate it
19:30
And it's not really that people are necessarily ill-intentioned. It just, you know, as I said, we get busy, you know, right
19:38
So you're trying to fix something, you forget to put it back in source control, and things end up out of sync, and that can create issues over time
19:43
So, but that is one of the methods to think about. If you have really tight security, I think this is perfectly, like, it's great, right
19:52
Because you don't have to worry about anybody getting into your production database and changing things
19:57
I, however, did not have that same luxury when I was setting up my first couple systems
20:04
And we will try, I'm going to try to change screens. So to give you an idea of what a migration-based database project would look like in source control
20:16
we've got this solution on the right-hand side. And I'm using a free tool called dbup for my migration-based source control
20:25
And you can see I've got a script over here called create vendors
20:30
You essentially need to create the scripts in a sort of numerical file name order
20:35
so that you can control the order that they're deploying in. And so for this particular script
20:41
I have this table DBO vendor, and that's in my source control
20:47
If I go to my database and I open Object Explorer, you can see, let's see here
20:57
I've got the table vendor in there. But what I can do is I can go and copy this table creation statement
21:10
And I'm going to make a new item. And the one funky thing that I've, the way I've gotten it to work is I can go ahead and
21:22
create it, but I've got to use a text file to do it. If not, I can change the name
21:32
I'm going to go ahead and create a new file. Ender payment, perfect
21:40
And I can go ahead and save that change. Now if I go back to my database, one of the things that happens with
21:50
with, you see this table schema versions? If I query this table schema versions
22:07
right now it tells me that it has already run the script create vendor table
22:13
So part of what dbup does is it keeps this log of activities that have happened
22:18
Now I added the second file to my source control but I haven actually run it So what I would need to do is I can hit F5 to run that It going to pop up this little console window
22:36
And if all works well, I will have a new table. Fingers crossed
22:48
Nope, I did not. Now that's what's supposed to happen. Let me just check my package
22:59
my program configuration. It is correct. All right, so here's what we're going to do
23:07
Just so we can see the magic happen. We're going to try to trick it
23:21
Okay. And then
23:34
I've. Seemed to get a bit more text there, so we'll see if it woke up
23:47
All right, so we've got the vendor table, so there might be like a syntax error. let's see what's I don't want to get too far into debugging code so we're not going to worry about
23:55
why that didn't show up but I did successfully get the vendor paint the vendor table to show up I'm
24:01
going to guess what's actually happening is since I haven't checked this change in that may be why
24:06
it hasn't picked it up yet but we won't worry about that for the moment okay so that's what a
24:15
migration-based looks like is you have script by script each individual item you can do table
24:20
creation statements i said earlier we'd get into those ad hoc queries migration-based in my opinion
24:27
is really kind of the better tool for handling data update data manipulation sorts of activities
24:34
so definitely something to look at the other option is state-based so that's where everything
24:41
in source control is going to get deployed so once again i've got my code i've got my development
24:47
database i'm deploying it it goes to development i can do the same thing when i deploy my changes
24:55
essentially depending on all my toggles production will look like development after i've finished
25:01
deploying to production and then we'll walk through an example just like we did with the
25:07
migration-based of what happens when there is a hotfix that's applied. So we'll start again
25:17
We have some code. The code goes to development. The code that's in source control goes to
25:21
development. We have a production issue. We deploy a hotfix. We don't put that information in source
25:28
control. And then we're ready for our production deployment. Well, when we do our production
25:32
deployment, we overwrite whatever the hot fix was. That's actually what my company, my team
25:40
the development team, that's what we all decided we wanted. We wanted the assurance that source
25:44
control was our source of truth. So we chose a state-based, but I know lots of individuals who
25:50
prefer a migration-based. They prefer that kind of granular control over exactly what's being
25:56
deployed. Oh, and let's go do a demo of what that looks like
26:15
All right. This is what a state-based type of database might look like
26:32
I'm going to roll this one up because that's for a different conversation
26:36
What happens is if you already have a database in production, one of the nice things is you can right-click and
26:43
you can actually import the database into source control. It will import it automatically
26:48
So once again, I'm using SQL Server technology, Visual Studio. So under the covers, this is using SQL Server data tools
26:55
and it is free with Visual Studio. So it's another free tool you can use to manage your SQL Server source control
27:03
DBup, for instance, is usable with Postgres and MySQL. So it is more versatile in terms of the various databases out there besides SQL Server products
27:16
But we've got a vendor table also. And it's got some cool little functionality that if you don't want to write the SQL, you don't have to
27:26
Similarly, I could add a new item. This is a little, oh, you know what
27:32
I know. I'll show that in a minute. This is a little easier to kind of get that warm, fuzzy feeling where I can go look at tables and I can say that I'm going to make a table and I can call it vendor payment
27:51
And then I've got vendor payment. And So you can see it had a little bit of error handling to kind of tell me everything was fine
28:11
I can go ahead and build this solution and check it in
28:27
I know that I'm using the main branch. I haven't renamed yet
28:37
We'll get into branching and merging in a Jiffy. I'm going to go ahead and sync this
28:47
I actually have a continuous integration, continuous delivery pipeline set up for this database
28:52
Right now, if I go look at the database, it should not have that table but in a few minutes it should so right now it just has
29:02
build.vendor I forgot to rename the schema so we'll we'll bump into that in a second
29:10
okay so that is a state-based see if it's going to take like a few minutes for the state base to
29:18
update and I guess to give you a sneak peek. So I don't really get into building the pipelines as
29:27
part of this presentation because my real goal is to make sure that you know how to how to how to set
29:33
up your state-based, migration-based, your branching and merging strategies in order to get these
29:40
pipelines set up. Once you kind of have the pipelines set up, they're really not, they're kind of
29:46
So once you get one database set up, you can you can reuse it for each of them
29:52
Let's see here. So. If I go back to the previous. See here
29:59
two minutes so we're at about we've got about another minute to go maybe until it finishes
30:10
so while that's running i remembered what i did what i needed to do on that db up to get
30:18
that script to deploy so we're gonna we're gonna jump back to that real quick while this is running
30:23
because the db up i like it i will say i come from a an operations like not it operations but
30:35
business operations and database background so i do not come from the development background so
30:41
some of this is a little uh little little different for me but one of the things that you need to do
30:46
on these sql files in db up is you need to go in and you need to change the build action property
30:53
to embedded resource. And then once you do that, now it should go ahead and deploy that table
31:06
So we're going to go check on. Yep, so the build succeeded for my state base
31:20
And right now, excuse me, Right now it's working on deploying. I believe
31:30
Deploying all of that. But if I go back to the forecast migration
31:37
which is the first one we're looking at, that we remember I added the script and then it wouldn't deploy
31:41
I'm just going to refresh this. Then if I go to tables
31:47
I should see it now and I do. There we go. That was a migration-based deployment
31:53
You saw, I did the one deployment, I got the vendor table migrated
31:58
and then I did another deployment and did the vendor payment, and there were not any issues
32:02
That is okay because of this schema versions table. That table I showed you earlier
32:09
If I go back, it might let me query it again. Now you can see that I do have those two entries
32:17
It has recorded that both those items have been deployed, And I think from a deployment perspective
32:21
it actually is kind of nice so that you can keep track and know when everything's been deployed
32:27
Now we're gonna go check on that automated deployment process. And it looks like it is still in progress
32:35
I actually have built some unit testing in. I'm trying to build out my overall pipeline. So
32:43
got a few minutes here still. So it's working on deploying the database
32:48
and we're going to give it those just a minute or two, I think
33:03
Let's see how long the other deployments took. I didn't really say
33:14
We'll check on that in a minute, because we've got to keep on moving on
33:18
So branching and merging. So branching and merging are kind of the two strategies you use to kind of keep track of where your changes are and then how you're going to put them back into the overall perspective
33:33
So where I'm at, one of the ways that we use branching is essentially to give us a copy of the current code base and give us kind of an individual area to use that code base to do our development so that I'm not changing one stored procedure and somebody else is changing a table and maybe our changes are conflicting with each other
33:54
it kind of gives us our own little workspace. So that's kind of one of the main benefits of
34:00
branching. And then the strategy is how do you get all that information back together
34:04
And that does get particularly tricky with SQL Server, just because there is this SQL project
34:10
file that keeps track of each individual item. And at least in my experience, almost all of our
34:17
merge conflicts have to do with that one particular file when people are adding lots of different
34:21
things. So where I'm at, we actually have probably, I would say, potentially a pretty complex branching
34:29
strategy. Once we've gotten used to it, it's pretty easy to follow. We do feature branches
34:37
release branches, hotfix branches, story branches, epic branches. So we've got potentially branches
34:44
everywhere, but we kind of have decided that what we want is we want to have everything kind of
34:50
partitioned off for each individual task. Once again, the strategy that we went with for setting this up was it's the same strategy
35:00
that the development team is using for branching their application code. So it kind of helps keep that process for them similar
35:07
And that's kind of my overall strategy with continuous integration and continuous delivery
35:13
in DevOps is I want the process to be consistent. So I don't want developers to have to remember the database is super special and I got to do all this super special stuff
35:23
I want it to be consistent. So having a lot of branching options sounds kind of complex
35:29
And I'll be honest, it is kind of it can get complex, but once you kind of get in the swing of it, it's not used
35:36
It's not too bad. So to give you an idea, here's a branch. Right
35:40
So we've got our main branch. We're going to do a pull request
35:44
So that's basically going to, or not a pull request, I'm sorry. We going to pull master down to our main branch down to make sure that we have the most up main branch And then once we have the most up main branch on our machines when we using GitHub
36:02
because that's the joy of GitHub, is gonna go ahead and create a branch off of main
36:09
And in this case, it could be like a feature branch. So essentially I'm starting with a copy
36:14
of basically the same code that's in production. When I'm ready to deploy it
36:21
I've got my feature branch. For where we're at, our company decided we want to control
36:28
exactly what's going into the release. We want to control exactly the code that's getting deployed
36:33
We want to know exactly what code that's getting deployed. We want to keep it separate from
36:39
the main branch until we know we're happy with it. What we do is we make sure we pull main down again
36:46
We create another branch for the release. that's where we start getting into our merging strategy
36:52
So that's where we will take that feature branch and merge it into the release branch
37:00
so that when we're ready to deploy, it's all there. And one of the nice things is that then we can very clearly see
37:09
and choose what's being deployed. So it kind of gives us that granularity of
37:15
you know, this user story is okay to deploy, this user story isn't, and it lets us pick and choose what we're deploying and then handle
37:23
any potential merge conflicts prior to releasing all the code. So for us, we deploy that release branch to our UAT environment, and then once it's deployed
37:41
to the UAT environment, we will deploy it to production. after it's deployed to production, we will then merge the release branch back to the main branch
37:53
So that's kind of our overall flow. And that's kind of what we've done that makes us feel like
37:58
we're mitigating risk and that we're kind of in control of the things that are happening
38:03
So just kind of walking through this again, if we've got one feature, right? And then we have
38:10
another feature branch so now we've got two feature branches it would be the same sort of process
38:23
where you merge both of them in to your release branch and then deploy to uat deploy to production
38:29
and merge back and so this is kind of a high level look at what our our overall branching
38:36
release, branching, and merging strategy can look like. Now, I've had people attend my presentation
38:42
to give you an idea. As I said, I work at a retail company. All of our applications are internal
38:48
So we normally are not keeping track of, like, versions of applications. So this is not necessarily
38:54
the solution you want to go with. I know people that, you know, like software vendors that are
38:59
selling multiple versions of applications. This kind of branching and merging strategy won't work
39:04
for them at all. And honestly, I think they look at me like we're a little silly for having this
39:10
But it really does depend on your business. So you don't want to just copy whatever's out there
39:16
You really want to have those conversations and come to a conclusion about what you want to do
39:22
So merge conflicts, which is absolutely something that can happen, as I said, with SQL projects
39:29
it's even more likely because you've got if you if you have two people adding items at the same
39:36
time that kind of merge conflict will happen you definitely want to discuss what your strategies
39:43
are for handling a merge conflict another process that that my company uses which is not considered
39:52
ideal is that because we tend to keep those branches out and you know like we could have
40:00
like an epic and a database epic branch open for several months is we can actually you know create
40:08
merge conflicts and we know that so where we're at we handle those merge conflicts
40:14
in the pull request now i know a lot of times the strategy is to handle the merge conflict
40:21
before the pull request. So that's going to be another conversation that you want to have internally
40:28
And there are ways to do it. I will say, I think that if the concern
40:32
is with merge conflicts, then adopting a strategy similar to ones I've heard before
40:38
So once again, some of the companies that develop their own software, they merge their code like every day
40:44
or every three days. Like no more than three days can they have their code checked out
40:48
of like the main branch. and that's kind of how they manage it. So you definitely want to consider
40:55
how your branching and merging is going to work and how that's going to interact
40:59
with your potential for merge conflicts and then how you want to manage those merge conflicts
41:06
So we've got a demo and I know I've done a little bit of demo already
41:14
So you have five minutes. Do I know? I usually joke that I'm a 45-minute speaker, and today, yeah, this is good. It's okay. It's good
41:24
Okay. So, I'm going to check out this forecast table. I'm going to see before I go look at the code if it
41:33
made it there, and it did, and you see I have a DBO vendor payment table, but not that bill
41:42
table vendor payment table because I didn't name that correctly. So we going to see if in five minutes we can do some branching and merging So
42:02
I'm in the main branch. I am going to I use I tell you I use this every day and I still feel
42:12
like I click around half the time more than I should. So we're gonna
42:18
We're going to make a branch for a new table and I would normally not name it at these names
42:29
but I'm trying to kind of simulate that merge conflict quickly. And we're going to make a new branch called new stored procedure
42:44
So in this lower right hand corner you can see what branch you're working in. and
43:00
so i'm just going to add a new stored procedure and i even can click add new stored procedure
43:08
Alright, let's just say, let's see if I can build this
43:25
Alright so build succeeded. I'm going to go ahead and. Commit and sync these changes and all that good stuff
43:36
And then I'm going to switch branches
43:57
I'm going to make. A new table. I don't know if I'm going to get this done in time
44:15
so we'll see. I've got like one minute, no big deal. We'll see how the questions are doing. Maybe
44:30
Go ahead and save it. Sink. Alright, so those are both going on up to GitHub right now
44:42
Of course, I didn't have GitHub set up. All this stuff out here
45:56
that you have saved by using these methods? Oh, my gosh. So we put all of our databases in source control
46:05
So we had just one for like two years. So starting in September of last year
46:11
we finally moved all the databases to source control. I would easily say we save
46:19
we do deployments every two weeks. so I'd say at least 40 hours probably more than that um to give you an idea our deployments used
46:30
to take two to three hours to deploy and it was oftentimes because of all the different SQL
46:38
scripts so the more SQL scripts we had um to deploy this method I literally click a button
46:46
I review the script to make sure I'm comfortable with what is being deployed
46:51
So I just cursory look at something to see what's being deployed
46:55
And then I hit a button and it goes. And so my database portion to deploy a database now takes five minutes
47:06
A deployment. So it's pretty, for me, it's pretty phenomenal. I've kind of gotten used to the fact that we do..
47:16
Our deployments take maybe an hour and that used to be scary
47:21
If we had an hour deployment, we were pretty scared. Now if we have an hour deployment
47:25
we're not uncomfortable. It's pretty great. If you guys want to stick around
47:35
if there are any other questions, I'm going to try to finish merging this if that's okay
47:42
Pretty much want to let it finish processing though I think that great Okay cool We see if I can get a merge conflict and if I can solve it in Azure DevOps Kind of switch to Azure DevOps from GitHub
47:57
A lot of times I start with what my company uses, and then as I'm trying to experiment and learn more ways of doing things
48:02
I'll branch out. The other benefit that I didn't really get into with
48:09
continuous integration and continuous delivery if you haven't used those before is you have artifacts. So you have like packages at the end that are built. And so it
48:19
won't help you with the data. So if you're changing like a table, it won't help. But these automated
48:25
tools, you have like, so I have access to the last version of the database code that I deployed. And
48:33
so for instance, if I came in the next morning and found out that everything was broken and I
48:38
I couldn't figure out how to fix it fast enough. With just pretty much like a button click
48:44
you can roll it back to the previous version without having that concern about
48:48
I don't know what I deployed or I don't have those scripts handy
48:52
Probably 40 hours for time saved on deployments is probably a mild estimate
49:01
I would say we probably save, at least five to 10 hours of deployment
49:12
but probably five hours of deployment on people managing code. So we used to have people write stored procedures
49:19
that had the if exists, then do this thing. Every single deployment had to have rollback scripts
49:26
So now we're kind of in that environment where the developers can just write a script
49:30
and check it in. And that's really all that they need to worry about. So it takes a lot of time off of their hands
49:36
It does make it less stressful for my team that's deploying. And honestly, what I've found happens is it's kind of gotten so easy to do that now when we have to do something manual
49:50
because like, for instance, I said that we haven't figured out how to do those manual script changes
49:55
Now that we haven't, since we haven't done that, whenever we have to do a manual script change
49:59
it now feels like a big deal because we're not as accustomed to doing those changes
50:04
So, yeah, I'm glad to feel like I have my life back
50:09
I can put it that way. I honestly think this might take a while, and I know that we're getting near time
50:17
Yeah, so we'll have the next presentation starting in 10 minutes. So maybe we don't have the next presenter showing up yet, though
50:26
So that's what I'm saying. We have time. All right. Well, great. We'll see how long it takes
50:30
We'll go look at the log and see if it's telling us anything exciting. You would think for two little tables
50:37
This merge request you're seeing and what I was like, we're saving a lot of time on the deployment
50:43
You're not necessarily doing this particular step on the deployment. So it's really, this is all planned and staged ahead of time
50:52
So it takes some of that work off of there. Now I should get a merge conflict and I did
51:00
You see it's in the SQL project file. I'm going to see if it will let me
51:08
Yeah. See if I can figure out how to handle side by side
51:14
There we go. There's my new table. And it's like, should give me the option
51:26
Let's see here. So what's happening and I'm kind of, Let's see here, security bill, vendor info
51:35
It's usually like in GitHub an option to like pick one versus the other
51:42
There we go. Let's see if I can figure it out real quick
51:53
It might just tell me I got a. It's real time, I know
52:03
I got spoiled. I'm telling you, I got spoiled with the other way. There we go
52:15
We're going to approve with suggestions. We're just going to approve, actually
52:22
There we go. That should let us finish now. Alright
52:42
So what's happening is it's basically saying that there's like this new line item and it
52:47
doesn't know what to do with it and and basically to resolve this type of merge request, you
52:51
have to. Go ahead and accept that new line so you can either you can manage that a bunch of different
52:58
ways, but as I said, we've chosen to manage that just by manually editing the file. But I want to
53:04
thank you all for attending and hearing about the joys of database deployments. As I said
53:11
they're a huge passion of mine. Just remember, choose migration-based or state-based or
53:17
there's honestly options for both. But once again, thank you for attending
53:22
Thank you for your time and sharing with us. This has been tremendous
53:25
We appreciate great taking in the. And you know, contributing to the
53:32
Knowledge base is terrific, thanks. Alright, have a good day. Thank you too. If anyone has any questions
53:40
please reach out to Elizabeth Noble