Plan and implement data platform resources

Choosing a database offering

Decision shortcuts

• App needs SQL Agent, cross-database transactions, linked servers or Service Broker and you want PaaS: Managed Instance.
• New cloud-native app, single database or many similar tenant databases: SQL Database (elastic pool for the many-tenant case).
• Need OS-level access, an older SQL Server version, FILESTREAM, SSIS on the same host, or unsupported features: SQL Server on a VM.
• Need the largest storage and fast scale-out reads: SQL Database Hyperscale.

Purchasing models and service tiers

vCore service tiers:

• General Purpose: remote storage, standard HADR. Serverless compute is available for SQL Database.
• Business Critical: local SSD, built-in readable secondary, lowest latency, in-memory OLTP on SQL Database.
• Hyperscale (SQL Database): page servers, up to 100 TB, near-instant backups and fast restores regardless of size, named replicas and HA replicas for read scale-out. Reverse migration back to General Purpose is possible only within a limited window after conversion (check the current doc for the number of days).
• Next-gen General Purpose on Managed Instance gives higher storage/IOPS flexibility than the original tier.

Serverless (SQL Database vCore General Purpose): autoscaling between a minimum and maximum vCore and auto-pause after an idle delay, billed per second for compute while online. Good for intermittent workloads. A paused database resumes on the first connection with a delay, so it’s wrong for latency-sensitive always-on apps. Provisioned compute is better for steady load.

Elastic pools share resources (eDTUs or vCores) among databases with different peak times. Cheaper than sizing each database for its own peak. Not a fit when every database peaks at the same time.

Automated deployment methods

You pick a method that is repeatable and declarative when a question says “consistent environments”, “source control” or “CI/CD”.

Deployment tips: a logical server is the parent of SQL Database databases and carries the firewall, the Entra admin and auditing settings. Managed Instance deployment takes hours the first time (cluster creation in a dedicated subnet), and later instances in the same subnet are faster. SQL Server VMs come from marketplace images with the SQL Server IaaS Agent extension, which turns on portal management, automated backup and automated patching.

Azure Arc-enabled SQL services

Azure Arc-enabled SQL Server connects SQL Server running anywhere (on-premises, another cloud) to Azure for management: inventory, Defender for Cloud, Microsoft Entra authentication, Azure Update Manager patching, best practices assessment, Azure Monitor and pay-as-you-go billing. Use it to get one management plane for a hybrid estate without moving data.

Azure Arc-enabled SQL Managed Instance runs a Managed Instance on your own Kubernetes infrastructure (data controller needed), with PaaS-style updates, point-in-time restore and elastic scale, in places where data must stay on-premises or at the edge. Use it for data residency, edge, or disconnected needs while keeping the Managed Instance experience.

SQL database in Microsoft Fabric

SQL database in Fabric is a Fabric-native, auto-scaling operational database built on the SQL Database engine. Its data is mirrored automatically into OneLake in Delta format, so analytics, notebooks and Power BI see transactional data without ETL. Use it for application back ends and operational data that analytics teams also need, with the simplest provisioning (no server or tier decisions) and capacity billed through Fabric. It is not a replacement for large, tuned production OLTP with full tier and networking control.

Table partitioning

Partitioning splits a large table or index into ranges that behave like one table.

1. Create a partition function (data type and boundary values, RANGE LEFT or RANGE RIGHT).
2. Create a partition scheme mapping partitions to filegroups (on SQL Database and MI everything is PRIMARY; filegroup placement matters on VMs).
3. Create the table or clustered index ON the scheme(partition column).

Benefits: faster loads and deletes by switching a partition (ALTER TABLE ... SWITCH, a metadata operation), partition elimination for queries that filter on the key, per-partition index rebuilds and compression. Rules: the partition column must be part of the clustered key and of unique indexes. Planning: choose a column used in filters and for lifecycle management (date), a boundary set that you can split and merge over time, and RANGE RIGHT for date boundaries. Partitioning is a manageability feature first. It does not guarantee faster queries.

Database sharding

Sharding spreads data across multiple databases (horizontal partitioning) when a single database can’t hold the load or size, or for tenant isolation. In Azure SQL you use the Elastic Database client library and tools:

• Shard map manager: a database that stores the mapping of sharding key to shard. Types are list maps and range maps; with a single-tenant-per-database model use list maps.
• Data-dependent routing: the app opens a connection by key and the library routes to the right shard.
• Multi-shard queries (fan-out), and split-merge to move key ranges between shards.
• Elastic jobs to run schema changes across all shards, and elastic pools to host the shards cost-effectively.

Hyperscale (100 TB) removes many reasons to shard on size alone. Shard when you need to exceed single-database compute, isolate tenants, or keep data in specific regions.

Deploy to selected platforms and hybrid

