Box 1: HASH The T-SQL syntax DISTRIBUTION = <Type> (<ColumnName>) is exclusive to Hash-distributed tables. ROUND_ROBIN and REPLICATE do not accept a column argument. Hash distribution is appropriate here to distribute large fact table data evenly across nodes based on the ProductKey.

Box 2: OrderDateKey To minimize the time required to delete old data, the table should be partitioned by date. This allows for partition switching or truncation, which are metadata-only operations and significantly faster than running DELETE statements on millions of rows. OrderDateKey is the only column representing the date dimension compatible with the partition range values (e.g., 20170101) provided in the RANGE RIGHT clause.

Microsoft Official Documentation | Distributed tables

Reference: "Hash-distributed tables... A hash-distributed table distributes table rows across the Compute nodes by using a deterministic hash function to assign each row to one distribution... The algorithm uses a distribution column to determine the distribution."

Microsoft Official Documentation | Partitioning tables

Reference: "Partitioning enables data operations... effectively on subsets of data... For example, you can quickly delete old data from your table by switching out a partition."

Microsoft Official Documentation | CREATE TABLE (Transact-SQL)

Reference: Syntax for CREATE TABLE in Azure Synapse Analytics clearly shows DISTRIBUTION = HASH ( distribution_column_name ) vs DISTRIBUTION = ROUND_ROBIN.

SQL Server's authentication mode is a server-level configuration stored in the Windows Registry. To change this setting programmatically using Transact-SQL, the extended stored procedure xpinstanceregwrite is used. This procedure allows writing values to the SQL Server-specific keys in the registry. To enable mixed mode authentication, the LoginMode value under the HKEYLOCALMACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\...\MSSQLServer key must be set to 2. xpinstanceregwrite is the correct tool among the options to perform this specific registry modification. A restart of the SQL Server service is required for the change to take effect.

A. spaddremotelogin: This is a deprecated procedure used for managing remote server logins, which is unrelated to the instance's primary authentication mode.

B. xpgrantlogin: This procedure is used to grant a Windows user or group access to SQL Server, which is a login management task, not a server configuration change.

C. spchangeuserslogin: This is a deprecated procedure used to remap database users to server logins, typically for resolving orphaned users. It does not alter the server authentication mode.

1. Microsoft. (2023). Change server authentication mode. Microsoft Learn.

Section: "To change the authentication mode using Transact-SQL"

Content: This official document explicitly provides a T-SQL script that uses EXEC xpinstanceregwrite ... N'LoginMode'

REGDWORD

2 to change the server authentication to mixed mode.

2. Microsoft. (2023). xpinstanceregwrite (Transact-SQL). Microsoft Learn.

Section: "Syntax" and "Remarks"

Content: This document describes the function of xpinstanceregwrite as a procedure that writes a value to the registry for a given instance of SQL Server

confirming its suitability for the task described in the question.

The exhibit displays Live Query Statistics, identifiable by the "Estimated query progress" bar at the top and the dynamic row count labels (e.g., "392 of 446 (87%)") on the operators, which show real-time data flow versus estimates. A standard Actual Execution Plan is static and typically displays final row counts without progress percentages.

The Key Lookup operator indicates the query utilizes a non-clustered index that does not cover all requested columns, forcing a costly lookup into the clustered index for every row found. This generates significant random I/O and is the primary trigger for the "Missing Index" warning shown (Impact: 99.6%), indicating the query requires tuning via a covering index.

Microsoft Learn. (n.d.). Live Query Statistics. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/performance/live-query-statistics

Relevance: Confirms that Live Query Statistics displays the "Estimated query progress" and real-time execution statistics (X of Y rows) as seen in the exhibit.

Microsoft Learn. (n.d.). Key Lookup Showplan Operator. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/showplan-logical-and-physical-operators-reference

Relevance: Defines the Key Lookup operator and explains its occurrence when a non-clustered index requires retrieval of data from the clustered index (heap or table), often signaling the need for a covering index to improve performance.

To use Change Tracking, it must first be enabled at the database level before it can be enabled for individual tables. The ALTER DATABASE ... SET CHANGETRACKING = ON command is the required initial step. This command configures the database-wide settings for the feature, such as the retention period and auto-cleanup behavior. Only after this database-level configuration is complete can you proceed to enable change tracking on specific tables using the ALTER TABLE statement. Therefore, this is the correct first step in the solution.

A. Run the sys.spcdcenabledb stored procedure.

This procedure enables Change Data Capture (CDC), which is a different feature from Change Tracking. CDC captures historical data changes, whereas Change Tracking only records that a change occurred.

B. Run the alter table statement and specify the enable changetracking clause.

This is the second step in the process. You cannot enable Change Tracking on a table until it has been enabled for the database itself (the action in option C).

D. Run the spaddarticle stored procedure.

