QUESTION NO: 1
Hotspot Question
You have an Azure SQL database.
You need to identify whether a delayed query execution is associated to a RESOURCE_SEMAPHORE wait.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hotspot Question
You have an Azure SQL database.
You need to identify whether a delayed query execution is associated to a RESOURCE_SEMAPHORE wait.
How should you complete the Transact-SQL statement? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Box 1: wait_type
Determine if a RESOURCE_SEMAHPORE wait is a top wait
Use the following query to determine if a RESOURCE_SEMAHPORE wait is a top wait SELECT wait_type, SUM(wait_time) AS total_wait_time_ms FROM sys.dm_exec_requests AS req JOIN sys.dm_exec_sessions AS sess ON req.session_id = sess.session_id WHERE is_user_process = 1 GROUP BY wait_type ORDER BY SUM(wait_time) DESC; Box 2: sys.dm_exec_requests Use the sys.dm_exec_requests or sys.dm_os_waiting_tasks to see the wait_type and wait_time.
Azure SQL RESOURCE_SEMAPHORE wait "wait_time" is_user_process
QUESTION NO: 2
Case Study 2 - Contoso, Ltd
Overview
General Overview
Contoso, Ltd. is a financial data company that has 100 employees. The company delivers financial data to customers.
Physical Locations
Contoso has a datacenter in Los Angeles and an Azure subscription. All Azure resources are in the US West 2 Azure region. Contoso has a 10-Gb ExpressRoute connection to Azure.
The company has customers worldwide.
Existing Environment
Active Directory
Contoso has a hybrid Azure Active Directory (Azure AD) deployment that syncs to on-premises Active Directory.
Database Environment
Contoso has SQL Server 2017 on Azure virtual machines shown in the following table.
SQL1 and SQL2 are in an Always On availability group and are actively queried. SQL3 runs jobs, provides historical data, and handles the delivery of data to customers.
The on-premises datacenter contains a PostgreSQL server that has a 50-TB database.
Current Business Model
Contoso uses Microsoft SQL Server Integration Services (SSIS) to create flat files for customers.
The customers receive the files by using FTP.
Requirements
Planned Changes
Contoso plans to move to a model in which they deliver data to customer databases that run as platform as a service (PaaS) offerings. When a customer establishes a service agreement with Contoso, a separate resource group that contains an Azure SQL database will be provisioned for the customer. The database will have a complete copy of the financial data. The data to which each customer will have access will depend on the service agreement tier. The customers can change tiers by changing their service agreement.
The estimated size of each PaaS database is 1 TB.
Contoso plans to implement the following changes:
* Move the PostgreSQL database to Azure Database for PostgreSQL during the next six months.
* Upgrade SQL1, SQL2, and SQL3 to SQL Server 2019 during the next few months.
* Start onboarding customers to the new PaaS solution within six months.
Business Goals
Contoso identifies the following business requirements:
* Use built-in Azure features whenever possible.
* Minimize development effort whenever possible.
* Minimize the compute costs of the PaaS solutions.
* Provide all the customers with their own copy of the database by using the PaaS solution.
* Provide the customers with different table and row access based on the customer's service agreement.
* In the event of an Azure regional outage, ensure that the customers can access the PaaS solution with minimal downtime. The solution must provide automatic failover.
* Ensure that users of the PaaS solution can create their own database objects but he prevented from modifying any of the existing database objects supplied by Contoso.
Technical Requirements
Contoso identifies the following technical requirements:
* Users of the PaaS solution must be able to sign in by using their own corporate Azure AD credentials or have Azure AD credentials supplied to them by Contoso. The solution must avoid using the internal Azure AD of Contoso to minimize guest users.
* All customers must have their own resource group, Azure SQL server, and Azure SQL database. The deployment of resources for each customer must be done in a consistent fashion.
* Users must be able to review the queries issued against the PaaS databases and identify any new objects created.
* Downtime during the PostgreSQL database migration must be minimized.
Monitoring Requirements
Contoso identifies the following monitoring requirements:
* Notify administrators when a PaaS database has a higher than average CPU usage.
* Use a single dashboard to review security and audit data for all the PaaS databases.
* Use a single dashboard to monitor query performance and bottlenecks across all the PaaS databases.
* Monitor the PaaS databases to identify poorly performing queries and resolve query performance issues automatically whenever possible.
PaaS Prototype
During prototyping of the PaaS solution in Azure, you record the compute utilization of a customer's Azure SQL database as shown in the following exhibit.
Role Assignments
For each customer's Azure SQL Database server, you plan to assign the roles shown in the following exhibit.
You need to recommend a solution to ensure that the customers can create the database objects.
The solution must meet the business goals.
What should you include in the recommendation?
Case Study 2 - Contoso, Ltd
Overview
General Overview
Contoso, Ltd. is a financial data company that has 100 employees. The company delivers financial data to customers.
Physical Locations
Contoso has a datacenter in Los Angeles and an Azure subscription. All Azure resources are in the US West 2 Azure region. Contoso has a 10-Gb ExpressRoute connection to Azure.
The company has customers worldwide.
Existing Environment
Active Directory
Contoso has a hybrid Azure Active Directory (Azure AD) deployment that syncs to on-premises Active Directory.
Database Environment
Contoso has SQL Server 2017 on Azure virtual machines shown in the following table.
SQL1 and SQL2 are in an Always On availability group and are actively queried. SQL3 runs jobs, provides historical data, and handles the delivery of data to customers.
The on-premises datacenter contains a PostgreSQL server that has a 50-TB database.
Current Business Model
Contoso uses Microsoft SQL Server Integration Services (SSIS) to create flat files for customers.
The customers receive the files by using FTP.
Requirements
Planned Changes
Contoso plans to move to a model in which they deliver data to customer databases that run as platform as a service (PaaS) offerings. When a customer establishes a service agreement with Contoso, a separate resource group that contains an Azure SQL database will be provisioned for the customer. The database will have a complete copy of the financial data. The data to which each customer will have access will depend on the service agreement tier. The customers can change tiers by changing their service agreement.
The estimated size of each PaaS database is 1 TB.
Contoso plans to implement the following changes:
* Move the PostgreSQL database to Azure Database for PostgreSQL during the next six months.
* Upgrade SQL1, SQL2, and SQL3 to SQL Server 2019 during the next few months.
* Start onboarding customers to the new PaaS solution within six months.
Business Goals
Contoso identifies the following business requirements:
* Use built-in Azure features whenever possible.
* Minimize development effort whenever possible.
* Minimize the compute costs of the PaaS solutions.
* Provide all the customers with their own copy of the database by using the PaaS solution.
* Provide the customers with different table and row access based on the customer's service agreement.
* In the event of an Azure regional outage, ensure that the customers can access the PaaS solution with minimal downtime. The solution must provide automatic failover.
* Ensure that users of the PaaS solution can create their own database objects but he prevented from modifying any of the existing database objects supplied by Contoso.
Technical Requirements
Contoso identifies the following technical requirements:
* Users of the PaaS solution must be able to sign in by using their own corporate Azure AD credentials or have Azure AD credentials supplied to them by Contoso. The solution must avoid using the internal Azure AD of Contoso to minimize guest users.
* All customers must have their own resource group, Azure SQL server, and Azure SQL database. The deployment of resources for each customer must be done in a consistent fashion.
* Users must be able to review the queries issued against the PaaS databases and identify any new objects created.
* Downtime during the PostgreSQL database migration must be minimized.
Monitoring Requirements
Contoso identifies the following monitoring requirements:
* Notify administrators when a PaaS database has a higher than average CPU usage.
* Use a single dashboard to review security and audit data for all the PaaS databases.
* Use a single dashboard to monitor query performance and bottlenecks across all the PaaS databases.
* Monitor the PaaS databases to identify poorly performing queries and resolve query performance issues automatically whenever possible.
PaaS Prototype
During prototyping of the PaaS solution in Azure, you record the compute utilization of a customer's Azure SQL database as shown in the following exhibit.
Role Assignments
For each customer's Azure SQL Database server, you plan to assign the roles shown in the following exhibit.
You need to recommend a solution to ensure that the customers can create the database objects.
The solution must meet the business goals.
What should you include in the recommendation?
Correct Answer: D
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 3
Drag and Drop Question
You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1.
You need to encrypt the data in Col1.
Which four actions should you perform for DB1 in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Drag and Drop Question
You have an Azure SQL database named DB1. DB1 contains a table that has a column named Col1.
You need to encrypt the data in Col1.
Which four actions should you perform for DB1 in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
Explanation:
Use the following steps for column level encryption:
1. Create a database master key (Step 1)
2. Create a self-signed certificate for SQL Server (Step 2)
3. Configure a symmetric key for encryption (Step 3)
4. Encrypt the column data (this includes Open the symmetric key - Step 4)
5. Query and verify the encryption
Step 1: Create a database master key
Create a database master key for column level SQL Server encryption
In this first step, we define a database master key and provide a password to protect it. It is a symmetric key for protecting the private keys and asymmetric keys.
Step 2: Create a certificate.
Create a self-signed certificate for Column level SQL Server encryption In this step, we create a self-signed certificate using the CREATE CERTIFICATE statement. You might have seen that an organization receives a certificate from a certification authority and incorporates into their infrastructures. In SQL Server, we can use a self-signed certificate without using a certification authority certificate.
Step 3: Create a symmetric key.
Configure a symmetric key for column level SQL Server encryption.
In this step, we will define a symmetric key that you can see in the encryption hierarchy as well.
The symmetric key uses a single key for encryption and decryption as well.
Step 4: Open the symmetric key
Data encryption.
Let's encrypt the data in this newly added column.
In a query window, open the symmetric key and decrypt using the certificate. We need to use the same symmetric key and certificate name that we created earlier Etc.
Reference:
https://www.sqlshack.com/an-overview-of-the-column-level-sql-server-encryption/
QUESTION NO: 4
You have an on-premises Microsoft SQL Server 2016 database named DB1.
You need to migrate DB1 to an Azure SQL managed instance. The solution must meet the following requirements:
- Minimize downtime.
- Support online migration.
What should you use to perform the migration?
You have an on-premises Microsoft SQL Server 2016 database named DB1.
You need to migrate DB1 to an Azure SQL managed instance. The solution must meet the following requirements:
- Minimize downtime.
- Support online migration.
What should you use to perform the migration?
Correct Answer: D
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 5
You have an Azure subscription that contains two instances of SQL Server on Azure Virtual Machines named VM1 and VM2. Both instances run Microsoft SQL Server 2019 CU8.
You need to deploy a failover cluster instance (FCI) to VM1 and VM2. The solution must eliminate the need for the following:
- A distributed network name (DNN)
- A load balancer
What should you do?
You have an Azure subscription that contains two instances of SQL Server on Azure Virtual Machines named VM1 and VM2. Both instances run Microsoft SQL Server 2019 CU8.
You need to deploy a failover cluster instance (FCI) to VM1 and VM2. The solution must eliminate the need for the following:
- A distributed network name (DNN)
- A load balancer
What should you do?
Correct Answer: C
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 6
Drag and Drop Question
You have an on-premises server named Server1 that has Microsoft SQL Server 2022 installed.
You have an Azure subscription that contains an Azure SQL managed instance named Ml1. Ml1 contains a database named DB2.
You need to create a copy of DB2 on Server1.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Drag and Drop Question
You have an on-premises server named Server1 that has Microsoft SQL Server 2022 installed.
You have an Azure subscription that contains an Azure SQL managed instance named Ml1. Ml1 contains a database named DB2.
You need to create a copy of DB2 on Server1.
Which three actions should you perform in sequence? To answer, move the appropriate actions from the list of actions to the answer area and arrange them in the correct order.
Correct Answer:
Explanation:
Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/restore-database-to-sql-server
QUESTION NO: 7
Hotspot Question
You have an Azure subscription that contains an Azure SQL database named DB1 and a managed identity.
You need to create a user in DB1 that will be used by the managed identity to perform automated tasks.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hotspot Question
You have an Azure subscription that contains an Azure SQL database named DB1 and a managed identity.
You need to create a user in DB1 that will be used by the managed identity to perform automated tasks.
How should you complete the query? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Box 1: FROM EXTERNAL PROVIDER
Create a user-assigned managed identity
Box 2: OBJECT_ID
The above syntax allows creation of Microsoft Entra users without validation. For this to work, the Object Id of the Microsoft Entra principal would have to be supplied and used as an SID in the T- SQL statement.
The validity check of the Object Id is the responsibility of the user running the T-SQL statement.
Reference:
https://learn.microsoft.com/en-us/azure/azure-sql/database/authentication-azure-ad-user-assigned-managed-identity
QUESTION NO: 8
You have an Azure subscription that contains a user named User1 and an Azure SQL database named DB1.
You configure long-term retention (LTR) for DB1.
You need to ensure that User1 can delete LTR backups of DB1. The solution must follow the principle of least privilege.
Which role should you assign to User1?
You have an Azure subscription that contains a user named User1 and an Azure SQL database named DB1.
You configure long-term retention (LTR) for DB1.
You need to ensure that User1 can delete LTR backups of DB1. The solution must follow the principle of least privilege.
Which role should you assign to User1?
Correct Answer: B
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 9
Hotspot Question
You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that contains two databases named DB1 and DB2. The database and log files for DB1 and DB2 are hosted on managed disks.
You need to perform a snapshot backup of DB1 and DB2.
How should you complete the T-SQL statements? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hotspot Question
You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that contains two databases named DB1 and DB2. The database and log files for DB1 and DB2 are hosted on managed disks.
You need to perform a snapshot backup of DB1 and DB2.
How should you complete the T-SQL statements? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
QUESTION NO: 10
SIMULATION
You need to ensure that all queries executed against db1 are captured in the Query Store.
To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.
SIMULATION
You need to ensure that all queries executed against db1 are captured in the Query Store.
To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.
Correct Answer:
QUESTION NO: 11
You have an Azure subscription that contains the resources shown in the following table.
App1 experiences transient connection errors and timeouts when it attempts to access db1 after extended periods of inactivity.
You need to modify db1 to resolve the issues experienced by App1 as soon as possible, without considering immediate costs.
What should you do?
You have an Azure subscription that contains the resources shown in the following table.
App1 experiences transient connection errors and timeouts when it attempts to access db1 after extended periods of inactivity.
You need to modify db1 to resolve the issues experienced by App1 as soon as possible, without considering immediate costs.
What should you do?
Correct Answer: C
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 12
Your on-premises network contains a server that hosts a 60-TB database named DB1. The network has a 10-Mbps internet connection.
You need to migrate DB1 to Azure. The solution must minimize how long it takes to migrate the database.
What should you use?
Your on-premises network contains a server that hosts a 60-TB database named DB1. The network has a 10-Mbps internet connection.
You need to migrate DB1 to Azure. The solution must minimize how long it takes to migrate the database.
What should you use?
Correct Answer: A
Vote an answer
Explanation: Only visible for Actual4test members. You can sign-up / login (it's free).
QUESTION NO: 13
Hotspot Question
You have an Azure subscription that is linked to an Azure Active Directory (Azure AD) tenant named contoso.com The subscription contains an Azure SQL database named SQL1 and an Azure web app named app1. App1 has the managed identity feature enabled.
You need to create a new database user for app1.
How should you complete the Transact-SQL statement? To answer select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Hotspot Question
You have an Azure subscription that is linked to an Azure Active Directory (Azure AD) tenant named contoso.com The subscription contains an Azure SQL database named SQL1 and an Azure web app named app1. App1 has the managed identity feature enabled.
You need to create a new database user for app1.
How should you complete the Transact-SQL statement? To answer select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
In T-SQL, just point to App name not managed identity.
https://learn.microsoft.com/en-us/azure/active-directory/managed-identities-azure-resources/tutorial-windows-vm-access-sql