AZ-305 Question 96
Single answerYou are designing a data integration solution for a financial services firm that needs to ingest daily data from an on-premises SQL Server into an Azure Synapse Analytics dedicated SQL pool. The firm requires data cleansing and transformation as part of the ingestion process. They also mandate that credentials for the on-premises SQL Server be securely stored and that the process be scheduled to run once per day. Which design approach should you recommend?
- A
Use Azure Data Factory with a Self-Hosted Integration Runtime and store credentials in Azure Key Vault, scheduling the pipeline from within Data Factory.
- B
Use Azure Logic Apps with the built-in SQL connector, embedding the on-premises SQL credentials in the code, triggered by a daily recurrence.
- C
Use Azure Database Migration Service to replicate data from the on-premises SQL Server to Synapse in near real-time, storing credentials in Azure Key Vault.
- D
Implement Azure Synapse Link for SQL Server on-premises to stream data directly into the Synapse environment, storing credentials in local configuration files.
Show answer and explanation
Correct answer: A
Explanation
Azure Data Factory is a common solution for orchestrating data movement and transformations at scale. The Self-Hosted Integration Runtime securely connects to on-premises SQL databases, while Azure Key Vault safely stores secrets. This approach aligns with best practices outlined in Azure documentation regarding secure, scheduled pipeline execution and data transformations. See Microsoft documentation on Azure Data Factory (https://learn.microsoft.com/azure/data-factory/) for more details.
- A. Correct.
Option 1 is correct. Azure Data Factory, coupled with a Self-Hosted Integration Runtime, is well-suited for securely connecting to on-premises SQL databases. By storing credentials in Azure Key Vault, you maintain a secure approach to secrets management. Data Factory provides built-in capability for daily scheduling of pipelines, and you can include transformations (e.g., via Mapping Data Flows) within the data flow.
- B. Incorrect.
Option 2 is incorrect. While Logic Apps can be triggered on a schedule, embedding on-premises SQL credentials in the code is not a secure method of storing credentials. Logic Apps are typically used for lightweight integrations, not large-scale, ongoing data transformations and movements.
- C. Incorrect.
Option 3 is incorrect. Azure Database Migration Service is primarily used for migrating databases to the cloud or performing continuous replication for migration scenarios, but it is not intended for recurring data transformations, cleansing tasks, or orchestrated workflows. Although storing credentials in Key Vault is possible, this approach does not meet the daily data cleansing and transformation requirements in a straightforward way.
- D. Incorrect.
Option 4 is incorrect. Azure Synapse Link (for SQL) supports certain Azure databases like Azure Cosmos DB or dedicated SQL pools within Azure, but there is no native Azure Synapse Link for on-premises SQL Server that replicates data directly in real time. Additionally, storing credentials in local files is not secure or recommended.