SQL Server in the Cloud (Azure SQL)

What Is Azure SQL?

Azure SQL is Microsoft's family of fully managed cloud database services built on the SQL Server engine. The same T-SQL you have learned throughout this course runs on Azure SQL — SELECT, JOIN, GROUP BY, stored procedures, triggers, UDFs, indexes — all work identically. What changes is the infrastructure layer: Microsoft handles the hardware, operating system, SQL Server installation, patching, and high availability. You manage only the database itself.

Azure SQL comes in three deployment options, each suited to a different scenario. Azure SQL Database is a fully managed single database — the simplest option, best for new cloud-native applications. Azure SQL Managed Instance is a near-complete SQL Server instance in the cloud — the closest to on-premises, best for lifting and shifting existing SQL Server workloads. SQL Server on Azure Virtual Machines is a full SQL Server installation on a cloud VM — maximum control and compatibility, closest to running your own server, but you manage the OS and SQL Server updates.

• Azure SQL Database — single database, fully managed, serverless or provisioned compute, best for new apps
• Azure SQL Managed Instance — full SQL Server instance, supports SQL Agent, cross-database queries, linked servers
• SQL Server on Azure VM — full control, supports all SQL Server features, you manage OS patching
• All three run the same T-SQL engine — your query skills transfer directly
• Azure SQL Database and Managed Instance include automatic backups, high availability, and geo-replication
• Pricing is based on compute tier (DTUs or vCores), storage size, and region

-- Azure SQL Database behaves like SQL Server -- the same T-SQL works.
-- A few system queries differ slightly. Check which service you are on:
SELECT
    @@SERVERNAME                    AS ServerName,
    @@VERSION                       AS SqlVersion,
    SERVERPROPERTY('Edition')       AS Edition,        -- e.g. SQL Azure
    SERVERPROPERTY('EngineEdition') AS EngineEdition,  -- 5 = Azure SQL DB, 8 = Managed Instance
    DB_NAME()                       AS CurrentDatabase;
-- Azure SQL Database does not have sys.databases for all databases on the server.
-- Use sys.databases scoped to the current database context:
SELECT name, compatibility_level, collation_name, state_desc
FROM sys.databases
WHERE name = DB_NAME();
-- Check service tier and compute size (Azure SQL Database only):
SELECT
    database_id,
    service_objective  AS ServiceTier,    -- e.g. S3, P1, GP_Gen5_4, Serverless
    edition            AS Edition
FROM sys.database_service_objectives;

• SERVERPROPERTY('EngineEdition') returns 5 for Azure SQL Database and 8 for Managed Instance — useful for writing scripts that behave differently depending on the platform
• sys.database_service_objectives is an Azure-specific view — it does not exist on on-premises SQL Server
• The SQL Azure version string in @@VERSION confirms you are on the cloud engine, not a local instance
• Most system views (sys.tables, sys.columns, sys.indexes) work identically on Azure SQL Database and on-premises SQL Server

Key Differences from On-Premises SQL Server

Azure SQL Database removes or restricts several on-premises features that depend on direct OS access. Understanding these differences prevents confusion when migrating existing scripts or building new applications. The restrictions mainly affect server-level administration — the data access and manipulation features you use daily are unchanged.

The most important differences are: there is no SQL Server Agent in Azure SQL Database (use Elastic Jobs instead), there are no cross-database queries with three-part names like OtherDB.dbo.Table (Managed Instance supports this), there is no xp_cmdshell or file system access, BULK INSERT requires Azure Blob Storage instead of a local file path, and USE DatabaseName cannot switch databases mid-session — you connect directly to the target database. Managed Instance restores most of these capabilities.

