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

 

Azure SQL Managed Instance – An Introduction

October 17, 2018 1 comment

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.

Historically there have been two offerings available for running SQL Server in Azure.  The first is Azure SQL Database, released in 2010, which is Platform as a Service (PaaS). The second is SQL Server on an Azure VM, which is Infrastructure as a Service (IaaS), also released in 2010.  The benefits and drawbacks of each offering is listed below.

 BenefitsDrawbacksUse Cases
Azure SQL DatabaseInfrastructure is fully managedLimited to a single databaseNew development in Azure
Cross-database communication is difficultMulti-tenant applications
No SQL Server Agent
Cannot be powered down
SQL Server on an Azure VMFull feature set of SQL Server
Client is responsible for managing infrastructureMigration of existing applications
Can be powered down to reduce costCreation of test environments
Multiple Databases
Cross-database communication
SQL Server Agent

The drawbacks of Azure SQL Database make it difficult to migrate existing applications, because of the number of application changes required.  Azure SQL Database is designed to be used for new development in Azure and for multi-tenant environments, where each tenant requires their own copy of a database.

The benefits of SQL Server on an Azure VM make it much easier to migrate an existing application to Azure.  However, the VMs underlying the application still have to be managed by the client.  This fails to take advantage of the management of resources in Azure, and uses Azure as a VM host.

A third option, Azure SQL Managed Instance, was released at the beginning of October 2018.  Managed Instance combines the best of the previous options.  With Managed Instance, the infrastructure is fully managed and the majority of the SQL Server feature set is available.  The full list of differences between a traditional install of SQL Server and Managed Instance can be found here.  A number of the most dramatic differences are listed below.

  • Filestream is not supported
  • Backups must be run to Azure Blob Storage
  • No control over file placement

In the following series of blog posts, I will detail the steps required for the following tasks:

Additionally, I will delve into the internals of an Azure SQL Managed Instance and describe where they differ from a regular SQL Server instance.

I am really excited about the options Azure SQL Managed Instance provides for migrating existing SQL applications to Azure and am looking forward to blogging about it.