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 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.