How To Deploy an Azure SQL Database using Terraform

Moving on to Terraform seems to be an easy transition after working on some ARM Templates.  The Terraform configuration file is a little easier than a JSON file to read and work with.  Depending on what you want to deploy you would have a section for each compute resource or back end resource, i.e. storage account, resource group or a virtual machine.

In this article I will walk through a Terraform configuration file to deploy an Azure SQL database in an Azure Resource Group along with a storage account used for setting up a threat detection policy.  I am also calling a PowerShell command in the configuration file to setup the long term retention backup policy for the database.

First, setup the Terraform executable.  Terraform has a good step by step guide on installing the executable here.

Initialize Terraform

After setting up the Terraform executable in a folder, the first thing to do is to initialize Terraform.  At a command prompt or in a terminal window in VS Code, type in Terraform Init and hit enter.  Once it is initialized you are ready to begin.

Terraform Provider section
The first section is the provider section, this is where you specify the cloud provider.  For this example, I am using the Azure provider.  A list of all the providers can be found here.  In this section, you also specify the subscription ID and the Tenant ID.

provider "azurerm" {
version="=1.20.0"

subscription_id=""
tenant_id=""
}

Terraform Resources

The majority of the Terraform configuration file will consist of resource blocks.  The resource block consists of either the parameters or variables needed to deploy that resource.    Below, I will try to point out the resources I am using to deploy the SQL database.  Terraform will keep this state of the resource so that it maintains that same configuration through out your development process.

Resource Group

The first resource I have in the configuration file is to deploy a resource group.  In this one I am just giving it the name of the resource group along with the location.

resource "azurerm_resource_group" "test2" {
  name     = "RGTFDBGRL93"
  location = "North Central US"
}

Storage Account

Next, I am creating a storage account.  This one has a bit more detail to it.  Here you can see, I am giving it a name, telling it which resource group to deploy to along with location.  Since there are different types of storage accounts,  I need to tell it to create a standard storage account.

resource "azurerm_storage_account" "test2sa" {
  name                     = "satfsqldbdiag01"
  resource_group_name      = "${azurerm_resource_group.test2.name}"
  location                 = "North Central US"
  account_tier             = "Standard"
  account_replication_type = "GRS"
}

Logical SQL Server for Azure SQL Database

OK, now that some of the requirements are done.  We can get into the heart of creating an Azure SQL Database.  One thing about an Azure SQL Database is that it still uses a SQL Server, meaning there is a logical SQL Server instance created when deploying an Azure SQL Database.

In this next section, I point out the details of the SQL Server instance, name, resource group, location, along with the administrator login and password.  This login and password would be the equivalent of the sa account and password in a SQL Server instance on premise.

With Terraform there are a few things that can’t be configured in the resource block.    For this, I am adding a local-exec provisioner within the SQL Server resource group.  The Provisioner section gives me a way to call PowerShell commands to do some of the configuration work I need done to the database.

resource "azurerm_sql_server" "test2" {
  name                         = "sqldbsrvrtf01"
  resource_group_name          = "${azurerm_resource_group.test2.name}"
  location                     = "North Central US"
  version                      = "12.0"
  administrator_login          = "sqldbadmin"
  administrator_login_password = "chang3Me!"

  provisioner "local-exec" {
    command     = "Set-AzureRmSqlServerAuditing -State Enabled -ResourceGroupName ${azurerm_resource_group.test2.name}  -ServerName 'sqldbsrvrtf01' -StorageAccountName ${azurerm_storage_account.test2sa.name}"
    interpreter = ["PowerShell", "-Command"]
  }
}

Server Firewall Rule

Now that the logical SQL Server is set, we can give it the firewall rules.

resource "azurerm_sql_firewall_rule" "test2" {
  name                = "FirewallRule1"
  resource_group_name = "${azurerm_resource_group.test2.name}"
  server_name         = "${azurerm_sql_server.test2.name}"
  start_ip_address    = "10.0.17.62"
  end_ip_address      = "10.0.17.62"
}</code

Azure SQL Database

Now, to the SQL Database.  You can see here, the resource blocks are pretty much the same, give it a name, resource group and location.

Notice, at the end of this block, I added a PowerShell command to set the long term backup retention policy for the database.  This will give you the ability to set monthly or weekly backup retention plans to fit your needs.

resource "azurerm_sql_database" "test2" {
  name                = "sqldbtf01"
  resource_group_name = "${azurerm_resource_group.test2.name}"
  location            = "North Central US"
  server_name         = "${azurerm_sql_server.test2.name}"

  threat_detection_policy {
    state                      = "Enabled"
    email_addresses            = ["dbgrl93@gmail.com"]
    retention_days             = "30"
    storage_account_access_key = "${azurerm_storage_account.test2sa.primary_access_key}"
    storage_endpoint           = "${azurerm_storage_account.test2sa.primary_blob_endpoint}"
    use_server_default         = "Enabled"
  }

  provisioner "local-exec" {
    command     = "Set-AzureRmSqlDatabaseBackupLongTermRetentionPolicy -ResourceGroupName ${azurerm_resource_group.test2.name}  -ServerName ${azurerm_sql_server.test2.name} -DatabaseName 'sqldbsrvrtf01' -WeeklyRetention P12W -YearlyRetention P5Y -WeekOfYear 16 "
    interpreter = ["PowerShell", "-Command"]
  }
}

Basically, that is all you need. I kept it pretty simple here. Ideally you may want to create some variables and maybe some modules to that the code is a little more reusable and robust. But this is a good start to understanding Terraform.

One of the unique features of Terraform is that it creates a plan, sort of like an execution plan of the resources. When you enter Terraform Plan in the command prompt, it will list out all the resources it will deploy.

When ready, enter Terraform Apply to deploy the resource(s).

That’s about it.  It turns out to be a pretty simple process.  Next time, I will go over deploying a SQL Server virtual machine in Azure.

Hope this helps!!!

How to Deploy A SQL Server Virtual Machine in Azure – Part 3

For part 3 on deploying a SQL Server virtual machine, I am focusing on using ARM (Azure Resource Manager) Templates to build a virtual machine with SQL Server 2016 installed on it.  I used this Microsoft article as a reference.

ARM templates use the JSon (Java Script Object Notation) format.  There are a total of 3 files, 2 JSon files and a PowerShell script file.  There is a JSon file for the parameters and another file for the information on what to deploy.  The PowerShell script references the ARM files and executes the command New-AzureRmResourceGroupDeployment.