-- Things that work differently on Azure SQL Database vs on-premises.
-- WORKS the same: all DML, DDL, indexes, views, stored procedures, triggers, UDFs
SELECT o.OrderId, u.FirstName, SUM(oi.Price * oi.Quantity) AS Total
FROM Orders o
JOIN Users      u  ON o.UserId    = u.UserId
JOIN OrderItems oi ON o.OrderId   = oi.OrderId
GROUP BY o.OrderId, u.FirstName
HAVING SUM(oi.Price * oi.Quantity) > 500;
-- DIFFERENT: BULK INSERT uses Azure Blob Storage, not a local file path
-- On-premises:
-- BULK INSERT dbo.Products FROM 'C:\Data\products.csv' WITH (FORMAT = 'CSV');
-- Azure SQL Database:
BULK INSERT dbo.Products
FROM 'products.csv'
WITH (
    DATA_SOURCE = 'AzureBlobStorage',  -- named external data source
    FORMAT      = 'CSV',
    FIRSTROW    = 2
);
-- DIFFERENT: no USE statement to switch databases mid-session on Azure SQL DB
-- This works on-premises but fails on Azure SQL Database:
-- USE OtherDatabase;  -- Error: USE statement is not supported
-- SAME on Managed Instance, DIFFERENT on SQL Database:
-- Cross-database query
-- SELECT * FROM OtherDB.dbo.Products;  -- works on MI, fails on SQL DB

• All DML and DDL — SELECT, INSERT, UPDATE, DELETE, CREATE TABLE, ALTER TABLE — works identically on Azure SQL Database
• BULK INSERT on Azure SQL requires an external data source pointing to Azure Blob Storage instead of a local file path
• Cross-database queries are unavailable on Azure SQL Database — design your schema to fit within a single database, or use Managed Instance
• When migrating scripts from on-premises to Azure SQL Database, search for USE, xp_cmdshell, three-part names, and BULK INSERT file paths — these are the most common incompatibilities

Connecting to Azure SQL from SSMS

Connecting SSMS to Azure SQL Database uses the same connection dialog as on-premises SQL Server — server name, authentication type, and credentials. The server name follows the format yourserver.database.windows.net. Authentication options include SQL Server Authentication (username and password), Azure Active Directory Password, and Azure Active Directory Integrated (single sign-on). After connecting, the Object Explorer shows the database objects exactly as it does for on-premises instances.

-- Once connected via SSMS, standard queries work as normal.
-- Check firewall and connectivity first if the connection is refused.
-- Verify connected server and current user:
SELECT
    @@SERVERNAME                        AS AzureServer,
    DB_NAME()                           AS Database_,
    SYSTEM_USER                         AS LoginName,
    USER_NAME()                         AS DatabaseUser,
    CONNECTIONPROPERTY('net_transport') AS Transport,      -- TCP
    CONNECTIONPROPERTY('client_net_address') AS ClientIP;  -- your IP
-- Azure SQL firewall: your client IP must be allowed.
-- Add a firewall rule via the Azure Portal, or run this from master db (admin only):
-- EXECUTE sp_set_firewall_rule N'MyOfficeIP', '203.0.113.10', '203.0.113.10';
-- Check which users exist in the current database:
SELECT
    name        AS DatabaseUser,
    type_desc   AS UserType,
    create_date
FROM sys.database_principals
WHERE type IN ('S', 'U', 'E')   -- SQL user, Windows user, External (AAD) user
ORDER BY name;

• The server name ends in .database.windows.net — this is the Azure SQL Database endpoint format
• Azure Active Directory users appear as EXTERNAL_USER in sys.database_principals — they authenticate through AAD rather than SQL Server credentials
• If the connection is refused, the most common cause is a missing firewall rule — the client IP must be whitelisted in the Azure Portal or via sp_set_firewall_rule
• CONNECTIONPROPERTY('client_net_address') shows your current IP — copy it into the firewall rule if you are troubleshooting a blocked connection

Backups, High Availability, and Scaling

Azure SQL Database manages backups, high availability, and scaling automatically — these are no longer DBA tasks when using the fully managed service. Full backups are taken weekly, differential backups every 12 hours, and transaction log backups every 5 to 10 minutes. All backups are stored in geo-redundant Azure Blob Storage and retained for 7 to 35 days depending on the tier. Point-in-time restore is available through the Azure Portal or via T-SQL without any manual restore scripting. High availability is built in — the General Purpose tier provides 99.99% uptime SLA using storage-level redundancy, and the Business Critical tier adds in-memory replicas for sub-second failover.

