Skip to main content

Microsoft Certified: Azure Database Administrator Associate (DP-300)

The Microsoft Certified: Azure Database Administrator Associate (DP-300) validates your skills in managing and maintaining cloud-based and hybrid data platforms built with Microsoft SQL Server and Azure SQL services. It is a specialized, associate-level credential for DBAs who ensure high availability, security, and performance of relational databases.



---------- Question 1
An Azure SQL Database is suffering from unpredictable performance spikes. You suspect that certain queries are experiencing performance regression due to suboptimal execution plans being chosen after statistics updates. You need to identify the regressed queries and ensure that the database engine automatically reverts to a previous stable plan if a regression is detected. Which two actions should you take?
  1. Query the sys.dm_os_wait_stats DMV and manually rebuild all indexes that have high fragmentation.
  2. Use the Query Store to identify the regressed queries and enable Automatic Plan Correction in the tuning options.
  3. Set up an Azure Monitor alert for high CPU usage and run a SQL Profiler trace when the alert triggers.
  4. Enable the Resource Governor to limit the memory usage of ad-hoc queries and prevent them from impacting the cache.

---------- Question 2
You need to automate the deployment of a standardized Azure SQL Database environment across multiple regions for a development team. The deployment must include the logical server, the database, and specific firewall rules. The solution must be idempotent and allow for version control. Which technology should you use to create and manage these deployments most effectively?
  1. Create a series of PowerShell scripts using the Az.Sql module and run them through a local scheduled task.
  2. Develop a Bicep template that defines the server, database, and firewall resources and deploy it using a GitHub Actions workflow.
  3. Use the Azure CLI directly in the portal Cloud Shell to manually execute create commands for each region.
  4. Utilize the Export Template feature in the Azure Portal to download a JSON file and manually upload it for each new deployment.

---------- Question 3
An Azure SQL Database is experiencing high CPU usage during peak business hours. You have enabled Query Store and identified several queries that are performing poorly. You notice that the execution plans for these queries are frequently changing, leading to inconsistent performance. You want to implement a solution that automatically chooses the best known execution plan for these queries. Which feature should you enable?
  1. Intelligent Insights with Azure Monitor Logs
  2. Automatic Tuning with force plan enabled
  3. SQL Server Profiler with a captured trace
  4. Adaptive Query Processing with memory grant feedback

---------- Question 4
A healthcare provider must ensure that sensitive patient records stored in an Azure SQL Managed Instance are never visible to database administrators or Azure engineers, even in memory during query processing. The solution must support equality comparisons on encrypted columns to allow for searches by social security number. Which security technology should be implemented to meet these stringent privacy and compliance requirements?
  1. Transparent Data Encryption with Customer-Managed Keys
  2. Dynamic Data Masking with custom string masking
  3. Always Encrypted with virtualization-based security (VBS) enclaves
  4. Azure SQL Database Ledger for non-repudiation

---------- Question 5
A company is planning to migrate a legacy on-premises application that relies heavily on SQL Server Agent jobs, cross-database queries, and Service Broker. The organization wants to move to a fully managed platform as a service (PaaS) to reduce administrative overhead for operating system patching, yet they must maintain nearly 100 percent compatibility with the SQL Server database engine. Which Azure SQL deployment option should you recommend to meet these requirements?
  1. Azure SQL Database Single Database
  2. Azure SQL Database Elastic Pool
  3. Azure SQL Managed Instance
  4. SQL Server on Azure Virtual Machines

---------- Question 6
A financial services company needs to ensure that specific columns containing personally identifiable information (PII) in an Azure SQL Database are never visible to the database administrators (DBAs) or Azure support staff, even in memory or during processing. The data must be encrypted on the client side and only be accessible to authorized applications that possess the correct column master key. Which security feature should you implement?
  1. Transparent Data Encryption (TDE) with Customer-Managed Keys
  2. Dynamic Data Masking (DDM) on the PII columns
  3. Always Encrypted with VBS enclaves
  4. Azure SQL Database Ledger

---------- Question 7
You are designing a disaster recovery strategy for an Azure SQL Database that supports a global e-commerce platform. The business requirements specify a Recovery Time Objective (RTO) of 30 minutes and a Recovery Point Objective (RPO) of 5 seconds. The solution must support a readable secondary in a different geographical region for reporting purposes. Which feature provides the best fit?
  1. Active Geo-Replication.
  2. Auto-Failover Groups.
  3. Geo-redundant backup storage (GRS).
  4. Temporal Tables.