I go into some detail on the sections of the ARM Template in my article “How to Deploy an Azure SQL Database – Part 3” .  To deploy a VM, you would still create the template with the same sections, just specifying different resources.

The first section contains the Parameters. This is a fairly large list of parameters, but to create the VM with SQL Server you will need to specify things like the SQL connectivity type, SQL Server port number, disk for the database files and information on setting up the database backups for example. In the ARM Template you define the parameters and the type, in the parameters file, you specify the actual values. And that would look like

“location”: {
“value”: “northcentralus”
},

"parameters": {
        "location": {
            "type": "string"
        },
        "virtualMachineName": {
            "type": "string"
        },
        "virtualMachineSize": {
            "type": "string"
        },
        "adminUsername": {
            "type": "string"
        },
        "virtualNetworkName": {
            "type": "string"
        },
        "networkInterfaceName": {
            "type": "string"
        },
        "networkSecurityGroupName": {
            "type": "string"
        },
        "adminPassword": {
            "type": "securestring"
        },
        "diagnosticsStorageAccountName": {
            "type": "string"
        },
        "diagnosticsStorageAccountId": {
            "type": "string"
        },
        "diagnosticsStorageAccountType": {
            "type": "string"
        },
        "diagnosticsStorageAccountKind": {
            "type": "string"
        },
        "subnetName": {
            "type": "string"
        },
        "msiExtensionName": {
            "type": "string"
        },
        "sqlConnectivityType": {
            "type": "string"
        },
        "sqlPortNumber": {
            "type": "int"
        },
        "sqlStorageDisksCount": {
            "type": "int"
        },
        "sqlStorageWorkloadType": {
            "type": "string"
        },
        "sqlStorageDisksConfigurationType": {
            "type": "string"
        },
        "sqlStorageStartingDeviceId": {
            "type": "int"
        },
        "sqlStorageDeploymentToken": {
            "type": "int"
        },
        "sqlAutopatchingDayOfWeek": {
            "type": "string"
        },
        "sqlAutopatchingStartHour": {
            "type": "string"
        },
        "sqlAutopatchingWindowDuration": {
            "type": "string"
        },
        "sqlAutobackupRetentionPeriod": {
            "type": "string"
        },
        "sqlAutobackupStorageAccountName": {
            "type": "string"
        },
        "sqlAutobackupStorageAccountType": {
            "type": "string"
        },
        "backupSystemDbs": {
            "type": "string"
        },
        "backupScheduleType": {
            "type": "string"
        },
        "fullBackupFrequency": {
            "type": "string"
        },
        "fullBackupStartTime": {
            "type": "string"
        },
        "backupTimeWindow": {
            "type": "string"
        },
        "logBackupFrequency": {
            "type": "string"
        },
        "sqlAuthenticationLogin": {
            "type": "string"
        },
        "sqlAuthenticationPassword": {
            "type": "securestring"
        },
        "rServicesEnabled": {
            "type": "string"
        }
    },

The next section, Variables, is used more to build values such as the VM name or storage accounts. In my file I am getting the VirtualNet ID and the SubNet reference.

"variables": {
        "vnetId": "[resourceId('RG-DBGRL93-01P','Microsoft.Network/virtualNetworks', parameters('virtualNetworkName'))]",
        "subnetRef": "[concat(variables('vnetId'), '/subnets/', parameters('subnetName'))]"
    },

