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

Berchmans Marcelline 15

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,380 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 15 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,380 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 117.4K 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,380 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,380 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 15 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,380 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,380 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.
Keshavulu Dasari 3,380 Reputation points Microsoft Vendor

2025-02-17T19:47:06.5466667+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,380 Reputation points Microsoft Vendor

2025-02-18T18:13:52.1866667+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 15 Reputation points

2025-02-20T05:48:28.4633333+00:00

Hi @Keshavulu Dasari
Thanks once again for the details. We are progressing well but not there yet.

Today I managed to partition the data using the region-specific drive - currently we have 1 standard drive in IN region.

Weren't able to use Z drive directly in SQL but used the direct path add as a data file (shown above)

Do you think we may have done something which is preventing to use Z drive in SQL server? Also, the Z drive is shown as network drive not as local drive. I believe when mounted it should show as a local drive - I may be wrong.

Thanks
Berchmans
Erland Sommarskog 117.4K Reputation points MVP

2025-02-20T22:03:58.3266667+00:00

In addition, mounting is per user, so while you see a Z: drive, SQL Server does not.

In any case, I can't see that this will work out, unless the load is very light. The latency will be horrible.

Above Keshavulu said:

Use SQL Server Always On Availability Groups to set up asynchronous replication to other regions. This allows you to replicate data to secondary replicas in different regions without impacting the performance of your primary database.

But since different data, should be in difference places that does not help.

Transactional replication could be an option. Although, this means that data will be in two places, and if the requirement is that Australian data will only be in Australia, that is not going to work out well. In that case, I think you need one database per region.
Keshavulu Dasari 3,380 Reputation points Microsoft Vendor

2025-02-21T16:37:59.1433333+00:00

Hi Berchmans Marcelline

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.

1 answer

Keshavulu Dasari 3,380 Reputation points Microsoft Vendor

2025-02-20T16:57:47.49+00:00
Hi Berchmans Marcelline,

SQL Server does not support using network drives for database files. The Z drive is a network drive, which is why the directory lookup failed. Ensure that the SQL Server service account has the necessary permissions to access the Azure File Share. Verify that the account can read and write to the specified path.

Ensure that the connection to the Azure File Share is persistent and reconnects automatically after reboots. Use the net use command with the /persistent:yes option to mount the drive.

Recommended Approach

Given the limitations of using network drives for SQL Server database files, here are some alternative approaches:

Local Storage for Database Files:

Store all primary database files (data, log, tempdb) on local drives within the Azure AU region to ensure optimal performance.

Asynchronous Replication:

Use SQL Server Always On Availability Groups to set up asynchronous replication to other regions. This allows you to replicate data to secondary replicas in different regions without impacting the performance of your primary database.

Partitioning Data:

Continue partitioning your tables based on region-specific data. This can help manage and query data more efficiently.

Example Configuration

Primary Replica: SQL Server VM in Azure AU region (synchronous-commit mode for local replicas, asynchronous-commit mode for remote replicas).

Secondary Replicas: SQL Server VMs in Azure US and EU regions (asynchronous-commit mode).

Additional Information by Erland Sommarskog
SQL Server Always On Availability Groups

Mounting Azure File Share: If you still need to use Azure File Shares for other purposes, ensure that they are mounted correctly using the net use command or through the Azure portal. Continuously test and monitor the performance of your setup to ensure it meets your requirements.

Your contribution is highly appreciated. Please do not forget to "Accept the answer” and “up-vote” wherever the information provided helps you, this can be beneficial to other community members.

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.
Please sign in to rate this answer.

0 comments No comments
Sign in to comment

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

Share via

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

1 answer

Your answer