Replicate AzureSQL to AzureSQL DB using AlwaysOn ?

Question

question

MSTechie-7364 asked Mar 16, '21 CarrinWu-MSFT commented Mar 19, '21

Replicate AzureSQL to AzureSQL DB using AlwaysOn ?

My question is regarding Azure SQL PaaS option

We have several dotnet client apps which are accessing our SQL database of nearly 200 GB size and there are several performance issues due to it. So we have decided to create a read replica of our database , so that few client will access the main database and rest access the read-replica. So for PERFORMANCE reasons we are planning to use ReadReplica option via AlwaysOn feature of SQL Server .(for Azure SQL PaaS, not managed instance)

I beleieve in AlwaysOn , there is an option to create ReadReplica - secondary database.
1) Are there any constraints for migrating on-premises SQL to Azure SQL using Always On
2) What is the replication latency , if we use AlwaysOn , between the Primary and Secondary Databases?

Please help

sql-server-general azure-sql-database

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

CarrinWu-MSFT · Mar 19, 2021 at 08:07 AM

Hi @MSTechie-7364, we have not get a reply from you. Did the answers could help you?

0 ·

Answer 1 · 2021-03-17T08:29:05.21Z

CarrinWu-MSFT answered Mar 17, '21 CarrinWu-MSFT edited Mar 18, '21

Hi @MSTechie-7364,

2) What is the replication latency, if we use AlwaysOn , between the Primary and Secondary Databases?

Under synchronous-commit availability mode, commonly there have three reasons for the latency:
1. The duration of log harden in primary
2. The duration of log harden in remote replica
3. The duration of network traffic
If you would like to know that the total latency, we cannot simply sum them up because log flush on primary and the network transfer are happening in parallel. We can use the time delta between the two hadr_log_block_group_commit xevents to know the time to commit. For the details, please refer to Troubleshooting data movement latency between synchronous-commit AlwaysOn Availability Groups.

Best regards,
Carrin

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MSTechie-7364 · Mar 17, 2021 at 04:33 PM

Hi @Carrin-msft

Thanks for your reply.
1) If i choose Asynchronous commit mode, then can we configure replication between primary & secondary within 1 second. What is the minimum value possible ? is it a configuration ?
2) Also i dont see any direct image in Azure Portal to create Always On. Should we configure it manually ?

Please help

0 ·

CarrinWu-MSFT MSTechie-7364 · Mar 18, 2021 at 02:11 AM

1) If i choose Asynchronous commit mode, then can we configure replication between primary & secondary within 1 second. What is the minimum value possible ? is it a configuration ?
The latency probably will be decided by the above three reasons, so I think you need to test it in your environment and get the result, I can't give you a precise number. SQL Server Always On offers SYNCHRONOUS and ASYNCHRONOUS mode of replication. You can refer to SQL AlwaysOn: Choosing between the right replication model to get more information.

0 ·

CarrinWu-MSFT MSTechie-7364 · Mar 18, 2021 at 02:12 AM

2) Also i dont see any direct image in Azure Portal to create Always On. Should we configure it manually ?
It is possible to configure it on Azure VM, please refer to Use Azure portal to configure an availability group (Preview) for SQL Server on Azure VM

0 ·

question