DP-300 prep: Azure Database Administrator Associate (DBA)

Learn how to start a free Microsoft Azure account, including 12 months of services, a $200 credit, and always-free options like SQL Database 250 gigabytes and 750 hours of VM.

Create a standalone Azure SQL database with sample data by configuring a new server and a basic service tier, then deploy to a resource group.

Install and connect to Azure SQL Database using SSMS, explore tables, views, and stored procedures, and run a quick query like select star from saleslt.address.

Review the six principal clauses of the select statement: select, from, where, group by, having, and order by, and see how they shape filtering, grouping, and ordering data.

Master inner joins by combining sales order detail and header on their sales order id, alias tables, and select only unique columns to retrieve due date, order date, and quantities.

Explore execution plans, including estimated execution plan, actual execution plan, and live query statistics, with show plan options and parallelism illustrated in this session.

Explains execution plans for joining tables, detailing scan vs seek, and the three join types—nested loops, merge, and hash—plus how indexes influence plan choice and batch mode adaptive join.

Identify problem areas in execution plans by optimizing index use, avoiding scans with non-sargable predicates, and using index seeks with indexes; apply with recompile or option recompile to improve performance.

Identify and implement index changes for queries in Azure, while assessing index design for performance, explaining clustered and non-clustered indexes, sargable predicates, filtered indexes, fill factor, and column store considerations.

Explore dynamic management views (DMVs) in SQL Server to gather query performance data, including dm_exec_cached_plans, dm_exec_sql_text, and dm_exec_query_plan_stats, and learn to identify long-running queries, high CPU, and caching behavior.

Identify key dynamic management views (DMVs) for gathering query and performance information in SQL Server and Azure SQL Database. Learn to recognize the dm_exec_cached_plans, dm_exec_query_stats, sys.dm_exec_requests, sys.dm_exec_connections, and resource_stats DMVs.

Identify missing indexes with sys.dm_db_missing_index_details and apply targeted non-clustered index recommendations, including key and included columns, to improve sargable query performance.

Explore how hints drive query performance by selecting join types like merge join, hash join, and loop join, and manage plan stability with optimize for unknown and fixed plans.

Explore deploying database offerings on Azure by using manual setup and the portal, including single databases, elastic pools, managed instances, and SQL virtual machines, with licensing and networking considerations.

Explore exporting and editing ARM templates in JSON, then deploy templates to automate Azure resources; use PowerShell or CLI in Cloud Shell with DACPAC and Azure pipelines.yml for database deployments.

Compare Azure SQL database, SQL managed instance, and SQL virtual machine to understand PaaS vs IaaS, cost, and management tradeoffs. Assess compatibility, backups, patching, and deployment impacts for cloud migrations.

Compare scalability options across Azure database offerings, covering size limits, vertical and horizontal scaling, elastic pools, and service tiers; and examine security features like auditing, encryption, and authentication.

Explore high availability and disaster recovery options across Azure database offerings, detailing availability guarantees, local, zone, and geo redundancy, automated backups, point-in-time restores, and failover capabilities.

Learn to automate Azure deployments with ARM templates by editing json templates, using quickstart options, and understanding resource types like Microsoft.Sql/servers and Microsoft.Sql/servers/databases.

Automate deployment with Bicep, a JSON shorthand, by installing Visual Studio Code and the Bicep extension, then creating a main.bicep file with parameters.

Automate Azure SQL deployment with Bicep in Visual Studio Code, using parameters and defaults that translate to ARM templates. Compare Bicep and ARM, and explore deployment via CLI and portal.

Automate deployment with PowerShell using Cloud Shell to create a resource group, then a SQL server, and finally a database with basic edition and configurable options like ElasticPoolName and SkuName.

Automate deployment with the Azure CLI using Cloud Shell to create resource groups, SQL servers, SQL databases, managed instances, and virtual machines.

Explore configuring Azure SQL Database for scale and performance, covering single databases, elastic pools, hyperscale, serverless and provisioned compute, plus geo-redundant backups and paired regions.

Compare vCore-based tiers: general purpose, business critical, and hyperscale, balancing cost and latency. Use business critical for low latency and resiliency, or hyperscale for up to 100 TB.

Understand the DTU-based purchasing model for Azure SQL databases, from basic to premium, with DTUs as bundled compute, memory, and I/O, plus tempdb limits and vcore considerations.

Compare provisioned and serverless compute in general purpose Azure SQL databases, including billing by the second and pause options, plus using elastic pools to share resources across multiple databases.

Explore Azure SQL database configuration options, including firewall rules, public and private endpoints, ip-based access, connection policies, TLS 1.2, Azure Defender for SQL trial, backups, and collation.

Calculate resource requirements by comparing vcore and dtu models, assess storage, average and peak dtu per database, and decide on elastic pool usage for cost and scalability.

