Azure SQL Database Administrator Free Sample Questions

The correct answer is to use sp_query_store_clear_message_queues. When a failover occurs in a geo-replication or failover group setup, the Query Store data is replicated to the secondary. However, performance statistics (runtime stats) are not, leading to a potential mismatch. Running this procedure on the secondary just before a planned failover clears any queued-up, in-flight statistics, preventing the carry-over of potentially misleading performance data from the primary. This allows the new primary to generate fresh, relevant statistics post-failover, mitigating issues like PSP optimization problems. Failing back is disruptive. Automatic Tuning might eventually fix it but is not the immediate, targeted solution. Clearing the entire Query Store is a drastic measure that loses all historical performance data.

Setting a Microsoft Entra ID admin is a prerequisite for enabling Microsoft Entra-only authentication.

This is the specific action that disables SQL authentication logins (sa and others) and enforces that all connections must use Microsoft Entra authentication. This feature is available for Azure SQL Database, Managed Instance, and Synapse, and can be configured for SQL Server on Azure VM through specific setup.

Elastic Jobs are specifically designed for running T-SQL scripts across a group of Azure SQL Databases, including all databases in an elastic pool. It provides native capabilities for defining target groups, creating job steps with T-SQL, scheduling, and monitoring execution, making it the most suitable and efficient tool for this scenario. Azure Automation is a more general-purpose automation service and would require more complex scripting to iterate through all databases. SQL Server Agent is not available in Azure SQL Database.

An Always On availability group with synchronous-commit mode ensures an RPO of zero by requiring the transaction to be hardened on the secondary before committing on the primary. This configuration also provides automatic failover capabilities for a low RTO and allows the secondary replica to be configured for read-access, meeting the reporting requirement. An FCI provides HA at the instance level but doesn't inherently provide a readable secondary. Asynchronous-commit mode would not guarantee an RPO of zero. Log shipping has a higher RPO and RTO.

Data Migration Assistant (DMA) is the correct tool for this task. Beyond its primary function of identifying compatibility issues, DMA can run a performance data collection assessment. This assessment gathers detailed information about the source SQL Server's workload, which is then used to recommend an appropriate Azure SQL Database service tier and size. It collects the necessary metrics like CPU, memory, IOPS, and query statistics. DMS is for executing the migration, not for assessment. The SSMS Performance Dashboard is for real-time monitoring, not for long-term data collection for baselining.

While DDM and RLS are powerful security features, they don't natively support conditional unmasking based on the data in another column for the same user. DDM applies the mask to all non-privileged users, and RLS filters entire rows. The most direct and flexible way to implement this specific logic (unmask for your own department, mask for others) is to create a security view. The view's logic would contain a CASE statement that checks if the employee's department matches the analyst's department (e.g., using USER_NAME() or session context) and returns either the actual Salary or a masked value. The analyst is then granted permission only to the view.

The sys.dm_exec_requests DMV provides information about each request currently executing in SQL Server, including the sql_handle for the executing batch. To get the actual text of the SQL statement, you must pass this sql_handle to the sys.dm_exec_sql_text dynamic management function. Joining these two allows you to see the SQL text for a specific session ID. sys.dm_exec_sessions provides session-level information but not the currently executing statement text. sys.dm_tran_locks shows lock information but not the query text.

This is true. In an Azure environment, the availability group listener's IP address needs a mechanism to float between the VMs hosting the replicas. An Azure Load Balancer is used for this purpose. It is configured with a health probe to detect which node is the primary replica and directs traffic to that node's IP address accordingly.

The uniqueString() function is designed for this purpose. It creates a deterministic 13-character hash string based on one or more seed values you provide, such as the resource group ID. Because it's deterministic, if you run the same template with the same seed values again, it will generate the same unique string, which is essential for idempotent deployments. The guid() function generates a new random GUID on every run, which would cause the deployment to fail on subsequent runs as it would try to create a new resource with a different name.

In Azure SQL Database, including the Hyperscale tier, the maximum transaction log generation rate is directly tied to the number of vCores allocated to the compute replica. To increase the log rate limit beyond 100 MBps, the number of vCores must be increased. Adding read replicas helps with read scaling but does not affect the write or log generation capacity of the primary replica. Increasing max database size is irrelevant to the log rate limit. Switching to the DTU model is not an option for Hyperscale.