SQL SERVER – A Quick Notes on SQL Azure

SQL Azure was the original name and it is now Azure SQL Database. The engine is the same one you already know, so your T-SQL mostly moves unchanged. What changes is everything around the query: no agent, no cross database joins, and backups you no longer control.

The Name Changed, and So Did the Shape

SQL Azure was renamed Azure SQL Database, and the family grew. There is now a single database, an elastic pool that shares resources across several, and a managed instance that behaves far more like a whole SQL Server.

That last one matters if you are moving an existing application. Managed instance exists precisely because so many things did not fit in a single database.

What Carries Over Without Thinking

Tables, indexes, views, stored procedures, functions, triggers. Window functions, common table expressions, the JSON functions, temporal tables. Execution plans look the same and Query Store is there.

If your code is ordinary T-SQL against your own tables, it almost certainly runs. The surprises are never in the query language.

What You Give Up

SQL Server Agent. There are no jobs in a single database. Anything you schedule has to move to Elastic Jobs, an Azure Automation runbook, or a function somewhere. This is the single biggest migration surprise, because every real system has jobs.

Cross database queries. Three part names do not work. Each database is its own island, and reaching another one needs an external data source defined on purpose.

USE. You cannot switch database in a session. You connect to the one you want.

Server level things. No linked servers in a single database, no filestream, no direct access to the file system, no xp_cmdshell, and no control over where files live.

Some DMVs. Many are there, several are renamed, and a few are simply absent. Monitoring scripts written for a normal instance need checking rather than copying.

What You Stop Having to Do

Backups happen without you. Point in time restore is built in, with a retention window you choose. Patching is done for you. High availability is included rather than designed.

For a small team that has never had a dedicated database person, that list is the entire reason to go. The work that used to be somebody’s Saturday is now a setting.

Checking Whether Your Code Would Move

Run this against your current database before anybody promises a date:

-- three part names, which will not work in a single database
SELECT OBJECT_SCHEMA_NAME(object_id) AS sch, OBJECT_NAME(object_id) AS obj
FROM sys.sql_modules
WHERE definition LIKE '%[a-z]%.[a-z]%.[a-z]%.%'
ORDER BY sch, obj;

-- anything reaching outside the database
SELECT OBJECT_NAME(object_id) AS obj
FROM sys.sql_modules
WHERE definition LIKE '%xp_cmdshell%'
   OR definition LIKE '%OPENROWSET%'
   OR definition LIKE '%OPENQUERY%'
   OR definition LIKE '%sp_send_dbmail%';

Then count what you would have to rehome:

SELECT j.name, j.enabled, COUNT(s.step_id) AS steps
FROM msdb.dbo.sysjobs AS j
LEFT JOIN msdb.dbo.sysjobsteps AS s ON s.job_id = j.job_id
GROUP BY j.name, j.enabled
ORDER BY j.name;

That job list is usually the moment the conversation gets realistic.

Knowing Which One You Are Connected To

SELECT SERVERPROPERTY('EngineEdition')      AS engine_edition,
       SERVERPROPERTY('ProductVersion')     AS version,
       DB_NAME()                            AS current_database;

EngineEdition is the reliable test. It returns 5 for Azure SQL Database and 8 for a managed instance, and those numbers are how a deployment script should branch rather than guessing from the version.

The Cost Model Is the Real Change

On your own hardware the cost is fixed and the tuning is optional. In Azure the cost is a dial, so a badly written query is now a line on an invoice.

That cuts both ways. People who never looked at a query plan start looking, which is a good outcome. And people who solve a performance problem by raising the tier are buying an expensive habit.

What I Would Actually Check First

Do you have agent jobs, and where would they go. Does anything query across databases. Does anything touch the file system or send mail. What is your real recovery requirement, and does the built in retention meet it.

Four questions. They decide whether this is a weekend or a quarter, and they are worth answering before anybody signs anything.

Azure SQL Database is not SQL Server somewhere else, it is the same engine with the machine taken away.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.