Domain 1 of 5 · Chapter 1 of 3

Plan and deploy Azure SQL solutions

Three Azure SQL products, three levels of control

Suppose an order-processing application runs a nightly SQL Server Agent job and a stored procedure that reads Billing.dbo.Invoices, a table in a second database on the same server: that job and that three-part name (database.schema.object) decide where the application can run, because Azure SQL Database supports neither SQL Server Agent nor cross-database three-part-name queries, while Azure SQL Managed Instance supports both[1]. The Microsoft DP-300 study guide[2] lists that choice as 'Recommend a database offering based on specific requirements' under Plan and deploy Azure SQL solutions (Plan and implement data platform resources). The Microsoft DP-300 study guide[2] lists 'Configure table partitioning' under Configure resources for scale and performance and 'Implement a migration to Azure' under Plan and implement a migration strategy, both in Plan and implement data platform resources, and 'Automate deployment by using Azure Resource Manager (ARM) and Bicep templates' under Automate deployment of database resources (Configure and manage automation of tasks).

Three levels of control

The Azure SQL family has three products[3], and Microsoft's comparison separates them by how much of SQL Server each one gives you, from database-level capabilities through instance-level capabilities to operating system-level access[3]:

Three more targets sit outside these three products: SQL database in Microsoft Fabric, distinct from an Azure SQL Database[3]; SQL Server enabled by Azure Arc, for SQL Server instances hosted outside of Azure[6]; and SQL Managed Instance enabled by Azure Arc, which runs on Kubernetes and the infrastructure of your choice[7].

Each service offering can be characterized by the level of administration you have over the infrastructure[3], as the figure below shows. The nested boxes show levels of administrative control, not strict feature supersets.

Requirements that set the level

Find the highest level any single requirement needs:

Requirement Lowest level that meets it
SQL Server Agent jobs or Service Broker Instance level
Three-part-name queries or transactions across databases on one instance Instance level
A linked server to another SQL Server or Azure SQL Database Instance level
CLR that reads a local file share, FILESTREAM, or FileTable Operating-system level, on Windows
Operating-system administration or extra software on the host Operating-system level

The instance-level rows come from the feature comparison for SQL Database and SQL Managed Instance[1]. That page also records what replaces each missing feature on Azure SQL Database: elastic jobs instead of SQL Server Agent, and elastic query instead of cross-database queries and linked servers[1]. Managed Instance's linked servers reach SQL Server, SQL Managed Instance, SQL Database, and Azure Synapse SQL serverless and dedicated pools[8]. With the Distributed Transaction Coordinator (DTC), network connectivity, and DNS configured, distributed T-SQL transactions between Managed Instance and SQL Server are supported, but DTC transactions to Azure SQL Database aren't[9].

CLR runs on Managed Instance, but without access to the file system in the CREATE ASSEMBLY statement[1], so CLR code that needs a local file share still needs a VM. Managed Instance doesn't support FILESTREAM[1]. SQL Server on Linux doesn't support FileTable, FILESTREAM, or CLR assemblies with the EXTERNAL_ACCESS or UNSAFE permission set[10], so those workloads need SQL Server on a Windows VM.

An elastic pool doesn't move a workload up a level. Databases in an elastic pool are on a single server and share a set number of resources at a set price[11], so pooled databases still have no SQL Server Agent. Pooling is a cost and sizing choice, not a compatibility choice.

The level also sets who maintains it

With SQL Database and SQL Managed Instance, Azure configures, patches, and upgrades the database software[3]. With SQL Server on Azure VMs, you decide when to update the operating system and the database software[3]. Choosing a VM for a feature the workload doesn't use adds patching and maintenance work, so check whether another VM-specific requirement justifies it.

In short: list the instance-level and host-level features the workload uses, then choose the lowest level that covers all of them.

Operating-system levelSQL Server on Azure VMsInstance levelAzure SQL Managed InstanceDatabase levelAzure SQL Database
Nested levels of control in the Azure SQL family: database level, instance level, and operating-system level.

Purchasing models: vCore, DTU, and Azure Hybrid Benefit

This section covers how you buy Azure SQL capacity, because the purchasing model limits both the size options and the license discounts you can use.

Azure SQL Database offers two purchasing models. A DTU (database transaction unit) represents a blended measure of CPU, memory, reads, and writes[12], and the DTU-based purchasing model is available in the Basic, Standard, and Premium service tiers and for elastic pools[12]. You pick a size, not the hardware under it. By comparison, the vCore purchasing model lets you choose and scale compute and storage independently[12]. A vCore (virtual core) is a logical CPU, and vCore costs depend on the service tier (General Purpose, Business Critical, or Hyperscale), the hardware configuration, the vCores and memory, reserved database storage, and backup storage[13].

Only the vCore model offers Azure Hybrid Benefit discounts and reserved instance pricing[13], which the pricing page calls Azure SQL Database reserved capacity: prepaying for compute capacity on a one- or three-year term[14]. The DTU model exists only in Azure SQL Database, and the vCore model covers both SQL Database and SQL Managed Instance[3]. A requirement to size Managed Instance in DTUs therefore has no answer.

Azure Hybrid Benefit

Azure Hybrid Benefit lets you apply SQL Server licenses that have Software Assurance to Azure SQL Database and Azure SQL Managed Instance[15]. License Included pricing is made up of base compute and SQL license components, while Azure Hybrid Benefit pricing is made up of base compute and Software Assurance components[15]. You still pay for compute. The benefit removes the SQL license component.

To turn it on, set the license type to BasePrice. PowerShell uses -LicenseType on New-AzSqlDatabase or New-AzSqlInstance, and the Azure CLI uses --license-type on az sql db create or az sql mi create[15]. The other value, LicenseIncluded, keeps the SQL license in the price.

Eligibility is narrower in Azure SQL Database:

Deployment Azure Hybrid Benefit
SQL Database, vCore, provisioned compute Available except for new Hyperscale databases and dev/test subscriptions
SQL Database, vCore, serverless compute Not available
SQL Database, DTU model (any tier) Not available
New Hyperscale database Not available, because Hyperscale has no SQL license fee
SQL Managed Instance (vCore) Available

The first three rows follow from the rule that in Azure SQL Database, Azure Hybrid Benefit is available only on the provisioned compute tier of the vCore model, not in the DTU model or the serverless compute tier[15]. The Hyperscale row follows from the fact that Hyperscale has no SQL software license fee, so Azure Hybrid Benefit isn't available for new Hyperscale databases[13].

In short: Azure Hybrid Benefit is a vCore discount on provisioned compute. A plan that pairs it with a Premium DTU database or with serverless compute describes a combination that can't be licensed that way.

SQL database in Microsoft Fabric

This section covers when to choose SQL database in Microsoft Fabric and which of its two endpoints each workload should use.

Picture a team building a new order-entry application whose sales data is needed for near-real-time analytics, with no extract pipeline to build: SQL database in Fabric fits because its data is kept up to date in a queryable format in OneLake, where Fabric services such as Spark notebooks and Power BI reports can use it[16]. OneLake is the shared storage that those Fabric services read. It is a transactional database based on Azure SQL Database, and it uses the same SQL Database Engine as Azure SQL Database[16]. The data is automatically replicated into OneLake in near real time and converted to Parquet[16]. Microsoft Fabric is built on a software-as-a-service (SaaS) foundation, and creating a SQL database in Fabric creates items in your Fabric workspace[16].

