The BULK path string 'csv/busfare/tripdata_2020*.csv' uses a wildcard (*) after the prefix tripdata_2020. In Synapse serverless SQL pools, this pattern matches all files in the specified folder whose names start with that string and end with the .csv extension. It does not navigate into subfolders unless a recursive wildcard (/**/) is used.

The parameter FIRSTROW = 2 instructs the engine to start reading data from the second row of the file. In the context of CSV processing, skipping the first row is the standard configuration when the first row contains column headers rather than actual data records.

Microsoft Official Documentation: "Query CSV files using serverless SQL pool in Azure Synapse Analytics." Section: Path to files. (Describes wildcard usage for file filtering).

Microsoft Official Documentation: "OPENROWSET (Transact-SQL) - Azure Synapse Analytics." Section: Arguments - FIRSTROW. (Specifies that FIRSTROW identifies the row number where the data begins; skipping row 1 implies it is a header).

University of California, Berkeley (Data Science Courseware): "Data Ingestion and Storage Patterns." (Explains standard practices for skipping headers in bulk load operations using SQL).

Box 1: The requirement to support cross-database queries is a key differentiator. Azure SQL Managed Instance supports cross-database queries natively (using standard T-SQL), whereas Azure SQL Database (Singleton) does not support them in the same way (requiring Elastic Query). Furthermore, the JSON schema includes properties specific to Managed Instances, such as subnetId, vCores, and licenseType. A logical server (Microsoft.Sql/servers) does not utilize subnetId or vCores at the top level; those are properties of the instance or database.

Box 2: The resource includes a subnetId property which references a subnet within a Virtual Network. In Azure Resource Manager (ARM) templates, if a resource (the Managed Instance) depends on another resource (the Virtual Network) created within the same template to establish its networking configuration, it must explicitly define that dependency in the dependsOn array. Therefore, the instance depends on [parameters('virtualNetworkName')]. It cannot depend on targetName because that is the name of the instance itself (circular dependency).

Microsoft.Sql managedInstances - Bicep, ARM template & Terraform AzAPI reference | Microsoft Learn

Reference: Lists the valid properties for Microsoft.Sql/managedInstances, confirming that subnetId, vCores, storageSizeInGB, and licenseType are top-level properties for this resource type.

URL: https://learn.microsoft.com/en-us/azure/templates/microsoft.sql/managedinstances?pivots=deployment-language-arm-template

Run cross-database queries on Azure SQL Managed Instance | Microsoft Learn

Reference: "Azure SQL Managed Instance supports cross-database queries. This functionality allows you to query data from one database and use it in another database."

URL: https://learn.microsoft.com/en-us/azure/azure-sql/managed-instance/cross-database-queries-overview?view=azuresql

Define the order for deploying resources in ARM templates | Microsoft Learn

Reference: "When you deploy resources, you may need to make sure some resources exist before others... You use the dependsOn element to specify a dependency."

URL: https://learn.microsoft.com/en-us/azure/azure-resource-manager/templates/resource-dependency

Azure Log Analytics is the optimal destination for meeting monitoring requirements for Azure SQL audit logs. It provides a centralized workspace for collecting, correlating, and analyzing data from various sources. By sending audit logs to a Log Analytics workspace, you can leverage the powerful Kusto Query Language (KQL) for complex queries, create custom alerts based on specific audit events, and build interactive dashboards and workbooks for visualization. This enables proactive monitoring, threat detection, and compliance analysis directly within the Azure portal.

A. Azure Storage: This destination is primarily intended for long-term archival and compliance, not for active, real-time analysis, querying, or alerting. Analyzing logs requires an additional service.

B. Azure Event Hubs: This is a data streaming platform used to ingest audit logs and forward them to other downstream services, such as a third-party SIEM. It is a pipeline, not an analysis tool itself.

1. Microsoft. (2023). Auditing for Azure SQL Database and Azure Synapse Analytics. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/azure-sql/database/auditing-overview.

Section: "Auditing destinations": This section explicitly states

"Log Analytics workspace: For storing audit logs in Azure Monitor logs. In Log Analytics

you can use Log queries to run advanced analytics on your audit logs. Log Analytics provides a single location for all your logs

allowing you to correlate audit logs with other logs." This directly supports the use of Log Analytics for analysis and monitoring.

2. Microsoft. (2023). Create a Log Analytics workspace in the Azure portal. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/azure-monitor/logs/quick-create-workspace.

Section: "Overview": "A Log Analytics workspace is a unique environment for Azure Monitor log data... The workspace provides a geographic location for the data

data isolation

and scope for configurations like data retention." This establishes the workspace as the foundational environment for log analysis in Azure.

To implement Cell-Level Encryption in Azure SQL Database, you must establish the encryption key hierarchy. First, create a database master key (DMK), which is the root of encryption in the database. Second, create a certificate protected by the DMK. Third, create a symmetric key protected by the certificate (symmetric keys are used for the actual data encryption due to performance). Finally, update Col1 with the encrypted values using the EncryptByKey function. Note: While OPEN SYMMETRIC KEY is a required session command to perform the update, the high-level actions that define the configuration and application of encryption are the creation of the three hierarchy objects and the data update itself.

