troubleshooting

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 administer an Azure SQL Database that uses the General Purpose tier. Users report that queries are slow during peak hours. You need to identify if the slow performance is due to log write latency. Which metric should you examine in Azure Monitor?

Which TWO disaster recovery options are available for Azure SQL Database? (Choose two.)

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You run a query against `sys.dm_exec_query_stats` to find the top 10 queries by total worker time. Several queries show high worker time but low logical reads. The database is not experiencing any blocking or deadlocks. What is the most likely cause of the high worker time?

You are the database administrator for a SQL Server 2019 instance on Azure Virtual Machines. The instance hosts a database that must be available during a planned operating system update that requires a restart of the virtual machine. You need to ensure that the database remains online with minimal downtime. What should you do?

You manage an Azure SQL Database named SalesDB in the East US region. The database is in the Business Critical service tier. You need to implement a disaster recovery strategy that provides a recovery point objective (RPO) of less than 5 seconds and a recovery time objective (RTO) of less than 30 seconds during a regional outage. You configure an auto-failover group with a secondary server in the West US region. Which action should you take to meet the RPO and RTO requirements?

You manage an Azure SQL Database that experiences blocking. You need to identify the blocking chain and the T-SQL statements involved in the blocking. Which dynamic management view (DMV) should you query?

You are monitoring an Azure SQL Database using the sys.dm_db_resource_stats DMV. The avg_log_write_percent column shows 95% for the last hour. What does this indicate, and what should you do?

Your company wants to ensure business continuity for an Azure SQL Database that is used by a critical application. The database must remain available in the event of a single availability zone failure within a region. Which configuration should you use?

You have a SQL Server on Azure VM running a mission-critical database. The VM is configured with Azure Site Recovery (ASR) for disaster recovery. During a disaster recovery drill, you notice that the recovered database is not consistent. What is the most likely cause?

You are the database administrator for a healthcare organization that uses Azure SQL Database. You need to implement column-level encryption for a column containing patient Social Security numbers (SSNs). The SSNs must be encrypted at rest and in transit, and only authorized client applications should be able to decrypt them. Which technology should you use?

You have an Azure SQL Managed Instance used for an e-commerce platform. During a flash sale, you experience a deadlock that causes transaction rollbacks. You need to minimize deadlock occurrences in the future. What should you implement?

You are configuring an elastic job in Azure SQL Database to run a T-SQL script on all databases within an elastic pool. The script must run on a schedule. You have already created the job agent, job, and target group. You need to ensure that the job step executes against every database in the pool, including databases added later. What should you configure for the target group?

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?

Your company uses Azure SQL Database with a server-level Microsoft Entra ID admin. You need to implement a solution where database-level roles are automatically assigned based on the user's group membership in Microsoft Entra ID. What should you use?

You administer an Azure SQL Database named SalesDB in the East US region. The business requires that SalesDB remains available even if an entire Azure availability zone within East US fails. You need to configure the database so that replicas are automatically distributed across multiple availability zones with no application connection string changes. What should you do?

Which THREE actions can be performed by using Elastic Database Jobs in Azure SQL Database? (Choose three.)

You are responsible for an Azure SQL Managed Instance that hosts a database with a table named Orders. The table has a clustered index on OrderID and a nonclustered index on CustomerID. You notice that a frequently executed query that filters on CustomerID and returns a small number of rows is performing a clustered index scan. You need to improve the query performance. What should you do?

You are a database administrator for a healthcare company that uses Azure SQL Database for its electronic health records (EHR) system. The database is in the West Europe region using the General Purpose service tier. The company is expanding to the United States and wants to set up disaster recovery with the secondary in East US. The requirements are: RPO of 5 minutes and RTO of 1 hour. The application should automatically failover without manual intervention. Additionally, you must ensure that the secondary database is not used for read traffic to avoid any performance impact on the primary. What should you configure?

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 a database administrator for a healthcare company that uses Azure SQL Managed Instance. The company requires that a T-SQL script run every night to perform index maintenance on a specific database. You need to configure an automated solution that uses SQL Server Agent. Which of the following must you do to enable SQL Server Agent jobs on the managed instance?

You manage an Azure SQL Database that uses a Serverless compute tier. You notice that during idle periods, the database auto-pauses and then auto-resumes when a connection is made. However, users report that the first query after a pause is slow. You need to improve the performance of the first query. What should you do?

You need to automate the deployment of an Azure SQL Database and its schema to multiple environments (dev, test, prod) using a repeatable process. The solution must support version control and CI/CD integration. What should you use?

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in wait time for PAGEIOLATCH_SH. You need to identify the most likely cause. What should you investigate first?

Which THREE actions are required to configure Microsoft Entra ID authentication for an Azure SQL Database? (Choose three.)

You are a database administrator for a company that uses Azure SQL Database. You need to configure a diagnostic setting to send database metrics to a Log Analytics workspace for long-term analysis. The solution should be cost-effective and include metrics like CPU percentage, data IO, and log IO. What should you do?

Which THREE actions can you take to optimize query performance in Azure SQL Database using Intelligent Query Processing?

You are analyzing query performance in an Azure SQL Database. The query in the exhibit returns a list of queries ordered by total_logical_reads. What does high total_logical_reads typically indicate?

You are designing an automated backup strategy for Azure SQL Managed Instance. The solution must ensure point-in-time restore (PITR) within 2 hours for the last 7 days and long-term retention (LTR) for 5 years. Which configuration should you use?

You are configuring Azure SQL Database for a multi-tenant application. Each tenant's data is stored in a separate database. You need to ensure that a tenant admin can only manage their own database and not other databases on the same logical server. What is the best approach?

You need to configure alerts for an Azure SQL Database to notify the operations team when the database exceeds 80% DTU consumption for more than 10 minutes. What should you use?

You are tasked with automating the backups of multiple Azure SQL Databases to ensure long-term retention. Which ONE Azure service can be used to achieve automated backups with retention beyond the default 7-35 days?

You are optimizing an Azure SQL Database that runs a reporting workload. The database is in the General Purpose tier. You notice that many queries are performing table scans on large tables. Which TWO actions would most likely improve query performance without increasing costs?

Your Azure SQL Database is accessed by three separate applications. You must ensure that each application can connect only from its own set of IP addresses, that the addresses are managed centrally without editing each database, and that no application can reach the database over the public internet from any other address. What should you implement?

You have an Azure SQL Database named InventoryDB in the North Europe region. The database is in the General Purpose service tier. The business requires that InventoryDB remains available if a single availability zone within the North Europe region fails. You need to configure the database to meet this requirement with minimal downtime. What should you do?

You administer an Azure SQL Database named SalesDB that uses the Business Critical service tier. The database is deployed in the West Europe region, which supports availability zones. The application requires that the database remains available even if an entire availability zone fails. You need to configure the database to meet this requirement with minimal administrative effort. What should you do?

You are the database administrator for an Azure SQL Database that contains a column storing national ID numbers. A new regulation requires that this column be hidden from users who run ad hoc queries in the Azure portal Query Editor, while still being available to the payroll application. The payroll application connects with a login that has SELECT permission on the table. What should you implement?

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 tuning a query in Azure SQL Database. Which TWO actions can reduce logical reads?

Which TWO metrics in Azure SQL Database indicate that the database might need to be scaled up?

You administer an Azure SQL Database named SalesDB that uses the Business Critical service tier. The database is deployed in the West US 2 region, which does not support availability zones. The application requires a recovery time objective (RTO) of less than 30 seconds for a zonal failure within the region. You need to meet the RTO requirement with minimal administrative effort. 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 you manage in Azure Key Vault. You also need to ensure that the key is automatically rotated. What should you configure?

Your company has an Azure SQL Database that is accessed by multiple applications. You need to implement a security solution that meets the following requirements: - Each application must have its own database user with specific permissions. - All authentication must use Microsoft Entra ID. - You need to be able to rotate credentials for each application without impacting other applications. - The solution must support automatic credential rotation for service principals. What should you do?

Refer to the exhibit. You have configured the automatic tuning policy as shown. After a week, you notice that an index has been dropped automatically, causing a critical query to run slowly. What should you do to prevent this in the future while still benefiting from automatic tuning?

Which TWO are valid methods to connect to an Azure SQL Database without exposing a public endpoint?

You manage an Azure SQL Database that contains a table with sensitive columns. You need to implement Dynamic Data Masking so that users in the 'Reporting' database role see masked values, while users in the 'DataEntry' role see unmasked values. You have created the masking rules. Which two actions should you perform to meet the requirement? (Choose two.)

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 troubleshooting a failover group for Azure SQL Database. The automatic failover is not triggering as expected during a regional outage. You verify that the grace period for data loss is set to 3600 seconds. The outage lasts 30 minutes. What is the most likely reason the automatic failover did not occur?

You administer an Azure SQL Database named HRDB. The security team requires that all data written to the database be encrypted with a customer-managed key that is stored in Azure Key Vault, and that the key be automatically rotated every 90 days. You need to configure Transparent Data Encryption (TDE) with Bring Your Own Key (BYOK). What should you do first?

You need to audit all schema changes (DDL) on an Azure SQL Database for compliance. The audit logs must be retained for 7 years. 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?

Drag and drop the steps to configure a SQL Server Agent job in Azure SQL Managed Instance to run a maintenance task in the correct order.

Which TWO tools can be used to automate the deployment of database schema changes to Azure SQL Database as part of a CI/CD pipeline? (Choose two.)

You have an Azure SQL Database that uses a failover group for high availability. You need to automate the failover to the secondary region during a planned maintenance window. What is the best approach?

You manage an Azure SQL Database named HRDB. The security team requires that all data in transit between the application and HRDB be encrypted, and that the database reject any connections using TLS versions below 1.2. You need to enforce this requirement with the least administrative effort. What should you do?

You are a database administrator for a financial services company that uses Azure SQL Database. The company requires that all administrative tasks, such as index maintenance and statistics updates, be automated and run on a schedule. You need to implement a solution that uses T-SQL scripts and runs them on a schedule without requiring an external server. What should you use?

You have an Azure SQL Managed Instance. You need to automate the execution of a stored procedure every hour to clean up historical data. What is the most appropriate solution?

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 manage an Azure SQL Database that runs an online transaction processing (OLTP) workload. The database is in the General Purpose service tier with 4 vCores. During month-end processing, you observe that the database is hitting its maximum allowed log write throughput, causing delays. You need to increase the maximum log write throughput without changing the service tier. What should you do?

You manage an Azure SQL Database that runs a reporting workload. Users report that month-end reports are slow. You query sys.dm_db_resource_stats and observe that the average log write percentage is consistently high, but CPU and data IO are low. You need to reduce the impact of log write throughput on the workload. What should you do first?

Your company uses Azure SQL Database. You need to ensure that all connections to the database use TLS 1.2 or higher. Currently, some client applications are connecting using TLS 1.0. What should you do?

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?

You are the database administrator for an Azure SQL Database that hosts a multi-tenant SaaS application. Each tenant has its own database user mapped to a Microsoft Entra ID group. The security team requires that every tenant user can see only rows belonging to their own tenant, and that no tenant can infer the existence of other tenants' data through error messages or row counts. You need to implement row-level filtering that enforces this requirement with the least administrative effort. What should you do?

You are the database administrator for an Azure SQL Database that uses Microsoft Entra ID authentication. A new application must connect to the database using a managed identity. The application runs on an Azure virtual machine. You have assigned the managed identity to the VM. What should you do next to allow the application to authenticate to the database?

