Rediger

Del via


Azure Synapse Link for SQL FAQ

In this guide, find the most frequently asked questions for Azure Synapse Link for SQL.

No, currently Azure Synapse Link for SQL supports Synapse dedicated SQL pool as the data destination store only.

No, currently Azure Synapse Link for SQL only supports Azure SQL Database and SQL Server (starting with SQL Server 2022) as the source.

No, you can add or remove tables when the link connection is running. The tables that are removed will stop being replicated automatically. The newly added tables start from initial load with full snapshot and then do incremental synchronization. The rest of the tables in the same link connection will not be impacted.

You should remove the destination tables before starting a link connection. You can do that by either manually removing the tables from Synapse dedicated SQL pool, or in the Action on existing target table dropdown list, choose the option to Drop and recreate table on target, to let it automatically drop existing tables on the Synapse dedicated SQL pool every time when starts.

After you add tables to a link connection, these tables in the source database won't allow columns to be added, dropped, or altered. To do these schema changes, the table should be removed from the link connection temporarily and added back after schema is changed.

Yes, you can.

Yes, you can. Similar like all the workloads in Synapse. You can enable Azure Synapse Link for SQL in Azure Synapse-managed virtual network to replicate data from SQL to Synapse in a secure manner. You can protect against data exfiltration by allowing outbound connectivity from the managed virtual network only to approved targets using managed private endpoints.

Core count indicates the compute size required to replicate data from source store to the Synapse dedicated SQL Pool. It depends on the replication workload pattern on the source database, number of tables, rate of changes occurring, and size of rows, etc. We recommend you to start with a small core count and test the latency with the actual workload. Latency of replication can be reflected in the monitoring page of link tables; Time of last processed data column indicates how far the replication is running behind.

Should I choose “enable transaction consistency across tables”?

When this option is enabled, a transaction spanning across multiple tables on the source database is always replicated to the destination database in a single transaction. This, however, will create overhead on the overall replication throughput. When the option is disabled, each table replicates changes in its own transaction boundary to the destination in parallel connections, thus improving overall replication throughput. When you want to enable transaction consistency across tables, also make sure the transaction isolation levels in your Synapse dedicated SQL pool is READ COMMITTED SNAPSHOT ISOLATION.

How should I select the structure type of my destination table in the Synapse dedicated SQL pool?

You can refer to Indexing tables - Azure Synapse Analytics | Microsoft Docs to understand the three options for table structure type. When clustered columnstore index is chosen, data type with max length (for example, VARCHAR(MAX)) is not supported.

Do I need to clean up data in the landing zone?

No. There is a background thread that removes committed files after around 24 hours as long as the link is continuously running. When the link is stopped then the entire landing zone folder is removed.

Azure Synapse Link for SQL is available in the following regions:

Area Region
Asia Pacific East Asia
Southeast Asia
Australia Australia East
Australia Southeast
Azure Government US Gov Arizona
US Gov Texas
US Gov Virginia
Brazil Brazil South
Canada Canada Central
Canada East
China China East 2
China East 3
China North 2
China North 3
Europe North Europe
West Europe
France France Central
France South
Germany Germany West Central
India Central India
South India
Japan Japan East
Japan West
Korea Korea Central
Norway Norway East
Qatar Qatar Central
Sweden Sweden Central
Switzerland Switzerland North
Switzerland West
United Arab Emirates UAE North
United Kingdom UK South
UK West
United States Central US
East US
East US 2
North Central US
South Central US
West Central US
West US
West US 2
West US 3

Where is my landing zone located?

For Azure SQL Database, the landing zone is fully managed and is located in the same region as your target Azure Synapse Analytics workspace. For SQL Server 2022, the landing zone is customer-managed and can be located where you like.

What type of encryption is applied to the landing zone?

For Azure SQL Database, the landing zone is fully managed and is encrypted with the same key as the target Azure Synapse Analytics workspace - either platform-managed or customer-managed. For SQL Server 2022, the landing zone is customer-managed and can be encrypted with either a platform-managed key or a customer-managed key.

I updated all of the records in my source table. When I ran a query against the dedicated SQL pool I saw all of my data gone. I re-ran the query and all of the data was back - with the updates. Why does this happen?

The ingestion service processes updates as a delete followed by an insert - all in the same transaction. By default, dedicated SQL pools run in READ UNCOMMITTED mode - which allows you to see the changes before the transaction has been committed. To ensure that you do not see these in-process changes, you can turn on READ COMMITTED SNAPSHOT ISOLATION.

No. You need to edit the link connection in the Azure Synapse Analytics environment to add the new tables. You can add tables to a running connection without stopping it - the new tables will be included in the replication when you publish the changes to the link connection.

What is the latency for data replicated from Azure SQL Database and SQL Server 2022 to Azure Synapse Analytics dedicated SQL pools?

We do not have published latency SLA. If you are seeing high latency, you can adjust the number of cores allocated to the link connection, the size of the target dedicated SQL pool, or adjust the transactional consistency on write configuration to suit your workload.

What format is used for the landing zone data? Can it be used for other purposes?

The landing zone is a transient data store, and we do not support using that data for any purpose other than Azure Synapse Link for SQL.

Azure Synapse Link for SQL uses a push model where the source database writes data into a landing zone and is moved into the target dedicated SQL pool by an ingestion service that can either run continuously (providing near-real-time data processing) or in schedule mode. The copy activity uses a pull model where the data is queried from the source database and written into the target location on a scheduled basis.

This restriction is for on-page data only. For off-page data, the only thing that counts against the 7,500-byte limit is the 24-byte pointer to the off-page data.

A new change feed processor has been integrated into the Azure SQL Database and SQL Server 2022 engine to enable this functionality.

If the SAS has expired for the user managed storage account for the Landing Zone, use ALTER DATABASE SCOPED CREDENTIAL to update the database credential for the new SAS.

If the Azure Synapse workspace is removed, Azure SQL Database will stop data replication from landing zone into Azure Synapse. The system stored procedure sp_change_feed_drop_table_group will be called automatically, and the storage account for the landing zone is managed and will be cleaned up automatically. If this fails, you might receive error 22739 from the SynapseGatewayClient in sys.dm_change_feed_errors. If this occurs, you can manually drop the changefeed table group with sp_change_feed_drop_table_group.

In SQL Server, since landing zone storage account is user managed, data will continue to be published to the landing zone. You should disable the Azure Synapse Link by dropping the relevant table groups with sp_change_feed_drop_table_group, and manually remove the storage account for the landing zone.

In SQL Server, the landing zone storage account is user managed. If the landing zone storage account is no longer accessible, you will see errors in the sys.dm_change_feed_errors. You should disable the Azure Synapse Link by dropping the relevant table groups with sp_change_feed_drop_table_group.