Hybrid SQL Server solutions include: SQL Server on-premises with Arc, Managed Instance link (near-real-time replication of an on-premises or VM SQL Server to Managed Instance using distributed availability groups, usable for migration, DR or read offload), transactional replication into Azure, backup to URL (SQL Server writes to Azure Blob), log shipping to Azure VMs, and stretched Always On availability groups with replicas in Azure. Connectivity is via VPN gateway or ExpressRoute.

Patching and updates

Patch cadence on VMs: cumulative updates (CU) for features and fixes, GDR for security-only. In hybrid AGs patch secondary replicas first, fail over, then patch the former primary.

Configure resources for scale and performance

Azure SQL Database

• Scale up or down between tiers and sizes online (brief connection drop at the end of the switch). Configure max vCores/DTUs, storage max size, and for serverless the min/max vCores and auto-pause delay.
• Use elastic pools for many databases, read scale-out (Business Critical, Premium, Hyperscale) by adding ApplicationIntent=ReadOnly to the connection string, and Hyperscale named replicas for isolated read workloads.
• Add zone redundancy for availability and pick the right backup storage redundancy.

Azure SQL Managed Instance

• Choose service tier (General Purpose, Business Critical), hardware generation, vCores and reserved storage. Scale vCores and storage independently within limits. Scaling is an online operation but takes time (can be hours) because it can involve moving to a new cluster. Plan a subnet large enough for scaling (extra IPs).
• Use instance pools to host small instances cheaply.

SQL Server on Azure VMs

• Select memory-optimized VM series (E, M) with a high memory-to-vCore ratio and constrained vCPU sizes to lower license cost.
• Storage: Premium SSD v2 or Ultra Disk for demanding IO, Premium SSD with host caching read-only for data files and none for log files. Use multiple disks with Storage Spaces striped. Put tempdb on the local temporary disk (NVMe/SSD) if it’s present. Follow the SQL Server on Azure VM performance guidelines checklist.
• Enable accelerated networking, and configure max server memory, MAXDOP and tempdb files (one per core up to 8).

Data compression

Use sp_estimate_data_compression_savings to estimate before applying with ALTER TABLE ... REBUILD WITH (DATA_COMPRESSION = PAGE). Compression helps IO-bound, read-heavy workloads and reduces storage and memory use. It costs CPU on write-heavy tables. You can set compression per partition.

Migration strategy

Evaluate requirements

Assess: downtime tolerance, database size and bandwidth, version and feature compatibility, target platform, dependencies (SSIS, linked servers, logins, jobs), compliance and cutover rollback.

Tools:

• Azure Migrate and the Azure SQL Migration extension experience in SSMS/Azure Data Studio successors, with assessment, SKU recommendation from performance data collection, and readiness checks. (Azure Data Studio was retired in 2026, so use SSMS and the Azure portal flow.)
• Data Migration Assistant (DMA) legacy assessment for compatibility issues, and Database Experimentation Assistant for workload comparison across versions.
• Azure Database Migration Service (DMS) for the data movement itself.

Offline vs online

Paths by target

Notes that get tested:

• A BACPAC is schema plus data, not transaction-consistent unless the source is quiesced or you export from a copy. Use SqlPackage import/export. It is for small and medium databases.
• Log Replay Service restores log backups continuously from Blob storage to Managed Instance, for online migration with a controlled cutover. The database stays restoring and can’t be read during migration.
• Managed Instance link uses a distributed availability group, supports reverse failback to SQL Server 2022 and later, and the target is readable.
• Native backup restore to MI doesn’t support restoring a TDE-encrypted database without the certificate (a managed-key MI can restore service-managed TDE backups differently). Move the certificate first.
• Migrating logins, jobs, linked servers is separate work: logins via DMS login migration or scripts with sp_help_revlogin; Agent jobs via scripts.

Migration between Azure SQL services

• SQL Database to SQL Database (copy): CREATE DATABASE new AS COPY OF server.source is an online, transactionally consistent copy, within the same or another server.
• SQL Database to Managed Instance: BACPAC or DMS, and the MI link doesn’t apply (it is SQL Server to MI).
• Managed Instance to SQL Server on VM: the MI link in reverse, or BACPAC, or COPY_ONLY backup restore if the SQL Server version is at least as high.
• Managed Instance to Managed Instance: database copy and move between instances (same or different subscription or region) uses the Managed Instance move/copy feature, which can use the Log Replay engine under the hood so the source stays online during the copy and the move cutover is short. Use it to relocate databases to a different instance, resize via a new instance, or consolidate.
• Switch SQL Database tier or DTU to vCore by scaling in place.

Troubleshoot a migration

After migration, raise compatibility level gradually, update statistics, capture a Query Store baseline and compare with the source. The Query Tuning Assistant helps verify plans after upgrading the compatibility level.