Refer to the exhibit. You are deploying an Azure SQL Database audit policy using an ARM template. What is the MOST significant security concern with the configuration shown?

Which TWO configurations can help improve the performance of an Azure SQL Database experiencing high `WRITELOG` waits?

You are a database administrator for an Azure SQL Managed Instance. You need to ensure that all connections to the instance use encrypted connections. What should you configure?

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?

Which TWO actions can you perform using Elastic Database Jobs in Azure SQL Database?

You administer an Azure SQL Database named FinanceDB. Auditors require that all SELECT statements against a table named Ledger be recorded with the identity of the caller, and that the audit records be retained for seven years in immutable storage. You need to configure auditing to meet these requirements. What should you do?

You have an Azure SQL Database that uses the Hyperscale service tier. The database is 4 TB and has a readable secondary in a different region. You need to ensure that if the primary region fails, the secondary can be promoted to primary with minimal data loss and without reconfiguring the application connection string. What should you implement?

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?

Your company wants to implement transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key stored in Azure Key Vault. Which TWO prerequisites must be met? (Choose two.)

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 have an Azure SQL Database that uses the General Purpose service tier. The database is 200 GB and is used by a web application. The application experiences occasional unplanned failovers due to hardware failures in the primary region. You need to ensure that the database remains available during a single datacenter failure within the region without any application changes. What should you do?

You manage an Azure SQL Database named SalesDB in the East US region. The database is in the General Purpose service tier and is business-critical. The application that uses SalesDB requires a recovery point objective (RPO) of 5 minutes and a recovery time objective (RTO) of 1 hour in the event of a regional outage. You need to configure a disaster recovery solution that meets these requirements with minimal administrative effort. What should you implement?

You have an Azure SQL Managed Instance in the East US region. To meet a 1-hour RPO and 2-hour RTO, you configure a failover group with a secondary in West US using automatic failover. During a test, you notice that the RTO is consistently 10 minutes longer than required. What is the most likely cause?

Your company has an Azure SQL Database that uses the Business Critical service tier with three replicas. You need to ensure that during a regional outage, the database can be failed over to a secondary region with minimal data loss. What should you configure?

You are a database administrator for an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the instance is experiencing high PAGELATCH_EX waits on tempdb allocation pages. You need to reduce this contention without changing the service tier. 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?

You administer an Azure SQL Database for a ticketing platform. A nightly Azure Automation runbook must scale the database between the General Purpose and Business Critical service tiers based on the day of the week. The runbook runs under a system-assigned managed identity. Which cmdlet should the runbook use to change the service tier?

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 are planning a disaster recovery strategy for an Azure SQL Database that is part of a failover group. The application requires that after a failover, the database is accessible with minimal downtime and without data loss. Which THREE components are essential for this configuration? (Select three.)

You are reviewing an ARM template snippet for an Azure SQL Database. The database should be configured to automatically pause after 60 minutes of inactivity and resume with a minimum capacity of 0.5 vCores. However, the database is not pausing as expected. What is the most likely cause?

You need to automate the scaling of an Azure SQL Database in response to CPU usage using Azure Automation. Which Azure service should you use to monitor CPU metrics and trigger the runbook?

You execute the following query: SELECT c.client_ip, c.application_name FROM sys.dm_exec_sessions s JOIN sys.dm_exec_connections c ON s.session_id = c.session_id WHERE s.action_id = 'LGIF' AND s.state = 'ABORT'; What does this query return?

You are responsible for performance tuning of an Azure SQL Database that hosts a customer relationship management (CRM) application. The database has several tables with millions of rows. Users report that a report query that joins four tables is slow. You examine the query execution plan and notice that the database engine is using an Index Spool (Lazy Spool) operator. Which TWO actions should you take to improve query performance? (Choose two.)

You manage an Azure SQL Database that runs a reporting workload. Users report that queries are slow only when they filter on a specific customer region, and the slowness began after a large data load. You run Query Store and identify a plan that regressed. You want the database engine to automatically detect and revert to the last known good plan for that query. What should you configure?

You are configuring security for an Azure SQL Database. You need to ensure that only members of a specific Microsoft Entra ID group can connect to the database as contained database users with db_owner permissions. What should you do?

A company uses Azure SQL Managed Instance in the East US region. They need to configure a disaster recovery strategy that provides a readable secondary in a paired region and supports manual failover. The solution must minimize administrative overhead and use built-in Azure capabilities. What should they implement?

You have an Azure SQL Managed Instance configured with an auto-failover group between two regions. You need to ensure that client applications can automatically connect to the secondary instance after a failover without changing connection strings. What should you configure?

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 have an Azure SQL Database that stores sensitive customer data. You need to automate the masking of a specific column for non-admin users. Which feature should you use?

You manage an Azure SQL Database that runs a reporting workload. Users report that queries against a large fact table sometimes take much longer than usual, and you suspect that a plan regression occurred after a recent statistics update. You need to identify which queries have a plan that changed and performed worse, without deploying any external monitoring tools. What should you use?

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?

You are monitoring an Azure SQL Database that is running a mission-critical workload. You notice that the DTU consumption is consistently above 90% during peak hours. You need to recommend a solution to reduce the DTU consumption. What should you recommend?

You are the database administrator for a logistics company that uses Azure SQL Database. You need to automate the execution of a T-SQL script that rebuilds fragmented indexes every night at 2:00 AM. You want to minimize administrative overhead and avoid managing a separate virtual machine. What should you use?

Your Azure SQL Database uses a failover group for disaster recovery. You need to automate a planned failover for disaster recovery testing without data loss. What should you use?

You have an Azure SQL Managed Instance and notice that automatic tuning is not enabled. You want to automatically force a plan that performed better than the existing plan. What should you enable?

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 reviewing the ARM template snippet for an Azure SQL Database failover group. The primary server is in East US. The secondary is in West Europe. The readWriteEndpoint has automatic failover with a grace period of 60 minutes. The readOnlyEndpoint is disabled. After a complete outage in East US, what will happen?

You are configuring alerts for an Azure SQL Database. You need to be notified when the database reaches 90% of its allocated storage. Which Azure Monitor alert signal should you use?

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 a database administrator for a large e-commerce platform using Azure SQL Database. You notice that a specific query frequently causes high CPU usage during peak hours. The query is a SELECT with multiple JOINs and a WHERE clause on a non-clustered index. You have already updated statistics and rebuilt indexes. What should you do next to optimize performance?

You are optimizing a data warehouse workload on Azure SQL Database. The workload involves large batch inserts and nightly aggregations. You notice that the transaction log is growing excessively during the batch inserts, causing performance degradation. You need to reduce log growth without affecting data consistency. What should you do?

You need to optimize costs for SalesDB, which is used only during business hours (8 AM to 6 PM). The database currently runs 24/7. Which change should you make?

You are tuning an Azure SQL Database that uses the General Purpose service tier. The database experiences high transaction log write waits during peak hours, and you observe that the log rate is frequently near its limit. You need to increase the maximum log rate for the database. What should you do?

You are the database administrator for a company that uses Azure SQL Database. The company wants to ensure that the database remains available even if the entire Azure region experiences an outage. The solution must provide a read-write endpoint that automatically redirects connections after a failover. What should you configure?

You are monitoring an Azure SQL Database that is experiencing high DTU consumption. You need to identify the queries that are causing high resource usage. Which two data sources can you use? (Choose two.)

You have an Azure SQL Database that uses automatic tuning. Which TWO benefits does automatic tuning provide?

You have an Azure SQL Managed Instance configured with a failover group for disaster recovery. The primary instance is in the East US region and the secondary is in West US. You need to perform a planned failover for maintenance with zero data loss. What is the correct sequence of steps?

A DBA manages an Azure SQL Database and needs to schedule a T-SQL script to run every day at 02:00 UTC to perform index maintenance. The solution must minimize administrative overhead and must not require an on-premises server. What should the DBA use?

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 the DBA for an Azure SQL Database that stores sensitive financial data. The security team requires that all user activity on the database be audited, and audit logs must be retained for 90 days. You need to configure auditing with minimal effort. What should you do?

You manage an Azure SQL Database that contains a table with a column named CreditCardNumber. The security team requires that this column be encrypted so that even database administrators cannot view the plaintext values. The application that inserts and queries data must continue to work with minimal changes, and the encryption keys must be stored in Azure Key Vault. What should you implement?

You are configuring automatic tuning for an Azure SQL Database. Which THREE recommendations can be applied automatically without manual approval?

Your company uses Azure SQL Managed Instance. You need to automate the execution of a stored procedure that processes sales data every night at 2 AM. The solution must use native capabilities and minimize latency. What should you do?

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 Azure SQL Database firewall rules. You need to allow a team of developers to connect from their office IP range (192.168.1.0/24) to a specific database. The developers should not be able to access other databases on the same logical server. What should you do?

Drag and drop the steps to configure an Azure SQL Managed Instance link for disaster recovery in the correct order.

Your Azure SQL Database contains sensitive customer data. You need to implement column-level encryption so that only authorized users can read specific columns. The encryption must be managed by the application, not the database. What should you use?

You need to automate the execution of a T-SQL script against all user databases in an Azure SQL Database elastic pool. The script should run on a schedule and results should be logged to a table. Which feature should you use?

You manage an Azure SQL Managed Instance. You need to monitor storage space usage. Which TWO dynamic management views can you use?

You manage an Azure SQL Database that runs a reporting workload. Users report that a complex stored procedure occasionally returns results in under 5 seconds but sometimes takes over 60 seconds. You have enabled Query Store with the default settings. You need to identify the plan that is causing the slow executions and force the faster plan. Which Query Store report should you use?

You are tuning an Azure SQL Database that uses the General Purpose service tier. The database experiences performance issues during peak hours, and you notice a high number of PAGEIOLATCH_SH waits. You need to reduce these waits. What should you do?

You need to automate the deployment of Azure SQL Database with a specific configuration across multiple environments (dev, test, prod). The deployment must include firewall rules, auditing settings, and threat detection policies. Which THREE tools can be used to implement this automation?

You need to ensure that all users accessing Azure SQL Database from outside the corporate network are required to use multi-factor authentication (MFA). What should you configure?

You are implementing automated table partitioning maintenance for a large Azure SQL Database. The partitioning function uses a monthly range. You need to add a new partition for the next month and remove the oldest partition. What is the best way to automate this?

Your Azure SQL Managed Instance is configured with a long-term backup retention policy of 10 years. You need to reduce storage costs while still meeting a compliance requirement to retain monthly backups for 7 years. What should you do?

Which TWO metrics from sys.dm_db_resource_stats should you monitor to identify a disk IO bottleneck in an Azure SQL Database?

A DBA manages an Azure SQL Managed Instance and needs to automate a weekly full backup of a user database to an Azure Storage account. The solution must use native SQL Server functionality and minimize custom code. What should the DBA do?

You are a database administrator for a retail company that uses Azure SQL Database. You need to automate the process of detecting and responding to high CPU usage. You want to create an alert that triggers an action when CPU usage exceeds 80% for 10 minutes. Which two components must you configure? (Choose two.)

You need to audit all successful and failed login attempts on an Azure SQL Database. Which feature should you enable?

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?

Refer to the exhibit. An administrator runs this Azure CLI command. What is the immediate effect?

Match each Azure SQL Database security feature to its purpose.

You administer an Azure SQL Managed Instance that hosts a mission-critical database. You need to configure an automated task that will execute a T-SQL script to perform a full backup of the database to a URL every night at midnight. The solution must use built-in Azure capabilities and minimize cost. What should you use?

