Deploy SQL Resources Using Manual Methods

0:06Welcome to the content on deploying Azure SQL database resources. The DP-300 certification exam actually explicitly calls out deploying resources via the manual methods. In a separate section of the certification exam, it calls out deploying resources for the automated methods. So in this set of content, we're going to tackle the manual methods first. This is going to be things like using the Azure portal to deploy these. Or I also want to point out that you can do a lot of these steps directly from an application called Azure Data Studio.

0:44This is going to replace SMS at some point. It's not there yet. But it's good to start using that application and at least get familiar with it. So in this video, we are going to deploy an Azure SQL database the old fashioned way, as well as with an elastic pool. We're also going to talk about how to deploy an Azure SQL Managed instance. Block out your calendar for that one because it takes a long time. All right, without any further ado, let's get started deploying some Azure SQL resources.

0:06The easiest way to get started working with Azure SQL resources, and probably the one that's going to be most familiar for database administrators is to simply deploy an Azure SQL virtual machine. This looks and feels exactly like the things that you would be doing from vCenter or hyper-v manager, however, it's just done from the Azure portal. This, again, is going to be the infrastructure as a service offering. The one where you are going to be responsible for managing and maintaining, not only the SQL server instance itself, but also the operating system.

0:42This is great for lift and shift operations until you can start to refactor your applications and your databases to be more towards the platform as a service offering. So let's jump into this and just deploy an Azure SQL virtual machine, and you can see the factors that come into deploying this resource. Let's go. So our first step is to deploy an Azure virtual machine. And we're going to do this the manual way via the portal. And there are a few different ways that you could do this. You could, of course, deploy your virtual machine just by choosing virtual machines and then selecting the correct image with it.

1:19You can, in fact, deploy your own virtual machine, and then manually install SQL Server, provide your own license, and do it that way. And that makes a lot of sense. It's perfectly fine to do it that way, if that's what you want to do. But if you want to deploy a SQL virtual machine and have SQL already installed, we can go ahead and do that right here by choosing the actual SQL virtual machines that helps us skip a step. And it helps us actually identify the image a lot quicker. Now we are going to talk about licensing and how that works with SQL virtual machines in just a second.

1:51So let's click on SQL virtual machines. It takes us in here. It will go ahead and click Create SQL virtual machine. Now we've seen this page before, right? It's not actually the SQL virtual machine page. There's the platform as a service offerings, and then there's SQL virtual machines. From the image dropdown, we see these are all of the possible images that has the SQL server additions on top of Windows Server. Now you can scroll down and see there's a bunch of different mix or match options here. We have things like SQL Server 2019 Enterprise on Windows Server 2022.

2:28Scroll down some more though, and now we've got SQL Server 2019 on Windows Server 2019. We also have these (BYOL) for Bring Your Own License of SQL Server 2019 on Windows Server 2019. So if you had SQL Enterprise Edition, that does entitle you to hybrid environment benefits where you can provide your own license for SQL Server 2019. When you launch it it's going to ask you to license the actual application itself.

3:03And this actually gives you a lower cost per month. As opposed to above where it's not Bring Your Own License, the licensing for SQL Server is then built into your hourly rate. So that's how you actually can go about paying for this particular service in the cloud, is you're going to be able to bring your own license. Or if you don't have a license that enables you to Hyper benefits, then you can just build it into your hourly rate.

3:33The same thing goes for Windows Server for that matter. If you actually have the Windows Server Enterprise Edition, the data center editions, then you can actually use the Bring Your Own License for the Windows Server itself and get a reduced fee. So you could actually do the SQL Server 2019 Enterprise, which is not Bring Your Own License, build that into the hourly cost, but provide your own license for Windows Server 2019 and lower the cost there. There's a bunch of different options. Beyond that we also know that SQL Server, for a while now, has been able to be run on top of Linux environments.

4:06So if you want to use Red Hat or Ubuntu, then you can absolutely use all of those as well. So you get to choose the environment that works best for you, or really the licensing model that works best for you. And in my case, I was going to choose the free SQL server license for the developer edition on Windows Server 2022. So I'll click Create, and then this takes me into the actual wizard where we can create the virtual machine itself. Now just like everything in Azure, you're going to want to choose the correct resource or the correct subscription.

4:39And then I'm going to create a new resource group. I'm going to call this Knox SQL to create a new resource group on the fly. We're going to give our virtual machine a name. I'm going to call this knox-sql-iaas, just to get this deployed. And I'm going to deploy this in the South Central US. Now the availability zones, this is where we could actually define what kind of availability or infrastructure redundancy, I should say, that we want for our specific virtual machines.

5:11With an availability zone, we can physically separate our resources within an Azure region. That's the idea there. So there were actually multiple data centers within the South Central US region. And by choosing the availability zone, we are able to actually add redundancy that spreads our virtual machine out across multiple regions. This would be things like having redundant copies of our data, as well as redundant copies of our virtual machine configurations. Now beyond that, we have the security type.

5:42So what do we want here? Well, we've got the standard level of security for our virtual machines or the trusted launch virtual machines. It protects against persistent and advanced attacks on Gen 2 virtual machines by enabling features like secure boot and Trusted Platform Module. This is a virtual TPM. For me I'm just going to keep this at standard for right now. Now, again, we've already selected the image. But if you need to go back and change this, you can choose See all images here, and it will bring you into this long list of images. Now, I am going to ignore this and go back to my Create virtual machine.

6:16Now, ultimately, the big thing right here is where does all of this costs come in? It all comes in with the size of the virtual machine. If you choose See all sizes at the bottom, you can see all of the different sizes of your virtual machines that you can deploy. Again, this is a SQL server. So chances are we're going to want at least two cores and 8 gigs of RAM, depending on your workload, of course. But you can also choose different size virtual machines, depending on the different series that you have here. It also gives you kind of like what we were doing with the platform as a service offerings, where you could choose the different kinds of CPUs or resource optimizations if that's what you want to dig into.

