DP-300: Administering Relational Databases on Microsoft Azure Topic 3
Question #: 32
Topic #: 3
You receive numerous alerts from Azure Monitor for an Azure SQL Database instance.
You need to reduce the number of alerts. You must only receive alerts if there is a significant change in usage patterns for an extended period.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Set Threshold Sensitivity to High
B. Set the Alert logic threshold to Dynamic
C. Set the Alert logic threshold to Static
D. Set Threshold Sensitivity to Low
E. Set Force Plan to On
Selected Answer: B
Question #: 33
Topic #: 3
You have an Azure SQL database named sqldb1.
You need to minimize the amount of space by the data and log files of sqldb1.
What should you run?
A. DBCC SHRINKDATABASE
B. sp_clean_db_free_space
C. sp_clean_db_file_free_space
D. DBCC SHRINKFILE
Selected Answer: A
Question #: 33
Topic #: 2
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?
A. Azure Migrate
B. Azure Data Box
C. Azure Database Migration Service
D. Data Migration Assistant (DMA)
Selected Answer: B
Question #: 33
Topic #: 6
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.
Which deployment option and pricing tier should you configure?
A. Azure SQL Database Premium
B. Azure SQL Database serverless
C. Azure SQL Database Basic
D. Azure SQL Database Hyperscale
Selected Answer: A
Question #: 34
Topic #: 6
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.
Which deployment option and pricing tier should you configure?
A. Azure SQL Database Business Critical
B. Azure SQL Database Standard
C. Azure SQL Database managed instance General Purpose
D. Azure SQL Database Hyperscale
Selected Answer: A
Question #: 34
Topic #: 3
You have an Azure SQL Database server named sqlsrv1 that hosts 10 Azure SQL databases.
The databases perform slower than expected.
You need to identify whether the performance issue relates to the use of tempdb by Azure SQL databases in sqlsrv1.
What should you do?
A. Run Query Store-based queries
B. Review information provided by SQL Server Profiler-based traces
C. Review information provided by Query Performance Insight
D. Run dynamic management view-based queries
Selected Answer: C
Question #: 35
Topic #: 1
You have an on-premises Microsoft SQL Server 2019 server that hosts a database named DB1.
You have an Azure subscription that contains an Azure SQL managed instance named SQLMI1 and a virtual network named VNET1. SQLMI1 resides on VNET1.
The on-premises network connects to VNET1 by using an ExpressRoute connection.
You plan to migrate DB1 to SQLMI1 by using Azure Database Migration Service.
You need to configure VNET1 to support the migration.
What should you do?
A. Configure service endpoints.
B. Configure virtual network peering.
C. Deploy an Azure firewall.
D. Configure network security groups (NSGs).
Selected Answer: A
Question #: 35
Topic #: 6
You plan to deploy two instances of SQL Server on Azure virtual machines in a highly available configuration that will use an Always On availability group.
You need to recommend a deployment solution that meets the following requirements:
• Provides a Service Level Agreement (SLA) of at least 99.95%
• Replicates databases in the same group synchronously
• Minimizes the latency of database writes
What should you recommend?
A. Create two proximity groups and two availability sets. Deploy each virtual machine to a unique availability set. Add one virtual machine to each proximity group.
B. Create a proximity group and an availability set. Deploy each virtual machine to the availability set. Add both virtual machines to the proximity group.
C. Create a proximity group and two availability sets. Deploy each virtual machine to a unique availability set. Add both virtual machines to the proximity group.
D. Create two proximity groups and a single availability set. Deploy both virtual machines to the availability set. Add one virtual machine to each proximity group.
Selected Answer: B
Question #: 35
Topic #: 2
You have an Azure SQL database named DB1.
You need to encrypt DB1. The solution must meet the following requirements:
• Encrypt data in motion.
• Support comparison operators.
• Provide randomized encryption.
What should you include in the solution?
A. Always Encrypted with secure enclaves
B. Always Encrypted
C. column-level encryption
D. Transparent Data Encryption (TDE)
Selected Answer: A
Question #: 36
Topic #: 2
You have an instance of SQL Server on Azure Virtual Machines.
You need to ensure that a user named User1 can configure proxy accounts for SQL Server Agent jobs. The solution must use the principle of least privilege.
Which role should you assign to User1?
A. sysadmin
B. SQLAgentUserRole
C. SQLAgentReaderRole
D. SQLAgentOperatorRole
Selected Answer: A
Question #: 36
Topic #: 6
You have a single availability set that contains two SQL Server on Azure Virtual Machines instances.
The instances were deployed by using an Azure Marketplace SQL Server 2019 Enterprise image that has the latest cumulative updates applied. The instances are configured as the nodes of a failover cluster instance (FCI) named FCI1.
You need to ensure that client applications can connect to FCI1. The solution must meet the following requirements:
• Provide an availability SLA.
• Minimize costs.
What should you create?
A. an Azure Standard Load Balancer
B. a virtual network name (VNN) resource
C. a Basic Azure Load Balancer
D. a distributed network name (DNN) resource
Selected Answer: D
Question #: 36
Topic #: 1
You have an on-premises Microsoft SQL server that uses the FileTables and Filestream features.
You plan to migrate to Azure SQL.
Which service should you use?
A. Azure SQL Database
B. SQL Server on an Azure Virtual Machine
C. Azure SQL Managed Instance
D. Azure Database for MySQL
Selected Answer: B
Question #: 37
Topic #: 1
You need to migrate an on-premises Microsoft SQL Server database to Azure SQL Database. The solution must minimize downtime.
What should you do?
A. Configure Transaction Log Shipping.
B. Implement Always On availability groups.
C. Configure transactional replication.
D. Import a BACPAC.
Selected Answer: C
Question #: 37
Topic #: 3
You have an Azure SQL managed instance named MI1.
You need to implement automatic tuning for the databases of MI1.
What should you do?
A. Use The REST API to call the patch operation and modify the AutomaticTuningServerMode property.
B. From the Azure portal, configure automatic tuning.
C. Use Transact-SQL to enable the FORCE_LAST_GOOD_PLAN option.
Selected Answer: B
Question #: 37
Topic #: 2
You have an Azure SQL database named DB1. DB1 has a table named Table1 that contains the following columns.
You plan to enable Always Encrypted for Table1.
Which two columns support encryption? Each correct answer presents a complete solution.
NOTE: Each correct selection is worth one point.
A. Column1
B. Column2
C. Column3
D. Column4
E. Column5
Selected Answer: DE
Question #: 38
Topic #: 3
You have an Azure subscription that contains an Azure SQL database named db1.
You need to implement SQL insights for db1.
Which two resources should you create first? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. a storage account
B. a virtual machine
C. an Azure logic app
D. an Azure function
E. a Log Analytics workspace
Selected Answer: BE
Question #: 38
Topic #: 6
You have an Azure subscription that contains two Azure SQL managed instances named SQLMI1 and SQLMI2. SQLMI1 contains a database named DB1 and a user named User1.
User1 drops DB1.
You need to perform a point-in-time restore of DB1 to SQLMI2.
What should you use to perform the restore?
A. Azure CLI
B. Transact-SQL
C. the Azure portal
D. Azure PowerShell
Selected Answer: D
Question #: 38
Topic #: 1
You have an Azure SQL database named DB1.
You have a table name Table1 that has 20 columns of type CHAR(400). Row compression for Table1 is enabled.
During a database audit, you discover that none of the fields contain more than 150 characters.
You need to ensure that you can apply page compression to Table1.
What should you do?
A. Configure the columns as sparse.
B. Change the column type to NVARCHAR(MAX).
C. Change the column type to VARCHAR(MAX).
D. Change the column type to VARCHAR(200).
Selected Answer: D
Question #: 39
Topic #: 1
You have an on-premises Microsoft SQL Server named SQL1 that hosts five databases.
You need to migrate the databases to an Azure SQL managed instance. The solution must minimize downtime and prevent data loss.
What should you use?
A. Always On availability groups
B. Backup and Restore
C. log shipping
D. Database Migration Assistant
Selected Answer: B
Question #: 39
Topic #: 6
You deploy an instance of SQL Server on Azure Virtual Machines: named SQL1 that hosts multiple databases.
You configure the full recovery model for all the databases.
You perform a full backup of the master database on SQL1.
You need to a perform an additional backup of the master database on SQL1. The solution must minimize how long it takes to perform the backup.
Which type of backup should you perform?
A. log
B. full
C. differential
D. tail-log
Selected Answer: B
Question #: 39
Topic #: 3
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?
A. Enable automatic tuning for db1.
B. Increase the number of vCores allocated to db1.
C. Decrease the auto-pause delay for db1.
D. Disable auto-pause delay for db1.
Selected Answer: D
Question #: 40
Topic #: 2
You have an Azure subscription.
You create a logical SQL server that hosts four databases. Each database will be used by a separate customer.
You need to ensure that each customer can access only its own database. The solution must minimize administrative effort.
Which two actions should you perform? Each correct answer presents part of the solution.
NOTE: Each correct selection is worth one point.
A. Deny public access.
B. Create a private endpoint.
C. Create a database-level firewall rule.
D. Create a network security group (NSG).
E. Create a server-level firewall rule.
Selected Answer: C
Question #: 40
Topic #: 1
You have an Azure subscription that contains an Azure SQL database. The database contains a table named tablet that uses partitioned columnstores.
You need to configure table1 to meet the following requirements:
• Each partition must be compressed.
• The compression ratio must be maximized.
• You must be able to index the compressed data.
What should you use?
A. page compression
B. columnstore compression
C. GZIP compression
D. columnstore archival compression
Selected Answer: D
Question #: 40
Topic #: 6
You have an Azure SQL database named SQL1.
You need to implement a disaster recovery solution for SQL1. The solution must minimize the following:
• The recovery point objective (RPO)
• The recovery time objective (RTO)
• Administrative effort
What should you include in the solution?
A. Azure Site Recovery
B. active geo-replication
C. availability groups
D. auto-failover groups
Selected Answer: D
Question #: 41
Topic #: 1
You have an Azure subscription linked to an Azure Active Directory (Azure AD) tenant. The subscription contains 10 virtual machines that run Windows Server 2019 and host Microsoft SQL Server 2019 instances.
You need to ensure that you can manage the SQL Server instances by using a single user account.
What should you do first?
A. Enable a user-assigned managed identity on each virtual machine.
B. Deploy an Azure Active Directory Domain Services (Azure AD DS) domain and join the virtual machines to the domain.
C. Enable a system-assigned managed identity on each virtual machine.
D. Join the virtual machines to the Azure AD tenant.
Selected Answer: B
Question #: 41
Topic #: 6
You need to recommend a disaster recovery solution for an on-premises Microsoft SQL Server database. The solution must meet the following requirements:
• Support real-time data replication to a different geographic region.
• Use Azure as a disaster recovery target.
• Minimize costs and administrative effort.
What should you include in the recommendation?
A. database mirroring on an instance of SQL Server on Azure Virtual Machines
B. availability groups for SQL Server on Azure Virtual Machines
C. an Azure SQL Managed Instance link
D. transactional replication to an Azure SQL Managed Instance
Selected Answer: D
Question #: 42
Topic #: 3
You have the following resources:
• 15 SQL Server on Azure Virtual Machines instances
• 20 Azure SQL databases
You need to recommend a solution to centrally monitor the resources for security vulnerabilities.
What should you include in the recommendation?
A. database audits
B. Microsoft Defender
C. SQL insights
D. Azure SQL Auditing
Selected Answer: B
Question #: 42
Topic #: 2
You have an Azure subscription that contains an instance of SQL Server on an Azure virtual machine named VM1 and an Azure Active Directory Domain Services (Azure AD DS) domain that contains two users named User1 and User 2.
On the default instance of SQL Server on VM1, you create a credential named Credential1 for User1.
You need to ensure that User2 can create a SQL Server Agent proxy that will use Credential1. The solution must use the principle of least privilege.
Which role should you assign to User2?
A. SQLAgentUserRole
B. SQLAgentReaderRole
C. SQLAgentOperatorRole
D. sysadmin
Selected Answer: D
Question #: 42
Topic #: 6
You have an Azure subscription.
You need to deploy two instances of SQL Server on Azure virtual machines in a highly available configuration that will use an Always On availability group. The solution must meet the following requirements:
• Minimize how long it takes to fail over.
• Maintain existing connections to the primary replica during a failover.
What should you do?
A. Connect each virtual machine to a different subnet on a virtual network. Deploy a basic Azure load balancer.
B. Connect each virtual machine to a different subnet on a single virtual network.
C. Connect each virtual machine to a single subnet on a single virtual network.
D. Connect each virtual machine to a single subnet on a virtual network. Deploy a standard Azure load balancer.
Selected Answer: C
Question #: 43
Topic #: 1
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.
A. Increase the maximum storage of the elastic pool.
B. Delete data from a database.
C. Remove a database from the pool.
D. Enable data compression.
E. Shrink individual databases.
Selected Answer: ACD
Question #: 43
Topic #: 6
You have an Azure SQL database.
You need to implement a disaster recovery solution that meets the following requirements:
• Minimizes how long it takes to recover the database if a datacenter fails
• Minimizes administrative effort
What should you include in the solution?
A. Azure Site Recovery
B. active geo-replication
C. auto-failover groups
D. Azure Backup
Selected Answer: C
Question #: 44
Topic #: 1
You have an Azure subscription.
You need to deploy a new Azure SQL database by using Azure Command-Line Interface (CLI).
Which three parameters are required?
A. –name, –edition, and –capacity
B. –name, –tier, and –min-capacity
C. –name, –resource-group, and –server
D. –name, –licence-type, and –capacity
Selected Answer: C
Question #: 44
Topic #: 3
You have an Azure subscription that contains an Azure SQL managed instance named SQLMI1 and a Log Analytics workspace named Workspace1.
You need to collect performance metrics for SQLMI1 and stream the metrics to Workspace.
What should you do first?
A. Create a private endpoint connection on SQLMI1.
B. Configure Azure SQL Analytics to use Workspace1.
C. Modify the Compute + storage settings for SQLMI1.
D. Modify the diagnostic settings for SQLMI1.
Selected Answer: D
Question #: 46
Topic #: 3
You have an Azure SQL database named DB1 in the General Purpose service tier.
The performance metrics for DB1 are shown in the following exhibit.
You need to reduce the Log IO percentage. The solution must minimize costs.
What should you do?
A. Change Service tier to Business Critical.
B. Increase the number of vCores.
C. Perform a checkpoint operation.
D. Change Recovery model to Simple.
Selected Answer: A
Question #: 46
Topic #: 6
You have an Azure subscription that contains three instances of SQL Server on Azure Virtual Machines.
You plan to implement a disaster recovery solution.
You need to be able to perform disaster recovery drills regularly. The solution must meet the following requirements:
• Minimize administrative effort for the recovery drills.
• Isolate the recovery environment from the production environment
What should you use?
A. native Microsoft SQL Server backup
B. Azure Site Recovery
C. Recovery Services vaults
D. Azure Backup
Selected Answer: B
Question #: 46
Topic #: 1
You have two on-premises Microsoft SQL Server 2019 instances named SQL1 and SQL2.
You need to migrate the databases hosted on SQL1 to Azure. The solution must meet the following requirements:
• The service that hosts the migrated databases must be able to communicate with SQL2 by using linked server connections.
• Administrative effort must be minimized.
What should you use to host the databases?
A. a single Azure SQL database
B. SQL Server on Azure Virtual Machines
C. Azure SQL Managed Instance
D. an Azure SQL Database elastic pool
Selected Answer: C
Question #: 47
Topic #: 3
You have an Azure SQL database named DB1 that contains a nonclustered index named index1.
End users report slow queries when they use index1.
You need to identify the operations that are being performed on the index.
Which dynamic management view should you use?
A. Sys.dm_exec_query_plan_stats
B. Sys.dm_db_index_physical_stats
C. Sys.dm_db_index_operational_stats
D. Sys.dm_db_index_useage_stats
Selected Answer: C
Question #: 47
Topic #: 6
You have an instance of SQL Server on Azure Virtual Machines named VM1.
You need to implement a disaster recovery solution that meets the following requirements:
• Returns the solution to an operational state within 15 minutes of a failure
• Can perform disaster recovery testing in an isolated environment
• Minimizes administrative effort
What should you include in the solution?
A. active geo-replication
B. auto-failover groups
C. Azure Site Recovery
D. a failover cluster instance (FCI)
Selected Answer: A
Question #: 48
Topic #: 6
You plan to deploy an Azure SQL managed instance.
You need to restore database backups across regions.
Which type of storage account should you use?
A. locally-redundant storage (LRS)
B. zone-redundant storage (ZRS)
C. geo-zone-redundant storage (GZRS)
D. geo-redundant storage (GRS)
Selected Answer: D
Question #: 48
Topic #: 1
You have a SQL Server on Azure Virtual Machines instance named SQLVM1 that was deployed by using an Azure Marketplace SQL Server 2019 Enterprise image.
You need to change the Microsoft SQL Server instance on SQLVM1 to the Standard edition. The solution must ensure licensing compliance.
What should you do first?
A. From the SQL Server Installation Center on SQLVM1, run the Edition Upgrade wizard.
B. From SQLVM1, uninstall the SQL Server instance.
C. From the SQL Server Installation Center on SQLVM1, run the Repair wizard.
D. From the Azure portal, reconfigure SQLVM1.
Selected Answer: D
Question #: 49
Topic #: 2
Case study –
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study –
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.
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.
Existing Environment. 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.
Existing Environment. 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.
Existing Environment. 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.
Requirements. 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.
Requirements. 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.
Requirements. 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.
Requirements. Business Requirements
ADatum identifies the following business requirements:
• Minimize costs whenever possible, without affecting other requirements.
• Minimize administrative effort.
You need to recommend a process to automate the management of DB3. The solution must meet the management requirements.
What should be the first step of the process?
A. Configure Microsoft Entra authentication for the logical server that hosts DB3.
B. Configure a private endpoint for connectivity to DB3.
C. Create database-scoped credentials in DB3.
D. Create a database that has database-scoped credentials.
Selected Answer: C
Question #: 49
Topic #: 6
You have an Azure SQL database named DB1.
You need to ensure that DB1 will support automatic failover without data loss if a datacenter fails. The solution must minimize costs.
Which deployment option and pricing tier should you configure?
A. Azure SQL Database Premium
B. Azure SQL Database Basic
C. Azure SQL Database managed instance General Purpose
D. Azure SQL Database Hyperscale
Selected Answer: A
Question #: 49
Topic #: 1
Your on-premises network contains a Microsoft SQL Server 2016 server that hosts a database named db1.
You have an Azure subscription.
You plan to migrate db1 to an Azure SQL managed instance.
You need to create the SQL managed instance. The solution must minimize the disk latency of the instance.
Which service tier should you use?
A. Business Critical
B. Hyperscale
C. General Purpose
D. Premium
Selected Answer: A
Question #: 50
Topic #: 1
You have an Azure subscription.
You need to deploy an Azure SQL database. The solution must meet the following requirements:
• Dynamically scale CPU resources.
• Ensure that the database can be paused to reduce costs.
What should you use?
A. the Business Critical service tier
B. the serverless compute tier
C. an elastic pool
D. the General Purpose service tier
Selected Answer: B
Question #: 50
Topic #: 6
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?
A. Deploy VM1 and VM2 to a single proximity placement group.
B. Deploy VM1 and VM2 to different proximity placement groups in the same Azure region.
C. Connect VM1 and VM2 to a single subnet.
D. Connect VM1 and VM2 to different subnets on a single virtual network.
Selected Answer: C
Question #: 50
Topic #: 3
You have an Azure SQL database named DB1.
You need to query the fragmentation information of data and indexes for the tables in DB1.
Which command should you run?
A. sys.dm_db_index_usage_stats
B. DBCC CHECKALLOC
C. DBCC SHOWCONTIG
D. sts.dm_db_index_physical_stats
Selected Answer: D
Question #: 51
Topic #: 3
You have an Azure SQL managed instance named SQLMI1 that has the following settings:
• vCores: 4
• Service tier: General Purpose
• Hardware generation: Standard-series (Gen5)
You discover that memory pressure on SQLMI1 is high.
You need to reduce the memory pressure on SQLMI1. The solution must minimize costs.
What should to do?
A. Enable the Query Store.
B. Change vCores to 8.
C. Change Hardware generation to Premium-series.
D. Change Service tier to Business Critical.
Selected Answer: B
Question #: 51
Topic #: 2
Case study –
This is a case study. Case studies are not timed separately. You can use as much exam time as you would like to complete each case. However, there may be additional case studies and sections on this exam. You must manage your time to ensure that you are able to complete all questions included on this exam in the time provided.
To answer the questions included in a case study, you will need to reference information that is provided in the case study. Case studies might contain exhibits and other resources that provide more information about the scenario that is described in the case study. Each question is independent of the other questions in this case study.
At the end of this case study, a review screen will appear. This screen allows you to review your answers and to make changes before you move to the next section of the exam. After you begin a new section, you cannot return to this section.
To start the case study –
To display the first question in this case study, click the Next button. Use the buttons in the left pane to explore the content of the case study before you answer the questions. Clicking these buttons displays information such as business requirements, existing environment, and problem statements. If the case study has an All Information tab, note that the information displayed is identical to the information displayed on the subsequent tabs. When you are ready to answer a question, click the Question button to return to the question.
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.
Existing Environment. 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.
Existing Environment. 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.
Existing Environment. 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.
Requirements. 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.
Requirements. 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.
Requirements. 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.
Requirements. 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 that will enable remote developers to access DB1 and DB2. The solution must support the planned changes and meet the security requirements.
What should you include in the recommendation?
A. a public endpoint via a database-level firewall rule
B. a Point-to-Site (P2S) VPN
C. a public endpoint via a server-level firewall rule
D. a private endpoint
Selected Answer: D
Question #: 52
Topic #: 1
You have a Microsoft SQL Server 2017 server.
You need to migrate the server to Azure. The solution must meet the following requirements:
• Ensure that the latest version of SQL Server is used.
• Support the SQL Server Agent service.
• Minimize administrative effort.
What should you use?
A. an Azure SQL Database elastic pool
B. Azure SQL Database
C. SQL Server on Azure Virtual Machines
D. Azure SQL Managed Instance
Selected Answer: D
Question #: 52
Topic #: 3
You have five instances of SQL Server on Azure Virtual Machines.
You need to monitor Microsoft SQL Server performance for all the instances by consolidating metrics into a single graphic display. The solution must minimize administrative effort.
What should you use?
A. Azure Monitor
B. Log Analytics
C. SQL Insights
D. Azure SQL Analytics
Selected Answer: A
Question #: 53
Topic #: 3
You have an instance of SQL Server on Azure Virtual Machines named SQL1.
SQL1 contains an Extended Events session named session1 that captures Microsoft SQL Server events.
You need to correlate the session events with events captured by Event Tracing for Windows (ETW).
What should you do for session1?
A. Modify the Set Session Event Filters settings.
B. Add a target.
C. Add an action.
D. Modify the Specify Session Data Storage settings.
Selected Answer: B
Question #: 54
Topic #: 3
You have an Azure subscription that contains the following resources:
• 10 Azure SQL databases
• Five Azure SQL managed instances
• Five instances of SQL Server on Azure Virtual Machines
You need to implement a centralized monitoring solution for all the Azure SQL resources. The solution must minimize administrative effort.
What should you include in the solution?
A. Log Analytics
B. Azure SQL Analytics
C. Query Performance Insight
D. SQL Insights
Selected Answer: D
Question #: 54
Topic #: 1
You have an on-premises Microsoft SQL Server 2019 database named SQL1 that uses merge replication.
You need to migrate SQL1 to Azure.
Which service should you use?
A. Azure SQL Edge
B. Azure SQL Database
C. SQL Server on Azure Virtual Machines
D. Azure SQL Managed Instance
Selected Answer: B
Question #: 55
Topic #: 1
You have an on-premises datacenter that contains a 2-TB Microsoft SQL Server 2019 database named DB1.
You need to recommend a solution to migrate DB1 to an Azure SQL managed instance. The solution must minimize downtime and administrative effort.
What should you include in the recommendation?
A. Log Replay Service (LRS)
B. log shipping
C. transactional replication
D. SQL Data Sync
Selected Answer: B
Question #: 56
Topic #: 1
You have an Azure subscription.
You plan to deploy an instance of SQL Server on Azure Virtual Machines that supports Write Accelerator.
Which virtual machine series should you use?
A. E-series
B. G-series
C. H-series
D. M-series
Selected Answer: B
