Posts Tagged ‘PowerShell’

Azure SQL Managed Instance – Prerequisites

October 24, 2018 2 comments


NOTE: This post contains steps for creating Azure resources.  You must have access to an Azure subscription to complete these steps.  IMPORTANT: According to this post from Microsoft, Managed Instance creation is currently supported for Enterprise Agreement, Pay-As-You-Go, and Cloud Service Provider subscriptions.  I will update this post when additional subscription types are added.

Before creating an Azure SQL Managed Instance, a number of prerequisite resources must be provisioned.  These are:

  • An Azure Virtual Network
  • A dedicated subnet for Managed Instances
  • A route table

The Azure Virtual Network provides isolation of the Managed Instance from other tenants in the Azure cloud.  This VNet allows connectivity to your on-premises environment through an Azure Express Route or site-to-site VPN gateway.  Finally, the SQL endpoint for the Managed Instance is only exposed through a private IP, allowing safe connections from a private Azure or hybrid network.

A dedicated subnet is required within the VNet to contain your Managed Instances.  You will not be able to create a Managed Instance in a subnet that contains other resources.  Once you create a Managed Instance in a subnet, you will not be able to create other resources in it.  For ease of administration, it is best practice to create all of your Managed Instances in the same subnet, so it should be sized according to your requirements.

The Managed Instance subnet requires a route table assigned to it.  The route table must contain an address prefix of with Next Hop Internet.

The steps to create these resources can be found here.  Alternatively, the following PowerShell script contains the cmdlets to create these resources.  It creates a second subnet in the VNet and an Azure virtual machine in that subnet.  This allows connection to the Managed Instance in the event you do not have access to a site-to-site VPN gateway or Azure Express Route.  You can also download the code here.

<#Generates a dialog box to log into your Azure subscription#>

<#Local variables for required Managed Instance resources#>
$rgname = "yourmirg";
$vnet = "yourmivn";
$subnet = "default";
$vmsubnet = "yournvstsn";
$rtable = "yournvstrt";
$server = "yournvstsrv";
$location = "northcentralus";

<#If your resource group already exists, uncomment this line#>
#Remove-AzureRmResourceGroup -Name $rgname -Force;

<#If you need to create a resource group, uncomment this line#>
New-AzureRmResourceGroup -Name $rgname -Location $location;

<#Creates VNet with an IP range of>
$virtualNetwork = New-AzureRmVirtualNetwork `
-ResourceGroupName $rgname `
-Location $location `
-Name $vnet `

<#Creates a subnet with an IP range of
Used for Managed Instance#>
$subnetConfig = Add-AzureRmVirtualNetworkSubnetConfig `
-Name $subnet `
-AddressPrefix `
-VirtualNetwork $virtualNetwork;

<#Creates a subnet with an IP range of
Used for VM#>
$subnetVmConfig = Add-AzureRmVirtualNetworkSubnetConfig `
-Name $vmsubnet `
-AddressPrefix `
-VirtualNetwork $virtualNetwork;

<#Writes VNet information to the $VirtualNetwork variable#>
$virtualNetwork | Set-AzureRmVirtualNetwork;

<#The next two commands create a route table and write the route table information to variable $RouteTable#>
$Route = New-AzureRmRouteConfig -Name $rtable -AddressPrefix -NextHopType Internet;
New-AzureRmRouteTable -Name $rtable -ResourceGroupName $rgname -Location $location -Route $Route;

$RouteTable = Get-AzureRmRouteTable -Name $rtable -ResourceGroupName $rgname;

<#Configures the route table in the drfault subnet#>
Set-AzureRmVirtualNetworkSubnetConfig `
-VirtualNetwork $virtualNetwork `
-Name $subnet `
-AddressPrefix `
-RouteTable $RouteTable | Set-AzureRmVirtualNetwork;

$vnetlong = Get-AzureRmVirtualNetwork -Name $vnet -ResourceGroupName $rgname;

$subnetConfig = Get-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $vnetlong;


<#Creates an Azure VM in the yournvstsn subnet, opening ports 80 and 3389#>

New-AzureRmVm `
-ResourceGroupName $rgname `
-Name $server `
-Location $location `
-VirtualNetworkName $vnet `
-SubnetName $vmsubnet `
-SecurityGroupName &quot;$server-nsg&quot; `
-PublicIpAddressName &quot;$server-ip&quot; `
-OpenPorts 80,3389;

<#Once the VM is provisioned, RDP to it and download and install SQL Server Management Studio>


PowerShell Function to Automate Availability Group Failover

November 18, 2017 1 comment

I’ve been working with Availability Groups (AG) since their release in SQL Server 2012 and have always wanted to leverage PowerShell to administer them.  Recently I received a request to develop an automated process for failing Availability Groups over gracefully prior to server patching.  Believe it or not, but a hard shutdown of the primary replica is NOT the best way to force AG failover.

