Plan and implement data platform resources

You are managing an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has a table named 'Contacts' with columns: ContactID (int, primary key), Name (nvarchar(100)), Email (nvarchar(200)), Phone (nvarchar(20)), and CreditLimit (decimal(18,2)). The compliance team requires that the CreditLimit column be encrypted so that only authorized users can view it. The application must be able to search for exact matches on CreditLimit values. You need to implement encryption without changing the application code significantly. Which encryption method should you use?

You manage an Azure SQL Database that supports a critical web application. The database is currently configured with the General Purpose service tier and locally redundant backup storage. The compliance team requires that all backups be stored in a paired Azure region to ensure availability during a regional outage. You need to change the backup storage redundancy without affecting the database availability. What should you do?

You are troubleshooting a performance issue on Azure SQL Database. The database uses the General Purpose tier with 100 DTUs. Users report intermittent slowdowns during peak hours. Query Store shows frequent waits for RESOURCE_SEMAPHORE. What is the most likely cause?

You are deploying an Azure SQL Database using PowerShell as shown in the exhibit. The database will be used by a development team that works intermittently. You need to ensure the database is cost-effective while being available on demand. What is the purpose of the AutoPauseDelayInMinutes parameter?

You are deploying an Azure SQL Database and need to ensure that the database files are encrypted at rest using a key that you manage in Azure Key Vault. You also need to ensure that the key is automatically rotated. What should you configure?

You are deploying an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The instance must be placed in a dedicated subnet within an Azure virtual network to allow communication with other Azure resources and on-premises systems over a site-to-site VPN. You need to configure the network environment. What should you do?

You are planning the deployment of an Azure SQL Managed Instance to support a lift-and-shift migration of an on-premises SQL Server 2019 workload. The workload requires the ability to run cross-database queries, use SQL Server Agent, and support Service Broker. You need to choose a service tier that provides the highest availability and lowest latency for I/O-intensive operations. The budget allows for premium storage performance. Which service tier should you select?

You are migrating an on-premises SQL Server 2012 database to Azure SQL Managed Instance. The database is 5 TB and uses Transparent Data Encryption (TDE) with a certificate stored in the local machine store. What is the best approach to migrate while preserving TDE?

You are deploying a new Azure SQL Database for an application that will store sensitive financial data. The compliance team requires that the database be configured to automatically detect and alert on anomalous access patterns, and that all queries be logged for auditing. Which services should you enable?

Your company is migrating several on-premises SQL Server databases to Azure. The databases range from 50 GB to 2 TB and have varying performance requirements. You need to decide which Azure SQL deployment options to use. The requirements include: - Minimal application changes. - Support for SQL Server Agent jobs. - Ability to scale storage independently from compute. - Native support for cross-database queries. Which TWO options meet these requirements? (Choose two.)

You are a database administrator for a company that uses Azure SQL Database. The company wants to reduce the cost of storing backups. You need to configure the backup storage redundancy to the most cost-effective option while ensuring data durability within a single region. What should you do?

You are deploying a new Azure SQL Database for an internal HR application. The database will store employee records and must be encrypted at rest using a key that your organization rotates every 90 days. The key must be stored in Azure Key Vault and must not be accessible to Microsoft. You need to configure Transparent Data Encryption (TDE) to meet these requirements. What should you do first?

You are configuring a new Azure SQL Database. The application that will use the database requires read-only access to the database from an Azure App Service. You need to ensure that the application connects securely without embedding credentials in code, and that access is limited to the minimum required permissions. What should you do?

You are configuring an Azure SQL Database elastic pool for a SaaS application. The pool will host 50 databases with varying workloads. You need to minimize cost while ensuring performance meets baseline requirements. Which tier and configuration should you choose?

Your team is migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses Service Broker for cross-database messaging. The compliance requirement mandates that the migration must be performed with minimal downtime and that the target must support the Service Broker feature. What migration strategy should you recommend?

You have an Azure SQL Database that is used by a development team. The team works only during business hours and the database can be unavailable outside those hours. You need to minimize compute cost while allowing the database to automatically pause when it is idle and resume when a connection is made. What should you configure?

