Unveiling the magic of CI/CD for SQL Server using GitHub Actions

good afternoon everybody and welcome to day session and failing the magic of cicd the SQL server using GitHub actions in this expert session we are going to unfill some of the magic behind the scenes so before we get started just want to thank all of the sponsors for this year's SQL bits of course it's not going to work because without all of the sponsors events like this would not be possible why is my clicker not working did you do something slight technical for everybody why come up work with me there we go so again thank you so yeah like I was saying with my response is a fence like this would not be possible so thank you so what are we covering then sander so uh for the next six oh not 60 minutes 50 minutes or so we're going to talk about GitHub and how you can use it to do your workflows um and your and use GitHub actions we're going to talk about how you can set up your branching module models to make sure that you just don't just that it confirms to you or yeah like your development process right then we're going to talk about if we're going to do stuff in the cloud or locally on premise and we're going to talk about Runners because Runners do all the work for us we're going to talk about the workflows themselves we're going to talk about Secrets because secrets are important don't put passwords in your actions or in your workflows because I will personally go to you and kill you because I've seen it happen so many times we're going to talk a little bit a little bit about unit testing because how many people do unit testing not enough but thank you at least there's a couple and in the end Kevin is going to do some really cool demos so uh I'm not going to do demos I'm going to be pretty and stand here and talk about all kinds of stuff so this is me I am Sunday I work for data masterminds I'm from the Netherlands I have about 20 years of experience in I.T I feel really old at this moment because I started when I was 18. uh I've worked in a variety of industries from agriculture to financial Industries to Health Care I've seen it all I'm a Microsoft MVP for developer Technologies means I do a lot of stuff with Powershell and with devops because that's stealth developer related I'm a major contributor to the DBA tools project how many people use DBA tools still not enough but thank you for the ones that raised their hand Go download deviate those it works great it saves so much trouble actually we help a lot with DBA tools at data management so if you if you want to follow me on Twitter that's my Twitter handle I'm not that socially active but if you send me something I will probably look at it I have a Blog and if you have any questions after this session feel free to send me an email I will try to reply as fast as I can right um there are a couple demos that I will be referencing this is my repo go to the presentations repository and I've got a whole wide range of demos there free for you to use to reteach Jason whatever do whatever you want to do with them and now Kevin has a slightly more complicated bio than me so sure go ahead Kevin so hi everyone yeah my name is Kevin Chan and I am a manager for a company in the Netherlands however as some of you can probably tell I'm actually originally from the southwest of the UK so basically I'm an Englishman he graduated 10 minutes away from here living in the Netherlands because he met a woman in Australia now if you kept up with all of that you are in a good place for this session so I've been working I.T since the days of Windows 95 and that's probably as much I should say about that and over that time I've worked in fairies sectors including the banking oil and health insurance Industries over that time I've gained various certifications like the two you can see on the screen here I'm also a Microsoft certified trainer and in addition to that I'm also what's known as a dual category MVP so I've got awards for both data platform and developer Technologies show off sorry I just have one so just like my co-presenter you can find me in a few places online you can also find me on Mastodon as well in a very similar handle I'm on LinkedIn have my own blog where I talk a lot about data platform and developer Technologies strangely enough and I have a GitHub site like Sandra does as well where I share some templates you can use for CI CD stuff cool that was it that was it finally so uh Branch strategies so when you install git it's always the main branch right but that doesn't work with us because we have to do a lot more with it because you have to separate death and features and all that kind of stuff so we're going to go through a couple of examples how we can do that and then I'm going to show you how to do that with a couple of the strategies um small thing what are branches branches are basically pointers to a snapshot of changes right so if you go from one branch to another it's just a reference to that amount of changes that we have like this you can just make new branches uh development features all these kind of things um but there are different this is really annoying it's not working so these are a couple of examples how we can set up our branching so there is and these are used in the field but you can use parts of these to actually make sure that they fit your development process you've got git flow trunk based ways of doing this and the gitlab flow so if we go into those this is the one of the first flow branching models that we actually had it has a branch for every single hot fakes releases development features and you go back up and forth and it has some pros and cons to that because it's a complete set of rules right it's completely documented how it should work very thorough very detailed easy to skill but because it's very detailed it's really complex and it's not very agile right so we don't want to use this in most development scenarios the next one is the GitHub flow guess who created that they actually don't use it themselves but so we get rid of all the other kinds of branches we only have feature branches and when and a feature gets tested I hope hopefully it gets merged into the main range and it will then be hopefully push to a production release with that it's really clear and simple right so the cons is there's no complexity there makes it easier for teams to make quick changes continuous deployment is a must otherwise it's not going to work speed comes at a cost right so it's not well organized you need something to organize this around otherwise it's going to be a mess some other cons harder to test for multiple features together so if you have a lot of development you have a lot of features going like this um it can be hard to do the correct test uh test and tests in your workflow so and the main branch is both development and production kinda not 101. gitlab Pro is completely different that's based on environments right you can play it on based on environments and then you have like your staging your prod and your pre-prod and then features if you want to have that separated but it gets so it gets tested in all environments really nice right um you know what lives in production production is the the thing that Maine is not even your production release it's only your production we have a question we think do we I know sorry is one person's arm behind another carry on so um and there's only one way it's like a water flow method so you go from one way and you just develop and then you're released um you have to assume that your main branch is free of Errors otherwise it's all the way down like turtles all the way down but then all your errors are going to be in every single branch no release validation and hot fixing so if you have a hotfix outside of all the other branches you have to update all your wrenches so take care of that some popular merch and strategies that we have we have merge fast forward no fast forward squash and rebase um with merge we have first of all this is a normal uh before the merge you have like you commits you commit one and two and if you have a no fast forward you create another commit and then it gets merged into your main branch or any other Branch but in this case the main branch with fast forward we don't create another commit we just push it right up to them branch with squash if you have like thousands and thousands of commits it can be hard if you do something like your if you use your commit documentation I hope your commits have proper messages right you can use that documentation in your releases your release documentation if you have like thousands that not that's going to be a hassle to actually make it work so we can squash it down to like one one commit and then do the merge after that rebase if I have only used rebase actually when I really screwed up a repo happened a lot in the beginning so what you do is you have your commits and you kind of merge it right back into the main branch so these are the most popular mergers that we can do this uh so some common applications anybody here probably knows about Azure day Studio official Studio they have great integration with Git right we need to get sem for it's actually doing something with it but we also have to get gooey who used to get gooey serious good I'd rather just use command prompt I'm I'm a Powershell guy so I do everything from the command prompt so just use any of the other tools right the the GUI for uh that comes with Git is not not very efficient so I think Kevin is going to do some demos I am indeed so now some slaves have been covered so who's working with get at the moment just out of Interest there's quite a few people says as a lot of you know already there's various ways you can work with getting your branching strategies so what may you know may not be aware of is that you can do it directly within GitHub so as you can see here I just got a standard repository if I scroll down load a code here so yeah that was done on purpose I want to use the keyboard everybody if somebody's just pointed out to show there's no smoke and mirrors in this demo so if you press the dot button once you're inside a GitHub repository this is what happens it opens up a lightweight development editor that you can use and from here you can do quite a few different things and as you may notice it's basically a cut down version of Visual Studio code so from here I can open up Explorer I can view the files I can make a change so for example here we can just change on there now you need to press save in this particular version now the saving is dynamic then I can see here for example and do that then do a commit and to help with some jargon here a commit is basically hardening a change to the git Repository and I can also show you how to do it with branches as well so I just create a new Branch quickly and I'll show you a nice experience of this so so go to create a branch and I just cut it feature there Put It featuring new so you can switch to the branch and the good thing about this lightweight Dev outer is that it gives you lots of useful prompts which is why I want to show you this now quickly so sometimes it can take a while to load looks streaming on our Wi-Fi connection so if I go back to the same one here and I just make change again and then from here just get the heart did it so from there I can then look to do a pull request as you can see the actual pull request icon is actually here once I've done the change in a new Branch so I can click on here as you can see it's got very nice user friendly experience for me to actually create a pull request so I can just hit on the crate here and this is where it gets very user friendly because as well as creating the pull requesting GitHub for you if I go down to the bottom here you can see I can actually go to merge the commit directly into the branch so if I click on here now like it for merge and then which will be done and I can delete the branch afterwards now that's how you can do it directly within GitHub because you can do it for other applications as well so for example if I go directly over now to terminal a minute so you can open it directly in Azure data studio and for this demo I thought as well just a little nice thing a very nice easy way you can open up a repository straight away using the command line and you can do the same with Visual Studio code as well just by using the code command instead so from here once it's opened I can browse through again so for example I have an extension stored called database projects this allows me to a few all the objects in the database project in a very nice way so I can go through and then if I want to make the change again and sync it up Etc and you can work from branches here the same as I showed you just now so that's it and back over to you so so GitHub Runners Runners are actually the programs that we all run to get our actions executed so they do all the work so they do all the processing of all the actions they run on Windows Ubuntu or Mac OS right all the os's so you can have a lot of choices in that they can be GitHub uh you can they can be in on GitHub in the cloud or self-hosted and the hosted image for GitHub is the same as for Azure pipeline so Microsoft actually did something he we're going to use the same image saves us a lot of trouble um the windows on Linux run on the standard dstv2 image so if you wonder what the performance is for your Runner you can actually look up the specs for that so it's about like two CPUs certain amount of memory for that to learn and and the Mac OS images oh we need to check the but as far as I know only run in the US so if you have anything in the cloud you want to run a Mac OS you can only run it in the US so does that basically mean if you're doing anything for Mac OS and you've got to work with gdpr you're basically going to have to use self-hostive Runners yeah yeah one thing about your Runners make sure you run in a in the right image that you want to use so if you don't need to use the Windows or Mac OS image just use the Ubuntu one it's way cheaper because one minute in Ubuntu is one minute but one minute in the windows uh image or in the windows Runner is two minutes that's being calculated being used and I think Mac OS is five or ten times that so it's really expensive if you want to run a macawatch runner and you do that in the cloud you're going to run through your credits really fast so make sure you if you don't have to use Windows or Mac OS run it down Ubuntu and the the South hosted Linux images they have Docker installed by deforger yep so yeah so some optimal settings we can use it for GitHub if you want to do anything in the cloud just use the GitHub one right that is basically what most of the people will do if you have a GitHub account self-host is for local deployments um so if you have I don't want to do anything locally and push that to one of your local servers you can use local Runners that way always use self-hosted for custom apps just way easier to do avoid running it as a service ask Kevin why he he will tell you why that actually should not run as a service just as an application and make sure you create your runner at the right level so if you have a lot of private repositories make sure you create your runner at the organizational level and not in every single repository because it's way more efficient right so all your other private repositories will have the runner for from your organization and enable long paths so that this was something that Kevin came with me like yesterday so thank you for changing the slice the so if you if your path is longer than 256 characters you have to go into the registry and change a couple things to make sure that that is enabled in Windows 10 and 11 right so I happen I have the website for it here I can show you because you have to opt into using long paths for some reason it's not there by default so just be aware of that if you're looking to use self-hosted runners cool so configuring workflow this is your part right it is that means I'll have to use it quicker and everything so yeah so this is where we're going to cover the actual SQL Server related deployments now and we're going to cover two types of deployments in this session first the deployment up to SQL servers themselves very quickly cover that and then we're going to talk a bit more about the SQL Server database deployments which basically means to do the updates to the schemas so there's various ways you can deploy your actual SQL servers in your environment so you can use for example arm templates you can use bicep now just out of curiosity who's using arm templates for their deployments at the moment uh it's magic as one two who is using bicep so quite a few more there so you can also use terraform as well so is anybody using terraform right now it's actually quite popular okay and who is using my there you go good so there's a few different ways you can do those deployments and now we'll create now we'll cover about the database side of it so I don't know I don't do anything with databases so for example as you can see here you can use a deployment pipeline to go through the different environments for testing and there are actually a couple of different ways you can do this uh you can do it either what's known as state-based deployment so you take the entire state of something usually catch it in a database project and then you create that file for deployment or you can do a migration based deployment and that's where you add scripts over time and there's advantages to both and some disadvantages as well so the first step I'm going to do is a state based deployment one mm-hmm so if we go back over the Azure data Studio and this is basically the same one that we did earlier so I'm just gonna just quickly save in and then what I'm going to do once you've made a change and you've saved an Azure data Studio you'll get some nice colors up here so we're just going to go to Source control and say I'll change the column I'm going to harden the change again and as you can see here GitHub very kindly invites me to synchronize the changes so I can just synchronize the change with the GitHub repository so we can jump back over there and now so if I click on pull request and again this is where GitHub will help you out because when you look at it from the top you think I've got a click new pull requests but if you scroll down and you go to select a new pool across from here and it actually gives you some other examples and not the one I actually wanted to cover here so a branch hasn't synced that properly so let me go back over Azure data Studio right so I will actually quickly show you what happens instead so so on Project that's right thank you so let's go back over here so if I go back to the right project it's like like we've done never done this before right so agency GitHub toes you don't know a comparison pool recently so we're just going to click on there scroll down and create the pull requests so see here we'll have to do then is click on motion pull request actually it's done as checks I've got the impatient there and I will keep the branch here for now because all I want to quickly do if we go with the actions you can see it's actually started a workflow so when you do a deployment in GitHub you do it a few what's known it's a workflow might sound pretty complicated it's just basically a yaml file so if I scroll down the hair and go into the code and click on this particular one apologies for those of you who do not like piano however okay not like ammo if you are working with GitHub actions you can only work with yaml at this moment in time so if I scroll down here for example you can see that the trigger is set so pull request on me if I scroll down what it does first it takes all of those files so in that particular database project it then does a build it basically creates a duck pack and then you can upload that that pack once it's been created and then use the same backpack file to do the deployments so if I scroll further down you can see I'm using various what known as GitHub actions so if you ever see something this startless syntax I mean it's a bit bigger for you with a slash in it that means that it's a GitHub action I'm going to talk a bit more about those shortly so some of you may have noticed is anybody using GitHub actions for SQL server deployments at the moment nope so I'm using a version of an action of 1.3 and this is because currently there can be an issue if you use the latest version which is version two now version 2 works great if you're under pointer Azure so if you're looking at deploy for example the Azure SQL database it's fine so that is basically a very short introduction on how you do it for state-based deployments in addition to that some of course I want to quickly show you all as well so if we go back over to here as you saw already got a database project you get a nice view in Azure data studio and if you explore it it's actually a lot of files so for example if we go back in the GitHub smaller again click on the code here so if you change your resolution by the way everybody too high it hides the code for you so you have to click on view code so if I go further down here and I scroll down this is a traditional kind of SQL project file that you get with your traditional database project as you can see it's quite long and it will contain everything all the files that it needs to do the deployment towards the bottom however there is now a nice new way that you can create a more streamlined SQL project file so if you have the latest version of net for example installed you can just use this command and I'll assume in a bit more here for everybody there this way instead and doing that I've just created a new database project for SQL Server 2022 and all I can do open the open Azure data Studio we go over to Explorer and that as you can see this SQL pod file is a little bit smaller than what I just showed you just a little bit just a tad and the really good thing about this new style the official term is SDK Styler project it's a bytes default it supports something called globbing so you don't have to reference the SQL files in there anymore you can just put them wherever you want to in your database project so it's worth looking into to be honest with you so now that's over if I will look to show you how to do migration based run so I can't remember it to this one it's a bit smaller so the migration based one there are a few different ways you can do this there are some open source Solutions and then there's some premium tools now these premium tools so we've got premium features of course they do come at a cost so in this session we show an open source solution and it's based on what's known as a DVR framework now does anybody use DB up out of curiosity right I think a lot of people are not doing deployments with single server at the moment maybe that's why that's why they are here right so I can show you how this looks in Visual Studio code so basically you set up a structure like this in your c-sharp project and then you can just add scripts to it over time and every time you run the deployment it logs an entry in a table in the SQL Server database for you can build up quite a lot over time if you're doing a lot of changes so just be aware of that now to do these deployments for the migration base nice and easily in GitHub we can use something known as a dbops Powershell module which is available on the data plant Network so if I go back over to the GitHub project I open up the workflow I can actually show you how the deployment's done here so if I scroll down this is why it's nice and easy to use the dbops parashore module so I make this bigger the deployment is literally a couple of lines of code so that is how you can do a migration based deployment in GitHub so now we're just going to correctly cover how to keep your secrets Secret in GitHub because you shouldn't put credentials like usernames passwords Etc directly in your yaml code it's kind of frowned upon by auditors so there's a few different places you can put your secrets in GitHub and it also depends on whether you're using your personal repositories or if you're working using a GitHub organization so for example here if I go into the settings I can scroll down switch to screen yeah there we go when you guys do that works a lot better check it so as you can see here you can put in secrets and variables in the repository settings if you're working with an organization you can actually put them in an organization level of a screw further down as you can see all the security is there to put them in so it all depends what you want to do with these secrets wherever you've been reusable multiple repositories or just one now you can also connect that to get secrets from Azure key Force as well and I can actually show you this in the project we were looking at earlier so if we go back over to here and like code I'm just going to go back into the workflow just so I can show you how it's done here so in this particular example I actually use a GitHub action so and that goes off and gets your secrets from the key for however I will be honest this action even though it is really good is now deprecated they just announced it recently so what they actually advise you to do now is cuc azure CLI commands in your workflow perfect however when you use this to get for example a connection string if you try and pull the entire connection string you're going to have a little bit of a problem when getting the password Friday because of how the security is done in GitHub so you'd have to figure out a way to concatenate the results however this action just makes the concept easier to show so you can keep your secret secret so unit testing so I guess that not a lot of so who was doing unit testing again with databases so what are you using for the unit test is that C sharp or is that any other platform like that um ssdt well that's probably C sharp right so um there's another platform that we can use that's T SQL t and T SQL T is a unit testing framework built by I've always forget his name it's German but um it's open source and it is purely T SQL and we can integrate this in our any kind of like a migration based or state-based deployment and it's keeps that the developer in its t-sql bubble right you don't need to use any Powershell or c-sharp or anything like that and I like that because I have one language that I have to learn it keeps it simple so there are some like I said there's T SQL T that we can use um I am lazy as hell when it comes to repetitive tasks so what I did was I wrote a Powershell module module called the PST SQL T Test generator what this will do and this is uh in the Powershell Gallery uh so you can just install it and use it it will take a snapshot of your database or look at all the objects of your database and generate basic unit tests for your uh for your project for all the different objects it looks for object existence looks for all the columns in your tables does it have the correct data type and the same thing for views and functions with all the parameters saves you a lot of trouble of course if you do something like integration testing and going from one stops here to another then it's going to be more work but the basic unit test you can already get started right so if some if a developer drops an index your tests will fail or you should remove the test um another thing is if you want to do it Kevin showed a nice way to use the new style.net to create a ssdt project I've got a template in my repository used and with the PS module development Powershell module you can use that template to create an ssdt project with the older style of.net in seconds and it already contains a GitHub workflow natural devops pipeline it is already available to you with a test project as well so if you want to use that that's already in there so is there a space between the eye and the T it'll change your slice at the last moment so you're going to use some magic right yeah Magic I told you I was going to be pretty so any questions so far by the way we're going to go through it really fast but can you tell say that again government count but the situation that several developers are working on the same development database yeah so what's your question so his question is if I have multiple developers working on the same developer database do uh how do I solve that with my branching model right well that's where feature branches come in that's where you actually work on the different features and they can use multiple that is actually drifted from the development you have several developers working in feature branches but all are working on the same development database you actually have a mix up of the feature branches and what they seem to data what is the state of the data yeah so so he's explaining that if you have like a lot of different development and your development branch is actually further ahead than your current when then the other feature branches don't it's a trip then you're in trouble right so what happens with that is you probably have to do more synchronizations between the development and your feature branches that's something that can obviously you cannot overcome otherwise it's yeah that's kind of it git is really good at that luckily enough if you had like SVN or any other version control system that would be real hassle so yeah that's not really a different solution for that any more questions so the question is does my Powershell module create test data no it does not what it does is it's really basic tests and tests like the objects itself you can or you can create unit tests with data right so that it generates data and then tests based on that but it doesn't do that automatically for you so is there any benefit for GitHub workflows against Azure devops Pipelines uh I think both of them are really good products um if you are already in Azure devops and you've have and you have a set environment there's not really a reason at the moment to change the GitHub workflows but I do have a feeling that development of GitHub goes it has more momentum at the moment than azure devops so um as we're about to move forward in this section that because of the magic behind them there are quite a few GitHub actions appearing yeah and I can show you now actually because that nicely ties in with the next part so we have to hurry up so we only have 10 minutes so I was not everyone thinks that GitHub actions are probably magic but they're not they're actually just GitHub repos stored somewhere and I can quickly show you this now so for example if I open up Azure SQL deploy and go to the marketplace listing and I scroll down here on the left hand side you can see there's a useful link if I click on here as you can see it is just a GitHub repository that's been created in a special way and GitHub provides a guide on how to do this and this is why there's a lot of momentum with GitHub actions at the moment because a lot of people are doing that so there are actually three different types of GitHub actions you can use so container composite and the JavaScript one and they can be used as well as for CI CD they can be used for different levels of automation as well as I will show in a demo coming out shortly so there are various types of triggers that you can use in your workflows in the yaml files basically in the one I showed you earlier the trigger was for pull request for the main branch you can have triggers for pushes as well and in addition to that you can also do a schedule as well so you can have your workflows run at a certain time which is really good if you're going to implement a level of automation and that brings me over to our GitHub script demo so GitHub script can be used to perform a lot of Automation in GitHub so for example here I'm in a different organization and I'm just going to create a new issue and I'm just going to see I'm just going to leave it all blank I'm going to submit a new issue so if I go over to the actions now you can see it's actually running a level of automation at the moment so if I open up now as you can see GitHub script has run it's very kindly assigned it to sander so he's got some he's got a couple of new emails waiting for him after this session get so many emails I get from him every single day the string is no demos yeah 100 other emails so of course if you're in the workplace you can use this functionality for example you can put certain labels on issues and they can be assigned to the right people so finally from there I didn't notice a couple of power bi people in the crowd earlier so I thought I better point out the fact that yeah because GitHub is an API you can actually get the metrics out of it for you some reports now I'm in two minds forever to actually show the one I've created now considering who's in the audience but I'm just going to go ahead and do it anyway so as you can see you can actually get at the metrics for how many times your GitHub actions have been run so I think we have just a couple minutes left so just to recap what we all did like we're five minutes perfect timing so we we're through a lot of features in GitHub we went through the bridging strategies and models that we can use uh we talked about doing stuff either in the cloud or on premise or using local local runners we talked about the workflows how you can actually do different triggers how you can set up with the uh all the actions we talked about secrets again if you put password in your workflow or in your in one of your actions I'm gonna I'm I see that too much a bit of unit testing uh really important by the way because if you don't test you don't know and Kevin did some a lot of magic so um any more questions you know and in case there are any questions there's a feedback slide as well yeah so here's our feedback slide please send any feedback that you could be good feedback or Worse feedback or the animal questions I want questions there's a question on the screen so watching you rename a table column without the refactor tool is physically painful that's not a question that's a comment but yeah that's painful but that's a demo right yeah it's just to give everybody an example anyone else I saw some hands go up there so the question is how how I'll be charged if I have an agent or a runner in this case because a devops is an agent and in in GitHub it's a runner how is that being charged it's charged by the minute so if if your task or your entire workflow runs uh for 10 minutes you're being charged 10 minutes if that is on a Windows one it's it's charged 20 minutes do that on Mac OS it's a multitude of that so so that's how you're being charged here charged by the minute okay there is a thank you slide as well um so that's a really long question um I'm trying to wrap around so your question is are how far along are we with the current ssdt project with all the post and pre-deployment scripts right based on what to compare the Azure the current Azure days here with the preschool I'm not really following that question I come back to me after this after a session because that's a hard question to answer right now by the way I've just put up our details again for everybody as well if you want to reach out to either reverse or you just want to grab any of the GitHub repositories Etc we have one minute so we're done if there are no more questions thank you

Channel: SQLBits

Views: 1,705

Keywords: Azure, DataOps & Automation, Developer, Developing, On Premises, PowerShell, SQL 2019, SQL 2022, SQL on Linux, Successful Delivery, Testing, deployment

Id: O9ZFmDR8dlc

Length: 48min 57sec (2937 seconds)

Published: Wed Jun 28 2023