Quiz DP-300: Administering Relational Databases on Microsoft Azure

The correct answer is DMA with DMS in online mode. This approach allows the source database to remain
available during migration, minimizing application downtime—critical for production systems. DMA assesses
compatibility and identifies potential issues before migration begins, while DMS handles the actual data
transfer with built-in validation and minimal downtime. Online mode enables continuous synchronization of
changes during the migration window.
Backup/restore works for smaller databases but requires taking the source offline and doesn't validate schema
compatibility. BACPAC export is useful for smaller, non-critical databases but is slower for 5 TB datasets and
doesn't validate compatibility. Transactional replication requires manual switchover logic and is more complex
than DMS for this scenario.

The correct answers are using Query Store and Intelligent Insights to analyze performance trends and
enabling automated tuning recommendations. Query Store captures query execution statistics and plan
choices, allowing you to identify which queries degraded and why. Intelligent Insights analyzes these patterns
and provides root-cause diagnosis. Automated tuning reviews the insights and recommends corrective actions
like index creation or plan forcing, which can be applied safely. This data-driven approach identifies the actual
problem before taking costly scaling actions.
Scaling up immediately is inefficient and expensive if the issue is a missing index or suboptimal query plan.
Archiving data is unlikely to be the root cause of a sudden performance change when the application code is
unchanged. Forcing recompilation without evidence is disruptive and may not resolve the issue. The diagnostic
tools (Query Store, Intelligent Insights, and automated tuning) should always be your first step for performance
troubleshooting in Azure SQL.

The correct answers are the first two options. TDE encrypts data at rest and is the baseline encryption for
Azure SQL Database, protecting stored data from unauthorized access. Enforcing Minimum TLS version 1.2
ensures all connections—whether over the internet or ExpressRoute—use secure encryption in transit.
Additionally, managing TDE keys via Azure Key Vault provides centralized key management, rotation, and
compliance auditing, which is a best practice for sensitive data.
Always Encrypted adds application-level encryption for specific columns, but the question asks for a
comprehensive strategy covering both at-rest and in-transit encryption for all sensitive data; TDE + TLS is the
foundational layer. Firewall rules provide network isolation but do not encrypt data itself, so they cannot satisfy
encryption compliance requirements alone. Disabling TLS on a private network is a compliance
violation—encryption in transit is required regardless of network topology, and ExpressRoute connections still
traverse external infrastructure.

The correct answer is automated backups with 15-minute frequency plus a geo-redundant read-only
replica. Azure SQL Database's automated backups, combined with geo-replication, achieves the RPO of 15
minutes through continuous backup sampling and replication lag. A read-only geo-replica provides a standby
copy in another region, supporting failover within approximately 1 hour (or faster with a failover group as an
enhancement). This approach is cost-effective for 200 GB and meets both RTO and RPO targets.
A failover group (second option) does provide automatic failover but is typically deployed alongside a
secondary database in another region; failover groups alone don't reduce RTO beyond what the replica
configuration allows, and the RTO depends on the replica lag, not on the failover group alone. Daily backups
cannot meet a 15-minute RPO. Always On availability groups are an on-premises or IaaS SQL Server feature,
not native to Azure SQL Database managed service.

The correct answer is Azure SQL Database Elastic Jobs. This is the native Azure SQL Database service that
replaces SQL Server Agent functionality, enabling you to create, schedule, and manage jobs across multiple
databases in a scalable way. Elastic Jobs provide centralized job management, logging, and execution across
the entire database estate, making it ideal for this scenario.
SQL Server Agent on an Azure VM introduces unnecessary complexity and licensing overhead when a native
managed solution exists. Logic Apps and Automation runbooks are general-purpose orchestration tools that
could work but are less efficient for database-specific maintenance tasks and don't provide native SQL Server
Agent-like job scheduling. Elastic Jobs is the purpose-built, Azure-native solution for this use case.

The correct answers are upgrading to a higher service tier and implementing read-only replicas with
read-scale-out. Scaling up to a higher tier increases CPU and memory resources, directly addressing the
bottleneck. Read-only replicas (or read-scale-out) offload SELECT queries from the primary database, reducing
CPU pressure on the primary instance. These are the two horizontal and vertical scaling options that address
the CPU constraint at the database level.
Connection pooling on the application side (third option) helps manage connection overhead but doesn't
increase database CPU capacity—the connections still execute queries that consume CPU. Adding storage
(fourth option) does not address CPU bottlenecks; storage is separate from compute. Caching at the application
layer (fifth option) may reduce query volume over time, but it's an indirect, slow-to-implement solution when
CPU resources are undersized for current load. The immediate solutions are scaling the compute tier and
offloading read workloads.
CASE STUDY 1
Existing Environment
Network Environment
The manufacturing and research datacenters connect to the primary datacenter by using a VPN.
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.

SalesSQLDb1 experiences performance issues that are likely due to out-of-date statistics and
frequent blocking queries.
A: Use sys.dm_pdw_nodes_tran_locks instead of sys.dm_tran_locks from Azure Synapse Analytics
(SQL Data Warehouse) or Parallel Data Warehouse.
E: Example:
The following query will show blocking information.
SELECT
t1.resource_type,
t1.resource_database_id,
t1.resource_associated_entity_id,
t1.request_mode,
t1.request_session_id,
t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
ON t1.lock_owner_address = t2.resource_address;
Note: Depending on the system you’re working with you can access these wait statistics from one of
three locations:
sys.dm_os_wait_stats: for SQL Server
sys.dm_db_wait_stats: for Azure SQL Database
sys.dm_pdw_nodes_os_wait_stats: for Azure SQL Data Warehouse
Incorrect Answers:
F: sys.dm_tran_locks returns information about currently active lock manager resources in SQL
Server 2019 (15.x). Each row represents a currently active request to the lock manager for a lock that
has been granted or is waiting to be granted.
Instead use sys.dm_pdw_nodes_tran_locks.
Reference:
https://docs.microsoft.com/en-us/sql/relational-databases/system-dynamic-management-
views/sys-dm-tran-locks-transact-sql