6:57So you can see the different series for higher memory speeds or continuous full CPU performance. That's the idea with all of these different series of virtual machines. The standard general purpose virtual machines, though, are almost always the D series of virtual machines. And when you see things like the D2, D4, D8, what you're really looking at there is the number of CPUs that it has. So when I see the D4s version 3, this is going to be the third version of a general purpose server virtual machine here that has four cores and 16 gigs of RAM.

7:36It can have up to 8 data disks, and its Max IOPS is 6,400. So, again, this is probably going to be a very, very important thing for you to make sure you're looking into the IOPS and performance as well. So when I see things like the D2as version 4 here, I know we've got 2 cores, 8 gigs of RAM, and 3,200 IOPS. If that's not going to meet my performance needs, maybe I could sacrifice 1 gig of RAM for higher IOPS. Or maybe I need to jump down here into the larger size VMs, like the D4s_v3.

8:10Let's click Select there on that particular one. I see it's going to cost $308.06 a month. Now for a Windows Server Virtual machine, you do have to specify a local administrator account first before you can do things like domain join in. So I'll type in a local administrator account, here we go. And also point out right there that the name that you gave-- I forgot to mention this-- the name of your virtual machine will also be the hostname of your VM2.

8:41I'm going to allow 3389 inbound, so we can already be up to it. And right here there's the checkbox where you can reduce the licensing by owning the Azure hybrid benefit. This is specifically for the Windows Server license. Just the Windows Server. If you were trying to do the SQL server license, you need to make sure you pick that from the image, the (BYOL) license. Now the number of disks that get attached to this virtual machine by default. If we take a look here and we go under Advanced, we're not seeing any virtual disks here, are we?

9:12Well, that actually has to do with the fact that we did a SQL virtual machine deployment. It's already getting a handle adding the additional disks for things like our database files, for our log files, for our temp files. Trust me, we'll see more of that in a second. Now let's jump into the Networking section here. Well, since we're deploying a new resource group and a new virtual machine in that resource group, it's going to attempt to scaffold a virtual network for us. And by default, it uses a subnet within a block of IP addresses.

9:43In this case, it's 10.0.0.0/24. What I'm going to do is I'm actually going to choose to create a new virtual network here, and I'm going to change this up a bit. I don't want to use this particular block of address space. I want to use something like 10.99.0.0.16. And, therefore, I'm going to have to create my own subnet for this. I'll call this default SQL, and give it the address range or address name of 10.99.00/24.

10:15It's going to be a little bit of subnetting there for you, so don't worry if you don't totally understand that. You may want to learn a little bit about subnetting at some point because it's very, very interesting stuff. And we've got some great IPv4 subnetting content here on CBT Nuggets that Keith Barker teaches. I'll click OK to this, and we see it updates this to have a new subnet that doesn't overlap on my particular on-premises subnets. Because ultimately what I'm going to want to do is I'm going to want to create a site-to-site tunnel, and I can't have the same subnets on-premises as I do in this virtual network.

10:52So with all that being said here, I've got the virtual network scaffolded. And here there are a bunch of different additional parameters that you can specify, like a network security group which kind of acts like a sudo firewall. I am going to allow 3389 in, so I can still reach this virtual machine and reachable on the actual network. So with that being said, we'll move on to the Management. Well, there's not a whole lot here that we want to do, except for we may want to enable Boot diagnostics which comes by default with the managed storage account.

11:22There's some content in the AZ-801 certification exam that covers the difference between the managed storage account and the custom storage account. The big difference here is with the custom storage account you can actually get direct access to the console of the virtual machine. Whereas with the managed storage account, you really are just looking at boot logs at that point. So that's it, that's what I want to do. There is for some virtual machine images, there is the capability to do log in with Azure AD, this is not one of them.

11:53And at this point I'm just going to leave the rest alone because we're going to cover the rest of that upcoming later. I'm going to jump to the SQL server settings because this is where the interesting stuff is. Here we have the ability to specify our SQL connectivity. By default, it only allows SQL connectivity via within the virtual network. If we've tried to fire up SSMS and connect to the SQL server over the public internet, it is not accessible. So you would have to switch this to the public internet if that's what you want to allow.

12:28Otherwise, this is only going to be accessible within the virtual network itself via the private IP address effectively, is what it's saying. You're specifying the port that it's listening on. Now, SQL authentication, do we want to enable SQL authentication or do we want to leave this to a particular user? If I put Enable here, it is going to specify a login name, as well as a password for what is going to be the administrator account. Let me go ahead and just specify an administrator account there. There's also the ability to do Azure Key Vault integration, which is where we can store secret keys and certificates where this particular SQL server can access that.

13:08So that's something we will talk about in a later set of videos, we won't dwell on that too much right now. Check this out, the storage configuration. OK, let's do change configuration and you'll see what's going on here. See, this is where it adds the disk types here. Now we've got a 1 terabyte drive with 5,000 max IOPS, throughput 200, and there is one disk here to be used for the data drive location. Again, if you need to specify these things or change these things, this is where you can go about doing it. Similar items for the actual log storage, it says, use a separate drive for log storage.

13:43Again, 1 terabyte drive, same settings, effectively using the premium SSD. And then the TempDb storage, this uses a local SSD drive stored right there on the spot. You can configure your TempDb files, that way you can see the number of files, initial size, and auto growth, and configure the TempDb log file as well, right here on the spot. So we'll click OK to that. Now we can actually configure the SQL instant settings itself.

14:14Give this a quick click here-- try and say that three times fast. And then you can specify, first of all, your collation and then the max degree of parallelism to option, to the limit, and the number of processors to use in parallel plan execution. We can also specify our SQL server memory limits. And do notice right here that it does, in fact, set a max memory limit. Right here it looks like it's roughly 2 megabytes in size. Remember, though, are two-- yeah, excuse me, what am I saying here?

