Archive

Archive for October, 2018

Azure SQL Managed Instance – Provisioning

October 31, 2018 3 comments

Once you have created the prerequisites, you are ready to create your first Managed Instance.  As of now, Managed Instance is only available in the following subscription types:

  • Pay-As-You-Go
  • Enterprise Agreement
  • Cloud Service Provider

Information about subscription and resource limitations can be found here.  I will update this with any changes.

For a PowerShell script to provision a Managed Instance, see the end of the post.

Add Resource

To provision a Managed Instance in the Azure Resource Manager portal, connect to your subscription and open the resource group in which you created the prerequisites.  Click the Add button at the top of the page:

A1 - AddMi

Search Resources

On the Everything page, search for Azure SQL Managed Instance and click the name in Results.

A2 - SearchMi

The portal will display an overview of the Managed Instance resource and links to Managed Instance documentation, pricing, and information about the Database Migration Service.  Confirm that you want to create a new Managed Instance and click Create.

Select Managed Instance

A3 - MiDisclaimer

Enter Managed Instance Information

In the SQL Managed Instance pane, select the subscription in which you created the prerequisite resources.  Enter a name for the Managed Instance, an admin account name, and an admin password.

NOTE: The admin password must be at least 16 characters in length.

Select the resource group and location in which the prerequisite resource exist.  If the virtual network and subnet were created successfully, the subnet that contains the route table will appear in the Virtual Network dropdown.  Select it and the pricing tier link will unlock.

A4 - MiInformation.png  

Choose Pricing Tier

I will provide more information about pricing tiers and processor generations in a subsequent post.  For this example, I will select a General Purpose Managed Instance containing 8 vCores and 32GB of storage.  Select the desired parameters and click Apply.

A5 - ConfigureMiPerformance

Create Managed Instance

At this point you are ready to create your first Managed Instance.  Note that the initial creation of a Managed Instance in a subnet can take up to 6 hours.  Click the Create button to start the process.

A6 - CreateMi

View Managed Instance

Navigate back to your resource group.  Once the Managed Instance is created, click the Managed Instance name on the Overview page.

A7 - SelectMi

Celebrate!

The Overview for the Managed Instance will display with a message that your Managed Instance is ready.  Congratulations!

A8 - MiOverview

In my next post, I will cover the process for connection to the Managed Instance and restoring a database.

<# NOTE: The initial deployment of an Availability Group to a subnet will take ~3 hours. <span 				data-mce-type="bookmark" 				id="mce_SELREST_start" 				data-mce-style="overflow:hidden;line-height:0" 				style="overflow:hidden;line-height:0" 			>&#65279;</span>Subsequent deployment will take minutes #>
<# Connect to Azure Account #>
Connect-AzureRmAccount;

<# Initialize local variables     Confirm that resource group, location, VNet, and subnet are the same    as those for the Managed Instance prerequisites #>
$resourcegroup = "yourmirg";
$location = "eastus";
$miname = "yourmi";
$vnetname = "yourmivn";
$subnetname = "default";
$subscrname = "Your Subscription"

<# Get subscription ID for the specified subscription #>
$subscrid = (Get-AzureRmSubscription -SubscriptionName $subscrname).SubscriptionId;

<# Switch to the desired subscription #>
Set-AzureRmContext -SubscriptionID $subscrid;

<# Create a new Managed Instance    This example will create a General Purpose Managed Instance    with 8 Gen4 vCores, indicated by the GP_Gen4 sku    To create a Business Critical instance with Gen5 cores    use sku BC_Gen5 #>
New-AzureRmSqlManagedInstance -Name $miname `
-ResourceGroupName $resourcegroup `
-Location $location `
-AdministratorCredential (Get-Credential) `
-SubnetId "/subscriptions/$subscrid/resourceGroups/$resourcegroup/providers/Microsoft.Network/virtualNetworks/$vnetname/subnets/$subnetname" `
-LicenseType LicenseIncluded `
-StorageSizeInGB 32 `
-VCore 8 `
-SkuName GP_Gen4

SQL Saturday Oregon 2018

October 24, 2018 2 comments

sqlsat808_header

This will be my fourth year at SQL Saturday Oregon and I’ve had a great time every time.  Since it takes place the Saturday before PASS Summit, the event draws speakers from across the globe on their way to Seattle.

This year, I am presenting The SQL Server Transaction Log for Developers.  The transaction log is a subject near and dear to my heart and it has been really interesting looking at it from a different perspective.

If you are in the Portland area, I highly recommend attending.  You can find more information and register here.

Azure SQL Managed Instance – Prerequisites

October 24, 2018 2 comments

ManagedInstanceLogo

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 0.0.0.0/0 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#>
Connect-AzureRmAccount;

<#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 10.0.0.0-10.0.255.255#>
$virtualNetwork = New-AzureRmVirtualNetwork `
-ResourceGroupName $rgname `
-Location $location `
-Name $vnet `
-AddressPrefix 10.0.0.0/16;

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

<#Creates a subnet with an IP range of 10.0.1.0-10.0.1.255
Used for VM#>
$subnetVmConfig = Add-AzureRmVirtualNetworkSubnetConfig `
-Name $vmsubnet `
-AddressPrefix 10.0.1.0/24 `
-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 0.0.0.0/0 -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 10.0.0.0/24 `
-RouteTable $RouteTable | Set-AzureRmVirtualNetwork;

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

$subnetConfig = Get-AzureRmVirtualNetworkSubnetConfig -VirtualNetwork $vnetlong;

$subnetConfig[0].Id;

<#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
https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms?view=sql-server-2017#>