📖 About this Domain
This domain focuses on automating routine DBA tasks using built-in SQL Server and Azure SQL features. You will learn to configure SQL Agent jobs, manage database mail for notifications, and set up alerts for specific database events. It also covers automating database deployments to streamline development and operational workflows.
🎓 What You Will Learn
- Learn to create, configure, and manage SQL Agent jobs, job steps, schedules, and proxies for task automation.
- Understand how to configure Database Mail profiles and accounts to send email notifications from SQL Server.
- Discover how to create and manage alerts and notifications based on performance conditions, errors, and events.
- Explore methods for automating the deployment of database resources using DACPACs and BACPACs.
🛠️ Skills You Will Build
- You will build the skill to implement automated maintenance plans and custom T-SQL scripts using SQL Agent jobs.
- You will gain proficiency in setting up a robust notification system using Database Mail and SQL Server Agent alerts.
- You will develop the ability to automate database schema and data deployments across different environments.
- You will be able to manage job outcomes, troubleshoot failed jobs, and configure operator notifications for proactive monitoring.
💡 Top Tips to Prepare
- Practice creating multi-step SQL Agent jobs with different subsystems like T-SQL, PowerShell, and SSIS in a lab environment.
- Master the configuration of Database Mail, including profiles, accounts, and troubleshooting common send failures using sysmail views.
- Focus on creating alerts for specific error numbers, severity levels, and performance counter thresholds using sp_add_alert.
- Get hands-on experience with the SqlPackage.exe command-line utility to deploy DACPAC and BACPAC files for automated deployments.
📖 About this Domain
This domain covers the deployment and configuration of relational data platform resources on Azure. It focuses on provisioning SQL Server on Azure VMs, Azure SQL Database, and Azure SQL Managed Instance. The core tasks involve resource deployment and initial configuration to meet technical requirements.
🎓 What You Will Learn
- You will learn to deploy SQL Server on Azure Virtual Machines (IaaS) and configure underlying storage for optimal performance.
- You will learn to configure Azure SQL Database and Managed Instance resources, including compute tiers like vCore, DTU, and Serverless.
- You will learn to implement secure access to the database using authentication methods, firewalls, and private endpoints.
- You will learn to configure high availability and disaster recovery (HADR) strategies, including failover groups and geo-replication.
🛠️ Skills You Will Build
- You will build skills to deploy SQL Server on Azure VMs using marketplace images and ARM templates.
- You will gain proficiency in configuring Azure SQL Database and Managed Instance service tiers for specific workload requirements.
- You will develop competence in securing database endpoints with firewalls, VNet rules, and Azure AD authentication.
- You will be able to implement and manage HADR solutions like Always On availability groups and geo-backups.
💡 Top Tips to Prepare
- Master the key differentiators between IaaS (SQL on VM) and PaaS (Azure SQL DB, SQL MI) deployment models.
- Practice resource deployment using the Azure portal, Azure CLI, and PowerShell to understand automation options.
- Understand database connectivity configuration, including VNet integration, private link, and service endpoints.
- Review the capabilities and limitations of different service tiers like General Purpose, Business Critical, and Hyperscale.
📖 About this Domain
This domain covers configuring a secure environment for Azure data platforms. It focuses on authentication, authorization, data encryption, and implementing compliance controls. You will learn to safeguard data from unauthorized access and meet security requirements.
🎓 What You Will Learn
- You will learn to configure authentication using Azure AD principals and SQL logins, including contained database users.
- You will learn to configure database authorization by assigning permissions to securables using fixed and user-defined database roles.
- You will learn to implement security for data at rest using Transparent Data Encryption (TDE) and Always Encrypted.
- You will learn to implement compliance controls like database auditing, data classification, and dynamic data masking.
🛠️ Skills You Will Build
- You will build skills to create and manage security principals like logins, users, and roles using T-SQL and the Azure Portal.
- You will build skills to implement a least privilege security model by granting, denying, and revoking permissions on database objects.
- You will build skills to configure and manage encryption for data at rest and in transit, including BYOK scenarios for TDE.
- You will build skills to configure server and database audits and apply data protection features for sensitive data columns.
💡 Top Tips to Prepare
- Practice creating Azure AD and SQL authenticated users and assigning them to database roles using T-SQL.
- Understand the difference between server-level roles and database-level roles and their permission scopes.
- Master the configuration of Transparent Data Encryption (TDE) and the process for using customer-managed keys from Azure Key Vault.
- Review the implementation steps for SQL Auditing, Data Classification, and Dynamic Data Masking in the Azure Portal.
📖 About this Domain
This domain covers operational database administration for Azure SQL. You will monitor performance, troubleshoot bottlenecks, and optimize database objects and server configuration. It emphasizes proactive and reactive performance tuning using built-in and Azure-specific tools.
🎓 What You Will Learn
- Monitor database activity and performance using Dynamic Management Views (DMVs), Extended Events, and Query Store.
- Implement performance-related maintenance tasks such as index rebuilds, reorganizations, and statistics updates.
- Troubleshoot performance issues by analyzing query execution plans and identifying blocking scenarios.
- Configure server and database-scoped settings, including MAXDOP, cost threshold for parallelism, and memory configurations.
🛠️ Skills You Will Build
- Analyzing query performance and identifying regressions using Query Store and execution plans.
- Implementing robust indexing strategies, including columnstore, filtered, and covering indexes.
- Configuring and interpreting telemetry from Azure Monitor, Log Analytics, and performance dashboards.
- Managing resource contention and workload isolation using Resource Governor.
💡 Top Tips to Prepare
- Gain practical experience with DMVs like sys.dm_exec_requests and sys.dm_os_wait_stats to diagnose live issues.
- Understand the differences between actual and estimated execution plans and their key operators.
- Practice forcing and unforcing plans within the Query Store to mitigate performance regressions.
- Review Azure SQL's automatic tuning options for index management and query plan correction.
📖 About this Domain
This domain covers the critical processes for ensuring business continuity for your data platform solutions. You will focus on recommending and implementing high availability and disaster recovery strategies using Azure SQL and SQL Server features.
🎓 What You Will Learn
- You will learn to recommend a database offering based on Service Level Agreements (SLAs), Recovery Point Objective (RPO), and Recovery Time Objective (RTO).
- You will learn to plan and implement a backup and restore strategy, including full, differential, and transaction log backups.
- You will learn to configure Always On availability groups and failover cluster instances for SQL Server.
- You will learn to configure geo-replication and auto-failover groups for Azure SQL Database and Azure SQL Managed Instance.
🛠️ Skills You Will Build
- You will build skills to perform database backups and restores using T-SQL and other management tools.
- You will build skills to configure and manage Always On availability groups, including listeners and read-only routing.
- You will build skills to implement and test failover procedures for both on-premises and Azure SQL environments.
- You will build skills to evaluate and recommend HA/DR solutions like log shipping, failover clustering, and Azure Site Recovery.
💡 Top Tips to Prepare
- Master the concepts of RPO and RTO and how they map to specific HA/DR technologies like availability groups and geo-replication.
- Practice backup and restore scenarios using T-SQL commands, including point-in-time recovery (PITR).
- Understand the key differences and use cases for Azure SQL Database service tiers, geo-replication, and auto-failover groups.
- Gain hands-on experience configuring a Windows Server Failover Cluster (WSFC) and an Always On availability group in a lab environment.