You are designing a disaster recovery plan for an Azure SQL Database that uses the Business Critical tier. The database is deployed in the West US region. You need to ensure that if the entire West US region becomes unavailable, the database can be failed over to a secondary region with minimal data loss. What should you implement?

You are planning to deploy a new Azure SQL Database. The database will store sensitive financial data and must be encrypted at rest using a key that your organization manages and rotates independently. You need to implement this encryption with minimal administrative overhead. What should you do?

Your company is deploying a new application that uses an Azure SQL Database. The security policy requires that all connections use Microsoft Entra ID authentication and that no SQL authentication users are created. Which server-level setting should you enforce?

You are deploying a new Azure SQL Database for a line-of-business application. The application's usage pattern is unpredictable, with long idle periods overnight and short bursts of heavy activity during business hours. Cost optimization is a priority, and the database can tolerate a brief reconnection delay when scaling. You need to select a purchasing model and service tier that minimizes cost while automatically adjusting compute resources. What should you do?

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database. You need to ensure that tenant data is isolated and that performance is predictable. Cost efficiency is important. Which deployment model should you use?

You are deploying an Azure SQL Database for a new application. The database must be encrypted at rest using Transparent Data Encryption (TDE) with a customer-managed key stored in Azure Key Vault. You need to ensure that the key is automatically rotated every 90 days and that the database remains accessible if the key is rotated. What should you configure?

You are configuring security for an Azure SQL Database. You need to ensure that only traffic from a specific virtual network and a specific set of public IP addresses can connect to the database. Which two features should you enable?

Which TWO benefits does the Hyperscale service tier of Azure SQL Database provide?

You are designing a backup strategy for an Azure SQL Database. The database is in the General Purpose service tier and is used for a critical application. You need to ensure that you can restore the database to any point in time within the last 30 days, and you need to retain backups for 10 years for compliance. (Choose two.)

You are a database administrator for a healthcare organization. You need to deploy a new Azure SQL Database that stores protected health information (PHI). The database must be encrypted at rest using a customer-managed key in Azure Key Vault. Additionally, you need to ensure that backups are also encrypted with the same key. Which configuration should you use?

You are deploying a new Azure SQL Database for a small internal inventory application. The workload is steady, with predictable usage during business hours, and the application team requires a fixed monthly cost with no risk of unexpected overage charges. You need to choose a purchasing model and service tier that meets these requirements. What should you do?

You are deploying an Azure SQL Database and need to ensure that the database files are encrypted at rest using a key that your organization manages and can revoke. The key must be stored in Azure Key Vault and rotated regularly. You also need to ensure that the database remains accessible during key rotation. What should you do?

A healthcare company is required to encrypt all patient data at rest and in transit. They are deploying Azure SQL Database. Which combination of features should they implement to meet this requirement?

You are configuring Azure SQL Database for a new e-commerce application that must support high read throughput for product catalog queries. The application uses Entity Framework Core and requires that read-only queries be offloaded to a secondary replica to reduce load on the primary. Which feature should you enable?

Your Azure SQL Managed Instance is experiencing performance degradation. You suspect a query plan regression caused by parameter-sensitive plan issues. Which feature should you use to identify and resolve the issue?

Match each Azure SQL Database service tier to its description.

You are deploying a new Azure SQL Database for a line-of-business application. The application's workload is unpredictable, with periods of near-zero activity overnight and heavy transactional bursts during business hours. You need to choose a purchasing model and service tier that minimizes cost while automatically scaling compute resources based on demand. What should you do?

You are configuring an Azure SQL Database to support a mission-critical application. The database is in the Business Critical service tier and uses zone redundancy. You need to ensure that the database remains available even if an entire Azure region becomes unavailable. The solution must minimize data loss and provide automatic failover. What should you do?

You are analyzing the SQL script in the exhibit. This script is used to query data stored in Azure Blob Storage from Azure SQL Database. What is the primary purpose of the database scoped credential?

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses a SQL Server Agent job that runs a PowerShell script to process files. You need to ensure the job continues to run after migration with minimal changes. What should you do?

Which TWO are valid ways to secure access to an Azure SQL Database?