Explore horizontal and vertical partitioning strategies, including sharding, range/lookup/hash methods, and partition functions, plus backups, archiving, and high availability in Azure SQL Database.

Explore data compression options for tables and indexes in Azure SQL, including row and page compression, prefix and dictionary techniques, and the impact on space, performance, and supported data types.

Set up SQL data sync across Azure SQL databases and on-prem or VM databases using a hub and member model, with a sync metadata database and hub-or-member conflict resolution.

Evaluate migration requirements and choose online or offline strategies for Azure SQL databases and SQL Server using tools like Azure Migrate, DMA, DMS, SSMA, and DEA.

Explore Azure Migrate and its discovery and assessment tools, then migrate on-premises servers—VMware, Hyper-V, and physical—and public cloud VMs to Azure.

Learn how SQL Server Migration Assistant (SSMA) automates migrating databases from Access, DB2, MySQL, Oracle, and SAP ASE to SQL Server, including Azure SQL Database and Azure SQL Managed Instance.

Explore the Azure Database Migration Service (DMS) for online and offline migrations to Azure data platforms, with premium vCores and DMA-assisted project setup for SQL Server, PostgreSQL, MySQL, and MongoDB.

Learn how to migrate between Azure SQL services, including from Azure Virtual Machine and Azure SQL Database, using export data with SSIS.

Master migrating Azure SQL databases using data tier applications (DAC) and bacpac exports to capture schema and data; import to a database and explore SQL package, PowerShell, and AZ CLI.

Validate post migration by confirming data parity and functionality with cross-database queries, and assess performance, compatibility level impact, and feature differences between source and target databases.

Configure azure active directory authentication, contrast authN and authZ, and review azure ad options like fido keys, microsoft authenticator, text messages, and cloud-only or federated authentication.

Create SQL server users from Azure Active Directory identities by configuring an Azure Active Directory admin, assigning DB owner roles, and using external provider authentication, while understanding authentication versus authorization.

Configure security principals in Azure SQL Database by assigning users to roles such as DB owner, data reader, and DDL admin, and explore RBAC and master database roles avoiding over-privilege.

Explore how to grant, revoke, and deny permissions to a security principal, such as Susan, on specific objects, and understand how deny overrides role-based grants.

Learn how to grant, revoke, and deny individual permissions in Azure SQL, including securables, principals, rights like select, insert, update, delete, execute, and with grant option.

Explore how the deprecated all permission misleads security by outlining its true scope across tables, views, procedures, and functions, and how deny overrides grant.

Package grants with custom database roles by creating roles, granting select on objects, and altering roles to add members and revoke where needed, ensuring access adapts to new tables.

Apply the principle of least privilege to all securables by using roles and fixed database roles, granting only what users need, and routing access through views or stored procedures.

Explore transparent data encryption (TDE) for data at rest in Azure SQL, using a symmetric DEK with service-managed or BYOK keys, configured at server or database level.

Implement always encrypted to protect sensitive columns in Azure SQL databases, selecting deterministic versus randomized encryption and configuring key vaults for column master keys.

Learn to enable Always Encrypted in SSMS, configure Azure Key Vault access policies, and manage keys and parameterization, while understanding role separation between security administrator and DBA.

Compare Always Encrypted and TDE, noting Always Encrypted encrypts at column level and decrypts on the client, while TDE encrypts the entire database and decrypts on the server.

Explore how Always Encrypted with virtualization-based security enclaves enables secure plain-text computations inside the enclave, supporting pattern matching and stronger isolation without revealing data.

Explore dynamic data masking in Azure SQL Database, configuring column-level masking to show select digits or patterns, exclude administrators, and grant unmask to authorized users via portal or PowerShell.

Configure server and database level firewall rules for Azure SQL, deny public access, allow Azure services, and implement rules via T-SQL, PowerShell CLI, and REST API.

Configure transport layer security to encrypt data between a SQL server and a client. Set the minimum tls version to 1.2 in azure sql via portal, powershell, or azure cli.

Apply a data classification strategy in Azure database by assigning information types and sensitivity levels to columns via data discovery in the portal and ssms for gdpr compliance.

Enable server level auditing for all databases to retain a trail of actions, report on a dashboard, and analyze suspicious activity, storing audit data in blob storage or log analytics.

Learn how data change tracking works in Azure SQL databases, how it differs from change data capture, and how to enable it per database and table to track changed rows.

Enable change data capture (CDC) on Azure SQL databases by enabling the database and table with captured columns. Monitor changes via LSN; ensure minimum S3 tier.

Perform a vulnerability assessment in Azure Defender for SQL, configure periodic scans and storage, view findings, apply baselines, and export results for ongoing security monitoring.