Hotspot Question You have an Azure virtual machine named Server1 that has Microsoft SQL Server installed. Server1 contains a database named DB1. You have a logical SQL server named ASVR1 that…

Question

Exhibit

Answer Area

• Sync data by using:
  A service endpointAzure Private LinkThe SQL Data Sync private link
• Set the database isolation level to:
  REPEATABLE READSERIALIZABLESNAPSHOT ISOLATION

Explanation

To ensure private connectivity for SQL Data Sync between an Azure VM and Azure SQL Database, utilize Azure Private Link and configure Snapshot Isolation for optimal database performance.

Approach. The question asks to prepare the environment for SQL Data Sync migration from an Azure VM (Server1) to an Azure SQL Database (ADB1) while ensuring the connection from Server1 to ADB1 does NOT use a public endpoint. This requires two correct selections:

1. For 'Sync data by using:', select 'Azure Private Link'. Azure Private Link allows you to connect privately to Azure PaaS services (like Azure SQL Database) from your virtual networks via a private endpoint. This brings the service into your VNet's private IP space, ensuring that traffic never traverses the public internet, thus meeting the requirement of 'NOT use a public endpoint'. When an Azure SQL Database is configured with a Private Endpoint (powered by Azure Private Link), the Azure VM (Server1) can connect to it using its private IP address within the VNet.

2. For 'Set the database isolation level to:', select 'SNAPSHOT ISOLATION'. SQL Data Sync performs read and write operations on the participating databases. Microsoft documentation and best practices for SQL Data Sync recommend enabling SNAPSHOT ISOLATION on both hub and member databases. This isolation level uses row versioning, which allows read operations to proceed without blocking write operations, and vice versa. This significantly reduces the likelihood of deadlocks and concurrency conflicts, improving the reliability and performance of the sync process.

Common mistakes.

• common_mistake. Regarding the connection method:

• 'A service endpoint' is incorrect because while service endpoints secure traffic over the Azure backbone, they do not provide a private IP address for the PaaS service within your VNet and traffic technically still uses the public endpoint of the service, albeit over a private route within Azure. The requirement is 'NOT use a public endpoint', which Private Link achieves more directly by assigning a private IP.
• 'The SQL Data Sync private link' is a very specific option that might confuse test-takers. While SQL Data Sync can utilize private link connectivity, 'Azure Private Link' is the foundational Azure networking service that enables private connectivity for Azure SQL Database itself, which is what Server1 needs to connect to ADB1 privately. The question focuses on the general connection from Server1 to ADB1, which is enabled by the Azure Private Link service configured on ADB1.

Regarding the database isolation level:

• 'REPEATABLE READ' and 'SERIALIZABLE' are incorrect because while they are valid SQL Server isolation levels, they are generally not recommended for data synchronization tools like SQL Data Sync. These levels use more restrictive locking mechanisms which can lead to increased contention, blocking, and deadlocks during concurrent read/write operations performed by the sync service, negatively impacting performance and reliability compared to SNAPSHOT ISOLATION.

Concept tested. This question tests the candidate's knowledge of Azure networking services for private connectivity to PaaS resources (Azure Private Link vs. Service Endpoints) and the specific configuration requirements and best practices for using Azure SQL Data Sync, particularly regarding database isolation levels for optimal concurrency and performance.

Reference. https://docs.microsoft.com/en-us/azure/sql-database/sql-data-sync-private-link-overview https://docs.microsoft.com/en-us/azure/sql-database/sql-data-sync-best-practices

No community discussion yet for this question.