Migrate to an Azure SQL Managed Instance

By:   |   Updated: 2024-01-16   |   Comments (3)   |   Related: > Azure SQL Managed Instance


Problem

Are you creating a proof of concept (POC) or migrating your databases to the cloud? I don't want the headache of setting up an SQL Server on an Azure VM. Can't you create an Azure SQL database and call it a day? The problem is your SQL Server relies on multiple databases, plus you dread the IaaS maintenance overhead. What can you do, rearchitect the entire application? Wait, there's another option.

Solution

In this article, I'll explore how easy it is to get your databases on an Azure Managed Instance. Here, you'll experience the latest PaaS offering of SQL Server. Some of the questions I hope to answer include: Why would you choose a Managed Instance over an Azure SQL database in the first place? Can't I rearchitect my existing databases to save on cost? By the end of this article, you'll have the skills to move your databases to a Manage Instance today.

Explore an Azure SQL Managed Instance

A Managed Instance is Microsoft's most complete SQL Platform as a Service (PaaS) offering to date. PaaS describes a service where you forget the underlying infrastructure. When it comes to a Managed Instance, you're not responsible for:

  • Upgrading the OS
  • New versions of SQL Server
  • Applying updates, etc.

For any SQL PaaS offering, you don't need to worry about backup management, and implementing High Availability/Disaster Recovery (HA/DR) is a breeze compared to on-premises.

Why would I choose a Managed Instance over an Azure SQL database? That's a great question. It comes down to the features a Managed Instance offers. For example, a Managed Instance is a good choice if your application uses multiple databases. A typical use case involves a mature application where redesigning the data structure is off the table. An Azure SQL database also lacks features such as:

  • Service Broker
  • SQL Agent
  • CLR

What's not to love about a Managed Instance? Like raising kids, they are expensive.

Cost in the Cloud

From a cost perspective, an Azure SQL database offers you the most bang for your buck. The Azure cost calculator can help determine if it fits your budget. If you only have one database and don't care about the extra features a Managed Instance provides, then skip it. Yes, you heard me right: I'm not making a dime off big Azure.

A Managed Instance doesn't deliver a true serverless offering like an Azure SQL DB. You can lower the cost on a Managed Instance to something more reasonable. This article outlines how to host one for approximately $100 monthly on a Dev/Test subscription. Inflation will make this $100 baseline absurd in a few years. I'm continuing this article assuming you can access a Managed Instance or create one.

Building an On-premises Dataset

Now, it's time to set up a dataset for our demo. Below, I'll create two databases containing two tables. There's also a stored procedure that performs a cross-database query. Before you leave a comment, if my structure were this simple, I'd combine our databases in a heartbeat. But this example shows how easy it is to move databases to Azure. Throughout this article, I execute all scripts in SQL Server Management Studio (SSMS). For the one below, I first connect to an on-premise SQL server.

--https://www.mssqltips.com

USE [master];
GO

-- Create our first database
IF DATABASEPROPERTYEX('PizzaTracker', 'Version') IS NOT NULL
BEGIN
    ALTER DATABASE PizzaTracker SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE PizzaTracker;
END;
GO

CREATE DATABASE PizzaTracker;
GO

ALTER DATABASE PizzaTracker SET RECOVERY SIMPLE;
GO

USE PizzaTracker;
GO

CREATE TABLE dbo.MutantFighters
(
    Id INT PRIMARY KEY,
    [Name] VARCHAR(50) NOT NULL,
    Species VARCHAR(50) NOT NULL
);

INSERT INTO dbo.MutantFighters (Id, Name, Species)
VALUES
(1, 'Leonardo', 'Turtle'),
(2, 'Michelangelo', 'Turtle'),
(3, 'Donatello', 'Turtle'),
(4, 'Raphael', 'Turtle'),
(5, 'Splinter', 'Rat'),
(6, 'Bebop', 'Warthog'),
(7, 'Rocksteady', 'Rhino');

CREATE TABLE dbo.PizzaLog
(
    Id INT IDENTITY(1, 1) NOT NULL,
    MutantId INT NOT NULL,
    Slices INT NOT NULL,
    DateEaten DATE NOT NULL
);

-- Generate dates for February.
DECLARE @StartDate DATE = '2023-02-01';
DECLARE @EndDate DATE = '2023-02-28';

WITH Dates
AS (
   SELECT @StartDate AS Date
   UNION ALL
   SELECT DATEADD(DAY, 1, Date) AS Date
   FROM Dates
   WHERE Date < @EndDate
   )