Microsoft lists use cases that need both kinds of access, among them translytical applications, which need transactional and analytical access to the same data, where automatic replication to OneLake creates a SQL analytics endpoint so analytical queries don't affect transactional performance[16].

Two endpoints, two jobs

Creating a SQL database in Fabric also creates a SQL analytics endpoint that points to the database's Delta table storage[17], as the figure below shows. Delta tables use the open-source Delta Lake table format[16], which extends Parquet data files with a file-based transaction log[18], so a Delta table is one table format whose data sits in Parquet files, the format OneLake calls Delta Parquet[19]. The database endpoint and the SQL analytics endpoint are separate connections:

Database endpoint SQL analytics endpoint
Connection string form <server-unique-identifier>.database.windows.net <server-unique-identifier>.<tenant>.fabric.microsoft.com
Data access Reads and writes Read-only over the replicated Delta tables
Typical caller The application Reports, ad hoc T-SQL, views, and functions

The connection-string forms come from the SQL analytics endpoint documentation[17]. The SQL analytics endpoint runs in read-only mode: T-SQL there can define and query objects such as views and functions but can't modify the data[17]. An application's write connection belongs on the database endpoint. Security is also separate: object-level permissions set with GRANT, REVOKE, and DENY on the SQL analytics endpoint apply only to access through that endpoint[17], so plan permissions for each endpoint.

A new Fabric database or mirroring

Creating a SQL database in Fabric gives you a new, empty operational database. If the operational database already exists in Azure SQL Database and must stay there, use mirroring in Fabric (Fabric mirroring) to continuously replicate the existing database into OneLake[16]. Fabric mirroring brings data from various systems into a single analytics platform[20]; it isn't SQL Server database mirroring, which brings a standby copy of a database online at failover[21]. A new Fabric database would only add a second database beside the first.

In short: choose SQL database in Fabric for a new transactional workload that lives in Fabric, send its writes to the database endpoint, and use Fabric mirroring to bring an existing Azure SQL Database into OneLake.

ApplicationSQL database in Fabricdatabase endpointOneLakeDelta tables (Parquet)SQL analytics endpointread-only T-SQLreads and writesautomatic replicationqueried by
Data path in SQL database in Microsoft Fabric, from application writes to read-only queries on the SQL analytics endpoint.

Choose an automated deployment method

This section covers the tools that create Azure SQL resources repeatably, and how to tell a declarative definition from an imperative script. The Microsoft DP-300 study guide[2] lists 'Choose an automated deployment method' under Plan and deploy Azure SQL solutions, and 'Automate deployment by using Azure Resource Manager (ARM) and Bicep templates' as a separate skill under Automate deployment of database resources.

This section compares three automated deployment approaches, and each one describes something different:

Approach What it describes Choose it when
ARM template (JSON) or Bicep file The desired state of Azure resources Environments must be recreated the same way from source control
Azure CLI or Azure PowerShell script An ordered list of commands The workflow needs branching or a sequence of administrative actions
SqlPackage publish of a DACPAC Database schema objects Tables, views, and procedures must match a SQL project

ARM stands for Azure Resource Manager, the Azure service that deploys resources from these templates. The first two approaches create Azure resources, and the third works inside a database; the next section covers it.

Declarative templates: ARM and Bicep

Bicep is a domain-specific language that uses declarative syntax to deploy Azure resources[22]: you state which resources and properties you want, not the steps to create them. During deployment, the Bicep CLI converts a Bicep file into a Resource Manager JSON template[22], so Bicep and ARM JSON are two syntaxes for the same deployment engine. Bicep files are idempotent: you can deploy the same file many times and get the same resource types in the same state[22]. One template behavior matters when a deployment runs again: resource-group deployments default to Incremental mode, which leaves resources that aren't in the template unchanged, while Complete mode deletes them[23].

The listing below is the resource portion of the Bicep quickstart for a single database[24]. The parent: sqlServer line places the database under the logical server, and Bicep manages the dependency automatically when one resource uses another's symbolic name, so no dependsOn is needed[22].

Bicep excerpt: a logical server and its database

// ... parameters (serverName, sqlDBName, location, administrator login) omitted
resource sqlServer 'Microsoft.Sql/servers@2022-05-01-preview' = {
  name: serverName
  location: location
  properties: {
    administratorLogin: administratorLogin
    administratorLoginPassword: administratorLoginPassword
  }
}

resource sqlDB 'Microsoft.Sql/servers/databases@2022-05-01-preview' = {
  parent: sqlServer          // the database is a child of the logical server
  name: sqlDBName
  location: location
  sku: {
    name: 'Standard'
    tier: 'Standard'
  }
}

The // lines are Bicep comments, which Bicep allows. The complete file and its deploy commands are in the worked example for this section.

Imperative scripts: Azure CLI and Azure PowerShell

Scripts run commands in order, so they fit work that branches or must happen in a set sequence. They also deploy templates: the quickstart deploys the Bicep file with az deployment group create --template-file main.bicep or with New-AzResourceGroupDeployment -TemplateFile ./main.bicep[24]. Where you run a script doesn't change what it is. Azure Cloud Shell is an interactive, authenticated, browser-accessible terminal for managing Azure resources[25]. It is a place to run CLI or PowerShell commands, not a stored definition of resources.

In short: declare Azure resources in Bicep or ARM JSON when the environment must be reproducible, use CLI or PowerShell for ordered operations, and treat the shell you run them in as a runtime, not a definition.

Publish database schema with SqlPackage

This section covers the deployment method for what lives inside a database, which ordinary server and database resource declarations from the previous section don't touch.

A template creates the logical server and an empty database resource. The tables, views, and stored procedures come from a different artifact. A DACPAC (data-tier application package) is a file that contains a database's schema, and SqlPackage is the command-line tool that deploys it. The SqlPackage publish operation incrementally updates the schema of a target database to match the structure of a source database[26], such as a DACPAC file.

SqlPackage command: publish a DACPAC to Azure SQL Database with a managed identity

SqlPackage /Action:Publish /SourceFile:"C:\AdventureWorksLT.dacpac" \
    /TargetConnectionString:"Server=tcp:{yourserver}.database.windows.net,1433;Initial Catalog=AdventureWorksLT;Authentication=Active Directory Managed Identity;Encrypt=True;..."

This command is from the SqlPackage Publish reference[26], with connection-string options cut where the ... marker appears. /SourceFile names the DACPAC. /TargetConnectionString names the database whose schema changes to match it.

Permissions differ by target. Publishing to an existing database requires db_owner, and the publish action can create a new database when the user has create-database permissions[26]. So the database can come from the template or the publish action[26]; Publish never creates the logical server, so infrastructure deployment still runs first. Publishing has separate drop settings: the DropObjectsNotInSource property defaults to False, but DropIndexesNotInSource, DropConstraintsNotInSource, DropDmlTriggersNotInSource, and DropStatisticsNotInSource default to True[26]. Review those settings rather than assuming all target-only objects are preserved.

The order follows from the two layers. Deploy the infrastructure (logical server, networking, service tier) with a template or script first, then publish the schema into the target database. Ordinary server and database resource declarations do not add stored procedures, and a DACPAC never creates a logical server.

In short: templates deploy Azure resources, SqlPackage deploys database schema, and a repeatable pipeline usually runs both, in that order.

Deploy Azure SQL Database: logical server, then database

This section covers creating Azure SQL Database from the command line, where the logical server must come first.

