Plan and Implement Data Platform Resources (15-20%) Deploy resources by using manual methods
- Deploy database offerings on selected platforms https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-get-started?tabs=azure-portal&?WT.mc_id=AZ-MVP-5003451
- configure customized deployment templates https://docs.microsoft.com/en-us/azure/templates/microsoft.sql/servers?tabs=json&?WT.mc_id=AZ-MVP-5003451
- apply patches and updates for hybrid and IaaS deployment https://docs.microsoft.com/en-us/azure/sql-database/sql-database-single-database-get-started?tabs=azure-portal&WT.mc_id=AZ-MVP-5003451
Recommend an appropriate database offering based on specific requirements
- evaluate requirements for the deployment https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml&WT.mc_id=AZ-MVP-5003451
- evaluate the functional benefits/impact of possible database offerings https://docs.microsoft.com/en-us/azure/sql-database/sql-database-paas-vs-sql-server-iaas?WT.mc_id=AZ-MVP-5003451
- evaluate the scalability of the possible database offering https://techcommunity.microsoft.com/t5/azure-sql-database/azure-sql-database-8211-scalability/ba-p/386201?WT.mc_id=AZ-MVP-5003451
- evaluate the HA/DR of the possible database offering https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr?WT.mc_id=AZ-MVP-5003451
- evaluate the security aspects of the possible database offering https://docs.microsoft.com/en-us/azure/sql-database/sql-database-security-overview?WT.mc_id=AZ-MVP-5003451
Configure resources for scale and performance
configure Azure SQL database/elastic pools for scale and performance https://docs.microsoft.com/en-us/azure/sql-database/sql-database-elastic-scale-introduction
configure Azure SQL managed instances for scale and performance https://docs.microsoft.com/en-us/azure/azure-sql/database/scale-resources?WT.mc_id=AZ-MVP-5003451
configure SQL Server in Azure VMs for scale and performance https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-performance?WT.mc_id=AZ-MVP-5003451
calculate resource requirements https://docs.microsoft.com/en-us/sharepoint/administration/storage-and-sql-server-capacity-planning-and-configuration?WT.mc_id=AZ-MVP-5003451
evaluate database partitioning techniques, such as database sharding https://docs.microsoft.com/en-us/azure/architecture/patterns/sharding?WT.mc_id=AZ-MVP-5003451
set up SQL Data Sync https://docs.microsoft.com/en-us/azure/azure-sql/database/sql-data-sync-sql-server-configure?WT.mc_id=AZ-MVP-5003451
Evaluate a strategy for moving to Azure
- evaluate requirements for the migration https://docs.microsoft.com/en-us/azure/dms/pre-reqs?WT.mc_id=AZ-MVP-5003451
- evaluate offline or online migration strategies https://azure.microsoft.com/en-gb/services/database-migration/?WT.mc_id=AZ-MVP-5003451
- evaluate requirements for the upgrade https://docs.microsoft.com/en-us/sql/database-engine/install-windows/supported-version-and-edition-upgrades-version-15?view=sql-server-ver15#pre-upgrade-checklist&WT.mc_id=AZ-MVP-5003451
- evaluate offline or online upgrade strategies https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade?WT.mc_id=AZ-MVP-5003451
Implement a migration or upgrade strategy for moving to Azure
- implement an online migration strategy https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-azure-sql-online?WT.mc_id=AZ-MVP-5003451
- implement an offline migration strategy https://docs.microsoft.com/en-us/azure/dms/tutorial-sql-server-to-azure-sql?WT.mc_id=AZ-MVP-5003451
- implement an online upgrade strategy https://docs.microsoft.com/en-us/azure/sql-database/sql-database-manage-application-rolling-upgrade?WT.mc_id=AZ-MVP-5003451
- implement an offline upgrade strategy https://docs.microsoft.com/en-us/sql/database-engine/install-windows/upgrade-to-a-different-edition-of-sql-server-setup?view=sql-server-ver15?WT.mc_id=AZ-MVP-5003451
Implement a Secure Environment (15-20%)
Configure database authentication by using platform and database tools
- configure Azure AD authentication https://docs.microsoft.com/en-us/azure/sql-database/sql-database-aad-authentication-configure?tabs=azure-powershell?WT.mc_id=AZ-MVP-5003451
- create users from Azure AD identities https://docs.microsoft.com/en-us/azure/azure-sql/database/authentication-aad-configure?tabs=azure-powershell#create-contained-users-mapped-to-azure-ad-identities&WT.mc_id=AZ-MVP-5003451
- configure security principals https://techcommunity.microsoft.com/t5/azure-sql-database/azure-ad-service-principal-authentication-to-sql-db-code-sample/ba-p/481467?WT.mc_id=AZ-MVP-5003451
Configure database authorization by using platform and database tools
- configure database and object-level permissions using graphical tools https://docs.microsoft.com/en-us/sql/relational-databases/security/authentication-access/getting-started-with-database-engine-permissions?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- apply principle of least privilege for all securables https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/sql/authorization-and-permissions-in-sql-server?WT.mc_id=AZ-MVP-5003451
Implement security for data at rest
- implement Transparent Data Encryption (TDE) https://docs.microsoft.com/en-us/azure/sql-database/transparent-data-encryption-azure-sql?tabs=azure-portal&WT.mc_id=AZ-MVP-5003451
- implement object-level encryption https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted-azure-key-vault?tabs=azure-powershell&WT.mc_id=AZ-MVP-5003451
- implement Dynamic Data Masking https://docs.microsoft.com/en-us/azure/sql-database/sql-database-dynamic-data-masking-get-started-portal?WT.mc_id=AZ-MVP-5003451
- implement Azure Key Vault and disk encryption for Azure VMs https://docs.microsoft.com/en-us/azure/virtual-machines/windows/disk-encryption-portal-quickstart?WT.mc_id=AZ-MVP-5003451
Implement security for data in transit
- configure server and database-level firewall rules https://docs.microsoft.com/en-us/azure/azure-sql/database/firewall-configure#server-level-versus-database-level-ip-firewall-rules?WT.mc_id=AZ-MVP-5003451
- implement Always Encrypted https://docs.microsoft.com/en-us/azure/sql-database/sql-database-always-encrypted?WT.mc_id=AZ-MVP-5003451
Implement compliance controls for sensitive data
- apply a data classification strategy https://docs.microsoft.com/bs-latn-ba/azure/cloud-adoption-framework/govern/policy-compliance/data-classification?WT.mc_id=AZ-MVP-5003451
- configure server and database audits https://docs.microsoft.com/en-us/azure/sql-database/sql-database-auditing?WT.mc_id=AZ-MVP-5003451
- implement data change tracking https://docs.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- perform a vulnerability assessment https://docs.microsoft.com/en-us/azure/sql-database/sql-vulnerability-assessment?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Monitor and Optimize Operational Resources (15-20%)
Monitor activity and performance
- prepare an operational performance baseline https://docs.microsoft.com/en-us/sql/relational-databases/performance/establish-a-performance-baseline?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- determine sources for performance metrics https://docs.microsoft.com/en-us/azure/azure-monitor/platform/data-sources?WT.mc_id=AZ-MVP-5003451
- interpret performance metrics https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance?WT.mc_id=AZ-MVP-5003451
- assess database performance by using Intelligent Insights for Azure SQL Database and Managed Instance https://docs.microsoft.com/en-us/azure/sql-database/sql-database-intelligent-insights?WT.mc_id=AZ-MVP-5003451
- configure and monitor activity and performance at the infrastructure, server, service, and database levels https://docs.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview?WT.mc_id=AZ-MVP-5003451
Implement performance-related maintenance tasks
- implement index maintenance tasks https://docs.microsoft.com/answers/questions/129461/automating-azure-sql-database-index-maintenance.html?WT.mc_id=AZ-MVP-5003451
- implement statistics maintenance tasks https://docs.microsoft.com/en-us/sql/relational-databases/maintenance-plans/update-statistics-task-maintenance-plan?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- configure database auto-tuning https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automatic-tuning-enable?WT.mc_id=AZ-MVP-5003451
- automate database maintenance tasks https://docs.microsoft.com/en-us/azure/azure-sql/database/automation-manage?WT.mc_id=AZ-MVP-5003451
- manage storage capacity https://docs.microsoft.com/en-us/azure/sql-database/sql-database-file-space-management?WT.mc_id=AZ-MVP-5003451
Identify performance-related issues
- configure Query Store to collect performance data https://docs.microsoft.com/en-us/azure/sql-database/sql-database-query-performance?WT.mc_id=AZ-MVP-5003451
- identify sessions that cause blocking https://azure.microsoft.com/en-in/blog/finding-blocking-queries-in-sql-azure?WT.mc_id=AZ-MVP-5003451
- assess growth/fragmentation of databases and logs https://docs.microsoft.com/en-us/azure/azure-sql/database/monitor-tune-overview?WT.mc_id=AZ-MVP-5003451
- assess performance-related database configuration parameters https://docs.microsoft.com/en-us/azure/azure-sql/database/performance-guidance?WT.mc_id=AZ-MVP-5003451
Configure resources for optimal performance
- configure storage and infrastructure resources https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-server-storage-configuration?WT.mc_id=AZ-MVP-5003451
- configure server and service account settings for performance https://docs.microsoft.com/en-us/sql/database-engine/configure-windows/configure-windows-service-accounts-and-permissions?WT.mc_id=AZ-MVP-5003451
- configure Resource Governor for performance https://azure.microsoft.com/en-in/blog/resource-governance-in-azure-sql-database/?WT.mc_id=AZ-MVP-5003451
Configure a user database for optimal performance
- implement database-scoped configuration https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-database-scoped-configuration-transact-sql?view=sql-server-ver15
- configure compute resources for scaling https://docs.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/sql-data-warehouse-manage-compute-overview?WT.mc_id=AZ-MVP-5003451
- configure Intelligent Query Processing (IQP) https://docs.microsoft.com/en-us/sql/relational-databases/performance/intelligent-query-processing?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Optimize Query Performance (5-10%)
Review query plans
- determine the appropriate type of execution plan https://docs.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics?view=sql-server-ver15?WT.mc_id=AZ-MVP-5003451
- identify problem areas in execution plans https://docs.microsoft.com/en-us/sql/relational-databases/performance/analyze-an-actual-execution-plan?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- extract query plans from the Query Store https://docs.microsoft.com/en-us/sql/relational-databases/system-catalog-views/sys-query-store-plan-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Evaluate performance improvements
- determine the appropriate Dynamic Management Views (DMVs) to gather query performance information https://docs.microsoft.com/en-us/azure/azure-sql/database/monitoring-with-dmvs#monitoring-query-performance?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- identify performance issues using DMVs https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs?WT.mc_id=AZ-MVP-5003451
- identify and implement index changes for queries https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- recommend query construct modifications based on resource usage https://docs.microsoft.com/en-us/sql/relational-databases/performance/monitoring-performance-by-using-the-query-store?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- assess the use of hints for query performance https://docs.microsoft.com/en-us/azure/azure-sql/database/performance-guidance#query-tuning-and-hinting?WT.mc_id=AZ-MVP-5003451
Review database table and index design
- identify data quality issues with duplication of data https://docs.microsoft.com/en-us/sql/data-quality-services/data-matching?view=sql-server-ver15?WT.mc_id=AZ-MVP-5003451
- identify normal form of database tables https://docs.microsoft.com/en-us/office/troubleshoot/access/database-normalization-description?WT.mc_id=AZ-MVP-5003451
- assess index design for performance https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-index-design-guide?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- validate data types defined for columns https://docs.microsoft.com/en-us/t-sql/data-types/data-types-transact-sql?WT.mc_id=AZ-MVP-5003451
- recommend table and index storage including filegroups https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15#Recommendations&WT.mc_id=AZ-MVP-5003451
- evaluate table partitioning strategy https://docs.microsoft.com/en-us/azure/architecture/best-practices/data-partitioning?WT.mc_id=AZ-MVP-5003451
- evaluate the use of compression for tables and indexes https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-estimate-data-compression-savings-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Perform Automation of Tasks (10-15%)
Create scheduled tasks
- manage schedules for regular maintenance jobs https://docs.microsoft.com/en-us/azure/sql-database/sql-database-job-automation-overview?WT.mc_id=AZ-MVP-5003451
- configure multi-server automation https://docs.microsoft.com/en-us/sql/ssms/agent/automated-administration-across-an-enterprise?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- configure notifications for task success/failure/non-completion https://docs.microsoft.com/en-us/sql/ssms/agent/notify-an-operator-of-job-status?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Evaluate and implement an alert and notification strategy
- create event notifications based on metrics https://docs.microsoft.com/en-us/azure/mysql/howto-alert-on-metric?WT.mc_id=AZ-MVP-5003451
- create event notifications for Azure resources https://docs.microsoft.com/en-us/sql/t-sql/statements/create-event-notification-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- create alerts for server configuration changes https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/alerts-create?WT.mc_id=AZ-MVP-5003451
- create tasks that respond to event notifications https://docs.microsoft.com/en-us/azure/azure-monitor/learn/tutorial-response?WT.mc_id=AZ-MVP-5003451
Manage and automate tasks in Azure
- perform automated deployment methods for resources https://docs.microsoft.com/en-us/azure/devops/pipelines/targets/azure-sqldb?view=azure-devops&tabs=yaml&WT.mc_id=AZ-MVP-5003451
- automate backups https://docs.microsoft.com/en-us/azure/sql-database/sql-database-automated-backups?tabs=single-database&WT.mc_id=AZ-MVP-5003451
- automate performance tuning and patching https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-automated-patching?WT.mc_id=AZ-MVP-5003451
- implement policies by using automated evaluation modes https://docs.microsoft.com/en-us/sql/relational-databases/policy-based-management/administer-servers-by-using-policy-based-management?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Plan and Implement a High Availability and Disaster Recovery (HADR) Environment (15-20%)
Recommend an HADR strategy for a data platform solution
- recommend HADR strategy based on RPO/RTO requirements https://azure.microsoft.com/en-in/blog/understanding-and-leveraging-azure-sql-database-sla?&WT.mc_id=AZ-MVP-5003451
- evaluate HADR for hybrid deployments https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr#hybrid-it-disaster-recovery-solutions?&WT.mc_id=AZ-MVP-5003451
- evaluate Azure-specific HADR solutions https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-sql-high-availability-dr#azure-only-disaster-recovery-solutions?&WT.mc_id=AZ-MVP-5003451
- identify resources for HADR solutions https://docs.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/business-continuity-high-availability-disaster-recovery-hadr-overview?&WT.mc_id=AZ-MVP-5003451
Test an HADR strategy by using platform, OS, and database tools
- test HA by using failover https://docs.microsoft.com/en-us/azure/sql-database/sql-database-configure-failover-group?tabs=azure-portal&WT.mc_id=AZ-MVP-5003451
- test DR by using failover or restore https://docs.microsoft.com/en-us/azure/sql-database/sql-database-disaster-recovery?WT.mc_id=AZ-MVP-5003451
Perform backup and restore a database by using database tools
- perform a database backup with options https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-database-backup-options-page?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- perform a database restore with options https://docs.microsoft.com/en-us/sql/t-sql/statements/restore-statements-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- perform a database restore to a point in time https://azure.microsoft.com/en-in/blog/azure-sql-database-point-in-time-restore?WT.mc_id=AZ-MVP-5003451
- configure long-term backup retention https://docs.microsoft.com/bs-cyrl-ba/azure/sql-database/sql-database-long-term-backup-retention-configure?WT.mc_id=AZ-MVP-5003451
Configure HA/DR by using OS, platform, and database tools
- configure replication https://docs.microsoft.com/en-us/azure/sql-database/replication-to-sql-database?WT.mc_id=AZ-MVP-5003451
- create an Availability Group https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sql/virtual-machines-windows-portal-sql-availability-group-tutorial?WT.mc_id=AZ-MVP-5003451
- configure auto-failover groups https://docs.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-overview?tabs=azure-powershell&WT.mc_id=AZ-MVP-5003451
- integrate a database into an Availability Group https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/availability-group-add-a-database?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- configure quorum options for a Windows Server Failover Cluster https://docs.microsoft.com/en-us/windows-server/failover-clustering/manage-cluster-quorum?WT.mc_id=AZ-MVP-5003451
- configure an Availability Group listener https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-or-configure-an-availability-group-listener-sql-server?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Perform Administration by Using T-SQL (10-15%)
Examine system health
- evaluate database health using DMVs https://docs.microsoft.com/en-us/azure/sql-database/sql-database-monitoring-with-dmvs?WT.mc_id=AZ-MVP-5003451
- evaluate server health using DMVs https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-views/system-dynamic-management-views?view=sql-server-ver15?WT.mc_id=AZ-MVP-5003451
- perform database consistency checks by using DBCC https://docs.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkdb-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Monitor database configuration by using T-SQL
- assess proper database autogrowth configuration https://docs.microsoft.com/en-us/troubleshoot/sql/admin/considerations-autogrow-autoshrink?WT.mc_id=AZ-MVP-5003451
- report on database free space https://docs.microsoft.com/en-us/sql/relational-databases/databases/display-data-and-log-space-information-for-a-database?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- review database configuration options https://docs.microsoft.com/en-us/sql/relational-databases/databases/change-the-configuration-settings-for-a-database?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
Perform backup and restore a database by using T-SQL
- prepare databases for Always On Availability Groups https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/manually-prepare-a-secondary-database-for-an-availability-group-sql-server?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- perform transaction log backup https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-a-transaction-log-sql-server?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- perform restore of user databases https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-a-database-backup-using-ssms?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- perform database backups with options https://docs.microsoft.com/en-us/sql/t-sql/statements/backup-transact-sql?view=sql-server-ver15#Additional_Considerations?WT.mc_id=AZ-MVP-5003451
Manage authentication by using T-SQL
- manage certificates https://docs.microsoft.com/en-us/sql/relational-databases/security/sql-server-certificates-and-asymmetric-keys?WT.mc_id=AZ-MVP-5003451
- manage security principals https://docs.microsoft.com/en-us/azure/sql-database/sql-database-managed-instance-aad-security-tutorial?WT.mc_id=AZ-MVP-5003451
Manage authorization by using T-SQL
- configure permissions for users to access database objects https://docs.microsoft.com/en-us/sql/relational-databases/security/permissions-database-engine?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
- configure permissions by using custom roles https://docs.microsoft.com/en-us/sql/t-sql/statements/create-role-transact-sql?view=sql-server-ver15&WT.mc_id=AZ-MVP-5003451
