Create an Azure SQL Database Managed Instance in the Azure portal

This tutorial demonstrates how to create an Azure SQL Database Managed Instance (preview) using the Azure portal in a dedicated subnet of a virtual network (VNet) and then connect to the Managed Instance using SQL Server Management Studio on a virtual machine in the same VNet.

  • Whitelist your subscription
  • Configure a virtual network (VNet)
  • Create new route table and a route
  • Apply the route table to the Managed Instance subnet
  • Create a Managed Instance
  • Create a new subnet in the VNet for a virtual machine
  • Create a virtual machine in the new subnet in the VNet
  • Connect to virtual machine
  • Install SSMS and connect to the Managed Instance

If you don't have an Azure subscription, create a free account before you begin.

Important

For a list of regions in which Managed Instance is currently available, see Migrate your databases to a fully managed service with Azure SQL Database Managed Instance.

Log in to the Azure portal

Log in to the Azure portal.

Whitelist your subscription

Managed Instance is being released initially as a gated public preview that requires your subscription to be whitelisted. If your subscription is not already whitelisted, use the following steps to be offered and accept preview terms and send a request for whitelisting.

  1. Click Create a resource in the upper left-hand corner of the Azure portal.
  2. Locate Managed Instance and then select Azure SQL Database Managed Instance (preview).
  3. Click Create.

    managed instance create

  4. Select your subscription, click Preview terms, and then provide the requested information.

    managed instance preview terms

  5. Click OK when completed.

    managed instance preview terms

    Note

    While awaiting preview approval, you can continue and complete the next few sections of this tutorial.

Configure a virtual network (VNet)

The following steps show you how to create a new Azure Resource Manager virtual network (VNet) for use by your Managed Instance. For more information about VNet configuration, see Managed Instance VNet Configuration.

  1. Click Create a resource in the upper left-hand corner of the Azure portal.
  2. Locate and then click Virtual Network, verify the Resource Manager is selected as the deployment mode, and then click Create.

    virtual network create

  3. Fill out the virtual network form with the requested information, using the information in the following table:

    Setting Suggested value Description 
    Name Any valid name For valid names, see Naming rules and restrictions.
    Address space Any valid address range, such as 10.14.0.0/24 The virtual network's address name in CIDR notation.
    Subscription Your subscription For details about your subscriptions, see Subscriptions.
    Resource Group Any valid resource group (new or existing) For valid names, see Naming rules and restrictions.
    Location Any valid location For information about regions, see Azure Regions.
    Subnet name Any valid subnet name, such as mi_subnet For valid names, see Naming rules and restrictions.
    Subnet address range Any valid subnet address, such as 10.14.0.0/28. Use a subnet address space smaller than the address space itself to allow space to create other subnets in the same VNet, such as a subnet for hosting test / client apps or gateway subnets to connect from on-prem or other VNets. The subnet's address range in CIDR notation. It must be contained by the address space of the virtual network
    Service endpoints Disabled Enable one or more service endpoints for this subnet

    virtual network create form

  4. Click Create.

Create new route table and a route

The following steps show you how to create a 0.0.0.0/0 Next Hop Internet route.

  1. Click Create a resource in the upper left-hand corner of the Azure portal.
  2. Locate and then click Route table, and then click Create on the Route table page.

    route table create

  3. Fill out the route table form with the requested information, using the information in the following table:

    Setting Suggested value Description 
    Name Any valid name For valid names, see Naming rules and restrictions.
    Subscription Your subscription For details about your subscriptions, see Subscriptions.
    Resource Group Select the resource group you created in the previous procedure For valid names, see Naming rules and restrictions.
    Location Select the location you specified in the previous procedure For information about regions, see Azure Regions.
    Disable BCP route propagation Enabled

    route table create form

  4. Click Create.

  5. After the route table has been created, open the newly created route table.

    route table

  6. Click Routes and then click Add.

    route table add

  7. Add 0.0.0.0/0 Next Hop Internet route as the only route, using the information in the following table:

    Setting Suggested value Description 
    Route name Any valid name For valid names, see Naming rules and restrictions.
    Address prefix 0.0.0.0/0 The destination IP address in CIDR notation that this route applies to.
    Next hop type Internet The next hop handles the matching packets for this route

    route

  8. Click OK.

