$0.00
Microsoft DP-300 Practice Exam Questions
Administering Relational Databases on Microsoft Azure
Total Questions : 429
Update Date : October 06, 2026
PDF + Test Engine
$65 $95
Test Engine
$55 $85
PDF Only
$45 $75
Last Week DP-300 Exam Results
263
Customers Passed Microsoft DP-300 Exam
94%
Average Score In Real DP-300 Exam
98%
Questions came from our DP-300 dumps.
Prepare your Microsoft DP-300 Certification Exam
Getting ready for the Microsoft DP-300 certification exam can feel challenging, but with the right preparation, success is closer than you think. At PASS4EXAMS, we provide authentic, verified, and updated study materials designed to help you pass confidently on your first attempt.
Why Choose PASS4EXAMS for Microsoft DP-300?
At PASS4EXAMS, we focus on real results. Our exam preparation materials are carefully developed to match the latest exam structure and objectives.
- Real Exam-Based Questions – Practice with content that reflects the actual Microsoft DP-300 exam pattern.
- Updated Regularly – Stay current with the most recent DP-300 syllabus and vendor updates.
- Verified by Experts – Every question is reviewed by certified professionals for accuracy and quality.
- Instant Access – Download your materials immediately after purchase and start preparing right away.
- 100% Pass Guarantee – If you prepare with PASS4EXAMS, your success is fully guaranteed.
What’s Inside the Microsoft DP-300 Study Material
When you choose PASS4EXAMS, you get a complete and reliable preparation experience:
- Comprehensive Question & Answer Sets that cover all exam objectives.
- Practice Tests that simulate the real exam environment.
- Detailed Explanations to strengthen understanding of each concept.
- Free 3 months Updates ensuring your material stays relevant.
- Expert Preparation Tips to help you study efficiently and effectively.
Why Get Certified?
Earning your Microsoft DP-300 certification demonstrates your professional competence, validates your technical skills, and enhances your career opportunities. It’s a globally recognized credential that helps you stand out in the competitive IT industry.
Microsoft DP-300 Sample Question Answers
Question # 1Task 3You need to prevent users from accidentally deleting db1 from the Azure portal. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Apply an Azure Resource Manager Delete lock / CanNotDelete lock directly to the Azure SQL database resource db1. Microsoft states that Azure resource locks can be applied at subscription, resource group, or resource scope to protect resources from accidental deletion or modification. In the Azure portal, the lock types are shown as Delete and Read-only; in CLI/PowerShell, they are called CanNotDelete and ReadOnly. A CanNotDelete/Delete lock allows users to read and modify the resource, but prevents deletion. Azure Portal Method — Recommended for Simulation Step 1: Open the database resource Sign in to the Azure portal. In the search bar, search for SQL databases. Select the database named db1. Make sure you select the database resource itself, not only the SQL logical server. Step 2: Open Locks In the left menu of db1, scroll to Settings. Select Locks. Select Add. Step 3: Create the delete lock Configure the lock as follows: Setting Value Lock name PreventDelete-db1 Lock type Delete Notes Prevent accidental deletion of db1 Then select OK or Save. In the portal, choose Delete, not Read-only. A Read-only lock is too restrictive because it can block management updates. For this task, the requirement is only to stop accidental deletion, so Delete / CanNotDelete is the correct lock type. Microsoft confirms that CanNotDelete prevents deletion but still permits reading and modifying the resource. Step 4: Verify the lock Stay on the db1 database page. Go back to Locks. Confirm the lock exists with: Name: PreventDelete-db1 Lock type: Delete The task is complete once db1 has a Delete lock applied. PowerShell Method Use this if the lab provides Azure PowerShell. New-AzResourceLock ` -LockLevel CanNotDelete ` -LockName "PreventDelete-db1" ` -LockNotes "Prevent accidental deletion of db1" ` -ResourceGroupName "" ` -ResourceName "/db1" ` -ResourceType "Microsoft.Sql/servers/databases" Microsoft’s New-AzResourceLock documentation includes an Azure SQL Database example using resource type Microsoft.Sql/servers/databases and resource name format serverName/databaseName. Example format: New-AzResourceLock ` -LockLevel CanNotDelete ` -LockName "PreventDelete-db1" ` -LockNotes "Prevent accidental deletion of db1" ` -ResourceGroupName "RG1" ` -ResourceName "sql60152867/db1" ` -ResourceType "Microsoft.Sql/servers/databases" Replace RG1 and sql60152867 with the actual resource group and SQL logical server that hosts db1. Azure CLI Method Use Azure CLI only if the lab gives Cloud Shell and you know the full resource ID. First get the database resource ID: az sql db show \ --resource-group \ --server \ --name db1 \ --query id \ --output tsv Then create the lock: az resource lock create \ --name PreventDelete-db1 \ --lock-type CanNotDelete \ --resource \ --notes "Prevent accidental deletion of db1" Azure CLI supports resource-level lock creation with --lock-type CanNotDelete or ReadOnly. SSMS / T-SQL Clarification SSMS is not the correct tool for this task. A delete lock is an Azure Resource Manager control-plane setting, not a SQL data-plane setting. SQL Server Management Studio can manage database objects and run T-SQL, but it cannot create Azure portal deletion protection locks for an Azure SQL Database.
Explanation: Apply an Azure Resource Manager Delete lock / CanNotDelete lock directly to the Azure SQL database resource db1. Microsoft states that Azure resource locks can be applied at subscription, resource group, or resource scope to protect resources from accidental deletion or modification. In the Azure portal, the lock types are shown as Delete and Read-only; in CLI/PowerShell, they are called CanNotDelete and ReadOnly. A CanNotDelete/Delete lock allows users to read and modify the resource, but prevents deletion. Azure Portal Method — Recommended for Simulation Step 1: Open the database resource Sign in to the Azure portal. In the search bar, search for SQL databases. Select the database named db1. Make sure you select the database resource itself, not only the SQL logical server. Step 2: Open Locks In the left menu of db1, scroll to Settings. Select Locks. Select Add. Step 3: Create the delete lock Configure the lock as follows: Setting Value Lock name PreventDelete-db1 Lock type Delete Notes Prevent accidental deletion of db1 Then select OK or Save. In the portal, choose Delete, not Read-only. A Read-only lock is too restrictive because it can block management updates. For this task, the requirement is only to stop accidental deletion, so Delete / CanNotDelete is the correct lock type. Microsoft confirms that CanNotDelete prevents deletion but still permits reading and modifying the resource. Step 4: Verify the lock Stay on the db1 database page. Go back to Locks. Confirm the lock exists with: Name: PreventDelete-db1 Lock type: Delete The task is complete once db1 has a Delete lock applied. PowerShell Method Use this if the lab provides Azure PowerShell. New-AzResourceLock ` -LockLevel CanNotDelete ` -LockName "PreventDelete-db1" ` -LockNotes "Prevent accidental deletion of db1" ` -ResourceGroupName "" ` -ResourceName "/db1" ` -ResourceType "Microsoft.Sql/servers/databases" Microsoft’s New-AzResourceLock documentation includes an Azure SQL Database example using resource type Microsoft.Sql/servers/databases and resource name format serverName/databaseName. Example format: New-AzResourceLock ` -LockLevel CanNotDelete ` -LockName "PreventDelete-db1" ` -LockNotes "Prevent accidental deletion of db1" ` -ResourceGroupName "RG1" ` -ResourceName "sql60152867/db1" ` -ResourceType "Microsoft.Sql/servers/databases" Replace RG1 and sql60152867 with the actual resource group and SQL logical server that hosts db1. Azure CLI Method Use Azure CLI only if the lab gives Cloud Shell and you know the full resource ID. First get the database resource ID: az sql db show \ --resource-group \ --server \ --name db1 \ --query id \ --output tsv Then create the lock: az resource lock create \ --name PreventDelete-db1 \ --lock-type CanNotDelete \ --resource \ --notes "Prevent accidental deletion of db1" Azure CLI supports resource-level lock creation with --lock-type CanNotDelete or ReadOnly. SSMS / T-SQL Clarification SSMS is not the correct tool for this task. A delete lock is an Azure Resource Manager control-plane setting, not a SQL data-plane setting. SQL Server Management Studio can manage database objects and run T-SQL, but it cannot create Azure portal deletion protection locks for an Azure SQL Database.
Question # 2
Task 6You need to ensure that you can connect to db1 by using a private IP address on a virtual network named VNET1 You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Create an Azure Private Endpoint for the Azure SQL logical server that hosts db1, place the private endpoint in VNET1, and integrate it with the private DNS zone: privatelink.database.windows.net This is the correct solution because Azure SQL Database is a PaaS service. You do not assign a private IP directly to db1. Instead, Azure creates a private endpoint network interface in the virtual network. That private endpoint receives a private IP address from a subnet in VNET1, and clients in VNET1 use that private IP path to reach the SQL server. Microsoft defines a private endpoint as a network interface that uses a private IP address from your virtual network to connect privately to a Private Link resource such as Azure SQL Database. Azure Portal Method — Recommended for Simulation Step 1: Identify the SQL logical server that hosts db1 Sign in to the Azure portal. Search for SQL databases. Open db1. On the database overview page, identify the Server name. The private endpoint is created for the Azure SQL logical server, not for the database object alone. For Azure SQL Database, the Private Link resource type is: Microsoft.Sql/servers and the target subresource is: sqlServer Microsoft lists Azure SQL Database private endpoint DNS configuration under Microsoft.Sql/servers with subresource sqlServer. Step 2: Open the SQL server networking page Open the Azure SQL logical server that hosts db1. In the left menu, go to: Security > Networking Select the Private access tab. Select Create a private endpoint. Microsoft’s Azure SQL private endpoint workflow is performed from the SQL server resource under Networking > Private access, where you can create or manage private endpoint connections. Step 3: Configure the private endpoint basics On the Create private endpoint page: Setting Value Subscription Use the lab subscription Resource group Use the lab resource group Name pe-db1-sql Region Same region as VNET1, if possible The name is not exam-critical. The critical part is that the endpoint is associated with VNET1 and the SQL server that hosts db1. Step 4: Configure the target resource On the Resource tab, configure: Setting Value Connection method Connect to an Azure resource in my directory Resource type Microsoft.Sql/servers Resource SQL logical server that hosts db1 Target sub-resource sqlServer Do not choose storage, VM, managed instance, or any unrelated resource type. This is Azure SQL Database, so the target subresource must be sqlServer. Step 5: Configure VNET1 and subnet On the Virtual Network tab: Setting Value Virtual network VNET1 Subnet Select an available subnet in VNET1 Private IP configuration Dynamic is fine unless the lab requires static Azure will create a network interface for the private endpoint and assign it a private IP address from the selected subnet. Microsoft notes that the network interface page for the private endpoint shows the private IP address assigned to the private endpoint connection. Step 6: Configure private DNS integration On the DNS tab: Enable private DNS zone integration. Use or create the private DNS zone: privatelink.database.windows.net Link the private DNS zone to: VNET1 This is not optional in a clean exam solution. Without DNS integration, clients may still resolve the SQL server name to the public endpoint instead of the private endpoint. Microsoft states that DNS is critical because it resolves the private endpoint IP address, and for Azure SQL Database the recommended private DNS zone is privatelink.database.windows.net. Step 7: Review and create Select Review + create. Confirm: Resource: SQL logical server hosting db1 Target subresource: sqlServer Virtual network: VNET1 Private DNS zone: privatelink.database.windows.net Select Create. After deployment, the SQL server will have a private endpoint connection associated with VNET1. Step 8: Approve the private endpoint connection if required In most same-directory deployments, approval may be automatic. If approval is pending: Open the SQL logical server. Go to: Networking > Private access Select the pending private endpoint connection. Select Approve. Microsoft documents that SQL administrators can approve or reject private endpoint connections from the SQL server private access page. Step 9: Optional but recommended — Disable public network access The task only says you need to connect by private IP from VNET1. It does not explicitly say to block public access. But if the exam expects private-only access, then disable public access after the private endpoint works. On the SQL logical server: Go to: Security > Networking > Public access Set Public network access to: Disabled or select: Deny public network access Save. Be careful: Microsoft states that adding a private endpoint does not automatically block public routing to the logical server. Public access must be denied separately if you want private-only access. How to Connect from SSMS You should connect from a machine that is inside VNET1, such as an Azure VM joined to VNET1. Step 1: Test DNS from a VM in VNET1 From a VM in VNET1, run: nslookup .database.windows.net Expected result: the name should resolve through the private endpoint path and return a private IP address from VNET1’s address space. Microsoft explains that connection URLs do not change; DNS resolution is overridden so the existing service FQDN resolves to the private endpoint private IP address. Step 2: Connect with SSMS In SSMS, connect using the normal Azure SQL server name: .database.windows.net Then select database: db1 Use normal SQL authentication or Microsoft Entra authentication. Do not type the raw private IP address into SSMS unless the lab specifically forces it. For Azure SQL, the correct operational pattern is to connect to the SQL server FQDN and allow private DNS to resolve that FQDN to the private endpoint IP. Direct IP connection can cause TLS/certificate name problems because the server certificate matches the DNS name, not the private IP. Verification The task is complete when all of these are true: Private endpoint exists for the SQL logical server hosting db1. Target subresource is sqlServer. The private endpoint is deployed into VNET1. A private IP address is assigned to the private endpoint NIC. Private DNS zone privatelink.database.windows.net exists. The private DNS zone is linked to VNET1. The SQL server FQDN resolves to the private endpoint private IP from inside VNET1. SSMS can connect to db1 from a VM or client connected to VNET1. Final Exam-Lab Action Use the Azure portal and configure: SQL server hosting db1 > Networking > Private access > Create private endpoint Resource type: Microsoft.Sql/servers Target subresource: sqlServer Virtual network: VNET1 Private DNS zone: privatelink.database.windows.net Then connect from a VM or client in VNET1 using: .database.windows.net That is the correct way to ensure db1 is reachable through a private IP address on VNET1.
Explanation: Create an Azure Private Endpoint for the Azure SQL logical server that hosts db1, place the private endpoint in VNET1, and integrate it with the private DNS zone: privatelink.database.windows.net This is the correct solution because Azure SQL Database is a PaaS service. You do not assign a private IP directly to db1. Instead, Azure creates a private endpoint network interface in the virtual network. That private endpoint receives a private IP address from a subnet in VNET1, and clients in VNET1 use that private IP path to reach the SQL server. Microsoft defines a private endpoint as a network interface that uses a private IP address from your virtual network to connect privately to a Private Link resource such as Azure SQL Database. Azure Portal Method — Recommended for Simulation Step 1: Identify the SQL logical server that hosts db1 Sign in to the Azure portal. Search for SQL databases. Open db1. On the database overview page, identify the Server name. The private endpoint is created for the Azure SQL logical server, not for the database object alone. For Azure SQL Database, the Private Link resource type is: Microsoft.Sql/servers and the target subresource is: sqlServer Microsoft lists Azure SQL Database private endpoint DNS configuration under Microsoft.Sql/servers with subresource sqlServer. Step 2: Open the SQL server networking page Open the Azure SQL logical server that hosts db1. In the left menu, go to: Security > Networking Select the Private access tab. Select Create a private endpoint. Microsoft’s Azure SQL private endpoint workflow is performed from the SQL server resource under Networking > Private access, where you can create or manage private endpoint connections. Step 3: Configure the private endpoint basics On the Create private endpoint page: Setting Value Subscription Use the lab subscription Resource group Use the lab resource group Name pe-db1-sql Region Same region as VNET1, if possible The name is not exam-critical. The critical part is that the endpoint is associated with VNET1 and the SQL server that hosts db1. Step 4: Configure the target resource On the Resource tab, configure: Setting Value Connection method Connect to an Azure resource in my directory Resource type Microsoft.Sql/servers Resource SQL logical server that hosts db1 Target sub-resource sqlServer Do not choose storage, VM, managed instance, or any unrelated resource type. This is Azure SQL Database, so the target subresource must be sqlServer. Step 5: Configure VNET1 and subnet On the Virtual Network tab: Setting Value Virtual network VNET1 Subnet Select an available subnet in VNET1 Private IP configuration Dynamic is fine unless the lab requires static Azure will create a network interface for the private endpoint and assign it a private IP address from the selected subnet. Microsoft notes that the network interface page for the private endpoint shows the private IP address assigned to the private endpoint connection. Step 6: Configure private DNS integration On the DNS tab: Enable private DNS zone integration. Use or create the private DNS zone: privatelink.database.windows.net Link the private DNS zone to: VNET1 This is not optional in a clean exam solution. Without DNS integration, clients may still resolve the SQL server name to the public endpoint instead of the private endpoint. Microsoft states that DNS is critical because it resolves the private endpoint IP address, and for Azure SQL Database the recommended private DNS zone is privatelink.database.windows.net. Step 7: Review and create Select Review + create. Confirm: Resource: SQL logical server hosting db1 Target subresource: sqlServer Virtual network: VNET1 Private DNS zone: privatelink.database.windows.net Select Create. After deployment, the SQL server will have a private endpoint connection associated with VNET1. Step 8: Approve the private endpoint connection if required In most same-directory deployments, approval may be automatic. If approval is pending: Open the SQL logical server. Go to: Networking > Private access Select the pending private endpoint connection. Select Approve. Microsoft documents that SQL administrators can approve or reject private endpoint connections from the SQL server private access page. Step 9: Optional but recommended — Disable public network access The task only says you need to connect by private IP from VNET1. It does not explicitly say to block public access. But if the exam expects private-only access, then disable public access after the private endpoint works. On the SQL logical server: Go to: Security > Networking > Public access Set Public network access to: Disabled or select: Deny public network access Save. Be careful: Microsoft states that adding a private endpoint does not automatically block public routing to the logical server. Public access must be denied separately if you want private-only access. How to Connect from SSMS You should connect from a machine that is inside VNET1, such as an Azure VM joined to VNET1. Step 1: Test DNS from a VM in VNET1 From a VM in VNET1, run: nslookup .database.windows.net Expected result: the name should resolve through the private endpoint path and return a private IP address from VNET1’s address space. Microsoft explains that connection URLs do not change; DNS resolution is overridden so the existing service FQDN resolves to the private endpoint private IP address. Step 2: Connect with SSMS In SSMS, connect using the normal Azure SQL server name: .database.windows.net Then select database: db1 Use normal SQL authentication or Microsoft Entra authentication. Do not type the raw private IP address into SSMS unless the lab specifically forces it. For Azure SQL, the correct operational pattern is to connect to the SQL server FQDN and allow private DNS to resolve that FQDN to the private endpoint IP. Direct IP connection can cause TLS/certificate name problems because the server certificate matches the DNS name, not the private IP. Verification The task is complete when all of these are true: Private endpoint exists for the SQL logical server hosting db1. Target subresource is sqlServer. The private endpoint is deployed into VNET1. A private IP address is assigned to the private endpoint NIC. Private DNS zone privatelink.database.windows.net exists. The private DNS zone is linked to VNET1. The SQL server FQDN resolves to the private endpoint private IP from inside VNET1. SSMS can connect to db1 from a VM or client connected to VNET1. Final Exam-Lab Action Use the Azure portal and configure: SQL server hosting db1 > Networking > Private access > Create private endpoint Resource type: Microsoft.Sql/servers Target subresource: sqlServer Virtual network: VNET1 Private DNS zone: privatelink.database.windows.net Then connect from a VM or client in VNET1 using: .database.windows.net That is the correct way to ensure db1 is reachable through a private IP address on VNET1.
Question # 3
Task 7You need to capture the following information for all the databases on sql60l 52867; • Queries to the databases • Users that executed the queries The captured information must be stored in sa60152867. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Enable Azure SQL Auditing at the server level on the Azure SQL logical server: sql60152867 and configure the audit destination as the Azure Storage account: sa60152867 This is the correct solution because the task says all databases on sql60152867. Database-level auditing would only apply to one database. Server-level auditing applies to all existing and newly created databases on the logical server. Microsoft states that server auditing policies apply to all existing and newly created databases on the server. The default auditing policy includes BATCH_COMPLETED_GROUP, which audits queries and stored procedures executed against the database, plus successful and failed authentication events. Azure Portal Method — Recommended for Simulation Step 1: Open the Azure SQL logical server Sign in to the Azure portal. Search for: SQL servers Open the SQL logical server named: sql60152867 Be careful with the name. In your prompt, it appears as sql60l 52867, but the earlier tasks strongly indicate the real server name is probably: sql60152867 Use the SQL logical server that hosts the databases, not a single SQL database. Step 2: Open Auditing On the SQL server page: In the left menu, go to Security. Select Auditing. Microsoft’s setup path is to navigate to Auditing under the Security heading in either the SQL database or SQL server pane. For this task, you must use the SQL server pane because the requirement covers all databases. Step 3: Enable server-level auditing Set: Enable Azure SQL Auditing = On or: Auditing = On depending on the portal wording. This enables the server-level audit policy. Step 4: Select Storage as the audit destination Under Audit log destination, select: Storage Then choose the storage account: sa60152867 Microsoft states that Azure SQL auditing can write database events to an Azure storage account, Log Analytics workspace, or Event Hubs. For this simulation, the destination must be the storage account sa60152867, so do not choose Log Analytics or Event Hub unless the task separately asks for them. Step 5: Configure storage authentication If the portal asks for authentication type, use one of these depending on what is available in the lab: Option Use when Managed Identity Preferred if available Storage access keys Use if the lab expects the older/default storage-key method Microsoft recommends managed identity for storage authentication because storage access keys are a security risk. However, many exam simulations still accept the default storage-key flow as long as the audit destination is correctly set to the required storage account. Step 6: Leave default audit action groups enabled Do not remove the default audit action groups. The default Azure SQL auditing policy includes: BATCH_COMPLETED_GROUP SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP FAILED_DATABASE_AUTHENTICATION_GROUP BATCH_COMPLETED_GROUP is the key one here because it captures completed T-SQL batches, meaning the queries executed against the databases. Authentication and principal fields identify the login/user context. Microsoft states that the default auditing policy audits all queries and stored procedures executed against the database, plus successful and failed logins. Step 7: Save the auditing configuration Select: Save Wait for the portal notification that the auditing settings were saved successfully. That completes the required configuration. Verification Verify from the Azure portal Open the SQL server: sql60152867 Go to: Security > Auditing Confirm: Auditing: On Destination: Storage Storage account: sa60152867 Scope: Server-level Verify storage output After users execute queries, audit files should appear in the storage account. Open storage account: sa60152867 Go to Containers. Open: sqldbauditlogs Azure SQL audit logs written to Azure Storage are stored in a container named sqldbauditlogs. Audit logs are written as .xel files and can be opened with SQL Server Management Studio. What Information Is Captured? The audit log includes the exact fields needed for this task. Required information Audit log field Query executed statement / statement_s Database name database_name / database_name_s User context database_principal_name / database_principal_name_s Login context server_principal_name / server_principal_name_s Session principal session_server_principal_name / session_server_principal_name_s Time of execution event_time / event_time_t Client IP client_ip / client_ip_s Microsoft’s audit log format documents statement as the T-SQL statement that was executed, database_principal_name as the database user context, and server_principal_name as the current login. SSMS Method — Viewing the Audit Logs SSMS is not the main tool to enable Azure SQL auditing in this simulation, but it can be used to read the .xel audit files after they are written. Step 1: Download or access the .xel files From the storage account: sa60152867 > Containers > sqldbauditlogs Download the .xel audit file, or access it through supported tooling. Step 2: Open the audit file in SSMS Open SQL Server Management Studio. Go to: File > Open > File Select the .xel audit file. Review fields such as: statement database_principal_name server_principal_name database_name event_time client_ip This verifies that the queries and the users that executed them are being captured. Azure CLI Method Use this only if Cloud Shell is available and you know the resource group names. az sql server audit-policy update \ --resource-group \ --name sql60152867 \ --state Enabled \ --storage-account sa60152867 Depending on the environment, you may also need to specify the storage endpoint and access key, or use managed identity configuration. In the portal simulation, the UI normally handles this for you. PowerShell Method Use this if Azure PowerShell is available. Set-AzSqlServerAudit ` -ResourceGroupName "" ` -ServerName "sql60152867" ` -BlobStorageTargetState Enabled ` -StorageAccountResourceId "/subscriptions//resourceGroups//providers/Microsoft.Storage/storageAccounts/sa60152867" This configures auditing at the server level, which is the correct scope for all databases. Final Exam-Lab Action Configure this in the Azure portal: SQL server: sql60152867 Security > Auditing Auditing: On Audit destination: Storage Storage account: sa60152867 Default audit action groups: Enabled Save That captures queries and the users who executed them for all databases on the SQL logical server and stores the audit records in sa60152867.
Explanation: Enable Azure SQL Auditing at the server level on the Azure SQL logical server: sql60152867 and configure the audit destination as the Azure Storage account: sa60152867 This is the correct solution because the task says all databases on sql60152867. Database-level auditing would only apply to one database. Server-level auditing applies to all existing and newly created databases on the logical server. Microsoft states that server auditing policies apply to all existing and newly created databases on the server. The default auditing policy includes BATCH_COMPLETED_GROUP, which audits queries and stored procedures executed against the database, plus successful and failed authentication events. Azure Portal Method — Recommended for Simulation Step 1: Open the Azure SQL logical server Sign in to the Azure portal. Search for: SQL servers Open the SQL logical server named: sql60152867 Be careful with the name. In your prompt, it appears as sql60l 52867, but the earlier tasks strongly indicate the real server name is probably: sql60152867 Use the SQL logical server that hosts the databases, not a single SQL database. Step 2: Open Auditing On the SQL server page: In the left menu, go to Security. Select Auditing. Microsoft’s setup path is to navigate to Auditing under the Security heading in either the SQL database or SQL server pane. For this task, you must use the SQL server pane because the requirement covers all databases. Step 3: Enable server-level auditing Set: Enable Azure SQL Auditing = On or: Auditing = On depending on the portal wording. This enables the server-level audit policy. Step 4: Select Storage as the audit destination Under Audit log destination, select: Storage Then choose the storage account: sa60152867 Microsoft states that Azure SQL auditing can write database events to an Azure storage account, Log Analytics workspace, or Event Hubs. For this simulation, the destination must be the storage account sa60152867, so do not choose Log Analytics or Event Hub unless the task separately asks for them. Step 5: Configure storage authentication If the portal asks for authentication type, use one of these depending on what is available in the lab: Option Use when Managed Identity Preferred if available Storage access keys Use if the lab expects the older/default storage-key method Microsoft recommends managed identity for storage authentication because storage access keys are a security risk. However, many exam simulations still accept the default storage-key flow as long as the audit destination is correctly set to the required storage account. Step 6: Leave default audit action groups enabled Do not remove the default audit action groups. The default Azure SQL auditing policy includes: BATCH_COMPLETED_GROUP SUCCESSFUL_DATABASE_AUTHENTICATION_GROUP FAILED_DATABASE_AUTHENTICATION_GROUP BATCH_COMPLETED_GROUP is the key one here because it captures completed T-SQL batches, meaning the queries executed against the databases. Authentication and principal fields identify the login/user context. Microsoft states that the default auditing policy audits all queries and stored procedures executed against the database, plus successful and failed logins. Step 7: Save the auditing configuration Select: Save Wait for the portal notification that the auditing settings were saved successfully. That completes the required configuration. Verification Verify from the Azure portal Open the SQL server: sql60152867 Go to: Security > Auditing Confirm: Auditing: On Destination: Storage Storage account: sa60152867 Scope: Server-level Verify storage output After users execute queries, audit files should appear in the storage account. Open storage account: sa60152867 Go to Containers. Open: sqldbauditlogs Azure SQL audit logs written to Azure Storage are stored in a container named sqldbauditlogs. Audit logs are written as .xel files and can be opened with SQL Server Management Studio. What Information Is Captured? The audit log includes the exact fields needed for this task. Required information Audit log field Query executed statement / statement_s Database name database_name / database_name_s User context database_principal_name / database_principal_name_s Login context server_principal_name / server_principal_name_s Session principal session_server_principal_name / session_server_principal_name_s Time of execution event_time / event_time_t Client IP client_ip / client_ip_s Microsoft’s audit log format documents statement as the T-SQL statement that was executed, database_principal_name as the database user context, and server_principal_name as the current login. SSMS Method — Viewing the Audit Logs SSMS is not the main tool to enable Azure SQL auditing in this simulation, but it can be used to read the .xel audit files after they are written. Step 1: Download or access the .xel files From the storage account: sa60152867 > Containers > sqldbauditlogs Download the .xel audit file, or access it through supported tooling. Step 2: Open the audit file in SSMS Open SQL Server Management Studio. Go to: File > Open > File Select the .xel audit file. Review fields such as: statement database_principal_name server_principal_name database_name event_time client_ip This verifies that the queries and the users that executed them are being captured. Azure CLI Method Use this only if Cloud Shell is available and you know the resource group names. az sql server audit-policy update \ --resource-group \ --name sql60152867 \ --state Enabled \ --storage-account sa60152867 Depending on the environment, you may also need to specify the storage endpoint and access key, or use managed identity configuration. In the portal simulation, the UI normally handles this for you. PowerShell Method Use this if Azure PowerShell is available. Set-AzSqlServerAudit ` -ResourceGroupName "" ` -ServerName "sql60152867" ` -BlobStorageTargetState Enabled ` -StorageAccountResourceId "/subscriptions//resourceGroups//providers/Microsoft.Storage/storageAccounts/sa60152867" This configures auditing at the server level, which is the correct scope for all databases. Final Exam-Lab Action Configure this in the Azure portal: SQL server: sql60152867 Security > Auditing Auditing: On Audit destination: Storage Storage account: sa60152867 Default audit action groups: Enabled Save That captures queries and the users who executed them for all databases on the SQL logical server and stores the audit records in sa60152867.
Question # 4
Task 9You need to ensure that when non-administrative users query the SalesLT.Customer table in db1, email addresses are obscured. For example, an email address of [email protected] must appear as [email protected]. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Configure Dynamic Data Masking on the email column in: SalesLT.Customer The column is normally: EmailAddress Use the built-in masking function: email() Microsoft documents that Dynamic Data Masking hides sensitive data in query results for nonprivileged users without changing the stored data. The built-in email() masking function exposes the first letter and returns the masked format [email protected], which exactly matches the requirement. Method 1 — SSMS / T-SQL Method This is the fastest and most reliable method. Step 1: Connect to db1 Open SQL Server Management Studio. Connect to the Azure SQL logical server that hosts db1. Open a query window against database: db1 Step 2: Apply the email mask Run: ALTER TABLE [SalesLT].[Customer] ALTER COLUMN [EmailAddress] ADD MASKED WITH (FUNCTION = 'email()'); This adds a Dynamic Data Masking rule to the EmailAddress column. The actual email address remains stored in the table, but users without permission to view unmasked data will see the masked value. Microsoft’s documented syntax for adding an email mask is ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'). Step 3: Verify that the column is masked Run: SELECT OBJECT_SCHEMA_NAME(mc.object_id) AS schema_name, OBJECT_NAME(mc.object_id) AS table_name, c.name AS column_name, mc.masking_function FROM sys.masked_columns AS mc JOIN sys.columns AS c ON mc.object_id = c.object_id AND mc.column_id = c.column_id WHERE OBJECT_SCHEMA_NAME(mc.object_id) = 'SalesLT' AND OBJECT_NAME(mc.object_id) = 'Customer' AND c.name = 'EmailAddress'; Expected result: schema_name SalesLT table_name Customer column_name EmailAddress masking_function email() Step 4: Test as a non-administrative user If you have a test user, run: EXECUTE AS USER = 'TestUser'; SELECT TOP (10) EmailAddress FROM SalesLT.Customer; REVERT; Expected output should look like: [email protected] [email protected] [email protected] A user with administrative privileges, db_owner, or UNMASK permission can still see the original email value. Microsoft states that users with administrative rights such as server admin, Microsoft Entra admin, and db_owner can view the original unmasked data. Method 2 — Azure Portal Method Use this if the simulation expects portal configuration. Step 1: Open db1 Sign in to the Azure portal. Search for SQL databases. Open database: db1 Step 2: Open Dynamic Data Masking From the db1 page: Security > Dynamic Data Masking Microsoft states that for Azure SQL Database, Dynamic Data Masking can be configured in the Azure portal from the SQL database configuration pane under Security > Dynamic Data Masking. Step 3: Add a masking rule Add a mask for the email column: Setting Value Schema SalesLT Table Customer Column EmailAddress Masking field format Email Masking function email() Then select: Save The portal may show the mask type simply as: Email That is the correct option because it maps to the email() masking function. Important Permission Check Dynamic Data Masking only affects users who do not have permission to view unmasked data. If a non-administrative user was previously granted UNMASK, remove it: REVOKE UNMASK TO [UserName]; Or, if a role was granted UNMASK, revoke it from the role: REVOKE UNMASK TO [RoleName]; Do not grant UNMASK to normal users. UNMASK allows users to bypass masking and see the original values. Microsoft documents that UNMASK permission controls whether users can view masked or original data. Final Exam-Lab Action Run this against db1: ALTER TABLE [SalesLT].[Customer] ALTER COLUMN [EmailAddress] ADD MASKED WITH (FUNCTION = 'email()'); Then verify: SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, OBJECT_NAME(object_id) AS table_name, name AS column_name, masking_function FROM sys.masked_columns WHERE OBJECT_SCHEMA_NAME(object_id) = 'SalesLT' AND OBJECT_NAME(object_id) = 'Customer' AND name = 'EmailAddress'; The task is complete when non-administrative users querying SalesLT.Customer.EmailAddress see masked email values such as: [email protected]
Explanation: Configure Dynamic Data Masking on the email column in: SalesLT.Customer The column is normally: EmailAddress Use the built-in masking function: email() Microsoft documents that Dynamic Data Masking hides sensitive data in query results for nonprivileged users without changing the stored data. The built-in email() masking function exposes the first letter and returns the masked format [email protected], which exactly matches the requirement. Method 1 — SSMS / T-SQL Method This is the fastest and most reliable method. Step 1: Connect to db1 Open SQL Server Management Studio. Connect to the Azure SQL logical server that hosts db1. Open a query window against database: db1 Step 2: Apply the email mask Run: ALTER TABLE [SalesLT].[Customer] ALTER COLUMN [EmailAddress] ADD MASKED WITH (FUNCTION = 'email()'); This adds a Dynamic Data Masking rule to the EmailAddress column. The actual email address remains stored in the table, but users without permission to view unmasked data will see the masked value. Microsoft’s documented syntax for adding an email mask is ALTER COLUMN Email ADD MASKED WITH (FUNCTION = 'email()'). Step 3: Verify that the column is masked Run: SELECT OBJECT_SCHEMA_NAME(mc.object_id) AS schema_name, OBJECT_NAME(mc.object_id) AS table_name, c.name AS column_name, mc.masking_function FROM sys.masked_columns AS mc JOIN sys.columns AS c ON mc.object_id = c.object_id AND mc.column_id = c.column_id WHERE OBJECT_SCHEMA_NAME(mc.object_id) = 'SalesLT' AND OBJECT_NAME(mc.object_id) = 'Customer' AND c.name = 'EmailAddress'; Expected result: schema_name SalesLT table_name Customer column_name EmailAddress masking_function email() Step 4: Test as a non-administrative user If you have a test user, run: EXECUTE AS USER = 'TestUser'; SELECT TOP (10) EmailAddress FROM SalesLT.Customer; REVERT; Expected output should look like: [email protected] [email protected] [email protected] A user with administrative privileges, db_owner, or UNMASK permission can still see the original email value. Microsoft states that users with administrative rights such as server admin, Microsoft Entra admin, and db_owner can view the original unmasked data. Method 2 — Azure Portal Method Use this if the simulation expects portal configuration. Step 1: Open db1 Sign in to the Azure portal. Search for SQL databases. Open database: db1 Step 2: Open Dynamic Data Masking From the db1 page: Security > Dynamic Data Masking Microsoft states that for Azure SQL Database, Dynamic Data Masking can be configured in the Azure portal from the SQL database configuration pane under Security > Dynamic Data Masking. Step 3: Add a masking rule Add a mask for the email column: Setting Value Schema SalesLT Table Customer Column EmailAddress Masking field format Email Masking function email() Then select: Save The portal may show the mask type simply as: Email That is the correct option because it maps to the email() masking function. Important Permission Check Dynamic Data Masking only affects users who do not have permission to view unmasked data. If a non-administrative user was previously granted UNMASK, remove it: REVOKE UNMASK TO [UserName]; Or, if a role was granted UNMASK, revoke it from the role: REVOKE UNMASK TO [RoleName]; Do not grant UNMASK to normal users. UNMASK allows users to bypass masking and see the original values. Microsoft documents that UNMASK permission controls whether users can view masked or original data. Final Exam-Lab Action Run this against db1: ALTER TABLE [SalesLT].[Customer] ALTER COLUMN [EmailAddress] ADD MASKED WITH (FUNCTION = 'email()'); Then verify: SELECT OBJECT_SCHEMA_NAME(object_id) AS schema_name, OBJECT_NAME(object_id) AS table_name, name AS column_name, masking_function FROM sys.masked_columns WHERE OBJECT_SCHEMA_NAME(object_id) = 'SalesLT' AND OBJECT_NAME(object_id) = 'Customer' AND name = 'EmailAddress'; The task is complete when non-administrative users querying SalesLT.Customer.EmailAddress see masked email values such as: [email protected]
Question # 5
Task 2You need to configure differential backups for the db1 Azure SQL database to be once a day instead of twice day. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Requirement: Configure differential backups for Azure SQL Database db1 to run once a day instead of twice a day. Correct setting: Change Differential backup frequency from 12 Hours to 24 Hours. Azure SQL Database supports differential backup frequency of either 12 hours or 24 hours. A 12-hour frequency means twice per day; a 24-hour frequency means once per day. Microsoft also notes that 24-hour differential backup frequency can increase restore time compared with 12-hour frequency. Method 1 — Azure Portal Method This is the best method for the simulation. Step 1: Open the Azure SQL logical server Sign in to the Azure portal. Search for SQL servers. Open the logical SQL server that hosts database db1. Do not start from SQL Server Management Studio for this task. The differential backup frequency is an Azure SQL backup policy setting, not a normal T-SQL database setting. Step 2: Open the Backups page In the SQL server left menu, select Backups. Select the Retention policies tab. Microsoft’s documented portal path is to go to the logical SQL server, select Backups, then select the Retention policies tab. Step 3: Select database db1 In the list of databases, locate db1. Select the checkbox next to db1. Select Configure policies from the action bar. Step 4: Change the differential backup frequency In the policy configuration pane: Find Differential backup frequency. Change it from: 12 Hours to: 24 Hours Leave the PITR retention period unchanged unless the task specifically tells you to change it. Select Apply or Save. Microsoft’s documented option is exactly 12 Hours or 24 hours under Differential backup frequency. Method 2 — Azure CLI Method Use this if the simulation provides Cloud Shell. az sql db str-policy set \ --resource-group \ --server \ --name db1 \ --retention-days \ --diffbackup-hours 24 Example: az sql db str-policy set \ --resource-group RG1 \ --server sql60152867 \ --name db1 \ --retention-days 7 \ --diffbackup-hours 24 Microsoft documents az sql db str-policy set with --diffbackup-hours 24 for changing active database differential backup frequency. Valid values are 12 or 24 hours. Be careful: do not guess the retention days blindly in a real environment. In the exam lab, use the existing retention value shown in the portal unless the task also asks you to change retention. Method 3 — PowerShell Method Use this if Azure PowerShell is available. Set-AzSqlDatabaseBackupShortTermRetentionPolicy ` -ResourceGroupName "" ` -ServerName "" ` -DatabaseName "db1" ` -RetentionDays ` -DiffBackupIntervalInHours 24 Microsoft documents Set-AzSqlDatabaseBackupShortTermRetentionPolicy with - DiffBackupIntervalInHours 24 for setting Azure SQL Database differential backup frequency. SSMS / T-SQL Clarification For this task, SSMS is not the right tool to change the setting. There is no normal ALTER DATABASE T-SQL command in Azure SQL Database to change automated differential backup frequency. Microsoft documents this change through: Azure portal Azure CLI PowerShell REST API not SSMS/T-SQL. You may use SSMS only to confirm the database exists and is accessible, but the backup frequency setting must be changed from Azure management tools.
Explanation: Requirement: Configure differential backups for Azure SQL Database db1 to run once a day instead of twice a day. Correct setting: Change Differential backup frequency from 12 Hours to 24 Hours. Azure SQL Database supports differential backup frequency of either 12 hours or 24 hours. A 12-hour frequency means twice per day; a 24-hour frequency means once per day. Microsoft also notes that 24-hour differential backup frequency can increase restore time compared with 12-hour frequency. Method 1 — Azure Portal Method This is the best method for the simulation. Step 1: Open the Azure SQL logical server Sign in to the Azure portal. Search for SQL servers. Open the logical SQL server that hosts database db1. Do not start from SQL Server Management Studio for this task. The differential backup frequency is an Azure SQL backup policy setting, not a normal T-SQL database setting. Step 2: Open the Backups page In the SQL server left menu, select Backups. Select the Retention policies tab. Microsoft’s documented portal path is to go to the logical SQL server, select Backups, then select the Retention policies tab. Step 3: Select database db1 In the list of databases, locate db1. Select the checkbox next to db1. Select Configure policies from the action bar. Step 4: Change the differential backup frequency In the policy configuration pane: Find Differential backup frequency. Change it from: 12 Hours to: 24 Hours Leave the PITR retention period unchanged unless the task specifically tells you to change it. Select Apply or Save. Microsoft’s documented option is exactly 12 Hours or 24 hours under Differential backup frequency. Method 2 — Azure CLI Method Use this if the simulation provides Cloud Shell. az sql db str-policy set \ --resource-group \ --server \ --name db1 \ --retention-days \ --diffbackup-hours 24 Example: az sql db str-policy set \ --resource-group RG1 \ --server sql60152867 \ --name db1 \ --retention-days 7 \ --diffbackup-hours 24 Microsoft documents az sql db str-policy set with --diffbackup-hours 24 for changing active database differential backup frequency. Valid values are 12 or 24 hours. Be careful: do not guess the retention days blindly in a real environment. In the exam lab, use the existing retention value shown in the portal unless the task also asks you to change retention. Method 3 — PowerShell Method Use this if Azure PowerShell is available. Set-AzSqlDatabaseBackupShortTermRetentionPolicy ` -ResourceGroupName "" ` -ServerName "" ` -DatabaseName "db1" ` -RetentionDays ` -DiffBackupIntervalInHours 24 Microsoft documents Set-AzSqlDatabaseBackupShortTermRetentionPolicy with - DiffBackupIntervalInHours 24 for setting Azure SQL Database differential backup frequency. SSMS / T-SQL Clarification For this task, SSMS is not the right tool to change the setting. There is no normal ALTER DATABASE T-SQL command in Azure SQL Database to change automated differential backup frequency. Microsoft documents this change through: Azure portal Azure CLI PowerShell REST API not SSMS/T-SQL. You may use SSMS only to confirm the database exists and is accessible, but the backup frequency setting must be changed from Azure management tools.
Question # 6
Task 1You need to implement a disaster recovery solution by using active geo replication for an Azure Azure SQL database named db1. The replica must be in the East US or East US 2 Azure region on a server named sql60152867-dr.database.windows.net. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Requirement: Configure active geo-replication for Azure SQL Database db1. The georeplica must be created in East US or East US 2 on the logical Azure SQL server: sql60152867-dr.database.windows.net The important point: in Azure SQL, the logical server name used in portal/T-SQL is usually: sql60152867-dr not the full FQDN. Microsoft states that active geo-replication is configured per database, and a geo secondary is created for an existing Azure SQL Database. After creation and seeding, changes from the primary are replicated asynchronously to the secondary. Method 1 — Azure Portal Method This is the safest method for the simulation if the portal is available. Step 1: Open the primary database Sign in to the Azure portal. Search for SQL databases. Select the database named db1. Confirm you are looking at the primary database, not an existing secondary. Step 2: Open the Replicas blade In the left menu of the db1 database page, scroll to Data management. Select Replicas. Select Create replica. Microsoft’s portal workflow is: open the database, go to Data management > Replicas, and choose Create replica. Step 3: Configure the geo-secondary replica On the Create SQL Database replica page, configure the target like this: Setting Value Database db1 Replica type Geo replica / Active geo-replication Target server sql60152867-dr Region East US or East US 2 Database name db1 Compute + storage Keep same or compatible with primary Elastic pool Only choose this if the lab specifically requires an elastic pool Do not create a failover group unless the task asks for one. This task says active geo replication, so configure a database-level geo-replica, not a failover group. Microsoft explicitly separates active geo-replication from failover groups and notes that active georeplication is configured per database. Step 4: Review and create Select Review + create. Confirm the target server is: sql60152867-dr Confirm the region is either: East US or East US 2 Select Create. Azure will create the secondary database and begin the seeding process. Microsoft notes that the secondary database has the same name as the primary by default and begins replication after it is created and seeded. Step 5: Verify replication After deployment completes: Go back to the primary database db1. Open Replicas again. Under Geo replicas, confirm that a replica exists on: sql60152867-dr.database.windows.net Confirm the replica status is healthy, online, or synchronizing. You can also open the target SQL server sql60152867-dr and verify that a database named db1 now exists there. Method 2 — SSMS / T-SQL Method Use this method if the portal is awkward or the exam simulation expects T-SQL. Step 1: Allow SSMS connectivity Before connecting with SSMS: In Azure portal, open the primary SQL server hosting db1. Go to Networking or Firewalls and virtual networks. Add your client IP address. Repeat this on the secondary server: sql60152867-dr.database.windows.net This matters because SSMS must be able to connect to the Azure SQL logical server. Step 2: Connect to the primary server in SSMS Open SQL Server Management Studio. Connect to the primary Azure SQL logical server that hosts db1. Use SQL admin credentials or Microsoft Entra admin credentials. In Connection Properties, connect to the database: master This is important. For Azure SQL Database geo-replication setup through T-SQL, run the command from the master database on the primary server. Step 3: Run the active geo-replication command Run this query: ALTER DATABASE [db1] ADD SECONDARY ON SERVER [sql60152867-dr] WITH (ALLOW_CONNECTIONS = ALL); Microsoft documents that ALTER DATABASE ... ADD SECONDARY ON SERVER creates a secondary database for an existing Azure SQL Database and starts data replication. The official example also uses WITH (ALLOW_CONNECTIONS = ALL) to create a readable geo-secondary. Step 4: Verify the replication link Still connected to the primary server, run: SELECT * FROM sys.geo_replication_links; Or use: SELECT * FROM sys.dm_geo_replication_link_status; Microsoft lists sys.geo_replication_links and sys.dm_geo_replication_link_status as views used to return information about existing replication links and replication status. Optional PowerShell Method Only use this if the lab gives you Cloud Shell or PowerShell access. New-AzSqlDatabaseSecondary ` -ResourceGroupName "" ` -ServerName "" ` -DatabaseName "db1" ` -PartnerResourceGroupName "" ` -PartnerServerName "sql60152867-dr" ` -PartnerDatabaseName "db1" Microsoft identifies New-AzSqlDatabaseSecondary as the PowerShell cmdlet that creates a secondary database for an existing Azure SQL Database and starts replication. Final Answer / What You Must Achieve The task is complete when: Database db1 still exists on the primary server. A secondary database named db1 exists on: sql60152867-dr.database.windows.net The target server is in East US or East US 2. The replica appears under db1 > Replicas > Geo replicas. Replication status is healthy, online, seeding, or synchronizing. You did not configure a failover group unless separately requested.
Explanation: Requirement: Configure active geo-replication for Azure SQL Database db1. The georeplica must be created in East US or East US 2 on the logical Azure SQL server: sql60152867-dr.database.windows.net The important point: in Azure SQL, the logical server name used in portal/T-SQL is usually: sql60152867-dr not the full FQDN. Microsoft states that active geo-replication is configured per database, and a geo secondary is created for an existing Azure SQL Database. After creation and seeding, changes from the primary are replicated asynchronously to the secondary. Method 1 — Azure Portal Method This is the safest method for the simulation if the portal is available. Step 1: Open the primary database Sign in to the Azure portal. Search for SQL databases. Select the database named db1. Confirm you are looking at the primary database, not an existing secondary. Step 2: Open the Replicas blade In the left menu of the db1 database page, scroll to Data management. Select Replicas. Select Create replica. Microsoft’s portal workflow is: open the database, go to Data management > Replicas, and choose Create replica. Step 3: Configure the geo-secondary replica On the Create SQL Database replica page, configure the target like this: Setting Value Database db1 Replica type Geo replica / Active geo-replication Target server sql60152867-dr Region East US or East US 2 Database name db1 Compute + storage Keep same or compatible with primary Elastic pool Only choose this if the lab specifically requires an elastic pool Do not create a failover group unless the task asks for one. This task says active geo replication, so configure a database-level geo-replica, not a failover group. Microsoft explicitly separates active geo-replication from failover groups and notes that active georeplication is configured per database. Step 4: Review and create Select Review + create. Confirm the target server is: sql60152867-dr Confirm the region is either: East US or East US 2 Select Create. Azure will create the secondary database and begin the seeding process. Microsoft notes that the secondary database has the same name as the primary by default and begins replication after it is created and seeded. Step 5: Verify replication After deployment completes: Go back to the primary database db1. Open Replicas again. Under Geo replicas, confirm that a replica exists on: sql60152867-dr.database.windows.net Confirm the replica status is healthy, online, or synchronizing. You can also open the target SQL server sql60152867-dr and verify that a database named db1 now exists there. Method 2 — SSMS / T-SQL Method Use this method if the portal is awkward or the exam simulation expects T-SQL. Step 1: Allow SSMS connectivity Before connecting with SSMS: In Azure portal, open the primary SQL server hosting db1. Go to Networking or Firewalls and virtual networks. Add your client IP address. Repeat this on the secondary server: sql60152867-dr.database.windows.net This matters because SSMS must be able to connect to the Azure SQL logical server. Step 2: Connect to the primary server in SSMS Open SQL Server Management Studio. Connect to the primary Azure SQL logical server that hosts db1. Use SQL admin credentials or Microsoft Entra admin credentials. In Connection Properties, connect to the database: master This is important. For Azure SQL Database geo-replication setup through T-SQL, run the command from the master database on the primary server. Step 3: Run the active geo-replication command Run this query: ALTER DATABASE [db1] ADD SECONDARY ON SERVER [sql60152867-dr] WITH (ALLOW_CONNECTIONS = ALL); Microsoft documents that ALTER DATABASE ... ADD SECONDARY ON SERVER creates a secondary database for an existing Azure SQL Database and starts data replication. The official example also uses WITH (ALLOW_CONNECTIONS = ALL) to create a readable geo-secondary. Step 4: Verify the replication link Still connected to the primary server, run: SELECT * FROM sys.geo_replication_links; Or use: SELECT * FROM sys.dm_geo_replication_link_status; Microsoft lists sys.geo_replication_links and sys.dm_geo_replication_link_status as views used to return information about existing replication links and replication status. Optional PowerShell Method Only use this if the lab gives you Cloud Shell or PowerShell access. New-AzSqlDatabaseSecondary ` -ResourceGroupName "" ` -ServerName "" ` -DatabaseName "db1" ` -PartnerResourceGroupName "" ` -PartnerServerName "sql60152867-dr" ` -PartnerDatabaseName "db1" Microsoft identifies New-AzSqlDatabaseSecondary as the PowerShell cmdlet that creates a secondary database for an existing Azure SQL Database and starts replication. Final Answer / What You Must Achieve The task is complete when: Database db1 still exists on the primary server. A secondary database named db1 exists on: sql60152867-dr.database.windows.net The target server is in East US or East US 2. The replica appears under db1 > Replicas > Geo replicas. Replication status is healthy, online, seeding, or synchronizing. You did not configure a failover group unless separately requested.
Question # 7
Task 4You need to encrypt the LastName column of the SalesLT.Customer table in db1 by using Always Encrypted. You must use the Windows Certificate Store. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Use SQL Server Management Studio Always Encrypted Wizard and encrypt: Database: db1 Schema: SalesLT Table: Customer Column: LastName Encryption feature: Always Encrypted Column master key store: Windows Certificate Store Do not use Transparent Data Encryption. TDE encrypts the database at rest, but this task specifically requires Always Encrypted, which protects selected columns and keeps the encryption keys outside the database engine. Microsoft states that Always Encrypted uses column encryption keys to encrypt column data and column master keys to protect those column encryption keys. Column master keys are stored outside the database system, such as in the Windows certificate store. Method 1 — SSMS Always Encrypted Wizard This is the correct method for the simulation. Step 1: Open SSMS and connect to Azure SQL Database Open SQL Server Management Studio. Connect to the Azure SQL logical server that hosts db1. Use a SQL admin account or Microsoft Entra admin account. In Options > Connection Properties, select database: db1 Connect. If SSMS cannot connect, go to the Azure portal and add your client IP address under the SQL server firewall/networking settings. Step 2: Open the table column In Object Explorer: Expand Databases. Expand db1. Expand Tables. Expand: SalesLT.Customer Expand Columns. Locate: LastName Microsoft confirms that the Always Encrypted Wizard can be launched at the database, table, or individual column level. For one column, the cleanest path is to launch it directly from the column. Step 3: Launch the Always Encrypted Wizard Right-click the LastName column, then select: Encrypt Column or, depending on the SSMS version: Always Encrypted Wizard Alternative path: Right-click db1 > Tasks > Always Encrypted Wizard Then manually select: SalesLT.Customer.LastName Step 4: Select the LastName column for encryption On the Column Selection page: Find: SalesLT.Customer.LastName Select the checkbox for LastName. Set the encryption type. Use: Randomized unless the lab specifically requires searching or equality filtering on LastName. Reason: Randomized encryption is stronger because identical plaintext values produce different ciphertext values. Deterministic encryption allows equality lookups, joins, grouping, and indexing, but leaks more pattern information because identical plaintext values produce identical encrypted values. Microsoft describes deterministic encryption as query-friendly but more pattern-revealing, while randomized encryption is more secure but does not support normal searching/grouping/joining without secure enclaves. For this task, the requirement is only to encrypt the LastName column, so Randomized is the safer default. Step 5: Choose or create a Column Encryption Key For Encryption Key, select a new key such as: CEK_Auto1 or create a new column encryption key if one does not already exist. This is the key that encrypts the data in the LastName column. Microsoft states that a column encryption key encrypts the data in encrypted columns, and the column master key encrypts/protects the column encryption key. Select Next. Step 6: Configure the Column Master Key in Windows Certificate Store On the key configuration page, create a new Column Master Key. Use settings like: Setting Value Column master key name CMK_WindowsCert or default generated name Key store Windows Certificate Store Certificate location Current User Certificate Generate new certificate Column encryption key CEK_Auto1 or default generated CEK In many SSMS versions, the wizard creates both: CMK_Auto1 CEK_Auto1 That is acceptable as long as the CMK key store is Windows Certificate Store. Microsoft states that SQL Server Management Studio supports column master keys stored in the Windows Certificate Store, and that a column master key can be a certificate stored in Windows Certificate Store. Step 7: Run the wizard On the final wizard pages: Review the configuration. Choose: Proceed to finish now or: Run immediately Select Finish. SSMS will generate the column master key metadata, column encryption key metadata, and perform the data encryption operation. Microsoft explains that the wizard can encrypt selected columns and can generate a new column master key and column encryption key when needed. During encryption, SSMS may temporarily create a new table, copy data, encrypt the selected column, and swap the table back, depending on whether secure enclaves are used. Microsoft notes that the wizard can move data out of the database and perform cryptographic operations inside the SSMS process when secure enclave in-place encryption is not used. Verification Steps Step 1: Confirm Always Encrypted metadata exists Run this in db1: SELECT name, key_store_provider_name, key_path FROM sys.column_master_keys; You should see a column master key that uses the Windows certificate store provider. Then run: SELECT name FROM sys.column_encryption_keys; You should see the column encryption key created by the wizard. Step 2: Confirm LastName is encrypted Run: SELECT t.name AS table_name, c.name AS column_name, c.encryption_type_desc, cek.name AS column_encryption_key FROM sys.columns AS c JOIN sys.tables AS t ON c.object_id = t.object_id LEFT JOIN sys.column_encryption_keys AS cek ON c.column_encryption_key_id = cek.column_encryption_key_id WHERE t.name = 'Customer' AND SCHEMA_NAME(t.schema_id) = 'SalesLT' AND c.name = 'LastName'; Expected result: table_name: Customer column_name: LastName encryption_type_desc: RANDOMIZED column_encryption_key: CEK_Auto1 or similar If you selected deterministic encryption, the expected value will be: DETERMINISTIC The key requirement is that encryption_type_desc is no longer NULL. Step 3: Test viewing the encrypted column Open a new SSMS query connection without Always Encrypted enabled and run: SELECT TOP 10 LastName FROM SalesLT.Customer; You should not see normal plaintext values. Then reconnect with Always Encrypted enabled: In SSMS, select Connect > Database Engine. Select Options. Go to Additional Connection Parameters. Add: Column Encryption Setting=Enabled Connect again. Run: SELECT TOP 10 LastName FROM SalesLT.Customer; A client that has access to the Windows certificate/private key should be able to decrypt the values. Microsoft explains that the database stores encrypted data and key metadata, while client-side components with access to the column master key perform encryption and decryption. Important Exam Notes Do not choose Azure Key Vault The task explicitly says: You must use the Windows Certificate Store. So the column master key should not be stored in Azure Key Vault. Wrong: Azure Key Vault Correct: Windows Certificate Store Do not use TDE TDE is not column-level Always Encrypted. It encrypts database files/logs at rest, but users and administrators querying the database still see plaintext if they have SQL permissions. Correct technology: Always Encrypted Correct key store: Windows Certificate Store Correct target column: SalesLT.Customer.LastName
Explanation: Use SQL Server Management Studio Always Encrypted Wizard and encrypt: Database: db1 Schema: SalesLT Table: Customer Column: LastName Encryption feature: Always Encrypted Column master key store: Windows Certificate Store Do not use Transparent Data Encryption. TDE encrypts the database at rest, but this task specifically requires Always Encrypted, which protects selected columns and keeps the encryption keys outside the database engine. Microsoft states that Always Encrypted uses column encryption keys to encrypt column data and column master keys to protect those column encryption keys. Column master keys are stored outside the database system, such as in the Windows certificate store. Method 1 — SSMS Always Encrypted Wizard This is the correct method for the simulation. Step 1: Open SSMS and connect to Azure SQL Database Open SQL Server Management Studio. Connect to the Azure SQL logical server that hosts db1. Use a SQL admin account or Microsoft Entra admin account. In Options > Connection Properties, select database: db1 Connect. If SSMS cannot connect, go to the Azure portal and add your client IP address under the SQL server firewall/networking settings. Step 2: Open the table column In Object Explorer: Expand Databases. Expand db1. Expand Tables. Expand: SalesLT.Customer Expand Columns. Locate: LastName Microsoft confirms that the Always Encrypted Wizard can be launched at the database, table, or individual column level. For one column, the cleanest path is to launch it directly from the column. Step 3: Launch the Always Encrypted Wizard Right-click the LastName column, then select: Encrypt Column or, depending on the SSMS version: Always Encrypted Wizard Alternative path: Right-click db1 > Tasks > Always Encrypted Wizard Then manually select: SalesLT.Customer.LastName Step 4: Select the LastName column for encryption On the Column Selection page: Find: SalesLT.Customer.LastName Select the checkbox for LastName. Set the encryption type. Use: Randomized unless the lab specifically requires searching or equality filtering on LastName. Reason: Randomized encryption is stronger because identical plaintext values produce different ciphertext values. Deterministic encryption allows equality lookups, joins, grouping, and indexing, but leaks more pattern information because identical plaintext values produce identical encrypted values. Microsoft describes deterministic encryption as query-friendly but more pattern-revealing, while randomized encryption is more secure but does not support normal searching/grouping/joining without secure enclaves. For this task, the requirement is only to encrypt the LastName column, so Randomized is the safer default. Step 5: Choose or create a Column Encryption Key For Encryption Key, select a new key such as: CEK_Auto1 or create a new column encryption key if one does not already exist. This is the key that encrypts the data in the LastName column. Microsoft states that a column encryption key encrypts the data in encrypted columns, and the column master key encrypts/protects the column encryption key. Select Next. Step 6: Configure the Column Master Key in Windows Certificate Store On the key configuration page, create a new Column Master Key. Use settings like: Setting Value Column master key name CMK_WindowsCert or default generated name Key store Windows Certificate Store Certificate location Current User Certificate Generate new certificate Column encryption key CEK_Auto1 or default generated CEK In many SSMS versions, the wizard creates both: CMK_Auto1 CEK_Auto1 That is acceptable as long as the CMK key store is Windows Certificate Store. Microsoft states that SQL Server Management Studio supports column master keys stored in the Windows Certificate Store, and that a column master key can be a certificate stored in Windows Certificate Store. Step 7: Run the wizard On the final wizard pages: Review the configuration. Choose: Proceed to finish now or: Run immediately Select Finish. SSMS will generate the column master key metadata, column encryption key metadata, and perform the data encryption operation. Microsoft explains that the wizard can encrypt selected columns and can generate a new column master key and column encryption key when needed. During encryption, SSMS may temporarily create a new table, copy data, encrypt the selected column, and swap the table back, depending on whether secure enclaves are used. Microsoft notes that the wizard can move data out of the database and perform cryptographic operations inside the SSMS process when secure enclave in-place encryption is not used. Verification Steps Step 1: Confirm Always Encrypted metadata exists Run this in db1: SELECT name, key_store_provider_name, key_path FROM sys.column_master_keys; You should see a column master key that uses the Windows certificate store provider. Then run: SELECT name FROM sys.column_encryption_keys; You should see the column encryption key created by the wizard. Step 2: Confirm LastName is encrypted Run: SELECT t.name AS table_name, c.name AS column_name, c.encryption_type_desc, cek.name AS column_encryption_key FROM sys.columns AS c JOIN sys.tables AS t ON c.object_id = t.object_id LEFT JOIN sys.column_encryption_keys AS cek ON c.column_encryption_key_id = cek.column_encryption_key_id WHERE t.name = 'Customer' AND SCHEMA_NAME(t.schema_id) = 'SalesLT' AND c.name = 'LastName'; Expected result: table_name: Customer column_name: LastName encryption_type_desc: RANDOMIZED column_encryption_key: CEK_Auto1 or similar If you selected deterministic encryption, the expected value will be: DETERMINISTIC The key requirement is that encryption_type_desc is no longer NULL. Step 3: Test viewing the encrypted column Open a new SSMS query connection without Always Encrypted enabled and run: SELECT TOP 10 LastName FROM SalesLT.Customer; You should not see normal plaintext values. Then reconnect with Always Encrypted enabled: In SSMS, select Connect > Database Engine. Select Options. Go to Additional Connection Parameters. Add: Column Encryption Setting=Enabled Connect again. Run: SELECT TOP 10 LastName FROM SalesLT.Customer; A client that has access to the Windows certificate/private key should be able to decrypt the values. Microsoft explains that the database stores encrypted data and key metadata, while client-side components with access to the column master key perform encryption and decryption. Important Exam Notes Do not choose Azure Key Vault The task explicitly says: You must use the Windows Certificate Store. So the column master key should not be stored in Azure Key Vault. Wrong: Azure Key Vault Correct: Windows Certificate Store Do not use TDE TDE is not column-level Always Encrypted. It encrypts database files/logs at rest, but users and administrators querying the database still see plaintext if they have SQL permissions. Correct technology: Always Encrypted Correct key store: Windows Certificate Store Correct target column: SalesLT.Customer.LastName
Question # 8
Task 8You need to increase the maximum storage size of the query store on db1 to 4,000 MB You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Connect to db1 in SQL Server Management Studio and run: ALTER DATABASE [db1] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000); Microsoft documents that Query Store maximum storage size is changed with: ALTER DATABASE SET QUERY_STORE (MAX_STORAGE_SIZE_MB = ); For Azure SQL Database, the maximum allowed MAX_STORAGE_SIZE_MB value is 10,240 MB, so 4,000 MB is valid. Method 1 — SSMS / T-SQL Method This is the cleanest method for the simulation. Step 1: Open SQL Server Management Studio Open SQL Server Management Studio. Connect to the Azure SQL logical server that hosts db1. Use SQL authentication or Microsoft Entra authentication. In Connection Properties, select: db1 You can also connect to master, but for this task it is cleaner to open a query window in db1. Step 2: Run the Query Store configuration command Open a new query window and run: ALTER DATABASE [db1] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000); This changes the Query Store maximum allocated storage size to 4,000 MB. Step 3: Verify the setting Run this query in db1: SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options; Expected result: max_storage_size_mb = 4000 Microsoft documents sys.database_query_store_options as the catalog view that returns Query Store configuration, including max_storage_size_mb. Method 2 — SSMS Graphical Method Use this if the simulation expects a GUI action. Step 1: Connect to the server Open SQL Server Management Studio. Connect to the Azure SQL logical server. Expand Databases. Right-click db1. Select Properties. Step 2: Open Query Store settings In the Database Properties window, select Query Store. Find the setting: Max Size (MB) or: Maximum Storage Size (MB) Change the value to: 4000 Select OK. Step 3: Verify Run: SELECT max_storage_size_mb FROM sys.database_query_store_options; Expected value: 4000 Azure Portal Clarification The Azure portal is not the best tool for this specific Query Store configuration. Query Store size is a database engine setting, not a normal Azure resource setting like backup retention, locks, alerts, or private endpoints. Use SSMS/T-SQL for this task. Important Notes Query Store must remain enabled Do not disable Query Store. Azure SQL Database uses Query Store heavily, and Microsoft notes that Query Store is enabled by default for Azure SQL Database. The task only asks to increase the maximum storage size, not to change capture mode, cleanup policy, or operation mode. 4,000 MB is valid Azure SQL Database allows Query Store MAX_STORAGE_SIZE_MB up to 10,240 MB, so this setting is within the allowed range. Final Exam-Lab Action Run this against db1: ALTER DATABASE [db1] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000); Then verify with: SELECT max_storage_size_mb FROM sys.database_query_store_options; The task is complete when the result shows: 4000
Explanation: Connect to db1 in SQL Server Management Studio and run: ALTER DATABASE [db1] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000); Microsoft documents that Query Store maximum storage size is changed with: ALTER DATABASE SET QUERY_STORE (MAX_STORAGE_SIZE_MB = ); For Azure SQL Database, the maximum allowed MAX_STORAGE_SIZE_MB value is 10,240 MB, so 4,000 MB is valid. Method 1 — SSMS / T-SQL Method This is the cleanest method for the simulation. Step 1: Open SQL Server Management Studio Open SQL Server Management Studio. Connect to the Azure SQL logical server that hosts db1. Use SQL authentication or Microsoft Entra authentication. In Connection Properties, select: db1 You can also connect to master, but for this task it is cleaner to open a query window in db1. Step 2: Run the Query Store configuration command Open a new query window and run: ALTER DATABASE [db1] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000); This changes the Query Store maximum allocated storage size to 4,000 MB. Step 3: Verify the setting Run this query in db1: SELECT actual_state_desc, desired_state_desc, current_storage_size_mb, max_storage_size_mb FROM sys.database_query_store_options; Expected result: max_storage_size_mb = 4000 Microsoft documents sys.database_query_store_options as the catalog view that returns Query Store configuration, including max_storage_size_mb. Method 2 — SSMS Graphical Method Use this if the simulation expects a GUI action. Step 1: Connect to the server Open SQL Server Management Studio. Connect to the Azure SQL logical server. Expand Databases. Right-click db1. Select Properties. Step 2: Open Query Store settings In the Database Properties window, select Query Store. Find the setting: Max Size (MB) or: Maximum Storage Size (MB) Change the value to: 4000 Select OK. Step 3: Verify Run: SELECT max_storage_size_mb FROM sys.database_query_store_options; Expected value: 4000 Azure Portal Clarification The Azure portal is not the best tool for this specific Query Store configuration. Query Store size is a database engine setting, not a normal Azure resource setting like backup retention, locks, alerts, or private endpoints. Use SSMS/T-SQL for this task. Important Notes Query Store must remain enabled Do not disable Query Store. Azure SQL Database uses Query Store heavily, and Microsoft notes that Query Store is enabled by default for Azure SQL Database. The task only asks to increase the maximum storage size, not to change capture mode, cleanup policy, or operation mode. 4,000 MB is valid Azure SQL Database allows Query Store MAX_STORAGE_SIZE_MB up to 10,240 MB, so this setting is within the allowed range. Final Exam-Lab Action Run this against db1: ALTER DATABASE [db1] SET QUERY_STORE (MAX_STORAGE_SIZE_MB = 4000); Then verify with: SELECT max_storage_size_mb FROM sys.database_query_store_options; The task is complete when the result shows: 4000
Question # 9
Task 10You need to ensure that indexes are created automatically for all the databases on sql60152867. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Enable Automatic tuning at the server level and turn Create Index to On. Microsoft states that Azure SQL Database automatic tuning can create indexes automatically, verify performance improvement, and roll back changes if performance regresses. Server-level automatic tuning settings are applied to all databases on the server by default, unless a database has its own override. Azure Portal Method — Recommended for Simulation Step 1: Open the SQL logical server Sign in to the Azure portal. Search for SQL servers. Open: sql60152867 Do not open only db1. The task says all databases on sql60152867, so configure this at the server level. Step 2: Open Automatic tuning In the SQL server left menu: Go to Intelligent Performance. Select Automatic tuning. Microsoft’s documented path is to open the Azure SQL server in the portal and select Automatic tuning from the menu. Step 3: Enable automatic index creation On the Automatic tuning page, configure: Setting Value Create index On Drop index Leave unchanged unless required Force last good plan Leave unchanged unless required The only required setting is: Create index = On Do not confuse this with Drop index. The task only asks to ensure indexes are created automatically. Step 4: Apply the setting Select Apply or Save. Wait for the portal confirmation. The setting is now configured at the SQL logical server level. Important Database Inheritance Check This is the part people miss. Server-level automatic tuning applies to databases that inherit from the server. Microsoft states that individual databases can override server-level automatic tuning settings. Therefore, if the simulation shows any database with custom automatic tuning settings, set that database to Inherit from server or manually set Create index = On for that database. For each database, the correct inherited state should be: Automatic tuning = Inherit from server Create index = Inherited / On If a database is set to: Custom Create index = Off then the server-level setting might not affect that database. Fix it. Database-Level T-SQL Method Use this only if the portal is unavailable or you need to correct a specific database override. Connect to each database and run: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING ( CREATE_INDEX = ON ); Microsoft documents this exact syntax for enabling the CREATE_INDEX automatic tuning option by T-SQL on an individual Azure SQL Database. To make an individual database inherit server settings, run: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING = INHERIT; But this must be executed inside each database, not from master as a single server-wide T-SQL command. Verification with T-SQL Connect to a database and run: SELECT name, desired_state_desc, actual_state_desc FROM sys.database_automatic_tuning_options WHERE name = 'CREATE_INDEX'; Expected result: name CREATE_INDEX desired_state_desc ON or DEFAULT actual_state_desc ON If desired_state_desc is DEFAULT, that usually means the database is inheriting the server-level setting. The key result is: actual_state_desc = ON SSMS Clarification SSMS can be used to verify or configure the setting per database using T-SQL, but it is not the best tool for the requirement as written. Because the task says: all the databases on sql60152867 the correct primary method is: Azure portal > SQL server > Automatic tuning > Create index = On Final Exam-Lab Action Configure this: SQL server: sql60152867 Automatic tuning Create index: On Apply Then verify databases are inheriting the server setting or have CREATE_INDEX = ON. That completes the task.
Explanation: Enable Automatic tuning at the server level and turn Create Index to On. Microsoft states that Azure SQL Database automatic tuning can create indexes automatically, verify performance improvement, and roll back changes if performance regresses. Server-level automatic tuning settings are applied to all databases on the server by default, unless a database has its own override. Azure Portal Method — Recommended for Simulation Step 1: Open the SQL logical server Sign in to the Azure portal. Search for SQL servers. Open: sql60152867 Do not open only db1. The task says all databases on sql60152867, so configure this at the server level. Step 2: Open Automatic tuning In the SQL server left menu: Go to Intelligent Performance. Select Automatic tuning. Microsoft’s documented path is to open the Azure SQL server in the portal and select Automatic tuning from the menu. Step 3: Enable automatic index creation On the Automatic tuning page, configure: Setting Value Create index On Drop index Leave unchanged unless required Force last good plan Leave unchanged unless required The only required setting is: Create index = On Do not confuse this with Drop index. The task only asks to ensure indexes are created automatically. Step 4: Apply the setting Select Apply or Save. Wait for the portal confirmation. The setting is now configured at the SQL logical server level. Important Database Inheritance Check This is the part people miss. Server-level automatic tuning applies to databases that inherit from the server. Microsoft states that individual databases can override server-level automatic tuning settings. Therefore, if the simulation shows any database with custom automatic tuning settings, set that database to Inherit from server or manually set Create index = On for that database. For each database, the correct inherited state should be: Automatic tuning = Inherit from server Create index = Inherited / On If a database is set to: Custom Create index = Off then the server-level setting might not affect that database. Fix it. Database-Level T-SQL Method Use this only if the portal is unavailable or you need to correct a specific database override. Connect to each database and run: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING ( CREATE_INDEX = ON ); Microsoft documents this exact syntax for enabling the CREATE_INDEX automatic tuning option by T-SQL on an individual Azure SQL Database. To make an individual database inherit server settings, run: ALTER DATABASE CURRENT SET AUTOMATIC_TUNING = INHERIT; But this must be executed inside each database, not from master as a single server-wide T-SQL command. Verification with T-SQL Connect to a database and run: SELECT name, desired_state_desc, actual_state_desc FROM sys.database_automatic_tuning_options WHERE name = 'CREATE_INDEX'; Expected result: name CREATE_INDEX desired_state_desc ON or DEFAULT actual_state_desc ON If desired_state_desc is DEFAULT, that usually means the database is inheriting the server-level setting. The key result is: actual_state_desc = ON SSMS Clarification SSMS can be used to verify or configure the setting per database using T-SQL, but it is not the best tool for the requirement as written. Because the task says: all the databases on sql60152867 the correct primary method is: Azure portal > SQL server > Automatic tuning > Create index = On Final Exam-Lab Action Configure this: SQL server: sql60152867 Automatic tuning Create index: On Apply Then verify databases are inheriting the server setting or have CREATE_INDEX = ON. That completes the task.
Question # 10
Task 5 You need to generate an email alert for db1 if the average CPU percentage utilization is greater than 50 percent for five minutes sampled at one-minute intervals. The alert must be sent to [email protected]. You may need to use SQL Server Management Studio and the Azure portal.
Answer: See the explanation part for the complete Solution.
Explanation: Create an Azure Monitor metric alert rule on database db1 using the metric: CPU percentage Configure the condition as: Aggregation: Average Operator: Greater than Threshold: 50 Aggregation granularity / Period: 1 minute Frequency of evaluation: 1 minute Evaluation period / Lookback window: 5 minutes Action group email: [email protected] Azure SQL Database exposes CPU percentage as a platform metric, and Azure Monitor metric alerts can send notifications through action groups such as email. Microsoft describes SQL Database alerts as metric-based alerts that can send email when metrics such as CPU usage reach a defined threshold. Azure Portal Method — Recommended for Simulation Step 1: Open the db1 Azure SQL database Sign in to the Azure portal. Search for SQL databases. Open the database named: db1 Do not open the SQL logical server unless the alert needs to apply to all databases. This task is specifically for db1, so the alert scope must be the db1 database resource. Step 2: Create a new alert rule From the db1 database page: In the left menu, select Alerts. Select Create. Select Alert rule. Microsoft’s Azure Monitor workflow allows you to create an alert rule directly from the target resource. When you create it from a resource, the resource is automatically set as the alert scope. Step 3: Confirm the alert scope On the alert rule page, confirm the scope is the Azure SQL database: db1 The resource type should be similar to: SQL database Microsoft.Sql/servers/databases If the scope is the SQL server instead of the database, remove it and select the db1 database resource. Step 4: Add the alert condition Under Condition, select Add condition. Choose the metric: CPU percentage Sometimes it appears as: CPU percent or metric name: cpu_percent For Azure SQL Database, CPU percentage represents CPU consumption toward the database workload limit, expressed as a percentage. Step 5: Configure the signal logic Configure the alert logic exactly like this: Setting Value Threshold type Static Aggregation type Average Operator Greater than Threshold value 50 Unit Percent Aggregation granularity / Period 1 minute Frequency of evaluation 1 minute Evaluation period / Lookback window 5 minutes This means Azure Monitor evaluates the CPU metric every minute, using one-minute metric samples, and fires the alert only when the average CPU percentage is greater than 50% across the five-minute evaluation window. Be precise here. The task says: average CPU percentage utilization is greater than 50 percent for five minutes sampled at one-minute intervals So the correct choices are: Average Greater than 50 Every 1 minute Over the last 5 minutes Azure Monitor metric alert rules combine the monitored resource, the metric condition, and action groups that run when the condition is met. Step 6: Create the email action group Under Actions, select: Create action group Configure the basics: Setting Value Subscription Use the current subscription Resource group Use the lab resource group Action group name AG-db1-CPU-Email Display name db1CPU Then go to Notifications. Add a notification: Setting Value Notification type Email/SMS message/Push/Voice Name EmailAdmin Email [email protected] Select OK, then Review + create, then Create. Action groups define the notification or automation action that runs when an alert fires. Microsoft documents email as a supported action group notification type. Step 7: Configure alert rule details Under Details, configure: Setting Value Severity 2 or 3 Alert rule name db1 CPU greater than 50 percent Description Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes. Enable upon creation Yes Severity is usually not specified by the task, so any reasonable severity is acceptable. In an exam lab, I would use Severity 2 for CPU performance impact. Step 8: Review and create Select Review + create. Confirm the condition says something equivalent to: Whenever the average CPU percentage is greater than 50 Confirm the evaluation settings are: Check every 1 minute Lookback period 5 minutes Confirm the action group sends email to: [email protected] Select Create. Verification After creation: Open db1. Go to Alerts. Select Alert rules. Confirm the alert rule exists and is enabled. Open the rule and verify: Scope: db1 Signal: CPU percentage Aggregation: Average Operator: Greater than Threshold: 50 Evaluation frequency: 1 minute Window size: 5 minutes Action group: [email protected] Azure CLI Method Use this only if the simulation gives you Cloud Shell. First get the database resource ID: az sql db show \ --resource-group \ --server \ --name db1 \ --query id \ --output tsv Create the action group: az monitor action-group create \ --resource-group \ --name AG-db1-CPU-Email \ --short-name db1CPU \ --action email EmailAdmin [email protected] Create the metric alert: az monitor metrics alert create \ --name "db1 CPU greater than 50 percent" \ --resource-group \ --scopes \ --condition "avg cpu_percent > 50" \ --window-size 5m \ --evaluation-frequency 1m \ --action AG-db1-CPU-Email \ --description "Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes." The metric name commonly used for Azure SQL Database CPU percentage in CLI/ARM contexts is: cpu_percent SSMS Clarification SSMS is not the correct tool for this task. Do not configure Database Mail. Azure SQL Database does not use SQL Server Agent/Database Mail in the same way as SQL Server on a VM or SQL Managed Instance. This requirement is an Azure Monitor metric alert requirement. Correct tool: Azure portal > db1 > Alerts > Create alert rule Wrong tool: SSMS Database Mail Final Exam-Lab Configuration Use this exact configuration: Resource: db1 Alert type: Metric alert Metric: CPU percentage Aggregation: Average Operator: Greater than Threshold: 50 Aggregation granularity: 1 minute Evaluation frequency: 1 minute Evaluation period/window: 5 minutes Action group notification: Email Email recipient: [email protected] That completes the task
Explanation: Create an Azure Monitor metric alert rule on database db1 using the metric: CPU percentage Configure the condition as: Aggregation: Average Operator: Greater than Threshold: 50 Aggregation granularity / Period: 1 minute Frequency of evaluation: 1 minute Evaluation period / Lookback window: 5 minutes Action group email: [email protected] Azure SQL Database exposes CPU percentage as a platform metric, and Azure Monitor metric alerts can send notifications through action groups such as email. Microsoft describes SQL Database alerts as metric-based alerts that can send email when metrics such as CPU usage reach a defined threshold. Azure Portal Method — Recommended for Simulation Step 1: Open the db1 Azure SQL database Sign in to the Azure portal. Search for SQL databases. Open the database named: db1 Do not open the SQL logical server unless the alert needs to apply to all databases. This task is specifically for db1, so the alert scope must be the db1 database resource. Step 2: Create a new alert rule From the db1 database page: In the left menu, select Alerts. Select Create. Select Alert rule. Microsoft’s Azure Monitor workflow allows you to create an alert rule directly from the target resource. When you create it from a resource, the resource is automatically set as the alert scope. Step 3: Confirm the alert scope On the alert rule page, confirm the scope is the Azure SQL database: db1 The resource type should be similar to: SQL database Microsoft.Sql/servers/databases If the scope is the SQL server instead of the database, remove it and select the db1 database resource. Step 4: Add the alert condition Under Condition, select Add condition. Choose the metric: CPU percentage Sometimes it appears as: CPU percent or metric name: cpu_percent For Azure SQL Database, CPU percentage represents CPU consumption toward the database workload limit, expressed as a percentage. Step 5: Configure the signal logic Configure the alert logic exactly like this: Setting Value Threshold type Static Aggregation type Average Operator Greater than Threshold value 50 Unit Percent Aggregation granularity / Period 1 minute Frequency of evaluation 1 minute Evaluation period / Lookback window 5 minutes This means Azure Monitor evaluates the CPU metric every minute, using one-minute metric samples, and fires the alert only when the average CPU percentage is greater than 50% across the five-minute evaluation window. Be precise here. The task says: average CPU percentage utilization is greater than 50 percent for five minutes sampled at one-minute intervals So the correct choices are: Average Greater than 50 Every 1 minute Over the last 5 minutes Azure Monitor metric alert rules combine the monitored resource, the metric condition, and action groups that run when the condition is met. Step 6: Create the email action group Under Actions, select: Create action group Configure the basics: Setting Value Subscription Use the current subscription Resource group Use the lab resource group Action group name AG-db1-CPU-Email Display name db1CPU Then go to Notifications. Add a notification: Setting Value Notification type Email/SMS message/Push/Voice Name EmailAdmin Email [email protected] Select OK, then Review + create, then Create. Action groups define the notification or automation action that runs when an alert fires. Microsoft documents email as a supported action group notification type. Step 7: Configure alert rule details Under Details, configure: Setting Value Severity 2 or 3 Alert rule name db1 CPU greater than 50 percent Description Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes. Enable upon creation Yes Severity is usually not specified by the task, so any reasonable severity is acceptable. In an exam lab, I would use Severity 2 for CPU performance impact. Step 8: Review and create Select Review + create. Confirm the condition says something equivalent to: Whenever the average CPU percentage is greater than 50 Confirm the evaluation settings are: Check every 1 minute Lookback period 5 minutes Confirm the action group sends email to: [email protected] Select Create. Verification After creation: Open db1. Go to Alerts. Select Alert rules. Confirm the alert rule exists and is enabled. Open the rule and verify: Scope: db1 Signal: CPU percentage Aggregation: Average Operator: Greater than Threshold: 50 Evaluation frequency: 1 minute Window size: 5 minutes Action group: [email protected] Azure CLI Method Use this only if the simulation gives you Cloud Shell. First get the database resource ID: az sql db show \ --resource-group \ --server \ --name db1 \ --query id \ --output tsv Create the action group: az monitor action-group create \ --resource-group \ --name AG-db1-CPU-Email \ --short-name db1CPU \ --action email EmailAdmin [email protected] Create the metric alert: az monitor metrics alert create \ --name "db1 CPU greater than 50 percent" \ --resource-group \ --scopes \ --condition "avg cpu_percent > 50" \ --window-size 5m \ --evaluation-frequency 1m \ --action AG-db1-CPU-Email \ --description "Alert when average CPU percentage for db1 is greater than 50 percent for 5 minutes." The metric name commonly used for Azure SQL Database CPU percentage in CLI/ARM contexts is: cpu_percent SSMS Clarification SSMS is not the correct tool for this task. Do not configure Database Mail. Azure SQL Database does not use SQL Server Agent/Database Mail in the same way as SQL Server on a VM or SQL Managed Instance. This requirement is an Azure Monitor metric alert requirement. Correct tool: Azure portal > db1 > Alerts > Create alert rule Wrong tool: SSMS Database Mail Final Exam-Lab Configuration Use this exact configuration: Resource: db1 Alert type: Metric alert Metric: CPU percentage Aggregation: Average Operator: Greater than Threshold: 50 Aggregation granularity: 1 minute Evaluation frequency: 1 minute Evaluation period/window: 5 minutes Action group notification: Email Email recipient: [email protected] That completes the task
Copyright © Pass4exams. All rights reserved.