 Hi, welcome to Visual Studio Toolbox. I'm your host, Robert Green. And joining me today from Bill at the beautiful Washington State Convention Center is Steve Jones from Redgate. Thank you. And today's special guest co-host, Mr. Scott Klein. Thank you. Thank you for having me. We are going to be talking about data today, specifically database DevOps. Yes. Redgate provides some database DevOps tools inside Visual Studio Enterprise. So we're going to talk about those. You're going to do some demos. Yes. But we're going to start off by explaining this concept of database DevOps. We talk about DevOps all the time, continuous integration, continuous deployment, pipelines, fast cadence, lots of quick releases. And we talk about it from code. But apparently, it applies to databases as well. No, we don't. Redgate has spent a lot of time in the last five, six, seven years evolving our tools to try to support more rapid database development. As much as we've studied the DevOps movement, the continuous delivery stuff, the things that Martin Fowler talked about, they always leave the database out because the database is a challenge. The database has to maintain state from time to time to time, unlike code where we can just replace a DLL or an XE. In the database, I can't just drop those tables. I mean, my life would be easy if I could, right? Life would be great. Yeah, you can't just invent a new entity, try it out, A-B testing. Did people actually want a person class in this application? Well, some do, some don't, so we'll cut that out. It's easy in code, right? It's been easy for developers, but for the database people, it's more difficult. And I think that's led to the hesitation from database people to move faster. But certainly all the application developers, you've got to move faster. You've got to deploy code. I've got to deploy enhancements. And that often means I need a place to store some of that data. So Redgate is working to try to get the database to move just as fast as the application. So give us an example, a real-world example of what that might look like. How is a database changing? So here's a great example. We all know security is a big deal, right? We're seeing data breaches every week. We've got all these problems all the time. There are plenty of customers that are starting to do things like implement encryption into their system. So I want to implement, always encrypted, onto a database platform. So for PayPal, whatever e-commerce I'm doing. So I've got a field that's storing perhaps credit card numbers or emails or something else. And I want to implement that quickly. Well, it takes time, and it's difficult to do encryption. So one of the things I might do is I might add a new column that will support that encryption. So I will do encrypted data there. But my application has to run 24-7. So what I do is I build a view on top of this table that contains the old column that's still there. And I put a case statement in there, some type of switch that says, if I have encrypted data, we'll send that back. And the client will deal with the encryption and always encrypted. But if I haven't got encryption, because one of my applications isn't there, let's send the old column back that isn't encrypted. So in a 24-7 environment, I can evolve my database quickly using a DevOps process, keep it running at the same time, meet the new requirements that I have for security or for anything else. Cool. So it's not like you're inventing entirely new tables, changing the basic structure, but it's more incremental changes to support incremental functionality or needs. Exactly. Database, DBAs have been hesitant to make changes because I've been a DBA in my career and we're responsible for stability and availability and performance. And so we get nervous about changes. Not that you don't write good code, but every once in a while it's a little creaky. So we get hesitant, which means we try to delay deployments. We try to stack things together and get lots of changes, which increases my risk. And it means I've got to make a bunch of changes at once. And that's the opposite of what we want to do in DevOps. Developers, we want to deploy often. I want to be able to make a new branch, write some code, do a pull request, have somebody approve it, deploy that next week or deploy that tomorrow. And we want the database to follow along. And so I've done that a lot in my career and Redgate has really spent a lot of time to try to help do that as well. Mm-hmm. Cool. Can we back up just a second because you and I were asking about this. I'm like, you know, DevOps is understood, I think primarily by developers, right? Right. But if I'm a SQL person, you know, what is, it's very simple. What is DevOps and what does that mean to me for, you know, because really how many SQL people really understand what it means? Yeah, what it means. I mean, DevOps is kind of a morphous like the cloud. There isn't necessarily this definite definition of what it is, but it's adhering to these principles that I followed for most of my career when I've done Six Sigma or ISO type stuff where I want to learn and I want to become better over time. I want to keep my risk to a minimum. And I want to adapt process around what I do, right? A lot of what we do in DevOps, when we do continuous integration, continuous delivery, we do instrumentation and telemetry, is we're adding process, but we're doing it in a very lightweight, non-intrusive way. So we're trying to do a database as well so that you can make your changes, you can deploy them out in smaller increments and have them work with less risk. There's no magic, right? There's no magic to making database changes. All we're doing with DevOps is we're really automating the things you would normally do. And trying to wrap them in a process so they're reliable and repeatable because ultimately, we as humans, we're not reliable and repeatable. We're very creative, we get paid to solve problems and come up with solutions, but we have to do the same thing over and over and over again. We're bad. And what we're trying to do with DevOps is try to implement some process that makes it repeatable and reliable. Yeah. Cool. All right. Should we see some things happen? Absolutely. Demo time. Demo time. So I've got Visual Studio Enterprise on my machine, and in the box, Redgate is shipping a ReadyRoll core product. And ReadyRoll is one way of performing database development. And we've built it as a project that's a first-class project just like the database projects that are in SSTT. And so when you implement this, it's very similar to that project. Now, ReadyRoll works in what's called a migrations-based development framework where we track every changes. So if I look over here, I've got all these migration scripts. And each one of these is just a different set of data scripts that I would run in development. As a developer, a database developer, or a full-stack developer, I would just kind of make some of these changes. And what ReadyRoll is doing is kind of tracking them as we go along. And so that when we deploy them, we execute them the same way. Now, it's just, it's kind of philosophically different from SSTT or some of the other Redgate products in that those work in a state-based world where we compare the state of the database, the state of the development, and we come up with a script. So this is kind of tracking one by one. Yeah, let's spend a little bit more time on that because there are the SQL Server Database tools, the Redgate tools, which ship in the box. Although you do, they don't just install by default, but you can go to the individual components and there they are. So, talk to us a little bit more about this state versus migration approach. In the state-based world. Are they complementary or do one or the other? They're kind of opposite. Redgate got started with SQL Compare, which is a schema comparison tool that is very similar to what's an SSTT. And in the state-based world, we make a bunch of changes just like I might do in C Sharp or Java or something else. And then I take those changes and I compare those changes with what my production database looks like. And then I develop a script kind of in real time, a dynamic script. So kind of like the things we would do in application program, it's kind of like runtime versus design time. So in the state-based world, you do that and that's fine. And it works great for so many people. People use Redgate SQL Compare to do that. They use SSTT to do that and it's great. The problem is there's a domain of problems that you can't solve with a state-based comparison. So if I rename something, how do I know I renamed it? Is it a drop-in ad or a rename? If I add a not-no column, there's data manipulation involved. I can't solve that problem. So there's a class of problems that state-based comparison doesn't solve. In SSTT, people use pre-post scripts to get around them. With Redgate 2s, we'll use what we call migration scripts to get around it. It's just one way of development. In my past, I've worked in Oracle and DB2 world and a lot of those larger systems, older systems have worked in the migrations world where they go, every script you write, every change you make to the database, we track that. So when I go to upgrade, I run script one and then two and then three and then four and then 56 and 57 and 58. And it's a bulletproof way of doing deployment but it can be cumbersome and time-consuming because the other thing is if I add a column to a table, drop a column to a table, add a different column, all three of those things run in production. On my development database with 10 rows of data, who cares? My production database with a billion rows of data, slightly problematic. So neither one is perfect. They both have pros and cons. They're just different ways of working at the world. Throughout my career, I've been working with SQL Server for 25 years now. What I find is that people fundamentally fall into one of two camps, either like state-based, like SSTT and SQL Compare, or they liked migrations like Ready Roll or Liquid-Based, Flyway DB, DB Up, various frameworks that do that. Is this a kind of a thought shift? As we've been talking about database compare, schema compare and SSTT and things like that. Is this a thought shift on how I think about working with databases now from a, you know, because we have all these people like, yep, no schema compare, I know SSTT, but now we, you know, Ready Roll, how do we get people, you know, start thinking down those? I think just philosophically, you decide, I'm going to develop one way or the other. It's kind of like saying, you know, do I mix functional programming and object-oriented programming? I don't. I kind of choose one or the other, and I have either some philosophical leaning or I have some requirement that says we should go one way or the other. And in the database world, it's the same thing. It's just, neither one is better than the other one. They both have pros and cons. They both have workarounds. Yep, but is one more of a best practice type of thing? No. I mean, there's advantage. So one of the things we love when I've worked in like Oracle Financials or large ERP systems is that when I do migration-based development, I can take any version of the database and upgrade it to any other version because I have all the scripts, right? So if I have clients at version two and version three and version four, I can give them the same set of scripts and get to version five and that's easy. In the SSTT world, I need custom scripts for every version, right? Okay. So there's pros and cons. The other side is I'm tracking all these scripts, right? Some developers hate this, right? Right. And some don't like the state-based thing because they're not confident that we'll actually develop all the changes and get them deployed, right? So it's not really a shift. It's an option. So ReadyRoll is giving us an option for people that prefer this. Just talking, yeah, just talking about things. Exactly. Okay. So let me make a couple changes. So we were in Visual Studio. ReadyRoll is a part of Visual Studio. It ships in the box for enterprise. There's various things you might want to do. Some people like to work in Visual Studio, so, yeah, it doesn't like that. Let's see if it'll work this time. Let's forget, let's do it this way because I'm a code guy, so I don't mind writing code. So let's create a new table. We'll call it channel nine and I'm here with Robert. He's an in and Scott. He's a Varchar. We'll make you a Max, right? I want to be a Varchar. Yeah. Oh, Max too. You're a Max guy, right? I'm a reliable type of person. Very kind of on a path and you're just flighty apparently. Yeah, so you're there. That's the takeaway I'm getting from this. You know, I've got this, I've got this table here that's got feeds in it. Let's add a new feed. So, it says feeds. And so one of the things we also ship with Visual Studio Enterprise is SQL Prompt Core, which is our IntelliSense version. And it's pretty good. So we'll add another feed in there. And then because we know some people like to work in management studio, we can always work in management studio as well. And so I can pick up changes here. So let's pick something in my development database here. And let's pick some stored procedure in here. And let's just pick this one. So here's a stored procedure. And here's one of the other things about SQL code that makes life difficult is this code is fundamentally different from the code I used to build a stored procedure. Because I would actually have to have a create here to build a stored procedure, but instead I need an alter. When I build a class in C-sharp, when I say public void class, that code always looks like that. Whether I add or subtract, the code fundamentally the same and here it's different. But in this case, let's change something. Let's actually do a best practice and we'll just pick up some of these columns. And rather than a select star, we'll do this and I'll execute this. So I've made a bunch of changes here now. So in ready roll, one of the things we do is we've added a pane here. And what this does is allows me to kind of look for any of the changes that I've made. And once I've done them, you'll see it's detected a number of changes. And when I import these, it's actually gonna add them to the project. And so what we'll actually get, and I'll refresh it to make sure everything is added, we've added some different scripts here. So those migrations are records. It's a record of changes that you have made in the database that are ultimately gonna need to be propagated or published or whatever the code is. Exactly. To the actual database. So this script, 17 here is my insert, right? We've added that in there. And my script 16 is that, well, the other table I think was somewhere else. Maybe it's in here still. But I've added these scripts in here. And so they exist as part of my project. And they're under source control, so, right? Exactly. And so what we're trying to do is treat this exactly like our code. So in this case, I've actually just got new code here that I've added. So I can, like anything else, I can add it. So I added channel nine demo. And I can do what I wanna do, just like I might do in Visual Studio, is I've got my board here in Visual Studio where I'm tracking work. So I can use this pound syntax to actually add, commit, and push my changes. So part of what we're trying to do with ReadyRoll is implement the database just like we did any other code and actually track this stuff in here. I think that sort of answers the question that we asked in the beginning. What does it mean to be database DevOps? Is that just as you make changes to an application and code and you wanna be able to manage those efficiently, you make changes to the database. Right. You wanna treat that the same way. Right, so we wanna add those in there. And so one of the things I've also done here is that we're actually running a build in the background. So I've actually got a Visual Studio agent running on its machine and it's actually running a build right now. So just like we would do with our C-sharp code or VB.NET code when we submit it in, we want Visual Studio team services to actually be running. And if I look here, I should see my build just succeeded, I think. And I've got all the information in here that looks like what I would expect. I've got test coverage running. And you can presumably break the build because you made database changes that are violating some rule or didn't quite work out or something. Right, I mean, one of the things we wanna do with DevOps is we're trying to pull all of our code together and determine very quickly if we've made a mistake. Right. Because that's a risk issue. So here, I can pull this in, I can run my build and if I look at the definition, this is just a Visual Studio build task. There's nothing special about what this is. Right, okay. I could have this build in the C-sharp as well if I was doing it. I might run tests. And in this case, I'm using the Microsoft Test Framework to actually run tests against the database. What does the build and test actually do? Does it take those scripts to like a sample database and run those and say, yep. That's exactly what it does. So the build will actually re-run those scripts against the temporary database and determine if they're valid SQL, all my object references resolve, all the things like that. The test is just a test like you would run for anything else, right? There's, they're written in T-SQL, but I set up a test and I say, check this value, run this query, see if the results match and go from there. And so once I do that, I just output an artifact and just like any other code, I've done that. Okay. And in this case, I'm actually, I'm committing out to a release process as well. So we're doing continuous delivery. So one of the things that we want to do here, and I want to say my build, it succeeded a minute ago, so I should see up here a release to integration that just ran. And so if I were to actually go back over here and let's change the integration database and let's say I've got that feed that we just added. Now I didn't change that in this database, or I didn't do anything else. And as a matter of fact, let me go further. Let's pop over here to QA and let's look at QA. Now that doesn't exist yet, but let's make it exist. So here I have the ability, I have these downstream releases as well and Scott will let you, Scott, watch me. So I'm not going to use the keyboard as well. We'll put the keyboard away. I'm going to create a release and I'm going to say release this. And so without using the keyboard at all, we're going to see this database change deployed to another database. All right? No hands, nothing up my sleeves. In a second, this will be run. And as a matter of fact, let's come over here and we'll execute this. I think it's deploying right now. And we should see that data change actually in here and we would see the other change as well if we wanted to go through that. Right. I think we're in the process, there it is. So here we are in a QA database, right? You saw me, I didn't touch the keyboard at all and we've deployed this in the same way that we do all our code. And again, when I look at this release definition, this looks like I would deploy ASP.net web app to Azure or a sharp or a mobile application or anything else. And in this case, I've got multiple environments that I'm deploying to. I've got these tasks that I've put in here. In this case, these are tasks from Redgate that allow you to do that and just deploy all my code. What's the setup time for, if I wanted to set everything you showed, what's the setup time that would take me to, you know, properly set up a DevOps environment properly? So, is that what you intended? Yep, so to set up the entire environment technically from the technical standpoint, we could do it in less than an hour. As a matter of fact, I do talks all the time where we'll take, we'll go from scratch and we'll import a database into a project and we'll set up CI, we'll commit it to version control, we'll set up CD and we'll do it. The cultural changes are hard to, right? Because everybody has to get used to understanding all the steps. Yeah, so I was asking from a technical standpoint, I think the other aspect, you know, the now getting me to use this process is the not the hour, right? It's months, it's probably months because we don't realize, I think sometimes, all the little steps that we make as humans when we're trying to deploy code. And we see this when I'm trying to deploy to Azure. If we take the sample apps and I deploy ASP.net to Azure, you know, I can use Yeoman or something and be done in 10 minutes. But in terms of getting all my code out there and realizing, well, I really need to make sure that I've set up firewalling and that I'm actually deploying this extra bit of code that's side to my project and I've got to config set. All those little changes are what take time. But that's part of DevOps because we're learning as we go. So we start to make those changes, right? Right, so where would somebody go to learn more about database DevOps? So we do a lot of work at Redgate. I run SQL Server Central. We do some work there where we're publishing information. And then I'm working on a course for MS Learning right now and hopefully it will be done in June, depending on my travel schedule. But at openedx.microsoft.com, Microsoft is publishing a huge series on DevOps and Azure and all sorts of languages, platforms, different things. And we hope in June have a database DevOps course out there that will give you more information about how you can work with state-based, with migration-based, with open source platforms or SQL Server with whatever you want. Okay, so the tooling that ships in Visual Studio Enterprise, is it a trial? Is it a starter version? Is it the real deal? Yeah, it's an untimed version. What we have there is a Visual Studio Enterprise contains ready roll core and SQL prompt core and SQL search, so three Redgate products. The SQL prompt and the ready roll core have a few less features than we have in our full product, but you can upgrade, but they're not trials, they're full versions that will allow you to do this deployment and development as much as you want, so get Visual Studio Enterprise. And then what is SQL search? So SQL search is just a full search product that lets you go throughout your database and find objects in a more intuitive and flexible way than what ships in management studio. Okay, I have to be honest, how many people know that these products are in because I had no idea? Well, it was announced at Connect, so if you were paying attention, you'd know. Thanks for calling that out, Robert. Well, it's amazing with Visual Studio 2017 that there's all these different workloads, and you don't realize sometimes that, if you don't get that mobile workload, you don't get Xamarin in there, and if you don't get the data workload, you don't get ready roll, and there's various workloads, so you should definitely look at the Visual Studio Installer and look at all the different workloads that are out there. 50 Visual 2015 and 17. Just 17. Oh, just, okay, that's why, because I haven't looked in 17 yet, I've done 15. Okay, so it's 17 only. The 17 has changed a little. Okay, I feel redeemed now. Yeah. One last thing I just wanted to talk to you about briefly, one of the things that developers always want to do is we want more data, we want to work on real data, right, because it helps us do other applications. And often if we just use the 10 rows of data that I feel like typing that day, I don't get a good feel for whether my application works with the data's in production or at scale or anything else. So Redgate's been working on a product that we just released called SQL Clone that allows me to make copies of full-sized databases and use very little space. So if I jump over to my screen real quick, one of the things we'll see is I've got a production database here, and it's got 300 megs, so it's not big, but for my laptop, that's decent size. But I built these clones and these are real copies of that 300 meg, but each one is only using 48 megabytes of disk space. And that 48 megabytes is fairly constant, even if this was a terabyte database. Yeah, okay. And I have the ability to quickly create a clone. So on this machine, I can create a clone and I'll say, we'll call this Scott because Scott's my friend. And I'm going to create that 300 megabytes here and in about five, six, seven seconds, I've got a copy of this database. And if I look at over here in my management studio, we've got the Scott database. How big was it? 40 megs, so I've got all my rows. Actually, let's take that top out of there. I try not to break things too often, but so I've got 1,000 rows in here. And if we look at this, Scott. And this is a full-blown database? Full database. So if I look at the properties database, this says it's 270 meg and 60 meg, but on disk, this has only taken 48 meg of space. Wow, wow. So we can do right now up to two terabytes because we're using VHD files. Very soon we'll have up to the 64 terabytes VHDX files, but we can make copies of those databases with roughly 50, 70 meg on your laptop and allow you to get access to that full-size database. And then- Are you compressing that? How are you getting that? No. Is that the secret sauce you can't? Yeah. There's definitely secret sauce. It's a little bit of magic and unicorn dust on there, but really what we do is we take a point-in-time snapshot of our database and then we have a central copy of that and we're all reading from that. And so it's a similar data virtualization technology that other companies do, but Redgate has brought it to the SQL Server platform at a very low cost and it allows you to give every developer copies of the real database. And if I don't like this database, I can delete it in seconds and I can create a new one. So if I've updated the data and I want to do something else, I can do a new one in about, what do we say, seven seconds and we'll call this one Robert. And I'll build another database in seconds. And then I can just go ahead and work with that and be ready to go. That is very cool. Cool. Cool. Love it. Thanks so much for coming on. Thank you very much for having me. So we've seen a good overview of what database DevOps is. I've learned a lot. There's tools that you can, that ship in enterprise, go back into the installer and go into the individual components tab and find them, they're there. Start playing around with it and learn. Yep. That's your homework assignment, everybody. Yeah, give it a try. Yep. Thanks. Hope you enjoyed that and we will see you next time on Visual Studio Toolbox.