By: Jared Westover | 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
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:
- Read
- Write
- Delete
- List
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.
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.
-- 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.
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
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
- Are you interested in learning more about a Managed Instance? Bob Ward and Niko Neugebauer are the two best resources on the web. Here's a video where they cover all the features at a high level: "Inside Azure SQL Managed Instance By Bob Ward and Niko Neugebauer."
- Daniel Taylor built a four-part series on the "Tales From the Field" YouTube channel using Azure Data Studio to migrate to a Managed Instance. If you're into Azure, subscribe to them.
- For a quick overview of a Managed Instance, please check out John Martin's "Introduction to Azure SQL Database Managed Instances."
About the author
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