INSERT INTO dbo.PizzaLog (MutantId, Slices, DateEaten)
SELECT mf.Id,
       CASE
           WHEN mf.Id = 2 THEN
               ABS(CHECKSUM(NEWID()) % 15) + 3 -- Mikey eats the most pizza            
           ELSE
               ABS(CHECKSUM(NEWID()) % 10) + 1
       END AS Slices,
       d.Date
FROM dbo.MutantFighters mf
    CROSS JOIN Dates d
WHERE mf.Id <> 5;
GO


USE [master];
GO
-- create our second database
IF DATABASEPROPERTYEX('TurtleGear', 'Version') IS NOT NULL
BEGIN
    ALTER DATABASE TurtleGear SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE TurtleGear;
END;
GO

CREATE DATABASE TurtleGear;
GO

ALTER DATABASE TurtleGear SET RECOVERY SIMPLE;
GO

USE TurtleGear;
GO

CREATE TABLE dbo.Weapons (Id INT PRIMARY KEY, [Name] VARCHAR(50) NOT NULL);

INSERT INTO dbo.Weapons (Id, [Name])
VALUES
(1, 'Katana Swords'),
(2, 'Nunchaku'),
(3, 'Bo'),
(4, 'Sai');

CREATE TABLE dbo.MutantFighters (Id INT PRIMARY KEY, [Name] VARCHAR(50) NOT NULL, WeaponId INT NOT NULL);

INSERT INTO dbo.MutantFighters (Id, Name, WeaponId)
VALUES
(1, 'Leonardo', 1),
(2, 'Michelangelo', 2),
(3, 'Donatello', 3),
(4, 'Raphael', 4);
GO

-- Create a stored procedure that crosses databases
USE PizzaTracker;
GO

CREATE OR ALTER PROCEDURE dbo.GetPizzaUsage
    @MutantId INT,
    @DateEaten DATE
AS
BEGIN

    SELECT mf.[Name] AS MutantName,
           SUM(pl.Slices) AS SlicesEaten,
           w.[Name] AS Weapon
    FROM PizzaTracker.dbo.MutantFighters mf
        INNER JOIN PizzaTracker.dbo.PizzaLog pl
            ON pl.MutantId = mf.Id
        INNER JOIN TurtleGear.dbo.MutantFighters tmf
            ON tmf.Id = mf.Id
        INNER JOIN TurtleGear.dbo.Weapons w
            ON w.Id = tmf.WeaponId
    WHERE mf.Id = @MutantId
          AND pl.DateEaten = @DateEaten
    GROUP BY mf.[Name],
             w.[Name];
END;
GO
Database List SSMS

Our existing databases are ready to go. Now, you need a place to store files in Azure. If you haven't already, create a storage account with a blob container to hold the database backup files.

Talking to the Cloud

We need a way to take full database backups from an on-premises location and drop them in a blob container. To get started, I'll create a credential in SQL Server that references a Shared Access Signature (SAS) token. Create a SAS token for your container. The four permissions I chose were:

  1. Read
  2. Write
  3. Delete
  4. List
Create a SAS token

Back in SSMS, the code below creates a credential in SQL Server that references the SAS token you created above. Credentials allow SQL Server users to talk with outside services. I named the credential the same as the URL to keep things simple. For obvious reasons, I won't share the key.

-- https://www.mssqltips.com

USE master;

IF NOT EXISTS
(
    SELECT 1
    FROM sys.credentials c
    WHERE c.name = 'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup'
)
BEGIN
    CREATE CREDENTIAL [https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
         SECRET = 'Iwillnotsharemysecretwithyou';
END;
ELSE
    PRINT 'Already there';
GO

Once you have a credential and storage account, take a full database backup and place it in a storage container using the TO URL option.

-- https://www.mssqltips.com

-- Here we make a full backup.
BACKUP DATABASE PizzaTracker
    TO URL = 'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup/pizzatracker.bak'   
    WITH 
    COMPRESSION,
    CHECKSUM,
    FORMAT;
GO

BACKUP DATABASE TurtleGear
    TO URL = 'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup/turtlegear.bak'   
    WITH 
    COMPRESSION,
    CHECKSUM,
    FORMAT;
GO

The screenshot below shows the two fresh backups we created in our container. If you're working with larger files, the process takes longer.

Storage Container

Restore to a Managed Instance

We are near the finish line for restoring our databases to the Managed Instance. Before crossing, we must allow our Managed Instance to talk with the storage account. You could use a Managed Identity, but I'll stick with a SAS token. The code below creates a SAS token. Notice the syntax is the same as SQL Server. Make sure to establish a connection via SSMS to the Managed Instance when running it. Test your access to the storage account and see if the backup files are valid with FILELISTONLY.

Connect to Managed Instance in SSMS
-- https://www.mssqltips.com

USE master;

IF NOT EXISTS
(
    SELECT 1
    FROM sys.credentials c
    WHERE c.name = 'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup'
)
BEGIN
    CREATE CREDENTIAL [https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup]
    WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
         SECRET = 'Iwillnotsharemysecretwithyou';
END;
ELSE
    PRINT 'Already there';
GO

RESTORE FILELISTONLY FROM URL =
   'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup/pizzatracker.bak';

RESTORE FILELISTONLY FROM URL =
   'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup/turtlegear.bak';
GO

Now, it's as simple as running the restore command below.

-- https://www.mssqltips.com

USE master;
GO
-- If the database doesn't exist then restore
IF NOT EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'Pizzatracker')
BEGIN
RESTORE DATABASE [PizzaTracker] FROM URL =
  'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup/pizzatracker.bak';