In Azure SQL Database, a server is a logical construct that acts as a central administrative point for a collection of databases[4]. At that level you manage logins, firewall rules, and auditing, among other settings. The logical server must exist before you can create a database, and it is distinct from a SQL Server instance[4]. It isn't a VM and has no instance features. The figure below shows the hierarchy the commands build.

Azure CLI script excerpt: create a logical server, a firewall rule, and a database

# ... variable assignments ($resourceGroup, $server, $database, $login, $password, IP range) and progress messages omitted; --tags and --zone-redundant options removed from the commands below
az group create --name $resourceGroup --location "$location"
# ...
az sql server create --name $server --resource-group $resourceGroup --location "$location" \
    --admin-user $login --admin-password $password
# ...
az sql server firewall-rule create --resource-group $resourceGroup --server $server -n AllowYourIp \
    --start-ip-address $startIp --end-ip-address $endIp
# ...
az sql db create --resource-group $resourceGroup --server $server --name $database \
    --sample-name AdventureWorksLT --edition GeneralPurpose --family Gen5 --capacity 2

The commands are adapted from Microsoft's create-and-configure Azure CLI script[27], with long lines wrapped, variable setup and progress messages omitted, and the --tags $tag and --zone-redundant true options omitted. Read them in order:

  1. az sql server create creates the parent. It doesn't create any user database.
  2. az sql server firewall-rule create is a server-level rule. It opens the server to the client IP range, which clients need before they can connect.
  3. az sql db create names its parent with --server. Here --edition GeneralPurpose --family Gen5 --capacity 2 selects a vCore size: General Purpose, standard-series (Gen5) hardware[13], 2 vCores. For a DTU size, --service-objective names the size instead, for example --service-objective S0 for a Standard S0 database[28].

The PowerShell version of the same script uses New-AzSqlServer, New-AzSqlServerFirewallRule, and New-AzSqlDatabase[29]. A command that creates a different product is the usual wrong turn here. az sql mi create creates a SQL managed instance, which the Azure Hybrid Benefit guide lists alongside New-AzSqlInstance as the Managed Instance commands[15]. It never creates the parent for Azure SQL Database.

In short: create the logical server, allow client access, then create each database under it with a size selection.

Resource groupLogical serverDatabaseDatabaseFirewallrule
Azure SQL Database resource hierarchy: a resource group holds the logical server, which is the parent of databases and firewall rules.

Deploy Azure SQL Managed Instance: subnet and quota

This section covers the two capacity checks a Managed Instance deployment must pass before az sql mi create or New-AzSqlInstance can succeed: subnet addresses and regional vCore quota.

The subnet

The subnet that SQL Managed Instance uses must be dedicated, so only SQL Managed Instance resources can be deployed in it, and it must be delegated to the Microsoft.Sql/managedInstances resource provider[30]. Each SQL managed instance needs a minimum of 32 IP addresses, so the smallest subnet mask is /27[31]. Size the subnet for its whole life, because changing the subnet address range isn't supported while resources exist in the subnet[31].

Microsoft's sizing formula for a subnet with several instances is 5 + (gp * 4) + (bc * 10) + (bc_zr * 2) + (vmg * 8)[31], where:

  • 5 addresses are reserved by Azure.
  • 4 per General Purpose instance: 2 for deployment and 2 for a later scaling operation.
  • 10 per Business Critical instance: 5 for deployment and 5 for scaling.
  • 2 more per zone-redundant Business Critical instance.
  • 8 per VM group, a group of the VMs that host the instances.

The formula gives the planned address requirement including scaling headroom, not the subnet size, and the 32-address minimum still applies: because IP ranges are defined in powers of 2, the subnet needs the next range that holds the total[31], never less than /27.

The scaling allowance exists because compute scaling can temporarily require additional instance addresses, while General Purpose storage scaling needs no additional addresses[31]. Microsoft's first worked example has three General Purpose and two Business Critical instances in one VM group with no zone redundancy: 5 + (3 x 4) + (2 x 10) + 0 + (1 x 8) = 45 addresses, so the subnet needs a range of 64 addresses, a /26[31].

The regional vCore quota

The second check is independent of the network. The vCore unit limit is the maximum number of vCore units per hardware generation that you can deploy across all instances in a single region. One General Purpose vCore uses one vCore unit, and one Business Critical vCore uses four[32]. Default limits depend on the subscription type, for example 320 vCore units for Pay-as-you-go and 960 for Enterprise Agreement[32].

Microsoft's quota example reports a current consumption of 80 vCore units against a limit of 320, which leaves 240 vCore units for standard-series hardware in that subscription and region[32]. Applying the four-unit rule, an 8-vCore Business Critical instance needs 32 of those 240 units, while the same instance in General Purpose needs 8.

When the quota is short, the subnet can't help. You can raise the limit with a quota-increase support request, or create the instance in another Azure region[32].

In short: confirm a dedicated, delegated subnet with room for scaling, and separately confirm enough vCore units for the hardware generation in that region.

Managed Instance collation and time zone at creation

This section covers two Managed Instance settings that you can set only when you create the instance.

A managed instance's collation can be set when the instance is created and can't be updated later[1]. Its time zone can also be set only during creation, the default is UTC, and the time zone of an existing instance can't be changed[5]. The time zone matters for behavior, not just display: GETDATE() and CLR code observe the instance time zone, and SQL Server Agent jobs follow schedules in that time zone[5].

ARM template excerpt: properties of a new managed instance

"properties": {
    "administratorLogin": "[parameters('user')]",
    "administratorLoginPassword": "[parameters('pwd')]",
    "subnetId": "[parameters('subnetId')]",
    "storageSizeInGB": 256,
    "vCores": 8,
    "licenseType": "LicenseIncluded",
    "hardwareFamily": "Gen5",
    "collation": "Serbian_Cyrillic_100_CS_AS",
    "timezoneId": "Central European Standard Time"
}

The excerpt is from the Managed Instance time zone article[5], which shows only the properties block of the resource. collation and timezoneId are the two creation-time keys, and if timezoneId isn't specified, the time zone is set to UTC[5]. licenseType is the Azure Hybrid Benefit switch from the purchasing section above.

If an instance was created with the wrong time zone, restoring the databases into it doesn't fix the clock. The workaround is to create a new managed instance with the right time zone, then back up and restore, or preferably do a cross-instance point-in-time restore[5]. The same rule holds in a failover group: after a failover, the secondary instance keeps its own original time zone[5].

Azure SQL Database works differently. The database collation is set when the database is created, and there's no server collation[1]. Azure SQL Database doesn't support time zone settings and always follows UTC; use AT TIME ZONE to interpret dates in another time zone[5].

In short: copy the source server's collation and time zone into the Managed Instance creation request. Afterward the only way to change either one is a new instance.

Deploy SQL Server on Azure VMs

This section covers the two ways to put SQL Server on an Azure VM and the extension that makes the VM manageable as SQL Server.

You can start from a SQL Server image in Azure Marketplace or install SQL Server yourself on a plain VM. On Windows VMs, either way, the SQL Server IaaS Agent extension connects the instance to Azure's SQL features. The extension unlocks automation benefits such as portal management, license flexibility, automated backup, and automated patching[33]. Azure portal management, which lets you view all SQL Server VMs in one place and turn SQL-specific features on or off, is included with basic registration[33].

Registration differs by path:

A VM with SQL Server installed but not registered is still just a VM to Azure, and the SQL-specific management features stay unavailable until registration.

Registration doesn't make the VM a managed service. On a SQL Server VM you have full control over the operating system and the SQL Server instance configuration, and you decide when to update or upgrade the operating system and database software[3]. A Marketplace image is only the starting build. Check its SQL Server patch level after deployment and keep servicing it, which the patching section below covers.

