Tag: Azure SQL PowerShell

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