The function takes a replica name as input and queries system tables for Availability Groups running as secondary that are online, healthy, and synchronous.  For each AG found, the function generates an ALTER AVAILABILITY GROUP statement.  If the -noexec parm is set to 0, the command will be executed.  If -noexec is set to 1, the command will be written out to a file.

When writing the function, I started out trying to use the native PowerShell Availability Group cmdlets.  After several false starts, I found it easier to develop the T-SQL code in Management Studio and use Invoke-Sqlcmd to execute the code.  The code is available below.  I hope you can put it to use.

Author: Frank Gill
Date: 2017-11-17
function Invoke-AgFailover {
Checks specified instance for healthy, synchronous Availability Groups running as secondary and
fails them over
Checks the instance passed in for healthy, synchronous Availability Groups running as secondary and
fails them over.  If the instance is not hosting secondary replicas, a message will be output.
If there are AGs running as secondary, a message will output for each, including AG name, destination,
and failover duration.
Invoke-AgFailover -Instance YourSecondaryInstance -NoExec 0;
Any Availability Groups running as secondary on YourSecondaryInstance will be failed over.
Invoke-AgFailover -Instance YourSecondaryInstance -NoExec 1;
If Availability Groups are running as secondary on YourSecondaryInstance, T-SQL commands for each AG failover
will be generated and written to C:\AGFailover\failover_YourAgName_YYYYMMDD_HHMMSS.sql.
The instance to check for secondary replicas.
Set to 1 to generate T-SQL script for failover.
Position = 1,
HelpMessage='Which instance do you want to check for secondary replicas?')]

Position = 2,
HelpMessage='Set to 1 if you want to execute the database restore.  Otherwise ')]
&lt;# If the $noexec is set to 1, create file path to hold out files #&gt;
if($noexec -eq 1)
$rundate = Get-Date -Format yyyyMMdd_HHmmss;
$outpath = &quot;C:\AgFailover&quot;;
if((Test-Path -Path $outpath) -eq $true)
Remove-Item -Path &quot;$outpath\*&quot; -Recurse;
New-Item -Path $outpath -ItemType Directory;

&lt;# Create query to check for failover-eligible AGs #&gt;
$query = &quot;SELECT g.[name], ar.replica_server_name
FROM sys.dm_hadr_availability_replica_states r
INNER JOIN sys.availability_replicas ar
ON ar.group_id = r.group_id
AND ar.replica_id = r.replica_id
INNER JOIN sys.availability_groups g
ON g.group_id = ar.group_id
WHERE r.role_desc = N'SECONDARY'
AND r.recovery_health_desc = N'ONLINE'
AND r.synchronization_health_desc = N'HEALTHY'
AND ar.availability_mode_desc = N'SYNCHRONOUS_COMMIT';&quot;

&lt;# Execute failover-eligible query #&gt;
$secondaries = Invoke-Sqlcmd -ServerInstance &quot;$instance&quot; -Database master -Query $query;

&lt;# Output message if there are no failover-eligible AGs #&gt;
if($secondaries.Count -eq 0)
Write-Output &quot;There are no Availability Group replicas available to fail over to $instance.&quot;

&lt;# If eligible AGs exist, loop through them #&gt;
foreach($secondary in $secondaries)
$secreplica = $secondary.replica_server_name;
$ag = $;


&lt;# If $noexec is set to 0, execute the AG failover
and output a message when complete #&gt;
if($noexec -eq 0)
$starttime = Get-Date;
Invoke-Sqlcmd -ServerInstance &quot;$instance&quot; -Database master -Query $query;
$endtime = Get-Date;
$duration = (New-TimeSpan -Start $starttime -End $endtime).Seconds;
Write-Output &quot;Failed Availability Group $ag to replica $instance in $duration seconds&quot;;
&lt;# If $noexec is not set to 0, write a file out to the path built above for each AG #&gt;
$comment = &quot;/* Run against instance $instance */&quot; ;
$comment | Out-File -FilePath &quot;$outpath\failover_$ag`_$rundate.sql&quot; -Append;
$use = &quot;USE master;&quot;;
$use | Out-File -FilePath &quot;$outpath\failover_$ag`_$rundate.sql&quot; -Append;
$query | Out-File -FilePath &quot;$outpath\failover_$ag`_$rundate.sql&quot; -Append;

SQL Saturday Columbus

It’s been a while since my last blog post.  Since then, I’ve been to PASS Summit, which was the best one yet.  I’ve presented at SQL Saturdays in Portland, Cleveland, and Iowa City. And, most excitingly, started a new job with BlueMetal.  I am in the middle of week 3 and am really excited about the new challenge.

I’ll be presenting on Using PowerShell to Automate Your Restore Strategy at SQL Saturday Columbus on July 16th.  It will be my first time in Columbus, Ohio and I am looking forward to seeing old friends, making new ones, and teaching and learning.  You can register and find more information at the link above.  If you are in the area, don’t miss the opportunity to get a free day of training while networking with your peers in the SQL Server community.