Create DNS alias for dedicated SQL pool in Synapse workspace for disaster recovery

This blog will walk through the details on how to enable custom DNS (Domain Name System) entries on an Azure Synapse dedicated pool inside an Azure Synapse workspace in case of disaster recovery.

The DNS alias provides a translation layer that can redirect your client programs to different servers. This layer spares you the difficulties of having to find and edit all the clients and their connection strings (in disaster recovery implementation). This is not supported out-of-box, so we need to take extra steps to enable this feature. There are some limitations, so please read these steps carefully.

Background

Custom DNS (Domain Name System) is supported on Synapse SQL dedicated pool (previously known as Azure SQL Data Warehouse) but is not natively supported when a dedicated pool is created in a Synapse workspace.

For the purpose of this blog, we need to be aware of the differences between a ‘connected’ workspace (sometimes known as workspace lite or workspace experience) and a Synapse workspace.

A ‘connected’ workspace is when an existing SQL dedicated pool (previously known as SQL DW) is migrated/upgraded to the Synapse workspace experience. To learn more, check out Enabling Synapse workspace features on an existing dedicated SQL pool (formerly SQL DW) 

Differences between Synapse workspace and connected workspace:

Note: This proposed workaround is only tested on Synapse dedicated SQL Pools, it has not been tested on the Synapse workspaces or serverless SQL pools. This workaround has been tested and works with private endpoints.

Create a dedicated SQL pool with DNS Alias

1. Create a Dedicated SQL pool (formerly SQL DW)

• Quickstart: Create and query a dedicated SQL pool (formerly SQL DW) (Azure portal) - Azure Synapse Analytics

2. Restore the SQL pool inside the Synapse workspace into SQLDW with connected workspace.

• Restore an existing dedicated SQL pool - Azure Synapse Analytics

Note: This step needs to be followed only in case of an existing SQL pool within workspace. For Greenfield use case ignore this step.

 3. Update/Migrate the SQLDW to a connected workspace.  

• Enabling Synapse workspace features on a dedicated SQL pool (formerly SQL DW) - Azure Synapse Analytics

4. Create DNS Alias

• DNS alias - Azure SQL Database

Full workaround steps – including a DNS switch

1. Create a dedicated pool outside of workspace (Former SQL DW).

Note: Enable AAD authentication on SQL DW.

2. Restore the SQL pool inside the Synapse workspace into SQLDW with connected workspace.
   • Restore an existing dedicated SQL pool - Azure Synapse Analytics | Microsoft Learn 

Note: This step needs to be followed only in case of already existing SQL pool within a workspace. For Greenfield use case ignore this step.

3. Take Geo back up for the dedicated SQL pool.
4. Migrate SQL DW to synapse workspace.
   • Enabling Synapse workspace features on a dedicated SQL pool (formerly SQL DW) - Azure Synapse Analytics

Note: As you can see, SQL DW is now assigned a workspace - cselogserver

5. Assign a DNS alias via PowerShell command:

a.) Check for existing alias

       b.) Create DNS Alias on the server

6. Now, create a target SQL DW and enable workspace in a similar way. (Repeat Step 1 to 5 for Target SQL DW) 

7. Restore the Geo backup for the source pool in the target workspace (created in some other region for Disaster Recovery) via PowerShell script:

  8. Move the DNS alias from server 1 to server 2:

  9. Get the alias on server 2:

10. Now, we can login to the restored pool using DNS alias – “respo” as shown:

Note: This works with both SQL Server Auth and AAD.

Our team publishes blog(s) each week and you can find all these blogs here: https://aka.ms/synapsecseblog

For deeper level understanding of Synapse implementation best practices, please refer our success by design site: https://Aka.ms/Synapse-Success-By-Design