How to change Storage Location for External Tables in Dedicated SQL Pool (Formerly SQL DW)

Question

Hello everyone,

I'm currently in the process of restoring a copy of a dedicated SQL pool (Formerly SQL DW) from server1 to server2 within the same Azure resource group. Following the restoration, I'll need to adjust the storage location in the external tables from Storage1 to Storage2. Both Storage1 and Storage2 have identical folder structures and reside within the same resource group.

Is there a method to efficiently update the location for all external tables in bulk, directing them to the new storage, using PowerShell, and incorporate it into my automation workflow?

Your insights and ideas are greatly appreciated.

Thank you!

Answer 1

Hey there PS

Thats a good question and thanks for using QandA platform

First, connect to your Azure environment using PowerShell and query the metadata of your dedicated SQL pool to identify all external tables currently pointing to Storage1.

For each identified external table, you'll need to update its definition to point to Storage2 instead of Storage1.

use PowerShell script into your automation workflow. This way, the updates can be executed automatically after the restoration process completes.

Hope this helps!

Kindly accept the answer if it helps thanks much.

Answer 2

Hey,

The external tables access the storage account via the external data source.

So you would have to just update the external datasource to the new storage account and credential with the latest stg account value.