Microsoft Administering Relational Databases on Microsoft Azure - DP-300 Exam Practice Test
Case Study 1 - Litware, Inc
Overview
Litware, Inc. is a renewable energy company that has a main office in Boston. The main office hosts a sales department and the primary datacenter for the company.
Physical Locations
Existing Environment
Litware has a manufacturing office and a research office is separate locations near Boston. Each office has its own datacenter and internet connection.
The manufacturing and research datacenters connect to the primary datacenter by using a VPN.
Network Environment
The primary datacenter has an ExpressRoute connection that uses both Microsoft peering and private peering. The private peering connects to an Azure virtual network named HubVNet.
Identity Environment
Litware has a hybrid Azure Active Directory (Azure AD) deployment that uses a domain named litwareinc.com. All Azure subscriptions are associated to the litwareinc.com Azure AD tenant.
Database Environment
The sales department has the following database workload:
* An on-premises named SERVER1 hosts an instance of Microsoft SQL Server 2012 and two 1- TB databases.
* A logical server named SalesSrv01A contains a geo-replicated Azure SQL database named SalesSQLDb1. SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool. SalesSQLDb1 uses database firewall rules and contained database users.
* An application named SalesSQLDb1App1 uses SalesSQLDb1.
The manufacturing office contains two on-premises SQL Server 2016 servers named SERVER2 and SERVER3. The servers are nodes in the same Always On availability group. The availability group contains a database named ManufacturingSQLDb1 Database administrators have two Azure virtual machines in HubVnet named VM1 and VM2 that run Windows Server 2019 and are used to manage all the Azure databases.
Licensing Agreement
Litware is a Microsoft Volume Licensing customer that has License Mobility through Software Assurance.
Current Problems
SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.
Requirements
Planned Changes
Litware plans to implement the following changes:
* Implement 30 new databases in Azure, which will be used by time-sensitive manufacturing apps that have varying usage patterns. Each database will be approximately 20 GB.
* Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01. ResearchDB1 will contain Personally Identifiable Information (PII) data.
* Develop an app named ResearchApp1 that will be used by the research department to populate and access ResearchDB1.
* Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.
* Migrate the SERVER1 databases to the Azure SQL Database platform.
Technical Requirements
Litware identifies the following technical requirements:
* Maintenance tasks must be automated.
* The 30 new databases must scale automatically.
* The use of an on-premises infrastructure must be minimized.
* Azure Hybrid Use Benefits must be leveraged for Azure SQL Database deployments.
* All SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.
Security and Compliance Requirements
Litware identifies the following security and compliance requirements:
* Store encryption keys in Azure Key Vault.
* Retain backups of the PII data for two months.
* Encrypt the PII data at rest, in transit, and in use.
* Use the principle of least privilege whenever possible.
* Authenticate database users by using Active Directory credentials.
* Protect Azure SQL Database instances by using database-level firewall rules.
* Ensure that all databases hosted in Azure are accessible from VM1 and VM2 without relying on public endpoints.
Business Requirements
Litware identifies the following business requirements:
* Meet an SLA of 99.99% availability for all Azure deployments.
* Minimize downtime during the migration of the SERVER1 databases.
* Use the Azure Hybrid Use Benefits when migrating workloads to Azure.
* Once all requirements are met, minimize costs whenever possible.
You need to provide an implementation plan to configure data retention for ResearchDB1. The solution must meet the security and compliance requirements.
What should you include in the plan?
Overview
Litware, Inc. is a renewable energy company that has a main office in Boston. The main office hosts a sales department and the primary datacenter for the company.
Physical Locations
Existing Environment
Litware has a manufacturing office and a research office is separate locations near Boston. Each office has its own datacenter and internet connection.
The manufacturing and research datacenters connect to the primary datacenter by using a VPN.
Network Environment
The primary datacenter has an ExpressRoute connection that uses both Microsoft peering and private peering. The private peering connects to an Azure virtual network named HubVNet.
Identity Environment
Litware has a hybrid Azure Active Directory (Azure AD) deployment that uses a domain named litwareinc.com. All Azure subscriptions are associated to the litwareinc.com Azure AD tenant.
Database Environment
The sales department has the following database workload:
* An on-premises named SERVER1 hosts an instance of Microsoft SQL Server 2012 and two 1- TB databases.
* A logical server named SalesSrv01A contains a geo-replicated Azure SQL database named SalesSQLDb1. SalesSQLDb1 is in an elastic pool named SalesSQLDb1Pool. SalesSQLDb1 uses database firewall rules and contained database users.
* An application named SalesSQLDb1App1 uses SalesSQLDb1.
The manufacturing office contains two on-premises SQL Server 2016 servers named SERVER2 and SERVER3. The servers are nodes in the same Always On availability group. The availability group contains a database named ManufacturingSQLDb1 Database administrators have two Azure virtual machines in HubVnet named VM1 and VM2 that run Windows Server 2019 and are used to manage all the Azure databases.
Licensing Agreement
Litware is a Microsoft Volume Licensing customer that has License Mobility through Software Assurance.
Current Problems
SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and frequent blocking queries.
Requirements
Planned Changes
Litware plans to implement the following changes:
* Implement 30 new databases in Azure, which will be used by time-sensitive manufacturing apps that have varying usage patterns. Each database will be approximately 20 GB.
* Create a new Azure SQL database named ResearchDB1 on a logical server named ResearchSrv01. ResearchDB1 will contain Personally Identifiable Information (PII) data.
* Develop an app named ResearchApp1 that will be used by the research department to populate and access ResearchDB1.
* Migrate ManufacturingSQLDb1 to the Azure virtual machine platform.
* Migrate the SERVER1 databases to the Azure SQL Database platform.
Technical Requirements
Litware identifies the following technical requirements:
* Maintenance tasks must be automated.
* The 30 new databases must scale automatically.
* The use of an on-premises infrastructure must be minimized.
* Azure Hybrid Use Benefits must be leveraged for Azure SQL Database deployments.
* All SQL Server and Azure SQL Database metrics related to CPU and storage usage and limits must be analyzed by using Azure built-in functionality.
Security and Compliance Requirements
Litware identifies the following security and compliance requirements:
* Store encryption keys in Azure Key Vault.
* Retain backups of the PII data for two months.
* Encrypt the PII data at rest, in transit, and in use.
* Use the principle of least privilege whenever possible.
* Authenticate database users by using Active Directory credentials.
* Protect Azure SQL Database instances by using database-level firewall rules.
* Ensure that all databases hosted in Azure are accessible from VM1 and VM2 without relying on public endpoints.
Business Requirements
Litware identifies the following business requirements:
* Meet an SLA of 99.99% availability for all Azure deployments.
* Minimize downtime during the migration of the SERVER1 databases.
* Use the Azure Hybrid Use Benefits when migrating workloads to Azure.
* Once all requirements are met, minimize costs whenever possible.
You need to provide an implementation plan to configure data retention for ResearchDB1. The solution must meet the security and compliance requirements.
What should you include in the plan?
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
Case Study 5 - ADatum Corporation
Overview
ADatum Corporation is a financial services company that has a main office in New York City.
Existing Environment
Licensing Agreement
ADatum has a Microsoft Volume Licensing agreement that includes Software Assurance.
Network Infrastructure
ADatum has an on-premises datacenter and an Azure subscription named Sub1.
Sub1 contains a virtual network named Network1 in the East US Azure region.
The datacenter is connected to Network1 by using a Site-to-Site (S2S) VPN.
Identity Environment
The on-premises network contains an Active Directory Domain Services (AD DS) forest.
The forest contains a single domain named corp.adatum.com.
The corp.adatum.com domain syncs with a Microsoft Entra tenant named adatum.com.
Database Environment
The datacenter contains the servers shown in the following table.
DB1 and DB2 are used for transactional and analytical workloads by an application named App1.
App1 runs on Microsoft Entra hybrid joined servers that run Windows Server 2022. App1 uses Kerberos authentication.
DB3 stores compliance data used by two applications named App2 and App3.
DB3 performance is monitored by using Extended Events sessions, with the event_file target set to a file share on a local disk of SVR3.
Resource allocation for DB3 is managed by using Resource Governor.
Requirements
Planned Changes
ADatum plans to implement the following changes:
- Deploy an Azure SQL managed instance named Instance1 to Network1.
- Migrate DB1 and DB2 to Instance1.
- Migrate DB3 to Azure SQL Database.
- Following the migration of DB1 and DB2, hand over database development to remote developers who use Microsoft Entra joined Windows 11 devices.
- Following the migration of DB3, configure the database to be part of an auto-failover group.
Availability Requirements
ADatum identifies the following post-migration availability requirements:
- For DB1 and DB2, offload analytical workloads to a read-only database replica in the same Azure region.
- Ensure that if a regional disaster occurs, DB1 and DB2 can be recovered from backups.
- After the migration, App1 must maintain access to DB1 and DB2.
- For DB3, manage potential performance issues caused by resource demand changes by App2 and App3.
- Ensure that DB3 will still be accessible following a planned failover.
- Ensure that DB3 can be restored if the logical server is deleted.
- Minimize downtime during the migration of DB1 and DB2.
Security Requirements
ADatum identifies the following security requirements for after the migration:
- Ensure that only designated developers who use Microsoft Entra joined Windows 11 devices can access DB1 and DB2 remotely.
- Ensure that all changes to DB3, including ones within individual transactions, are audited and recorded.
Management Requirements
ADatum identifies the following post-migration management requirements:
- Continue using Extended Events to monitor DB3.
- In Azure SQL Database, automate the management of DB3 by using elastic jobs that have database-scoped credentials.
Business Requirements
ADatum identifies the following business requirements:
- Minimize costs whenever possible, without affecting other requirements.
- Minimize administrative effort.
You need to recommend a solution to meet the security requirements and the business requirements for DB3.
What should you recommend as the first step of the solution?
Overview
ADatum Corporation is a financial services company that has a main office in New York City.
Existing Environment
Licensing Agreement
ADatum has a Microsoft Volume Licensing agreement that includes Software Assurance.
Network Infrastructure
ADatum has an on-premises datacenter and an Azure subscription named Sub1.
Sub1 contains a virtual network named Network1 in the East US Azure region.
The datacenter is connected to Network1 by using a Site-to-Site (S2S) VPN.
Identity Environment
The on-premises network contains an Active Directory Domain Services (AD DS) forest.
The forest contains a single domain named corp.adatum.com.
The corp.adatum.com domain syncs with a Microsoft Entra tenant named adatum.com.
Database Environment
The datacenter contains the servers shown in the following table.
DB1 and DB2 are used for transactional and analytical workloads by an application named App1.
App1 runs on Microsoft Entra hybrid joined servers that run Windows Server 2022. App1 uses Kerberos authentication.
DB3 stores compliance data used by two applications named App2 and App3.
DB3 performance is monitored by using Extended Events sessions, with the event_file target set to a file share on a local disk of SVR3.
Resource allocation for DB3 is managed by using Resource Governor.
Requirements
Planned Changes
ADatum plans to implement the following changes:
- Deploy an Azure SQL managed instance named Instance1 to Network1.
- Migrate DB1 and DB2 to Instance1.
- Migrate DB3 to Azure SQL Database.
- Following the migration of DB1 and DB2, hand over database development to remote developers who use Microsoft Entra joined Windows 11 devices.
- Following the migration of DB3, configure the database to be part of an auto-failover group.
Availability Requirements
ADatum identifies the following post-migration availability requirements:
- For DB1 and DB2, offload analytical workloads to a read-only database replica in the same Azure region.
- Ensure that if a regional disaster occurs, DB1 and DB2 can be recovered from backups.
- After the migration, App1 must maintain access to DB1 and DB2.
- For DB3, manage potential performance issues caused by resource demand changes by App2 and App3.
- Ensure that DB3 will still be accessible following a planned failover.
- Ensure that DB3 can be restored if the logical server is deleted.
- Minimize downtime during the migration of DB1 and DB2.
Security Requirements
ADatum identifies the following security requirements for after the migration:
- Ensure that only designated developers who use Microsoft Entra joined Windows 11 devices can access DB1 and DB2 remotely.
- Ensure that all changes to DB3, including ones within individual transactions, are audited and recorded.
Management Requirements
ADatum identifies the following post-migration management requirements:
- Continue using Extended Events to monitor DB3.
- In Azure SQL Database, automate the management of DB3 by using elastic jobs that have database-scoped credentials.
Business Requirements
ADatum identifies the following business requirements:
- Minimize costs whenever possible, without affecting other requirements.
- Minimize administrative effort.
You need to recommend a solution to meet the security requirements and the business requirements for DB3.
What should you recommend as the first step of the solution?
Correct Answer: B
Vote an answer
You have an Azure Synapse Analytics dedicated SQL pool.
You run PDW_SHOWSPACEUSED('dbo.FactInternetSales'); and get the results shown in the following table.
Which statement accurately describes the dbo.FactInternetSales table?
You run PDW_SHOWSPACEUSED('dbo.FactInternetSales'); and get the results shown in the following table.
Which statement accurately describes the dbo.FactInternetSales table?
Correct Answer: A
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have an Azure SQL database named DB1. You run a query while connected to DB1.
You review the actual execution plan for the query, and you add an index to a table referenced by the query.
You need to compare the previous actual execution plan for the query to the Live Query Statistics.
What should you do first in Microsoft SQL Server Management Studio (SSMS)?
You review the actual execution plan for the query, and you add an index to a table referenced by the query.
You need to compare the previous actual execution plan for the query to the Live Query Statistics.
What should you do first in Microsoft SQL Server Management Studio (SSMS)?
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have an Azure SQL Database elastic pool that contains 10 databases.
You receive the following alert.
Msg 1132, Level 16, state 1, Line 1
The elastic pool has reached its storage limit. The storage used for the elastic pool cannot exceed (76800) MBs.
You need to resolve the alert. The solution must minimize administrative effort.
Which three actions can you perform? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
You receive the following alert.
Msg 1132, Level 16, state 1, Line 1
The elastic pool has reached its storage limit. The storage used for the elastic pool cannot exceed (76800) MBs.
You need to resolve the alert. The solution must minimize administrative effort.
Which three actions can you perform? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
Correct Answer: A,C,E
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have SQL Server 2019 on an Azure virtual machine that runs Windows Server 2019. The virtual machine has 4 vCPUs and 28 GB of memory.
You scale up the virtual machine to 8 vCPUs and 64 GB of memory.
You need to reduce tempdb contention without negatively affecting server performance.
What is the number of secondary data files that you should configure for tempdb?
You scale up the virtual machine to 8 vCPUs and 64 GB of memory.
You need to reduce tempdb contention without negatively affecting server performance.
What is the number of secondary data files that you should configure for tempdb?
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
You have an Azure subscription that contains a SQL Server on Linux Azure Virtual Machines instance named VM1. VM1 contains a database named DB1.
You need to create operational performance baselines for DB1 and VM1.
What should you use?
You need to create operational performance baselines for DB1 and VM1.
What should you use?
Correct Answer: C
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
SIMULATION
You need to create a new database role named role1 that can read the contents of the SalesLT.SalesOrderDetail table only in db1.
To complete this task, sign in to the virtual machine. You may need to use SQL Server Management Studio and the Azure portal.
You need to create a new database role named role1 that can read the contents of the SalesLT.SalesOrderDetail table only in db1.
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:
You can create a new server role in SQL Server by using SQL Server Management Studio or Transact-SQL.
Using SQL Server Management Studio to create a new server role
Step 1: In Object Explorer, expand the server where you want to create the new server role.
Step 2: Expand the Security folder.
Step 3: Right-click the Server Roles folder and select New Server Role....
Step 4: In the New Server Role -server_role_name dialog box, on the General page, enter a name for the new server role in the Server role name box. [Enter role1] Step 5: In the Owner box, enter the name of the server principal that will own the new role.
Alternately, click the ellipsis (...) to open the Select Server Login or Role dialog box.
Step 6: Under Securables, select one or more server-level securables [Select the SalesLT.SalesOrderDetail table]. When a securable is selected, this server role can be granted or denied permissions on that securable.
Step 7: In the Permissions: Explicit box, select the check box to grant, grant with grant, or deny permission to this server role for the selected securables [Select Read]. If a permission cannot be granted or denied to all of the selected securables, the permission is represented as a partial select.
Step 8: On the Members page, use the Add button to add logins that represent individuals or groups to the new server role. [Skip] Step 9: A user-defined server role can be a member of another server role. On the Memberships page, select a check box to make the current user-defined server role a member of a selected server role. [Skip] Step 10: Select OK.
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-server-role
Using SQL Server Management Studio to create a new server role
Step 1: In Object Explorer, expand the server where you want to create the new server role.
Step 2: Expand the Security folder.
Step 3: Right-click the Server Roles folder and select New Server Role....
Step 4: In the New Server Role -server_role_name dialog box, on the General page, enter a name for the new server role in the Server role name box. [Enter role1] Step 5: In the Owner box, enter the name of the server principal that will own the new role.
Alternately, click the ellipsis (...) to open the Select Server Login or Role dialog box.
Step 6: Under Securables, select one or more server-level securables [Select the SalesLT.SalesOrderDetail table]. When a securable is selected, this server role can be granted or denied permissions on that securable.
Step 7: In the Permissions: Explicit box, select the check box to grant, grant with grant, or deny permission to this server role for the selected securables [Select Read]. If a permission cannot be granted or denied to all of the selected securables, the permission is represented as a partial select.
Step 8: On the Members page, use the Add button to add logins that represent individuals or groups to the new server role. [Skip] Step 9: A user-defined server role can be a member of another server role. On the Memberships page, select a check box to make the current user-defined server role a member of a selected server role. [Skip] Step 10: Select OK.
Reference:
https://learn.microsoft.com/en-us/sql/relational-databases/security/authentication-access/create-a-server-role
Hotspot Question
You have two on-premises servers that run Windows Server 2019 and host a Microsoft SQL server 2017 Always On availability group named AG1. AG1 contains a single database named DB1.
You have an Azure subscription. The subscription contains a virtual machine named VM1 that runs Linux.
You need to migrate DB1 to a SQL Server 2019 instance on VM1. The solution must minimize the downtime of DB1 during the migration.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one-point.
You have two on-premises servers that run Windows Server 2019 and host a Microsoft SQL server 2017 Always On availability group named AG1. AG1 contains a single database named DB1.
You have an Azure subscription. The subscription contains a virtual machine named VM1 that runs Linux.
You need to migrate DB1 to a SQL Server 2019 instance on VM1. The solution must minimize the downtime of DB1 during the migration.
What should you do? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one-point.
Correct Answer:
Hotspot Question
You have an Azure subscription.
You need to deploy an Azure SQL resource that will support cross database queries by using an Azure Resource Manager (ARM) template.
How should you complete the ARM template? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
You have an Azure subscription.
You need to deploy an Azure SQL resource that will support cross database queries by using an Azure Resource Manager (ARM) template.
How should you complete the ARM template? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
Explanation:
Box 1: Microsoft.Sql/managedInstances
There is a need to support cross database queries.
Box 2: parameters('virtualNetworkName')
The Managed Instance depends on the Virtual Network.
{
"type": "Microsoft.Sql/managedInstances",
"apiVersion": "2020-02-02-preview",
"name": "[parameters('managedInstanceName')]",
"location": "[parameters('location')]",
"dependsOn": [
"[resourceId('Microsoft.Network/virtualNetworks', parameters('virtualNetworkName'))]"
]
}
Reference:
https://docs.microsoft.com/en-us/azure/azure-sql/managed-instance/create-template-quickstart?tabs=azure-powershell
Hotspot Question
You have an Azure subscription that contains a group named Group1 and an Azure SQL managed instance that hosts a database named DB1.
You need to ensure that Group1 has read access to new tables created in DB1. The solution must use the principle of least privilege.
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.
You have an Azure subscription that contains a group named Group1 and an Azure SQL managed instance that hosts a database named DB1.
You need to ensure that Group1 has read access to new tables created in DB1. The solution must use the principle of least privilege.
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:
https://learn.microsoft.com/en-us/sql/t-sql/statements/grant-schema-permissions-transact-sql?view=sql-server-ver16
You have an Azure SQL database named db1 on a server named server1.
The Intelligent Insights diagnostics log identifies that several tables are missing indexes.
You need to ensure that indexes are created for the tables.
What should you do?
The Intelligent Insights diagnostics log identifies that several tables are missing indexes.
You need to ensure that indexes are created for the tables.
What should you do?
Correct Answer: B
Vote an answer
Explanation: Only visible for Fast2test members. You can sign-up / login (it's free).
Hotspot Question
You plan to deploy an Always On failover cluster instance (FCI) on Azure virtual machines.
You need to provision an Azure Storage account to host a cloud witness for the deployment.
How should you configure the storage account? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
You plan to deploy an Always On failover cluster instance (FCI) on Azure virtual machines.
You need to provision an Azure Storage account to host a cloud witness for the deployment.
How should you configure the storage account? To answer, select the appropriate options in the answer area.
NOTE: Each correct selection is worth one point.
Correct Answer:
