Archive
Azure SQL Managed Instance – Migrate a Database Using Backup and Restore
If you have run through my last Managed Instance blog post, you have a Managed Instance at your disposal. The PowerShell script for creating the network requirements also contains steps to create an Azure VM in a different subnet in the same VNet. Unless you have a site-to-site VPN or Express Route between your on-prem environment and Azure, you will use this VM to connect to your Managed Instance.
Install Management Studio on the Azure VM. To connect to your Managed Instance, you will need the host name for your Managed Instance. You can find the Managed Instance host name on the resource page for your Managed Instance in the Portal.
Open Management Studio and copy the instance name into the Server field in Management Studio and enter the admin account and password you specified on creation. Click connect.
Once the Managed Instance opens, you will be able to navigate it using Object Explorer. The experience will look very familiar if you are used to using SSMS. You will see all four system databases and SQL Server Agent. If you expand the Agent entry in Object Explorer, you will notice that Alerts do not currently exist. Also, note that the SQL version number is listed as 12.0.2000.8. This is NOT SQL Server 2014. Azure SQL Database and Azure SQL Managed Instance are currently at version 12.
To create your first database, you will need to upload a backup file to Azure blob storage. If you do not have a Storage Account, click the Add button at the top of your Resource Group.
In the search box, enter Storage Account. Select Storage account - blob, file, table, queue. On the next screen, click Create.
Confirm your subscription and resource group are correct. Select and name your storage account. Select the same location as your Managed Instance, Standard performance, and Storage V2 (general purpose V2). For this example, I selected Locally-redundant storage (LRS) because it is the least expensive option. Click the Review and Create button.
Once the storage account is provisioned, you will need to create a container to hold the backup file. I think of the storage account as a drive and containers as folders on that drive. Under Services, click Blobs.
Once the Blob service screen opens, click Containers at the top of the screen. Name your container and leave the Public access level set to the default. Click OK.
After your container is created, click the container name to open it. Click the Upload button at the top of the screen.
Click the folder button to the right of the Files text box and select your backup file. Once the Upload button becomes active, click it. Once the upload is complete, you are ready to restore a database to your Managed Instance.
Go back to your VM and connect to your Managed Instance. Right-click on Databases in Object Explorer and select Restore Database.
Click the Device radio button. Click the ellipsis button to the right of the Device text box. Notice that URL is the only entry available in the Backup media type dropdown. This is because you must restore a Managed Database from Azure blob storage.
Click the Add button. You will be prompted to sign into your Azure subscription. Click the Sign In button.
Enter the login and password for your Azure account. On the next dialogue, select your subscription, storage account, and container. Click the Create Credential button to create a Shared Access Signature to allow SQL Server to access Azure storage.
Once the credential populates in the Shared Access Signature Generated text box, click OK.
You will return to the Select a Backup File Location dialog with the Azure storage container and Shared Access Signature boxes populated. Click OK
If you have used Management Studio to select a backup file to restore, the Locate Backup File in Microsoft Azure will look familiar. Expand Containers in the left-hand pane and select your container. You should see your backup file in the right-hand pane. Click the file name and click OK.
The Select backup devices dialog will display with your backup file selected. Click OK.
The Restore Database dialog will display with your backup file listed in the Backup sets to restore text box. Note that there are no pages in the Select a page pane for Files or Options. Because storage is managed for a Managed Instance, you cannot select where the files will be placed and you cannot set a database to NORECOVERY. Click the Script button.
A query window will open in Management Studio containing the RESTORE script that SQL Server will execute if you click the OK button on the Restore Database dialog. Navigate to that script. The RESTORE statement is extremely simple:
You cannot add any WITH clauses to the statement. If you try you will receive the following error:
To complete the restore of your database, execute the restore script. Once the restore completes, you will be able to access it using Management Studio. In my next post, I will provide additional options for migrating databases and instance-level objects to Managed Instance.
Azure SQL Managed Instance – Provisioning
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:
Search Resources
On the Everything page, search for Azure SQL Managed Instance and click the name in Results.
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
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.
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.
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.
View Managed Instance
Navigate back to your resource group. Once the Managed Instance is created, click the Managed Instance name on the Overview page.
Celebrate!
The Overview for the Managed Instance will display with a message that your Managed Instance is ready. Congratulations!
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" ></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
Azure SQL Managed Instance – Prerequisites
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 "$server-nsg" ` -PublicIpAddressName "$server-ip" ` -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#>
Chicago Data Professionals
