 Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green, and joining me today is Steve Jones from Redgate. Hi. Steve's here to talk about database DevOps. That's right. Now, we chatted at Build. We did a show at Build, and we covered some of this stuff then, but we're going to do it in a bit of a more deep dive and get a little bit more into the meat behind it. Okay. Let's do it. Right? Yeah, absolutely. So we're going to talk about two things. We're going to talk about two different ways of doing the database DevOps. The first that we're going to cover today is migrations, and then the second one is database compare, right? Data compare. Have you ever done migrations when you've developed database software? Yeah. Do you like migration? Do I like migrations? It's a tool and stuff gets done, so I like stuff that works, but I don't have a passion one way or another for it. Let's put it that way. Yeah. I find a lot of people don't, either do or don't like migrations. I've done a lot of migrations more in the Oracle and DP2 world where large ERP systems often just track all the scripts. There are hundreds of developers, right? And then we run them all in order and it always works, but it can be really slow sometimes. Okay. So some people don't like migrations, but I prefer it, you know, and I work for regular software. We support both ways of doing database development, so whatever works for you is fine, and maybe you'll pick one or the other after this. But at least I'll learn how they both work. Right, exactly. We're doing at the end of these two parts. All right. All right. Migrations first. So migrations first. Let's back up a step before we dive in. What are we doing? So I'm a database person. I've been a SQL server person for most of my career. I've actually been working with SQL server for 26 years now, so more than half my life has been working with databases at this point, right, so it's kind of amazing that throughout that time I've developed software in a number of different ways as a DBA developer, and so I've tried to work through these different ways of efficiently getting database changes to production, making sure that I'm not interrupting an application, I'm not causing downtime unnecessarily, certainly I'm not causing any data integrity issues or losing anything along the way. Right. So, you know, you make code changes all the time, but you're saying that, how often do people make database changes? Not as much as code changes, obviously. Not as much as code changes, right? The database, because we have to maintain that state of our data, unlike an application, right, I can drop a class, I can add a method, I can make these changes, and I kind of just replace different versions of code, XC's, DLL's, Assemblies, Resources, whatever it is. I can do that with some of my code in the database world, my views, my procedures, things like that, but my tables I have to keep around so I'm always kind of evolving them. Okay. And because I have to do that, especially as my database grows larger, I want to be careful how often I make those changes. Right. Renaming a table is disruptive because unlike renaming something in Visual Studio, the compiler can find all the references or the ID can find all the references and do that. For database, I also have connections coming from various places and I don't know what's happening. Right. So I think it depends on a lot of times the maturity of your application. If you're starting out, you may make more database changes than later. Yeah, okay. As I grow, I get a lot of technical debt kind of in my database world because of all those connections to other applications, reports, ETL systems. Right. So I'm hesitant to make those changes. Okay. Now there are people that do a lot of additive changes where they just add things to the tables, they add systems to the, or objects to the database so that they hopefully don't disrupt those applications. Right. And they can continue to add features and functionality throughout the lifetime of their application. Okay. So one of the things that, I've been doing really for most of my life is a lot of things we call DevOps. We're trying to have lightweight processes that work that help me make changes in an efficient manner without causing problems. And the last three or four years, all of a sudden we have a name, we call it DevOps. Exactly. We DevOps all the things now, I think, as Donovan Brown might say. Yep. So, so let me show you some things here. Okay. So I work for Redgate Software and we have ReadyRolls included in Visual Studio Enterprise. So that's part of what's there. And I have a little ReadyRoll toolbar here that comes up and that's a pane in Visual Studio. And my project in ReadyRoll is just like any other project. So if I were to create a new project here and since we don't wanna run through everything, ReadyRoll appears just like my database project would for SSDT or for C-Sharp or any other type of application that we have in Visual Studio. In this case, the ReadyRoll project builds off of a database project, which is slow to come up. But you can see that there's database projects like we would do for SSDT, for Data Tools, or I've got ReadyRoll projects here. And the difference is the database project works as tracking all the changes and then it does a comparison with SQL package and it'll deploy those changes. My kind of package, everything I'm in Dackpack. In ReadyRoll, we have what we call migrations. So in this existing projects, I've made a bunch of changes over time. You can see I've got all these scripts. And each one of these is just a change that I made to my database as I was performing development. So in this case, I'm altering the table. Obviously, I can't alter. So you wrote those scripts and then saved them in the project, right? Yep, I actually have a couple ways of doing it. I can import those changes if I've made this in Management Studio or another developer's made changes. So I can ensure that I'm capturing all the changes or I could write the script directly. So ReadyRoll, what it does is it takes the burden off of you of trying to manually grab every script, make sure you've caught all the changes, giving it a name and saving it off. Right, okay. So I can do that. So over time, I just collect these different changes that could be DDL changes, they could be DML changes. They're just a series of things that I'm doing to my database. Creating objects, I'm altering objects, whatever's appropriate for my application. So in this case, this is a common pattern that in the SSDT world or in a state-based comparison world is difficult to handle. I've got to add a not-know column but I've got data in my table. So I can't add a not-know column, right? I have to add a no-column, update some data and then add a not-know change. So that's a complex change. But I still haven't changed that. It's been that way forever. Right, there are ways around where I could add a default, I could do some things but for a lot of systems I've worked with a lot of developers. This is a problematic item because I don't want a default value. What I need to do is populate that appropriately with some value that matters for that row of data. So ReadyRoll makes these types of changes much more robust and resilient. So where's the migration ID come from there? So let me show you how this is done. We'll create a migration script now. So if I want to create a new script, I could certainly just right-click and add like anything else. I could certainly add a store procedure, a table or anything that I want to do. So when I do my development- So there's the migration ID that gets created when you create the script. That gets created when I create the script. So it gives me a unique way of identifying this script and tracking whether it's been deployed in any environment. Okay. So we do that. So let me create a little procedure here that I want to do and that'll just give me something that I can run. And I can certainly run this if I want. I have a variety of options like I would in any other database development that I'm doing that I can just mark my code, run it, test it, do everything that I need to do here. Yep. This has been executed on a local instance. It actually exists there so I can run any of my application against it. I have all the ability to do the work that I do normally as a database developer. And then periodically what I want to do is I want to make sure that I've deployed everything in my project. So I can click here and it'll do a build in Visual Studio like anything else. So I know that popped quickly up there but I actually have a conflict and something failed. Already an object name get 10. Yeah. Oh, okay, cool. So it thinks it's still there. I don't think that that's the case. So let's just check. I'm more comfortable. Who are you gonna believe? Who am I gonna believe? It does exist. I think as I ran it and then I said it was deployed. Let's deploy it again and do a build because I'm a developer, I'm a human, I make mistakes, right? Things happen as I'm going. Let's see, it deployed, it succeeded, everything is good. Okay, cool. So I can kind of see what's going on in my project as I'm performing development because I have the code and scripts. I could certainly check the database against this and I've got everything like I would normally have. This migration ID is how the framework will actually check if this script has been run. Okay. So inside of my database, we actually track that. So if I pop over here and let's just check it from here, there's this migration log and we can see here that this contains all of the migrations that have been run. So there's this GUID that we have, there's a checksum to make sure that the script I think ran is the correct script, right? I haven't edited or changed it and I have the file name and then various other metadata that lets me know what's going on. So this ensures that my scripts in essence will only run once so I can't do something like add data in a script and then worry about it being added again. Right. So I get data, recreate tables, that kind of thing. Mm-hmm. So far as I go through database development, it's similar to what I might do in C-Sharp or any other language. Right. I'm just kind of working with my project to make it changes and like any other change, this kind of appears as part of my team explore. Right. I've hooked up to version control which is one of those things that I find so many database developers don't do. Right. They don't track their DDM. Yeah. Right. And that's what they want to do. You've deployed this to the database already? My development database. Right. So I'm working with an development database and I've got this change out there. Mm-hmm. Okay. And I can certainly make other changes and let me show you something else because I do find there's a lot of developers that like to work in Managers Studio still because it's a comfortable way of working and it's quick. So let me add a piece of data here to this table and if I look at what's in here, I've got some data in there. So I just added row number five. So one of the things ReadyRoll also allows to do is track reference data or static data that I might want to include as I deploy this further. Mm-hmm. All right. So here's a value that I want to include. Well, once I've done that in ReadyRoll, what I want to appear I could do is check as anybody made any changes outside of Visual Studio. Certainly I could have just done run a query on Visual Studio as well that did that same insert. We can see here that I've actually detected there's a data change here. In fact, there's one row that was inserted. So I can add this as another script and this will come in as another migration script. This is script 32. It's got a different migration ID which you'll have to trust me that's the case. We won't compare that. I can't. But ReadyRoll has built me an insert statement. It could build me an update statement. It could build me a delete statement. Whatever would be appropriate here. Again, that's here inside of the change in Team Explorer as well. So all of this is my local development as a database developer, my laptop, my workstation. I'm doing my thing. Right. So are you committing this to version control as a way of keeping track of the history? Because you changed the database. It's a little bit different than writing code. It's a little bit different. It's a little different. Because certainly anything I do to a table or to data has to, it's kind of this evolutionary change of thing. That I'm moving from one state to the next constantly. I'm tracking it in version control. A, because I make mistakes, sometimes I have to go backwards. Okay. Just the way of the world. Sometimes I will misinterpret a requirement. The business person will tell me the wrong requirement and we will write code and realize it doesn't work. So we can roll back the database changes. Well, we can certainly find out what we change so that we can go back. I can certainly go to version control and grab those changes. Rolling back database changes is not always easy. Yeah, because it's not like the data is in source control, the history of the data. It's not. If you go back to a snapshot. Right. Now for store procedures and functions of views, I can just grab that previous code. Right, because those are just code. Those are just code. Yes, okay. I haven't come across a tool yet nor I think would I trust my job to a tool to roll back my data changes. Right, exactly. Because if there's been data added to the table, I need to decide what to do with that data. Right. If I've dropped a column, which is a very dangerous thing, I need to have prepared for that in advance because nothing is rolling, bringing me that table change back. Right, right, okay. So those are different, but as much as possible, I'm trying to treat all my database code like application code. Mm-hmm. Okay. And part of this is that I want to use this code to move forward to do CI and do CD, just like I would do with an application. Okay. So let me commit this. We'll see this out there. Actually, before I commit this, let's just go look. I've got a project out here in Visual Studio Team Services, which I think is fantastic, by the way. Mm-hmm. You're right. It is. This is maybe the best developer tool I've seen Microsoft ever build. Now, the Visual Studio Team Services is just amazing. Really is. And I use it all the time. So I've got a board like I would have for anything else and I can track all my database work. Yep. And I've got my code here as well and I've got my project. So I've got my main database project, my test project, because I want to test things. Mm-hmm. You know, in various items. And if I flip down in here and look at my migrations and my last change was this morning, right? A little while ago, we were checking things out. Let's go ahead and commit this change. So we'll say, channel nine with Robert. We'll call it one. Let's commit and push that. So once I do that, this will actually push up into the Microsoft Cloud. I remember the first time I looked closely at that dropdown and saw that commit and push, you could do all at once instead of committing and then going and finding push. This, that might be the one thing I wish existed. Instead of a dropdown, it would just give me the option to remember that. Ooh, that'd be even better. Right? Yeah. But if I, let's refresh this. So then I see what's out there. I should see, we just pushed out two changes. Okay. So it tracked two new scripts. Yep. And now I have a history of what's going on because in the migrations world, I'm tracking all these scripts over time. So that's an interesting best practice. You've got two changes and one comment. So given that all the rest of your comments are pretty descriptive, would you then typically just commit one script at a time? It depends on the work that I'm doing at that. You know, I, that's kind of a developer philosophical question, right? Do I include multiple changes into one commit? I would have that option just like I do with anything in Visual Studio, I could pick and choose what commits. In this case, if I was, if that data change was associated with the stored procedure change, I would probably commit them together with the work item and in a better description, of course that's not a great comment. But if they were separate items, as is the case sometimes in the database world, I would make two separate commits. And that's one of those things that comes along with you being a developer, learning how to commit in batches or singly as appropriate, right? That takes your experience there. Okay. So we can make our changes here and commit them, but we want to go further, right? Because this is a great safety net in version control, but it's not helping me build software faster, right? It's not helping me build software better. So I want to make sure that I'm actually building things as well. And the build system in Visual Studio Team Services is fantastic as well. I mean, I've watched people build all kinds of stuff here. That from Java to Python to C-Sharp to database code, everything. It's just amazing. And we can see here, we've actually just built a new build here. So when you created that build, was there a built-in template that you got to select or did you have to build it from scratch? I actually built it from scratch. Now, if those of you haven't used Visual Studio Team Services, give it a try, but it's a very simple step-based system. Yeah, just like you would do for C-Sharp, I'm doing a Visual Studio Build, and this is just the standard Visual Studio Build task. Oh, okay. So you named that build. I named it, right? So I can change the name right here in the display, but this is the standard Visual Studio Build task. So if I was doing C-Sharp in the same solution... Right. Let's not restart now. Let's not do that. So if I were doing a C-Sharp project in the same solution, I could build it here. I could have two Visual Studio Builds to build each project separately, whatever is appropriate for my system, but I'm doing the same solution build. I've specified the name. I've got the MS Build arguments just like I would normally have. Okay. Readyroll wants to build against another database, a target database, where I'm going to deploy to. So I give it a parameter and say, just where am I going to deploy? So here's a question. So when you wrote the script, you deployed it. So you already changed the database. Right. Now you've got a continuous integration build. What is that doing? So what it's doing is going through and reevaluating the entire script to make sure that everything's correct. It's looking against a downstream environment that I would want to send it to you to evaluate which of these changes may want to go forward. So you just changed a development database. Yes. Confirmed that it all works, and now you're using the CI process to update the production database or a further downstream database? I'm doing a downstream database. So I always like to work in multiple environments. And these days with containers and virtualization and VMs, I always try to have at least three environments, if not more. So in this case, I've actually got five. So one of the things I want to do is I'll make my changes, but you want to be able to see my changes as well, right? Or we want to see all of our code together just like we would in a C-Sharp project and web app. So in this build, what I'm actually doing is I'm building and I'm running my tests. So I've got the Microsoft unit testing project as part of this. I'm running tests in there against my database. So I'm doing SQL server unit tests. And then I'm doing my artifacts. And if I pop back to the summary of what's going on, and we'll look at the actual results of that build that just ran. And what we'll find, it took a few seconds. My tests ran successfully. I could see the commits that are going against it, any warnings, anything that I've got going on. In this case, because it looked at that next environment over here on the right, I see which migrations weren't applied to my next database. Oh, okay. So that gives me an integration place. And in a matter of fact, you can see that it knows which store procedure was changed there. So it's kind of read through some of the metadata that I could see what's going on. And as a further step out of this build, I'm actually triggering a release as well. And where do you specify what database it's going to? Is that part of the build definition? That is part of the build definition. So when I pop over here, that is a parameter visual studio like I would add any other parameter. And you'll see here that I've said this build integration database is where I'm going to target next. Got it. So that's giving me kind of this intermediate environment. And if I pop over to my visual studio, let's say, or sorry, my management studio, let's grab this query right here. Let's copy this and this is my integration database. Okay. So we didn't touch this database. You know, I've got a different coloring on it, but my row number five has been added. Yeah, added. And my store procedure here should run. Cool. Right, so right now I've done the process that I would normally do with C-Sharp. And that I take my code, like everybody else's code, put it together, generate an artifact and we can then see if that is what we actually want. So you, your continuous integration there is actually a deployment as well? It is to another environment because in the database world, I kind of need a place to go look at that stuff. So I've actually moved this from one environment to the next. So let me zoom in slightly here. I started right here in this development area. So this is where I made the change. And this is environment two right here where we just sent that change. Okay. And what I want to do then is of course I've got QA, I've got this staging and I've got production as well. Right, so I want to kind of make sure my code flows. Obviously testing at each point in time, running an application against it, having other developers, QA people, et cetera, look at what's going on, including business people. So I want to make sure that my code is being deployed. So let me grab, let's grab all this code real quick. And let me just look at my QA environment, right? So if I run this now, that store procedure doesn't exist in QA. Yep. Right? Neither does that row of data. Right, because your build definition only sent it to the first database. Yeah, my build definition actually kicked off this release process as part of continuous delivery. So this release process says, once I've built, if I've built successfully and if I've passed my tests, whatever's appropriate for myself, what I'm going to do is I'm going to deploy this package out to a database. And in this case, I'm sending it to my integration environment. Oh, so the build doesn't actually do the deploying? No, no, in this case, Wow, okay. We popped to the old editor. This new editor has gotten slightly odd for me, but when I look at this build process, or sorry, this release process, this is set as the options here. Where they moved. Yeah, go back to the first here. Click on one phase, one task. That's where it is. Yeah. Yeah, so here I'm actually having this kick off as once the build is successful, it triggers this release. Okay. So just like I want to do is I want to have kind of an organized process that makes sense. Outside of the path in the parameters, we could set this up in a couple of minutes. We would have to go through the path and make sure we haven't made anything incorrect there. But I have the ability to do that. But I've also got other release processes in place. In this case, I've got a downstream process as well, which will push it to those other environments. So I have the ability to make releases in a consistent, reliable fashion in a way that as a DBA, various times in my career I've struggled with. Because somebody sends me a script, I have to look at it, I have to hope all the objects are included. I have to hope that I've actually remembered to connect to the correct server, send it down there, all the minutiae that just slows down development. It just becomes complexity. Just like deploying an ASP.NET website or C Sharp to that client there, I have to make sure I go through all the process correctly. I have to remember that I need to copy it to these folders, to these servers. Those things are just, they're kind of tedious things that we don't want in general to do. So having this stuff happen automatically in a lightweight process like Visual Studio Team Services makes it really easy to move those changes forward. Yep, awesome. And see what works. And so this release is created. It's probably run by now because usually they run fairly quickly. And if I come back over here and let's rerun this code, it actually works this time. Excellent. All right, so I've moved it to QA. And we can certainly repeat that forward to the other environments as well. All right, so. Cool. Trying to build some of that DevOps process in there is that I make the process a definition that is always handled in the same way. So I execute the same manner. And then if I find issues or if they find new requirements, I can slowly modify that. But I'm not dependent on a human to do it. Right. And it gives you the ability to not necessarily be the only one making changes to the database. Just like in coding, you're not the only one coding, right? Right. But things about the continuous integration is I make changes on my machine, I run my tests, everything works. You make changes on your machine, run your tests, everything works. Marry the code together. Stuff doesn't work anymore. Exactly. Right, the CI will do that. I check in my code. You check in your code. All the unit tests run were told immediately. You could apply the same process here. I make changes to the database. Works on my machine. You make changes. Works on your machine. And then you marry them together and it all then goes downstream. And hopefully works, right? But if it doesn't. Of course. And if it doesn't, then the process breaks. But we know. And now you know why, right? So I've got, cool. Visual Studio Team Services gives me all the instrumentation to say what was deployed, what's included. So I don't have to dig through the entire database. I know, in this case, there's two files that broke. So if my code doesn't work with your code, we know which files I committed to go look at. And this isn't that hard to learn how to do. It's pretty easy to set up. It is pretty easy to set up. Yeah, even if the UI changes. It is, I mean, Visual Studio Team Service, I was amazed at how easy it is to actually do a build after working in something like Jenkins, which works great, but it's not the easiest thing to work with. So Visual Studio Team Services and all the extensions have made this a smooth process. Very cool. All right. All right. Do we learn something? Do you like migrations in database development now? I love it, yeah. All right. Well, I love the fact that you can, that DevOps and CICD continuous integration to continuous deployment. Pipelines are for databases too. It's not just for code. Right. And you can just as easily set one up for your database stuff as you can for your code. I love that. Yeah, yeah. And I think that's one of those things that will really help improve the quality of our database applications as we move forward. Oh, absolutely. Cool. So that's part one. Part one. Part two. Part two will do this again, but we'll do it in a comparison method where we're not going to try to track every change. Right. We'll just figure it out later. Okay, cool. We'll see you next time on Visual Studio Toolbox.