Next is the heart of the ARM Template, the resources. The first part, I define the properties for the virtual machine. You will need to define the name of the VM, then the type, Microsoft.Compute/virtualMachines. Moving down the file, you specify the hardware profile along with the storage profile. The storage profile is where you specify the SQL Server Image you want to deploy. Then on to the OS disk and data disks. For SQL Server, make sure you will want to setup Managed Premium disks to get better performance on the database(s).

    "resources": [
        {
            "name": "[parameters('virtualMachineName')]",
            "type": "Microsoft.Compute/virtualMachines",
            "apiVersion": "2018-06-01",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[concat('Microsoft.Network/networkInterfaces/', parameters('networkInterfaceName'))]",
                "[concat('Microsoft.Storage/storageAccounts/', parameters('diagnosticsStorageAccountName'))]"
            ],
            "properties": {
                "osProfile": {
                    "computerName": "[parameters('virtualMachineName')]",
                    "adminUsername": "[parameters('adminUsername')]",
                    "adminPassword": "[parameters('adminPassword')]",
                    "windowsConfiguration": {
                        "provisionVmAgent": "true"
                    }
                },
                "hardwareProfile": {
                    "vmSize": "[parameters('virtualMachineSize')]"
                },
                "storageProfile": {
                    "imageReference": {
                        "publisher": "MicrosoftSQLServer",
                        "offer": "SQL2016SP1-WS2016",
                        "sku": "Standard",
                        "version": "latest"
                    },
                    "osDisk": {
                        "createOption": "fromImage",
                        "managedDisk": {
                            "storageAccountType": "Premium_LRS"
                        }
                    },
                    "dataDisks": [
                        {
                            "createOption": "empty",
                            "lun": 0,
                            "diskSizeGB": "1023",
                            "caching": "ReadOnly",
                            "managedDisk": {
                                "storageAccountType": "Premium_LRS"
                            }
                        }
                    ]
                },

For a SQL Server deployment, you will also need to setup and configure the SQLIaaS Agent extension. This extension provides the Azure portal interface for SQL Server. You will also need it for the managed database backups along with the automated patching settings.
In this section, I am defining the Auto Patch settings along with the Auto Backup settings.  With the backups, we can set the backup retention settings, how often to run log backups and the schedule type (manual or automated).  I like to include these settings in the template, so that the server has everything configured and it is ready to go.

{
            "apiVersion": "2015-06-15",
            "type": "Microsoft.Compute/virtualMachines/extensions",
            "name": "[concat(parameters('virtualMachineName'), '/SqlIaasExtension')]",
            "location": "[parameters('location')]",
            "dependsOn": [
                "[concat('Microsoft.Compute/virtualMachines/', parameters('virtualMachineName'))]",
                "[concat('Microsoft.Compute/virtualMachines/',parameters('virtualMachineName'),'/extensions/', parameters('msiExtensionName'))]"
            ],
            "properties": {
                "type": "SqlIaaSAgent",
                "publisher": "Microsoft.SqlServer.Management",
                "typeHandlerVersion": "2.0",
                "autoUpgradeMinorVersion": "true",
                "settings": {
                    "AutoTelemetrySettings": {
                        "Region": "[parameters('location')]"
                    },
                    "AutoPatchingSettings": {
                        "PatchCategory": "WindowsMandatoryUpdates",
                        "Enable": true,
                        "DayOfWeek": "[parameters('sqlAutopatchingDayOfWeek')]",
                        "MaintenanceWindowStartingHour": "[parameters('sqlAutopatchingStartHour')]",
                        "MaintenanceWindowDuration": "[parameters('sqlAutopatchingWindowDuration')]"
                    },
                    "AutoBackupSettings": {
                        "Enable": true,
                        "RetentionPeriod": "[parameters('sqlAutobackupRetentionPeriod')]",
                        "EnableEncryption": false,
                        "BackupSystemDbs": "[parameters('backupSystemDbs')]",
                        "BackupScheduleType": "[parameters('backupScheduleType')]",
                        "FullBackupFrequency": "[parameters('fullBackupFrequency')]",
                        "FullBackupStartTime": "[parameters('fullBackupStartTime')]",
                        "FullBackupWindowHours": "[parameters('backupTimeWindow')]",
                        "LogBackupFrequency": "[parameters('logBackupFrequency')]"
                    },

When both the template and parameter JSON files are ready, you can go on to create a PowerShell script. I first setup the password for the admin account. Then call the New-AzureRmResouceDeployment command. For this command, you just need to specify the Resource Group to deploy to and then tell it where the Template file is and the Parameter file. Save the script and you are ready to run it.

$secpasswd = ConvertTo-SecureString "ch@ng3MeQ" -AsPlainText -Force

New-AzureRmResourceGroupDeployment -adminPassword $secpasswd -ResourceGroupName 'RG-DBGRL93-01P' -TemplateFile 'C:\DBAInTheCloud\Code\DBAInTheCloud2\ARMTemplates\DBA_ARMTmplt_SQLVM.json' -TemplateParameterFile 'C:\DBAInTheCloud\Code\DBAInTheCloud2\ARMTemplates\DBA_ARMParams_SQLVM.json'

It will take a few minutes to deploy the VM. Once it is deployed, you are ready to connect to the SQL Server and setup your databases.

That’s it for this series. The next set of scripts will focus on using Terraform scripts to deploy to Azure.

Happy scripting!!!!
DBA Work Matters

How to Deploy A SQL Server Virtual Machine in Azure – Part 2

For this series, I am focusing on deploying a virtual machine (VM) using three different methods.  Part 1 focused on using the Azure Portal.  Part 2, this post, will go over how to deploy a VM using a PowerShell script.  For the final part, Part 3, I will focus on using ARM (Azure Resource Manager)  templates for deploying virtual machines. For reference, I used Microsoft’s Quickstart article on how to deploy a VM.

To deploy a VM you will need to make sure you have a couple of things setup prior to running the script.

  1. Resource Group
  2. Virtual Network (VNet)
  3. SubNet

First, declare some variables.

$ResourceGroupName = "RG-Name-01"
$VNetName = "vnet-dba-vm-name"
$SubNetName = "snet-dba-vm-name"
$VMName = "VMDBAITCSQL01"
$Location = "North Central US"

You will need to get the VNet information

$VNet = Get-AzureRmVirtualNetwork -Name $VnetName -ResourceGroupName $ResourceGroupName
$SubnetID =  Get-AzureRmVirtualNetworkSubnetConfig -Name $SubnetName -VirtualNetwork $VNet
$InterfaceName = $VMName + "-nic"

$Interface = New-AzureRmNetworkInterface -Name $InterfaceName `
   -ResourceGroupName $ResourceGroupName -Location $Location `
   -SubnetId $SubnetID.Id  

Setup and create a data disk on the VM. This disk will be for the database and log files.  For SQL Server database files, you will want to use premium managed disks.  Azure will setup folders for the data and log files automatically.  On a basic install I have done it is usually F:\Data & F:\Log.

$storageType = 'Premium_LRS'
$dataDiskName = $vmName + '_datadisk1'

$diskConfig = New-AzureRmDiskConfig -SkuName $storageType -Location $Location -CreateOption Empty -DiskSizeGB 1000
$dataDisk1 = New-AzureRmDisk -DiskName $dataDiskName -Disk $diskConfig -ResourceGroupName $ResourceGroupName

Next, you will need to define an admin account, or sa account. The sa account is disabled by default in the SQL Server VM in Azure. The account I am creating here will act as the sa account. This is also the account you can use when using RDP to get on the virtual machine.

$SecurePassword = ConvertTo-SecureString 'Change.Me2019' `
-AsPlainText -Force
$Cred = New-Object System.Management.Automation.PSCredential ("sqlvmadmin", $securePassword)

Now, we are ready to configure and create the VM.  When configuring the VM, you specify the size of the server, publisher, offer, SKU and version.  In this case it is the latest version of Microsoft SQL Server 2017.  The SKU is the developer edition of SQL Server.

# Create a virtual machine configuration
$VMConfig = New-AzureRmVMConfig -VMName $VMName -VMSize "Standard_D2s_v3" | `
   Set-AzureRmVMOperatingSystem -Windows -ComputerName $VMName -Credential $Cred -ProvisionVMAgent -EnableAutoUpdate | `
   Set-AzureRmVMSourceImage -PublisherName "MicrosoftSQLServer" -Offer "SQL2017-WS2016" -Skus "SQLDEV" -Version "latest" | `
   Add-AzureRmVMNetworkInterface -Id $Interface.Id | `
   Add-AzureRmVMDataDisk -Name $dataDiskName -CreateOption Attach -ManagedDiskId $dataDisk1.Id -Lun 1

# Create the VM
New-AzureRmVM -ResourceGroupName $ResourceGroupName -Location $Location -VM $VMConfig

In order to automate management of some SQL Server tasks, such as running and managing backups and automatic patching, you will need to setup the SQLIaaS extension. You can find more information here.  Installing the extension enables the SQL Server configuration option under the server blade in the Azure portal.

To do this in Powershell

# Setup the SQL IaaS Agent
Set-AzureRmVMSqlServerExtension -ResourceGroupName $ResourceGroupName -VMName $VMName -name "SQLIaasExtension" -version "1.2" -Location $Location

At this point you can stop here and run the script and it will create the VM in Azure with SQL Server installed. You can take it a step further and install the SQL Server IaaS extension, which will enable Azure to manage the full database backups along with the transaction log backups.

With the New-AzureRmVMSqlServerAutoBackupConfig command you can setup the backup retention period,the frequency and schedule of the backups. It does require a storage account, so in this script, I am creating a storage account first and then running the backup configuration.

#Setup a storage account for DB backups and then enable automated backups in SQL Server
$saName = $VMName.ToLower()
$storage_accountname = "sa"+ $saName

$storage = New-AzureRmStorageAccount -ResourceGroupName $ResourceGroupName `
    -Name $storage_accountname -SkuName Standard_GRS -Location $Location

$autobackupconfig = New-AzureRmVMSqlServerAutoBackupConfig -Enable `
-RetentionPeriodInDays 14 -StorageContext $storage.Context `
-ResourceGroupName $ResourceGroupName -BackupSystemDbs `
-BackupScheduleType Manual -FullBackupFrequency Daily `
-FullBackupStartHour 20 -FullBackupWindowInHours 2 `
-LogBackupFrequencyInMinutes 60 

Set-AzureRmVMSqlServerExtension -AutoBackupSettings $autobackupconfig `
-VMName $VMName -ResourceGroupName $ResourceGroupName

Now you are ready to run the script as you would any other PowerShell script. The script will take a few minutes to deploy the server. Once it is completed you will be able to begin working with SQL Server.

Look for the next post on ARM Templates.

How to Deploy A SQL Server Virtual Machine in Azure – Part 1

In this series of blogs, I will be focusing on deploying SQL Server virtual machines to Azure.  Part 1 will cover how to deploy using the Azure Portal, part 2 will focus on using PowerShell and finally part 3 I will use an ARM (Azure Resource Manager) Template.  Each of these will create the same server, just showing the different ways it can be done.

Deploying a SQL Server virtual machine is part of Azure’s IaaS (Infrastructure as a Service) offering.  Typically in IaaS deployments the configuration and administration is left up to the administrators or IT department.

By deploying SQL Server virtual machine using the Azure Marketplace, Microsoft sets up

  • Automated Updates – Azure gives you the ability to configure automated patching times
  • Automated Backups  – Azure manages the database backups to a storage account
  • High Availability – Azure provides Availability Group templates to deploy SQL Server in an High Availability group

Let’s get started

From the Portal Dashboard, click on Create a resource and enter SQL Server 2016 in the search bar and a list of options will come up.  For this example, I am selecting the Free License edition.

The Legal Terms blade will appear

When ready, click on the Create button.

The Create Virtual Machine Blade will appear to begin entering the information for the server.

You will need to supply a unique server name.

Give it an admin account and password.  This account will be added to the Local Administrators Group on the VM.  I typically will use this one to initially login into the VM using RDP (Remote Desktop Protocol).

Specify the Resource Group to place the server in.  If needed, you have the option to create a new resource group.  I am using the existing Resource Group.

Select the location to deploy the VM.  I am selecting the North Central location for this server.  It is recommended to choose the location closest to the users or location where the server will be accessed from most frequently.

When ready click on OK

The next blade will ask you to select the size of VM you want to deploy.

In this example I am selecting a D2s_v3, click on Select.

The next blade is for configuring optional settings

High Availability, here you can select an Availability Zone and/or an Availability set.  In my example, the Availability Zone is grayed out because the North Central US location is unable to support that.  I will not be selecting an Availability Set in this example.  For SQL Server to be highly available, we will need to deploy High Availability Groups for SQL Server.

Microsoft has a good article explaining availability sets here  stating that “An availability set is a logical grouping of VMs within a datacenter that allows Azure to understand how your application is built to provide for redundancy and availability.

Where as an Availability Zone is explained here, where ” Availability Zone is a physically separate zone within an Azure region. There are three Availability Zones per supported Azure region. “

When deploying a SQL Server VM the recommended practice is to Enable the use of Managed Disks.  Azure can then manage the best performance settings for the databases on the disks.

You will then need to configure the Network Settings. Organizations may have this setup and you may need to talk to the Network team to get the specifics for your deployment.  In this example, I am having Azure create the configuration and accepting the defaults.

  • Virtual Network
  • Subnet
  • Public IP
  • Network Security Group (NSG)
  • Extensions
  • Monitoring

Click on OK when all the information is entered.

The next blade that comes up is for the SQL Server settings.

SQL Server Port, by default SQL Server uses 1433.  This can be changed according to your standards.

SQL Authentication, here is where you can setup the equivalent of an sa account.  The SQL Server VMs from the Azure Marketplace disable the sa account.  In this example, I am using the same account I created previously in the server configuration blade.

SQL Managed Backups.  Here is where we can setup the backups for the SQL Server databases and have Azure manage them.  For this, we will need to give it a storage account for the backups.  We can also configure the backup retention time frame in addition to manual schedules if needed.  With the SQL Managed Backups, there is no need to create separate backup jobs in the SQL Server Agent.

The last blade will ask to verify the configuration.

When ready click on Create to begin the deployment.

It may take a few minutes to deploy the server.  Once it is ready, you can remote into the server and complete your configurations for SQL Server.

How To Deploy an Azure SQL Managed Instance using ARM Templates – Part 3

Part 1 of this series, went over the steps on how to create a SQL Managed Instance using the Azure Portal.  In part 2, I described using a PowerShell script to create a Managed Instance.  For this final part in deploying SQL Managed Instances, I will go over what it would take to use an ARM Template.

To start off, I used this blog post as a starting point and plan on building it from there.

A SQL Managed Instance has some requirements that were discussed in part 2.  The article goes into a little more detail on how to get the requirements setup.  This article from Microsoft goes into how to configure a VNet for a Managed Instance.

As a review here are the prerequisites for a SQL Managed Instance in Azure.

  1.  Setup/configure a VNet (Virtual Network) and a subnet.  Once a VNet is created, you will need to create a  dedicated subnet that the Managed Instance will be in, please note the subnet cannot contain any other cloud service.
  2. A compatible Network Security Group (NSG), the site mentioned above has the security rules laid out in detail.
  3. Create a user route table with  0.0.0.0/0 Next Hop Internet as the mandatory user-defined route table (UDR) assigned to it.
  4. Must not have a service endpoint associated with it.
  5. Must have a minimum of 16 IP addresses assigned to it.

Once all the requirements are met, you will need to obtain the Subnet ID, it should look something like the following. We will be using this in the Parameters file for the ARM Template.

/subscriptions/<Subscription ID>/resourceGroups/RG-DBGRL93-01P/providers/Microsoft.
Network/virtualNetworks/vnet-dba-mi-azcloud/subnets/snet-dba-mi-azcloud

The first part of the ARM Template will be the parameters.  This, really is not too different from the parameters section of the Azure SQL Database template we used.  As you can see, I use a lot of the same parameters.


    "parameters": {
        "administratorLogin": {
            "type": "string"
        },
        "administratorLoginPassword": {
            "type": "securestring"
        },
        "location": {
            "type": "string"
        },
        "managedInstanceName": {
            "type": "string"
        },
        "subnetId": {
            "type": "string"
        },
        "skuName": {
            "type": "string",
            "defaultValue": "GP_Gen4"
        },
        "skuEdition": {
            "type": "string",
            "defaultValue": "GeneralPurpose"
        },
        "storageSizeInGB": {
            "type": "int",
            "defaultValue": "32"
        },
        "vCores": {
            "type": "int",
            "defaultValue": "16"
        },
        "licenseType": {
            "type": "string",
            "defaultValue": "LicenseIncluded"
        },
        "hardwareFamily": {
            "type": "string",
            "defaultValue": "Gen4"
        }
    },

In this example, I am using a separate Parameters file.  This is where I tell it the subnet ID I created and to use it for the Managed Instance.
"subnetId": {
"value": "/subscriptions//resourceGroups/RG-DBGRL93-01P/providers/Microsoft.Network/virtualNetworks/vnet-dba-mi-azcloud/subnets/snet-dba-mi-azcloud"
},

Usually after the Parameters section, you would create any variables that may be needed for the template.  In this example, I am not going to be using any variables.  I will be going straight to the resources section.

As you can see, it is pretty straight forward.  If you would like, there is a resources template for adding databases to the managed instance.  In this post, I am keeping it pretty basic and just deploying the Managed Instance.


   "resources": [
        {
            "apiVersion": "2015-05-01-preview",
            "type": "Microsoft.Sql/managedInstances",
            "identity": {
                "type": "SystemAssigned"
            },
            "location": "[parameters('location')]",
            "name": "[parameters('managedInstanceName')]",
            "sku": {
                "name": "[parameters('skuName')]",
                "tier": "[parameters('skuEdition')]"
            },
            "properties": {
                "administratorLogin": "[parameters('administratorLogin')]",
                "administratorLoginPassword": "[parameters('administratorLoginPassword')]",
                "subnetId": "[parameters('subnetId')]",
                "storageSizeInGB": "[parameters('storageSizeInGB')]",
                "vCores": "[parameters('vCores')]",
                "licenseType": "[parameters('licenseType')]",
                "hardwareFamily": "[parameters('hardwareFamily')]"
            }
        }

Finally, to deploy the Managed Instance using Powershell.


New-AzureRmResourceGroupDeployment -administratorLoginPassword $secpasswd -ResourceGroupName 'RG-DBGRL93-01P' -TemplateFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ARMTemplt_SQLMI.json' -TemplateParameterFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ARMParams_SQLMI.json'

When deploying a Managed Instance for the first time in a VNet, I have seen it take up to 3 hours.  Once it is complete, you will be able to complete the configurations and connect to it using SQL Server Management Studio.

Hope this helps in deploying SQL Managed Instance.

How to Deploy an Azure SQL Managed Instance Using PowerShell – Part 2

In Part 1 of this series, I talked a little about what a SQL Managed Instance is and how it works like an on premise SQL Server Instance.  In this next part of the series, I will be showing the steps necessary to deploy a Managed Instance using PowerShell.

Deploying a SQL Managed Instance is a little different than that of a SQL Database in Azure or even a SQL Server instance on a virtual machine.  There are a couple of prerequisites for a SQL Managed Instance.

Requirements

You can find more detail from Microsoft in the article titled Configure a VNet for Azure SQL Database Managed Instance

  1.  Setup/configure a VNet (Virtual Network) and a subnet.  Once a VNet is created, you will need to create a  dedicated subnet that the Managed Instance will be in, please note the subnet cannot contain any other could service.
  2. A compatible Network Security Group (NSG), the site mentioned above has the security rules laid out in detail.
  3. Create a user route table with  0.0.0.0/0 Next Hop Internet as the mandatory user-defined route table (UDR) assigned to it.
  4. Must not have a service endpoint associated with it.
  5. Must have a minimum of 16 IP addresses assigned to it.

Here is a sample script to setup a VNet and a subnet.  Here, I am setting up the parameters for the names of the VNet and Subnet.  I can then create the VNet and the SubNet using the PowerShell commands.


$ResourceGroupName = "RG-DBGRL93-01P"
$VNetName = "vnet-dba-mi-azcloud"
$SubNetName = "snet-dba-mi-azcloud"

$virtualNetwork = New-AzureRmVirtualNetwork `
  -ResourceGroupName $ResourceGroupName `
  -Location "North Central US" `
  -Name $VNetName `
  -AddressPrefix 10.0.0.0/16

  $subnetConfig = Add-AzureRmVirtualNetworkSubnetConfig `
  -Name $SubNetName `
  -AddressPrefix 10.0.0.0/24 `
  -VirtualNetwork $virtualNetwork

  $virtualNetwork | Set-AzureRmVirtualNetwork

To Create the Route Table and Route


$Route = New-AzureRmRouteConfig -Name "dbatc-mi-rte" -AddressPrefix 0.0.0.0/0 -NextHopType "Internet"
New-AzureRmRouteTable -Name "RouteTableMI" -ResourceGroupName $ResourceGroupName -Location "North Central US" -Route $Route

Once the VNet, Subnet and Route Table are in place you are ready to deploy the SQL Managed Instance.

I found the following script here, it is a pretty good article showing that has another example using PowerShell.  In this snippet, you can see that the command to create the SQL Managed Instance needs the Subnet ID.  We can get that by running the Get-AzureRmVirtualNetwork and the Get-AzureRmVirtualNetworkSubnetConfig commands.

The Subnet ID needs to be in the format of, getting this ID will be necessary for the ARM Template script I will be working on for the next article.

/subscriptions/<Subscription ID>/resourceGroups/RG-DBGRL93-01P/providers/Microsoft.
Network/virtualNetworks/vnet-dba-mi-azcloud/subnets/snet-dba-mi-azcloud

Now, once we create the requirements, we can setup PowerShell to run the commands to deploy the managed instance.  In this script, I am using the command New-AzureRmSqlInstance.  This command is available after installing the AzureRM.SQL module

Install-Module -Name AzureRM.Sql -RequiredVersion 4.12.1
Then run the
Import-Module AzureRm.Sql

Once that is setup you can put it all together as below.


$ResourceGroupName = "RG-DBGRL93-01P"
$VNetName = "vnet-dba-mi-azcloud"
$SubNetName = "snet-dba-mi-azcloud"

$vNet = Get-AzureRmVirtualNetwork -Name $VNetName -ResourceGroupName $ResourceGroupName
$subnet = Get-AzureRmVirtualNetworkSubnetConfig -Name $SubnetName -VirtualNetwork $vNet
$subnetId = $subnet.Id


New-AzureRmSqlInstance -Name "dbaitc-mi-ps-01" `
-ResourceGroupName $ResourceGroupName -Location "North Central US" -SubnetId $subnetId `
-AdministratorCredential (Get-Credential) `
-StorageSizeInGB 1024 -VCore 8 -Edition "GeneralPurpose" `
-ComputeGeneration Gen4 -LicenseType LicenseIncluded

Hope this helps out, since Managed Instances are still pretty new, it took me some time to figure this out.  Go ahead and give it a try and let me know how it goes.

 

How to deploy an Azure SQL Managed Instance – Part 1

The next series of articles are focused on the different ways you can deploy a SQL Managed Instance to the Azure Portal.  Since it was released on October 1, 2018, I have been working with the Managed Instance either through the portal, using ARM templates or PowerShell scripts.

The SQL Managed Instance is a PaaS (Platform as a Service) offering from Microsoft that offers many of the features found in SQL Server instances that are found either on premises or on virtual machines.  Microsoft manages the backups, the patching and even provides built in high availability.

It is also unique in it’s PaaS offering in that it is setup in it’s own Virtual Network and can be isolated using Network Security Groups on the subnet.  I will get more detailed in how this is configured in future articles.  For this article Microsoft makes it real easy when you create the managed instance on the portal. During the setup, if you specify to create a new virtual network, Azure will setup the VNet specifically for the Managed Instance.

Many features that are setup are very similar to the on premise instance, such as linked servers, SQL Server Agent and cross database queries.   You may also find that there are some features in these offerings that are still not available.  Such as being able to set properties for the SQL Agent or the ability to run command prompt or PowerShell jobs.  Or, Linked Servers are limited to just connecting to other SQL Servers.  Here is a good article that describes all the differences in T-SQL from that of SQL Server.

Let’s Get Started

Click on Create a resource in the upper left corner of the portal and search for “Azure SQL Managed Instance”, you will then see a blade come up where you can click on Create to begin the wizard.

Once you click on create a blade will come up where you can enter the information for the Managed Instance.

  1. Select the subscription
  2. Give the Managed Instance a Name
  3. Enter an Admin Login and password
  4. Select the data center location for your Managed Instance
  5. Here is where the Managed Instance really separates itself from the Azure SQL Database PaaS offering. You must now either select or create a Virtual Network for the instance.
    For this example, I am telling it to Create a new Virtual Network.
  6. Select or create a Resource Group and then on to the Pricing tier.
  7. When ready click on Create at the bottom of the blade.

Once the deployment starts, it could take some time to deploy, especially the initial Managed Instance.  Once it is complete you can use SQL Server Management Studio to connect to it using the admin account created earlier.

 

I hope this was helpful in getting you started on Managed Instances.

 

 

How to Deploy an Azure SQL Database with an ARM Template – Part 3

When I decided to start this Blog, I had an idea to post how to deploy resources to the Azure Cloud in 3 different ways.  I chose to use the Azure SQL Database as the first resource to deploy.  Part 1 of this series shows the steps on how to deploy the SQL Database using the portal.  In part 2 , I covered deploying the SQL Database using only PowerShell commands.

Now for part 3, I will cover using ARM (Azure Resource Manager) templates to deploy an Azure SQL Database.

To get started on learning how to use ARM Templates, I found that Microsoft had a bunch of  quick start templates you can download and try yourself.  I found these to be a good starting point to working with the templates and getting you to where you can build on to them.  You can also find them at GitHub.

ARM Templates are basically JSON files with a basic schema as below.  In the template I have for an Azure SQL Database, I have a section for parameters, variables, resources and outputs. I will have to discuss functions in another post.

{
"$schema": "http://schema.management.azure.com/schemas/2015-01-01/deploymentTemplate.json#",
"contentVersion": "",
"parameters": { },
"variables": { },
"functions": [ ],
"resources": [ ],
"outputs": { }
}

So, let’s get started.

Parameters

This section is optional, you do want to add parameters so you can enter names for resource groups, storage accounts or the SQL Database name.  As you can see below, my Parameters section is pretty long, but I do want to provide a template that I can use to create a variety of sizes.  For each parameter, you define a “type” and optionally you can define a default value, and even a description of the parameter.


    "parameters": {
        "sqladministratorLogin": {
            "type": "string",
            "defaultValue": "sysdbadmin",
            "metadata": {
                "description": "This is the SQL Administrator Login (sa)"
            }
        },
        "sqlPassword": {
            "type": "securestring"
        },
        "databaseName": {
            "type": "string",
            "metadata": {
                "description": "Name of database, format SQLDB---<01(P)(N)>"
            }
        },
        "serverName": {
            "type": "string",
            "metadata": {
                "description": "Name of logical SQL Server, format SQL<01(P)(N)>"
            }
        },
        "serverLocation": {
            "type": "string",
            "defaultValue": "North Central US",
            "metadata": {
                "description": "Azure Data Center Location"
            }
        },
        "collation": {
            "type": "string"
        },
        "tier": {
            "type": "string"
        },
        "skuName": {
            "type": "string"
        },
        "zoneRedundant": {
            "type": "bool"
        },
        "emailAddresses": {
            "type": "string",
            "defaultValue": "dba@dbainthecloud.com"
        },
        "firewallRuleName": {
            "type": "array",
            "defaultValue": [
                {
                    "IPRangeName": "IPRange1",
                    "IPRangeStart": "192.168.1.0",
                    "IPRangeEnd": "192.168.1.10"
                },
                {
                    "IPRangeName": "IPRange2",
                    "IPRangeStart": "0.0.0.0",
                    "IPRangeEnd": "0.0.0.0"
                }
            ]
        }
    },

Variables

When I started working with an ARM Template, I didn’t quite understand what the difference is between the parameters and variables section.  However, when I was at Microsoft Ignite this year (September 2018), the one thing they did mention in all the sessions on DevOps was to use the variables to build/format your resource names.

For example, you could get some basic information from a user such as the resource group name or application name and use the Variables section to build the SQL Database Name or deploy a storage account.


    "variables": {
        "databaseServerName": "[toLower(parameters('serverName'))]",
        "databaseName": "[parameters('databaseName')]",
        "databaseServerLocation": "[parameters('ServerLocation')]",
        "databaseServerAdminLogin": "[parameters('sqladministratorLogin')]",
        "databaseServerAdminLoginPassword": "[parameters('sqlPassword')]",
        "emailAddresses": "[parameters('emailAddresses')]"
    },

Resources

The heart of the ARM Template.  Here is where you can define all the configuration items of the SQL Database you want to deploy.  In this template, I am setting up the logical server name, auditing settings which includes a storage account, firewall rules and then finally the SQL database.

I try to breakdown type of resource into it’s own section. It is also easier to think of it as a top down approach. For example, when creating a database, you first need a server (albeit a logical server, then all the resources that you want to configure at the server level and finally the last resource I have is for the database.

In this snippet I am giving it a type of Microsoft.Sql/servers, giving it the location to deploy the server, a name for the server along with properties for the server. In this case I am giving it an administrator login and password.


    "resources": [
        {
            "apiVersion": "2017-10-01-preview",
            "type": "Microsoft.Sql/servers",
            "location": "[parameters('serverLocation')]",
            "name": "[parameters('serverName')]",
            "properties": {
                "administratorLogin": "[parameters('sqladministratorLogin')]",
                "administratorLoginPassword": "[parameters('sqlPassword')]"
            }
        },

The next section, I create the firewall rules.  The firewall rules are at the server level, so, I am putting that under the server section.  Since it is possible to setup several firewall rules along with enabling the Allow Access to Azure services, I am setting up a loop to go through each set of IP Ranges that were identified in the parameters.  I also added the “dependsOn” label to tell the Resource Manager that the Firewall rules depend on the ServerName just created.

If you look back up at the Firewall Rule parameter, I setup an array and defined 2 different IP Ranges.  With the array I can then use the “copy” section to count the number of parameters in the array to give me a loop count.  I can then reference the parameter names for the Start and End IP addresses for the firewall rules.

        {
            "type": "Microsoft.Sql/servers/firewallrules",
            "name": "[concat(variables('databaseServerName'), '/', parameters('firewallRuleName')[copyIndex()].IPRangeName)]",
            "apiVersion": "2014-04-01-preview",
            "dependsOn": [
                "[variables('databaseServerName')]"
            ],
            "location": "[parameters('serverLocation')]",
            "properties": {
                "startIpAddress": "[parameters('firewallRuleName')[copyIndex()].IPRangeStart]",
                "endIpAddress": "[parameters('firewallRuleName')[copyIndex()].IPRangeEnd]"
            },
            "copy": {
                "name": "firewallloop",
                "count": "[length(parameters('firewallRuleName'))]"
            }
        },

Finally, for the last part of the Resources section, I give the properties to create the Azure SQL Database.  For the properties, I can tell it which location to deploy the database, SKU information of the level of SQL Database I wish to deploy.  There are many other properties you can set at this site.


        {
            "type": "Microsoft.Sql/servers/databases",
            "location": "[parameters('serverLocation')]",
            "apiVersion": "2017-10-01-preview",
            "dependsOn": [
                "[concat('Microsoft.Sql/servers/', parameters('serverName'))]"
            ],
            "name": "[concat(parameters('serverName'),'/', parameters('databaseName'))]",
            "properties": {
                "collation": "[parameters('collation')]",
                "zoneRedundant": "[parameters('zoneRedundant')]"
            },
            "sku": {
                "name": "[parameters('skuName')]",
                "tier": "[parameters('tier')]"
            }
        }

And, finally, the outputs section of the ARM Template.


    "outputs": {
        "ServerObject": {
            "value": "[reference(variables('databaseServerName'))]",
            "type": "object"
        },
        "DBObject": {
            "value": "[reference(variables('databaseName'))]",
            "type": "object"
        }
    }

Now that the ARM Template is done, you can reference the template in a Powershell script using the command, New-AzureRmResourceGroupDeployment .  Here is how I setup the parameters and execute the command in Powershell.


$Parameters = @{
    sqladministratorLogin = 'dbadmin'
    databaseName= 'sqldb-dba-tmplt-01n'
    servername= 'sqldbatmplt01n'
    collation = 'SQL_Latin1_General_CP1_CI_AS'
    serverLocation = 'northcentralus'
    tier= 'Standard'
    skuName= 'Standard'
    zoneRedundant = $false
}

    
$secpasswd = ConvertTo-SecureString "" -AsPlainText -Force

New-AzureRmResourceGroupDeployment  -sqlPassword $secpasswd -ResourceGroupName 'RG-DBGRL93-01P' -TemplateFile 'C:\DBAInTheCloud2\ARMTemplates\DBA_ITC_ARMTmplt_SQLDB.json' -TemplateParameterObject $Parameters 

It may take a few minutes to deploy, but you can check the progress of the deployment in the portal, or in Powershell you can run

Get-AzureRmResourceGroupDeployment -ResourceGroupName ”

Hopefully this helps you through your journey on learning Cloud technologies and scripting.

 

How to Deploy an Azure SQL Database Using PowerShell – Part 2

How to Deploy an Azure SQL Database Using PowerShell – Part 2

In my first blog I went over the steps on how to create an Azure SQL Database using the Azure Portal.  In this post I will go over a PowerShell script that deploys a SQL Database.  I am starting with the PowerShell script example Microsoft provides at Use PowerShell to create a single Azure SQL database and configure a firewall rule.

Here, Microsoft provides a pretty good start for creating a database.  I will break the script down and discuss some of the modifications I made to create a database.

At the top of the script some parameters are set.

SubscriptionID
Resource Group Name
Location – Data Center Location
Database Admin – SQL Login, this would be the equivalent to the sa account
Server Name – Logical Server Name for the database
Database Name
IP Range – Range of IP addresses in an Hash Table to create the Firewall Rules on the server level

Here is a sample of the code so far

Select-AzureRmSubscription -Subscription "<subscriptionID>"
    
# Set the resource group name and location for your server
$resourcegroupname = "<ResourceGroupName>"
$location = "North Central US"
# Set an admin login and password for your server
$adminlogin = "AzureSA"
$password = ""
# Set server name - the logical server name has to be unique in the system
$servername = "<ServerName>"
# The sample database name
$databasename = "<DatabaseName>"

Next, create the server for the database.  Even though you are just creating a database, Azure still uses a logical server in the background.  Here you can give the logical server a name to follow your naming standards.

Microsoft made it pretty easy in Powershell, the command is New-AzureRmSqlServer.  All you need to provide is the resource group name, server name, location and the admin credentials.

# Create a server with a system wide unique server name
$server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-Location $location `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

Next step is to set the firewall rules.  Here is where I stray from the sample Microsoft script.  For my environment, we need to setup a range of internal IP addresses in addition to enabling the Allow Azure Services option.

Here is where I am setting the Hash table with the range of IP addresses I need to configure.  IPRange1 is the list of internal addresses that I need to allow to have access to the database.  It may also be necessary to Enable to Allow Access To Azure Services.  This option basically sets a range of 0.0.0.0 for both the start and end.  This is useful for scripting this.

# The ip address range that you want to allow to access your server
$IPRangeList = @{
IPRange1 = "192.168.1.180","192.168.1.185"
IPRange2 = "0.0.0.0","0.0.0.0"
}

Once I set the Hash Table with the list of IP Ranges, you can setup a ForEach loop to set each range. The Powershell command New-AzureRmSqlServerFirewallRule makes it pretty easy to do this.  Once again, just give it the resource group name and server name.  The next parameter sets the Firewall Rule Name.  In this one I just reference the Hashtable to create the name and set the Start and End addresses.

ForEach ($IPRange in $IPRangeList.Keys){
# Create a server firewall rule that allows access from the specified IP range
$serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-FirewallRuleName "AllowedIPs" $IPRange -StartIpAddress  $IPRangeList[$IPRange][0] -EndIpAddress  $IPRangeList[$IPRange][1]
}

Once this is set, the script then creates the database using the New-AzureRmSqlDatabase.  The RequestedServiceObjectiveName is basically the pricing tier.

# Create a blank database with an S0 performance level
$database = New-AzureRmSqlDatabase  -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-DatabaseName $databasename `
-RequestedServiceObjectiveName "S0"

And with those 3 sections, you can create a basic SQL Database in Azure.  There are some other options that can be configured in the script to completely automate this.  For example, Auditing and the Advanced Threat Protection can be enabled and configured in PowerShell.  The logical SQL Server for the database can also be configured with an Active Directory Admin.

Here is an example of setting the Active Directory Admin.  This will add the Active Directory Group called DBATeam.

#Set the Active Directory Admin for the Logical SQL Server
Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName $resourcegroupname  `
-ServerName $servername -DisplayName "DBATeam"

To enable the Advance Threat Protection and Auditing, you will need to create a storage account first. Enabling auditing and the Advanced Threat Protection feature on the server level will automatically enable them for every database created under that server.

# Create a Storage Account
$storageaccount = New-AzureRmStorageAccount -ResourceGroupName $resourcegroupname `
-AccountName $storageaccountname `
-Location $location `
-Type "Standard_LRS"

# Set an auditing policy on the server level
Set-AzureRmSqlServerAuditing -State Enabled `
-ResourceGroupName $resourcegroupname `
-ServerName $servername `
-StorageAccountName $storageaccountname

# Set a threat detection policy
Set-AzureRmSqlServerThreatDetectionPolicy -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-StorageAccountName $storageaccountname `
-NotificationRecipientsEmails $notificationemailreceipient `
-EmailAdmins $true

In the Set-AzureRmSqlServerThreatDetectionPolicy command there is a new parameter called NotificationRecipientsEmails.  This can be a semicolon list of email addresses that can be alerted when an event is kicked off.

#Email Recipient list
$notificationemailreceipient = "dba@DBAInTheCloud.com;secteam@DBAInTheCloud.com"

There are many other features that can be set using PowerShell.  The example I provided above creates a basic database with some standard features to get someone up and running fairly quickly while adhering to any standards required by your organization.  To view some of the other things you can do in PowerShell for the Azure SQL Database, Microsoft has plenty of examples at Azure PowerShell samples for Azure SQL Database .

Hope this was helpful.

How to Create a Basic Azure SQL Database – Part 1

Requirements

  1. Microsoft Azure Account
  2. Resource Group

Steps

Once logged into the Azure Portal click on the Option for SQL Databases from the menu on the left side.

You should then see an Add button as shown below, Click on Add.

The Create new Database Blade will then appear.

You will need to fill in the name of the database you want to create.  When ready select the subscription.  If you have not already created a Resource Group, you can click on the Create New link.

A Resource Group in Azure is basically a logical container to group the services you are creating.  This grouping can then be setup for different business functions or applications and permissions can be set accordingly.

Click on the Configure Server.  If no server is found, the create new server blade will appear.  It will require you to set a server name.

Even though you are creating a single database, Azure creates a virtual SQL Server in the background.  You are able to create multiple databases on this server.  However, it does not function as an instance of SQL Server would if you had an instance on a VM or in your data center.

Enter a name for the server admin login and a password.  Choose a location nearest you or where the database will be used.

Checking the box to Allow Azure services to access the server basically opens the firewall settings to let all IP addresses get to the database. More on the Firewall settings in another article.

Click on Select at the bottom of the blade.  Another blade will appear where you can select the pricing tier and the collation. The default is the Standard, for this demonstration, I am selecting the Basic tier.

When ready you can click on Create.  Clicking on Automation Options will create an ARM Template to save. More on ARM Templates in future articles.

Once you click on Create, the deployment process will begin.  Going to the Resource Group you created, you can also see the status of the deployment