 Hi there, and welcome to another episode of Visual Studio Toolbox. I'm your host, Dimitri Lailand, and I'm excited to be back with Eric. Eric, welcome to the show. Thank you. Thank you, Dimitri. Yeah. So we're going to talk about SQL Server Data Tools today, one of my favorite topics I've been on with Kevin. You've been on this show with Robert right talking about this, and today we're going to talk about the DevOps Pipeline. So why don't you give us some details? Yeah. Today, the main topic is about the SQL Server and SQL Database on Azure CI CD in DevOps Pipeline. So to get there, we are going to start with the database development approach, which is migration-based approach, which is more traditional, and state-based database approach, and then we will jump into CI CD. Most of this session, we'll go through, we will run it over demos so that they can be more exciting. Yeah, we love demos. That's the plan for today. Sounds good. So just to set the context for people a little bit more just in case maybe some of you aren't as familiar with all this technology and all the acronyms here. Yeah. So we're going to specifically look at how Visual Studio, let's say Visual Studio 2017 developer using the SQL Server data tools. Right. Can use all this technology that we have for the developer in Visual Studio to let them work with SQL. SQL is part of their source control, their team-oriented development, and specifically the DevOps Pipeline, which is all the stuff that you're going to use in a demo. Yeah, you got it right. Also, let's start with then what is the migration-based and state-based? What's the key difference? Yeah. So the key difference between those two approaches is about what is the system of truth for your database. In migration-based development, your live database is the system of truth. Then there. All right. All we got there. So state-based database development, your database source code is the system of truth. So let's get to the demo and take a look at what is really mean in the session. Awesome. Let's take a look. Okay. So let me switch to application. This is my demo application. It's called the BanchoWorks Directory, and let's put on a hat as an application developer today. Okay. Awesome. So we are not like professional DBAs or we are application developers. Sure. So what kind of app is it? Is it just being on this one? Yeah. This one is Azure Web App Service by using SQL Database on Azure. Basically, what it does is that it pulls down the employee information like a phone book. If you click up one of the employee information, it shows detail of that employee. Now, if you take a look at that, there are sensitive information there. So as a developer, I start my day by looking at my work. So if I expand it, then the task for today is that make a mask sensitive employee information. So we are showing like a phone number and then the emails. So ask is mask it, hide it. Sure. Very common ask. Yeah, exactly. So in SQL Server 2016, there is a great feature called the Dynamic Data Masking, my favorite demo scenario, that you can really easily mask any sensitive information without making any single line of change in your application. So let's- The database handles all the masking in your application just gets the data back and displays it. However, we choose the mask to be applied. Right. Cool. So let's look at the migration based approach first. Ever since SQL language was invented back in 1970, database developer have lived in the world of writing and executing, create, alter, and drop statements. Yes. Then for making changes to your database. Right. Then emailing those scripts to your friends and the team to run locally. That's right. Until you release the first version, it's straightforward that you can do all the app actions. But once you have the first version released, to the public, then you have many users using your app and services day and night. Then the change like this is business of altering your live database. So let's take a look at what it is. So first, let's take a look at what we have. I will execute it against the, I'm connecting to my live database, which is running on local, which is my dev environment. So I connect. So you get SQL Server 2016 installed locally? Right. Right. I'm running SQL Server 2016 locally as a dev environment which is my currently live data. Right. I execute it. As you see, emails are all shown very clearly. Right. So the key thing is that with the dynamic that I'm asking SQL Server 2016, it's really simple code change like this. There's two line of code and I will execute it. Boom. Two errors. Right. So your database is like the stem of truth. Now as a developer, I need to understand what's in there exactly. Right. And then I can understand what is these errors. Right. Right now what message is selling us is that, the employee view has dependency on the table that I modify. Also, there are other objects that is actually using this table at the same time. So to figure out as a developer in the migration approach, either you need to get a great tool to figure this out, or you have to have a great knowledge about your live database already. Yeah. Or you have to ask around your friends and experts. Or keep trying to alter until you run out of error messages. Right. So that's kind of a typical approach. Let's do that in the state-based approach. Then let's see the difference there. Right? Okay. So in state-based, as I mentioned, I'm going to solution explorer. Everything is based on source code. That's your system of truth and not the live database. What is most natural way as an application developer to make a changes or creating a new version of application? Right. Let's take a look at, before we go jump into a database side, let's take a look at the application. So I have, this is my web application code, it's written in C-sharp, and there is a v-employee model class. Right? So let's say if you don't need this property, what you do is you delete it on the source code file. If you want to modify, then you modify the changes on your source code file. Right. That's what you do, and now you have a new state of your application, and then you build it, test it, and you install it. We're deployed to the web app service. Sure. So that's the practice. You can do the exactly same thing. Let me just to close this one, don't save it. If you go to a database project, SSDT database project, which is all state-based, then you do the exactly same thing. All your database objects are captured in the project as a source code. SSDT is smart enough for those that maybe haven't tried it in a while. I mean, it knows the relationship between it. It really is rich intelligence, friendly environment. It's part of your source control, part of your project, and get latest on your developer friend next door to you. Next desk over will get the changes you're making here. So it's a great way to collaborate, but just listen to challenges you got to work through. Yeah. So let's make the same change like altering it. Now here, likewise, you modify the class in your Shisharo Java. In state-based, you're not dealing with alter business. It's always your definition of the table, which is defined and created. You can always look at your table in whole holistic view. What you need to do? You make a change. It knows you made the change. You're deploying it. It will alter accordingly. It doesn't require you to create alter statements. Exactly. So just to declare the state where your table should be like this. So in this case, I made a change, exactly the same change on the definitions up and I go build. Okay. So first, we showed the ad hoc capability of SSDT from Visual Studio to run SQL code. So you ran some SQL code just to demonstrate that that wasn't as straightforward as you find it. Now, you're doing it more of the way we would recommend people do this in this approach. In state-based. Yeah, in state-based. Right. So the build was successful. There was no error. Then likewise, you're installing app, but you can install or publish, if you will, to your live database, which is a target. So like your Dev database, let's say your common Dev database. The concept is that you're making your target, whatever it is, to the state of your source code that you just developed and verified. That's basically the key concept. So to make it easy, I made up the published profile here. So I can just double-click that goes to my local database and I run it. I'll turn it over here. I love the fact that you can generate the script if you can't. Yeah. Let's say you don't want to run it until you look at what the changes are that it's generating. Exactly. I'll show you that one. Yeah. So error. Okay. Let me click. We should probably we have to do it again. It's a data loss case. So just a second. It's trying to protect us. Yeah. So what we just showed here is actually, when you get an error like that from SSDT, if you're confident that the changes something you want to go through, especially in a Dev environment, right? Yeah, I see. In production, you would think about this a little bit harder. All right. So you want to know what happened? Actually, this is my fault. I was kind of a prepping the demo and then practicing it on my local machine, and I actually made the really dangerous changes at the same time that could cause the data loss illustration. Yeah. So it actually, SSDT detectives smartly and stopped it because there is a possible the error of losing your data, right? So if we go to the publishes setting and there is a one. Yeah. The blog incremental, yeah. Blog incremental deployment if data loss might occur. It was checked. So it was basically blocking it. Yeah. That's the default setting, right? That is the default setting. That is the most protective. That is right. So I was not expected. So that's why I was, oh, what's happening here? But that's actually a legit feature and it's a great feature that you can, we can always guarantee that you don't have a data loss. And at least you have to, like me, you have to figure out, oh, wait a second, wait a second. The data loss can happen. So I have to check it out. So my change was just changing the column size here and there. Yeah. That was my experiment before this session. So that's what happened. So that's it. We don't have bugs, folks. We show features. That's features. Just say in the demo. Demo. It wasn't unexpected, right? Unexpected part of the demo. Unexpected. Right. So let's run it again. So actually, it's another demo of data protection. I wasn't expecting. So I was a little bit confused. But yes, it is a part of the SSD feature. Now it is successful. So let's go back to our script. And I love the fact that it shows you the various states of published rights. You can see what happened before and what's succeeded now. Exactly. I always find it irritating when the tool kind of takes away the previous state. And you're like, oh, what did happen? You know, maybe you can email somebody about that. Even though it worked for you now, OK. So that's great. I will show you that part after seeing the result. So now I'm running my query as a suspicious user and see what happens with the dynamic that I'm asking. If you take a look at here, it's all masked. Right. So the data is still there. But SQL Server is just taking one of its capabilities and masking, getting you as this user with this level of access, with this query. That's what you're going to get in there for. An app could get the same data. The same hair close works. And the index is defined on this field. It all works exactly as is. That's the dynamic that I'm asking is the great feature. So let's see then how it worked. Before, with just the simple auto statement, it was not working, right? But with the statement, state-based, it worked. So let's take a look at the preview, what happened. Basically, the SSTT detected that the email address table has dependencies on the many others, like employee view has a schema binding here. You see that it will make it bigger. It has a schema binding. And then the employee, the view itself, has a dependency with many other stuff here. So to make that simple change work in migration way, I will show you the other script. And so before we showed you the preview of what the changes were, that's why there was no coloring of the intelligence. It wasn't code. And here's the actual code generated. Exactly. The other one is just a summary report, right? And then this is actual code that ran against the target database based on your state in project, right? So a bunch of this code had it to be executed to make those simple auto statements successful. So using SSTT powered by DAGFX, it intelligently figures out all these dependencies and schema bindings and index matter. And it generates, produces the necessary physical scripts when you deploy your changes to target database. So it depends on your target database version. It can be a different version. It has different content in it, like in terms of schema. And it produces different schema that changes your database to the state of your source code, which is your system of truth. That's a state-based. So we just covered the basic, right? That's the difference between migration versus state-based database development. And the state-based development is very natural to application developer because that's the way that application developers change their application code. So if you're changing C-sharp, that's what you expect. You wouldn't expect anything else kind of as a process. You would want to just have your source code be the truth. And your published version is just executable. Database, in this case, is being treated as an executable sort of speed. And this is the way that I worked with SSTT and a couple of projects that I worked on last. And it works great, even in a large team setting. So we had like 25 devs. And everybody could just get up and running from get file, get latest version from source control, do this sort of change to their local database, even if they want a vacation for a week or two. They weren't asking for scripts from us. And then we can actually leverage the script to change other environments like the common dev database or whatever. So this process, I can testify to it works really well. First-hand experience. Yeah, I totally agree on that one. So now, we just recovered the basics, right? Let's jump into a CI CD pipeline, right? Database CI CD. Nowadays, the continuous integration and continuous deployment is common practice for any serious application development project. Everybody just uses it. It became the standard and then something that you must develop. If you develop your database in state-based using project, then it naturally goes to database CI CD pipeline. Because there is no much difference between your application CI CD practice versus database CI CD practice in this matter. That's main reason that I showed you application change first and then showed you the database change. It's basically the same, right? Yeah, it's just CI CD is the automated version of what we just demonstrated. Exactly, exactly. So let's take a look at, then what you do is that in application development, what you do is that you make a change and then you check in. So let's check in. So I did it in a channel nine, VS Toolbox demo, I will just write it. You just broke all the rules around how do you comment your check-ins, but it's okay. We'll let you get away with this one. Yeah, for this one I will just take a little bit. It was kind of a demo error, right? And this is the main change. And what you do is that you can commit. I will just do it, come it all. And now I have one commit so I can push it. That's common practice for any developer does, right? Commit it and push it. And also on the source control, you create a pool recast and we're going to skip that part but that's the same process that you do on database development using projects. Yeah, it's not specific to what you're demoing. So totally get it. So now I checked it in, right? Yeah. Then if I go to Visual Studio Team Services and build, then you see here, the new build is automatically triggered. It's now happening. The good thing is that Visual Studio Team Service has a great CI-CD feature and it has all the database CI-CD feature built in as well and it's greatly integrated with the Visual Studio. So by just applying the same concept of using the application CI-CD using Visual Studio and the Visual Studio Team Service, then it just flows through the database development as well. That's basically what I'm demoing now. So the new build is triggered automatically and it's just running. So while it's running, it would just take in a couple of minutes and let's take a look at what is in there. So for somebody that might actually not be familiar with what we're demoing here, this is Visual Studio Team Services. Visual Studio Team Services. Yeah, so we're in that portal as we can view some of this data for Visual Studio, but then we can get the richest set of data for the portal itself. So let me then explain a little bit for people who are not familiar. The Visual Studio Team Service is like the source control repository and then the command center to run the CI-CD pipeline and all those goodies, right? So that's how I understand it from the database developer perspective. So if you go to code, you saw all the code that is running on the Visual Studio, but the source is basically on here. It was on the pull request. So it was all the source code is here. So if you're running in a team environment, then everybody can just clone this repository to local, develop it and check in. That's what you can do. There is no difference between any other application development and then it has the flow of a build and release and the flow. Those are mainly two things that we are demoing today and that we have those. So that's kind of a very, the nutshell, high level version of what it is. So let me go back to- How I build succeeded here and it shows- Right. So this one was the one and it already passed, right? So I will quickly show you how simple it is to just have CI build going with the VSTS, SSDT and Visual Studio. Great. So the guest source is just the common template. All those tasks are defined in template like this, right? So for database development, what you care about is that this is one, these are all the common steps that you do, like you're defining your location of your source. And if you have an application running well app, in my case, it has a Nougat dependency. Right, has to do store any dependencies on the server side through the build. And this is the key thing. SSDT database project has a built-in support from MS build. So you can just build a project using MS build. So it's nothing special, installing additional tools is just part of the build process. Yeah. So you can just, you know, any other app application in a build, application build, you can just, you know, a space by what is just the solution name. And that's it basically. And this one is something specific for SSDT. If you define this parameter, I will just make it bigger. Then instead of using disk for temporary, the artifacts, what it's kind of building, and then the analyzing your data source, database source, it uses memory. Do you need to add this parameter or is this something you're adding? It's optional, and it makes things much faster. So you optimize the, you made the build faster, but you manually went in here and added this argument. So if folks don't add it, it still works, but it'll be slower in the build pipeline. Yeah, that is right. And that's basically, and this one is also a common thing that after the artifacts are created from the build, then you're basically publishing or copying those artifacts to the server so that later on the release process can get those artifacts and use it, right? So it's a common practice for, common steps for any application projects, right? Okay, that's basically it. And you can see whole history of your build operation for yourself and your team members and everything. If I click one of their build now, it runs, right? And if you take a look at the deployment, the key thing is that whenever there's a new build successfully finished, then continuous deployment occurs. That means it picks up the build artifacts and then deploy it to your target location. It can be your production. It can be your test, anything like this. So in this pipeline, two steps gonna finish successfully. It's gonna build it all successfully and it's gonna push it all successfully. That is correct. If I go to the release, then probably it's already finished. And I will show you. So I had the setup like a workflow. In this workflow, what is that? First one is deploying that database to your acceptance test. Let's just go straight to production. I mean, what can go wrong? Yeah, there is a, it's more like a safety net to have a test in a deployment and then have some integration test or the usual acceptance test. And if everything is good, then we just see your team service has like a sign off process. So if the authorized person can go and sign off, then production deployment occurs automatically as well. So that's basically the good thing about that. If you're not building a demo app, you want multiple steps. That's the reality. Unless you're completely crazy, but I've seen all things. Deployment is even more simpler, actually. So I have a two task, but you can choose one or the other. We just see the team service has a task built in task that is called a deploy address equal backpack. Backpack is the artifact of the project build. So that was the output of the build process. And you knowing that that's part of something you wanted to get released during this pipeline, you went in here and I'm assuming you had to manually add the task, but it was a template that you already had. So VSTS knows about this kind of task, but you still have to configure it. Something people need to be aware of. Right, so configuration is a very simple. I will highlight this part. So if you take a look at, because it's a deployment to a SQL Server, Azure SQL Server or SQL Database on Azure, so the key thing is the connection part, right? So these are the connection strings and your admin. The good thing is that for password, it's protected. So it's protected through variables. So if I go to variable, you can define your variable and then actually hide it. Yeah, they're in demos like this. Nobody sees your password. Right, so that's a good thing about that one. And then the rest of them is pretty good. They're simple. That's the key thing for your specific environment. And the rest of them is the same thing, just in the location of the backpack file. So it's kind of a template. So for database project, probably this is kind of a same for all the projects. Right, so that's as simple as this. That's it. So when I was preparing the demo, wow, it's really simple, right? Yeah. And if you want to do it in command line and bring in your own version of a DAC FX or SSD version for your specific need, or you want to host it in your own private agent, then you can run the same operation using command line. We have a tool, command line tool called the SQL package. Basically these two tasks are doing the same thing. It called the SQL package that you see. But in this case it's more like you have control. Yeah, so if you have really extreme scenarios or things you just don't fit the standard template, you're not blocked, you can still set up this pipeline, but you have to know how to configure this command line based tool. Exactly, cool. All right, so let's go to our application. And it was the state, right? Before the change. And if I refresh it. There you go. Now it is a new state, which is a dynamic data mask on emails. Without changing any application code, it happened. And without going through all the executing all the statements to your live database, you just change your source code file, right? Yeah. And? Change your SQL, then your app reflects it in your Q&A environment in this case. Exactly, use your acceptance. So this is the power of adopting state-based and the database CI-CD pipeline in your DevOps. Right? Yes. So that's basically the key thing that I wanted to demo today. And as you saw, they're using the SSTT and state-based, then you can easily, quickly, and efficiently adopt these DevOps practice to your current database development process. Yeah. And I call it modernize. The definition of easy, quicker, and efficient. And the good thing is that, note that SQL Server is only RDBMS in the market that supports solutions for both migration-based and state-based, right? And migration-based approach, there are great tools that can help going through those alter the coding and the execution business. And Visual Studio 2017 Enterprise Edition, the RediGate ReadyRoll is now included in that Visual Studio Enterprise Edition. And it's a great tool that even it can enable CI-CD pipeline as a part of your development practice. So at the end, it is kind of your choice which fits best, right? We, as Microsoft, we provide you which choices based on whatever your choice is, we provide you full support on that one. Right, so if you're just a customer of Visual Studio, any edition, SSDT is there for you, for the state-based. And then, if you're an enterprise customer 2017 or if you own the license of RediGate for your own purposes or some other potential tool out there in the marketplace at some point, it just, it's going to be integrated for you for those solutions. Right. Cool. So if you haven't tried SSDT and Visual Studio team service-based DevOps pipeline CI-CD, then try it today. SSDT is fully supporting all editions of Visual Studio 2017. Today, you saw that I demoed it on the community version of, community edition of Visual Studio 2017. Free version of our product. Right, exactly. And VSTS is also free for small team with the five users and the great unlimited features like repo and the work item management that I started in my demo. And it has one private agent that's great and one hosted agent with four hour use per month for free. That's awesome. It's your flexibility and it's free. Yeah, exactly. And then you tried it out today. It's all, you can just start without adding any cost on your side and then if you like it, then you can go into a Visual Studio team service paid version for your real team project and kind of stuff. Yeah, awesome. So you've got some resources for folks to take a look at here. Yeah, I have, if you want to find out more about what is DevOps, there is a great keynote by Brian Harris. Shift left the concept as part of the Visual Studio 2017 launch keynote. Yeah, very fresh video, very recent. Yeah, exactly. So I have listed the useful references here. So you can just go and find out more about, all about SSDT, all about the database CICD. And then you're gonna have a database CICD tutorial posted on my blog. So while you can go and then follow the step by step, then you can do exactly the same thing that I did today in your own environment. And if you're a Mac and Linux developers, then the CICD part is not there yet, but we have a great tool like MSQL extension for VS Code. So you can get started in developing your application and database on Mac and Linux as well. That's awesome. And we're trying to bring the goodness to every platform where we have developers and VS Code runs everywhere. So that's awesome and Visual Studio and Windows has all this already up and running. And I'll be sure to put all these notes into the show description so folks don't have to worry about memorizing that we're on URLs here. We'll get them posted and thank you so much for being on the show. Thank you for inviting us. Well, we'll be back with more on the next Visual Studio toolbox. Thank you very much for watching. Thank you.