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]:
- Database level: Azure SQL Database. A platform-as-a-service (PaaS) database that supports most on-premises database-level capabilities, with built-in backups, patching, and recovery[3]. Its databases sit under a logical server, but Azure SQL Database doesn't expose any instance-level access or features[4]. There is no server collation[1], and the service always follows UTC[5].
- Instance level: Azure SQL Managed Instance. A PaaS instance that supports almost all on-premises instance-level and database-level capabilities[3]. These include SQL Server Agent, Service Broker, cross-database transactions within the instance, and CLR without file-system access[1].
- Operating-system level: SQL Server on Azure Virtual Machines (VMs). Infrastructure as a service (IaaS) that keeps 100% SQL Server compatibility and 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.
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.
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.
Complete Bicep file and deploy commands
This is the Bicep file from the Microsoft quickstart for creating a single database with Bicep[24], shown complete. Save it as main.bicep.
Bicep file: logical server and one database (complete)
@description('The name of the SQL logical server.')
param serverName string = uniqueString('sql', resourceGroup().id)
@description('The name of the SQL Database.')
param sqlDBName string = 'SampleDB'
@description('Location for all resources.')
param location string = resourceGroup().location
@description('The administrator username of the SQL logical server.')
param administratorLogin string
@description('The administrator password of the SQL logical server.')
@secure()
param administratorLoginPassword string
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
name: sqlDBName
location: location
sku: {
name: 'Standard'
tier: 'Standard'
}
}
Deploy commands (Azure CLI, then Azure PowerShell)
az group create --name exampleRG --location eastus
az deployment group create --resource-group exampleRG --template-file main.bicep --parameters administratorLogin=<admin-login>
New-AzResourceGroup -Name exampleRG -Location eastus
New-AzResourceGroupDeployment -ResourceGroupName exampleRG -TemplateFile ./main.bicep -administratorLogin "<admin-login>"
Both commands prompt for administratorLoginPassword because the file marks it @secure() and gives it no default.
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:
az sql server createcreates the parent. It doesn't create any user database.az sql server firewall-rule createis a server-level rule. It opens the server to the client IP range, which clients need before they can connect.az sql db createnames its parent with--server. Here--edition GeneralPurpose --family Gen5 --capacity 2selects a vCore size: General Purpose, standard-series (Gen5) hardware[13], 2 vCores. For a DTU size,--service-objectivenames the size instead, for example--service-objective S0for 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.
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:
- Marketplace image. A SQL Server VM deployed from an Azure Marketplace image through the Azure portal is registered with the extension automatically[33].
- Self-installed SQL Server or a custom VHD. You must register the VM with the extension to unlock its features. Self-installed VMs with SQL Server 2016 or later register automatically when the CEIP service detects them, and you should register any that it doesn't detect[33], for one VM, in bulk, or automatically for a whole subscription. The CEIP service is SQL Server's usage and diagnostic data collection (CEIP)[34].
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:
- Only one column can be the partitioning column. A computed column used in a partition function must be explicitly created as
PERSISTED[37], which is how you combine several columns into one key. - The optimizer can process equijoins between partitioned tables faster when the partitioning columns are the same as the join columns[37]. Partitioning on a column the join doesn't use gives no such guarantee.
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:
- Database per tenant. Single-tenant databases give strong tenant isolation, and recovering one tenant to an earlier point in time restores only that tenant's database, with no impact on other tenants[39].
- Multitenant databases. The schema needs tenant identifier columns, tenant isolation is sacrificed, and per-tenant operations such as a point-in-time restore of one tenant are more complex[39].
- Sharded multitenant databases. Tenants are spread across many multitenant databases, and the tenant identifier is the sharding key and the leading element of every sharded table's primary key[39].
- Hybrid. Shared and dedicated databases mix. A tenant can be moved from a shared database to its own single-tenant database[39], for example when one busy tenant crowds the others.
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.
Hybrid SQL Server with Azure Arc
This section covers the two Azure Arc products for SQL, which share a name but solve different problems.
- SQL Server enabled by Azure Arc connects an existing SQL Server installation, wherever it runs, to Azure for management. The databases stay where they are.
- SQL Managed Instance enabled by Azure Arc is an Azure data service that Azure Arc runs on-premises, at the edge, or in public clouds, using Kubernetes and infrastructure of your choice[7].
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:
- Best practices assessment compares an instance's configuration with best practices established by Microsoft Support and shows specific ways to improve performance and security[6].
- Migration assessment analyzes cloud readiness and recommends an Azure SQL target and size[6].
- For ongoing monitoring without an assessment, performance dashboards monitor SQL Server instances from the Azure portal[6].
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:
- Pay-as-you-go. You can pay for SQL Server through Azure instead of buying licenses, which suits instances with variable compute demand or ones you need only for a short period[6].
- Extended Security Updates (ESUs). After SQL Server reaches the end of its support lifecycle, an ESU subscription keeps servers protected for up to three years[43]. You subscribe after connecting the instance to Azure Arc[43], and the ESU subscription is offered with Software Assurance, a SQL Server subscription, or pay-as-you-go, not with a license-only server[6]. Connecting a server for inventory doesn't enroll it.
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.
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:
- Connections require SQL authentication; Microsoft Entra ID authentication isn't supported[44].
- Each table must have a primary key, and you shouldn't change a primary key value in place[44].
- Schema changes aren't replicated automatically[44].
- The conflict policy is set per group: with Hub wins, the hub's changes overwrite the member's; with Member wins, the member's changes overwrite the hub's[44].
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:
- Remove automatic failover on all synchronous-commit replicas[48], so the AG doesn't fail over by surprise mid-update.
- Update all asynchronous-commit secondary replicas.
- Update all remote synchronous-commit secondary replicas.
- Update all local synchronous-commit secondary replicas.
- Manually fail over to a newly updated local synchronous-commit secondary replica.
- Update the instance that was the primary.
- 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.
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.
- "The app uses SQL Server Agent and cross-database queries; minimize administration." Choose Azure SQL Managed Instance. An elastic pool of Azure SQL databases is the tempting wrong answer: pooled databases only share resources[11], and SQL Database has no SQL Server Agent or native cross-database three-part-name queries[1].
- "CLR code must read a local file share" or "the app uses FILESTREAM." Choose SQL Server on a Windows Azure VM. Managed Instance runs CLR without file-system access in the
CREATE ASSEMBLYstatement[1], and Linux doesn't support FILESTREAM or FileTable[10]. - "Apply existing SQL Server licenses to Azure SQL Database." Choose a vCore database on provisioned compute with
LicenseTypeset toBasePrice. A Premium DTU database or a serverless database can't use Azure Hybrid Benefit[15]. - "An application writes to the Fabric SQL database through the SQL analytics endpoint and fails." That endpoint is read-only[17]. Point writes at the database endpoint.
- "Make an existing Azure SQL Database available in OneLake." Configure Fabric mirroring[16], not a new Fabric SQL database.
- "Add stored procedures to an existing database in a pipeline." Publish a DACPAC with SqlPackage, which needs
db_owneron an existing target[26]. Ordinary server and database resource declarations in an ARM template don't create SQL schema objects. - "Which artifact defines the environment?" A Bicep file or ARM template. Azure Cloud Shell is a terminal[25], not a definition.
- "Create the parent for an Azure SQL database."
az sql server create, thenaz sql db create --server.az sql mi createcreates a different product. - "The Managed Instance deployment failed although the subnet has free addresses." Check the regional vCore quota per hardware generation. Business Critical vCores count four units each[32].
- "Agent jobs must keep running on the source server's local time." Create the managed instance with that
timezoneId. The time zone can't be changed later[5], and restoring databases into a UTC instance doesn't change its clock. - "Rebalance an overloaded shard." Use the split-merge tool.
SplitMappingchanges only the metadata[38]. - "Range or list shard map for arbitrary tenant IDs that each need explicit placement?" List. Range maps describe contiguous intervals[38].
- "Restore one tenant without affecting others." Database per tenant. A shared database makes per-tenant restore more complex[39].
- "Run a managed instance on our own Kubernetes cluster." SQL Managed Instance enabled by Azure Arc, which needs an Arc data controller[7]. Installing only the Connected Machine agent onboards a host; it doesn't deploy a data service.
- "Keep getting security updates for an out-of-support SQL Server without migrating." Subscribe to Extended Security Updates through Azure Arc[43]. Inventory registration alone isn't enrollment.
- "Schedule SQL Server cumulative updates on Azure VMs." Azure Update Manager. Automated Patching installs only Important or Critical updates[46].
- "Patch both AG replicas simultaneously so their versions stay identical." Simultaneous patching is wrong: update secondaries first, then fail over[48], keeping a healthy failover target.
- "Sync a Managed Instance with an Azure SQL Database hub using SQL Data Sync." Not possible. Data Sync doesn't support Managed Instance[44], and the service is retiring.
Azure SQL Database vs Azure SQL Managed Instance vs SQL Server on Azure VMs
| Decision point | Azure SQL Database | Azure SQL Managed Instance | SQL Server on Azure VMs |
|---|---|---|---|
| Level of control | Database level: most database-level capabilities | Instance level: almost all instance-level and database-level capabilities | Operating-system level: full engine control plus OS access |
| SQL Server Agent | No (elastic jobs instead) | Yes | Yes |
| Cross-database three-part-name queries | No (elastic query instead) | Yes, within the instance | Yes |
| CLR | No | Yes, without file-system access | Yes |
| FILESTREAM and FileTable | No | No | Yes, on Windows |
| Instance time zone | None; always UTC | Chosen at creation, can't be changed | Yours to configure, with the OS and instance |
| Purchasing model | vCore or DTU | vCore | VM size and storage you choose |
| Who installs engine and OS updates | Azure | Azure | You (Azure Update Manager can schedule them) |
| How you create it | az sql server create, then az sql db create | az sql mi create or New-AzSqlInstance | SQL Server Marketplace image, or self-install and register |
Decision tree
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
- You are migrating an on-premises application that uses Service Broker to exchange messages between two databases on the same instance and SQL Server Agent jobs for nightly processing. You need a platf
- You are choosing between Azure SQL Database and Azure SQL Managed Instance for a migrated application. The application needs Always Encrypted columns, ledger tables, Query Store on secondary replicas,
- You are migrating a database that contains a SAFE CLR assembly to Azure SQL Managed Instance. The on-premises deployment script runs CREATE ASSEMBLY with a path to a DLL on a local drive. You need to
- You have two databases named SalesDb and RefDb in Azure SQL Database. A report uses three-part names to join SalesDb tables to RefDb tables. Company policy requires the databases to remain in Azure SQ
- 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
- You migrate a SQL Server instance to SQL Server on Azure Virtual Machines because it needs operating system access for a third-party monitoring agent. Management asks who must make sure SQL Server cum
- You plan to migrate a SQL Server instance to SQL Server on Azure Virtual Machines instead of Azure SQL Managed Instance. The operations team asks which capability they will gain by choosing the virtua
- You are comparing Azure SQL Managed Instance and SQL Server on Azure Virtual Machines for a document application. It needs SQL Server Agent jobs, cross-database transactions, a SAFE CLR assembly deplo
- You are migrating a SQL Server 2019 database that has an EXTERNAL_ACCESS CLR assembly. The assembly reads CSV files from a Windows file share every hour. You need to move the database to Azure without
- 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
- You manage an Azure SQL Database named db1 that uses the General Purpose service tier with provisioned compute. You verify that your organization has SQL Server licenses with active Software Assurance
- You plan to create a new Azure SQL Database named db2 in the Hyperscale service tier with 16 vCores. Finance wants to apply existing SQL Server licenses through Azure Hybrid Benefit to lower the cost
- Your company plans to buy one-year reserved capacity for Azure SQL Database. The databases are db1 (Standard S3), db2 (Premium P2), db3 (Basic), and db4 (General Purpose, 8 vCores, provisioned). You n
- You have an Azure SQL Database named db1 in the Premium P4 service objective. Your company has SQL Server Enterprise licenses with active Software Assurance and wants to use Azure Hybrid Benefit for d
- 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
- You are planning a new order-tracking application for a team whose reports, notebooks, and Power BI models already live in one Microsoft Fabric workspace. The application needs a transactional databas
- You mirror an Azure SQL Database named SalesDb into Microsoft Fabric. A developer wants a Fabric notebook to correct product prices so that the change also appears in the e-commerce application that r
- You have an existing Azure SQL Database named SalesDb that serves an e-commerce application. SalesDb must stay in Azure SQL Database. Analysts need SalesDb data in OneLake near real time for Power BI
- You have a SQL database in Fabric named AppDb that an order-entry application updates throughout the day. A data engineer is building Spark notebooks in the same Fabric workspace that must read AppDb'
- 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
- You have a SQL database in Fabric named AppDb. A line-of-business application must run INSERT and UPDATE statements against AppDb tables, and Power BI reports must query the same data. You need to con
- A report developer must connect SQL Server Management Studio to the SQL analytics endpoint of a SQL database in Fabric named AppDb to build views for analysts. The developer reused a connection string
- You connect to the SQL analytics endpoint of a SQL database in Fabric named AppDb. Analysts need a reusable object on that endpoint that joins two replicated tables and exposes only selected columns.
- You have a SQL database in Fabric named AppDb that serves an order-entry application. During business hours, analysts run large read-only T-SQL reports against AppDb's database endpoint, and order tra
- 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
- A Bicep file declares a logical server named SQL1, an elastic pool named Pool1, and 10 databases created by a for loop. Each database references Pool1 and SQL1, and no explicit dependsOn entries are u
- A colleague proposes storing the team's SQL infrastructure definition as a set of commands typed into Azure Cloud Shell, because Cloud Shell includes Azure CLI and Azure PowerShell. You need a definit
- Your release pipeline can run only Azure PowerShell tasks. You need the pipeline to deploy a logical server named SQL1 and a database named db1 from a versioned definition that declares their desired
- 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
- You publish a DACPAC to an existing database named db1 by using SqlPackage with default properties. The new schema changes a column from nvarchar(200) to nvarchar(50), and the column contains data. Th
- Your pipeline publishes a DACPAC with SqlPackage to a logical server named SQL1. The target database named db2 doesn't exist yet. The pipeline's login is a member of the dbmanager role in master on SQ
- Your pipeline publishes a DACPAC to an existing Azure SQL Database named db1 by using SqlPackage with a Microsoft Entra service principal. The principal is a member of db_ddladmin in db1, and the publ
- Developers add three stored procedures and a new column to a SQL project for an existing Azure SQL Database named db1. Your release pipeline already deploys SQL1 and db1 by using a Bicep file. You nee
- 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
- You run az sql server create to deploy SQL1 in a resource group named RG1 for a new application. A teammate plans to sign in to SQL1's operating system, install SQL Server cumulative updates, and then
- You run az sql server create to deploy a logical server named SQL1 in a resource group named RG1. A report must list the databases on SQL1 before the next step of the script creates any user database.
- You create a logical server named SQL1 and a database named db1 by using an Azure CLI script. A developer on the corporate network can't connect to db1 by using SQL Server Management Studio, and the e
- You have a logical server named SQL1 in a resource group named RG1. A teammate's script runs az sql mi create to add a database named db1 at the Standard S0 service objective, and the command asks for
- 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
- You run az sql mi create to deploy a SQL Managed Instance into a subnet named Subnet1 that already hosts two Azure virtual machines used by the application tier. The deployment fails validation. You n
- A deployment of a new SQL Managed Instance in East US fails because the subscription's regional vCore quota is exhausted. The project can't wait for a support request to be processed, and the business
- You plan to deploy one General Purpose SQL Managed Instance now and add more instances and scaling operations later. The network team wants to allocate the smallest subnet that SQL Managed Instance al
- Your subscription has a regional quota of 64 vCore units for SQL Managed Instance on standard-series hardware. You already run a General Purpose instance with 16 vCores in that region. You need to kno
- 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
- You installed SQL Server 2022 yourself on an Azure Windows VM named VM1 that was created from a custom image. You need VM1 to appear as a SQL virtual machine resource so that you can use portal manage
- A team created an Azure VM named VM5 from a plain Windows Server image and installed SQL Server 2022 Standard themselves. The CEIP service hasn't detected the instance, so VM5 isn't registered with th
- Your subscription contains 60 existing Azure VMs on which teams self-installed SQL Server, and new teams keep adding similar VMs. You need all current and future VMs that have SQL Server installed to
- You register a self-installed SQL Server Enterprise instance on an Azure VM named VM2 with the SQL IaaS Agent extension. Your company has licenses with active Software Assurance and wants to use them
- 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
- You migrated an application to an Azure SQL Managed Instance named SQLMI1 that was created with default settings. SQL Server Agent jobs scheduled for 01:00 now start at 02:00 or 03:00 Central European
- You plan to migrate an on-premises SQL Server instance that uses the Latin1_General_100_CI_AS_SC server collation to Azure SQL Managed Instance. Application queries join user tables to temporary table
- 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
- You have a 3-billion-row table named Sales in Azure SQL Database. Most queries filter on a range of OrderDate values, and each month the oldest month of data must be removed quickly. SalesOrderID is a
- You plan to partition a table named Shipments in SQL Server on Azure Virtual Machines by a combination of Region and ShipYear. Because only one column can be the partitioning column, you add a compute
- 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
- You are choosing a platform for a 20-TB partitioned table named Claims. Partitions older than three years must sit on cheaper, slower disks that you choose, and each year's filegroup must be backed up
- You migrate a partitioned table to Azure SQL Managed Instance and keep one filegroup per year. The script runs ALTER DATABASE db1 ADD FILE (NAME = 'f2024', FILENAME = 'E:\Data\f2024.ndf') TO FILEGROUP
- You are partitioning a 5-TB Sales table by month in SQL Server on Azure Virtual Machines. All data files sit on the same storage pool, and the database is always backed up and restored as a whole. A c
- 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
- A multitenant database named db1 in Azure SQL Database has reached the largest compute and storage size you can buy for its tier, and the growth plan requires spreading tenants across many databases.
- You use a range shard map for TenantId. The range [100,200) already maps to a database named DBC. You need tenants 400 through 599 to be stored in DBC as well, while tenants 200 through 399 remain in
- You manage a range shard map for TenantId in Azure SQL Database. The map has two mappings: [0,1000) points to a shard named DB1, and [1000,2000) points to a shard named DB2. A new tenant is assigned T
- 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
- A range shard map stores tenants [0,1000) in a shard named DB1, which is overloaded. You created an empty shard named DB2 and want tenants 500 through 999 to live in DB2, with the shard map updated to
- You must change the range [200,300) in a range shard map so that it points to a different shard, and you will coordinate the data move yourself. The UpdateMapping call is rejected. You need to make th
- A range shard map maps [0,1000) to a shard named DB1. An administrator calls SplitMapping at 500 so that [500,1000) can later be served by a new shard named DB2, and then checks DB2. You need to expla
- 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
- You are designing a sharded multitenant SaaS database in Azure SQL Database. Every transaction reads and writes rows for a single tenant, and the application must route each connection to one shard by
- Your SaaS application stores tenant data in Azure SQL Database. Contracts require the operations team to return any single tenant to an earlier point in time by using only the built-in point-in-time r
- Your sharded multitenant SaaS application stores 200 small tenants per shard in Azure SQL Database. One tenant's growing workload is slowing down the other tenants on its shard. You need to isolate th
- 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
- You have 30 SQL Server VMs in Azure that are managed by using Azure Update Manager. A change board must approve every update before installation. You need a daily report of the missing SQL Server and
- You enable Azure Update Manager for a SQL Server on Azure Virtual Machines instance named SQLVM1. The assessment results for SQLVM1 list Windows operating system updates but no SQL Server updates. You
- You manage 12 SQL Server 2022 instances that run on Azure virtual machines. You need to centrally automate the installation of SQL Server cumulative updates and Windows updates during scheduled mainte
- You have an Always On availability group that has replicas on two Azure virtual machines named SQLVM1 and SQLVM2. You move both VMs to Azure Update Manager. You need to ensure that monthly updates nev
- 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
- You have an Azure Arc-enabled SQL Server instance named SQL1 that uses the pay-as-you-go license type and has automatic updates enabled. After the latest maintenance window, a Critical security update
- You have SQL Server 2022 on an on-premises Ubuntu server named LNX1 and SQL Server 2022 on an on-premises Windows server named WIN1. Both servers are connected to Azure Arc. You need to keep both inst
- A Windows server named SQL1 is located in a datacenter in Chicago and is connected to Azure Arc. The Azure Arc resource for SQL1 is in the West Europe region. You configure automatic updates for SQL1
- You have an on-premises SQL Server 2019 instance on a Windows server named SQL1 that is connected to Azure Arc. SQL1 uses the license only license type. You need to configure automatic updates for SQL
- An on-premises Windows server named HOST1 is connected to Azure Arc. HOST1 runs two SQL Server instances named INST1 and INST2. You enable automatic updates in the SQL Server configuration of HOST1 to
- 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
- You have an availability group named AG1 that is used only for disaster recovery. It has a primary replica SQL1 and an asynchronous-commit secondary SQL2 at a remote site. You upgrade SQL2 to a newer
- You have an availability group named AG1 with two synchronous-commit replicas. You upgraded the secondary, SQLVM2, to SQL Server 2025 and manually failed AG1 over to it, and SQLVM1 still runs SQL Serv
- You have an availability group named AG1 with a primary replica SQLVM1, a local synchronous-commit secondary SQLVM2, and an asynchronous-commit secondary SQLVM3 at a disaster recovery site. You need t
- You have an availability group named AG1 with a primary replica SQL1 and a readable synchronous-commit secondary SQL2 that hosts reporting queries. You upgrade SQL2 to a newer SQL Server version. You
- 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
- You have an on-premises SQL Server 2022 instance named SQL1 on Windows Server 2022. Regulations require SQL1 and its databases to stay in your datacenter. You need to manage SQL1 from the Azure portal
- You configure automated backups for an Azure Arc-enabled SQL Server instance named SQL1 by using the default schedule. The storage team asks where the backup files will be written so that they can siz
- Your company runs a validated Kubernetes cluster in its own datacenter. You need to deploy SQL Managed Instance enabled by Azure Arc on the cluster so that the company controls the upgrade cadence of
- You have an Azure Arc-enabled SQL Server 2019 instance on Windows named SQL1 that uses the pay-as-you-go license type. You need SQL1 databases to be backed up on a policy with a 14-day retention so th
- You have 40 on-premises SQL Server 2019 instances on Windows that are connected to Azure Arc and use the pay-as-you-go license type. You need to evaluate each instance's configuration, index managemen
- 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
- You have Azure Arc-enabled SQL Server instances on Windows that use the Software Assurance (Paid) license type. You plan to enable best practices assessment for the instances. You need to provide the
- You onboard an on-premises Windows server named SRV1 to Azure Arc, and the Azure Arc server resource shows a Connected status. SRV1 hosts a SQL Server 2022 instance, but no SQL Server enabled by Azure
- You plan to connect four SQL Server instances to Azure Arc. SQL1 is SQL Server 2012 on Windows Server 2016. SQL2 is SQL Server 2019 on an Azure virtual machine. SQL3 is SQL Server 2016 on Windows Serv
- You plan to connect on-premises SQL Server instances to Azure Arc. The security team allows no inbound connections from the internet to the datacenter. You need to configure the firewall so that the C
- 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
- You have an out-of-support SQL Server 2014 instance on Windows that is connected to Azure Arc with the license only type. The license has no Software Assurance and no SQL Server subscription. You need
- Your company runs an out-of-support SQL Server version on premises and connects the servers to Azure Arc to subscribe to Extended Security Updates while it plans an upgrade. Management asks for the ma
- Your organization connected 30 out-of-support SQL Server 2014 instances to Azure Arc last month for inventory and uses the pay-as-you-go license type. A manager believes the instances now receive Exte
- Your QA team runs 20 on-premises SQL Server Enterprise VMs that are powered on for only about 10 days each quarter. The VMs are connected to Azure Arc. Finance wants to pay for SQL Server software onl
- You run SQL Server 2022 on an on-premises Ubuntu server named LNX2 as a standby instance that receives log backups for disaster recovery. LNX2 is connected to Azure Arc with the pay-as-you-go license
- 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
- You run SQL Server 2022 on an Azure VM named LNX1 that uses Ubuntu 22.04 with the Microsoft CU repository configured. You need to update SQL Server to the latest cumulative update for the same major v
- A new Ubuntu 22.04 VM named LNX7 has the mssql-server package for SQL Server 2022 installed from the Microsoft repository, but the instance has never been configured. You need to choose the Developer
- You have SQL Server 2022 on an Ubuntu 22.04 server named LNX4 and need to upgrade it to SQL Server 2025, which supports Ubuntu 22.04. Running sudo apt-get update and sudo apt-get install mssql-server
- You plan to deploy SQL Server 2025 on Linux virtual machines in Azure. The infrastructure team offers four approved images: SLES 15, Ubuntu 24.04, Ubuntu 20.04 LTS, and RHEL 8 Server. You need to choo
- 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
- https://learn.microsoft.com/en-us/azure/azure-sql/database/features-comparison
- https://learn.microsoft.com/en-us/credentials/certifications/resources/study-guides/dp-300
- https://learn.microsoft.com/en-us/azure/azure-sql/azure-sql-iaas-vs-paas-what-is-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/logical-servers
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/timezones-overview
- https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/overview?view=sql-server-ver17
- https://learn.microsoft.com/en-us/azure/azure-arc/data/overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/distributed-transaction-coordinator-dtc
- https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-editions-and-components-2025
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-pool-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-dtu
- https://learn.microsoft.com/en-us/azure/azure-sql/database/service-tiers-sql-database-vcore
- https://azure.microsoft.com/en-us/pricing/details/azure-sql-database/single/
- https://learn.microsoft.com/en-us/azure/azure-sql/azure-hybrid-benefit
- https://learn.microsoft.com/en-us/fabric/database/sql/overview
- https://learn.microsoft.com/en-us/fabric/database/sql/sql-analytics-endpoint
- https://learn.microsoft.com/en-us/azure/databricks/delta/
- https://learn.microsoft.com/en-us/fabric/onelake/onelake-overview
- https://learn.microsoft.com/en-us/fabric/mirroring/overview
- https://learn.microsoft.com/en-us/sql/database-engine/database-mirroring/database-mirroring-sql-server
- https://learn.microsoft.com/en-us/azure/azure-resource-manager/bicep/overview
- https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/deployment-modes
- https://learn.microsoft.com/en-us/azure/azure-sql/database/single-database-create-bicep-quickstart
- https://learn.microsoft.com/en-us/azure/cloud-shell/overview
- https://learn.microsoft.com/en-us/sql/tools/sqlpackage/sqlpackage-publish
- https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/create-and-configure-database-cli
- https://learn.microsoft.com/en-us/cli/azure/sql/db
- https://learn.microsoft.com/en-us/azure/azure-sql/database/scripts/create-and-configure-database-powershell
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/connectivity-architecture-overview
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/vnet-subnet-determine-size
- https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/resource-limits
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/sql-server-iaas-agent-extension-automate-management
- https://learn.microsoft.com/en-us/sql/sql-server/usage-and-diagnostic-data-configuration-for-sql-server
- https://learn.microsoft.com/en-us/sql/linux/sql-server-linux-setup
- https://learn.microsoft.com/en-us/sql/linux/quickstart-install-connect-ubuntu
- https://learn.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-scale-shard-map-management
- https://learn.microsoft.com/en-us/azure/azure-sql/database/saas-tenancy-app-design-patterns
- https://learn.microsoft.com/en-us/azure/azure-sql/database/elastic-query-overview
- https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/manage-autodeploy
- https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/backup-local
- https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/extended-security-updates
- https://learn.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-data-sql-server-sql-database
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/azure-update-manager-sql-vm
- https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/automated-patching
- https://learn.microsoft.com/en-us/sql/sql-server/azure-arc/update
- https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/upgrading-always-on-availability-group-replica-instances