How to point External Tables in Dedicated SQL Pool to Storage Secondary End Point ?

PS 396

All,

What is the procedure to point external tables in the Dedicated SQL Pool to the container secondary endpoint, in case of a disaster recovery scenario when a pool is restored in the secondary region? Which method can be used for this task? Your guidance on this issue would be much appreciated.

Additional information:

The technologies involved are Dedicated SQL Pool and External Tables.
The desired outcome is to point the external tables to the Container Secondary Endpoint in case of a disaster recovery scenario.

Thank you!

PRADEEPCHEEKATLA-MSFT 89,386 Reputation points Microsoft Employee

2023-07-13T06:53:50.71+00:00

@PS - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
PS 396 Reputation points

2023-07-24T20:09:40.64+00:00

@PRADEEPCHEEKATLA-MSFT - thank you for checking. I will update the post with my observations.
PRADEEPCHEEKATLA-MSFT 89,386 Reputation points Microsoft Employee

2023-07-31T08:33:56.88+00:00

@PS - Just checking in to see if the below answer helped. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

1 answer

PRADEEPCHEEKATLA-MSFT 89,386 Reputation points Microsoft Employee

2023-07-11T09:16:09.9833333+00:00
@PS - Thanks for the question and using MS Q&A platform.

To point external tables in a Dedicated SQL Pool to the container secondary endpoint in case of a disaster recovery scenario, you can use the ALTER EXTERNAL DATA SOURCE statement to modify the endpoint URL.

Here's an example of how to do this:

Connect to the Dedicated SQL Pool using SQL Server Management Studio or Azure Data Studio.

Run the following command to view the current external data source configuration:

SELECT * FROM sys.external_data_sources;

This will display a list of external data sources, including the endpoint URL.

Run the following command to modify the endpoint URL to the container secondary endpoint:

ALTER EXTERNAL DATA SOURCE <data-source-name> WITH (LOCATION = '<container-secondary-endpoint-url>');

Replace <data-source-name> with the name of the external data source, and <container-secondary-endpoint-url> with the URL of the container secondary endpoint.

Verify that the external data source has been updated by running the following command:

SELECT * FROM sys.external_data_sources;

This should display the updated external data source configuration.

Note that you may need to update the endpoint URL for each external table that uses the external data source. You can do this by running the ALTER EXTERNAL TABLE statement with the new data source name.

Also, keep in mind that the container secondary endpoint must be accessible from the Dedicated SQL Pool in the secondary region. If the endpoint is not accessible, you may need to update your network configuration or use a different disaster recovery strategy.

Hope this helps. Do let us know if you any further queries.

If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.
Please sign in to rate this answer.
PRADEEPCHEEKATLA-MSFT 89,386 Reputation points Microsoft Employee

2023-07-14T06:27:56.06+00:00

@PS - Following up to see if the above answer was helpful. If this answers your query, do click Accept Answer and Yes for was this answer helpful. And, if you have any further query do let us know.

PS 396 Reputation points

2023-07-26T17:42:35.4533333+00:00

After changing the redundancy of storage to RA-GRS and upon successful replication. I have updated the external table location to point to secondary storage by adding "-secondary"

When querying the external table, I got the following error.

below is the txt from the error msg.

Msg 105019, Level 16, State 1, Line 20 External file access failed due to internal error: 'Error occurred while accessing HDFS: Java exception raised on call to HdfsBridge_IsDirExist. Java exception message: HdfsBridge::isDirExist - Unexpected error encountered checking whether directory exists or not: StorageException: One of the request inputs is out of range.'

Please let me know if I am missing something.

Thank you!

PRADEEPCHEEKATLA-MSFT 89,386 Reputation points Microsoft Employee

2023-07-27T05:40:27.62+00:00

@PS - The error message you received indicates that there was an issue accessing the external data source. Specifically, the error message mentions that there was an issue accessing HDFS, which is the Hadoop Distributed File System that is used by Azure Synapse Analytics to access external data sources.

Based on the error message, it seems that the issue may be related to the fact that you changed the storage redundancy to RA-GRS and updated the external table location to point to the secondary storage by adding "-secondary". It's possible that the external data source is not able to access the secondary storage for some reason.

Here are a few things you can try to troubleshoot the issue:

Verify that the secondary storage account is accessible and that the container and blob that contain the external data are available. You can use the Azure Storage Explorer or the Azure portal to check this.

Check the firewall settings for the storage account to ensure that the IP address of the Synapse workspace is allowed to access the storage account. You can check this in the Azure portal under the "Firewalls and virtual networks" section of the storage account.

Check the access key for the storage account to ensure that it is correct. You can check this in the Azure portal under the "Access keys" section of the storage account.

Check the external data source definition to ensure that it is correct and that it points to the correct storage account and container. You can use the following query to retrieve the external data source definition:

SELECT * FROM sys.external_data_sources WHERE name = 'MyExternalDataSource'

In this example, MyExternalDataSource is the name of the external data source that is used by the external tables.

I hope this helps! Let me know if you have any further questions.

PS 396 Reputation points

2023-08-17T20:51:50.47+00:00

Hi @PRADEEPCHEEKATLA-MSFT ,

I have verified and the secondary storage exists and am able to access the files using the Storage Explorer and also ADF.

I am able to access the primary storage and I was hoping for the same firewall & network rules will be in place for the secondary storage.

anything else that I would be missing?

Thank you!

PRADEEPCHEEKATLA-MSFT 89,386 Reputation points Microsoft Employee

2023-08-22T08:24:47.1433333+00:00

@PS - I agree that this issue looks strange and I wasn't able to reproduce this issue. If you have a support plan could you please file a support ticket for deeper investigation and do share the SR# with us?

PS 396 Reputation points

2023-09-25T15:53:12.7433333+00:00

@PRADEEPCHEEKATLA-MSFT - can you please share the email to provide the requested details. thank you!

PRADEEPCHEEKATLA-MSFT 89,386 Reputation points Microsoft Employee

2023-09-28T08:21:25.78+00:00

@PS - You can share the details here, in case if you have any confidential data please do let me know?

PS 396 Reputation points

2023-09-28T17:14:57.3033333+00:00

@PRADEEPCHEEKATLA-MSFT

I prefer sharing over email if it's ok?

Thank you!
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

How to point External Tables in Dedicated SQL Pool to Storage Secondary End Point ?

1 answer

Your answer