14:46This is specified in megabytes right here. So this gigantic number here effectively means that it's going to eat up as much memory as the host virtual machine will allow it to at this point. But you can specify a max server memory right here if you want to manually set it. We also have the ability to optimize for ad-hoc workload, lock pages in memory, instant file initialization which, of course, reclaims storage space that's been used by filling it with zeros.

15:17Interesting idea there that it can actually instantly initialize the file operations when it needs to. I'm going to discard all this, and then we'll scroll on down. Notice something else here, we can automate when Windows and SQL Server will be patched, right here. And notice, very, very important. This is enabled by default for Sunday at 2:00 AM. You may not want your SQL Server to automatically patch itself. And this is a configuration that you would want to change when you do that.

15:47Also notice that the Azure SQL platform has the ability to actually automate backups themselves. So if I click Enable here, we can set things, like our retention period. We specify what storage container, basically what blob storage do we want this to live on. Do we need to enable encryption? Do we want to back up the Sys Db? Do we need to configure the automated-- do we want to manually configure our backup schedule? Or do we want to leave this to the default backup schedule? Which is the one that we actually talked about when we were going through the evaluation of our SQL resources.

16:21Then, of course, we have the ability to enable our services with Advanced analytics, which is great for the machine learning operations. So having made all of these changes here, I am going to review and create my virtual machine, and let it go through the process of creating all of the resources. Starting with the resource group, the virtual network, then the actual virtual machine itself, which has the image of MySQL server on it. So all of these things here look good to me. I pass Validation, I click Create, and there it goes, it's off to the races.

16:54I'm going to circle back to it when this virtual machine has been deployed. Now while this is deploying, I want to point out this template over here. If you've been following along in your deploying your own virtual machine, and you plan on continuing with this content, you'll want to click out this template button right here. And then you'll want to download this template. This is going to be really, really important for us later down the road in a different set of videos. I'm going to click Save here. I'm also going to click Add to library. This is going to be a fun one to remember.

17:25So let's add this item to the library real quick. And we'll give this a name called knox-sql-iaas, something simple like that. I'll put this in-- may just put it in my app-- no, let's put it my sql-iaas-group, that's where I want to put this. And I'll just say Review and create. Or actually we've got to put a version number here, let's put version 1. And we'll just go ahead and say Review and create to save this template real quick. There, create, just like that. Now we've saved this template while the resource is deploying.

17:55And we'll be able to deploy from that template later. Trust me, that's going to help us a lot when the time comes. All right, so the total resource deployment time is about seven minutes to get this thing completely up and running. I'm going to go into the resource itself. And now I actually see that I have a virtual machine, I see its public IP address located here, and I see its private IP address located right here. I can connect to the virtual machine with RDP, and we will do just that.

18:27Let me get logged in. It takes just a moment to get into the virtual machine itself. If I click Start here-- let's just take a look to see if it's working. Under the SQL server section-- well, first of all, let's see if the SQL Service is installed, right? I can bring up the configuration items, and I see right there the SQL service is running. That's a great start. Let's bring up SQL tools, launch SSMS, and see if we can connect in to this local machine using the credential specified. Again, remember I was doing a SQL authentication.

19:01So we can use Windows authentication or SQL server authentication. Let's start with Windows authentication because it is in mixed mode. Click Connect, and boom, we are connected. We don't have any databases yet but guess what, we now have a functioning SQL server using infrastructure as a service in the Azure cloud. So that's how you go about deploying this particular type of resource. In the next video, we're going to focus in on how to deploy the platform as a service offerings. I hope this has been informative for you, and I'd like to thank you for viewing.

0:00[MUSIC PLAYING] Now we're going to jump into the portal and, this time, deploy a platform as a service offering. We're going to be deploying an Azure SQL database. Now, I want to tell you right now, the thing that oftentimes throws people for a loop is deploying the Azure SQL server first because it's just a logical SQL server. And it feels a little fuzzy the first time you do it. But don't worry, because we're going to make some sense out of it in this video.

0:31Let's start deploying an Azure SQL database. Now we're going to have some fun and get a quick and dirty SQL database brought to life using the Azure SQL platform as a service offerings. Remember, this one, when we deploy just an individual database, is just that. It's an individual database that, by default, comes with a publicly accessible, fully qualified domain name. And this is perfect for deployments that are, really, based on platform as a service offerings that we're going to integrate into many different apps, particularly in the Azure fabric.

1:09We can either go to the SQL virtual machines, or just jump straight into SQL databases. I'm going to do SQL virtual machines, first of all. Because we do see my SQL virtual machine right here, don't we? Yeah, here it is. I'm going to click Create, and then it should jump me into this page right here, where we see the three different types of the Azure SQL platform. Now, for this video, I'm going to be deploying a single database. But trust me, there is going to be a moment that kind of throws you for loop the first time you see it.

1:39I'm going to click Create to jump into the wizard, and we're going to get started with this right now. I'm going to change my subscription to be the right subscription. I'm going to put this into my SQL group, even though it does say sql-iaas. I know, I know. Now, for the database name, you see some constraints immediately, like it can't end with special characters or contain these particular special characters. The database names shouldn't be empty. Now, we are going to attach this database to a SQL server. And that SQL server-- this can't have the same database name of any other databases that are attached to that SQL server.

2:15So let's go ahead and type this in. We're going to say knox-sql-- I'm going to call this P-A-A-S for Platform As A Service. And I check-- I get the little check mark here that says everything is passing the checks here. Now, the SQL server itself, is something that will have to create for the first time. This is a pretty interesting concept to wrap your head around because this SQL server isn't like a typical SQL server. This is just a logical instance that you will connect to.

2:48This SQL server will have a fully qualified domain name that you will connect to over the public internet, for the first time that you connect to it, using SSMS. Now, how you connect to it and how you authenticate to it is something that we're going to cover. But I just wanted to wrap your head around this right now. You're not paying for this logical instance. You're paying for the amount of compute that is dedicated to this particular database. And you can have multiple databases connected to this one logical instance.