---------- Question 8
You need to automate the deployment of several Azure SQL Databases across development and testing environments. The deployment must be repeatable, idempotent, and defined in a human-readable declarative language that integrates well with Azure DevOps. You also need to manage the logical server and firewall rules in the same deployment. Which tool is most appropriate?
  1. A series of PowerShell scripts using the Az.Sql module to create resources sequentially.
  2. A Bicep template that defines the SQL resources and their configurations in a modular format.
  3. A T-SQL script executed via the Azure Portal Query Editor to create the databases and users.
  4. The Azure CLI run manually in the Cloud Shell to provision each resource one by one.

---------- Question 9
A DevOps team wants to implement Infrastructure as Code (IaC) to ensure that every new Azure SQL Database deployment is consistently configured with a specific firewall rule set and that the Microsoft Defender for SQL setting is always enabled. They prefer a declarative language that is native to the Azure platform. Which technology should they use to define these resources?
  1. Azure CLI scripts.
  2. Bicep templates.
  3. T-SQL ALTER SERVER CONFIGURATION commands.
  4. Azure Portal Blueprints.

---------- Question 10
You are designing a data partitioning strategy for a multi-tenant Azure SQL Database that contains a massive Sales table. The table grows by 50 GB every month. You need to implement a solution that improves query performance for time-based reports and simplifies the archival of old data. What is the most effective way to implement this requirement using T-SQL?
  1. Create a Sharding Map using the Elastic Database client library
  2. Implement Vertical Partitioning by moving non-key columns to a separate table
  3. Implement Table Partitioning by creating a partition function and a partition scheme
  4. Use Azure Data Factory to move data to a cold storage blob monthly

Are they useful?
Click here to get 360 more questions to pass this certification at the first try! Explanation for each answer is included!

Follow the below LINKEDIN channel to stay updated about 89+ exams!

Comments

Post a Comment

Popular posts from this blog

Microsoft Certified: Azure Fundamentals (AZ-900)

The Microsoft Certified: Azure Fundamentals (AZ-900) is the essential starting point for anyone looking to validate their foundational knowledge of cloud services and how those services are provided with Microsoft Azure. It is designed for both technical and non-technical professionals ---------- Question 1 A new junior administrator has joined your IT team and needs to manage virtual machines for a specific development project within your Azure subscription. This project has its own dedicated resource group called dev-project-rg. The administrator should be able to start, stop, and reboot virtual machines, but should not be able to delete them or modify network configurations, and crucially, should not have access to virtual machines or resources in other projects or subscription-level settings. Which Azure identity and access management concept, along with its appropriate scope, should be used to grant these specific permissions? Microsoft Entra ID Conditional Access, applied at...
Post a Comment
Read more

Google Associate Cloud Engineer

The Google Associate Cloud Engineer (ACE) certification validates the fundamental skills needed to deploy applications, monitor operations, and manage enterprise solutions on the Google Cloud Platform (GCP). It is considered the "gatekeeper" certification, proving a candidate's ability to perform practical cloud engineering tasks rather than just understanding theoretical architecture.  ---------- Question 1 Your team is developing a serverless application using Cloud Functions that needs to process data from Cloud Storage. When a new object is uploaded to a specific Cloud Storage bucket, the Cloud Function should automatically trigger and process the data. How can you achieve this? Use Cloud Pub/Sub as a message broker between Cloud Storage and Cloud Functions. Directly access Cloud Storage from the Cloud Function using the Cloud Storage Client Library. Use Cloud Scheduler to periodically check for new objects in the bucket. Configure Cloud Storage to directly ca...
Post a Comment
Read more

CompTIA Cybersecurity Analyst (CySA+)

CompTIA Cybersecurity Analyst (CySA+) focuses on incident detection, prevention, and response through continuous security monitoring. It validates a professional's expertise in vulnerability management and the use of threat intelligence to strengthen organizational security. Achieving the symbol COMP_CYSA marks an individual as a proficient security analyst capable of mitigating modern cyber threats. ---------- Question 1 A security analyst is reviewing logs in the SIEM and identifies a series of unusual PowerShell executions on a critical application server. The logs show the use of the -EncodedCommand flag followed by a long Base64 string. Upon decoding, the script appears to be performing memory injection into a legitimate system process. Which of the following is the most likely indicator of malicious activity being observed, and what should be the analysts immediate technical response using scripting or tools? The activity indicates a fileless malware attack attempting to ...
Post a Comment
Read more