Tag: Azure Virtual Machine

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.