3:23And you'll pay for each of those databases individually. So really, all this SQL server is, is just a connection point into the associated databases and how you authenticate to it, and that's really about it. Let's go ahead and create the SQL server for the first time. Notice, it is going to end with .database.windows.net. So if I call this knoxsql, check it out. This is uniquely identifiable around the world. I'm going to also put this in South Central US where the rest of my SQL items are.

3:57Now, look here's where we can specify the authentication method. We can use SQL authentication. We can use only Azure Active Directory authentication, and you have to specify an admin upfront. Or we can use a mixed mode of both. So I'm going to choose mixed mode, just for good measure. I'm going to set my Azure AD admin to be myself. I'll search for myself, click on my name, and click Select. Now I have an Azure admin located here. I can also specify a manual Azure admin for a login here-- a local login-- for the SQL server instance itself.

4:36Again, this is for the SQL instance, not the given database. You will be able to attach many different databases to that instance, and this is where we're specifying the admin of all of those databases. So I'll click OK here. And now it's going to create a new SQL server instance that we can specify. Notice here, we've got the elastic pool option here. We also have compute and storage that we specify here. Now, we covered this in great detail. For very simple reasons, I'm just going to drop this in basic and just leave everything else the same because this is very, very cheap to deploy at $4.90 a month.

5:14And this is just a lab environment at the end of the day, anyways. So now that I've got the basic tier set for the DTU model, we can also look at the backup storage redundancy. Remember, again, that backups are automatic with the given Azure SQL database. And when we choose geo-redundant backup storage, this is going to replicate to a different data center, a different region in Azure, and give us read access to those database backups.

5:46Now, in the networking tier, this is going to be an interesting thing. You would think that this is what specifying the connectivity to the actual SQL server. But this is actually creating unique objects when we look at the connectivity method so that other applications in Azure see this endpoint and can connect to this endpoint, rather than an IP address or a fully qualified domain name. We can skip this because it does allow you to create these services after the fact, after deployment.

6:20The connection policy also here is how clients communicate with your SQL database server. When it's within Azure, by default-- we've got Default selected. When it's within Azure, it tells you right here, "Clients establish connections directly to the node" using the redirect method. When it's outside of Azure, coming in, all connections are proxied via SQL database gateways. Remember that database.windows.net? That's really a proxy. There's only two IP addresses that that resolves to.

6:52And from there, it knows based on the host name that's being connected to, the record that's being connected to. From there, it knows to redirect you to the correct Azure SQL instance in database using the proxy. So leave this as default. And for encrypted connections, you'll want to leave this as a minimum of TLS 1.2-- that is the latest version-- unless you have a really old application that needs to connect over older standards of TLS. Now with that being said, I'm just going to jump ahead a little bit, past Security and Additional Settings, because we just want to get this resource deployed and then connect to it via SSMS.

7:31So I am going to create this configuration real quick and deploy the Azure SQL database. It should deploy pretty quickly. So we'll come back to it when it's done. So it doesn't take terribly long for the resource to get deployed. We see that the SQL server has successfully deployed with a database instance to it. When we click on it, it says get started working with your database. But first, we have to configure access to your SQL server. It doesn't allow anybody in over the public internet by default. I know, that was probably something you were worried about, is that when you deploy these and it's got a fully qualified domain name, that it just allows anyone in.

8:08It doesn't. Let's go to Configure so that we can allow access for a specific network or a specific IP address to be allowed in. Check this out. With public network access, it does allow public network access to selected networks. Otherwise, we can just disable it outright. We can choose what virtual networks are allowed to connect to our public access endpoints if we created those endpoints. Otherwise, we could allow specific IP addresses or IP address ranges to be allowed in.

8:42It detects my IP address by default. So I can white-list my IP address by clicking this "Add your client IPv4 address" and click Save. So now only my IP address can connect in to the actual SQL server instance itself. And then, of course, it has to pass authentication from there in order to actually be connected. So now that I've gotten my IP address white-listed right here-- no, you can also specify a firewall rule.

9:14So you can specify a starting IP and an ending IP if you want to white-list a range or a subnet that you may have public access to. So let's jump back to my SQL database now. And now that I've got the configured access, we could choose to connect-- we could get our connection strings in order to actually connect our applications into it. Or we could actually start editing or developing directly right here within the platform itself. Now that is in preview, the query editor. It's still in preview right here.

9:45I want to point that out. But you can-- look. Because we chose to log in with Azure authentication, it does allow me to continue. Right here, look, boom. Now I'm connected into this database where I can create tables and everything like that. I also see hooks into the Power Platform, like Power BI, Power Automate, and Power Apps. Again, I want to stress this. Power Automate is something that individual users can use on their account. Logic Apps is the equivalent for developing a central workflow application.

10:16And that's probably better suited for most systems administrators. Now let's actually go ahead and get connected into this SQL server. Let's see how we go about doing that. From the Overview pane right here, I see my server name knoxSQL.database.windows.net. What I can do is I can actually launch SQL Server Management Studio on my local computer. And because I've white-listed my IP address, I can go ahead and get connected in. Once SSMS gets launched up on my computer-- it's still launching right now-- then we'll go ahead and get connected.

10:51So here we go, SQL Server has launched on my screen. I'm going to change my server name to be the knoxSQL.database.windows.net. Now, remember I set this to be SQL authentication as well as Azure Active Directory authentication. So we have three different options for how to do Azure SQL-- or Azure Active Directory authentication. There's the universal with modern factor authentication-- or multi-factor authentication, excuse me. So if you have modern authentication set up and MFA enabled on a particular account, this is the method that you want to specify.

