 Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me is Steve Jones from Redgate. Hey, Steve. How are you doing, Robert? Good. We're going to talk about database DevOps today. This is part two of a two-parter. Yes. In part one, we looked at creating a CI-CD, continuous integration, continuous deployment pipeline, and in that one, we use database migrations. Yes. So as you're making changes to the database, you're keeping track of each individual one. Right. That's an approach, and then we put some DevOps on that. In this episode, we're going to look at a different approach, which is basically database changes, where you're not tracking necessarily every single iteration. Correct. We're going to slap some DevOps on that. We're also going to look at some stuff that we didn't cover in the first episode. So if you watch the first one, you should definitely watch this one as well, because there's new stuff that we're going to cover. Right. All right. All right. So when we did migrations, we have to track each change as we go along because we'll replay those, just like a profiler of some sort. Right. Now, I want to talk about comparison, which is similar to what database projects do, and it's also what we at Redgate do with our SQL source control in that we want to let you make all the changes you want, test them as a developer, and then what we'll do is we'll figure out everything that needs to go and be deployed to production, QA, all your other environments. So in the last one, we did make this stored procedure, but I don't have it in this database. So it's a different database, but this is my development database in my workstation. So we certainly want to create that. So let's create that stored procedure. We won't go through all the logic involved here, but we'll just create it. Now, one of the things, as a developer, you write unit tests. If you do ASP.NET or C Sharp, you write unit tests and run them. We want to do that in the SQL server world as well. And I've got the t-sql-t framework installed here with the number of tests that I want to execute periodically. Now, certainly I'll do these again in CI-CD because I don't trust developers will always run tests. Exactly. But as a developer, I do try to run my tests, and we'll see here that in fact, I've got this little failure on one of my tests that I've got a table without a primary key. And as a database person, you always have a primary key in a table. So if there's nothing else we learned today, it's primary keys go on tables. Right? There might be a reason you don't need a primary key, but it's very rare. So for all you developers out there, please always put primary keys on your tables. So let's make that change as well. I'll go ahead and do this quick alter statement, and I'm going to add a column and make it the primary key. This table in fact just has a VARCHAR column, so I don't want to make that my primary key. And then let me rerun my tests. And in this case, when I run these, now everything succeeds. And you'll see that I've got just a number of tests here that I'm running across a variety of different items. But I'm testing things in my database just like I would test them in C-Shark. Yep. So everything looks good at this point in time. So let me commit this to my version control system. And one of the challenges in management studio has always been trying to commit changes to version control. Visual Studio has it built in. It's very nice. Redgate, we've actually done some things where we've tried to make this work a little smooth. So this is a plug-in for management studio? It's a plug-in for management studio. You can see that my table change here shows that I've added a column over here on the left. That's my current database version, what's on my local system. And on the right, what's in version control. So I've actually need to add these constraints and the primary keys. These things are changed. Because in the previous episode, you were committing things inside Visual Studio. Correct. Is that migrations only? Or could you also be doing this in Visual Studio? I couldn't do this in Visual Studio right now. We haven't built a plug-in in Visual Studio to do this. For changes. For comparison changes. For comparison. OK. Got it. Because now I'm stacking up these two changes. These are separate changes that I'm going to commit. So certainly I could commit one. So just like anything else, I can say I'm going to commit one or commit two. But here, let me say I'm going to commit two. So I'm going to add a primary key and a new proc for data retrieval. So I give it a comment like I would anywhere else. I'm going to commit this. And once I do this, it's making a table change. So it's given me a warning that you want to be careful with table changes. I want to be sure that this is actually what I want to do. And then like anything else, I'm working with Git here. I'm going to have to push this out to somewhere else. So I'll push this out. And just like we saw in the last episode, that's going to get pushed out here to my project. So I've got this project here. Again, I've got a board of stuff. And then I've got code. In this case, if I want to look at the files that I have in here, we can see that rather than a set of scripts that are ordered, we've got things broken out like I might expect to see them as a developer, right? It's tables, store procedures, and other items. In that case, a lot of people like this format because it's easy for me to look at version control, look at my database, and understand what's going on. The migrations is less intuitive. But some people prefer that. So this is the alternative to part one to tracking our changes. Now, I don't see a change here, right? My last change was hours ago. But that's because I worked on a branch. So in this case, if I look through here, right? We see a minute ago, I made some changes that appear there now. And in the first episode, we talked about treating the database code like any other code. Like application code. So you can branch and do pull requests on that file. So I can do a pull request here, right? So I can do this thing and I can say, hey, I actually have an active pull request here. Let's approve and complete that. And then, do I have another one still? Maybe I just do it when I know. Two minutes ago. No, this was added. It's now there. It's the one, yep. And then master, where do we stand? Two minutes ago, we added our change, right? It's there now. So just like any other application code, I'm committing it here. What I see in version control looks very much like what I see in my database. And so I've got some correlation here that helps me to be sure that what I've done makes sense. And if I need to understand what has broken or what was done yesterday, last week, I can look and find. So if I was doing migrations, can I do branches and pull requests? You could. The one downside I say with migrations is that merging becomes complex because of ordering. So if you and I are working on changes that potentially touch each other in some way, when we did the merge, we would have to go, do I need to rename a file? Do I need to reorder things appropriately? So it can be done, but there's no good way to merge SQL code yet. And especially because in the database world, ordering matters. Unlike in the C-sharp world, the compiler works out which classes fit where. We have to do that manually. So I could do merging in the migrations world, but it's complex. So in the migrations world, you tend to develop more on trunk or more on main and say, everybody commit and let's just figure out if we've broken something quickly rather than branching. Okay. But you could do it certainly just like here we could develop on main as well. But I just want to show that I can treat that database code like application code and do the same pull request stuff. Right. Sweet. And so as part of that, the next thing we typically want to do is we want to build to run. Yeah. So I think, see if our build is actually completed here. And at this point here with the creating, the build continuous integration, the deploying to the downstream database, that's going to be exactly the same whether you did the migration or the compare, right? Exactly, right. Because at this point, the changes had been made in the development database and now it's just a question of pushing things down the pike. Well, doing again, we want to do the build. So we verify that everything syntactically is correct. All my name resolution works out in the SQL Server world. We want to run tests again just like I ran before, right, that I want to make sure that I'm running the tests that are appropriate. But you'd set up the build the same way, you'd set up the release the same way. Yeah, the process of what I want to do is the same, right? I want to build and test and then I want to release in whatever fashion I do the downstream environment. The mechanics are different, right? So in migrations I need to know what order those scripts run in and need to know which scripts have run. In this world, what I'm actually going to do is I'm going to generate a package, you know, Redgate, we use our own package. We package a NuGet version of the database. In SSTT, you would package up a DAC pack, but they both do the same thing, right? They're a version of all my code and I perform a comparison between that and the database, generate my upgrade script, send it along, right? So the process is the same and the mechanics are different. Okay. But you can see here everything has worked, everything has run fine. And a matter of fact, in my previous build, the failure was in fact that test. So that same test that I saw fail my development database, we caught it in CI. Because obviously I didn't run my test before I committed that last change. But CI lets me know very quickly that, hey, you did something wrong. You get publicly shamed. Yes, yes, all the other developers make me fix the build right away, so that we keep going. Right. And so now those changes have been made and in this case, I'm actually as part of the build process doing the release. So this build failed, obviously, but the one build that did succeed, I have this sync step and this actually gets pushed out to the downstream environment. Oh, so that's the release. Yeah, this is the release that's actually sending it to just another database. So I have my changes, plus all the other developer's changes are in fact listed down. When would you do the release as part of the build versus doing it separately? I think it's just how you feel the process works. In the previous episode, I had them separate. Yeah. I could have put it in the same place, but I like to show different methods because different environments have different requirements. Right. And I want people to understand that DevOps doesn't say do A, B, and C. Yes. Right, it says, as Donovan would say, I have to deliver value to my end users and that's what I have to do. So if that means I'm more comfortable doing the release as part of the build, do that. If I want it separated out, perhaps for security reasons, as part of the release process, I can do that. Okay. So I like to show that different ways of doing it because I want people to think, do what works for you. Right. I don't want them to do what works for me because then they'll complain it doesn't work in their environment. Yep, right. That's another issue. But everything else is the same. You know, if I go to the releases again, I've got release process here and you can see here that I've actually sometimes I release all the way to production and sometimes I don't. And that's a typical process. Even in the C-sharp world or the Java world, I'll release often, I'll build something, I'll get a release candidate, we'll put it in a test environment and go, it's not quite right. Mm-hmm. And so then we go back and rebuild, we do more development, and eventually we say we release. Right, that's what SQL Server's doing, right? We're releasing every 12 to 18 months now and they're dropping internal builds constantly for the MVPs we get, like every two months, they'll give us a build and say, here's the latest. Same thing here. So as we can see on the screen, some of my releases go to QA, which is my left-most environment, that's when I often release to. Some more of those will go to my staging environment, which is the one in the middle so that I get a chance to practice things and then very few of them actually go to production. You can see we were playing around, having issues with production as our security errors go in there. But like everything else, I have this stuff here. Some of the things that I may do that you might wanna include here is, in my staging environment, I actually have approvals here. So I actually ask, does somebody need to look at this environment and say, who is going to say this is okay? So in this case, I've added myself, but Visual Studio Team Services makes it really easy to pick a group or other people in your environment that may wanna look at this before it goes to some other environment, okay? Get a notification, they can review scripts, they can review the changes, or just be aware that something's changing. Okay, so you might, as part of the process, as part of the build, you would send it to a testing server or the QA server. That always happens automatically, so we make that part of the build. And then the release, it goes to staging requiring approvers and the approvers will wait until they get the results from QA and if it's okay, all they have to do is approve. It's already ready to be released. Yeah, so exactly what happened in QA in this case, it's easier to see in the old editor, but the same tasks exist. So in QA, I'll actually create a release and then deploy it, okay? And as part of our packaging, we actually do kind of a snapshot check when we create that release so that if some significant amount of time were to pass between the create and deploy and the environment change, so production change, we wouldn't deploy. Warn you, hey, something happened. Somebody made a change in production, but in QA I do this and in staging I do the same type of thing. So I'm gonna kind of follow the same process every time. Visual Studio Team Services makes it super easy because I can clone an environment, save as a template, makes it very easy for me to build things out. And I just specify the changes I need. So in this case, I'm actually specifying a different database for each environment. I've got my username and password, which can be set to the release in Visual Studio Team Services, but they can be overridden for different environments, which is often very important in the database world because where I send something to QA, I may not want the same accounts, the same passwords in production. Yeah. So as part of this process, I kind of ensure that I release in that consistent fashion in a secure manner without requiring Robert to be there at 9 a.m. or 9 p.m. on Friday to do the release. Yes. I can set this up. Well, it's 9 a.m. I'll be there for 9 p.m., not a chance. Not a chance, sure. So this way I can just get all my code working the same way, whether it's application codes or database code. And certainly, just like I've said this up, I could add application code to this as well. So I could be releasing a C-sharp code or ASP.NET code to Azure, to my clients or servers or anything that I want. Right. So as we showed in the first slide, I'm treating the database just like application. Yeah. Doing the same thing. Awesome. And that's what we want to do. Cool. Yeah. All right. Great stuff. Yeah, hopefully convinced you now you're going to treat the database like an application. You bet. All right. That's what we want. So I don't think we mentioned this in the first one, but the Redgate tools are included in Visual Studio Enterprise? Yeah. So we have some tools that are included in Visual Studio Enterprise and then we have other tools that are available if you do more database development and you need other things. Certainly. So people want to check this out, they can get a copy of Enterprise, either the trial or the preview, check out these tools and then decide how they work. Yeah. See if it works for you. Yeah. Awesome. You know, up your database development, add some DevOps to it and better applications. Right. Better software. That's the goal. Thanks. Thanks for doing this. My pleasure. Thanks for having me. Hope you enjoyed that and we will see you next time on Visual Studio Toolbox.