In short: start from a Marketplace image or register a self-installed instance with the SQL Server IaaS Agent extension, and keep patching it, because the VM remains IaaS.

Install and update SQL Server on Linux

This section covers deploying and servicing SQL Server on a Linux host, whether that host is an Azure VM or a server elsewhere.

On Linux, the package repository decides which SQL Server you get. When you install or upgrade SQL Server, you get the latest version from your configured Microsoft repository[35], so configure the repository for the major version you intend before installing mssql-server.

Ubuntu commands: install the engine, then configure it

sudo apt-get update
sudo apt-get install -y mssql-server
sudo /opt/mssql/bin/mssql-conf setup   # sets the sa password and edition

These commands are from the Ubuntu installation quickstart[36]. Installing the package isn't the end: after the package installation finishes, you run mssql-conf setup to set the sa password and choose the edition[36].

Updates use the platform's package manager:

Platform Update command
RHEL sudo yum update mssql-server
SLES sudo zypper update mssql-server
Ubuntu sudo apt-get update, then sudo apt-get install mssql-server

These commands download the newest package and replace the binaries under /opt/mssql/; they don't affect user or system databases[35]. An update stays within the configured version. A major-version upgrade has one extra step: first change the configured repository to the target version, then run the same update command, which works only if the upgrade path between the two repositories is supported[35].

Linux also changes the feature list. FileTable, FILESTREAM, and CLR assemblies with the EXTERNAL_ACCESS or UNSAFE permission set aren't supported on Linux[10], which is why the first section of this topic sends those workloads to Windows.

In short: on Linux the repository sets the version, mssql-conf setup finishes a new install, and the package manager applies updates without touching the databases.

Plan table partitioning

This section covers deciding whether to partition a table and which column to partition on. The Microsoft DP-300 study guide[2] lists configuring table partitioning as a separate skill; see Configure resources for scale and performance.

Partitioning maps groups of rows into individual partitions. All partitions of one table or index must reside in the same database, and the table is treated as a single logical entity for queries and updates[37]. Partitioning makes one big table easier to manage. It doesn't spread data across databases; that is sharding, in the next section.

What partitioning buys

The benefits include transferring or accessing subsets of data quickly, such as switching out a month into an archive table, and running maintenance on one or more partitions instead of the whole table[37]. Query speed can improve, but only for certain queries. When a query predicate references the partitioning column, the database engine might be able to skip (eliminate) some partitions[37], while queries that can't use partition elimination could take longer as the number of partitions grows[37].

Choosing the partitioning column

Choose the key from the access and retention pattern, not from row count. A sales table that reports by date and ages out old months should be partitioned on its date column, because partition elimination works when the predicate references the partitioning column[37]: date filters then skip unrelated months, and each month can be maintained or archived on its own. Partitioning on an unrelated identifier leaves a date filter with no partitioning column to reference, so it can skip nothing. The rules for the column:

How many partitions

The database engine supports up to 15,000 partitions[37], but more partitions cost resources. Building a nonaligned partitioned index needs memory for all partitions' sort tables at once, and more partitions need more memory[37]. A nonaligned index is one partitioned differently from its table. Creating or rebuilding nonaligned indexes on a table with more than 1,000 partitions might be possible but isn't supported[37]. Lowering the index build's MAXDOP reduces the memory needed, at the cost of a potentially longer build[37].

Where partitions are stored

The platform limits where partitions can be stored. In Azure SQL Database and SQL database in Fabric, all partitions must be on the PRIMARY filegroup, because that is the only filegroup provided[37]. Partition maintenance and switching still work there. Multiple filegroups have two uses: assigning older partitions to slower, cheaper storage tiers, and backing up and restoring each filegroup independently[37]. Storage tiering depends on controlling file placement; independent filegroup backup and restore depend on platform support. On Managed Instance file paths are assigned automatically and the file location can't be specified[1]. Its users can create only full database COPY_ONLY backups[8]. So customer-controlled storage tiering and independent filegroup backup and restore are SQL Server capabilities, including SQL Server on Azure VMs, which keeps 100% SQL Server compatibility[3]; Managed Instance offers neither, and Azure SQL Database has only PRIMARY. If you don't use tiered storage and your backup and restore procedures don't benefit from multiple filegroups, a single filegroup for all partitions is recommended[37].

In short: partition on the column your queries filter and your retention rules cut on, keep the partition count reasonable, and match the filegroup plan to what the platform can store.

Recommend a database sharding solution

This section covers sharding: spreading data across many databases when one database's resources aren't enough. It differs from the partitioning in the previous section, which keeps every partition in one database.

In a sharded design, each database is a shard. A shard map records which database holds the data for each sharding key value, and the slice of data for one key is called a shardlet[38]. The elastic database tools for Azure SQL Database manage these maps.

Choose the tenancy model first

Most sharding plans start from how tenants are stored. Microsoft's SaaS design patterns[39] compare the options:

Most SaaS applications access the data of only one tenant at a time, which is what allows tenant data to be distributed across multiple databases[39]. That makes the tenant identifier a natural sharding key: a transaction that touches one tenant then stays on one shard.

List or range shard map

A list shard map associates individual key values with databases; list mappings are explicit, and different keys can map to the same database[38]. A range shard map maps intervals written as [Low Value, High Value), where the low value is included and the high value is the first value outside the range[38]. Choose a list map when tenants need explicit, individual placement. Choose a range map when contiguous key intervals belong together.

How a request finds its shard

The shard map manager keeps its data in three places: the global shard map (GSM) in a dedicated database, a local shard map (LSM) in every shard, and an application cache[38]. The LSM lets the application check whether a cached mapping is still valid without placing load on the GSM[38]. This lookup is data-dependent routing, traced in the figure below: a request carries a key, the map resolves it, and the connection opens to that one shard. Routing reaches one shard per key and doesn't run a query across shards. Cross-shard reads use a different feature: an elastic query is used to query or compile reports across many shards, and that reporting requires an elastic database shard map[40]. Microsoft notes that elastic query in shard map manager mode reaches end of support on March 31, 2027[38], so check that date before you plan cross-shard reporting on it.

Changing the map doesn't move rows

The shard map methods change only the metadata. A split breaks a range into two parts but leaves both mapped to the same shard, and moving rows needs a separate operation such as the split-merge tool[38]. UpdateMapping and DeleteMapping work only on an offline mapping, and while a mapping is offline, data-dependent requests for its keys return an error[38]. Rebalancing therefore needs a data-movement plan and a routing outage plan, not just a mapping change.

In short: pick the tenancy model from the isolation and restore needs, pick list or range maps from how keys are assigned, and move rows with the split-merge tool, never with mapping calls alone.

Application requestcarries a sharding keyShard map managercache first; GSM refreshShard databaselocal shard map validateslook up keyopen connection
Data-dependent routing with the elastic database tools: a sharding key resolves through the shard map to one shard database.

Hybrid SQL Server with Azure Arc

This section covers the two Azure Arc products for SQL, which share a name but solve different problems.

Onboarding an existing SQL Server

Check the platform first. Supported versions are SQL Server 2014 and later, 64-bit only[6], on operating systems that include Windows Server 2016 and later, Ubuntu 20.04, RHEL 8, and SLES 15[6]. Windows Server versions earlier than 2016 and SQL Server running in containers aren't supported[6].