11:27All you have to do is type in a username right here, and it'll actually pop up a window in an embedded browser, where it walks you through the login process and validates your credentials. Otherwise, you can specify your username and password right here. I'm just going to use SQL authentication right now because it's very easy. I know my username and password. I actually don't know my Azure password. All right, we're going to click connect here, and boom, I am connected in just like that, because there's my database, because I white-listed my IP address and I used the credentials that we're allowed to use.

12:03So now I have successfully deployed a platform as a service offering using the Azure SQL Database options. In the next video, we'll take a look at how to do it with an elastic pool. I hope this has been informative for you, and I'd like to thank you for viewing.

0:06Now, we're going to deploy an Azure SQL database again, but this time, we're going to focus on deploying it within an elastic pool. Remember, the idea here is that you can buy a bundle of compute and then deploy your Azure SQL databases attached to that. That way when some have an unexpected spike in workload, we've already paid for a pool of compute resources that our databases can actually pull from and scale using that. So let's deploy an Azure SQL database within an elastic pool.

0:38So in the previous video, we got a public facing Azure SQL database up and running. Woo-hoo! Congrats to us. But now, we want to take it a step further and start deploying our databases at scale. But it could be tricky, because our databases could have unpredictable workloads. And it would be ideal to just reserve a pool of compute and then deploy our databases into that pool. And that's what Elastic pools do for us at the end of the day, right? Here we see SQL elastic pools as a resource that we can create and deploy.

1:13Let's start by clicking on that and actually creating a SQL Elastic pool. So we'll click the Create button, and it takes us in to the basics of deploying this configuration. I'll change my subscription. It automatically dumps me into my resource group that has the SQL resource groups in it. But you can choose the ones or create a new resource if that's what you want to do. Now, what we can do is we can choose an elastic pool that we want to create and deploy to. Notice though, pretty interestingly, how this all came together is trying to pick my server as the elastic pool, or that's going to be the authentication and connection point into the elastic pool.

1:55So think about this. If I'm here smiling on my computer in my office and I connect in over the public internet, I was going to write internet right here, into Azure, what am I connecting into? I am connecting into this Knox SQL server, and that Knox SQL Server is logically connected into the database that we deployed in the previous video that I reserved for $5 a month.

2:25But now I can also deploy an elastic pool of resources and have this still be my connection point to connect into in order to reach these databases that I want to deploy. So that's the idea that we're having here, keeping in mind that the SQL server really isn't so much a SQL Server in the traditional sense that you're used to administering, but rather it's just the connection point to get to this group of databases. Sure, you can spin up multiple SQL servers, doesn't cost you a penny to create the server itself.

3:02What you're reserving is the workloads for the individual databases. Now, here is where you specify what your pool is going to be. And guess what, it looks exactly like the previous options that we have, doesn't it? If we click on the Configure elastic pool, well, guess what, we still have general purpose and business critical, but we no longer have hyperscale. Thus, the only thing that's missing from this dropdown now is it removes hyperscale out of the mix, because the idea with hyperscale is you are trying to hyperscale a single database rather than a pool of databases.

3:40So I'm going to leave this at the general purpose as it is now, and that's really all you need to do to create your pool. So let's review and create this. Oh, I got validation field. Something messed up somewhere. Let's figure out where it's going. Oh, I forgot to get my pool name, duh. Let's call this azsql-- just got to spell SQL correctly though-- sqlpool. And now, we're OK to go. Now, let's go to review and create validation succeeds. And I'll create my Azure SQL pool of resources. So that'll take a moment to deploy this. And then we can deploy databases directly into this pool.

4:13Let's let that sit for a second and come back to it when it's done. So the Elastic pool gets deployed pretty quickly. I can go to the resource. And now, I actually have a menu where I can look at the elastic pool overview. Very, very cool to see this because I can see the resource utilization for the entire pool or, very cool, under the monitoring section if you scroll down just a little bit, you can see these storage. There's also database resource utilization which is pretty neat. You can see for individual databases what the CPU percentage, data space, Log IO, Workers, blah, blah, blah, blah, are.

4:47So that's actually a really, really interesting thing. And this is a great way to manage and maintain your overall pool. We can also set up maintenance windows and set up SQL health alerts too which is also pretty nice. There's even some quick starts to help you get started with connecting to your database, as well as using the SDK a four C# and other programming languages as well. All right, now that the pool has been deployed, let's start deploying resources to those pools. This is not going to look too unfamiliar from what we just did.

5:18I'll create a new SQL database here, choose the correct subscription, got in the right resource group. It automatically defaults to the correct server. I'm going to give this a new name like knox-sql-paas2. Because this is a unique database name, it can be linked up to the server. And I'm going to say, yeah, I want to use the Elastic pool the one I just created right there. So now I've got this SQL database as part of the elastic pool. And with everything else looking good, I can review and create this database.

5:50So now, I'm not paying for this necessarily at the individual database level but rather at the entire-- rather it's just coming from the prepaid pool, effectively, is what it's doing. So the deployment is in progress. And when I open up SSMS and connect to that SQL server, it should dump me right there into the pool. Now, with that being deployed, I'm also going to deploy one more database into the exact same pool at the exact same time. Look at this. I'm a madman like this. I'm going to call this knox-sql-paas3, got to give it a unique name.

6:26Leave everything the exact same. Yeah, I'm going to put it in the elastic pool that I created before, reviewing create and create. Boom, there we go. So now I've got two Azure SQL databases coming from that same pool as soon as this resource gets deployed. So once this resource is deployed, we will pull up SSMS, get connected to the SQL server, and validate that I see all of those three databases. Let's give it just a second to get done deploying. Deployment succeeds relatively quickly.

6:57It takes just a few seconds to get it up and running. But the ultimate test is, did it work? We're going to launch SQL Server Management Studio one more time from my platform item right here, and we'll get connected in to the actual database once SSMS gets done loading up. OK, so in this case, now, notice here 10.99.1.4. That was actually the infrastructure as a service virtual machine that we deployed a couple of videos ago. I got to site-to-site VPN set up between my office and that virtual network. It was just validating that I had connectivity.

