question

MSTechie-7364 avatar image
0 Votes"
MSTechie-7364 asked CarrinWu-MSFT commented

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-generalazure-sql-database
· 1
5 |1600 characters needed characters left characters exceeded

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

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

0 Votes 0 ·

1 Answer

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered CarrinWu-MSFT edited

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

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

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 Votes 0 ·

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 Votes 0 ·

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 Votes 0 ·