Onboarding has two layers, traced in the figure below. The Azure Connected Machine agent and the Azure extension for SQL Server connect to Azure using only outbound HTTPS on TCP port 443[6], so no inbound SQL port is opened to Azure. You connect the machine with the Connected Machine agent. Then Microsoft automatically installs the Azure extension for SQL Server on each Arc-enabled server that has SQL Server instances installed, and creates the Arc-enabled SQL Server instance resources in Azure[41]. Because the extension does the discovery, a connected host with a missing instance resource points to the extension layer, not to the database engine or its data.

What you get, and on which OS

Arc features are not all available on every OS. Discovery and registration work on Windows and Linux, but best practices assessment, monitoring with performance dashboards (preview), automated backups to local storage (preview), and automatic updates are Windows-only[6]. Some features also need extra setup: best practices assessment and Microsoft Defender for Cloud require the Azure Monitor Agent (AMA) extension connected to a Log Analytics workspace[6].

Choose between the two assessments by the question being asked:

Automated backups (preview) write to the instance's default backup location, on local storage or network shares[42]. They don't go to Azure-managed backup storage.

Licensing through Arc

Two licensing options are common reasons to connect a server:

When the requirement is a managed data service on your hardware

If the goal is a managed-instance-style service running on infrastructure you control, SQL Managed Instance enabled by Azure Arc is the fit. It needs an Azure Arc data controller created first[7]. You set the update cadence yourself from Microsoft Container Registry updates[7], and scaling depends on the available capacity of your infrastructure[7]. Installing the Connected Machine agent on a host never produces this service.

In short: connect existing servers with SQL Server enabled by Azure Arc to manage, assess, and license them in place, and choose SQL Managed Instance enabled by Azure Arc only when you want a Kubernetes-hosted data service.

Existing SQL Server hostdatacenter, edge, other cloudConnected Machine agentconnects the machineAzure extension for SQL Serverdiscovers SQL instancesSQL Server resources in Azureinventory, assessment, ESUOutbound HTTPS only, TCP 443
Onboarding an existing SQL Server to Azure Arc: machine agent, then SQL extension, then SQL Server resources in Azure.

SQL Data Sync: restricted new groups

This section covers SQL Data Sync, which you may find in existing hybrid estates but shouldn't plan for new work.

SQL Data Sync retires on September 30, 2027, and you can't create new sync groups in Azure subscriptions that didn't use it before[44]. Existing groups keep running until then, so the planning task is to understand them and schedule their replacement.

A sync group uses a hub-and-spoke topology. The hub database must be an Azure SQL Database, and member databases can be Azure SQL Database or SQL Server[44]. Data Sync doesn't support Azure SQL Managed Instance[44]. An on-premises member needs a local sync agent[44]. Sync can run hub to member, member to hub, or both ways[44].

Its consistency model rules it out for migration and disaster recovery. Data Sync tracks changes with insert, update, and delete triggers, so it doesn't guarantee transactional consistency; Microsoft guarantees only that all changes are eventually made, without data loss[44].

The main requirements and limits:

In short: treat SQL Data Sync as selected-table, eventually consistent synchronization between an Azure SQL Database hub and SQL Server or SQL Database members, and plan to replace existing groups before September 30, 2027.

Patch SQL Server on Azure VMs and hybrid hosts

This section covers the patching tools for SQL Server you run yourself, on Azure VMs or on Arc-connected hosts. Azure SQL Database and Managed Instance are patched by Azure, as the first section explains.

Three tools apply, and they differ in scope and in which updates they install:

Tool Where it applies What it installs
Azure Update Manager SQL Server on Azure VMs, Windows or Linux SQL Server cumulative updates, plus SQL Server and OS updates marked Critical or Important
Automated Patching (retiring) SQL Server on Azure Windows VMs Windows and SQL Server updates marked Important or Critical
Arc automatic updates Arc-connected Windows hosts Windows and SQL Server updates marked Important or Critical

Azure Update Manager

Azure Update Manager can install SQL Server cumulative updates as well as SQL Server and OS updates marked Critical or Important, on Windows or Linux SQL Server VMs[45]. It supports on-demand updates, updates at scale across VMs, schedules within maintenance windows, and periodic assessments, which check each VM for updates every 24 hours and report machines out of compliance[45]. An assessment only reports. Installs happen through a one-time update or a schedule, and you enable Microsoft updates on the SQL virtual machines resource so the VM can receive SQL Server updates[45].

Use only one scheduler. Enabling both Automated Patching and Azure Update Manager could lead to scheduling conflicts and changes outside maintenance windows, so disable Automated Patching before using Azure Update Manager[45].

Automated Patching

Automated Patching installs only Windows and SQL Server updates marked Important or Critical; other updates, such as cumulative updates not marked that way, must be installed manually[46]. It is scheduled to retire on September 17, 2027, and Microsoft advises avoiding it in new environments[46].

Arc automatic updates

For SQL Server enabled by Azure Arc, automatic updates work at the level of the host operating system and apply to all installed SQL Server instances, run only during the maintenance window, currently work only on Windows hosts, and apply only updates marked Important or Critical[47]. The maintenance window's start hour is in local time[47]. Automatic updates are available only for servers covered by Software Assurance or the pay-as-you-go license type[47]. Two consequences follow. You can't use this feature to separate the maintenance of two instances on one host, and it isn't a patch policy for Linux SQL Server. Linux hosts use the package commands from the Linux section above.

In short: use Azure Update Manager for cumulative updates on SQL Server VMs[45] and retire Automated Patching[46]. Remember that Arc automatic updates cover a whole Windows host and only Important or Critical updates[47].

Rolling updates for availability groups

This section covers the order in which to update SQL Server instances that host an Always On availability group (AG), where a primary replica's databases are copied to one or more secondary replicas.

The goal of a rolling update is that downtime for the primary replica shrinks to a single manual failover, or two if you fail back to the original primary[48]. The rule behind the order: always update the remote secondary replicas first, then the local secondary replicas, and the primary replica last[48], where a remote secondary sits on the remote site and a local secondary sits on the primary site[48]. The figure below shows Microsoft's simplest sequence:

  1. Remove automatic failover on all synchronous-commit replicas[48], so the AG doesn't fail over by surprise mid-update.
  2. Update all asynchronous-commit secondary replicas.
  3. Update all remote synchronous-commit secondary replicas.
  4. Update all local synchronous-commit secondary replicas.
  5. Manually fail over to a newly updated local synchronous-commit secondary replica.
  6. Update the instance that was the primary.
  7. Configure automatic failover partners as needed.

Steps 2 through 7 follow the rolling upgrade process[48]. Two checks guard step 5. Fail over only to a synchronous-commit secondary, because failing over to an asynchronous-commit secondary risks data loss[48], and verify that the failover target's synchronization state is SYNCHRONIZED first[48]. Updating every replica simultaneously would remove the healthy failover target the process depends on.

Major-version upgrades add constraints. After the first failover to a replica on the newer version, the AG's databases go through an upgrade to the new version, and no replicas are readable until it finishes[48]. An upgraded primary also can't ship logs to a secondary that still runs the older version[48], so failing back to an un-upgraded replica isn't a rollback path. Mixed versions aren't supported outside a rolling upgrade, and the AG shouldn't stay in that state for long[48].

In short: update secondaries first, confirm a synchronized synchronous-commit target, fail over once, then update the old primary.

1. Remove automatic failover2. Update asynchronous-commitsecondary replicas3. Update remote synchronous-commit secondary replicas4. Update local synchronous-commit secondary replicas5. Manual failover to anupdated local secondary6. Update the former primary7. Configure automaticfailover partners
Rolling update order for an Always On availability group: secondaries first, one manual failover, former primary last.