Your company is deploying a new application that will use Azure SQL Database. You need to ensure that all connections to the database use Microsoft Entra ID authentication. Which step is required to enable this?

You need to create an Azure SQL Database that will be used by a new application. The database must support JSON data storage and querying. Which data type should you use to store JSON documents?

You are deploying an Azure SQL Managed Instance for a legacy application that requires SQL Server Agent jobs and cross-database queries. The instance must be able to access an on-premises file share for backup and restore operations. You need to configure network connectivity so that the managed instance can communicate with the on-premises network. What should you implement?

You are configuring an Azure SQL Database to meet a compliance requirement that mandates encryption of data at rest with customer-managed keys and the ability to audit all access to the database. You need to implement the necessary features. Which two actions should you perform? (Choose two.)

You are deploying an Azure SQL Managed Instance to support a lift-and-shift migration of an on-premises SQL Server database. The application requires the ability to restore a database from a backup taken on an on-premises SQL Server 2019 instance. The backup file is stored in an Azure Storage account. You need to restore the database to the managed instance. What should you do first?

You have an Azure SQL Database with Query Store enabled. You notice that a critical stored procedure has regressed in performance. You need to force a previous, better-performing execution plan for that query. What should you do?

Which TWO actions are required to enable Microsoft Entra ID authentication for an Azure SQL Database?

Your company is deploying a multi-tenant application using Azure SQL Database. Each tenant gets its own database. You need to manage resources efficiently while ensuring performance isolation between tenants. The number of tenants fluctuates, and you want to minimize cost. What is the best strategy?

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The instance must support cross-database queries, SQL Server Agent jobs, and Service Broker. You need to ensure that the instance can handle the expected IOPS and throughput requirements. Which configuration should you implement?

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. The database uses SQL Server Agent jobs, Service Broker, and cross-database queries within the same instance. Which PaaS option should you choose?

You are designing a new Azure SQL Database for a critical OLTP workload. The database will be used by a global application with users in North America, Europe, and Asia. The primary requirement is low-latency reads for all regions. You need to choose a deployment option that supports geo-distributed reads and provides a single write endpoint. Which option should you select?

You are planning to migrate an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses cross-database queries and SQL Server Agent jobs. You need to ensure that the migration supports these features with minimal changes. What should you do first?

You are deploying an Azure SQL Database for a new line-of-business application. The database must remain fully available during planned maintenance windows and provide a secondary copy in a different Azure region for disaster recovery. You need to configure the deployment to meet these requirements with minimal administrative effort. What should you implement?

Which THREE of the following are required steps to configure a failover group for an Azure SQL Database with a readable secondary in a different region?

You are deploying an Azure SQL Database for a new line-of-business application. The application's usage pattern is unpredictable, with long idle periods and occasional bursts of heavy read/write activity. You need to minimize compute cost while ensuring the database automatically scales compute resources based on workload demand. The database must remain online during scaling operations. What should you do?

You need to migrate an on-premises SQL Server 2019 database to Azure SQL Database with minimal downtime. The database is 500 GB and uses some features not supported in Azure SQL Database, such as FileTables. What is the best migration strategy?

You are planning to deploy a new Azure SQL Database for an internal HR application. The application is used only during business hours on weekdays and can tolerate a brief outage if the database needs to be scaled. To minimize cost, you need the database to automatically scale compute resources based on workload demand and allow the database to be paused when not in use. Which purchasing model and service tier should you choose?

You are a database administrator for a financial services company that uses Azure SQL Database. The company must ensure that all database backups are encrypted with a customer-managed key stored in Azure Key Vault. You need to configure the database to meet this requirement. What should you do first?

You are deploying an Azure SQL Database that will be used by a global application. You need to ensure that read-intensive workloads are offloaded from the primary database to improve performance. Which feature should you enable?

You are deploying an Azure SQL Database and need to enforce that all connections to the database use encrypted channels and that the server presents a specific certificate that the client validates. You also need to ensure that the database cannot be accessed from the public internet except through a private endpoint. Which two actions should you perform? (Choose two.)

