CertifyDeckCargando contenido…
The official Microsoft Certified: Azure Database Administrator Associate syllabus, organized by domain. Check off each point as you review it.
Inicia sesión para guardar tu progreso entre sesiones.
Plan and deploy Azure SQL solutions
Azure SQL Database is PaaS for cloud-born applications, with elastic pools for multitenant SaaS; Azure SQL Managed Instance offers near-complete SQL Server compatibility (SQL Server Agent, CLR, Service Broker, Database Mail) for migrations with few changes; SQL Server on Azure VMs gives full OS and engine control, needed for features such as FILESTREAM.
Azure SQL resources are deployed repeatably with Bicep or ARM templates (az deployment group create or New-AzResourceGroupDeployment) and with commands such as az sql db create. Incremental mode is the default and reapplies all properties; the what-if operation previews changes without deploying, and @secure() keeps passwords out of deployment history.
Bicep: Create a single database ↗Deployment modes - Azure Resource Manager ↗
SQL Server enabled by Azure Arc brings Azure management to instances in data centers, edge sites and other clouds: an inventory you can query with Azure Resource Graph, best practices and migration assessments, Microsoft Defender for Cloud, Microsoft Entra ID authentication, pay-as-you-go billing and Extended Security Updates. It connects with outbound HTTPS traffic.
SQL database in Microsoft Fabric is a transactional database built on the Azure SQL Database engine that automatically mirrors its data into OneLake and exposes a read-only SQL analytics endpoint. It supports only Microsoft Entra ID and does not support CDC, TDE with customer keys or Always Encrypted; it fits AI apps, operational data stores, reverse ETL and translytical apps.
SQL database Overview - Microsoft Fabric ↗Limitations for SQL database - Microsoft Fabric ↗
Partitioning splits a table or index horizontally: the partition function defines boundaries (RANGE LEFT or RIGHT) and the scheme maps partitions to filegroups. It eases per-partition maintenance, partition switching and partition elimination in queries. In Azure SQL Database everything sits on PRIMARY, and a unique clustered index must include the partitioning column.
Partitioned Tables and Indexes ↗Create partitioned tables and indexes ↗
Sharding spreads identically structured data across many Azure SQL Database databases. The Elastic Database client library manages shard maps (list or range) and key-based routing; the split-merge tool moves data between shards, and elastic query and elastic jobs operate across the shard set.
Scaling out - Azure SQL Database ↗Scale out a Database - Azure SQL Database ↗
Azure SQL Database, SQL Managed Instance and SQL Server on VMs are deployed with the portal, PowerShell, the CLI (az sql db, mi, elastic-pool and vm) or Bicep. SQL Managed Instance needs a dedicated, delegated subnet with a network security group and route table, and its collation and time zone can't change after creation. Self-installed SQL Server is registered with the SQL IaaS Agent extension.
Modifiable Configuration Reference - Azure SQL Managed Instance ↗Register with SQL IaaS Agent Extension (Windows) ↗
The Managed Instance link uses distributed availability groups to replicate in near real time between SQL Server and Azure SQL Managed Instance: disaster recovery, offloading reads to Azure and online migration with minimal downtime. It needs a VPN or ExpressRoute, a database master key in master and the Always On feature enabled on SQL Server.
Managed Instance link overview ↗Prepare Environment for Link ↗
For SQL Server on Azure VMs, Azure Update Manager installs Cumulative Updates, with periodic assessments and at-scale schedules; Automated Patching, which relies on the SQL IaaS Agent extension, installs only Important or Critical updates and shouldn't run alongside Update Manager. For Arc-enabled SQL Server, automatic updates use a maintenance window on Windows hosts.
Azure Update Manager - SQL Server on Azure VMs ↗Configure Automatic Updates - SQL Server enabled by Azure Arc ↗
Configure resources for scale and performance
A single database is scaled with T-SQL, the portal, Set-AzSqlDatabase or az sql db update; it stays online, but connections drop at the end, so applications need retry logic. Business Critical, Premium and Hyperscale offer read scale-out through ApplicationIntent=ReadOnly, and elastic pools share resources among databases with unpredictable spikes.
Scale Single Database Resources - Azure SQL Database ↗Read Queries on Replicas ↗
SQL Managed Instance scales vCores, storage and service tier after creation. In General Purpose, operations reattach remote storage; in Business Critical almost all of them seed data, which slows down under heavy load. Next-gen General Purpose supports flexible memory, instance pools share one VM, and tempdb accepts extra data files to reduce contention.
Management Operations Overview - Azure SQL Managed Instance ↗vCore purchasing model - Azure SQL Managed Instance ↗
On SQL Server on Azure Virtual Machines, data disks use ReadOnly host caching and log disks use None; tempdb goes on the local D: SSD and data disks are formatted with a 64-KB allocation unit. A storage pool's column count is fixed at creation, so adding disks only adds capacity. On M-series VMs, Write Accelerator improves transaction log write latency.
Checklist: Best practices for SQL Server on Azure VMs ↗Storage: Performance best practices for SQL Server on Azure VMs ↗
A partition function defines the boundaries and whether each boundary value belongs to the left (RANGE LEFT, the default) or right (RANGE RIGHT) partition; a partition scheme maps partitions to filegroups. SPLIT RANGE and MERGE RANGE add or remove boundaries, SWITCH moves a whole partition between tables, and in Azure SQL Database every partition is on PRIMARY.
Partitioned tables and indexes ↗ALTER PARTITION FUNCTION (Transact-SQL) ↗
Row and page compression shrink rowstore tables and indexes at the cost of extra CPU; they are set per table, index or partition, and nonclustered indexes don't inherit the table's setting. sp_estimate_data_compression_savings estimates savings by sampling into tempdb. Columnstore indexes also support archival compression, which is slower to query.
Data compression ↗sys.sp_estimate_data_compression_savings (Transact-SQL) ↗
Plan and implement a migration strategy
Before migrating, assess the number and size of databases, the acceptable downtime and the features in use. SQL Server enabled by Azure Arc automatically produces a readiness assessment with target and sizing recommendations. An Azure VM fits when you need operating system access or FileStream; Azure SQL Database doesn't support SQL Server Agent jobs (use elastic jobs instead).
Assess migration readiness - SQL Server enabled by Azure Arc ↗Migration overview: SQL Server to Azure SQL Database ↗
An online migration keeps the source active and replicates changes until cutover; an offline migration requires downtime. To Azure SQL Managed Instance, the Managed Instance link is the lowest-downtime online option and allows reading the target, and Log Replay Service in continuous mode also minimizes downtime. To Azure SQL Database, DMS is offline and transactional replication provides the online path.
Migration overview: SQL Server to Azure SQL Managed Instance ↗What is Azure Database Migration Service? ↗
In an online migration the source stays active while changes replicate, and cutover happens at the end: with Log Replay Service in continuous mode you upload the tail-log backup and run Complete-AzSqlInstanceDatabaseLogReplay; with the Managed Instance link you stop the workload, check the lag and perform a planned failover. To Azure SQL Database you use transactional replication with a push subscription.
Migrate databases from SQL Server by using Log Replay Service - Azure SQL Managed Instance ↗Migrate with the link - Azure SQL Managed Instance ↗
An offline migration stops the application during the copy. To Azure SQL Managed Instance you natively restore a .bak from Azure Blob Storage with a SAS credential, or use Log Replay Service in autocomplete mode, the only method that restores differential backups. To Azure SQL Database, a BACPAC imported with SqlPackage runs faster if you scale up the target and disable automatic statistics.
Migration guide: SQL Server to Azure SQL Managed Instance ↗Overview of Log Replay Service with Azure SQL Managed Instance ↗
To SQL Server on Azure VMs you can use backup and restore, detach and attach from a URL, log shipping, or convert the machine to a VHD to move the whole instance. Azure Database Migration Service needs a self-hosted integration runtime when backups are on an on-premises share. SQL Server enabled by Azure Arc migrates to Managed Instance from the portal.
Migration guide: SQL Server to SQL Server on Azure Virtual Machines ↗Migration guide: SQL Server to Azure SQL Managed Instance ↗
Between Azure SQL services, CREATE DATABASE ... AS COPY OF creates a transactionally consistent copy of an Azure SQL Database on the target logical server, and a database can be converted to Hyperscale with a manual cutover (MANUAL_CUTOVER and PERFORM_CUTOVER). To move from Azure SQL Managed Instance to Azure SQL Database you use transactional replication or a BACPAC exported with SqlPackage.
Copy a transactionally consistent copy of a database in Azure SQL Database ↗Convert an existing database to Hyperscale ↗
Azure SQL Managed Instance database copy and move use Always On availability group technology to create the database on another instance in the same region and replicate changes until the user completes the operation. With copy the source stays online; with move it's dropped without data loss. PITR backups don't move, and the destination starts a new backup chain.
Common migration failures have documented causes: Log Replay Service fails to start if the SAS token has more than Read and List permissions or if folders are nested; a TDE-protected database needs its certificate migrated first; and users orphaned by SID mismatches are remapped with ALTER USER ... WITH LOGIN. Restores on Managed Instance are asynchronous and tracked with sys.dm_operation_status.
Migrate databases from SQL Server by using Log Replay Service - Azure SQL Managed Instance ↗Troubleshoot orphaned users (SQL Server) ↗