Region-specific drives for a VM located in Azure AU region

Berchmans Marcelline 10

Hi Team,

We have a requirement from clients to have the data in the respective data drives, for example - client from US wants to have their data in a drive which is physically located in the azure data centre in US region. We are looking at partitioning SQL data based on that region the client is based on.

We are aware how to partition it but we need to have these region-specific separate drives added to the SQL server VM which is located in Azure AU region.

For example

SQL Sever 2019 VM SQL-AU-DEV located in Azure AU region data centre
C Drive : OS Windows 2019
D Drive AU : Azure Storage from AU Region
E Drive US : Azure Storage from US Region
F Drive EU : Azure Storage from EU Region

Wonder this can be achieved in Azure by using a BLOB drive or file share or any method.

Thanks

Keshavulu Dasari 3,280 Reputation points Microsoft Vendor

2025-02-06T23:52:46.3966667+00:00
Hi Berchmans Marcelline,

Greetings! Welcome to Microsoft Q&A Forum. Thanks for posting you query here!

By using either Azure Blob Storage or Azure File Shares you can set this up in Azure. You can configure region-specific drives for your SQL Server VM,

Using Azure Blob Storage

Create Blob Storage Containers Create separate containers in the Azure Blob Storage accounts located in the respective regions (US, EU, AU).

Create SAS Generate SAS tokens for each container to control access. And Create SQL Server Credentials: Use the SAS tokens to create credentials in SQL Server.

Use the BACKUP TO URL and RESTORE FROM URL commands to manage data files in the Blob Storage.

Using Azure File Shares

Create premium file shares in the Azure Storage accounts located in the respective regions. And mount these file shares to your SQL Server VM using the net use command or through the Azure portal. Configure SQL Server to use these mounted drives for storing data files.

For more information
https://learn.microsoft.com/en-us/sql/relational-databases/tutorial-use-azure-blob-storage-service-with-sql-server-2016?view=sql-server-ver16

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/failover-cluster-instance-premium-file-share-manually-configure?view=azuresql

Example Configuration

For your SQL Server VM SQL-AU-DE located in the Azure AU region:

C Drive: OS Windows 2019

D Drive AU: Mount Azure File Share from AU Region

E Drive US: Mount Azure File Share from US Region

F Drive EU: Mount Azure File Share from EU Region

This setup ensures that your clients' data is stored in the respective regional data centers, meeting compliance and data residency requirements.

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you.
Berchmans Marcelline 10 Reputation points

2025-02-07T09:53:13.8833333+00:00

Thanks for the quick response.

We tried the File Share option but having issues with visibility of the drive for all domain users. The admin user executes the PS script see the drive not any other domain user.

Is there a way it can be mounted and all users can see - including SQL service account?

Thanks
Keshavulu Dasari 3,280 Reputation points Microsoft Vendor

2025-02-07T18:41:00.64+00:00
Hi Berchmans Marcelline,

You can mount an Azure File Share so that it is visible to all domain users, including the SQL service account, assign share-level permissions to the Microsoft Entra identities representing the users, groups, or service principals in your AD DS. This ensures that all users have the necessary permissions to access the file share.

Mount the File Share for All Users : Persistent Mounting , use a PowerShell script to mount the Azure File Share persistently for all users. This script should be run with elevated privileges to ensure it applies to all users on the VM. below is an example,

$storageAccountName = "<storage-account-name>" $fileShareName = "<file-share-name>" $driveLetter = "Z" $username = "<domain-username>" $password = "<domain-password>" # Remove any existing persistent credentials cmdkey /delete:$storageAccountName.file.core.windows.net # Add new persistent credentials cmdkey /add:$storageAccountName.file.core.windows.net /user:$username /pass:$password # Mount the file share New-PSDrive -Name $driveLetter -PSProvider FileSystem -Root "\\$storageAccountName.file.core.windows.net\$fileShareName" -Persist -Scope Global

Ensure that the SQL Server service account has the necessary permissions to access the mounted drive. You can do this by adding the service account to the appropriate AD group that has access to the file share