Requirements matched to Azure SQL services and tools

The Microsoft DP-300 study guide[2] lists choices such as 'Choose an automated deployment method' and 'Recommend a database sharding solution' under Plan and deploy Azure SQL solutions. Each requirement below is paired with the Azure SQL service, tool, or setting that meets it, and with the documented limit that rules out the closest alternative.

Azure SQL Database vs Azure SQL Managed Instance vs SQL Server on Azure VMs

Decision pointAzure SQL DatabaseAzure SQL Managed InstanceSQL Server on Azure VMs
Level of controlDatabase level: most database-level capabilitiesInstance level: almost all instance-level and database-level capabilitiesOperating-system level: full engine control plus OS access
SQL Server AgentNo (elastic jobs instead)YesYes
Cross-database three-part-name queriesNo (elastic query instead)Yes, within the instanceYes
CLRNoYes, without file-system accessYes
FILESTREAM and FileTableNoNoYes, on Windows
Instance time zoneNone; always UTCChosen at creation, can't be changedYours to configure, with the OS and instance
Purchasing modelvCore or DTUvCoreVM size and storage you choose
Who installs engine and OS updatesAzureAzureYou (Azure Update Manager can schedule them)
How you create itaz sql server create, then az sql db createaz sql mi create or New-AzSqlInstanceSQL Server Marketplace image, or self-install and register

Decision tree

Existing SQL Serverstays outside Azure?SQL Server enabledby Azure ArcYesNoManaged service onyour Kubernetes?SQL Managed Instanceenabled by Azure ArcYesNoNew transactionalapp in Fabric?SQL databasein FabricYesNoNeeds OS accessor FILESTREAM?SQL Server onAzure VMsYesNoNeeds Agent orthree-part names?Azure SQLManaged InstanceYesNoAzure SQL Database

Sharp facts the exam loves — give these one last read before exam day.

Cheat sheet

Sharp facts the exam loves — scan these before test day.

Instance-level dependencies favor SQL Managed Instance over SQL Database

When an application needs native cross-database three-part-name queries or transactions within one instance, Service Broker, SQL Server Agent, or SQL CLR calculations without operating-system access, use Azure SQL Managed Instance to retain those instance capabilities in a managed service. Azure SQL Database suits independently managed application databases, but does not provide SQL Server Agent, user-defined CLR integration, Service Broker, or native cross-database transactions. For access to a remote server rather than another database within the instance, Managed Instance supports linked servers to supported SQL Server and Azure SQL Database targets; SQL Database has no native linked servers, so evaluate an alternative such as elastic query against the required remote-query behavior. Both services delegate infrastructure patching and built-in availability to Azure; compatibility requirements, not simply database count, determine the choice.

Trap Using an Azure SQL Database elastic pool to preserve SQL Server Agent and native cross-database instance behavior, although pooling shares database resources rather than supplying a SQL Server instance.

4 questions test this
Host-level integration requires SQL Server on Azure virtual machines

When a workload requires operating-system administration, local file-share integration from SQL CLR, or FILESTREAM and FileTable, deploy SQL Server on Azure Virtual Machines; the latter CLR and file-storage requirements need Windows. IaaS preserves SQL Server and host-level control on the selected supported platform, whereas the managed SQL services restrict access to their underlying hosts. That control also leaves the customer responsible for operating-system and SQL Server servicing rather than transferring those duties to a PaaS service.

Trap Choosing SQL Managed Instance solely because it supports CLR, although CLR support does not grant access to the managed service's local file system.

4 questions test this
The vCore model exposes resource and licensing choices that DTUs bundle

When sizing must map to processor capacity, hardware characteristics, and separately selected storage, use the vCore purchasing model. Azure SQL Database also offers the DTU model, which packages CPU, memory, and I/O into Basic, Standard, and Premium performance levels rather than exposing independent hardware choices. Azure Hybrid Benefit and reserved-capacity discounts are vCore options for eligible SQL Database deployments, and SQL Managed Instance uses the vCore model rather than DTUs. Azure Hybrid Benefit reuses qualifying SQL Server licenses with active Software Assurance: select LicenseType BasePrice instead of LicenseIncluded to exclude the Azure SQL license charge, while compute and storage remain billable. For SQL Database, DTU and serverless deployments are ineligible, and newly created Hyperscale databases use pricing without a separate SQL software license fee rather than receiving an additional Hybrid Benefit discount merely because they use vCores.

Trap Selecting a Premium DTU database to apply Azure Hybrid Benefit, although that licensing benefit requires an eligible vCore deployment.

4 questions test this
SQL database in Fabric combines an operational database with automatic analytics integration

When a new transactional application needs a SQL database inside a Fabric workspace and near-real-time analytical access without a separately assembled replication pipeline, use SQL database in Microsoft Fabric. It uses the Azure SQL Database engine and automatically replicates supported operational data into analytics-ready OneLake storage. If an Azure SQL Database must remain an external operational system, use Fabric mirroring to bring its data into OneLake rather than replacing it with a newly created Fabric SQL database.

Trap Creating a Fabric SQL database merely to expose an existing external Azure SQL Database to analytics, although creation provisions a new operational database rather than configuring mirroring for the existing one.

4 questions test this
Fabric's operational SQL endpoint and SQL analytics endpoint serve different workloads

SQL database in Fabric provides a transactional database endpoint for application reads and writes and a separate SQL analytics endpoint over the replicated OneLake data. Send operational inserts and updates to the database endpoint; use the analytics endpoint for read-only T-SQL analysis, views, and functions over the replicated tables. The two endpoints have different connection strings and independently applicable SQL access controls, so an analytics connection is not a substitute for the operational connection.

Trap Pointing an application's write connection at the SQL analytics endpoint because it supports T-SQL, although that endpoint is read-only over replicated data.

4 questions test this
Declarative templates describe resource state while scripts orchestrate operations

When repeatable environments must declare SQL resources and their dependencies as a versioned desired state, use ARM JSON templates or Bicep. Bicep compiles to ARM templates and expresses a database's dependency on its logical server through the parent resource relationship; repeated deployment of unchanged resource definitions is idempotent. Azure CLI and Azure PowerShell are appropriate when the workflow needs imperative branching or sequenced administrative actions, and they can also submit templates through az deployment group create or New-AzResourceGroupDeployment.

Trap Choosing Azure Cloud Shell as the deployment definition, although it is an execution environment rather than a versioned declaration of the required resources.

3 questions test this
DACPAC publishing manages database schema rather than Azure infrastructure

When an existing database needs its tables, views, and procedures reconciled with a SQL project, use SqlPackage /Action:Publish with a DACPAC. Publishing computes the schema changes required to match the package, so the deployment identity needs the documented database permissions, including db_owner for an existing target. Provision the Azure logical server, networking, and service tier separately through infrastructure deployment; a database-schema package is not an ARM resource template.

Trap Deploying an ARM template alone to add application stored procedures, although resource provisioning does not reconcile the database's SQL object definitions.

4 questions test this
Provision a logical server before its Azure SQL databases

An Azure SQL Database logical server is the administrative parent for databases, not a virtual machine or a SQL Managed Instance. Use az sql server create to provision the parent, then az sql db create with --resource-group, --server, --name, and the required performance selection such as --service-objective S0 to create a database under it. The PowerShell equivalents are New-AzSqlServer and New-AzSqlDatabase; creating the server alone does not create the application's user database, and client connectivity still needs its network access path configured.