Your company has an Azure SQL Database in the General Purpose tier. You need to reduce the recovery point objective (RPO) from 1 hour to less than 1 minute for disaster recovery. Which action should you take?

You are monitoring an Azure SQL Database using sys.dm_db_wait_stats. You see a high percentage of WRITELOG waits. What is the most likely cause?

You are responsible for a SQL Server 2019 instance on an Azure VM. The VM is part of a failover cluster instance (FCI) using Azure shared disks. During a recent failover test, the cluster took 15 minutes to bring the database online. You need to reduce the failover time to under 5 minutes. What should you do?

You are monitoring an Azure SQL Database using dynamic management views (DMVs). You want to identify the top queries by total CPU time over the last hour. Which DMV should you query?

You need to prevent users from accidentally deleting an Azure SQL Database. What should you configure?

You are reviewing an Azure Resource Manager template snippet for configuring long-term backup retention for an Azure SQL Database. The deployment fails with an error indicating the storage account is not accessible. What is the most likely cause?

You are a database administrator for a financial services company that uses Azure SQL Database. You need to automate the deployment of schema changes across multiple databases in a development environment. The solution must support version control, allow rollback to a previous state, and minimize manual intervention. Which two actions should you perform? (Choose two.)

Refer to the exhibit. You are troubleshooting an Azure SQL Database auditing configuration. The exhibit shows the blob auditing policy. The storage account access key is null, and the subscription ID is all zeros. What is the most likely issue?

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 administer an Azure SQL Managed Instance that hosts a database containing regulated data. The security team requires that all data be encrypted at rest with a customer-managed key stored in Azure Key Vault, and that the key be rotated annually. You configure a key in Key Vault and set the instance's Transparent Data Encryption protector to that key. Six months later, the key approaches its expiration date. What should you do to rotate the key while keeping the instance online and encrypted?

You have an Azure SQL Database that stores sensitive customer data. You need to ensure that the data is encrypted at rest using a customer-managed key stored in Azure Key Vault. What should you configure?

Your company uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

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 managing an Azure SQL Database that runs a critical line-of-business application. Users report that a specific query is running slower than usual. You identify that the query is performing a clustered index scan on a large table with over 10 million rows. The table has a clustered index on an identity column and a nonclustered index on a frequently filtered column. You need to minimize the query execution time without adding additional indexes. What should you do?

Drag and drop the steps to configure a failover group for an Azure SQL Database in the correct order.

You are deploying Azure SQL Database for a multi-tenant application. Each tenant's data must be isolated. You need to ensure that tenants cannot access each other's data even if there is a SQL injection vulnerability. Which security feature should you implement?

A developer at your company needs to run ad hoc queries against an Azure SQL Database from a workstation on the corporate network. Security policy forbids storing credentials in the application and forbids any inbound public network access to the database. The workstation already has a Microsoft Entra ID-joined identity. What should you configure to meet these requirements?

You need to audit all failed login attempts to an Azure SQL Database. Which feature should you enable?

You manage an Azure SQL Database in the East US region. The database uses the General Purpose service tier with geo-redundant backup storage. You need to ensure that in the event of a regional outage, you can restore the database to the West US region with the least possible downtime. What should you use?

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?

You are designing a disaster recovery strategy for a group of Azure SQL Databases hosted on a logical server in the East US region. The solution must provide automatic failover to a secondary region, support read-only access to the secondary during normal operations, and minimize application connection string changes during failover. You plan to use an auto-failover group. Which two actions should you perform? (Choose two.)

Your company is using Azure SQL Database with Microsoft Entra ID authentication. A developer needs to connect to the database using a service principal. What should you provide to the developer?

You are configuring automated backups for an Azure SQL Database. Which TWO settings can you configure?

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?

You are optimizing an Azure SQL Database that uses the Hyperscale service tier. You need to reduce the time it takes to perform a database restore. Which TWO factors directly affect the restore time? (Choose two.)

You have an Azure SQL Managed Instance configured with a failover group to a secondary region. During a regional outage, the failover group automatically fails over to the secondary. After the primary region is restored, you need to bring the primary back online and re-establish the failover relationship. What should you do?

Which TWO of the following are best practices for securing Azure SQL Database?

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?

Which THREE of the following are required to configure Microsoft Entra authentication for an Azure SQL Managed Instance?

You are a database administrator for a logistics company that uses Azure SQL Database. The company requires that a stored procedure, which archives old shipment records, runs every night at 2:00 AM UTC. You need to configure a solution that minimizes administrative overhead and uses built-in Azure SQL Database capabilities. What should you do?

You have an Azure SQL Database named DB1 that uses the Hyperscale service tier. The database has a primary replica and multiple named replicas. You need to ensure that read-only workloads are offloaded to a named replica and that the named replica remains available if the primary replica fails. What should you do?

You are troubleshooting a connectivity issue: an application running on an Azure virtual machine (VM) cannot connect to an Azure SQL Database. The VM is in the same region as the SQL Database. The VM can ping other resources, but the SQL connection fails. The SQL Database has a firewall rule allowing the VM's private IP address. What is the most likely cause?

Your Azure SQL Database is configured with Active Geo-Replication to a secondary region for disaster recovery. During a routine failover drill, you notice that after failover, the application cannot connect to the new primary because the login credentials fail. The logins are contained in the master database. What is the most likely cause?

You are a database administrator for a large financial services company. You manage an Azure SQL Database in the Business Critical tier with a failover group configured for disaster recovery. The database has a heavy OLTP workload. You notice that the secondary replica is experiencing high log write latency, impacting the primary's performance due to synchronous commit. You need to minimize the performance impact on the primary while maintaining disaster recovery capabilities. What should you do?

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company has a requirement to encrypt sensitive data in transit between the application and the database. Additionally, the company wants to ensure that database administrators (DBAs) cannot view the sensitive data. Which TWO features should you implement?

A production Azure SQL Database is experiencing high CPU usage during peak hours. The database uses the S3 service tier. You need to reduce CPU usage without changing the service tier. Which action should you take?

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?

You are the database administrator for a company that uses Azure SQL Database. You need to implement a security solution that automatically detects and alerts on suspicious activities, such as SQL injection attempts. Which feature should you enable?

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 a database administrator for a large financial services company. You need to ensure that all queries that read sensitive customer data use an optimized execution plan. What feature should you enable to automatically identify and fix regressed query plans?

You are the Azure SQL Database administrator for a healthcare company. A new compliance requirement mandates that all data at rest in Azure SQL Database be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that you can revoke access to the key at any time. The database is currently encrypted with the default service-managed key. What should you do first to meet this requirement?

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 alerts for an Azure SQL Database. You need to create an alert that fires when the database's DTU consumption exceeds 80% for a sustained period. Which Azure Monitor metric should you use?

Which TWO of the following are valid methods to connect to Azure SQL Database securely?

You are responsible for a set of Azure SQL Databases that are used by different departments in your organization. The databases are deployed in an elastic pool with Standard tier (eDTU 200). Usage patterns show that the marketing database uses high CPU during the day, while the sales database uses high IO at night. You want to optimize costs while ensuring each database gets the resources it needs. What should you do?

You are configuring an Azure Automation runbook to perform daily maintenance tasks on an Azure SQL Database. The runbook will run on a schedule and must securely connect to the database. Which two actions should you perform to enable the runbook to authenticate to the database? (Choose two.)

Your Azure SQL Database is configured with the Hyperscale service tier. You observe that log write latency is consistently high, affecting transaction throughput. What is the most likely cause and the recommended mitigation?

A company runs Azure SQL Database and wants to automatically receive an email alert when the database's CPU usage exceeds 90% for 10 minutes. The DBA needs to configure this with minimal effort. What should the DBA do?

You are a database administrator for a company that stores sensitive customer data in Azure SQL Database. The security team requires that all access to the database be authenticated using Microsoft Entra ID and that no SQL authentication logins exist. You need to verify that SQL authentication is disabled. What should you do?

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 planning high availability for an Azure SQL Database that runs an e-commerce application. The database uses the Business Critical service tier. Which TWO features are automatically enabled to provide high availability within a single region? (Choose two.)

You need to configure a long-term retention policy for backups of an Azure SQL Database that must retain weekly full backups for 5 years and monthly full backups for 10 years. Which backup retention feature should you use?

Your company plans to use Azure SQL Managed Instance for a mission-critical application. You need to ensure that all connections to the database are encrypted and that the server's identity is verified. Which configuration should you enforce?

You manage an Azure SQL Database that uses the General Purpose tier. The database has a failover group with a secondary in a paired region. During a regional outage, you initiate a forced failover. After the outage is resolved, you want to bring the original primary region back online without data loss. What should you do?

You are monitoring an Azure SQL Database. You need to identify which built-in tools can provide real-time performance data without additional cost. Which THREE should you select?

You need to automate monitoring and alerting for an Azure SQL Database. Which THREE actions can you achieve using Azure Monitor and SQL Insights?

You are reviewing an ARM template for Azure SQL Database. The exhibit shows the database settings. You notice the database is not being automatically paused. What is the most likely explanation?

You are automating index maintenance for an Azure SQL Database using an Azure Automation runbook. The runbook connects to the database and executes T-SQL to rebuild fragmented indexes. You need to ensure the runbook can authenticate without storing credentials in the script. What should you configure?

Refer to the exhibit. You are reviewing an ARM template for deploying an Azure SQL Database. The template specifies a point-in-time restore from a source database. The source database is configured with geo-redundant backup storage. You need to ensure that the restored database can be used for disaster recovery in a different region. What is missing from the template to achieve this?

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?

Your Azure SQL Managed Instance is experiencing high PAGELATCH_SH waits. You need to reduce this contention. What should you implement?

Refer to the exhibit. After a brief outage, the availability group recovered. However, SQL2 shows NOT_HEALTHY and DISCONNECTED. What is the most likely cause?

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?

You are deploying an Azure SQL Database that will contain highly sensitive personal data. The security policy requires that the data be encrypted at rest, in transit, and in use. Additionally, the encryption keys must be stored in a hardware security module (HSM) and be customer-managed. Which combination of features should you implement?

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data in transit between the application and the database be encrypted, and they want to enforce a minimum TLS version of 1.2 at the server level. The application connects using the server's fully qualified domain name. What should you configure to meet this requirement with the least administrative effort?

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to create an alert that fires when the database's CPU usage exceeds 90% for 10 minutes. Which metric should you use?

Drag and drop the steps to configure an Azure SQL Database elastic pool in the correct order.

Drag and drop the steps to configure automatic tuning for an Azure SQL Database in the correct order.

You need to monitor the performance of an Azure SQL Database and set up alerts when the DTU consumption exceeds 80% for more than 5 minutes. Which Azure service should you use?

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert indicating 'Degradation in performance due to increased log write wait time'. What is the most likely cause of this issue?

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

You are the database administrator for an Azure SQL Database that contains several tables with columns that store personally identifiable information (PII). The security team requires that these columns be identified and labeled as 'Confidential' in the database. You need to implement a solution that automatically classifies these columns based on their names and data patterns. What should you use?

Which THREE components are required to set up elastic jobs in Azure SQL Database?

You are responsible for automating index maintenance in Azure SQL Database. You need to ensure that index rebuilds and reorganizations are performed only when fragmentation exceeds 30% and 10%, respectively, and that the job runs weekly. Which approach should you use?