7:28Let's jump back though to knocksql.database.windows.net. Type in the local admin credentials, get connected in and let's see what's under my databases. Now, if I expand it, ah, there they are. Now, I have three databases deployed. One was deployed with a basic deployment model, using DTUs. The other two was using vCore models in an elastic pool, all linked to the same logical SQL server. Isn't it beautiful?

7:59That's how you deploy Azure SQL databases in the platform as a service offerings using Elastic pools. I hope this has been informative for you, and I'd like to thank you for viewing.

0:05Now this is going to be the big one, where we're actually deploying kind of the mixture of both. This is the platform as a service offering that happens to attach itself to an Azure VNet, which is really the infrastructure as a service section of Azure itself. Remember the big thing here is that we are still abstracting away the need to manage the operating system, as well as the SQL server instance itself. But we get almost identical functionality to all of the things that SQL server can do.

0:38We get things like the temp dB, we get things like the SQL agent, and many other things along with that. So we're going to deploy this instance in this video. But I want to tell you right now, if you're starting from scratch, if you're building this up for the first time, this can take a very long time to deploy, especially if you have to do something like deploy a site-to-site VPN first. For instance, if you're deploying the VNet for the first time and all the resources to it for the first time, you're connecting the hybrid network connection using the site-to-site VPN for the first time, then deploying the Managed Instance on top of that, you might as well clear out your day.

1:15And it's not because it's a lot of work, it's because these resources take a very long time to deploy, sometimes hours, from the moment you click Start. So I'm just warning you right now. If you're going to be following along and doing this, first of all, you can rack up a big bill doing this. Second of all, it takes a long time. So just brace for impact on that one. All right, let's get going deploying an Azure SQL Managed Instance. Now, our final resource that we're going to deploy in the Azure SQL platform-- of course, I'm saving the best for last-- and that's going to be the Azure SQL Managed Instance.

1:49This is a big one to deploy. And I'm telling you, it takes a long time to deploy. Now I'll tell you right now one thing that I've already done. I've already got my HQ network, right here I am in my HQ. I have already connected it in to an Azure Virtual Network. The same virtual network that we deployed when I deployed the SQL virtual machines. Recall that the subnet for that virtual network was 10.99.1.0/24 bit mask.

2:22Let me write a two a little bit better than that. So this is what is the virtual network that I am targeting. And I want MySQL Managed Instance to get an IP address out of 10.99.1.0/24. And I want to be sitting here on my local SSMS launcher, connecting in to that Managed Instance using the private IP address. Now, I did kind of hint at that in the previous video. I've already validated that network connectivity works, because I can get to the Azure SQL VM that we deployed previously.

2:55I can connect in using SSMS, so network connectivity is already there. If you haven't gone through the process of deploying a site-to-site VPN yet using the virtual network and the virtual network gateways, the local network gateways, I'd encourage you to watch the content on CBT Nuggets on how to deploy that. That can be an expensive process, and it is very time consuming too. The virtual network gateway can take up to an hour to deploy.

3:25In my experience it's been about 30 minutes for just the resource to deploy. Then you have to go through the process of configuring both firewalls, or in this case, the virtual network gateway connection on both ends. And then validate the connection comes up and takes place. The connection itself can take several minutes to come to life, and you'll spend something like five minutes troubleshooting why isn't it up. It will come up, you just got to give it some time to actually breathe and get there. So with all that being said, I have network connectivity between my on-premises and the virtual network in place already.

4:00And now I can deploy the SQL Managed Instance. So we have an option here, single instance or single instance with Azure Arc. You may be wondering what is Azure Arc. There are some types of specific servers that you in your HQ can deploy that server in your HQ, and manage that server from Azure, right here. So when you want to deploy a new virtual machine, you go to the Azure portal and you click on a new virtual machine.

4:35But you can tell it to deploy down here on your on-premises server. That is all enabled using the Azure Arc service. So when you see here that you can deploy a SQL Managed Instance with Azure Arc, you should really be thinking, I can deploy a SQL Managed Instance from the Azure portal into my on-premises resources because those on-premises are connected via Azure Arc.

5:05It's just a hybrid management option that's helping people get more into Azure for their management even if they're managing on-premises resources. In my case, I'm choosing single instance because I'm trying to deploy this in the cloud. So I'll click Create and it takes me into the management section. And this is where it gets kind of fun. I'm going to choose Pay as you go. And I'm going to choose My resource group, the one that has MySQL IaaS resource group. Now, this is the interesting part, you cannot deploy this in all regions.

5:37There are only specific regions that you can deploy this in. In fact, sometimes you'll choose a region like East US or East US 2, and it'll tell you the Managed Instance is not available for the chosen subscription and the region. You have to request that region access. Kind of interesting, right? Well, in my case, I'm going to choose South Central US, and that warning goes away. So now I have the ability to put that on the virtual network in the South Central US that's in that resource group.

6:10I'm going to call this sql-mi for the Managed Instance name. Oh, but that specified server name is already in use. How about sql-mi-knox? Yeah, there we go. Now we've passed the test. So SQL Managed Instance Knox, that is not a Managed Instance that is in use. The Managed Instance size, no, this is big, 8 vCores, 256 gigs of storage-- that's kind of small-- Geo-redundant storage. Let's click on Configure Managed Instance here, and you and see your estimated cost for this general purpose virtual machine.

6:43Using just the standard series of everything, we're looking at $1,681 a month for the eight virtual cores and 256 gigs of storage. If I ratchet this down to four virtual cores, it does cut the cost in half. You can apply your own license, bring your own license for a Azure hybrid benefit, and you can choose your backup storage redundancy. Again, we are talking platform as a service here, and Azure will automatically take backups of the storage.

7:15Now this is kind of another interesting thing that we haven't really hinted at because it is a very specific nuance. When you set your storage for the database itself, like 256 gigs, you get that amount of storage provided free. So if you have 256 gigs of storage for the server, then you will get that much storage for the backups provided in addition completely free. Anything over this amount is where you start to rack up the charge.