-- Azure SQL Database manages backups automatically.
-- You can still initiate a point-in-time restore from the Azure Portal
-- or query the restore points available for your database.
-- View available restore points (Azure SQL Database):
SELECT
    earliest_restore_date   AS EarliestRestorePoint,
    GETUTCDATE()            AS CurrentUTCTime,
    DATEDIFF(DAY, earliest_restore_date, GETUTCDATE()) AS RetentionDays
FROM sys.database_recovery_status
WHERE database_id = DB_ID();
-- Scaling: change the service tier without downtime (brief connection drop only)
-- Run from the master database as an admin:
-- ALTER DATABASE DataplexaStore MODIFY (SERVICE_OBJECTIVE = 'GP_Gen5_8');
-- GP_Gen5_8 = General Purpose, Gen5 hardware, 8 vCores -- doubles the compute
-- Monitor active connections and DTU/CPU usage on Azure SQL:
SELECT
    end_time,
    avg_cpu_percent,
    avg_data_io_percent,
    avg_log_write_percent,
    max_worker_percent,
    max_session_percent
FROM sys.dm_db_resource_stats        -- Azure-specific DMV -- last 64 intervals of 15s
ORDER BY end_time DESC;

• sys.database_recovery_status shows the earliest available restore point — 30 days of point-in-time restore is available on this database
• sys.dm_db_resource_stats is an Azure-specific DMV that records resource utilisation every 15 seconds — the equivalent of DMV performance monitoring on on-premises, scoped to the current database
• Scaling to a larger service tier (ALTER DATABASE ... MODIFY SERVICE_OBJECTIVE) takes effect with only a brief connection interruption — no downtime window needed
• If avg_cpu_percent or avg_data_io_percent is consistently above 80%, it is a signal to scale up to a higher tier or optimise the workload

Migrating from On-Premises to Azure SQL

The recommended tool for assessing and migrating an on-premises SQL Server database to Azure SQL is the Azure Database Migration Service (DMS) combined with the Database Migration Assessment feature in Azure Data Studio or SSMS. The assessment scans your database for features not supported in Azure SQL Database and reports any incompatibilities before migration begins. Common blockers include cross-database queries, SQL Server Agent jobs, linked servers, and certain deprecated syntax. Once incompatibilities are resolved, DMS handles the schema migration and data transfer with minimal downtime using online migration for large databases.

-- Pre-migration compatibility check: identify features that may not work on Azure SQL DB.
-- Run on the on-premises source database before migrating.
-- Check for linked servers (not supported in Azure SQL Database):
SELECT name, product, provider, data_source
FROM sys.servers
WHERE is_linked = 1;
-- Check for SQL Server Agent jobs (not available in Azure SQL DB -- use Elastic Jobs):
SELECT job_id, name, enabled, date_created
FROM msdb.dbo.sysjobs
ORDER BY name;
-- Check for three-part cross-database object references in stored procedures:
SELECT DISTINCT
    o.name  AS ProcedureName,
    m.definition
FROM sys.sql_modules      m
JOIN sys.objects          o ON m.object_id = o.object_id
WHERE o.type = 'P'                          -- stored procedures only
  AND m.definition LIKE '%].dbo.[%'        -- three-part name pattern
ORDER BY o.name;
-- After migration: verify row counts match between source and target
SELECT
    t.name              AS TableName,
    p.rows              AS RowCount_
FROM sys.tables     t
JOIN sys.partitions p ON t.object_id = p.object_id
WHERE p.index_id IN (0, 1)   -- heap or clustered index = base table rows
ORDER BY p.rows DESC;

• The linked server to LEGACY_ERP must be resolved before migration — either remove the dependency, replace with an API call, or switch to Managed Instance which supports linked servers
• SQL Server Agent jobs must be recreated as Azure Elastic Jobs or Azure Logic Apps after migration to Azure SQL Database
• The three-part name check surfaces stored procedures that reference other databases — these will fail on Azure SQL Database and must be refactored
• Post-migration row count verification confirms all data transferred correctly — compare this output against the same query run on the source before migration

Lesson Summary

Practice Questions

Quiz

Next up — Mini Project — Put everything together in a real end-to-end scenario: design a schema, write queries, build stored procedures, and analyse data using all the skills from this course.