END
ELSE PRINT 'The database already exist.';
GO
-- If the database doesn't exist then restore
IF NOT EXISTS (SELECT 1 FROM sys.databases d WHERE d.name = 'TurtleGear')
BEGIN
RESTORE DATABASE [TurtleGear] FROM URL =
  'https://<YourStoreageAccountName>.blob.core.windows.net/pizzatrackerbackup/turtlegear.bak';
END
ELSE PRINT 'The database already exist.';
GO

Since the two databases are tiny, restoring only takes a few seconds. If everything goes as planned, you can view the databases inside Object Explorer, as seen in the screenshot below.

SSMS Managed Instance

The primary reason I picked a Managed Instance as the solution was its ability to allow cross-database queries. Let's make sure we can run the stored procedure.

-- https://www.mssqltips.com

-- Does our stored procedure work?
USE PizzaTracker;
GO

EXECUTE dbo.GetPizzaUsage @MutantId = 1,
                          @DateEaten = '02-02-2023';
GO
Stored Procedure Results

It's time to celebrate because our databases are in the cloud and talking with each other. I can't wait to look at some of the other features a Managed Instance offers in future articles.

This demo showed how easy it is to migrate from on-premises to Azure. With an Azure SQL DB, you don't have the option to restore a database backup file. You must create a BACPAC file containing the schema and data, then import it. This process can be challenging for larger databases. Microsoft recommends using SqlPackage for BACPAC files over 200MB. You can also use the Azure SQL Migration Extension for Azure Data Studio. The extension offers a slick user interface for performing migrations.

I'm interested to hear about your experiences migrating to Azure. Please let me know in the comments below what methods you've used.

Key Takeaways

  • Once you create a Managed Instance, restoring the existing databases from on-premises is a breeze. Take full backups, place them into a storage container, and restore them to the Managed Instance. Don't forget the SAS token to allow everything to connect.
  • You can run a managed instance for about $100 monthly with pausing the compute and hybrid benefits. At the time of writing, you need a dev/test subscription to use the stop/start feature.
  • Remember what I outlined in this article applies to creating a quick and easy proof of concept. When migrating a production SQL Server instance to Azure, you need to consider several things, like database permissions, agent jobs, and encryption, to name a few.
Next Steps


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Jared Westover Jared Westover is a passionate technology specialist at Crowe, helping to build data solutions. For the past two decades, he has focused on SQL Server, Azure, Power BI, and Oracle. Besides writing for MSSQLTips.com, he has published 12 Pluralsight courses about SQL Server and Reporting Services. Jared enjoys reading and spending time with his wife and three sons when he's not trying to make queries go faster.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2024-01-16

Comments For This Article




Friday, January 19, 2024 - 11:49:22 AM - Rob Carrol Back To Top (91866)
Hi Jared. I set one up recently as well, look forward to reading your experiences!

Rob

Thursday, January 18, 2024 - 4:38:40 PM - Jared Westover Back To Top (91863)
@Rob

Thanks for stopping by, adding the link, and checking out the article. I have an article coming soon about my experience creating a free Managed Instance.

Thursday, January 18, 2024 - 11:04:23 AM - Rob Carrol Back To Top (91861)
You can now run a free trial of SQL Managed Instance for 12 months: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/free-offer?view=azuresql














Related Articles

Azure SQL Managed Instance vs. Azure SQL Database

Enable Auditing for Azure SQL Managed Instance

Deploy and Configure Azure SQL Managed Instance