Your company has an Azure SQL Managed Instance that hosts multiple databases. You need to implement a solution to automatically detect and alert on potential SQL injection attacks. The solution must integrate with Microsoft Sentinel for incident response. What should you configure?

You are monitoring an Azure SQL Database and notice a pattern of high CPU usage during business hours. You need to identify the queries consuming the most CPU over the last 24 hours. Which dynamic management view should you query?

Drag and drop the steps to configure geo-replication for an Azure SQL Database in the correct order.

You are managing an Azure SQL Database that experiences periods of high CPU usage. You need to identify the top resource-consuming queries and their execution plans to optimize performance. You want to use a built-in feature that provides historical query performance data with minimal configuration. What should you use?

You manage an Azure SQL Database that is experiencing performance degradation during peak hours. You suspect that the current pricing tier is insufficient. You need to increase performance with minimal downtime. Which action should you take?

You need to ensure that all queries executed against an Azure SQL Database are audited and logged to a Log Analytics workspace for security analysis. Which feature should you enable?

You are evaluating the configuration of an Azure SQL Database as shown in the exhibit. You need to ensure that the database remains available during a zonal failure without data loss. Which feature contributes to this requirement?

You are responsible for cost optimization of a non-production Azure SQL Database that is used for development testing. The database is only active during business hours (9 AM to 5 PM) on weekdays. Which compute tier and configuration would minimize cost while ensuring the database is available during working hours?

You are configuring Azure SQL Database for a new application. The security policy requires that all connections use Microsoft Entra authentication and that the database blocks IP addresses from outside your corporate network. You also need to ensure that the application can connect without storing credentials in code. Which combination of features should you implement?

Which TWO actions are required to implement transparent data encryption (TDE) with customer-managed keys for an Azure SQL Database?

You need to deploy an Azure SQL Database that automatically scales compute resources based on workload demand, with minimal administrative overhead. The database should scale between a minimum and maximum number of vCores. Which purchasing model and service tier should you use?

You are designing a security strategy for Azure SQL Database. You need to ensure that database access is secured using Microsoft Entra ID (formerly Azure Active Directory) authentication. Which THREE actions should you take? (Choose THREE.)

You are deploying Azure SQL Database for a new application. You need to ensure that connections from Azure services use a private IP address and do not traverse the public internet. What should you configure?

A DBA needs to create a new Azure SQL Database and wants to ensure that the database automatically fails over to a secondary region without manual intervention. The recovery point objective (RPO) is 5 seconds. What should the DBA configure?

You are deploying Azure SQL Database for a multi-tenant SaaS application. Each tenant has its own database, and you need to ensure that resource usage is isolated and predictable. You also need to manage performance at the tenant level. Which Azure SQL Database offering should you choose?

You manage an Azure SQL Database that is used by a reporting application. The application runs large analytical queries during business hours and you need to isolate these read-only workloads from the primary database to avoid performance impact. You want to use the built-in read-only replica without changing the application connection string to a different server. What should you configure?

You need to migrate an on-premises SQL Server 2017 database to Azure SQL Database. The database contains a table with a column of data type geography and uses cross-database queries. You must determine the appropriate target platform. What should you do?

A company is planning to migrate their on-premises SQL Server databases to Azure SQL Managed Instance. They have a database that uses SQL Server Agent jobs with proxies and also uses cross-database queries extensively. What is the main consideration for this migration?

Your company is adopting Microsoft Defender XDR for enhanced security. You need to enable Microsoft Defender for SQL for your Azure SQL Database to receive security alerts and vulnerability assessments. What is the first step you must take?

You need to deploy a new Azure SQL Database that will store sensitive financial data. The database must use customer-managed keys for encryption at rest, and the keys must be stored in Azure Key Vault. You want to ensure that the keys are automatically rotated and that the database remains available during rotation. What should you configure?

Your company requires that all Azure SQL Databases use Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The security policy mandates that the key must be rotated every 90 days. You need to implement this requirement with minimal administrative overhead. What should you do?

Which TWO of the following are supported high availability features in Azure SQL Managed Instance?

You are configuring managed backup for an Azure SQL Managed Instance as shown in the exhibit. What is the purpose of this configuration?