For more detail information

https://learn.microsoft.com/en-us/azure/storage/files/storage-files-identity-mount-file-share

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you.
Erland Sommarskog 116.9K Reputation points MVP

2025-02-07T22:01:50.3866667+00:00

It's not clear to me exactly what setups you are intending for these geographically distributed drives. If you are only going to store backup files on these drives, I guess it should work OK.

However, if you intend to put the database files on these drives, I definitely recommend against this. With the SQL Server instance in Australia and the drives on other parts of the globe, the latency is going to make the performance really bad. A better option would be to have the database files locally in Australia, and then set up some sort of asynchronous replication around the world.
Keshavulu Dasari 3,280 Reputation points Microsoft Vendor

2025-02-10T19:12:22.6433333+00:00

Hi Berchmans Marcelline,

Just checking in to see if the response helped. If you have any questions, let me know in the "comments" and I would be happy to help you.
Keshavulu Dasari 3,280 Reputation points Microsoft Vendor

2025-02-11T20:25:36.81+00:00

Hi Berchmans Marcelline,

Just checking in to see if the response helped. If you have any questions, let me know in the "comments" and I would be happy to help you.
Berchmans Marcelline 10 Reputation points

2025-02-12T06:32:39.1066667+00:00

Hi @Keshavulu Dasari
We are working on the POC, and latency is a concern - do you think it will be?
Currently checking the standard drive and we may go for premium when ready to go live. As far as I understand File Share serves our purpose over BLOB drive. We are in fact looking at partition some tables to retain the region-specific data in the respective region. It is not highly transactional but fair amount of transactions will be expected.
Appreciate if you could share your expertise.
Thanks
Keshavulu Dasari 3,280 Reputation points Microsoft Vendor

2025-02-12T08:24:22.4966667+00:00

Hi Berchmans Marcelline,

Latency can certainly be a concern, especially when dealing with region-specific data storage. I Suggest some insights and recommendations to help you optimize performance,

Azure File Share vs. Blob Storage:

Azure File Share generally better suited for scenarios where you need to mount the storage as a drive. It provides structured data storage and is easier to integrate with SQL Server VMs.

Azure Blob Storage more cost-effective for unstructured data and programmatic access. However, it might not be as straightforward to use for your specific requirement of mounting as a drive.

Performance Tiers:

Standard File Shares backed by HDDs, suitable for less demanding workloads, they might introduce higher latency compared to premium options.

Premium File Shares backed by SSDs, offering lower latency and higher throughput. This is ideal for workloads with higher IOPS and lower latency requirements.

Recommendations for Your POC , given your concern about latency and the expected fair amount of transactions, starting with premium file shares can provide better performance and help you gauge the actual latency impact And Use Azure Monitor to track the latency and IOPS for your file shares. This will help you identify any performance bottlenecks.

Ensure that your SQL Server is configured to handle the data partitioning efficiently. Place data, log, and tempdb files on separate drives to optimize performance, since your SQL Server VM is in the AU region, accessing data from US and EU regions will inherently introduce some network latency. Ensure that your application can tolerate this latency or consider caching frequently accessed data locally.

Example Configuration

For your SQL Server VM SQL-AU-DEV

C Drive: OS Windows 2019 , D Drive AU: Premium Azure File Share from AU Region , E Drive US: Premium Azure File Share from US Region , F Drive EU: Premium Azure File Share from EU Region.

For more Information

https://learn.microsoft.com/en-us/azure/storage/files/understand-performance

https://learn.microsoft.com/en-us/azure/azure-sql/virtual-machines/windows/performance-guidelines-best-practices-storage?view=azuresql

If you have any other questions or are still running into more issues, let me know in the "comments" and I would be happy to help you.
Keshavulu Dasari 3,280 Reputation points Microsoft Vendor

2025-02-13T18:51:37.44+00:00

Hi Berchmans Marcelline,

I hope this information helps! Let us know if you have any further questions. We will be glad to assist you further.

Please do consider to “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

Share via

Region-specific drives for a VM located in Azure AU region

Your answer