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

Posted by

This post has been republished via RSS; it originally appeared at: New blog articles in Microsoft Community Hub.

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:

Item

Connected Workspace

Synapse Workspace

Firewall

SQL server and Workspace

Workspace

DNS Alias support

yes

No

Rename Database

yes

No

TLS 1.2

Optional

Required

Connection policy

Proxy or Redirect

Default

 

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)

 

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

 

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.  

 

4. Create DNS Alias

 

Resham_Popli_0-1668419392735.png

 

                              

Full workaround steps – including a DNS switch

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

Resham_Popli_1-1668419392743.png

 

 

Resham_Popli_2-1668419392754.png

 

Note: Enable AAD authentication on SQL DW.

 

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

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.

 

  1. Take Geo back up for the dedicated SQL pool.
  2. Migrate SQL DW to synapse workspace.

Resham_Popli_3-1668419392765.png

 

 

Resham_Popli_4-1668419392769.png

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

Resham_Popli_5-1668419392770.png

 

       b.) Create DNS Alias on the server

Resham_Popli_6-1668419392771.png

 

  1. Now, create a target SQL DW and enable workspace in a similar way. (Repeat Step 1 to 5 for Target SQL DW) 
  1. Restore the Geo backup for the source pool in the target workspace (created in some other region for Disaster Recovery) via PowerShell script:

Resham_Popli_7-1668419392775.png

 

  8. Move the DNS alias from server 1 to server 2:Resham_Popli_8-1668419392776.png

 

  9. Get the alias on server 2:Resham_Popli_9-1668419392778.png

 

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

Resham_Popli_10-1668419392779.png

 

 

Resham_Popli_11-1668419392781.png

 

 

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

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.