Power your DevOps Development with SQL Server Containers

in 2016 I remember being here on the Microsoft campus over in building 16 which is where the SQL team used to be and was in some meetings with some of our leaders and Executives and in this room they announced to our team that SQL Server we were going to launch an adventure to build SQL server on Linux now this is my 30th year at Microsoft and I've heard that idea proposed about five other times throughout my career but I didn't really believe it until a gentleman named Slava Oaks used to be our technology leader for SQL Server he works in a different organization now he showed a demo he showed SQL Server running on Linux we're like why we just couldn't believe it so then the next thing that Slava showed us that well because we're running on Linux we can run in containers and my first question was what's a container so I started on that journey in 2016 like everybody else and eventually for SQL Server testing 17 in that year we launched a version of SQL Server that runs on Linux and with containers and when we first did this I'm not sure we quite knew the path we were going down so I'm Bob Ward from Microsoft I'm a principal architect and Azure data team I've just talked to a few Folks up front I'm actually from Texas like 40 degrees hotter there but I do work for the company for 30 years based in Texas I did remote before it was a thing uh coming up here several different times of the year but also traveling around the world quite frankly talking to customers I used to do some stuff like coding and those kind of type things now they just kind of pay me to speak about SQL server but also do architecture work for our product and everything actual SQL Edge to Cloud whether it's SQL running in Azure SQL VMS containers Linux or SQL Server so here's what I like to do today I would like to talk about containers first about what are they so we just kind of ground set ourselves on what is a SQL Server container and how much you could actually use it but what about this devops thing so we're going to talk about other technologies that we have built for developers to help you do things like database projects devops types works and then we'll conclude by talking about how do we integrate this in an environment like GitHub like how do we put all this together in a way that you can take advantage of SQL containers for databases develop free and then actually even publish things to the cloud if you need to do that so one thing I like to do when I do presentations and I love coming to vs live I think it's about like a fourth vs live I've been coming to because I want to make sure you have all my content and everything I do so you see this URL at the top up here feel free to take a photo right now the final slide that I'm doing today I'll leave up has those same links again the first one is a link to all my presentations um so right now if you go up there uh there's not a folder yet for vs live uh this particular event but I'll do that today I'll put a folder called vs live 2023 Microsoft HQ and you can go there and you'll be able to have this deck in its full entirety but what is already there is a site called Bob SQL demos that's a GitHub repo I've built and all the demos I'm showing you are there available for you to try you can just go through step by step and GitHub and do them yourselves of course one of the cool things about this is because SQL Server itself has something called Developer Edition it's all free so you can actually go through all these demos without spending a single dime because even the GitHub demos use a concept called GitHub Runners which can be free to use as well at a small scale so with that in mind let's start going to the content but let's talk to the logistics about questions we've got a lot of topics 75 minutes so what I'll do is I'll go through a section of material and I'll stop and say hey what kind of questions you have and we'll have time for all of them I'll stay here afterwards how I need to as well I'm also going to be after later today at your mixer event we have some keynote rehearsals to do because kind of related what I'm talking about tomorrow afternoon my vice president Assad Khan and some of my other colleagues will be here to do a keynote to talking about Azure SQL database as developers but part of that story is how we're going to help you use containers to do free development for Azure SQL database so lots of opportunity to talk to and my team is also here tomorrow and Thursday doing other presentations about t-sql programming and about database development in general so I don't know if this is your environment or not but I've seen this before many many times that a company will have a single SQL or maybe multiple SQL instances running somewhere could be in the cloud even like in a virtual machine and they share it with Developers so in this scenario you could have these developers all having different databases on a SQL Server instance and sharing it and it seems okay to do this you're isolated everybody has their own database it's all great but who manages the virtual machine or the instance so for example what if you got to take the instance offline what if some developer has rights to say well I'm going to take this down because what I'm doing I'm going to restart SQL everybody's down you're all affected so it may seem like you have this isolated model with databases but because it's a single SQL Server instance it's not now there's a couple of techniques you could do you can create multiple SQL instances one per developer that's a possibility but you might lose consistency for developers working on say the same application you could use the cloud the cloud is a way to do that we have something called Azure SQL database where that's an isolated model but of course you're paying them for an Azure subscription so a lot of people though could use Developer Edition of SQL server in this model and it's free to actually develop on and do testing but is there a way to do this where it's not so shared it's more isolated it feels like developers can develop on their own but still be consistent and containers are a way to do this and it's multi-platform so for example I could have maybe a consistent database schema of all tables and objects and procedures and everything that I share across the development team to use each one of them can work on their own laptop if they need to or even something called GitHub you've seen GitHub in the cloud before as well it could be like a virtual machine in GitHub and notice here though if it was using their laptops it could be Mac it could be Linux Windows doesn't matter because you'll find out that a SQL Server container is a consistent image of SQL Server regardless of where it's running because of the power of container Technologies so in this particular case everybody uses their own container but as an organization we can build something called a custom image we'll talk about how to do that for consistency across these developers and it works across all the iOS platforms and then we'll show you using this in combination with our devops Technologies you could be all working in the same project doing this model when I started talking about SQL Server containers back all the way in 2017 and 18 customers would come to me and say hey is anybody using containers in production and they are today actually we have customers using a container actually as a production version of a SQL server but the first thing I tell them is go do this go look at your development environment and see whether you should go deploy some like this to make your developers more effective so you're not all burdened by a shared SQL Server instance so with that context in mind let's talk about what is this thing what is an actual container so let's stop and ask a question to the audience how many of you actually use containers at all today anybody know what a container is you use containers I got a kind of some hands a little bit here and there so you're familiar with the fact that a container is based on an image and so for SQL Server containers it is an instance of a SQL Server container image running in an isolated fashion so we'll talk a little bit about just containers in general in a second so you get a feel for why is it different from running like a process in a virtual machine like is any different right it is but actually complements virtual machines doesn't replace them necessarily so think of an instance of a SQL Server image running an isolated fashion as a process so what's a SQL Server image well it's a binary image there's a protocol for how to go build images there's tools to do container images to build them and SQL Server container images are a pre-installed version of SQL server on Linux how many of you been through the installation process of SQL server on Windows today anybody tried that you go through the steps the guis the whole thing right so when you pull up and run a container for SQL Server a SQL Server container image it's our pre-install everything's there in fact so it's one of the quickest ways to get up and running with the SQL Server to deploy it because by running a container within like 30 seconds you're just running SQL Server all of a sudden so we've built these images in a way that it can just start running as soon as you run the container one thing that I have I haven't pointed on this slide that you should know because people have asked me this if it's this binary image how do I trust it so we have something called the Microsoft container image registry which is where all where our container images go and we run virus scans we run several things on that registry where these images are to ensure that they're secure for you and safe but we publish them all out there and we tell you uh globally across the internet how would you get access to these things so what are the images for well we started this process with SQL 17 so there are images in our registry container images for SQL 17 19 and the newest version we just released 2022 and cumulative updates to go with those based on Red Hat Linux and Ubuntu so all those versions are out there and anytime we do an update for a SQL Server we call it a cumulative update like a patch that we're doing we post a new version of an image that lines up with that so for example in your organization you all agree that we're going to be on SQL 19 some cumulative update because that's a standard in our company CU we call them CU cu6 or something well it's pretty cool because you can just pull down a specific container image that has that CU in it and you're already ready to go so you never patch anything so for SQL Server patching we just produce a new image for you to use versus you having to go run some hotfix installation to actually patch the image so that's what a SQL Server image is and what is in it well first of all there's binary files in there to support the actual operating system like Ubuntu or Red Hat Linux and then there's the SQL Server software itself and what we include today is what we call the core SQL engine and tools like command line tools so if you think about anything you need to do with SQL Server that's the core engine you think about security performance availability query processing all the stuff we normally have in our database product it's all contained in this actual image itself for a container and then it's just SQL Server so somebody when I first presented this I started talking about all the diagrams for the container and how it works with you know Linux systems and so forth somebody said well like how's it different than SQL Server 2019 that's out there today I'm like it's the same it's SQL server on Linux so if you installed SQL server on Linux I'm just giving you an easier way to run it but as a developer a very isolated isolated way to run this in your environment Okay so I need to make sure before I move forward though that you understand about SQL Server 2022 because when you're looking to see am I going to start using these containers again a free Developer Edition for you to use what's in the thing so in SQL Server 2022 we have Cloud connected features you could even go out there and do development and testing Developer Edition for free and test connectivity to the cloud for things like manage Disaster Recovery or something like that then there's a whole set of range of query processing capabilities called built-in query intelligence what do I mean by built-in query intelligence it sounds like a fancy AI term okay here's what it is our query processor is the thing that handles your queries you send select statements insert you'll set all these SQL statements to our engine we have built technology in our query processor to recognize query patterns that come in and on the Fly adjust and make them faster that's what it comes down to so we looked at common problems that developers and SQL professionals have run into when they build queries with applications and we try to figure out how can we adapt to your workload versus you having to go tune every single thing you send to the server so that's called built-in query intelligence and we've started that process with SQL 17 and 19 and 2022 has even more functionality for it then there's core security capabilities availability capabilities and performance capabilities like scalability here's one does anybody interested in doing blockchain technology not Bitcoin mining blockchain technology does anybody blockchain is blockchain is all about tamper evidence of data like trusting data and it's usually distributed system we have now built a blockchain in the engine you can go create tables in a SQL Server engine in a container and you can achieve and have blockchain functionality inside the database engine itself we call it Ledger and that came with SQL 2022 and here's the cool part it's in Developer Edition for you to test for free but has anybody ever heard of SQL Express built that thing a long time ago didn't we that's a free version to use in production now it's got limits on its sizing and so forth but ledgers even in Express so even allowing people to run production systems with a blockchain technology I had a customer that didn't have a large database size and wanted to run this blockchain across many different like stores in their environment and they're doing it today with a free version of SQL express running Ledger Technologies in their database now does any of you uh consider today as a developer looking at things like parquet files Delta tables you've heard of data Lakes maybe you've been working with before you've there's a buzzword out there right data Lakes well SQL participates in that technology and here's how you're used to using tables inside of SQL Server engine itself could be a container but you may be in an organization where your data is sending out a lake somewhere like a parquet file so how do you access that typically is anybody queried a parquet file before anybody written code of query parquet what might be a technology you would use to do that with anybody use Spark so spark is a technology typically python sparkbooks to go read things like parquet but do you guys want to go write that and write SQL no so in the SQL Server engine in your container that you're developing for free the SQL language can go read parquet just like a table we call it data virtualization even better you can take sqls and technology and Export your SQL data as parquet somewhere else in a different data Lake it's kind of like a way of doing archiving of cold data so all that comes in the engine with SQL 2022 and that's supported even back to this Express Edition I talked about and of course as a developer remember as a developer for free you're developing SQL Server a container when you first pull it down by default is a free Dev edition of SQL Server you then go and we'll talk about how we do this in a second if you want to go deploy this container production you would then go specify an argument to the way you run it to say oh I've got a paid addition now a SQL Server remember for free when you're using when using containers out of the gate it's a free development experience and then finally we believe in the SQL language is still powerful for Developers we don't want you to put all the logic in your app all the time maybe you should use the SQL language for it like Json so before Json types we have all sorts of different t-sql functionality anybody to work with time series data so we have t SQL functions to help you process data in buckets and intervals just like a Time series type information you'd have in your database all of what you see right there in SQL 2022 which just came out last November all of it's part of that container experience we first talked about that's that pre-installed version of SQL Server one last thing about this as a developer you should know and it's not on the screen here something called DB compat does anybody want database compatibility level is for SQL Server you do what what is database so you didn't you get questions did you what is DB compat what does that mean to you yeah backwards compat ability for like language statements and syntax but this is another thing that's interesting about it it helps protect you from things like query plan changes so you could actually go run a SQL 2022 container create a database in there make the compat level back to maybe a version you were using like SQL 17 or something and all sudden your app doesn't break right because the syntax is going to be recognized back at that level your query plays aren't going to change and even more importantly these newer compat levels that would come with like a new version every time we have a new version we raise the compat level it lights up new stuff like that intelligent query processing stuff I talked about so you can actually a developer start testing with an older compat level then change to the new compat level and then this is something that wasn't even it's not even up there there's something called the query store built into the engine so you can actually turn on the query store and track performance of your queries across those compaction level changes and compare contrast was it faster was it slower what I needed to make sure I'm running on the latest and greatest stuff so keep all of us in mind as you think about what containers are as a technology so I built this way back when we first started talking about containers because I was in a session one time with an expert I really admire in the industry I was listening to a presentation and they called a container a lightweight virtual machine and I almost fell out of my chair like why would you say that because they presented a slide that showed a container which again for SQL is an instance running in an isolated fashion so it's a process they presented as some sort of abstraction layer to get to the operating system look at my diagram each container running SQL Server here has the necessary files in it to run the engine but notice it talks directly to the host operating system do not think that a container can't be fast that is bogus you can go develop on a container on your laptop and provided you're going to go test in production with a similar type machine you got to keep in mind cores and memory and speed and so forth right you're gonna get the same performance because containers are running directly all the memory all the threading all the things we do directly to the operating system a container has full access to but here's the trick containers when they're run by what's called a container runtime use operating system principles to isolate the processes so they don't know that other processes are running so if you're looking inside a container you will only see what's running in the container but look at this the diagram this could be a virtual machine where you're running multiple containers in fact for SQL server on Windows we have a concept called multiple instances maybe you've seen that before we don't have it on Linux but we have this so if somebody asked me on Linux how to run multiple instances of SQL on the same virtual machine consolidation I'll say go do that go run multiple containers because they're isolated they don't know each other is actually running now they've got to share resources you got to figure that part out but SQL server has configuration commands to do that give this much memory to this one these many CPUs to this one we have all the control that needs to happen as a developer you typically are going to run one container in your environment but if you have to go starting an environment where you're going to share a virtual machine like I first showed you in that diagram you could use multiple containers for each developer and each of you could have your own database running in there a huge concept for containers something called persisted storage inside that dashed line you see for that container is a file system that's mapped to storage but if you remove the container everything in there is lost that's a problem for a database you don't want to go lose your data right so container technology allows for a concept called persisted storage which is like a separate file system outside your container and typically for a SQL Server environment you'll put your databases there that way if you remove the container your databases stick around and also it provides a very nice way to update SQL Server so if you as a developer had a container running in your laptop with SQL running a certain cumulative update and you wanted to go to the newest one you don't patch it you stop the current one you then take the the new one and start it up with the new updates and point it to your databases and it just works so switching now is a way to do updates for a program like SQL Server because of that technology so consider these points as you're thinking about it make sure you understand this behind the scenes all platform supported Mac windows and Linux you're probably wondering on Windows how do we do this you've heard of the windows sub system for Linux so we have that so SQL Server containers can run in that environment so it's kind of virtualized right but it's still running Linux so it's portable that's the beauty of it right anywhere I can run a container I can run a SQL Server container and it's lightweight notice in this environment somebody may have decided to run multiple virtual machines for this but I only read one to run three SQL servers so it can be more lighter weight from a perspective of consolidation how you run the programs and then it's consistent that is one of the biggest selling points here if you want all your developers to run the same test and develop in the same version of SQL it's very easy to do that with containers but we'll also talk about a way where you can customize the SQL container image and add your own things to your own image to make sure you're working off the same set of scripts or schema or tables or object whatever you need to do and again I talked about this efficient concept faster deployment because it's pre-installed we'll see in a demo in a second how fast it is to run one of these things so this is what it looks like if you use for example Docker which is one very common uh container runtime system out there you've probably all heard of they have a client tool called docker.exe and a typical in the Linux environment world would be say hey go run go do a Docker run and these represent arguments to the docker run command let's talk about what they mean because it's important to understand when you're running one of these things what does it do first of all if you run a container and at the bottom that's the image we're using and it doesn't exist locally on your laptop we will pull the image first so it's just like a download so if it doesn't exist locally like a cache we'll pull it from the Microsoft registry we'll put it down your laptop that's why the first time you do one of these things it could take a little longer because it's pulling it down so the first uh argument or the first Enviro the first argument is environment variables so for SQL Server because it's pre-installed you need to accept our license agreement that's one of the arguments and notice here this is something called the system administrator password containers do support active directory authentication but out of the gate to get it deployed you need to give us the administrator password that you're going to use for the system next is a port mapping has anybody ever tried on your laptop to run two SQL servers just by default at the same time I mean you ever running a SQL Server try to spin up another real quick it doesn't work because we'll have a port conflict because SQL by default listens on Port 1433 for TCP so you two can't listen on the same port at the same time so typically use this environment variable to map a port to 1433 so for this container remember we could have multiple of these I'm going to connect on Port 1401 for this container which is going to map into the image itself to the default SQL port next is my persisted volume so here's like a mount point this directory VAR Optimist SQL and Linux is a file system Mount Point inside the container image and what I'm telling Docker is anytime anybody puts a file in there map it to a separate persistent storage volume which could be a drive on your laptop a separate drive or even a network drive so again that's how you're going to make sure your databases stick around even you remove your container the next one's important the hostname parameter to Docker feeds into SQL as ADD hat server name so if you ever go look at your server name for your SQL instance use the hostname parameter to dictate what that name is going to look like and the name here is for Docker not for SQL so if you're going to go manipulate this Docker container instance which like stop it start it remove it it's easier to have a friendly name with this otherwise it creates some sort of it's actually funny the names it creates creates these really weird names of different famous people or something the next one is something says run the process in the background have you ever tried to go to your SQL Server environment and run SQL Server from the command line type in SQL Server I use this as a trick by the way I used to be in support so if you can't start your SQL Server as a service I'll ask you to go in from the command line and start it because SQL can run from the command line and when we do that from the command line it dumps out the error log on your console window like all the screen stuff starts dumping out so run this in the background otherwise when you run it you'll start dumping out the command line because one of the ways we run this container phrase we just run SQL from the command line in the container instance itself so Dash D says run it in the background to not see it but I've used this by removing that flag if somebody can't run their container okay go take off the Dash D and it might show an error of why it can't start up so it's a debug a tool to use and then finally most importantly there's the image that's the Syntax for where is that image located that mcr.microsoft.com that's the Microsoft container registry so that's where we're going to put all of our container images and we document what's the path to find SQL 1917 latest by the way means just find the latest update of SQL 2022 out there or it could be you specify a specific update you want okay one last thing before you show a demo is I told you you could customize an image Docker as a runtime as a client has tools to do that Docker build or Docker compose what you're going to have to do to use this technology is build a text file called a Docker file and there's a protocol and a Syntax for it here's some examples so I could in my Docker file put the word from and notice there's that SQL image notice the next command says copy a file which is locally in my folder where I'm running the docker build command from copy it into that directory which is where the SQL databases go in the image and then start SQL Server so if I say Docker build with that the internet builds a new container image called whatever you want my image that include SQL Server and when it starts it does that it copies this backup file and it runs this command I'm sorry the backup files in the image so this the command is to start it up so you're effectively doing the same thing we do to run a SQL Server container but you're overwriting it by adding your own thing to it why would you want to do that well it could be a backup from your company that you want to use for test purposes but it's you want a consistent backup for your developers so once you start this you would then just do a restore backup command once you've started the container in SQL and then you're ready to go another method is I want to run scripts so I call this the venue method because a former colleague of mine's venue showed me how to do this many years ago and the concept is this I have several different scripts I want to run to start at the container for example I'd like to create a new database with a bunch of tables and stored procedures and objects through a SQL script so the way we do this this the docker file is going to say I want you guys to copy these files run this command to set the right permissions and then notice the command to start the container is running what's called The Bash shell inside Linux to run that shell script well what's inside that shell script that shell Crypt says Hey first go run this shell script and Ampersand says run it in the background so this shell script starts off then SQL Server will start off so I'm doing both of these but wait a minute though if the server starts how do I run my SQL commits how do I time this well notice this trick I'll sleep so I'll wait about 15 seconds and then as SQL is starting up this script will do it after 15 seconds run SQL command which is inside your container remember so the tools are in there it'll run SQL command with your SQL script well what's my SQL script what's my crate database statement so when I when I build one of these custom images and I start it up this runs first this Waits 15 seconds per sequel start then runs this this actually takes that SQL script and now when the container is started I have SQL installed and my database installed and I can give it to all my developers and say everybody use this to go build on and write your app on it's a consistent way to have a development environment for containers okay enough slideware show me the money okay so what I'm going to do first I'm going to show you a little bit with these GitHub GitHub repo looks like so we might get a repo I have readme files that talk about what to do and I don't memorize demos so I'm like what did I say what's what are the steps to run here so I've got some scripts for you let me go first over to Powershell and show this for you this is the Run container command now I'm using Powershell but notice I've got an sh file so if you're a Linux user you can just run this in a bash show in Linux so doctor comes as a Windows client as well and if you go look at this I'm going to use this sophisticated editing tool called Notepad this is very similar to what you saw me do in the slide right this is the same type of arguments I showed you before so let me go kick this off and see what happens so remember we're going to run this in the background so we're not going to see anything show up it's like oh Bob what happened I just started SQL Server that's it I I actually set up SQL deployed it and ran it we're all in one step and you're like I don't believe you so first of all what we're going to do is we're going to see okay well why don't you just try to connect to it now I'm using this password right here so maybe you've seen this tool called management Studio like half the world uses and notice here I'm using localhost 1401 that's the port mapping I talked about I'll put in my password say remember password go uh there it is and notice it looks just like SQL server on Windows I mean this looks very similar to what you're used to seeing so you're like well I don't believe you like fine I'll just go create a database then so let me come up here and say how about this Rangers in first place yeah that's a good database name man yeah sorry Astros fans stick it so I'm gonna go here and I'm good to go but you're a developer and you're like I need to do tracing no problem we have something called X event profiler kick that off and also with X event profiler I can do all anything I want I can start just if I just go actually go here and refresh the object Explorer you'll see queries start showing up so I'm trying to prove to you this SQL Server this is what you're used to using so consider what I just did as a developer like in a matter of like minutes I installed SQL I started running it started creating databases I'm up and running very very quickly on my laptop distributed across all my developers in the organization and that image could have been that Custom Image I wanted right that I built with my database already in it so I'm going to give that developer I could have Docker run that uh image it could have been stored on a network server somewhere in my environment could be stored in the cloud we have something called uh in Azure we have a container registry in Azure you can create your own Registries with privacy and security built in you have all those options to you let's take a look and poke around and see what this container looks like a little bit what can I look at some so number one just to get them a Docker a little bit if you stop in Docker PS that shows you what Docker containers are running on your machine and you can see this is the one that I actually started with SQL Server there as well I could do something even more interesting uh what I'd like to do is I told you you can actually run something in a container like I'd like to go run and look at the error log in my container so this command here exec oops back in yeah here we go yeah that's that's a special notepad uh notepad wait wait till you meet my colleague domini Mario you'll probably laugh at that so look at that command there like what the heck is that doing so Docker has a way for you to run an execution of a program in the context of a container and the program I'm going to run is Bash the shell inside this container and it says run it interactively so it's a way to interactively run and get a terminal effectively inside the running container a SQL so let's go do to see what that looks like and see what happens that doesn't look like Powershell that's because I'm running in Linux now so remember those directories we talked about so in SQL Server just like you might have seen in program files Microsoft SQL server and windows here VAR opt Ms SQL is the standard directory where we keep everything and you can see here there's a data and a log directory so if I go in the log directory and I say cat error log there you go there's your SQL server error log and the data directory has if you go back over here the system databases oh and it has a database I built as well so now I'm traversing inside the actual container if I want to go and I've got SQL command in there available for me to run if I run queries inside the container now management studio is connecting outside the container into the TCP Port running SQL command is like running inside this environment yourself okay let's exit out of that now the other thing I want to show you though was Docker desktop for Windows I love this is a new interface that the docker team has built it's very powerful I love this interface here's the images I can see in my environment now if you click here this is what I love too the the docker team is showing the docker file that we built at Microsoft so our container image has got remember that those syntaxes I showed you for your your own Docker file well you can see how we actually built our Docker file for some examples and then if you go to Containers you can see here this is the one I'm running if you click here look at that I can go right in and look at logs or I can go to a terminal session directly in there so I love some of this functionality inside the actual Docker desktop interface itself versus having to use all the command line interfaces from the docker client it's pretty cool one last thing I want to show you so we talked about this venue method uh let's see yeah so there's a there's a concept called Docker compose let's say you have some sophisticated environment not sophisticated fairly regular environment in your laptop where you want to run a container of your app and run a SQL Server container but orchestrate it together as one deployment Docker compose allows that so Docker compose is a yaml file that has a syntax to it and in this particular yaml file what it says is hey for for the DB1 container go go to the dot BW SQL directory where I am and go take the container and run it from there and I can have another one called db2 or app one or something where I could start up and run my application so it's a way to compose multiple containers in a very orchestrated way but in this particular case I'm only going to run one but I'm doing that same method I talked about for these Scripts so if I go over here sorry go here if I go under BW SQL what's in there well notice those scripts that I have here is the actual Docker file so Docker compose says oh there's a Docker file there go do that so here's what it's going to do this is crazy compose will say I'm going to go build a custom image first if it doesn't exist so it's going to go use this Docker file to build one of those custom images with my file and then it's going to run that container so let's see what it looks like and it's using the same files to create the database that I showed you right so at the end of this I should have a running container so this is what it looks like and here's how you do it you say Docker compose up and off we're running now you're seeing here on the command line the process that it's going through and when it runs this container it does not run it in the background I could have told it to but you'll see in a second you probably say oh you see some stuff that's going on here from the actual error log file itself from SQL Server so what it's doing is starting up that container for SQL it's sleeping for 15 seconds and then it's going to run the SQL script to go actually create the database files themselves so once it's all done and it's finished I should be able to go into management Studio and see it so you notice here I put these comments in my SQL script those are comments from the actual T SQL script that's running and it's actually going to populate some data you can see it's putting some rows in there so how do I run this thing remember we have a container our running with Port 1401 right it's like oh what's that going to look like so if I go to my containers now notice here I've got a new one and it's Port 1402 let me zoom in and see you can see this there's BW SQL which is the container I talked at I told it use port 1402 so what happens over a manager Studio I should be able to hear and say hey let's go over here and connect not to 1401 but 1402. and I'm like okay that's great what password did you use well it's in here so if you remember in the actual Docker yaml file let's go run another Powershell here I'll SQL demos devops SQL containers and if we go look at the yaml file whoops we'll see it ah it's the same thing that's the password I specified so I'll go in here copy that started here hit it go boom I'm connected and here's the database I have called out I told you that's populated with tables and with data so again now in a matter of minutes I deploy to my laptop a customized image of my SQL server and my database or as a developer and now I can start running my app against this and I've got a consistent way of doing it across my company my project team whatever thing I want to go do questions let's stop and see which I've thrown a lot at you just to start with SQL containers let's see what you think about it go ahead the questions that support Windows authentication it does with active direct setup active directory for a container and do with a domain controller for example and do a Windows absolutely can yeah so you didn't set up the domain controller in the container it'd be something outside your actual container to do but there's a process to set up that container when I deploy it to say hey use active directory and then you can create 80 logins with it if you want to same thing SQL Linux supports so what we're doing SQL and Linux supports active directory authentication already so we're just allowing you still to do it with a container but you still have to supply an essay password to start with now at that point by the way you can disable essay if you don't want to use it anymore and just use your windows authentication logins but you got to always start with an essay password to begin we need that to kind of bootstrap the the product we don't support yet active director authentication Azure active identification or what is it called ontra we're going to support Entre yet but we will other questions the question is is that are there any issues trying to use Link servers and no you can use Link servers but only SQL Server link servers today not third parties link servers and in order to link servers though you got to be a little bit tricky because you got to set up the server names and so forth which is why that hostname is important because I recommend you use that host name to set up a server name that's added server name and now you can do sp ad link server based on those names and Docker compose is pretty interesting because if you want to do those linked servers between two containers it's got a little private subnet that it builds or the link server could be in some other environment that you've got to go connect to yeah today's just SQL Server link servers today but hey come talk to me afterwards I'd like to hear that scenario more to see how can we support that any other questions yeah foreign you use a failover cluster today for SQL Server you're saying yeah yeah so so the answer to that question the question is how do we do clustering and high availability and those kind of things and containers can support that but what we recommend is kubernetes so if you're gonna if you've got a failover clustering environment today with Windows SQL servers and you'd like to move to a production version of containers with SQL you should start using kubernetes kubernetes comes in with built-in failover clustering in a shared storage environment there's nothing SQL doesn't do anything we just declare ourselves as a stateful set and we're up and running in a failover cluster environment what's needed after that though is availability group technology which right now we partner with a third party company dh2i they have a solution that integrates with our availability groups to make it a complete ha system so it's pretty cool other questions about containers in general and SQL is it does this concept make sense you understand what we're doing yeah go ahead too in production so the question is is that you talked about development environment which is a great use case for it right but what if I want to go use Enterprise Edition now and in production and you can and we have customers doing it and what they do is they actually use multiple containers in a virtual machine as production SQL servers as a consolidation strategy so yes it's a SQL server on Linux so as long as all the features we have in Linux or help you support your environment containers are perfectly capable of doing that yeah one of them is the patching scenario I just showed you so if you want to have a more online way of patching SQL Server containers have a much better solution because you're stopping the container right starting a new one with a new update pointing a database and so also I could patch a SQL in 30 seconds so I can be online I can be almost online doing a patch of a SQL Server that's one huge Advantage another Advantage is consolidation like I said you have an environment with virtual machines and you're like hey I'd like to consolidate my VM somehow containers would be the way to actually do that and then the third option would be running in kubernetes like if you're going to run C if you like kubernetes as a platform you run on SQL Server you need container technology to do that yes the question is like like how do I what about all the stuff I put in scripts and so forth that are in my current SQL Server well here's your trick your system databases are in persistent storage so when you do an update to a SQL Server we don't touch that so you're literally going to be switching to a new container pointing to also your system databases and all your stuff's there you don't have to write the extra scripts it's already in your system databases so you wouldn't be running extra scripts at all and one of the cool things about this is I didn't mention you can roll back cumulative updates in SQL Server major versions are compatible with each other so you can go to cu6 in production oh there's a problem stop cu6 start up cu5 you're back and running because they're compatible between each other that's pretty cool so that's another reason that the benefit of running containers okay sorry go ahead the question is how big does a VM need to be it needs to be as big as your core and memory needs for your SQL servers so think of it this way uh if you're going to run SQL server in multiple instances in a VM today it's the same thing it's the same concept so and remember these don't talk to each other they don't share resources like one SQL Server could hog up everything but we have configuration options for you to control that hey I can affinitize this sequel to use these cores and I'll use this memory and this one uses these cores in this memory so you can actually manage all that yourself but you have to decide how big is that overall VM going to be what I found to be honest with you about this consolidation play is many customers over provision their VMS for SQL because they don't know but then I've had customers saying well I I gotta I gotta consult this down I gotta save cost I'm like okay you gotta do the exercise to figure out how much cores of memory you actually need from these things you do that and you might can actually go through this strategy okay let's switch on and talk a little bit about the devop environments I promise you we're going to talk devops with containers let's move on and talk about that okay so you might not have heard this before but there's a very interesting thing that we have called a data tier application framework it's an API to build something called database projects and what it deals with is schema by my schema by the way because there's a term called a schema as a group of objects in SQL but I mean schema more of like all your objects together your tables your indexes your columns your functions your procedures all the database objects I'm going to call a schema in this context and for years we've got something called SQL package it's a command line interface that uses that framework and you can use the SQL package framework to extract out your schema into something called a DAC package or a DAC pack so a DAC packs a binary file that's encoded to have all your schema well why do you care about that well you can take this backpack and go to a different database and publish it because hey I'd like to go take the schema out of one database and publish it on a different one and we've allowed you to do it with SQL package in addition I can read something called a backpack which is exporting my schema and data from a database and creating a new one or putting it into an existing database so what we've done now as a developer and we've had this for a long time as a developer we build something new something called an SDK style SQL build project and we've Incorporated this as an extension in Visual Studio code at Azure data Studio now we can use the dot net type commands that's SDK style commands with with this new package called build.sql you can use a.net new they build something called a SQL project a SQL project which we've had for a while now but with this SDK style is much more efficient and it's basically a yaml file that has descriptions about how to go build a DAC pack and publish it so with this new technology you can know a.net build and you can take a series of SQL scripts and you can go build a DAC pack so what do I mean by some of these terms like why is there an advantage between the old way and the new way well number one I can take a series of SQL scripts for my database objects and I can put them in a single directory and we do something called globbing we'll just find all your SQL scripts in a directory and we'll say oh those are all your objects to go create and we know how to order them so if you've got tables indexes procedures we'll order them in the right order to install and deploy everything so there's no dependency problems and then we have this concept called post pre and post deploy we'll talk in a second about some examples for that so SDK style projects through Visual Studio code and Azure day Studio provide a convenient way and behind the scenes the graphical interfaces will run those command line commands to build a DAC pack for you of your schema but do it based on scripts that you've built as a developer in a very efficient way so once you have this project in the stack pack what do I do with this thing remember I said you could actually put this into another database we have a published experience so I can take and it uses the framework here to take a DAC package and go create a new database or an existing database run all that stuff I said and you're like Bob wait a minute though I'm missing where's containers come into this thing well this is where it gets fun I actually can use this experience to start up a new container automatically and then go publish that in there so it's an integrated experience to do what I first showed you how to start up a container and how to build a set of scripts instead of customizing an image you can use this process through Visual Studio code to go publish the scripts required for your application all in a very integrated experience and this is what's interesting because Azure SQL database today with besides the trial version cost you money for an Azure subscription we have something called the Azure SQL database emulator the emulator allows you to develop for free and then do this publish process to an Azure SQL database so develop on a container even on arm even on arm develop on a container for free and when you're ready publish it to the cloud to your actual production database and then we're going to talk about in a second how we use GitHub to even make that more powerful so we call the Azure SQL database emulator what we mean is we're using SQL containers to emulate SQL in the cloud for you as a developer so develop locally move to the cloud so in this database project I want to break this down for you and show this to you before I show a demonstration of this so number one when you have these scripts you might do something called The pre-deployment Script that's up to you and you can declare it for us and one thing you might do is do some configuration you mentioned about configuration things as a developer you might use this script to do instance level configurations Max server memory other types of things that are configuration of your SQL Server so we'll run that first then we will glob all your SQL scripts that are built based on your database indexes tables procedures Etc globbing means you don't tell us the scripts we'll just go find all of them in your directory and run them and you have a way to exclude certain ones if you don't want those to be in there and then finally the post deployment script is interesting I use it for that populate data and testing so once this is done this post deployment will run in my examples and it runs in this order we'll run the instant config notice how we order the actual globbing of the objects correctly so dependency failures don't happen and then we run post deployment so if you're asking yourself like why would I want to do this one here like why would I do a test so I want to show you an example of of a scenario that you might make a mistake as a developer where you can use this process to make sure that you can do local testing to avoid a performance problem and use this method to do it so when I do that process and I fix my problem which I'm going to show you in a demonstration I want to make a change but in a change I want to do it incrementally and this is where it all comes into play if you make a change to an object like let's say a stored procedure you're going to fix a stored procedure problem and all those things I just showed you notice here you do a build to make sure those syntax problems uh publish only sends incremental changes that's powerful you could have 50 scripts out there for 50 tables and you change one file we're only going to go send the one change when we do the publish process so it's incremental building of your database objects then you can read post and before now keep track of pre and post deployed the only problem with these is we don't check syntax checking on build so it could be during publish if you don't you're not careful with these you actually run into some failures and then this is what I mean by testing you ever heard the term smoke test you guys use that before as a developer so I'm going to suggest to you that you can use post deployment as a way to do a smoke test at the end of this process so again you do a build you do a publish then part of your publishing though is going to be running your smoke test so right out of the gate you could run a test script to go see did this have a problem if I had a problem you'd make a change run it all over again and then you're good to go and then when I show you this I'm going to show you how to integrate with GitHub so let's go look at it so I'm running Visual Studio code and just to let you know if you go back look over here in the GitHub repo I'm running this one database projects so in here what I'm going to do is I've got a directory database projects BW SQL and what I'm going to do is I'm going to first create my project it's going to create a new directory and then I'm going to copy my SQL scripts that I need from here into that directory to build the project so if you go look at GitHub here yeah so let's go and do that let's go in here we're going to create a new project now I want you to pay attention to the choices I'm making I'm not going to use the emulator I'm going to use a local SQL Server development and I'm going to give it a project name bwdb and I'm going to pick where do I want these files to go for my project so that location I'm going to pick is where I have these scripts so I'm going to pick here and then this is beautiful because we're going to use containers I can pick my version so I'm saying oh hey why don't you go pick SQL 2022 and I'm going to pick the STK style project type I'm going to kick it off and I'm now going to have a project but I don't have any files Bob where are The globbing Happening no problem I'm going to go over here and for the files let me make sure I'm picking the right one for my instructions what do I want I want the customer SQL script that's my table I want the index to build the index and I want the get customer by ID that's my stored procedure and I'm going to create a post deployment script to populate the data itself to do the smoke test so let's go do that let's go over here let's get crate index customers and that's the three objects I need put them in here now when I go over here this is what I love about this because we're sporting this globbing concept I hit refresh Bears my scripts right but what I want to do is add a post deployment script so I click here and I'm going to say hey populate data and I'm going to go back over here to my populate data script just open it up a notepad and I want you to see what it does oh didn't do anything uh here this one I'm here that's the one that's getting built sorry that kind of scared me it was empty so all it's going to do is going to populate some data so let's do that so let's go run this we'll save it now I've got my populate data so what am I first going to do I'm going to do a build so I'm going to go build and the command line window you're going to see the steps of what's happening what it's doing is running that.net build that I showed you command line-wise it's running that behind the scenes to build your project and what you now have in your once you've done this build is you have a SQL project file this defines in yaml what we're going to do if I scroll down and look at this notice here that it has the post deploy script for me so it's going to take but it doesn't list all the SQL scripts for the tables objects and procedures that just comes by default but it specifically says hey go run that as a post deploy step so if I go back over here now let's go publish it now this is where this is where I get to go choose that I want to publish this to a brand new container remember Docker shows me the containers I'm already running so you've got these here running in fact what I'm going to do is I'm going to go ahead and just delete this one I don't want this one right now but I do have and I had 1402 as the port so what am I'm when I go actually go delete this new container actually I think this thing is over here still cooking yeah let me shut this thing down by the way uh for Docker compose you can say Docker compose down and it blows away the whole thing so if you want to like remove what I just did I can do that so this container is the only one running in Port 1401 why is that important so when we do a publish let's let's see what choices we get here so let's say publish the first choice is to a new SQL Server container and the port number is key here I want to be Port 1402 in this case I mean let's pick three just avoid any conflict with one of my head here's my password that I need and we talked about an essay password needed confirm it ah then I want to run SQL server and I accept the Yule agreement and then look at this I get to pick all those different versions because I picked 2022 remember I said there was updates for these different versions look at that those are all the choices I have so I can say I want latest but I don't have to do that I don't use a pro I don't have to use a profile and I want a new database ah here's the key so when I'm finished with this I expect this thing to go through create a new database start first of all start up a container create a new database and put all those scripts in there put all those objects in there for me and populate my data so I run it and here we go now one thing I've seen sometimes in this is that it it has its own retry logic so if SQL Server is starting up and it says waiting for 10 seconds so it's going to the process I almost like those scripts I showed you earlier where it's starting a container the SQL Server container and then trying to go run those scripts but it has retry in case it went too fast notice it says deploy DAC pack in progress so we're taking the DAC pack that we built and we're publishing what's inside it which is our objects and all our Scripts and putting that inside the SQL Server running container and at the end running the post deploy process which is what populating my data so it's finished this is great so I need to do this smoke test but I didn't put it in in the as an automated way of doing it so I want to show you so okay it says it's published this successfully so let's go add a connection let's say localhost 1403 and database name is bwdb and SQL login say and SQL 2022 is uh no trust certificate boom look over here this is like looking at management Studio I'm looking at what's over here and if I go down to tables and objects you see I've got the object I need I even have my stored procedures now I haven't done my smoke test yet so what I'm going to do is I'm going to go run this stored procedure and see what the plan looks like for the procedure I populated data a query plan because the way I built my design as a developer I know it should use an index because I set this all up correctly I'm a smart person so let's just see if it actually works that way so I'm going to come over here and I'm going to run this exec proc with plan so here's an example of running that stored procedure with a test value but setting up a command to go look at the plan after the facts let's run it and I'm going to pick that local container to run it on and it's going to finish now if you look over here I want to show you this this is what this output looks like notice here from this plan it's doing something called a table scan you see that that is not my intention like it should say index seek or something like that so with my manual smoke test I know it's wrong like crud what's going on here so uh again I'm just on my laptop just developing before I go into production I'm like this is bad uh so I'm like what do I do why is this not working so let me go do this I did a little research on the internet maybe I used a generative AI I don't know and I said hey why would I hit this problem well one of them is called an anti-pattern query this is the stored procedure definition if you go look at it let me go back and pull that up so inside here this is inside our project this is the store procedure definition and it uses a parameter for an integer to look up a customer but if you go look at the table over here if you look at the columns you can see that the customer ID is not an integer that is a typical anti-pattern query problem SQL can convert that correctly but it can't use an index if those are different data types it's a data type mismatch problem well crud what do I do no problem go in here change this to what nvarkar 10. save it great now what do I do let's go back to the database project now let me go look at this to make sure yep and varcar 10. so remember I told you what I'm going to do I'm going to do a build and then I'm going to do a publish and we'll only publish the incremental changes to the store procedure let's publish it aha I already have an existing SQL Server because the container is running pick existing don't use profile pick that one that's the container that's the database publish go so now it's creating it it's changing the stored procedure to fix the code to make sure it's using index so it's going that same process deploying the DAC pack but only incrementally applying the procedure chains that I need now the only problem with this process that I have so far is I could have been smarter because it's still trying to go populate my data it truncates and repopulates the data I could have gotten smarter about that probably but how do I know it's working let's open a file let's go back over here uh get customer or here exact proc with plan run it again running against that database now go down and look at the messages below and see over here whoops I can get this properly over aha and this is just two these windows are too wonky for me you see it says index seek so it's using an index now so I fixed the problem great well that's awesome but I did it all kind of manually right I did a little test manually and in fact the problem I run into now with my company is they told me hey we got to be part of the GitHub world we need to be in a devops environment so this is really beautiful what you just did what I want you to do is go publish your application and project in the cloud in GitHub and I want you to automate what you just did can I do something like that you can let's go show you what that looks like so I did all this locally with a container but wouldn't it be cool if GitHub could run my container for me automatically and do everything I just showed you it's possible with GitHub actions if you've not seen this before it's incredibly powerful GitHub actions support a CI CD process with devops how well it supports a concept called a workflow you can set up a series of events or jobs to go run things for you based on changes of code you make and here's the thing that's really amazing it has something called a runner and there are certain classes of Runners that are fairly smaller that are free so free running of containers if you want that to be the process to go check your code out I can start SQL Server Dev editions of free containers with a free GitHub Runner all part and I can run effectively that same process I just showed you with database projects but instead of just doing it on my laptop I can set up a repo and have GitHub do all what I just showed for you so now the GitHub SQL action comes along to be part of that and that includes things I just showed you an SDK style project you can publish a project and you run scripts in an automated fashion all using this GitHub action concept so let me show you what it looks like keep in mind everything I just showed you how to publish and build and do testing so in the cloud first of all over here in Visual Studio I have a GitHub Repro with all my stuff my project file everything that I showed you before I have and I put that in a repo all published all committed up in my repo cell but I added something called a test folder and something called make sure no warnings SQL Server is so powerful man inside the engine is a way for you to run a query and go automatically query and see did it do an index secret scan so I built this query to go run what's called a DMV query inside the plan in Cache and say hey do you see a warning like let me let me uh scroll that out a little bit do you see a warning over here it looking for warnings do you see that in the XML of this and here's what I do I say oh if there's a row that comes back and sends us a warning I insert into a temporary table I select from it and there's a Syntax for the SQL command utility that if you use the exit command with a query it Returns the number back so for me a zero is Success because there's no warning anything greater than zero is a failure because a warning shows up in what context if I don't use an index because of a type conversion problem so the engine knew actually by the way that I had that problem it already knew that had this type conversion issue and couldn't use an index and I have a way of querying and finding out did that happen so you're like well what do you do now well I can use a GitHub action to run this automatically anytime I make a change to my code let's show you what it looks like so I've got my own Repro for this app and again all this available for you to take a look at yourself I built this app repo with all this code and hit something called workflows and in workflows there's a Syntax for something called a GitHub workflow yaml file in here there's syntaxes for do things that are specific to GitHub like doing things like support running a container notice I've got that same syntax we're using a SQL container and then the SQL the SQL GitHub action has specific things like oh hey go build my project for me and go publish it but notice this one here this is the key it's going to do everything I showed you to create the database publish all the objects populate the data and at the very end it runs that automatically it runs that make sure no warning script with SQL command so in these GitHub action steps if the result back is a zero it's successful in this case if it's greater than zero it's a failure so I'm automating everything I just showed you by running that script at the very end and if it's zero everything worked if not and here's the beauty of it of GitHub actions it won't commit your changes so if this fails and I submitted like a new PR for a change to like a SQL query a table a procedure it would fail the pr and it wouldn't put it in the repo itself so now you've automated devops you've actually caught this stuff before it goes in production so if you go look at the GitHub workflow one of these examples right up here let's go back to the actual repo notice it says at the top here actions these are called GitHub actions and I've already published my initial repo when I made that first commit up there it failed well it failed because it ran the step here to go see that there are warnings notices test for warnings failure well what do I do now well watch this so here is that repo here is the procedure and what do we say it needed to be does anybody remember n varcar 10. right save it go to GitHub desktop there is that change fix anti-pattern I can't even type anti-pattern query commit it obviously I could have used a branch here I'm just using my own little private thing now here's the beauty I've set up that GitHub action that when I push that change it kicks in the workflow so let's go back up here let's go back up a level a couple level ah you see it running there it automatically clicks off the workflow again and what it's trying to do is it's trying to publish any incremental changes because I'm only publishing incremental changes and it runs my smoke test and if it works please does it work it'll come back with no warnings because I fixed the index problem so you see the beauty of this I now get to automate smoke testing for SQL stuff inside GitHub and this could have been part of a larger repo of my entire application you can chain these workflows together you could have an app workflow change you have multiple app workflow changes and then a SQL one is part of it so let's see is it going to work let's go click on it we've got three minutes left in this in the session build and deploy oh it's almost there I'm gonna let it go and see if you have any questions right now and we have a resource to slide to bring up in a second any questions about what I showed you as this thing is hopefully working right yeah Mike the question is like hey this is pretty cool how do I do this you can do the question should I put in the same GitHub repo that's all up to you my understanding you could even change these workflows across repos but yes so you could even have a repo with an app a repo with a database chain the workflows together and make sure that things work and are coordinated or you could have a repo with both of them in there I've seen both techniques used the key is this just like you've been able to do things before with applications with GitHub workflows to test your app you can now test databases and because of our GitHub SQL action it's a lot faster and we're using GitHub to spin up a container to do it for us that's pretty cool and that container image by the way I can specify what version of SQL that container images so if our developers say we need SQL 19cu6 that could have been part of my declaration looks like it finished looks like it worked yay you see it's green we're all happy but I thought you might find interesting this is a basic there could be other smoke tests that you have other things you might do from a database perspective making sure I don't have an anti-pattern query could be one of those this is one I've built I don't have others I I just started this process myself actually so yeah I mean there's other smoke tests I think I want to go build those examples and put in a GitHub example and show other workflow examples for you to do in your environment but uh yes it's kind of my first attempt for me I'm a SQL guy for like 30 years and I'm like oh I want to make sure an index is used that's the first thing I want to see and the SQL has a way to query the engine itself to make sure it happens pretty cool um okay just one last slide here been a great audience I appreciate it a couple things to to keep in mind here um so if you look at this oh yeah so this is the thing that's interesting you can actually publish directly into SQL let's say you're pretty comfortable with this whole container process you could actually set up another workflow that if that passes go publish it into my cloud database so that's a great example I would think of using this in terms of devops and then eventually pushing it into a production system it could be an existing sequel somewhere in your environment or it could be in the cloud and then this one be careful if you don't know what this is if you're publishing ddl changes table changes index changes things like that into production you could block your application so be careful when you actually go through those processes of actually making table definition changes column changes stored procedures are not typically a problem but those table changes index changes things like that that could actually get you into into trouble I'm going to leave this up here right now here's some resources for you um those are the scripts again up there at the top one thing I I do write books and I'm not trying to shamelessly sell books I take all my royalties and give them the charity I don't even make money on these things I don't care but I've read about four of them now and one I don't list is I wrote a book on Linux my very first one so there's a book out there for a press called SQL server Linux take a look at that one as well it has containers inside it the SQL 19 book also has a lot more information about containers 2022 I didn't do that because I already published things about it but uh yeah here's here's some information about GitHub SQL actions about database projects more about SQL containers and then the scripts and demos and my deck are all and I'll get that those decks because I'll put our keynote deck up there as well I'll get that done later today we're just about out of time great audience thank you you guys enjoy this was good thank you so much enjoy the rest of the conference appreciate it

Channel: Microsoft Developer

Views: 3,679

Keywords: Azure, Microsoft, Tech, Technology, Dev, Development, Cloud Computing

Id: g2hLS8PZBlM

Length: 74min 59sec (4499 seconds)

Published: Thu Aug 03 2023