Apply the route table to the Managed Instance subnet

The following steps show you how to set the new route table on the Managed Instance subnet.

  1. To set the route table on the Managed Instance subnet, open the virtual network that you created earlier.
  2. Click Subnets and then click the Managed Instance subnet (mi_subnet in the following screenshot).

    subnet

  3. Click Route table and then select the myMI_route_table.

    set route table

  4. Click Save

    set route table-save

Create a Managed Instance

The following steps show you how to create your Managed Instance after your preview has been approved.

  1. Click Create a resource in the upper left-hand corner of the Azure portal.
  2. Locate Managed Instance and then select Azure SQL Database Managed Instance (preview).
  3. Click Create.

    managed instance create

  4. Select your subscription and verify that the preview terms show Accepted.

    managed instance preview accepted

  5. Fill out the Managed Instance form with the requested information, using the information in the following table:

    Setting Suggested value Description 
    Managed instance name Any valid name For valid names, see Naming rules and restrictions.
    Managed instance admin login Any valid user name For valid names, see Naming rules and restrictions.
    Password Any valid password The password must be at least 16 characters long and meet the defined complexity requirements.
    Resource Group The resource group that you created earlier
    Location The location that you previously selected For information about regions, see Azure Regions.
    Virtual network The virtual network that you created earlier

    managed instance create form

  6. Click Pricing tier to size compute and storage resources as well as review the pricing tier options. By default, your instance gets 32 GB of storage space free of charge, which may not be sufficient for your applications.

  7. Use the sliders or text boxes to specify the amount of storage and the number of virtual cores. managed instance create form

  8. When complete, click Apply to save your selection.

  9. Click Create to deploy the Managed Instance.
  10. Click the Notifications icon to view the status of deployment.

    deployment progress

  11. Click Deployment in progress to open the Managed Instance window to further monitor the deployment progress.

    deployment progress 2

While deployment occurs, continue to the next procedure.

Important

For the first instance in a subnet, deployment time is typically much longer than in case of the subsequent instances - sometimes more than 24 hours to complete. Do not cancel deployment operation because it lasts longer than you expected. This length of time to deploy your first instance is a temporary situation. Expect a significant reduction of deployment time shortly after the beginning of the public preview.

Create a new subnet in the VNet for a virtual machine

The following steps show you how to create a second subnet in the VNet for a virtual machine in which you install SQL Server Management Studio and connect to your Managed Instance.

  1. Open your virtual network resource.

    VNet

  2. Click Subnets and then click +Subnet.

    add subnet

  3. Fill out the subnet form with the requested information, using the information in the following table:

    Setting Suggested value Description 
    Name Any valid name For valid names, see Naming rules and restrictions.
    Address range (CIDR block) Any valid address range within the VNet (use the default)
    Network security group None
    Route table None
    Service end points None

    vm subnet details

  4. Click OK.

Create a virtual machine in the new subnet in the VNet