You have an Azure SQL Database with sensitive customer data. You need to mask the credit card numbers so that only users with the 'Unmask' permission can see the full number. Non-privileged users should see only the last four digits. Which feature should you implement?

You are designing a solution for storing audit logs from Azure SQL Database. The logs must be retained for 7 years and must be immutable to prevent tampering. Which Azure service should you use?

You are deploying an Azure SQL Database for a new line-of-business application. The application's workload is unpredictable, with periods of near-zero activity and sudden bursts of high read/write volume. The database must scale compute resources automatically without manual intervention, and you want to minimize cost during idle periods. You create the database using the vCore purchasing model. Which service tier should you select?

You are reviewing an ARM template snippet that configures a long-term retention (LTR) policy for an Azure SQL Database. Based on the exhibit, how long will weekly backups be retained?

You are planning the deployment of a new Azure SQL Database for a line-of-business application. The application's workload is not yet known, and you must keep the monthly cost as low as possible while still being able to scale compute resources up or down without redeploying the database. You also need to ensure that storage is billed based on the actual data and log used rather than a pre-provisioned maximum. Which purchasing model and service tier should you choose?

You are evaluating Azure SQL Database for a new application that requires the database to be isolated from other Azure tenants and to have a dedicated compute and storage resources. The application also requires support for SQL Server Agent and cross-database queries. Which Azure SQL deployment option should you choose?

Your organization requires that all Azure SQL Database backups be retained for 10 years to meet compliance requirements. Which backup retention policy should you configure?

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. You need to implement a solution that allows applications to connect using their own managed identities without storing credentials in connection strings. What should you configure?

You are configuring security for an Azure SQL Database that will be accessed by multiple applications. Each application uses a separate service principal managed in Microsoft Entra ID. You need to ensure that each service principal has the minimum required permissions to access only its own set of tables. What should you implement?

You are configuring a new Azure SQL Database. The application that will use the database requires that all connections be encrypted and that the database be protected against SQL injection attacks. You also need to minimize administrative effort for monitoring and threat detection. What should you implement?

You are designing a data platform for a global SaaS company. The application requires a relational database that can handle up to 50 TB of data and supports high-frequency inserts. The database must be able to scale compute independently from storage and provide fast restores (within minutes) for large databases. Which Azure SQL offering should you choose?

Drag and drop the steps to troubleshoot a high CPU usage issue in Azure SQL Database in the correct order.

You are planning to deploy an Azure SQL Managed Instance to host several databases migrated from an on-premises SQL Server. The applications use cross-database queries, SQL Server Agent jobs, and CLR assemblies. You need to ensure the instance can support these features and that the network configuration allows the instance to be reached from an on-premises network over a site-to-site VPN. What should you do first?

You have an Azure SQL Managed Instance that is experiencing performance degradation. You suspect a query is causing excessive blocking. You need to identify the blocking chain and the resource holding the lock. Which DMV should you query?

You are reviewing an ARM template for creating a new Azure SQL Database. The template uses the above JSON to create a database named 'db2' from 'db1'. The source database 'db1' is currently in a failed state due to a storage issue. What will be the result of deploying this template?

You are a database administrator for a company that runs a SaaS application on Azure SQL Database. The application's workload is unpredictable, with rapid bursts of activity that last only a few minutes. You need to ensure that the database can handle these bursts without manual intervention, while minimizing cost during idle periods. What should you do?

You are migrating an on-premises SQL Server 2019 database to Azure SQL Managed Instance. The database uses cross-database queries and SQL Server Agent jobs. You need to ensure that the migration is as seamless as possible and that these features continue to work after migration. What should you do first?

You are configuring Azure SQL Database for an e-commerce application that experiences variable traffic. You need to ensure that the database can automatically scale resources based on demand without manual intervention. The solution must also support scaling to zero compute when not in use to save costs. Which Azure SQL Database offering should you use?

You are managing an Azure SQL Managed Instance that hosts multiple databases for a financial application. You need to implement a security solution that meets compliance requirements by auditing all database activity and sending the audit logs to a centralized Log Analytics workspace for analysis. The solution must also support real-time alerts on suspicious activities. What should you configure?