Azure SQL Managed Instance

See Quill? Who is Quill?

So you have a SQL database in Azure, and Microsoft manages the instance for you? And you've got some data in it that you want to access from VM's you deploy via Tuono? That's awesome! We can do that.

First: Azure SQL Managed Instance is too long to type repeatedly. So I'm going to call it SQL-MI. I have no idea how its pronounced, so make up something interesting in your head (Editor Note: I'm going to call it MISQL... no... wait...).

In this tutorial, I'll walk you through creating SQL-MI in a way that you can automatically connect it to your Tuono deployed VMs, provided your VMs will be in the same Virtual Network as the SQL-MI.

But, but, but.... You already have a database, with data in it, don't you? That's OK. You don't need to scrap it and start over. You can keep it. This is meant as a guide. Just make sure you match some of the important configuration options that I point out and you'll be good to go.

Overview

Microsoft has a bunch of supported database types in Azure - eight as of the time of this writing. For clarity, this tutorial covers this one:

https://azure.microsoft.com/en-us/services/azure-sql/sql-managed-instance/

I'm going to tackle this in three parts. You probably only need the last part, but I want to make sure you get the full picture. I'm going to start with a virgin Resource Group in Azure - as pure as the driven snow. No yellow snow here, folks (Note: Not safe for human consumption).

The first part will setup the base infrastructure. Basically the Resource Group and a Virtual Network. This will be my stand-in for your already existing infrastructure that is managed by IT. The second part will be installing and configuring SQL-MI. It won't have any data, but we can pretend it does. The last part will be deploying a VM and demonstrating it has access to SQL-MI.

To help you with pretending its real, I'm going to deploy the three parts separately.

Base Infrastructure: I'll use a Tuono environment to instantiate a suitable network - "Main-Net"
SQL-MI: I'll manually deploy a SQL-MI instance, to better simulate a already existing instance
Database Client: I'll use another Tuono environment - "SQL-User" - to bring everything together

Part 1 - Deploy Base Infrastructure

I'm going to use Tuono to do this. Maybe you did, too. Or maybe not. It doesn't really matter much - we just need someplace to put the rest of the stuff, and this is a very easy way to do that.

This is really simple - it creates a folder, a network, and a subnet for SQL-MI. We also need to add rules to allow SQL traffic into the subnet. Azure will automatically add a bunch of rules to this firewall, but they don't add rules to allow access to the database. Seriously. So we'll do it for them. Here's the blueprint:

# This example creates a folder and a network managed by IT
# Main-Net.yml
---
location:
  region:
    my-region:
      country: USA
      area: northwest
  folder:
    example:
      region: my-region
      name: Main-Net-example

networking:
  network:
    testing:
      range: 10.0.0.0/16
      scope: public
  subnet:
    sqlmi:
      range: 10.0.1.0/24
      network: testing
      firewall: sql
  protocol:
    sql:
      ports:
        - port: 1433
          proto: tcp
        - port: 11000-11999
          proto: tcp
  firewall:
    sql:
      rules:
        - protocols: sql
          to: self

I add my Azure credentials, create a new Environment "Main-Net", and add the blueprint above to it.

Then I apply the Environment to Azure.

Part 2 - Deploy SQL-MI

The next step is to manually deploy the SQL-MI instance to the VNET and subnet created in part 1.

To start, login to the Azure Portal and go to the Azure SQL service. On the "SQL deployment option" page, select "Create" on the middle box (SQL managed instances).

Make sure you pick:

The same Resource Group you deployed in the first part: Main-Net-example (example, prefixed with the Environment's prefix of "Main-Net")
1. A different Resource Group might be OK, but I didn't test that yet.
The same region you deployed to: us-west-2
1. This must be true. Or you wont be able to add it to the subnet you created, breaking everything.

Provide the instance with a name and set the Managed Instance admin login and password which we will use later to connect. You can also resize the compute and storage to a cheaper option than the default.

When done, click "Next".

For Networking, make sure you choose the VNET and subnet from the Blueprint: testing/testing-sqlmi in our example.

I'd also recommend you set the Connection type to Redirect, as it is more performant than Proxy. But Proxy should work OK, too.

Once done, click "Next"

It is also ok to just accept the defaults on the following pages, but you can optionally set some tags, if required. Click "Create" when done.

This will trigger the deployment, which takes about 15-180 minutes, depending on the time of day you do it (4-7pm of the region you are deploying to seems to be the worst time - I'd avoid that. Don't ask how I know.). Yeah, Azure can be a bit pokey at times. Fire up reddit, browse new, and laugh at the dumb stuff people put on the internet.

Fast-forward a bit.

Yay! The SQL instance is up! And my beard only grew 2cm this time.

From the SQL Managed Instance I'm going to add an empty db called test-sql, so we have something to log into. But yours will have real data in it.

Part 3 - Deploy SQL-User

Now we deploy a VM to a new subnet in the same VNET as the SQL-MI. Note that in this Blueprint, I've set the network and the SQL-MI subnet both to readonly: true, as you would need to do for IT controlled assets.

---
variables:
  admin_username:
    description: The username for the administrative user.
    type: string
    default: adminuser
  admin_public_key:
    description: The OpenSSH Public Key to use for administrative access.
    type: string
    default: ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQDDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDummyDu= dummy_key@tuono.com

location:
  region:
    my-region:
      country: USA
      area: northwest
  folder:
    example:
      region: my-region
      name: Main-Net-example

networking:
  network:
    testing:
      range: 10.0.0.0/16
      scope: public
      readonly: true
  subnet:
    sqlmi:
      range: 10.0.1.0/24
      network: testing
      readonly: true
    public2:
      range: 10.0.2.0/24
      network: testing
      firewall: only-ssh-access
      scope: public
  protocol:
    ssh:
      ports:
        - port: 22
          proto: tcp
  firewall:
    only-ssh-access:
      rules:
        - protocols: ssh
          to: self

compute:
  image:
    bionic:
      publisher: Canonical
      product: UbuntuServer
      sku: 18.04-LTS
      venue:
        aws:
          # if provisioning fails due to image not found, go to:
          # https://cloud-images.ubuntu.com/locator/ec2/
          # and search for "bionic amd64 ebs us-west-2"
          image_id: ami-04bb0cc469b2b81cc
  vm:
    example-vm:
      cores: 1
      memory: 1 GB
      image: bionic
      configure:
        admin:
          username: (( admin_username ))
          public_key: (( admin_public_key ))
      nics:
        demo-nic:
          ips:
            - private:
                type: dynamic
              public:
                type: static
          firewall: only-ssh-access
          subnet: public2
      tags:
        wicked: cool

So now I've created a new Environment named "SQL-User", added my credentials to it, and added the above Blueprint to it.

You'll see that this example deploys a Linux VM. You can use a Windows VM if you are more of the "Light and Fluffy" Windows type (inside joke to a co-worker, let's see if he actually reads this).

Make sure to update the admin_public_key with your own public key - otherwise you will not be able to SSH to the VM.

Once ready, apply the Environment. This should take about 5-10 minutes. Maybe watch a few TikTok videos or a Twitch stream, since you got to read all of reddit on deployment of SQL-MI (I never knew reddit had an end until I deployed SQL-MI).

Proof of life

Everything is deployed and should be ready to use. Let's verify.

First, grab the Public IP of the VM from your "Details" in the Job log of your Tuono portal session. I hope you have your private key, or this won't work. Luckily, I memorized mine.

This is a base Ubuntu image, so we need to install a few things to be able to login to SQL.

sudo apt update
sudo apt install npm
sudo npm install -g sql-cli

Now we need the database connect info from Azure:

And we can now connect to the database:

mssql -s main-net-sql-mi.14eaf1fd1e8f.database.windows.net -u adminuser -p 'IHateStupidPasswords!' -d test-sql -e

Yes! It's Alive!

adminuser@example:~$ mssql -s main-net-sql-mi.14eaf1fd1e8f.database.windows.net -u adminuser -p 'IHateStupidPasswords!' -d test-sql -e
Connecting to main-net-sql-mi.14eaf1fd1e8f.database.windows.net...(node:24994) [DEP0064] DeprecationWarning: tls.createSecurePair() is deprecated. Please use tls.Socket instead.
done

sql-cli version 0.6.2
Enter ".help" for usage hints.
mssql>

I assume you know how to use a SQL database at this point - after all you got the data in there some how. With that, I'll leave you to it - happy data-ing!

PreviousAWS RDS NextCreating SSH Keypairs

Last updated 4 years ago

Was this helpful?