The following steps show you how to create a virtual machine in the same VNet in which the Managed Instance is being created.

  1. Click Create a resource in the upper left-hand corner of the Azure portal.
  2. Select Compute, and then select Windows Server 2016 Datacenter or Windows 10. This section of the tutorial is using Windows Server. Configuring Windows 10 is substantially similar.

    compute

  3. Fill out the virtual machine form with the requested information, using the information in the following table:

    Setting Suggested value Description 
    Name Any valid name For valid names, see Naming rules and restrictions.
    VM disk type SSD SSDs provide the best balance between price and performance.
    User name Any valid user name For valid names, see Naming rules and restrictions.
    Password Any valid password The password must be at least 12 characters long and meet the defined complexity requirements.
    Subscription Your subscription For details about your subscriptions, see Subscriptions.
    Resource Group The resource group that you created earlier
    Location The location that you previously selected
    Already have a Windows license No If you own Windows licenses with active Software Assurance (SA), use Azure Hybrid Benefit to save compute cost

    virtual machine create form

  4. Click OK.

  5. Select a size for the VM. To see more sizes, select View all or change the Supported disk type filter. For this tutorial, you only need a small virtual machine.

    VM sizes

  6. Click Select.

  7. On the Settings form, click Subnet and then select vm_subnet. Do not choose the subnet in which the Managed Instance is provisioned, but rather another subnet in the same Vnet.

    VM settings

  8. Click OK.

  9. On the summary page, review the offer details and then click Create to start the virtual machine deployment.

Connect to virtual machine

The following steps show you how to connect to your newly created virtual machine using a remote desktop connection.

  1. After deployment completes, go to the virtual machine resource.

    VM

  2. Click the Connect button on the virtual machine properties. A Remote Desktop Protocol file (.rdp file) is created and downloaded.

  3. To connect to your VM, open the downloaded RDP file.
  4. When prompted, click Connect. On a Mac, you need an RDP client such as this Remote Desktop Client from the Mac App Store.

  5. Enter the user name and password you specified when creating the virtual machine, then click Ok.

  6. You may receive a certificate warning during the sign-in process. Click Yes or Continue to proceed with the connection.

You are connected to your virtual machine in the Server Manager dashboard.

Important

Do not continue until your Managed Instance is successfully provisioned. After it is provisioned, retrieve the host name for your instance in the Managed instance field on the Overview tab for your Managed Instance. The name is similar to this: drfadfadsfd.tr23.westus1-a.worker.database.windows.net.

Install SSMS and connect to the Managed Instance

The following steps show you how to download and install SSMS, and then connect to your Managed Instance.

  1. In Server Manager, click Local Server in the left-hand pane.

    server manager properties

  2. In the Properties pane, click On to modify the IE Enhanced Security Configuration.

  3. In the Internet Explorer Enhanced Security Configuration dialog box, click Off in the Administrators section of the dialog box and then click OK.

    internet explorer enhanced security configuration

  4. Open Internet Explorer from the task bar.
  5. Select Use recommended security and compatibility settings and then click OK to complete the setup of Internet Explorer 11.
  6. Enter https://docs.microsoft.com/sql/ssms/download-sql-server-management-studio-ssms in the URL address box and click Enter.
  7. Download the most recent version of SQL Server Management Studio and click Run when prompted.
  8. When prompted, click Install to begin.
  9. When the installation completes, click Close.
  10. Open SSMS.
  11. In the Connect to Server dialog box, enter the host name* for your Managed Instance in the **Server name box, select SQL Server Authentication, provide your login and password, and then click Connect.

    ssms connect

After you connect, you can view your system and user databases in the Databases node, and various objects in the Security, Server Objects, Replication, Management, SQL Server Agent, and XEvent Profiler nodes.

Note

To restore an existing SQL database to a Managed instance, you can use the Azure Database Migration Service (DMS) for migration to restore from a database backup file, the T-SQL RESTORE command to restore from a database backup file, or a Import from a BACPAC file.

Next steps

In this tutorial, you learned to create an Azure SQL Database Managed Instance (preview) using the Azure portal in a dedicated subnet of a virtual network (VNet) and then connect to the Managed Instance using SQL Server Management Studio on a virtual machine in the same VNet. You learned how to:

  • Whitelist your subscription
  • Configure a virtual network (VNet)
  • Create new route table and a route
  • Apply the route table to the Managed Instance subnet
  • Create a Managed Instance
  • Create a new subnet in the VNet for a virtual machine
  • Create a virtual machine in the new subnet in the VNet
  • Connect to virtual machine
  • Install SSMS and connect to the Managed Instance

Advance to the next tutorial to learn how to restore a database backup to an Azure SQL Database Managed Instance.