This stored procedure is used for setting up SQL Server Replication. It adds an article (typically a table) to a publication, which is unrelated to Change Tracking.

---

1. Microsoft Learn. (2023). Enable and Disable Change Tracking (SQL Server).

Reference: In the "Enabling Change Tracking for a Database" section

the documentation states

"Before you can use change tracking

you must enable it at the database level." It then provides the exact syntax shown in option C: ALTER DATABASE SET CHANGETRACKING = ON (...).

URL: https://learn.microsoft.com/en-us/sql/relational-databases/track-changes/enable-and-disable-change-tracking-sql-server

2. Microsoft Learn. (2024). ALTER DATABASE SET Options (Transact-SQL).

Reference: Under the "Arguments" section for CHANGETRACKING

it details the syntax for enabling the feature at the database level

confirming that this is a database-level setting that must be configured first.

URL: https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-set-options

3. Microsoft Learn. (2023). sys.spcdcenabledb (Transact-SQL).

Reference: The documentation explicitly states

"Enables change data capture (CDC) for the current database." This confirms that option A pertains to CDC

not Change Tracking.

URL: https://learn.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sys-sp-cdc-enable-db-transact-sql

After a failover, the primary replica of the database is running on a different logical server, which has a different server name (endpoint). To restore connectivity, the application's connection string must be updated to point to the fully qualified domain name (FQDN) of the new primary server. While using a failover group listener automates this redirection, the question implies a manual step is needed, which is typical for active geo-replication scenarios without a failover group. The application must be redirected to the new endpoint to re-establish a connection.

A. Configure SalesSQLDb1 as writable.

The failover process automatically promotes the secondary replica to a read-write primary. This action is redundant and unnecessary.

C. Update the firewall rules of SalesSQLDb1.

Firewall rules, like logins, should be configured on the secondary server before a failover occurs to ensure a smooth transition and minimize downtime.

D. Update the users in SalesSQLDb1.

Contained database users are replicated with the database. Server-level logins must be configured on the secondary server before the failover to prevent authentication failures.

---

1. Microsoft Docs: Active geo-replication - Azure SQL Database.

Section: Managing application upgrades and failover

Quote: "If you are using active geo-replication to build a business continuity solution

you should verify that... The application is ready for failover. ... After failover

you must ensure that the connection string of your application is updated to connect to the new primary." This directly supports that updating the connection string is a required post-failover action.

2. Microsoft Docs: Configure a failover group for Azure SQL Database.

Section: Failover (planned)

Quote: "When your application is ready

you can initiate a failover. ... The failover group read-write listener is redirected to the new primary." This explains the automated alternative

but if not used

the connection string pointing to the old server FQDN would need manual updating

reinforcing the principle behind answer B.

3. Microsoft Docs: Business continuity overview with Azure SQL Database.

Section: Recover a database to the existing server

Quote: "To ensure full access to the new primary database... after failover

you must ensure that the appropriate security configuration (such as server-level firewall rules... and SQL logins and permissions) is in place for the target secondary server and contained in the master database." This confirms that firewall rules (C) and logins (related to D) are pre-failover configuration requirements

not post-failover recovery steps.

The requirement explicitly demands support for master/target (MSX/TSX) jobs (Multi-Server Administration). This feature relies on the SQL Server Agent.

1. Azure SQL Database: Does not support the SQL Server Agent (replaced by Elastic Jobs) and consequently does not support MSX/TSX.
2. Azure SQL Managed Instance: While it supports the SQL Server Agent, Microsoft documentation explicitly states that Multi-server administration (Master/Target) is not supported on Managed Instance.
3. SQL Server on Azure Virtual Machines: This option provides full feature parity with on-premises SQL Server. It is the only Azure deployment option listed that supports the MSX/TSX architecture.

Therefore, despite the desire to minimize administrative effort (which usually favors PaaS), the functional requirement mandates the use of SQL Virtual Machines (IaaS) for both roles.

Microsoft Learn. (2024). T-SQL differences between SQL Server and Azure SQL Managed Instance. Section: SQL Server Agent. Retrieved from https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/transact-sql-tsql-differences-sql-server#sql-server-agent

Quote: "Multi-server administration is not supported."

Microsoft Learn. (2023). Automate management tasks using SQL Agent jobs in Azure SQL Managed Instance. Retrieved from https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/job-automation-managed-instance

Context: Confirms SQL Agent support is limited and does not include MSX/TSX capabilities found in on-premises SQL Server.

To configure email alerts for SQL Server Agent jobs, you must follow a specific functional hierarchy. First, Enable Database Mail at the instance level to provide the SMTP infrastructure required for sending messages. Second, you must Enable the email settings for the SQL Server Agent by associating a specific Mail Profile with the Agent service; without this, the Agent cannot utilize the Database Mail system. Finally, you Create a job notification within the specific Backup Job's properties, targeting the dbadmin1 operator to trigger on the "Failure" condition. Creating a "job alert" is incorrect here as alerts typically respond to performance thresholds or SQL errors, whereas notifications are the native mechanism for job-specific outcomes.