Trap Using az sql mi create to provision the parent for Azure SQL Database, although that command creates a different managed-instance offering.

4 questions test this
Managed Instance deployment requires both subnet headroom and regional vCore quota

Deploy Azure SQL Managed Instance into its dedicated delegated subnet only after verifying both subnet-address headroom and regional vCore quota; use New-AzSqlInstance or az sql mi create rather than a logical-server database command. The minimum subnet mask is /27, but size for instance count, service tiers, virtual-machine groups, future management operations, and growth because scaling can temporarily double the addresses allocated to the instance being scaled. Separately check the subscription's regional vCore-unit quota for the selected hardware generation across all instances: each General Purpose vCore consumes one unit, whereas each Business Critical vCore consumes four. A regional quota failure can require a quota-increase support request or another eligible region even when subnet addresses are sufficient and the requested instance meets its technical limits.

Trap Selecting a subnet that fits only the current managed-instance addresses, although scaling temporarily allocates additional compute and IP addresses.

4 questions test this
SQL VM registration adds management capabilities without making the engine PaaS

For SQL Server on an Azure Windows VM, use a SQL Server Marketplace image for preconfigured SQL deployment or register a manually installed instance with the SQL Server IaaS Agent extension. The extension exposes SQL-specific portal management, licensing flexibility, and supported backup and patching integration; a generic VM resource alone does not provide those SQL management features. Marketplace images are a deployment baseline rather than an ongoing servicing mechanism, so verify and update the engine's patch level after deployment.

Trap Assuming the Azure VM resource's existence is equivalent to SQL IaaS Agent registration, although SQL-specific management depends on the extension integration.

4 questions test this
Managed Instance collation and time zone are creation-time commitments

Choose Managed Instance's instance collation and time zone during creation because neither setting can be changed on that instance afterward. An ARM template exposes the collation and timezoneId properties, and omitting the time-zone selection uses UTC. The instance time zone governs GETDATE() and SQL Server Agent schedules, so preserve the source clock context unless the dependent application and scheduling logic are deliberately changed. Azure SQL Database instead selects collation at database scope and has no configurable server collation or instance time-zone setting; it follows UTC, with AT TIME ZONE available for query-level conversions.

Trap Restoring a database onto a UTC managed instance to reproduce the source server's local-time Agent schedule, although the destination instance retains its own creation-time time-zone setting.

2 questions test this
A useful partition key follows query predicates and data-lifecycle boundaries

When a large table is queried and aged out by time interval, partition on the relevant date column so interval predicates can eliminate unrelated partitions and maintenance can target one interval. All partitions remain one logical table in one database; queries that do not constrain the partitioning column can still visit every partition and may become slower. Partitioning accepts one partitioning column, and a computed column used for that purpose must be explicitly PERSISTED, so choose the key around actual access and retention patterns rather than row count alone. Although the ceiling is 15,000 partitions per table or index, finer granularity increases index-build memory and maintenance work, especially for nonaligned indexes, so prefer fewer partitions or a lower index-build MAXDOP when resources are constrained. For frequent equijoins, partition both inputs on their join columns with matching data types, partition counts, and boundaries so the optimizer can process paired partitions; partitioning unrelated to the join does not guarantee faster joins.

Trap Partitioning by an unrelated surrogate identifier to accelerate date-filtered reports, although those predicates cannot identify which identifier partitions to skip.

2 questions test this
Partition storage plans must respect the platform's filegroup capabilities

Azure SQL Database places every partition on PRIMARY because it exposes no additional filegroups, but it still supports partition-level maintenance and switching. SQL Server and Azure SQL Managed Instance can map partitions to multiple filegroups; customer-controlled tiered storage placement and independent filegroup backup and restore are SQL Server capabilities, not Managed Instance capabilities. A single filegroup remains a valid simpler design when neither storage tiering nor filegroup recovery is required.

Trap Designing one Azure SQL Database filegroup per month for archival storage, although that service provides only the PRIMARY filegroup.

3 questions test this
List and range shard maps express different key-to-database assignments

When tenants need explicit placement by individual tenant identifier, use a list shard map; multiple individual identifiers can point to the same database, or each can have its own database. Use a range shard map when contiguous intervals of a sharding key belong together, with ranges represented as inclusive low and exclusive high boundaries, [Low, High). Sharding distributes rows across databases to scale beyond one database's resource boundary, unlike table partitioning, which keeps all partitions inside one database.

Trap Choosing a range shard map for arbitrary noncontiguous tenant identifiers that each require explicit placement, although range mappings describe intervals rather than independent key assignments.

3 questions test this
Shard-map metadata changes do not move user rows between databases

When rebalancing existing data, coordinate actual row movement with mapping changes, for example through the elastic database split-merge service; SplitMapping and MergeMappings alone only change range metadata. The shard map manager stores global key-to-database mappings, while local shard maps let data-dependent routing validate cached mappings against the destination. Operations such as UpdateMapping or DeleteMapping require the mapping to be offline, so plan routing availability as well as data movement before relocating a shardlet.

Trap Calling SplitMapping to distribute an overloaded shard's rows to a new database, although the split leaves both resulting ranges mapped to the original shard.

3 questions test this
Tenant requirements determine transaction and management boundaries for shards

Choose a sharding key that keeps each transaction's related data on one shard, such as a tenant identifier for tenant-local operations. Use one database per tenant when independent recovery, isolation, and resource scaling are required; place multiple small tenants in one database when sharing is preferable, with a tenant key in each shared table. Shared tenants compete for that database's resources, so an unusually busy tenant can justify relocation to a dedicated database rather than merely adding another mapping. Data-dependent routing connects a key-specific request to one shard, whereas elastic query supplies cross-database reads; maintaining a shard map alone does not execute a distributed query.

Trap Choosing a shared multitenant shard for a requirement to restore one tenant directly as an independent database, although the native database recovery boundary includes the other tenants sharing that shard.

3 questions test this
Azure Update Manager is the preferred SQL VM patching path for cumulative updates

When SQL Server VMs need centrally assessed and scheduled SQL cumulative updates as well as operating-system updates, use Azure Update Manager and configure the required update sources and maintenance windows. The older SQL VM Automated Patching feature installs only updates classified Important or Critical and is scheduled to retire on September 17, 2027, so it is not the choice for new deployments or general CU automation. Disable Automated Patching before enabling Update Manager, because overlapping patch schedulers can conflict; periodic assessment can check update availability every 24 hours without itself installing updates.

Trap Using SQL VM Automated Patching for every SQL Server cumulative update, although updates outside its Important or Critical classifications require a different servicing path.

4 questions test this
Arc automatic SQL updates operate at Windows-host scope rather than per database

SQL Server enabled by Azure Arc can configure Windows-host automatic updates during a selected local-time maintenance window, affecting all installed SQL Server instances on that host. This feature requires Software Assurance or pay-as-you-go licensing and applies Windows and SQL updates classified Important or Critical; unclassified service packs or CUs require separate installation. Because this particular feature currently works only on Windows, do not treat its schedule as a Linux SQL Server patch policy or as isolation between instances sharing one host.

Trap Enabling Arc automatic updates on one SQL instance to isolate maintenance from another instance on the same host, although the feature configures host-level update services.

5 questions test this
Rolling SQL maintenance updates secondary replicas before the primary