Microsoft Learn. (2025). Encrypt a Column of Data. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encrypt-a-column-of-data?view=sql-server-ver16 (Sections: "Create database master key", "Create a certificate", "Create a symmetric key", "Encrypt the data").

Microsoft Learn. (2025). CREATE SYMMETRIC KEY (Transact-SQL). Retrieved from https://learn.microsoft.com/en-us/sql/t-sql/statements/create-symmetric-key-transact-sql

Microsoft Learn. (2025). Encryption Hierarchy. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/encryption-hierarchy

To enable Transparent Data Encryption (TDE) on a SQL Server instance, you must establish a trust hierarchy to secure the encryption keys. The process follows a strict dependency chain:

1. Master Key in master: First, create a Database Master Key (DMK) in the system master database. This key is used to encrypt certificates.
2. Certificate in master: Next, create a certificate in the master database, which is protected by the DMK created in the previous step.
3. Database Encryption Key in DB1: Finally, switch context to the user database (DB1) and create the Database Encryption Key (DEK). This DEK is encrypted using the certificate located in the master database.

After these three objects are created in sequence, you can set encryption to ON for the database.

Microsoft Learn - SQL Server: "Transparent data encryption (TDE)"

Section: "Enable TDE"

Step-by-step citation:

"Create a master key." (Code example: USE master; GO; CREATE MASTER KEY...)

"Create or obtain a certificate protected by the master key." (Code example: USE master; GO; CREATE CERTIFICATE...)

"Create a database encryption key and protect it by the certificate." (Code example: USE AdventureWorks2022; GO; CREATE DATABASE ENCRYPTION KEY...)

URL: https://learn.microsoft.com/en-us/sql/relational-databases/security/encryption/transparent-data-encryption?view=sql-server-ver16#enable-tde

Microsoft Learn - Azure SQL VM: "Configure Transparent Data Encryption - SQL Server on Azure VMs"

Note: Confirms that for SQL Server on Azure VMs (IaaS), the manual TDE setup matches the on-premises SQL Server procedure involving the master database hierarchy.

URL: https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/transparent-data-encryption?view=azuresql

For a 50-TB database, performance is the primary constraint. PHYSICAL_ONLY significantly reduces execution time by limiting checks to the integrity of the physical structure (page headers and allocation) and avoiding expensive logical consistency checks on data and metadata. It skips checking column values and computed columns.

The NOINDEX option further optimizes performance by instructing DBCC CHECKDB to skip intensive logical checks of non-clustered indexes for user tables. Since non-clustered indexes are redundant structures, their exclusion prioritizes the integrity of the base data (heaps or clustered indexes) while drastically reducing the I/O and CPU overhead required for the consistency check process.

Official Microsoft Documentation: "DBCC CHECKDB (Transact-SQL)" – Sections: PHYSICAL_ONLY and NOINDEX Arguments. [Microsoft Learn]

Official Microsoft Documentation: "Optimizing DBCC CHECKDB performance" – Best practices for large databases. [Microsoft Learn]

Academic Reference: Database Systems: The Complete Book, 2nd Edition (Garcia-Molina, Ullman, Widom) – Chapter on Recovery and Integrity Management (Page 894 regarding physical vs. logical consistency).

Official Microsoft Documentation: "Troubleshoot DBCC CHECKDB performance" – Identifier: 2024-SQL-MNT.

The configuration must follow the dependency chain of the Resource Governor architecture. First, create a resource pool to define the specific physical limits (CPU and memory) for App1. Second, create a workload group and map it to that resource pool; this group serves as the container for App1's queries. Third, create a user-defined classifier function that identifies sessions originating from "App1" (e.g., using APP_NAME()) and returns the name of the workload group created in the previous step. Finally, modify Resource Governor to register this new classifier function (ALTER RESOURCE GOVERNOR WITH (CLASSIFIER_FUNCTION = ...) ) and apply the configuration (ALTER RESOURCE GOVERNOR RECONFIGURE).

Microsoft Learn. (2023). Resource Governor. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/resource-governor

Microsoft Learn. (2023). Create a Resource Pool. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/create-a-resource-pool

Microsoft Learn. (2023). Create and Test a Classifier User-Defined Function. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/create-and-test-a-classifier-user-defined-function

Microsoft Learn. (2023). Enable Resource Governor. Retrieved from https://learn.microsoft.com/en-us/sql/relational-databases/resource-governor/enable-resource-governor

A Log Analytics workspace is the primary data store for Azure Monitor Logs. It is specifically designed to collect, aggregate, and analyze monitoring data from various Azure resources, including SQL Server on Azure Virtual Machines. The key requirement is to query metrics using the Kusto Query Language (KQL). A Log Analytics workspace natively uses KQL for complex analysis and querying of the collected data. By deploying the Azure Monitor Agent to the virtual machine, metrics can be automatically collected and sent to the workspace, fulfilling the requirement to minimize administrative effort.

