Edit

Troubleshoot: Azure Synapse Link for Azure SQL Database after failover of an Azure SQL Database

  • Article

This article is a guide to troubleshoot and configure Azure Synapse Link for Azure SQL Database after failover of an Azure SQL Database. This article applies only to databases in Azure SQL Database.

Symptom

For the safety of data, users may choose to set auto-failover group for Azure SQL Database. By setting failover group, users can group multiple geo-replicated databases that can protect a potential data loss. However, when Azure Synapse Link for Azure SQL Database has been started for the table in the Azure SQL Database and the database experiences failover, Synapse Link will be disabled in the backend even though its status is still displayed as running.

Resolution

You must stop Synapse Link manually and configure Synapse Link according to the new primary server's information so that it can continue to work normally.

  1. Launch Synapse Studio.

  2. Open the Integrate hub.

  3. Select the Synapse Link whose database has failover occurred.

  4. Select the Stop button.

    A screenshot of Synapse Studio. The Integrate hub is open and the Link Connection linkconnection1 is selected. The Stop button is highlighted.

  5. Open the Manage hub. Under External connections, select Linked services.

  6. In the list of Linked services, select the linked service whose database failed over.

    A screenshot of Synapse Studio. The Manage hub is open. In the list of Linked services, the AzureSqlDatabase1 linked service is highlighted.

  7. You must reset the linked service connection string based on the new primary server after failover so that Synapse Link can connect to the new primary logical server's database. There are two options:

    • Use the auto-failover group read/write listener endpoint and use the Synapse workspace's managed identity (SMI) to connect your Synapse workspace to the source database. Because of Read/Write listener endpoint that automatically maps to the new primary server after failover, so you only need to set it once. If failover occurs later, it will automatically use the fully-qualified domain name (FQDN) of the listener endpoint. Note that you still need to take action on every failover to update the Resource ID and Managed Identity ID for the new primary (see next step).
    • After each failover, edit the linked service Connection string with the Server name, Database name, and authentication information for the new primary server. You can use a managed identity or SQL Authentication.

    The authentication account used to connect to the database, whether it be a managed identity or SQL Authenticated login to the Azure SQL Database, must have at least the CONTROL permission inside the database to perform the actions necessary for the linked service. The db_owner permission is similar to CONTROL.

    To use the auto-failover group read/write listener endpoint:

    Screenshot of the Azure Synapse Studio Edit linked service dialog. The FQDN of the read/write listener endpoint is entered manually.

  8. You must refresh the Resource ID and Managed Identity ID after every failover. Open the Integrate hub. Select your Synapse Link.

  9. The next step depends on the connection string you chose previously.

    • If you choose to use the Read/Write listener endpoint for updating linked service connection string, you must update the SQL logical server resource ID and Managed identity ID corresponding to the new primary server manually.
    • If you provided the new primary server's connection information, select the Refresh button.

    A screenshot of the Integrate hub of Synapse Studio. The Refresh button updates the SQL logical server resource ID and the managed identity ID.

  10. Azure Synapse Link for Azure SQL Database currently cannot restart the synchronization from before the failover. Before restarting the Link connection, you must empty the target table in Azure Synapse if data is present. Or, check the option to Drop and recreate table on target, as seen in the following screenshot.

    A screenshot of the Integrate hub of Synapse Studio. The Drop and recreate table on target option is highlighted. The Start button is highlighted.

  11. Finally, restart the Azure Synapse Link. On the Integrate hub and with the desired Link connection open, select the Start button.

Next steps