Which TWO of the following are best practices for managing firewall rules for Azure SQL Database?

You administer an Azure SQL Database that must remain available if the primary region becomes unavailable. The business requires a secondary region with read-only access for reporting, and during a failover the application connection string must not change. You configure an auto-failover group. Which feature of the auto-failover group satisfies the requirement that the application connection string remains unchanged after failover?

Your company has an Azure SQL Database that stores sensitive customer data. You need to ensure that data is encrypted at rest and in transit. The database is currently using Transparent Data Encryption (TDE) with service-managed keys. Compliance requirements now mandate that you use customer-managed keys stored in Azure Key Vault. Additionally, all connections must use encrypted connections. What should you do?

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 monitoring an Azure SQL Database that uses the vCore purchasing model. You need to set up alerts to notify you when the database approaches its resource limits. Which two metrics should you alert on to detect CPU and I/O pressure? (Choose two.)

You need to encrypt sensitive columns in an Azure SQL Database table so that data is encrypted at rest and in transit between the application and database. Which feature should you use?

You run the query in the exhibit on an Azure SQL Database. The result shows high wait_time_ms for PAGEIOLATCH_SH waits. What does this indicate?

You have a new Azure SQL Database. You need to ensure that all connections use TLS 1.2 or higher. What should you configure?

You have an Azure SQL Database with active geo-replication. You need to monitor the replication lag to ensure the RPO is met. Which metric should you monitor?

You are the database administrator for a company that uses Azure SQL Database. The security team requires that all data at rest be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, rather than the default service-managed key. You need to implement this requirement with the least administrative overhead. What should you do?

You are managing an Azure SQL Database that uses Intelligent Insights. You receive an alert that there is a performance issue with a specific query. You need to analyze the root cause. What should you use?

Which THREE metrics should you monitor to proactively detect potential performance issues in an Azure SQL Database?

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database. The template configures backup retention. What is the effect of this configuration?

Your company runs a critical application on Azure SQL Managed Instance in the North Europe region. The application requires an RPO of 5 minutes and an RTO of 2 hours during a regional disaster. The current setup uses a single instance with geo-redundant backup storage (RA-GRS). During a disaster recovery planning session, you discover that geo-restore from RA-GRS backups takes approximately 4 hours to complete, which exceeds the RTO. You need to modify the disaster recovery solution to meet the RTO without exceeding the budget significantly. The solution must minimize administrative overhead. What should you do?

You manage an Azure SQL Database named OrderDB in the Business Critical service tier. A compliance requirement mandates that the database must remain available even if an entire Azure availability zone fails within the primary region. You need to configure the database to meet this requirement with the least administrative effort. What should you do?

A company uses Azure SQL Database and wants to automatically send an email notification when an index fragmentation exceeds 30% for any database. Which solution should they implement?

Drag and drop the steps to restore an Azure SQL Database to a point in time in the correct order.

Refer to the exhibit. You executed the Azure CLI command to list databases. You need to resume db3 to make it available for connections. Which command should you use?

Refer to the exhibit. A PowerShell script is used to move an Azure SQL Database into an elastic pool. The script runs without error. Which condition must be true before the script runs?

Refer to the exhibit. You are reviewing an ARM template for an Azure SQL Database backup policy. The database is used for a reporting workload that is updated daily. The compliance team requires that point-in-time restore (PITR) be available for the past 30 days. What action should you take?

Your organization has a policy that all Azure SQL Database connections must use Microsoft Entra authentication. You need to ensure that application developers cannot accidentally use SQL authentication. What should you do?

You are deploying SQL Server on an Azure Virtual Machine. You need to configure a high availability solution that provides automatic failover and does not require a shared storage solution. The solution must support multiple databases and allow for readable secondary replicas. What should you implement?

You need to automate the deployment of schema changes to an Azure SQL Database using Azure DevOps. Which THREE components are required? (Choose three.)

You are monitoring an Azure SQL Database that uses the vCore purchasing model. You need to identify the top resource-consuming queries. You decide to use Query Store. Which two actions should you perform? (Choose two.)

You are managing an Azure SQL Database that has automatic tuning enabled. You notice that a recent index creation recommended by automatic tuning has caused a performance regression for some queries. You need to revert the change and prevent automatic tuning from applying similar recommendations in the future. What should you do?

You manage an Azure SQL Database server that hosts multiple databases. The security policy requires that all connections to the server use a minimum TLS version of 1.2 and that the setting applies to all databases on the server. What should you configure?

You are responsible for securing an Azure SQL Database. You need to implement data masking for a column that contains credit card numbers, ensuring that users with the db_datareader role see a masked version. However, users with the db_owner role should see the unmasked data. What should you configure?

Your company uses Azure SQL Database and needs to protect sensitive columns (e.g., credit card numbers) from being accessed by unauthorized users. You implement Always Encrypted. However, some queries that perform pattern matching on the encrypted column are failing because the column cannot be searched. What should you do to allow pattern matching while maintaining security?

You administer an Azure SQL Database named HRDB. The security team requires that all data at rest be encrypted with a customer-managed key stored in Azure Key Vault, and that the key be automatically rotated every 90 days. You create the Key Vault and grant the logical server's managed identity the necessary permissions. What should you do next to meet the requirement?

You are administering an Azure SQL Managed Instance that hosts a busy OLTP database. Users report that during peak hours, queries that typically run in milliseconds now take seconds. You suspect that the issue is related to tempdb contention. Which action should you take to resolve the tempdb contention?

Which THREE are best practices for securing Azure SQL Database? (Choose three.)

Which THREE components are required to configure an auto-failover group for Azure SQL Database? (Choose three.)

You are the database administrator for a company that uses Azure SQL Managed Instance. You need to allow a specific application to connect to the database using a service principal. The application authenticates with Microsoft Entra ID. What should you configure?

Your Azure SQL Database is configured with a failover group between two regions. The primary database experiences a catastrophic failure that prevents any connectivity. You need to initiate a failover to the secondary region. However, the failover group status shows 'Primary is down'. What should you do?

Which THREE are valid methods to implement disaster recovery for Azure SQL Database? (Select three.)

You are troubleshooting a performance issue on an Azure SQL Database. Which TWO actions should you prioritize to identify the root cause of high resource consumption?

You are the database administrator for an Azure SQL Database named HRDB. The security team mandates that the database must be protected against SQL injection attacks and that any suspicious activity must be automatically detected and reported. You need to enable a feature that provides this protection with minimal administrative effort. What should you enable?

You are managing an Azure SQL Database that has Automatic Tuning enabled. You receive an alert that a query plan regression was detected and a plan correction was automatically applied. You want to verify the performance improvement. What should you use?

You have an Azure SQL Database configured with active geo-replication to a secondary region. The primary region experiences a full outage. You need to fail over with minimal data loss. What should you do?

You need to automate the creation of a new Azure SQL Database whenever a new customer signs up. The solution should use infrastructure as code and integrate with your CI/CD pipeline. What should you use?

You need to automate the deployment of an Azure SQL Database along with its firewall rules and performance tier using infrastructure as code. Which technology should you use?

You need to recommend a performance monitoring solution for a new Azure SQL Managed Instance deployment. The solution must provide historical query performance data and the ability to compare performance before and after index changes. What should you include in the recommendation?

You are responsible for security compliance of Azure SQL databases. You need to audit all successful and failed login attempts and store the audit logs in a Log Analytics workspace for analysis. You also want to detect potential brute-force attacks. What should you implement?

You are responsible for an Azure SQL Managed Instance that hosts a critical database. You need to configure alerts to notify the operations team when the average CPU usage of the instance exceeds 80% for 10 minutes. You want to use the built-in monitoring capabilities of Azure. What should you create?

You need to configure a backup policy for Azure SQL Database that allows restoring to any point within the last 7 days. What is the minimum point-in-time restore retention period you should set?

You have an Azure SQL Database that is part of a failover group with automatic failover. The primary region experiences a complete outage. The failover group automatically fails over to the secondary region. After the primary region is restored, you need to ensure the database is operational in the primary region with minimal data loss. What should you do?

You are configuring automatic tuning for an Azure SQL Database. The database has a heavy OLTP workload. You want to automatically correct query plan choice regressions without manual intervention. Which automatic tuning option should you enable?

You are reviewing the long-term retention (LTR) policy for an Azure SQL Database. The exhibit shows the current policy. You need to ensure that backups are retained for at least 10 years for compliance. What should you do?

You have an Azure SQL Database that is experiencing performance issues. You suspect that a recent deployment introduced a regression in a stored procedure. You need to identify the query plan change and the specific query that is performing poorly. What should you use?

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?

You manage an Azure SQL Database that is accessed by several applications. You need to implement the principle of least privilege for database access. Which three actions should you take? (Choose three.)

A company has an Azure SQL Database that is experiencing performance degradation during peak hours. The database is configured with the Standard tier (S2). Which action should you recommend to improve performance without changing the application code?

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 manage an Azure SQL Managed Instance that hosts several databases. You need to automate the process of patching the operating system and SQL Server engine with minimal downtime. What should you use?

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 need to automate the deployment of an Azure SQL Database and its schema updates as part of a CI/CD pipeline. The pipeline must apply T-SQL scripts to the database after deployment. Which Azure DevOps task should you use to execute the T-SQL scripts against Azure SQL Database?

You query the sys.dm_geo_replication_link_status dynamic management view for an Azure SQL Database configured with active geo-replication. The exhibit shows the output. What does this indicate about the replication health?

You are configuring Microsoft Defender for SQL for an Azure SQL Database. You want to receive email notifications when a suspicious activity is detected. What should you configure?

You need to automate the monitoring of Azure SQL Database performance and receive alerts when certain conditions are met. Which TWO Azure services can be used together to achieve this?

An Azure SQL Database contains personally identifiable information (PII). You need to mask the PII columns from non-administrative users while allowing administrators to see the actual data. Which feature should you use?

You have an Azure SQL Managed Instance named MI1 in the West Europe region. The instance hosts a mission-critical database that must be recoverable within 30 minutes in the event of a regional outage. You need to implement a disaster recovery solution that minimizes data loss and administrative effort. The solution must support read-only access to the secondary during normal operations. What should you configure?

You are tasked with automating index maintenance for an Azure SQL Database. Which Azure service should you use to run T-SQL scripts on a recurring schedule?

You are a DBA for a company that uses Azure SQL Database for its customer relationship management (CRM) system. The database is currently in the Standard tier (DTU S2) and is experiencing performance degradation during end-of-month reporting. Reports that aggregate large amounts of data take over 30 minutes to run. You notice that the database's DTU usage averages 80% during these reports, with high IO. You need to improve report performance without significantly increasing cost. The reports are read-only and can tolerate some staleness. What should you do?

You manage a business-critical Azure SQL Database named OrdersDB in the Business Critical service tier. The database is in the East US region. The company requires a secondary readable copy in West US that provides a recovery point objective (RPO) of 5 seconds and a recovery time objective (RTO) of 30 seconds during a regional outage. You need to implement the solution with the least administrative effort. What should you do?

Your company uses Azure SQL Database with Microsoft Entra ID (formerly Azure AD) authentication. You need to grant a group of external consultants access to a specific database with read-only permissions. The consultants are from a partner organization that uses their own Microsoft Entra ID tenant. What should you do?

You have a SQL Managed Instance that hosts a critical OLTP database. You notice that the average query wait time has increased significantly over the past hour. You need to identify the top resource waits. What should you use?