B. Azure Event Hubs: This is a data streaming platform. While it can ingest monitoring data, it does not provide a native capability to query that data using KQL. An additional service would be required.

C. Azure SQL Database: This is a relational database service that uses Transact-SQL (T-SQL) for queries, not the Kusto Query Language (KQL). Storing and querying metrics here would be a custom, high-effort solution.

D. an Azure Blob storage container: This is an object storage service. It does not have a built-in query engine that uses KQL. Querying data stored here would require another service like Azure Data Explorer, increasing complexity.

---

1. Microsoft Docs

"Log Analytics tutorial": This document explicitly states

"Log Analytics is a tool in the Azure portal that you use to edit and run log queries against data in Azure Monitor Logs. You might write a simple query that returns a set of records and then use features of Log Analytics to sort

filter

and analyze them. ... You write a query in Kusto Query Language (KQL)..." This confirms that Log Analytics workspaces are queried using KQL.

Source: Microsoft Corporation. (2023). Log Analytics tutorial. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/azure-monitor/logs/log-analytics-tutorial

2. Microsoft Docs

"Overview of Azure Monitor Agents": This document details how the Azure Monitor Agent collects data from virtual machines and where it sends the data. "The Azure Monitor agent (AMA) collects monitoring data from the guest operating system of Azure and hybrid virtual machines and delivers it to Azure Monitor. ... Send data to Azure Monitor Logs (Log Analytics workspace)." This supports using a Log Analytics workspace as the destination for VM metrics with minimal administrative effort.

Source: Microsoft Corporation. (2023). Overview of Azure Monitor agents. Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/azure-monitor/agents/agents-overview

3. Microsoft Docs

"Enable SQL Insights (preview)": This document describes a comprehensive

low-effort solution for monitoring SQL environments

which directly applies to the scenario. "SQL Insights stores its data in one or more Log Analytics workspaces. To enable SQL Insights

you need to create a workspace or select an existing one. ... All data collected by SQL Insights is stored in a single Log Analytics workspace." This reinforces that the standard

integrated solution for SQL monitoring uses a Log Analytics workspace.

Source: Microsoft Corporation. (2023). Enable SQL Insights (preview). Microsoft Learn. Retrieved from https://learn.microsoft.com/en-us/azure/azure-monitor/insights/sql-insights-enable

When the master database is lost, the link between the new instance's Service Master Key and the existing SSISDB Database Master Key is broken.

1. Attach: First, bring the existing SSISDB files into the new instance.
2. Open: Use the password (created during the original SSISDB setup) to manually open the DMK.
3. Encrypt: Add encryption by the new SMK to allow automatic decryption in the future.
4. Properties: SSISDB requires TRUSTWORTHY set to ON and the clr enabled server configuration set to 1 to execute the stored procedures and assemblies required for package execution.

Microsoft Documentation: Backup, Restore, and Move the SSIS Catalog. Section: "Restoring the SSISDB Database." (Describes the requirement to open the master key using the password and re-encrypting it with the Service Master Key).

Microsoft Documentation: SSIS Catalog (SSISDB). Section: "Permissions and Security." (Explains the requirement for the TRUSTWORTHY database property and CLR integration for SSISDB operations).

Microsoft SQL Server 2019 Administration: Database Master Key and Service Master Key Hierarchy. (Covers the manual decryption process when the SMK is replaced/lost).

Official Microsoft Learn: Manage the SSISDB Catalog on an Azure VM. (Confirms the same recovery procedures apply to SQL Server on Azure Virtual Machines).

To restore the database to the point of failure (Wednesday at 10:00), you must follow the most efficient recovery chain supported by the Full Recovery Model:

1. Restore the most recent Full Backup: The weekly full backup from Sunday serves as the baseline for the restore sequence.
2. Restore the most recent Differential Backup: Differential backups are cumulative, meaning they contain all changes made since the last full backup. The Wednesday differential backup (taken at 01:00) includes all changes from Monday and Tuesday. Therefore, restoring intermediate differentials (Monday/Tuesday) is unnecessary and incorrect.
3. Restore the Transaction Log Backups: Finally, apply the transaction log chain created after the differential backup was taken (from Wednesday 01:00) up to the time of failure (Wednesday 10:00).

Microsoft Learn - Differential backups (SQL Server):

"A differential backup is based on the most recent, previous full data backup... A differential backup captures only the data that has changed since that full backup... To restore a database... restore the most recent full backup... restore the most recent differential backup... restore all subsequent transaction log backups."

Microsoft Learn - Restore a SQL Server Database to a Point in Time (Full Recovery Model):

"The sequence of restore statements... 1. Restore the last full database backup... 2. Restore the last differential database backup... 3. Restore every transaction log backup in sequence..."