7:50So that is the one thing that I wanted to point out just so it is a nuance to the platform as a service offerings. Now that being said, I'm going to click Apply here, so we have the sizing specified. The authentication, I am going to do, yet again, select both SQL and Azure AD authentication. I'll choose my admin here as Knox, let's go ahead and choose Knox. And I'll specify an instance log in for a local credential, just so that we've got it, type in the password here.

8:20But I got to type it correctly, that's an important factor for deploying databases. All right, well, no this is interesting, the password must be 16 characters in length. So my password isn't long enough. So let's do this, let's do an extra one. Is that good enough? One-- no. Yeah, OK, no, no, I just needed to add an additional character there of my password's 15 characters now and 16 characters. OK, now let's go to the Networking section. I want to make sure that this gets put on my other virtual network that is ready for a Managed Instance.

8:54But notice here my virtual networks are not ready for a Managed Instance. Well, what's this all about? One of the interesting requirements that's actually pointed out in the connectivity architecture of the Managed Instance is right here, it has to be a dedicated subnet. No other resources are allowed to be on the same subnet. Right here, the subnet can't contain any resource but Managed Instances. And you can't later add other types of resources.

9:25Well, hang on a second, let's look at this. In my subnets, what did we do? In the first subnet, my default subnet, we deployed a virtual machine and the gateway subnets used for my VPN. So I have to go back and create a new subnet right now that this Managed Instance can use. So let's do that. I'm going to go back to the basics here for one second. And you know what? This isn't-- It's going to-- I'm going to have to blow this thing up. I'm just going to come back to this. Give me one second while I create a new virtual network. Here I'm going to jump into the VNet real quick, go down into my subnets.

9:58We'll create a new subnet real quick, I'll call this Managed Instance. The subnet range 10992 is perfect. I'll click Save. I am going to have to go back and edit my VPN, because this is going to break my VPN since they do not agree on the subnets that are in use right here. So I'll fix my VPN real quick, and then we'll be able to proceed. So I've just reconfigured the VPN just to make sure if I ping 10.99.1.4, I get return traffic. So my VPN on my local computer is looking good to go.

10:32So now I can go back to the actual determination of where we actually deploy the SQL virtual machine Managed Instance. So let me jump back to where we were on the network configurations, and we'll go from there. OK, so now I am back on the actual virtual networking pane. Notice it's trying to create a new virtual network called knox-sql-mi. But now when I click the dropdown, I do have a new subnet called in MySQL iaas-group-vnet for the Managed Instance itself.

11:05So this is good. Now we actually have a subnet that will be reconfigured to work for the Managed Instance once the connection has actually been applied and the resource has been deployed. The connection type for the private endpoint is still going to be set to be a proxy, that way we try to hit the resource itself. But it's actually going to go to a central Microsoft location and then be proxy to it. We don't have a public endpoint deployed for this in this case, we're just going to leave that one alone.

11:36And of course, the minimum TLS version is going to be 1.2. Checking out the Security section here, this is where you could actually add Microsoft Defender for SQL, as well as Identity Services if you need to add the actual system assigned identities to create a service principal for Windows authentication and Microsoft Active Directory. We don't need to do any of those things right now. We're going to cover all of that in a later video. We're going to jump straight to Review and create at this point. And we see, again, this is going to be a cost per month, and how it's going to be deployed.

12:09If I click Create, we'll let it get off to the resources. Now, again, this takes a long time, so just let it sit. Go to lunch, go do something else because it's going to be a while before this resource comes to life. Come back and check on it in an hour or so. And then once this is done deploying, we'll pick back up from there and make sure that we can actually connect to this over the site-to-site using the private IP. All right, see, I'm closing in on the hour mark.

12:41I started this at 11:25 AM, it's now 12.19. So it's probably close to getting done, but it's not done yet. So it's going to keep letting and spin until it finally gets deployed. See, I thought it would actually be worthwhile to show you what's going on here. You see what's actually happening in this deployment under the hood is it's actually creating a virtual cluster. And its creation is a synchronous step in incident management operations. Ultimately the estimated duration of this, 90% of operations finish in four hours.

13:16So this is just going to run for a really long time, that's what they're basically saying here. Now, once you've created a virtual cluster on the subnet for the first time, the next time you want to deploy a new Managed Instance to that same subnet, it should go quite a bit quicker. But that being said, the very first virtual cluster creation on the subnet, it's going to take up to four hours. So still just playing the waiting game. So just checking in, we're at a little over three hours now. Hopefully it'll be done within the hour.

13:47And we'll come back to it when it's finally deployed. Well, I'll be maybe just a few minutes after I just recorded that last clip, looks like about four minutes after I recorded that last clip, I get the fateful your Managed Instance deployment is complete. Let's go to the Resource to check it out. So here it is, we have now deployed, in our resource group, a Managed Instance. It has the series that we specified here, it is running on a virtual cluster, it's in the subscription.

14:18Let's scroll-- first of all check this out, the host is resolvable to database.windows.net. So this is why it's using the proxy, is it still accessible via a public or fully qualified domain name. So the big win for us here is that we've deployed a platform as a service SQL instance, rather than just an individual database. Now we can manage the entire SQL server instance. And check this out, we also have the ability to deploy multiple databases to this given instance.

14:51That's kind of one of the tricks here. This is almost like it's working like an Elastic pool, except for we have a full blown cluster of resources and access to nearly like 99% of all the features that you would get from deploying just a standard virtual machine. So we can deploy a new database right here directly to the Managed Instance, like so. We can walk through the process, we can say your subscription to the resource, we're going to enter the database name, like imdb for instance database.

15:23Something very simple like that. If you want, you can look at the data source itself, we can restore the data to a new database from a backup, if that's what we wanted to do. If we had an existing one. Otherwise, we can just do review and create, and create a new database on this Manage Instance directly from this portal. So let's deploy a database real quick. So that database resource gets deployed very, very quickly. We can go to the resource itself. And we can actually explore some of the information about the database.