You have an Azure SQL Managed Instance named MI1 in the East US region. The company requires a disaster recovery solution that provides a readable secondary in the West US region and automatic failover. You need to configure the solution with the least administrative effort. What should you do?

You are monitoring an Azure SQL Database. You need to identify which two metrics are most important for detecting a memory pressure issue. Which TWO should you select?

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?

Your organization uses Azure SQL Database and needs to automate email notifications when a database reaches 80% storage usage. Which native Azure feature can you use?

A company plans to migrate an on-premises SQL Server database to Azure SQL Database Managed Instance. They require a high availability solution that provides automatic failover between replicas within the same region with an RPO of 0 and an RTO of less than 30 seconds. Which service tier should they choose?

You are a database administrator for a financial services company. You have deployed an Azure SQL Database and configured auditing using the JSON policy shown in the exhibit. After a security incident, you need to review all successful and failed login attempts to the database. However, you notice that login events are not being captured in the audit logs. What is the most likely reason?

You are a database administrator for a logistics company that uses Azure SQL Database. You need to automate the process of copying data from an on-premises SQL Server to Azure SQL Database every night. The data volume is large, and you want to minimize the impact on the source server. You also need to ensure that the copy operation is resilient to transient failures. What should you use?

You need to automate the deployment of an Azure SQL Database using Infrastructure as Code. The deployment should include the database, firewall rules, and threat detection settings. Which tool should you use?

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 the DBA for a company that uses Azure SQL Database. You need to ensure that only authorized users can view sensitive columns (e.g., salary) in the Employees table. You want to obfuscate the data for certain users but allow full access to HR managers. Which feature should you use?

You manage an Azure SQL Database that contains a table with a column named 'CreditCardNumber' that stores sensitive data. You need to ensure that the data in this column is encrypted at rest and in use, and that only specific application users can decrypt it. You also need to minimize performance impact on queries that do not access this column. What should you implement?

Refer to the exhibit. An Azure SQL Database is experiencing performance degradation. Based on the Extended Events and wait statistics, which is the most likely root cause?

You manage an Azure SQL Database that is part of a business-critical application. You need to configure an alert that triggers when the database's CPU usage exceeds 80% for 10 minutes. The alert must notify an operations team via email. You want to minimize administrative effort. What should you do?

You are a database administrator for an Azure SQL Database. You need to ensure that only specific client IP addresses can connect to the database, while all other traffic is blocked. You also need to allow Azure services to access the database. What should you configure?

Your company requires that all production databases in Azure SQL Database have an RPO of less than 5 seconds and an RTO of less than 1 minute during a regional outage. You need to recommend a high availability and disaster recovery solution. Which feature should you use?

Which TWO options are required to configure a SQL Server Always On Availability Group on Azure Virtual Machines?

You are designing an automated backup retention policy for an Azure SQL Database. The business requirement is to retain daily backups for 30 days, weekly backups for 12 weeks, monthly backups for 12 months, and yearly backups for 7 years. Which backup retention type should you configure?

You manage a SQL Managed Instance in the East US region. The instance must be recoverable within 1 hour in the event of a regional disaster. You need to configure a secondary replica in a paired region with automatic failover. Which solution meets the requirement?

You have an Azure SQL Database with a heavy workload. You notice that the `PAGEIOLATCH_SH` wait is the top wait. Which performance issue does this indicate?

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?

A junior developer at your company connects to an Azure SQL Database using the SQL login 'appuser'. You need to grant 'appuser' the ability to read from a table named dbo.Orders in the Sales schema, but nothing else in the database. You also want to follow the principle of least privilege. What should you do?

You manage an Azure SQL Database. A security review finds that an application service principal is connecting with a SQL login that has db_owner membership, and that the login's password has not changed in two years. You must reduce the standing privilege and eliminate the long-lived password while keeping the application working. What should you do?

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

Match each Azure SQL Database monitoring metric to its meaning.

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 tuning an Azure SQL Database that uses the General Purpose service tier. You notice that a specific query has a high average CPU time but a low average elapsed time. Query Store shows that the query plan uses a Hash Match (Aggregate) operator. You need to reduce the CPU consumption of this query. What should you do?

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.)

You have an Azure SQL Database that is part of an elastic pool. You notice that the pool's eDTU consumption is consistently high, and some databases are experiencing resource contention. You need to ensure that a critical database always gets a minimum amount of resources. What should you configure?

You need to automate the deployment of schema changes to multiple Azure SQL Databases in different regions. The solution must support rollback and version control. Which technology should you use?

You need to design a disaster recovery solution for an Azure SQL Database that uses the General Purpose service tier. The solution must have an RTO of 1 hour and an RPO of 15 minutes. Which TWO options can achieve these requirements?

You are managing an Azure SQL Database that is used by a real-time analytics application. The database uses the Hyperscale service tier. You notice that the transaction log rate is consistently high, causing performance degradation. You need to reduce the log generation rate without compromising data durability. What should you do?

Your company runs a global e-commerce application using Azure SQL Database in the West Europe region. You need to implement a solution that provides automatic failover and allows the secondary region to be used for read-only queries during normal operations. The secondary must be in a different region. Which configuration meets these requirements?

You are optimizing an Azure SQL Database that uses the Business Critical tier. Which TWO factors affect the maximum log rate?

You are monitoring an Azure SQL Database using the Automatic Tuning feature. The database has a workload that is read-intensive. You enable the CREATE INDEX and DROP INDEX options. After a week, you observe that the database has created several new indexes automatically. However, you notice that one of the new indexes is causing increased write latency for an application that performs frequent updates. What should you do to resolve the issue without losing the benefits of automatic tuning for other indexes?

You are the database administrator for a financial services company using Azure SQL Database. The security team mandates that all administrative activities on the SQL logical server be performed using just-in-time (JIT) access with approval workflows, and that permanent elevated permissions be eliminated. You need to implement this requirement with the least amount of custom development. What should you use?

You need to automatically send an email notification when an Azure SQL Database reaches 80% storage usage. What should you configure?

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 configuring a private endpoint for an Azure SQL Database. The exhibit shows the current network ACLs. You need to ensure that only traffic from a specific subnet in VNet1 is allowed, and all other traffic is denied. What should you do?

You manage an Azure SQL Database that is part of a business-critical application. You need to ensure that network traffic between the application hosted on Azure VMs and the database is encrypted and does not traverse the public internet. What should you configure?

Your company has an Azure SQL Managed Instance in the General Purpose tier. You need to configure a failover group for disaster recovery. The secondary managed instance must be in a different region and must also be used for read-only workloads. During a failover, you want to minimize data loss. Which configuration should you use?

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?

Which TWO of the following are required steps to configure Azure SQL Database to use a customer-managed key (CMK) for Transparent Data Encryption (TDE) with Azure Key Vault? (Choose two.)

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

You have an Azure SQL Database that stores sensitive data. You need to automatically classify and apply sensitivity labels to new columns as they are added. What should you use?

Your company has an Azure SQL Database that uses active geo-replication to a secondary region. The primary database is hit by a logical corruption error. You need to restore the database to a point before the corruption occurred with minimal data loss. What should you do?

You have an Azure SQL Database that uses the Hyperscale service tier. You notice that the log rate is frequently throttled. Which configuration change can help reduce log rate throttling?

You need to ensure that all connections to an Azure SQL Database use encryption. The application uses the JDBC driver. What should you configure in the connection string?

You are responsible for an Azure SQL Database that hosts a reporting workload. The database runs a large number of ad-hoc queries that consume significant CPU. You need to identify the top CPU-consuming queries to optimize them. Which feature should you use?

You are reviewing a JSON configuration for an Azure SQL Database. The exhibit shows the database properties. Which statement about this database is correct?

You manage an Azure SQL Database named OrdersDB in the East US region. The business requires that OrdersDB be readable from a secondary region during a planned regional failover, and that the failover be initiated manually by a database administrator. You create a failover group and add OrdersDB as a member. Which read-write listener endpoint should applications use to connect to OrdersDB after a manual failover to the secondary region?

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 have an Azure SQL Database that needs to be backed up daily using Azure Automation runbooks. The runbook must trigger an export of the database to a storage account. How should you configure the runbook to authenticate securely to Azure?

You are reviewing a PowerShell script that configures auditing for an Azure SQL Database. The script sets an audit rule with the specified parameters. After running the script, you notice that SELECT operations are not being audited. What is the most likely cause?

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 are configuring security for an Azure SQL Database that will be used by a web application. The application uses a connection string with SQL authentication. You need to protect the database from SQL injection attacks. Which two measures should you implement? (Choose two.)

You have an Azure SQL Database in the Hyperscale service tier. You need to ensure that the database remains available during a single Azure zone failure. The solution must not require manual intervention. What should you configure?

You are designing a security strategy for Azure SQL Managed Instance. The compliance team requires that all database backups be encrypted at rest using a customer-managed key. Which feature should you enable?

Which TWO of the following are valid methods to configure network security for Azure SQL Managed Instance?

Refer to the exhibit. You are reviewing an Azure Resource Manager template for deploying an Azure SQL Database server. The template sets publicNetworkAccess to Disabled, minimalTlsVersion to 1.2, and azureAdOnlyAuthentication to true. However, the deployment fails with an error. What is the most likely cause?

You manage an Azure SQL Managed Instance that hosts a critical OLTP database. You notice that the average CPU usage is consistently above 90% during business hours. You have enabled Intelligent Insights, which recommends creating a missing index. What should you do first to validate the recommendation before implementing it?

You need to configure Azure SQL Database to automatically adjust indexing based on workload patterns. Which feature should you enable?

You need to automatically notify the operations team when an Azure SQL Database reaches 80% storage usage. Which Azure service should you use to create the alert?

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 reviewing an Azure SQL Database server's vulnerability assessment settings. The exhibit shows the current configuration. A recent security audit requires that vulnerability assessment scans be enabled and that results be retained for at least 90 days. What should you do?

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?

Which THREE of the following are required to automate schema deployments to Azure SQL Database using Azure DevOps? (Select exactly three.)

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?

Refer to the exhibit. You are deploying an Azure SQL Database with Transparent Data Encryption (TDE) enabled via ARM template. The database will contain highly sensitive data, and your security policy requires that the encryption key be managed by your organization using Azure Key Vault. What additional configuration is needed?

Your company has a compliance requirement to keep database backups for 10 years. You are using Azure SQL Database. Which backup retention feature should you use?

You have an Azure SQL Database in the General Purpose service tier. The database must be available during a planned patching event that updates the underlying infrastructure. What high availability feature is provided by default?

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 metrics are available in Azure Monitor for an Azure SQL Database that can be used to set autoscale rules? (Select two.)

You are configuring security for an Azure SQL Database. The security team requires that all administrative actions on the server and databases are logged to an Azure Storage account, and that the logs are retained for 90 days. You need to configure auditing to meet these requirements with minimal effort. What should you do?

Your company has an Azure SQL Database that contains sensitive financial data. You need to ensure that database administrators cannot view the actual data while still being able to perform administrative tasks such as backups and index maintenance. Which feature should you implement?

Refer to the exhibit. You run the Azure CLI command to check the configuration of an Azure SQL Database named db1. The output shows zoneRedundant is true and replicationRole is Primary. Which statement is true about this database?

Your company runs a mission-critical Azure SQL Database in the East US region. To meet an RPO of 5 seconds and an RTO of 30 minutes in the event of a regional outage, which deployment option should you choose?

You are responsible for an Azure SQL Database that hosts a financial application. The database is in the General Purpose service tier. You need to ensure that the database automatically scales compute resources based on workload demand without manual intervention. What should you configure?

Refer to the exhibit. An automatic tuning recommendation to force the last good plan is active. What should the database administrator do next?

Your organization uses Azure SQL Database and wants to automatically detect and alert on potential SQL injection attacks. Which Azure service should you enable?

Your company has an Azure SQL Database with a failover group configured to a secondary region. The primary region experiences a temporary network issue. The failover group is set to automatic failover with a grace period of 1 hour. What will happen?

You are managing an Azure SQL Database that is experiencing intermittent performance degradation. Query Store shows that a specific query's execution plan changed, causing increased CPU usage. You need to ensure consistent performance without rewriting the application. What should you do?

You are troubleshooting a performance issue in an Azure SQL Database. The database is in the Hyperscale service tier. You observe that read queries are slow, and you suspect that a specific query plan is causing excessive physical reads. You want to identify the query and its plan, and then force a better plan if available. Which tool should you use to capture and analyze the plan, and then force a plan?

You need to protect Azure SQL Database from SQL injection attacks. Which THREE of the following measures should you implement?

Which TWO actions are valid for implementing column-level encryption in Azure SQL Database using Always Encrypted? (Choose two.)

You administer a large Azure SQL Database that is used for a SaaS application. The database has a table with over 1 billion rows that is frequently queried by customer ID. The table currently has a clustered index on an identity column and a nonclustered index on customer ID. Queries that filter by customer ID are experiencing high IO and long execution times. You analyze the execution plan and see that the nonclustered index is used, but there are many key lookups. You need to optimize the query performance while minimizing storage overhead. What should you do?

You are monitoring an Azure SQL Database using Query Performance Insight. You see a query with high duration and high CPU usage. The query plan shows a clustered index scan. What is the most likely cause and recommendation?

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 the DBA for an Azure SQL Database that stores sensitive customer data. The security team requires that database administrators be able to manage the database but not see the sensitive data in plaintext. You need to implement a solution that meets this requirement with minimal application changes. What should you do?

You are the Azure SQL Database administrator for a financial services company. The compliance team requires that all data in transit between the application tier and Azure SQL Database be encrypted, and that the server enforce a minimum TLS version of 1.2. The application servers run Windows Server 2019 and use the Microsoft.Data.SqlClient provider. You need to configure the server so that only TLS 1.2 connections are accepted. What should you do?

You manage an Azure SQL Database that uses the General Purpose tier. You need to monitor the performance of the database and identify the top resource-consuming queries. You want to use a built-in feature that requires no additional cost. What should you use?

You manage an Azure SQL Database with the General Purpose service tier. The database experiences performance degradation during peak hours. You enable automatic tuning and want to ensure that the database automatically corrects plan regressions caused by parameter sniffing. Which automatic tuning option should you enable?

Which TWO are valid methods for auditing Azure SQL Database activity? (Choose two.)

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 setting up a new Azure SQL Database for a development team. The database will contain test data that mimics production but with some sensitive fields obfuscated. You need to ensure that developers can query the database without seeing the actual sensitive data. The developers will use Microsoft Entra ID authentication. You have the following requirements: - The sensitive data should be automatically masked in query results for all developers except the database administrator. - The masking should be applied without modifying the application code. - The solution should be easy to manage and not require changes to the data model. What should you implement?

You are monitoring an Azure SQL Database and notice that the average CPU usage is 80% and the average data IO percentage is 70%. You need to identify the most likely cause of the high resource usage. What should you check first?

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 support an Azure SQL Database that uses the General Purpose service tier. A nightly ETL process writes millions of rows, and during the load the database occasionally reports error 40501, 'The service is currently busy.' You need to reduce the chance that the ETL job is throttled while keeping the same service tier. What should you do?

You need to audit schema changes on an Azure SQL Database. Specifically, you must capture details of any DDL statements executed by any user. The audit logs must be stored in a Log Analytics workspace for analysis. What should you configure?

Your company has a strict policy that all Azure SQL Databases must have Microsoft Defender for SQL enabled. You need to enforce this policy across all subscriptions using a scalable, automated approach. What should you do?

You manage an Azure SQL Database that hosts a reporting workload. Users report that a monthly aggregation query sometimes completes in 2 seconds, but other times takes over 60 seconds, even though the underlying data volume is unchanged. Query Store shows the query has two distinct plans, and the faster plan is not always chosen. You need to force the faster plan for this query. What should you do?

You are the database administrator for a company that uses Azure SQL Managed Instance named MI1 in the East US region. The company requires a disaster recovery plan that ensures the instance can be failed over to a secondary region with minimal data loss and minimal downtime. The solution must support read-only workloads on the secondary. You need to configure the disaster recovery solution. What should you do?

You are configuring automated backup retention for Azure SQL Managed Instance. The compliance policy requires that you be able to restore a database to any point within the last 90 days, and that you keep backups for a minimum of 7 years for auditing purposes. Which backup retention policy should you configure?

You are reviewing a PowerShell script that is part of an Azure Automation runbook. The script is intended to monitor resource usage of an Azure SQL Database and trigger an alert if DTU usage exceeds 80%. The script runs successfully but does not trigger the alert. What is the most likely reason?

Refer to the exhibit. You are deploying an Azure SQL Database using this ARM template. After deployment, you need to automate the scaling of the database to a higher service tier when DTU consumption exceeds 80% for 5 minutes. Which Azure service should you use to trigger the scaling?

You are the database administrator for a logistics company that uses Azure SQL Database. A nightly Azure Automation runbook must trigger a stored procedure in the database after each successful run. The runbook authenticates to Azure using a system-assigned managed identity for the Automation account. You need to grant the managed identity the least-privilege permissions required to execute the stored procedure. Which two actions should you perform? (Choose two.)

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 have an Azure SQL Database that is experiencing high wait times on RESOURCE_SEMAPHORE waits. You need to identify the root cause. What should you check?

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 configure Azure SQL Database to allow connections only from Azure services and from a specific on-premises IP range. Which firewall rule configuration should you apply at the server level?

You need to monitor the long-running queries in an Azure SQL Database. Which dynamic management view should you query to see queries that have been running for more than 30 seconds?

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication? (Choose Two.)

You have an Azure SQL Managed Instance named MI1 in the West Europe region. The instance hosts a mission-critical database that must be recoverable in a different region within 15 minutes of a regional outage. The solution must minimize data loss and administrative overhead. You need to implement a disaster recovery solution. What should you do?

You administer an Azure SQL Managed Instance that hosts a mission-critical OLTP database. The instance has 16 vCores and uses the Business Critical service tier. Users report periodic stalls during index maintenance. You observe high PAGEIOLATCH_SH waits and want to reduce their impact without changing the service tier. What should you do first?

Which TWO methods can be used to automate index maintenance in Azure SQL Database?

You are responsible for an Azure SQL Database that supports an order-processing application. The database is configured with the General Purpose service tier. During month-end processing, the application experiences slow response times. You need to determine whether the performance issue is caused by the database reaching its resource limits. Which metric should you monitor in Azure Monitor?

You are monitoring an Azure SQL Database using Intelligent Insights. You receive an alert that 'Query performance degradation' was detected. After reviewing the details, you find that a specific query now has a higher duration and is using a different execution plan. What is the recommended first step to troubleshoot?

You manage an Azure SQL Database that contains a table with sensitive columns. You need to ensure that a specific application can access the data in those columns in plaintext, while other applications see ciphertext. You also need to minimize changes to the application code. What should you implement?

You need to configure monitoring for an Azure SQL Database to meet the following requirements: - Alert when average DTU consumption exceeds 90% for 10 minutes. - Track failed logins. - Analyze query performance over the last 30 days. Which THREE Azure services or features should you use? (Choose three.)

You administer an Azure SQL Database that hosts an order-entry application. The database is in the General Purpose tier and uses the default configuration. Users complain that some inserts and updates occasionally wait for several seconds. You observe that the database's log write throughput is frequently near its tier limit, and that many small transactions are committed one row at a time. You need to reduce log write pressure without changing the service tier. What should you do?

You are a database administrator for a manufacturing company that uses Azure SQL Database. The company wants to automate the deployment of database schema changes across multiple databases in an elastic pool. You need to implement a solution that tracks which scripts have been applied and ensures they are applied only once per database. Which two actions should you perform? (Choose two.)

A company runs SQL Server 2019 on Azure Virtual Machines in an availability set. They need to achieve high availability for a critical database with automatic failover and no shared storage. The solution must minimize downtime during planned maintenance. What should they implement?

Which TWO actions should you take to implement a secure environment for Azure SQL Database that meets the principle of least privilege?

You are reviewing an ARM template for Azure SQL Database. The exhibit shows a resource definition for Transparent Data Encryption (TDE). You need to ensure that the database uses customer-managed keys (CMK) stored in Azure Key Vault instead of service-managed keys. What additional configuration is required?

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?

Your company uses Azure SQL Database with active geo-replication. You notice that the secondary database in a different region has a high log write latency. Users report that the primary database performance is normal. What is the most likely cause?

You are managing an Azure SQL Database that experiences intermittent performance degradation. Query Store shows a significant increase in waits of type RESOURCE_SEMAPHORE. Which action should you take to resolve the issue?

You are reviewing a JSON representation of an Azure SQL Database firewall rule. What is the effect of this rule?

Your Azure SQL Database is configured with the Hyperscale service tier. You observe increased redo log latency. Which resource is most likely the bottleneck?

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?

You are monitoring an Azure SQL Database using Intelligent Insights. The built-in intelligence detects a performance issue and suggests a specific index to create. The database is running the Business Critical service tier. You want to automatically implement this recommendation without manual intervention. What should you configure?

Which TWO actions should you take to secure Azure SQL Database against SQL injection attacks?

You need to automate the deployment of database schema changes across multiple Azure SQL Databases in a development environment. Which Azure service is designed for this purpose?

You are a database administrator for a healthcare company. You have an Azure SQL Database that stores patient records. The database is currently accessible from the public internet via firewall rules. You need to implement a secure environment that meets the following requirements: - All traffic to the database must be private and not traverse the internet. - The database must be accessible from an Azure Virtual Machine in a specific VNet. - The solution must minimize management overhead and cost. - You need to ensure that the database can be failed over to a secondary region in case of an outage. What should you do?

The database 'mydb' is experiencing performance issues during peak hours. Based on the exhibit, what is the most likely cause?

Refer to the exhibit. Which action should you take to improve performance?

You are a database administrator for a healthcare company that uses Azure SQL Database. The compliance team requires that all automated tasks that modify data must be logged with the identity that executed them. You need to configure an elastic job to run a T-SQL script that updates patient records. Which authentication method should you use for the job step to meet the auditing requirement?

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 have an Azure SQL Database server named sqlsrv1. Several application teams connect using SQL logins. The security team mandates that all authentication use Microsoft Entra ID and that multifactor authentication be enforceable. You need to configure the server so that Entra ID authentication is available to database users. What should you do first?

You have an Azure SQL Database that must be automatically restarted every night to clear the procedure cache. You plan to use elastic jobs in Azure SQL Database. What should you create first?

You are configuring Azure SQL Database firewall rules. You need to allow a range of IP addresses (192.168.1.0 to 192.168.1.255) to connect to the database. Which firewall rule should you create?

You manage an Azure SQL Database that has automatic tuning enabled. You receive an alert that the database is experiencing plan regression. The automatic tuning has forced a plan, but performance is still poor. What should you do first?

Your organization requires that all Azure SQL Database administrators use multi-factor authentication (MFA) when connecting. Which authentication method must be used?

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 has an Azure SQL Database that uses a failover group with a secondary in a different region. You need to ensure that read-only queries are directed to the secondary database to offload the primary. What should you configure?

Which of the following are valid methods to authenticate to Azure SQL Database using Microsoft Entra ID? (Select all that apply.)

Refer to the exhibit. You run these commands in an Azure SQL Database. What is the result?

Which THREE actions can you take to monitor and optimize database resources in Azure SQL Database? (Choose three.)

Your Azure SQL Database is accessed by multiple applications. You need to ensure that all connections use Transport Layer Security (TLS) 1.2 or higher. Which TWO configurations should you verify or enable?

Which TWO options are valid methods to optimize query performance in Azure SQL Managed Instance?

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?

Your Azure SQL Database contains sensitive financial data. You need to audit all data modifications (INSERT, UPDATE, DELETE) and store the audit logs in a central Azure Storage account for compliance. What should you configure?

Drag and drop the steps to configure transparent data encryption (TDE) for an Azure SQL Database using a customer-managed key in Azure Key Vault in the correct order.

You are monitoring an Azure SQL Database and notice high PAGELATCH waits. What is the most likely cause?

Which THREE components are required to run Elastic Database Jobs for Azure SQL Database? (Choose three.)

You have an Azure SQL Database that uses a firewall rule allowing access from a specific range of IP addresses. A developer reports that they cannot connect from a new IP address that falls outside the allowed range. You need to temporarily allow the developer's IP address for 24 hours without affecting existing rules. What should you do?

You manage an Azure SQL Database that uses the Business Critical service tier. A critical reporting query normally completes in under 5 seconds but occasionally takes over 60 seconds. You observe that during these slow executions, the query uses a different execution plan that performs a large number of physical reads. You need to ensure that the fast plan is used consistently for this query. What should you do?

You are the DBA for an Azure SQL Database named OrdersDB. The security team requires that you implement row-level security (RLS) to ensure that sales representatives can only view orders for their own region. You need to create a security policy that filters rows based on the sales representative's region. Which two actions should you perform? (Choose two.)

You manage an Azure SQL Managed Instance that hosts a mission-critical database. The instance is in the East US 2 region. You need to configure a disaster recovery solution that meets the following requirements: (1) provide a readable secondary in a different Azure region, (2) support automatic failover, and (3) minimize administrative effort. Which two actions should you perform? (Choose two.)

You are monitoring an Azure SQL Database using Azure Monitor metrics. You need to configure alerts to notify the operations team when the database is approaching resource limits. Which two metrics should you use to detect potential CPU and I/O bottlenecks? (Choose two.)

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 preparing a disaster recovery runbook. You plan to use the PowerShell command shown in the exhibit to restore a database to a different region. What must be true for this command to succeed?

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?

Which TWO of the following are benefits of using Azure SQL Database failover groups compared to active geo-replication alone?

You observe that the average of Maximum DTU consumption over the last hour is consistently above 90%. What should you do next?

You are responsible for an Azure SQL Database that hosts a mission-critical application. You need to configure an alert that fires when the database's CPU usage exceeds 90% for more than 10 minutes. You want to use the built-in Azure Monitor metrics for Azure SQL Database. Which metric should you use?

A database administrator manages an Azure SQL Managed Instance that hosts a mission-critical database. The administrator needs to automate the execution of a T-SQL script that performs index maintenance and then sends an email notification with the results. The solution must use native Azure SQL Managed Instance capabilities and minimize external dependencies. Which two actions should the administrator perform? (Choose two.)

Which TWO actions are required to automate the export of an Azure SQL Database to a BACPAC file on a monthly basis? (Choose two.)

You administer an Azure SQL Database named HRDB. The security team requires that any connection to HRDB from outside the corporate network be blocked, but on-premises applications must continue to connect over the existing site-to-site VPN. The database currently has a public endpoint and a firewall rule allowing all Azure services. You need to restrict access so that only the VPN subnet can reach HRDB. What should you configure?

Refer to the exhibit. You are configuring Azure SQL Database Transparent Data Encryption (TDE) with customer-managed keys (CMK) stored in Azure Key Vault. The deployment uses a user-assigned managed identity. However, after deployment, the TDE status shows 'Inaccessible'. What is the most likely cause?

You manage an Azure SQL Managed Instance that hosts a business-critical database. The compliance team requires that the instance be recoverable in a different Azure region if the primary region becomes unavailable, and that the failover be initiated manually only by authorized administrators. You need to configure a disaster recovery solution. Which two actions should you perform? (Choose two.)

You are optimizing an Azure SQL Database that uses the General Purpose service tier. The database has a high volume of small transactions and you observe wait statistics showing significant WRITELOG waits. You need to reduce WRITELOG waits for this database. What should you do?

You manage an Azure SQL Database that runs an online transaction processing (OLTP) workload. Users report that transactions are slow during business hours. You query sys.dm_os_wait_stats and notice a high number of PAGEIOLATCH_SH waits. You need to reduce these waits without changing the application. What should you do?

Which TWO metrics should you monitor in Azure SQL Database to detect a potential memory pressure issue?

You need to automate the creation of an Azure SQL Database and a corresponding server-level firewall rule to allow access from a specific IP address. The deployment must be repeatable and version-controlled. What should you use?

You are a database administrator for a logistics company that uses Azure SQL Database. The company requires an automated task to run every night at 02:00 UTC to archive old shipment records into a separate table. You need to minimize administrative overhead and ensure the task runs reliably even if there is a transient failure. What should you implement?

You are configuring Azure SQL Database firewall rules for a new application. The application runs on Azure VMs in the same region. To minimize latency and security risk, which approach should you use?

You are responsible for automating backups of on-premises SQL Server databases to Azure Blob Storage. The solution must use the least administrative effort and provide point-in-time restore capability. What should you implement?

You are managing an Azure SQL Database that uses the Business Critical service tier. You need to ensure that the database can handle a sudden increase in transaction log write throughput without experiencing log write waits. Which factor should you primarily consider?

You need to ensure that only specific Azure services can access your Azure SQL Database server. You want to allow traffic from Azure services but block all other traffic. What should you configure?

You need to monitor Azure SQL Database performance over time and receive alerts when CPU usage exceeds 80%. Which Azure service should you use?

Your company uses Azure SQL Database and needs to comply with GDPR. You must implement data classification and protection. Which TWO actions should you take? (Choose two.)

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 the database administrator for a global e-commerce company. The company runs its production SQL Server on an Azure Virtual Machine (IaaS) in the West US region. The database is mission-critical and requires a Recovery Point Objective (RPO) of 5 minutes and a Recovery Time Objective (RTO) of 30 minutes in the event of a regional disaster. The VM uses premium SSDs and is backed up daily to a Recovery Services vault with geo-redundant storage. The current backup policy takes full backups weekly, differential backups daily, and transaction log backups every 15 minutes. The VM is in an availability set for high availability within the region. During a recent regional outage simulation, the database was unavailable for 4 hours because the backups needed to be restored to a different region, and the restore process took longer than expected. You need to recommend a solution to meet the RPO and RTO requirements. What should you do?

You deploy a new Azure SQL Database and need to ensure that all queries are logged for performance analysis. Which configuration should you enable?

You are designing a secure environment for Azure SQL Database. Which TWO of the following are recommended practices for network security?

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?

Your company is migrating an on-premises SQL Server database to Azure SQL Managed Instance. You need to ensure that the database is protected by Microsoft Defender for Cloud (formerly Azure Security Center) with advanced threat protection. What should you enable?

You are configuring monitoring for an Azure SQL Database that uses the vCore purchasing model. The database is in the General Purpose service tier. You need to receive an alert when the database's CPU consumption exceeds 90 percent for 10 minutes. What should you create?

You have an Azure SQL Database in the Business Critical tier with zone redundancy enabled. The database experiences a brief outage due to a zone failure. How does the platform automatically recover?

You need to audit all successful and failed login attempts to an Azure SQL Database. Which feature should you enable?

You manage an Azure SQL Database that is part of a failover group. You need to automate the failover to the secondary region in the event of a disaster. Which approach should you use?

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

You are designing a secure environment for Azure SQL Managed Instance. The company requires that all database backups be encrypted using customer-managed keys stored in Azure Key Vault. Which combination of actions should you take?

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?

You are optimizing an Azure SQL Database that uses the vCore purchasing model. The database is experiencing high RESOURCE_SEMAPHORE waits. You need to identify two actions that can reduce these waits. (Choose two.)

A company uses Azure SQL Database and wants to automate the process of refreshing a development database from production backups weekly. Which Azure service should be used to orchestrate this process including restore and post-restore scripts?

You are analyzing the exhibit KQL query that queries Azure Diagnostics logs for Query Store runtime statistics. The query is intended to show average CPU time per hour for each database. However, the result shows no data for the last 24 hours, although Query Store is enabled on all databases. What is the most likely reason?

You are a database administrator for a retail company that uses Azure SQL Database with the Serverless compute tier. The database experiences unpredictable idle periods, and you want to minimize costs by automatically pausing the database when it is idle for more than 60 minutes and resuming it when a connection is attempted. However, you also need to ensure that a critical reporting job that runs every hour can connect even if the database is paused. What should you do?

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 a database administrator for a global e-commerce company. The company uses Azure SQL Database for its product catalog, which is a mission-critical OLTP workload. The database is currently deployed in the West US region using the Business Critical service tier with zone redundancy enabled. The database size is 200 GB and grows at 10 GB per month. The company has a disaster recovery requirement: in the event of a regional outage, the database must be failed over to a secondary region with an RPO of less than 5 seconds and an RTO of less than 1 minute. Additionally, the secondary database must be readable to support read-heavy reporting workloads. The solution must minimize additional compute costs. You need to recommend a configuration. Which option should you choose?

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 manage an Azure SQL Database that is critical for a financial application. The database has a read-heavy workload, and you need to monitor and diagnose performance issues. You want to enable a feature that automatically captures detailed information about query plans and runtime statistics for later analysis. Which feature should you enable?

You are configuring security for an Azure SQL Managed Instance. The instance will host a critical application that requires always encrypted with secure enclaves. Which TWO actions must you take to support this feature? (Choose two.)

You are configuring performance monitoring for Azure SQL Managed Instance. You need to collect and analyze query performance data with minimal overhead. Which solution should you use?

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 configuring security for an Azure SQL Database. The security policy requires that all connections to the database must be encrypted and that the encryption keys must be managed by your organization. You need to implement Transparent Data Encryption (TDE) with a customer-managed key (CMK) stored in Azure Key Vault. What should you do first?

You are monitoring an Azure SQL Database that hosts a financial application. You notice that the average DTU consumption is 20%, but occasionally spikes to 95% for 5-minute intervals. Users report slow response times during these spikes. You need to ensure consistent performance without over-provisioning resources. What should you do?

You administer an Azure SQL Database that contains a table named dbo.Employees with columns for Social Security Number and salary. Company policy requires that support staff querying the table see only the last four digits of the Social Security Number and a masked salary value, while the payroll application, which connects with a different login, must see the actual values. You need to implement this with the least administrative effort and without changing the application queries. What should you do?

You have an Azure SQL Database that uses automatic tuning. You notice that a forced plan regression is causing performance degradation. You need to revert to the previous plan and prevent the automatic tuning from forcing the same plan again. What should you do?