When patching SQL Server instances that host an availability group, update secondary replicas first and verify that a suitable synchronous secondary has caught up before a planned manual failover. After applications have moved to the updated primary, service the former primary; keep replica maintenance windows staggered instead of patching all hosts simultaneously. For a major-version upgrade, failover also upgrades database internals, so mixed versions are a temporary upgrade state and failback to an older-version replica is not a rollback mechanism.

Trap Scheduling every availability replica for the same update window to keep versions identical throughout, although that removes the healthy failover target needed for rolling maintenance.

4 questions test this
Arc management of an existing SQL Server differs from running an Arc data service

When existing SQL Server installations must remain in a datacenter, edge site, or another cloud while gaining Azure inventory, assessment, performance monitoring, and optional security services, enable those servers through Azure Arc. For improvements to an existing instance's performance and security configuration, use best practices assessment to compare its configuration with Microsoft's recommendations rather than migration readiness assessment, which evaluates Azure target suitability. For ongoing performance monitoring of an eligible existing instance without migrating its databases, use Arc performance dashboards in the Azure portal rather than either assessment. Eligible Windows installations can also enable automated native backups (preview) to the instance's default local or network backup location for associated point-in-time restore, rather than obtaining PaaS-managed cloud backup storage. When the requirement is to run a managed-instance-style data service on infrastructure you control, SQL Managed Instance enabled by Azure Arc runs on Kubernetes with an Azure Arc data controller; that service consumes your cluster's capacity and uses a customer-controlled update cadence, whereas connecting an existing host to Arc does not convert it into that Kubernetes service or migrate its databases to Azure.

Trap Installing only the Azure Connected Machine agent to obtain a Kubernetes-hosted managed instance, although host onboarding does not deploy the Arc data controller or data service.

5 questions test this
Arc SQL onboarding connects the machine and discovers its SQL instances separately

Before onboarding an existing non-Azure host, verify a supported 64-bit SQL Server 2014-or-later and operating-system pairing; SQL Server in containers and Windows Server versions earlier than 2016 are unsupported. Connect the host through the Azure Connected Machine agent and ensure the Azure extension for SQL Server is installed; Arc normally deploys the SQL extension automatically when it discovers SQL Server, creating Azure resources for the discovered instances, and an already connected host can also have the extension added explicitly. Troubleshoot a missing SQL instance resource at the SQL extension and discovery layer instead of reinstalling the database engine or moving its data. Both agents communicate with Azure through outbound HTTPS on TCP 443, so Arc onboarding does not require exposing the inbound SQL client port to Azure. Discovery supports Windows and Linux, but best practices assessment and Arc performance monitoring are Windows-only; check feature-specific licensing and install Azure Monitor Agent (AMA) connected to Log Analytics when the selected service, such as best practices assessment, requires it.

Trap Installing the Azure VM SQL IaaS Agent extension on an on-premises host for Arc discovery, although Arc uses the Connected Machine agent and Azure extension for SQL Server.

4 questions test this
Arc subscription choice follows usage-licensing or extended-security needs

Use SQL Server enabled by Azure Arc pay-as-you-go licensing when a short-lived or variable-capacity SQL Server workload should be billed through Azure instead of requiring an upfront license purchase. The PAYG license type uses an hourly software meter, making workload duration and licensed core capacity relevant to cost. For an eligible SQL Server installation that must remain in place after its support lifecycle ends, an Extended Security Updates subscription provides security-update coverage for up to three years rather than migrating its databases. ESU enrollment requires an eligible licensing arrangement such as active Software Assurance, an active SQL Server subscription, or pay-as-you-go software licensing; inventory registration alone is not ESU enrollment.

Trap Assuming Arc inventory registration alone supplies Extended Security Updates, although eligible installations must also be subscribed to the security-update service.

5 questions test this
SQL Server on Linux is installed and serviced through a compatible package repository

For a Linux SQL Server host, select a supported distribution and version pairing and configure the Microsoft repository for the intended SQL Server major version before installing or updating mssql-server. Complete a fresh engine installation with sudo /opt/mssql/bin/mssql-conf setup rather than treating package installation alone as completed SQL Server configuration. On Ubuntu, refresh package metadata with sudo apt-get update and install the engine update with sudo apt-get install mssql-server; RHEL uses sudo yum update mssql-server, while supported SLES deployments use sudo zypper update mssql-server. An update replaces engine binaries without replacing the databases, whereas a major-version upgrade first requires selecting the target repository and validating that the upgrade path is supported.

4 questions test this
Existing SQL Data Sync groups provide selected-data synchronization rather than transactional continuity

Treat an existing SQL Data Sync group as selected-table, eventually consistent synchronization, not as a transactionally consistent migration or disaster-recovery mechanism; choose a supported purpose-built alternative for those requirements. Its insert, update, and delete triggers track changes for synchronization between an Azure SQL Database hub and SQL Server or Azure SQL Database members, with one-way or bidirectional flow; Azure SQL Managed Instance is unsupported. Connections require SQL authentication, synchronized tables require stable primary keys, and an on-premises member needs a local sync agent; schema changes are not automatically replicated and TRUNCATE is not tracked. The group's conflict policy determines which competing row change prevails: Hub wins overwrites the member's change with the hub's, whereas Member wins overwrites the hub's change with the member's. SQL Data Sync retires on September 30, 2027, and subscriptions that have not previously used it cannot create new sync groups, so plan replacement of existing groups instead of recommending unrestricted new deployment.

Trap Using SQL Data Sync to synchronize a Managed Instance member with an Azure SQL Database hub, although the service's supported member offerings exclude Managed Instance.

References

  1. https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison
  2. https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
  3. https://learn.microsoft.com/en-us/azure/azure-sql/azure-sql-iaas-vs-paas-what-is-overview
  4. https://learn.microsoft.com/en-us/azure/azure-sql/database/logical-servers
  5. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview
  6. https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/overview?view=sql-server-ver17
  7. https://learn.microsoft.com/en-us/azure/azure-arc/data/overview
  8. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
  9. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/distributed-transaction-coordinator-dtc
  10. https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2025
  11. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
  12. https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu
  13. https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-sql-database-vcore
  14. https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/
  15. https://learn.microsoft.com/en-us/azure/azure-sql/azure-hybrid-benefit
  16. https://learn.microsoft.com/en-us/fabric/database/sql/overview
  17. https://learn.microsoft.com/en-us/fabric/database/sql/sql-analytics-endpoint
  18. https://learn.microsoft.com/en-us/azure/databricks/delta/
  19. https://learn.microsoft.com/en-us/fabric/onelake/onelake-overview
  20. https://learn.microsoft.com/en-us/fabric/mirroring/overview
  21. https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server
  22. https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/overview
  23. https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/deployment-modes
  24. https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-create-bicep-quickstart
  25. https://learn.microsoft.com/en-us/azure/cloud-shell/overview
  26. https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish
  27. https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/create-and-configure-database-cli
  28. https://learn.microsoft.com/en-us/cli/azure/sql/db
  29. https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/create-and-configure-database-powershell
  30. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview
  31. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/vnet-subnet-determine-size
  32. https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits
  33. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management
  34. https://learn.microsoft.com/en-us/sql/sql-server/usage-and-diagnostic-data-configuration-for-sql-server
  35. https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup
  36. https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu
  37. https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
  38. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-shard-map-management
  39. https://learn.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns
  40. https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview
  41. https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/manage-autodeploy
  42. https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/backup-local
  43. https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/extended-security-updates
  44. https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database
  45. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/azure-update-manager-sql-vm
  46. https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-patching
  47. https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/update
  48. https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances