Using Azure DevOps for Microsoft SQL Databases with SSDT

[Music] [Applause] [Music] [Applause] [Music] hello and welcome to another episode of data lounge where we try to take a more relaxed and fun approach to date I am your host Alejandra Leguizamo and we thought with us today someone absolutely amazing cameo here he's the man the legend Camilla the large thank you thank you for having me how you doing today oh good good relaxing perfect so how many where we're going to be talking about today so yeah today we'll be talking about the DevOps approach and the DevOps culture but mainly we'll focus about the azure DevOps tool because currently is the same name yeah and it will focus how to build and deploy database with our DevOps tool okay excellent so we're talking with databases probably one of the challenges we're talking about DevOps in general is if we're data people not necessarily that may be one of the most natural things for us to do right because many of the operations related to the rocks in many cases are mostly centered on traditional development writing c-sharp basic Java but not necessarily for us more sequel type database relational people so it's great we're gonna take a look at that yeah because you know normal application or web application or something like this so they are stateless yeah the database is not so that's that's the the challenge that's one of the challenge with this so okay let's let's have a look on this so from my experience what is the normal situation in maybe not most of the company but at least you know from the market that we know that at least 50% of 50% of companies are not using the the DevOps approach for the database yet so it's it's it's time to change it you know so that's why I'm doing this session so the current situation is like this so the the the company is having the environments like production pre-production uit develop environment yeah so and also they have some tcq speed do you know with with this database whatever it is it might be you know per table it might be one big script to deploy a database and sometimes they even don't have the repository for that code okay but okay let's assume that they have okay so this is the the situation at the beginning we would like to change this approach a little bit okay okay so how how to do that okay so at the beginning would be good to have something like something what is called SS DT I will explain it in a minute so let's say that we have the database project as a GT project in our Dietrich poetry okay okay and then to do this we can import our current code on current database from the server okay okay so we can import the database from the the best the best environment is obviously production environment because this is life environment this is you know the most important version of code the other environment can be different one yeah okay when we import that so we will create the database project in our visual studio okay okay yeah so so what is the SS DT basically so acidity is the extension acidity is a free tool that can allows you you know to create the database project can maintenance all the objects with this with Visual Studio okay okay so that database project will contains all the things like schema like stored procedures like tables and etc etc everything what is in the database cope okay and this was my understanding is that correct me if I'm wrong but in visual still 2017 we had to download SDT separately but I think now in 2019 is part of the data workload from Visual Studio right that's true so so even now I mean currently it's a not separate product okay previously as you mentioned it was you could you have you had two options basically you could download it as a separate extension as a separate package okay all you could use the visual studio to install this as a one of the module yeah I come they toss beta storage the data started okay it's basically as you can see here on the slide it contains secure server data tools this is our SS DTS is our aces yeah yeah exactly so yeah in Visual Studio 2019 you don't have to basically you can't install as a separate tool you just need to you know add in this module here okay that's good and then you will be able to create database bit easier okay yeah so okay what is our next step when we install everything we need to basically create deployment pipeline with with other DevOps so let's say I will show you how it works on using this def environment first yeah the case we should do it in in specific order obviously so when we have our database project in our git repository for example I to be honest it might be any other reposed to you as well yeah I guess I will show you that in using the d-triple-c as example so when we have this we can build something called in our DevOps build pipeline okay so our a beam pipeline built our project built our solution and as a result we will get the duck pack file okay okay I will show you that during the demo and with this duck pack file another pipeline which is release pipeline in other a DevOps will be responsible to compare this duck pack file which contains all the objects of our database project okay to our target secure server or target secure database so we're going to be building two different pipelines and probably for for some of our viewers it is a little bit confusing right if you're a staff person so it might sound like other things you might have seen in in in our data lunch but this is two pipelines one to build the code yeah and the other one to actually deploy it release it correct exactly there's a to Pintrest yeah you are right so it might be at the beginning if if someone just starting with the tool might be a little bit confusing okay yeah you're right so the big pipeline is responsible to create the the duck pack file which is exactly the same action like we built in our visual studio okay this is very important okay and release pipeline is responsible to te use this file okay which has been created at the as a result of the B pipeline this is we called it as a artifact okay and then we are releasing deploying publishing this to the target of a target to the target database or target server okay so yeah so this because as I mentioned the database is not stateless yeah we need to compare because we don't know what is in the target so we want to deploy only incrementally what has been changed from the last time yeah so that's why we're comparing this server and the database yeah so in this case the the release pipeline comparing those two things and then as a result its generate the script is generate tcq script okay okay and this script basically contain all the changes then we need to bring to the target server okay good okay yeah yeah so the last step is to secure the script okay okay understood yeah good so what we need to do at the beginning yeah so when we have this database on production server okay so basically we need to create a new database project okay the database project and import the database to our visual studio okay I'm probably this is one of the interesting things for for our viewers is hey how do I get this started right yeah you already have my production database well it's not in source control right and we went to star in that process so this is exactly what we need to do this is the recipe that you should follow of course we're not saying doing life in production test first make sure you get acquainted with the to person but double this is these are the steps right this is what a very experienced people like like meal do exactly when they have to face this you go you start these exactly this process with these tools so always please test first yeah absolutely yeah so you know you can import the database from the production maybe not always you have the art direct access to the production this is the separate story but yet the production this is the right version of code we want to import because you know very often from my experience or all the other environments they are different they are differential a little bit between them yeah so there's always the case ok during this demo I will show you how to create empty database and how to import this directly from from the server ok this is one of the one of the one of the way how we can do it ok ok so I will create the empty database first ok then how it imports database on the server ok I have my local server so it would be easier to connect an input and then in our DevOps portal we will build the new build pipeline will run this build ok to create our artifact yep and check that the build basically works the database compile ok ok and then we create the release pipeline ok to deploy our database a new server and I will deploy it to the today to the perfect because that I should have up spot looks quite B so let let's run to it ok so let me switch to my view to a machine okay so I have Visual Studio opened ok so let me create new ok maybe we'll try this way so I have the secure several object Explorer here yeah ok so this is I can connect on my local machine so my local server I have this already and I will import this database adventureworks DW ok doesn't sixteen okay perfect so when I right click on the database name I can create new project basically it will create a new project and automatically imports their whole code ok for all the objects ok so lets me rename it adventure works DW ok my repo will be different will be this one okay this one i'm sporting all the settings and etc and also there's a folder structure by default this is quite good because you have schemas a slash object die so I will show with it in a minute ok let's start it I hope I think it works did I click this yes I did yes seems to be doing ok so now we just look creating the new project the empty one and in the next step you it it will start importing all the object from directly from the server yeah so it's very important that we need to have we to have the access to the database yeah ok and also need to have appropriate rights to read the metadata to read the definition ok as you can see this is simple database so there's not too many objects in there and as you can see it's finished so so we can check the report if we need yeah what has been imported ok let's click finish this and as you can see here we have type of objects like this is the database triggers and this is the dbo schema this all the folders but this is the structure of the project yeah so we have object types like functions tables etc and if we have a look on the one of the file you can see there's one file per object one fiber all right ok excess when we working on something so it's quite clear what we are doing and what we are changing yeah ok so let me close this so as you can see we have definition of the table and also we can change this table with this design note ok ok so all the files has been added let's double check if we can build this project ok rebuilt ok rebuild succeed great it's quite straightforward ok ok so let's push our code to the git repository ok ok so now we have the solution not waiting visually studio yes right yes and now it's not in our machine we ran our machine we see the code we see we finally know exactly how many tables we have and how the code looks like and the next step is now publishing correctness and push to the to the publishing key is the step when we are publishing to the to the target okay okay so let me put this here I already configured this one so let's call it like you need commit okay and calm it all and push okay okay everything's good now let's check in the portal okay has been pushed here is handy for Saturday so let's check this in the portal if we have any code in here okay our code in here yeah so this is exactly the same what we have in our visual studio it also creates the folders here yes everything will be exactly the same code that you have okay so obviously you control what you're pushing together to get your poetry on any other poster yeah but most important thing is that we have the folder the solution and folder there should be the superior project which is the database project addictions braces the SS DT project preceded by yes okay cool so we have the cult interpose dream next let's do and build the create build pipeline now we create the pipeline that actually builds the code itself yes when I compile the code and create the duck pack file I created that backside relief for our viewers that compilation that necessarily will generate the DLL or anything like that that generation will generate backpack me didn't it a couple more DL else but we don't really work with those yeah we don't use the DLL but there's a duck back here is the counterpart of the DLL the equipment you know that yeah content of the application dll's yes but the duck pack file is the normal is if you change the extension you can unpack this fire and see what was inside see the contents of the duct I would describe everything with what you have in in in the database and not on there's much much more okay perfect there's no data there there's no date okay let's plate the pipeline so so let me show you what's the pipeline so here we have all the pipelines so we have as you can see here we have two type of pipelines so firstly we are interested in create the big pipelines okay so let's do it so I don't have any pipeline yet okay that's great no one okay I'm not interesting to using the Hamline cetera I mean I will use the classic editor okay and yeah and now we need to choose where's our source okay because we have our repository and we'd get so this is our repository okay and we will use for this purpose demo purpose I will use the master branch okay okay and okay I will use the template right now the dotnet desktop template okay just to speed up the process but as you can see I don't need some of these steps or are we going to remove it obviously I need to build solution okay I don't need this one so I will remove it as well is it too few steps on me yeah so my first step is to build solution exactly what we did what we doing with our visual studio okay copy the files to the specific folder and the working folder and then publish artifacts to the drop artifact this is the only the name yeah I will show you how we use it in the next pipeline okay good everything's fine that's too much complex yeah might make it worse in a bit but so far it's all good yeah okay so what we have here basically we'll be building all the solution files with the SLN extension okay have in our code obviously in this case we have only one which is good and thankfully you can we can leave all the default parameters variables etcetera here so we don't need to change anything and okay so basically our first bid pipeline has been created let's change the name Wow that was easier than I thought yeah it's nothing complicated if you knew the path yeah you did when you see it for the first time right it has a lot of options and a lot of steps on the two different ends of family straightforward right now that was my goal you know the present is in very very straightforward way okay not complicated obviously we can't complicate it much much more but that's great okay so I can save it now and I can save and q IQ means that we start and that we run that we begin around the the agent locate be responsible to build it for us is correct I don't want to push in the comments and it's a time-saving my pipeline good and now it should be building let's have a look yeah okay now the port is looking for the first available agent okay so behind the camera settings they're not like aliens a lot of much change waiting to actually run yes yes so in this case I just used the default one which is in agile provided by Microsoft okay so currently we're using they defined virtual machine was the agent instance style with all the required libraries more than basically ready for us yes it's like you don't have to you know prepare and spend a lot of time to preparing and installing all the code modules answers and cetera okay everything is already here right okay obviously you can also doing the bill or or lease pipeline you can add it all some additional modules if necessary it's it's okay but as you can see here now the agent is basically compiling our code okay creating a director like being it's great were able to see like what going it's not simply a process that we trigger somewhere and that we are like okay I don't know what's going on with you see is spinning wheel but that we see actually what's happening yeah exactly and also is I think is recorded so you can back to this look later on okay yeah and okay try to investigate if something happened if bill doesn't work or something yeah so it's it's great tool because you have all the log here yeah good all the information so yeah I would first build succeed okay just good good perfect it's all green green is good yeah Green is good okay good so what is the next step so the next step is basically to let's let's double check what has been created here okay let's take a look in our build so there's some files yeah and this is the file that we'll be interested in yeah Oh mainly where's my duck back there's my black book okay weird interesting this fight only that one okay okay cool okay let's create an release pipeline now we do the release pipeline oh yes that's great no one so do I have something you know looks like I have something please okay oh sorry did is something to do not this release despite oh that's right okay yeah new one from a scratch yes I have one previously because I've created in one so so let me let me change the name in a minute okay so we are creating the completely empty job yeah I wanted to show you how to do it from the structure okay so normally we should you know release the changes step by step I mean step by step in appropriate order to our environments like and F maybe we have another one like you ATO test and pre-production production okay when everything who would be perfect and tested in the you know previous environment we can promote the code to the today higher environment okay so let's name the stage the environment like death for example so this is the owner's name and yeah we need to define where is our artifact so they artifact exactly that page you mentioned before the artifact is the result of whatever we did on the built by in the grid plan exactly exactly so remember the drop name for already yes so we find it here okay so this is our project yeah and our build pipeline sorry yes build pipeline was this one yeah okay okay so I'm just choosing this one yes I mean sure I think always latest version okay okay yes and the audience is the audience only in this pipeline okay so I just leave it it's it's it's really interesting how it simply links everything together yes oh the previous components definitely much much better than it was few years ago okay when people so company using the other third-party companies tools yeah but now you have everything in one tool I want that yeah okay so we have this we are using this artifact here yes that has been created before and now it's time to add some tasks you know release for our agent so this is our agent this is definition still as I mentioned we are using the other pipelines but there's no problem to create your own virtual machine with with agent okay system one okay all the problem but let's not complicate this and okay let's add a new task okay so if we're talking about tasks we can use many tools many proper tools and scripts from also marketplace ok but I will use the the one that was created and prepared for Microsoft here okay Altaria pure okay so let's add this task basically it would be only one task in our scenario of you okay okay then we need to configure that okay okay so we'll be deploying deploy or publish our data to us advanced works advanced works 2016 yep and I'm using my subscription not this one sorry this one okay how many doubt rise six okay I hope it works and then and the next step is just define what is the our target server and database okay you would like to use it so okay and secure server so let me back here this is my server good so I can use it directly you know pasting the name or putting the name here okay but I will show you how to use the variables yeah okay okay so we can have levels here yes see here there's another tab where we can define variables and what's fun here and very useful is that you can define variables for all pipelines or some specific variables can be defined for specific environment or specific stage okay yeah so let me show you that I will I will add something like secure server and put the value for this here okay next one we need it would be secure login and secure password okay okay let me paste my login and I don't want to paste the password I mean I will paste the password here but before I do it I change this property here okay so my password will be encoded so no but you can link or connect to Camille's server so notifications no passwords here it's Joe you can see on the part of this password so okay let me pass it here yeah perfect so we prepared three variables for our pipeline okay let's use it then okay so how to use the variables so the variables we can use it like this oops oh yeah it was secure server this is the server okay okay my database okay I'm not using the database here okay at the venture works and careful yeah I'm using the same server okay but normally per environment you should separate separate it yeah yeah but because I'm using only one so I will use this data warehouse I'm not sure if I don't have anything before so I've just changed the stranger okay okay good and so here we use the variables that we created a couple moments ago again yes and another one for password okay good code and we are choosing the action here so there's a deploying type is the file because we are using the duck back five and our built yes okay and our action is published but also you can you can choose different different action you can deploy the report for example to have a look what will be changed what would change script will contains okay okay so you can create the report for that and and that's it all you can create the script only not executing the not executing this script against the target environment I'll target server okay but we are interesting to simple thing step here to just publish to on to the server perfect last step is just choose that back file here okay so now we are browsing our our artifact so this is what contains our artifact yeah there's a structure like structure where the built pipeline put the Rosselli and okay so it's something it's not loading here come on element it's what happens when you're doing the most like what let's get a couple moments oh no it's too fast they try just about it sir let me double check if I have a connection something happened with the internet it's very slow time out okay now it's better should be fine my girls let me look at the screen again let's get a couple moments and let's see huh houston we have the problem that was good yes it been and release and in release we are interesting the puck perfect okay yeah cool okay during this demo I will not show you what is the publish profile etc just simple you know English yeah obviously we have more opportunities and capabilities to to control how the change script will be prepared and create okay okay good but now okay fine basically we prepared our pipeline so let me change this okay our database continuous deployment okay drive it the main folder right here so it's more you have it okay let's create a release 10 ok so we have only one environment here but obviously you can have more than marble for the basics is this one and now I'm not going to create this one because I know that we need to change something else okay in our database project what if so you change okay let me double check you okay we already built this project so we need to change one thing because as remember I imported my database from my local secure server yes I'm just on premise secure server so and also imported all the properties for the database yeah okay so my target platform is sequel server 2017 so in that case yes secure package or basically that the target script will not be able to be executed in the other secure database so actually okay good it will be small change in the database product ok so I need to push it to the git repository okay see it's a change therefore we just have yes again as you can see we can see what it kind of changes with how I look on this there is a small change yeah perfect was very important good so let's push it okay okay there it is yes so we have the changes we can check this in the history for example yep yeah this is my change there that was my last build a little I would like to create a new so we have the option to see us well hey what was the one that I actually exactly so what I have to do now is guilt and release on the tape done yes okay let's create a new bit here okay so I'm going to do this here and cue new bit okay this should be quite simply going saying create new one done yeah and also in the meantime I will show you how to set the property that allows us to trigger new built automatically ok have the appropriate continuous integration across integration branch yes how do we do that yeah so we need to go to the build and when we edit it yes there is option like triggers and we can define the trigger one of the type of the trigger is enable continuous integration okay and then from that perspective we can following one of the branches yes so when you push the changes to the specific branch in our case is the master now it will automatically trigger it yes okay great so in that way I will not curate it because it's already cute okay yeah so next time when they push it something automatically okay so let's check our build now it's finished still it's the working working on it you can check where we are okay it's building the script oh it's finished okay cool so perfect we have our second big success okay okay go to the release Python eyes now that's create the release okay we're interesting to my niece exactly same just new and create that's it yeah your create and yeah and the release has been cute perfect let's have a look what is going on and this is this is great as as a closing them or now that we actually end up with a very a very strong platform already set up right so this is the Speaker of the day now to close is we already actually have a repository with the full database project right yes we have a build pipeline in here and now we also have it configured so we have an actual release Bible and so this is actually pushed right now to our development database environment which is the one that we're doing right now yes yes exactly so we are pushing the code to the target server okay which is the other secure cell okay and because we have nothing in the tar I mean there's the secure server instance exists but we don't have that database yet okay so currently the secure package comparing the target server with our database project yes and the the tcq Spirit will contains basically the whole series including the create database it's in the creation of a statement oh yes absolutely first so we should be able to see it here in a minute mm-hmm I would be able to actually connect it let me do it yes before we go just to show you that this is not fake but going somewhere is for time we're gonna be able to see that database let me have a look on my PO Box yes very important multi-factor authentication very important multi-factor authentication okay all done and let's go to my server okay which is didn't want that one and you remember the name of the database is an auction word DW this the same the same concept that with the built right if you're looking for a magazine in the mindset of the world yeah who actually right now say okay put this work and do the actual deployment from there yeah so basically life you know looking at the lock which is producing by the agent okay so we are directly life connected to the agent and okay see what is going on there yeah okay we don't have to connect via remote remote app okay look so currently there's a new database I mean sorry there is publishing the date to database like this okay this will be the name of our database yes the server yeah so now the initially citing the deployment you thought it so it means in a minute it will be start you know creating and executing the whole script create gonna make it in the whole script okay but what happens if we already have objects there can you can you briefly describe to us what what happens if you already have things in that they can I mean yes in the target what happen so if we have the target in the target database some objects so only the changes will be released so for example if we added a new table or stored procedure only that new object will be in this change script okay okay so it's only the Delta it's only the deltas actually yeah perfect so it's taking a little bit longer than usual okay maybe Microsoft was doing something this is not Laura you should see Laura behind the cameras right now probably you've seen already our Twitter so you know you now know who Laura is she's good as one of those looks right now because she's like I need to go it don't come too much program go fast but Microsoft is not saying be so against a is is DPP more anybody from that team I don't okay okay we're gonna be finishing a one of those cooking shows where we show you the final dish so we'll be right back so has we promised yes we're back okay we have the cake ready and baked for you a scene any respectable cooking show okay so what happened what happened Camille what happened yeah we had some problems yes okay great some troubleshooting process but we're back okay let's have a look on what happened basically to the lock yeah as I mentioned we can have a look on the lock when something happened yes and I realized that I make a very small but important mistake so look on the server now here yeah Oh HTTP this is wrong yeah we need to put the server name without HTTPS here okay so in the meantime I create the the new pipeline the new release pipeline so this called adventureworks CD and if I edit it I just change this variable to the skill play it'll prompt out yes the proper secure server name okay okay let's have a rule around this yeah okay yeah great pipeline I've already did it for the test but also I dropped the database so I will be able to show it from from the stretch okay okay perfect go to the lock then okay we are connected to the agent again go looking for the available into agent now I hope everything's gonna work we did not have to go to another commercial break yes yeah so what's going on now is the agent is trying to find out this instance the server instance and you know realize if that target database exists on this server if not it it will create the database the empty database on the server yeah something really interesting there in that connection string for our viewers from home you remember we marked the variable for the password as a lock right like a secure one like a lock the the password yes but and here we cannot definitely see yeah that password yes exactly it's already protected there's you know the the portal knows that this is sensitive information okay value yeah so we don't want to see this text below not even in the lungs okay so you can rely on the release and the build pipe absolutely good so look so currently the database is creating good so after this step we will see the steps when all the objects will be created in this new brand new database okay empty database so as we say this is right now creating the whole database right it's a brand new this is the database right the portal I don't have this data by SIA okay okay good what's this now it should take smack Maxine another one minute okay or maybe less because it's quite small database just like about I don't know 100 objects in total okay so shouldn't be fine and this is the equivalent of that operation that we were doing from Visual Studio right click deploy right yes publish it it's exactly the same thing it's connecting comparing creating the script and target which mean executing the script against targets secure server against that target tickle server yeah so it's already two minutes next yeah so the last part it was it's very quick very last like five seconds let's just have a look yeah good on the lock again yeah so this is the tie everything's yeah everything has been created I mean database has been created and then all the objects has been created because we had nothing in this in this today and so I'll connect no to the database you see if it's actually real I will show you that yeah yeah exists good let me connect to it I've connected to the server let me refresh the databases and the database is here with exactly the same list of objects including tables procedures functions whatever you have in the database is where we can really they can scope yeah okay so thank you everyone for staying with us during this commercial break this is happen it was a problem if you were to be care we made a mistake we learned something but also we learn something and I think this is definitely very interesting from this and you and you mention it throughout your section is the importance of having Belloc's life right we're actually connected to a terminal we're seeing that terminal were seeing the locks and that's exactly what we're able to use to actually turbo shoot these so tell me thank you very much thank you very much it's a break it was absolutely amazing to have you with us and for the love you are watching this thank you very much we called you have you learned a little bit of troubleshooting and if you like what you're seeing and you like what you saw with rock stars like Camille don't forget to subscribe right below thank you everyone until next time thank you [Music] you [Music]

Channel: Data Lounge

Views: 14,706

Rating: 4.9159665 out of 5

Keywords:

Id: ObgY4XB0hHo

Length: 44min 13sec (2653 seconds)

Published: Wed Jan 22 2020