15:53Including we can also apply some security features like data discovery and classification, as well as Microsoft Defender for Cloud. These are things that we will be talking about later on in our journey. You can also configure backup retention right here if we need to change our retention settings for this specific database. All right, let's jump back into the SQL instance for a second, though, the managed SQL instance. And what we can do-- now I see my database, and it's online when it was created, and the restoration for it. We can also see some of the statistics like the CPU for the Managed Instance.

16:24We also see there's a built-in firewall. So let's see if we can actually go ahead and get connected into this. This firewall feature-- by the way, I just said that. This firewall feature is just part of the virtual network. When this was deployed into the subnet that it was deployed on, it also scaffolded some extra resources. Like a built-in firewall that will detect intrusions and protect you. So here's what I'm going to do, I'm going to choose to connect to the actual SQL instance by choosing this proxy host in order to connect into it. I'll bring up SQL Server Management Studio, and I'll click Connect.

16:58Here I'll paste in the actual endpoint here, and I'll specify the password, type that again, with 16 characters long to get connected in. Just like that I see that I'm connected, and there's the imdb that I created before. Now you may be thinking to yourself, wait, didn't you say we wanted to connect to this through the private IP address that's on the virtual network? That is correct. And you can do that if you scroll down to deploy a private endpoint connection right here. It tells you a private endpoint connections allow connections from within a virtual network to a private IP using the private IP endpoint feature.

17:37Connections to these private endpoints specified below provide access to all databases on the server. So if I create a private endpoint real quick, I can choose the resource group, I can give it the name like sqlmipriv, or something like that. It's going to create a new network interface. And I'm going to choose to put this in the South Central US where my particular resources are. I'll choose these specific resource that I want to specify, and there we go, there's the sub resource. It is a Managed Instance, so it automatically detected I'm trying to use a Microsoft SQL Managed Instance.

18:10The virtual network that I want to put this in, well, we can see it's going to put me on this particular subnet. I could choose a different subnet, like the default subnet, if that's what I wanted to do. So if I want my virtual machines on 1099.1 to be able to connect directly to this item, this is the private endpoint that it's going to get an IP address on. Let's go on to DNS next, OK. Do we want to integrate with a private DNS zone? If you either are using the Microsoft private DNS zones, you can go ahead and specify that here.

18:45Now, this particular object already has a DNS record associated with it, because that's what we just use to connect in. But you can utilize your own DNS servers or create your own DNS records using the host files if you don't want to integrate with the private DNS zones. I don't need to do anything about tags right now, so I'll go to Review and create, and create my private endpoint on that virtual network. So the private endpoint is completed. We can wrap up this configuration by jumping back to the SQL Managed Instance, scrolling on down to the private endpoint connections.

19:19And we see now I have a private endpoint connection. It's got a connection name, it's kind of long. It's state is in approved, which is what we need. And I see the private endpoint name here. If we click on the Private endpoint name, we see that this is where all of the details of the connection are. Interestingly, you can't find the IP address, though, you have to jump down here to like the DNS configuration. And now you see there's the Nick, there's the IP address that it got from connecting in, and the fully qualified domain name which, again, is the same domain name that we use to connect in in the original place.

19:56Woo-hoo, so we've now created the actual private DNS configuration, or we've created a private endpoint for our SQL Managed Instance. And now the virtual machines or other objects that connect into that VNet can connect directly in. So check this out. I've actually promoted into the virtual machine that I have running in Azure right now. I want you to see how this routing is going to work. You see, previously when I was just getting connected in right now, I was getting connected in through my public internet.

20:28Right here over the public internet, it was routed by public resolution, and it came to a public IP that got proxyed inbound. But what about what happens when I try to connect in via my local devices now that are on the same virtual network? If I bring up my database engine that's on my RDP session here, I've already pasted in the Managed Instance endpoint name right here. Let me go ahead and get connected to see how this DNS resolution works.

20:59If I press Enter here, well, to you, to the naked eye, it looks like nothing happened. It looks like I just got connected the same way I always do. But check this out, I'll bring up a command prompt real quick, and I'm going to zoom in quite a bit. I'm going to run the command nslookup to see what DNS resolves to whenever I actually try to get connected to that particular address. Let me just do disconnect real quick, reconnect so I can copy this DNS name real quick by fully qualified domain name. When I paste it and I press Enter, look at this, right there, the end response that I got was a different DNS name, an alias name, the proxy that was getting deployed.

21:39And it resolved to the private IP address that's on the same virtual network, 10.99.254. This is a different subnet, but it's the same virtual network. And all of my devices on that virtual network have direct connectivity to each other, thanks to the routing table. So this is what we're pointing out here, is that when we actually deploy the private endpoint and resources within the same virtual network, it does, in fact, allow for local resolution of the private IP address, and therefore local secure connectivity.

22:11All right, so this has been deploying the Managed Instance. I hope this has been informative for you, and I'd like to thank you for viewing.

0:06So there you go. Now we've actually deployed all of the various offerings when it comes to the Azure SQL platform. We took a look at them in the previous video when were evaluating the three different options. There was the platform-as-a-service Azure SQL Database, there was the platform-as-a-service manager instance, and then there was the infrastructure-as-a-service Azure SQL Virtual Machine. Now you've seen, at a very high level, how to deploy all three of those in the manual methods, and this is perfectly fine to deploy these in this particular manner if you're not deploying resources all the time.

0:40If you are deploying resources all the time at scale, chances are, you want to look into some automation capabilities. And that's why, in the next set of videos, we're going to look at how to deploy these resources using the automated methods-- things like templates, things like PowerShell, things like AZ CLI. So get ready as we start to explore that in the next set of videos. For now, I hope this has been informative for you, and I'd like to thank you for viewing.