Your Azure SQL Database is experiencing high DTU consumption. You need to identify the top resource-consuming queries. What should you do?

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.

Which TWO of the following are native options to automate index maintenance on Azure SQL Database? (Select exactly two.)

Which THREE components are required to configure a failover group for Azure SQL Database? (Choose three.)

You need to audit all schema changes in an Azure SQL Database and store the audit logs in a storage account for long-term retention. What should you enable?

You are troubleshooting a performance issue on an Azure SQL Database. The database is experiencing high PAGELATCH_EX waits. Which TWO measures can help reduce these waits?

You have an Azure SQL Database named SalesDB. You need to grant a user named 'ReportingUser' the ability to read all data in the Sales schema but not modify any data. You want to follow the principle of least privilege. What should you do?

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance to host multiple databases for different business units. The security policy requires that all connections to the managed instance must use encrypted connections (TLS 1.2 or higher). Additionally, the company wants to minimize the attack surface by restricting network access. You need to configure the managed instance to enforce encrypted connections and block all public internet traffic. What should you do?

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?

Your organization has Azure SQL Database with several databases. You need to implement a solution that allows a junior DBA to view the security logs for failed logins but not modify any security settings. What is the minimum role assignment needed on the logical server?

The query returns a list of query hashes with high average duration. You need to identify which queries are most likely causing CPU pressure. What additional metric should you include?

You have an Azure SQL Database that needs to be accessed by an application running on an Azure VM. The VM is in a different subscription. You want to minimize administrative overhead and ensure secure connectivity without exposing the database to the public internet. What should you do?

You are a database administrator for a retail company that uses Azure SQL Database. The security team wants to prevent SQL injection attacks by ensuring that all application queries use parameterized statements. Which built-in Azure feature should you enable to help detect and alert on potential SQL injection attempts?

You manage an Azure SQL Database that experiences high PAGELATCH_EX waits on tempdb during peak transaction processing. You need to reduce these waits. Which two actions should you perform? (Choose two.)

You are managing an Azure SQL Database that supports a reporting application. Users report that queries are slow during business hours. You suspect that the database is experiencing CPU pressure. Which metric should you monitor to confirm this?

You need to automate the deployment of Azure SQL Database logical servers and databases using Bicep. What is the best practice for storing the administrative password securely?

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?

Refer to the exhibit. You run the above PowerShell command to set the Transparent Data Encryption (TDE) protector for an Azure SQL Database server. What is the result?

You are troubleshooting a performance degradation on an Azure SQL Database. You notice that the database is hitting the maximum DTU limit frequently. Which action should you take first to reduce DTU consumption?

You are optimizing an Azure SQL Database that runs a heavy reporting workload. The database uses the General Purpose tier. You notice that many queries are scanning large tables. What is the best first action to improve performance?

You are designing a secure environment for Azure SQL Database. Which authentication method provides the strongest security and supports multi-factor authentication?

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?

Your company is migrating on-premises SQL Server databases to Azure SQL Managed Instance. You need to ensure that database backups are encrypted at rest using customer-managed keys stored in Azure Key Vault. You also need to allow the backup service to access the keys. What should you configure?

You are responsible for an Azure SQL Database that hosts a reporting application. Users complain that queries are slow during business hours. You run a query against sys.dm_db_resource_stats and see that the average CPU percentage is consistently above 90%, while other metrics are low. You need to identify the queries contributing most to CPU usage. What should you use?

You manage an Azure SQL Database named InventoryDB. The security team requires that all data in the database be encrypted at rest using a key that your organization controls and can revoke. You need to implement this requirement with minimal administrative overhead. What should you do?

You are deploying a new application on Azure SQL Database. The application requires that all connections use a specific login, 'AppUser', with the least privileges necessary. The login should only be able to execute stored procedures in the 'Sales' schema and should not have direct access to underlying tables. What should you do?

You manage an Azure SQL Database that is experiencing higher than expected DTU consumption. You need to identify which queries are consuming the most resources. Which dynamic management view should you query?

You are the administrator for an Azure SQL Database. The security team requires that all authentication to the database use Microsoft Entra ID (formerly Azure AD) and that multi-factor authentication (MFA) be enforced. You need to configure the database to meet this requirement. What should you do first?

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 managing an Azure SQL Database that uses the vCore purchasing model. You need to configure an alert that fires when the database's CPU usage exceeds 80% for 10 minutes. You want to minimize administrative effort. What should you do?

You are optimizing an Azure SQL Database that uses the General Purpose service tier. You observe that the database is experiencing high wait times due to PAGEIOLATCH_SH waits. You need to reduce these waits. Which two actions should you perform? (Choose two.)

You are a database administrator for a multinational corporation that uses Azure SQL Managed Instance. The instance is part of a failover group for disaster recovery. You need to automate the process of testing the failover group by performing a planned failover to the secondary region and then failing back. The test must be performed monthly during a maintenance window. The automation must ensure that the failover group is in a healthy state before and after the test and must log the results to a table. What should you do?

You administer an Azure SQL Database that must run a nightly index maintenance job. The job must execute T-SQL against the database, and you want to minimize administrative overhead by avoiding external schedulers or custom code. You create an elastic job agent and a target group that includes the database. What should you do next to define the T-SQL command that the job runs?

You have an Azure SQL Database in the General Purpose tier. You notice that the log write throughput is consistently above the service tier limit, causing transaction throttling. You need to resolve this without moving to Business Critical. What should you do?

You manage a SQL Server 2019 availability group on Azure Virtual Machines. The availability group has three replicas: one primary and two synchronous secondary replicas in the same region. A fourth asynchronous replica is in a different Azure region for disaster recovery. During a planned maintenance window, you need to perform a manual failover to one of the synchronous secondary replicas without data loss. Which Transact-SQL command should you run on the target secondary replica?

Refer to the exhibit. An Azure SQL Database is receiving Intelligent Insights degradation alerts. Which action should be taken 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?

A company uses Azure SQL Database for a critical application. They need to automate the process of exporting a database to a storage account every night, ensuring the export is consistent. The solution must minimize administrative overhead. What should they use?

Your organization requires that all changes to sensitive data in an Azure SQL Database be logged for compliance. You need to capture who changed what data and when, and store the logs in a Log Analytics workspace for analysis. What should you configure?

You manage an Azure SQL Database named SalesDB in the East US region, Business Critical tier. The database has a long-term retention policy that stores weekly full backups in a geo-redundant storage account. During a compliance audit, you need to prove that you can recover SalesDB to a point in time in the event of a complete East US regional outage. You must perform a geo-restore to the West US region. What is the maximum retention period for point-in-time restore that you can expect when performing this geo-restore?

You are reviewing an Azure RBAC role assignment for an Azure SQL Database. The role assignment shown in the exhibit is intended to allow a user to read data from the database. However, the user reports they cannot connect to the database. What is the most likely reason?

Your organization uses Azure SQL Managed Instance and needs to implement a defense-in-depth strategy. Which THREE security controls should you implement? (Choose three.)

You need to monitor the storage space usage of an Azure SQL Database over time. Which tool should you use?

You manage an Azure SQL Database that supports a reporting workload. Users report that a complex aggregation query returns different elapsed times throughout the day, but the logical reads remain consistent. You need to determine whether the query is experiencing CPU pressure or waiting on resources. Which Query Store view should you use to analyze wait statistics for the query?

You are a database administrator for a healthcare company that uses Azure SQL Database. You need to automate the process of exporting a BACPAC file to Azure Blob Storage every day at 3:00 AM. You want to minimize development effort and use an Azure-native service. What should you use?

You are the database administrator for a company that uses Azure SQL Database. The company has a policy that database administrators must not have access to sensitive data in a specific table named EmployeeSalaries. You need to implement a solution that allows DBAs to manage the database but prevents them from viewing or modifying data in the EmployeeSalaries table. What should you implement?

You are automating the creation of an Azure SQL database. You need to ensure that the deployment is idempotent using Azure Resource Manager (ARM) templates. Which deployment mode should you use?

Which TWO are benefits of using a failover group for Azure SQL Database? (Select two.)

You manage an Azure SQL Database in the Business Critical service tier. The database has a zone-redundant configuration. During a planned maintenance event, you need to ensure that the database remains online with no data loss and minimal downtime. What should you configure?

You are the database administrator for a large e-commerce company that uses Azure SQL Database for its transactional systems. The environment consists of 100 databases spread across 10 elastic pools in different regions. You need to implement an automated solution to perform the following tasks every night: (1) Run integrity checks (DBCC CHECKDB) on all databases, (2) Rebuild indexes with fragmentation > 30%, (3) Update statistics with full scan for databases that have had significant data changes (>20% of rows). The solution must minimize manual intervention, provide centralized logging, and be resilient to failures (e.g., if one database fails, the others should continue). Which approach should you use?

Your organization uses Azure SQL Database and wants to restrict access to only specific on-premises IP addresses. The database has a public endpoint. Which security feature should you configure?

You manage an Azure SQL Database that experiences periodic performance degradation. You need to identify the top queries by CPU consumption over the last hour. Which dynamic management view should you query?

Your company uses Azure SQL Database and needs to restrict access to a specific column containing credit card numbers. Only users with the 'CreditCardViewer' role should see the full number; others should see only the last four digits. Which feature should you implement?

You manage a SQL Server 2019 Always On availability group on Azure Virtual Machines. The availability group has two synchronous replicas in an availability set and one asynchronous replica in a different Azure region. During a planned maintenance window, you need to patch the operating system on the primary replica with minimal downtime and no data loss. What should you do first?

You are monitoring an Azure SQL Database that uses the General Purpose service tier. You need to configure an alert that triggers when the database's CPU usage exceeds 90% for 10 minutes. What should you use?

Your Azure SQL Database is experiencing a sudden increase in wait time due to PAGEIOLATCH_SH waits. What should you do to reduce these waits?

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?

You are a database administrator for a hospital that uses Azure SQL Database to store patient records. The hospital's security policy requires that all database access be authenticated using Microsoft Entra ID (formerly Azure AD). You have already created a Microsoft Entra ID user for yourself and granted you the 'db_owner' role. You now need to create a new Microsoft Entra ID user for a nurse who needs read-only access to the database. What should you do first?

You are the DBA for a company using Azure SQL Database. The security team requires that all data at rest in the database be encrypted with a customer-managed key (CMK) stored in Azure Key Vault, and that the DBA team be able to rotate the key without any downtime. You have already created an Azure Key Vault and an RSA 2048-bit key. What should you do next to meet these requirements?

You are configuring authentication for Azure SQL Database. Which TWO of the following are supported authentication methods?

You manage an Azure SQL Managed Instance that hosts a database with a high volume of transactions. You notice that the transaction log is growing rapidly and is not being truncated. You need to identify the cause and resolve the issue. What should you do?

You have an Azure SQL Database that uses the General Purpose service tier. The database is critical and you need to ensure that it remains available during a planned patching event that updates the underlying hardware. What does Azure SQL Database provide to maintain availability during such events?

You administer a SQL Managed Instance in the West Europe region. You need to create a disaster recovery replica in North Europe with automated failover. The replica must be readable and support backups. What should you configure?

A company manages an Azure SQL Database that stores sensitive customer data. The security team mandates that all connections to the database use Azure Active Directory (Azure AD) authentication and that no SQL authentication logins exist. You are tasked with implementing this requirement. What should you do first?

Which THREE of the following are best practices for managing keys in Azure Key Vault for use with Azure SQL Database TDE?