Official Vendor Documentation: Microsoft Learn. Configure SQL Server Agent Mail to Use Database Mail. Section: "Before you begin." [Link: https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/configure-sql-server-agent-mail-to-use-database-mail]

Official Vendor Documentation: Microsoft Learn. Database Mail. Section: "Database Mail Architecture." [Link: https://learn.microsoft.com/en-us/sql/relational-databases/database-mail/database-mail]

Official Vendor Documentation: Microsoft Learn. Connect an Operator to an Alert. (Explaining the distinction between Alerts and Job Notifications). [Link: https://learn.microsoft.com/en-us/sql/ssms/agent/connect-an-operator-to-an-alert]

University Courseware: University of North Texas, CSCE 4350: Database Systems. Lecture: SQL Server Management and Automation. (Covers Agent configuration and Operator notification workflows).

Official Vendor Documentation: Microsoft Learn. Create a Job. Section: "To create a SQL Server Agent job (Notifications page)." [Link: https://learn.microsoft.com/en-us/sql/ssms/agent/create-a-job]

The question asks for a type of backup to meet availability requirements. In Azure SQL, the automated backup service that enables Point-in-Time Restore (PITR) uses a combination of full, differential, and transaction log backups. Differential backups are critical for meeting availability requirements, specifically for reducing the Recovery Time Objective (RTO). A differential backup contains only the data that has changed since the last full backup. Using a recent differential backup during a restore process significantly reduces the number of transaction log backups that need to be applied, which makes the overall restore operation faster and brings the database online more quickly.

A. transaction log: While essential for minimizing data loss (low RPO) and enabling point-in-time recovery, relying only on full and transaction log backups can lead to longer restore times.

B. point-in-time restore (PITR): This is a restore capability or method that uses the underlying full, differential, and transaction log backups. It is not a type of backup itself.

D. long-term retention (LTR): LTR is a feature for storing backups for extended periods (up to 10 years) for compliance and archival, not for meeting operational availability and rapid recovery needs.

1. Microsoft Learn

"Automated backups in Azure SQL Database": This document outlines the backup strategy used by Azure SQL. It states

"Azure SQL Database and SQL Managed Instance use SQL Server technology to create full backups every week

differential backups every 12-24 hours

and transaction log backups approximately every 10 minutes." This confirms that differential backups are a fundamental type used in the automated solution that provides high availability.

2. Microsoft Learn

"Differential Backups (SQL Server)": This document explains the purpose of differential backups. Under the "Overview of differential backups" section

it states

"This can speed up the process of restoring a database because fewer log backups have to be applied." This directly supports the reasoning that differential backups improve RTO

a key component of availability.

3. Microsoft Learn

"Recover a database using automated database backups": This page describes the restore process. It confirms that PITR is the method of recovery: "Point-in-time restore (PITR) allows you to restore a database... to an earlier point in time." This distinguishes the restore method from the backup types.

4. Microsoft Learn

"Long-term retention overview": This document clarifies the purpose of LTR. The overview section states

"Many applications have regulatory

compliance

or other business purposes that require you to retain database backups beyond the 1-35 days provided by short-term retention." This positions LTR for archival

not for immediate availability.

The proposed solution is to create an external table using a serverless SQL pool. An external table in a serverless SQL pool is a metadata definition that allows you to query data directly from files in Azure Data Lake Storage. This action does not, by itself, load, insert, or move any data into Table1, which resides in a separate dedicated SQL pool. The primary goal is to populate Table1 in the dedicated SQL pool with the source data plus an additional DateTime column. Since the solution only defines a way to read the source data and does not perform the required data insertion into the target table, it fails to meet the goal.

A. The solution is incorrect because creating an external table is only a preparatory step for querying data in the lake; it does not accomplish the required task of loading the data into the dedicated SQL pool table.

1. Microsoft Documentation. (2023). Query storage files with serverless SQL pool in Azure Synapse Analytics. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/synapse-analytics/sql/query-data-storage. This document clarifies that serverless SQL pools are for querying data in place ("schema-on-read")

not for loading it into other storage systems like dedicated pools.

2. Microsoft Documentation. (2023). Data loading strategies for dedicated SQL pool in Azure Synapse Analytics. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/synapse-analytics/sql-data-warehouse/design-elt-data-loading. This article outlines the correct methods for loading data into a dedicated SQL pool

such as using the COPY statement or PolyBase

none of which are mentioned in the proposed solution.

3. Microsoft Documentation. (2023). CREATE EXTERNAL TABLE (Transact-SQL). Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/statements/create-external-table-transact-sql?view=azure-sqldw-latest&preserve-view=true&tabs=serverless. The documentation specifies that this statement creates a table that references data stored externally

confirming it does not perform a data load operation.