Azure SQL load balance consideration

-

In this article I explained about the Azure SQL Load balance with read only replicas

What is read only replicas?

                The replica of the primary database which allows read only access. Secondary replica can be in either synchronous-commit availability mode, or asynchronous-commit availability mode.

Availability of read only replicas

Availability of read only replicas are based on the tiers. Read Scale-Out is enabled by default on Premium, Business Critical and Hyper scale service tiers. Read Scale-Out cannot be enabled in Basic, Standard, or General Purpose service tiers. Read Scale-Out is automatically disabled on Hyper scale databases configured with 0 replicas.
You can disable and re-enable Read Scale-Out on single databases and elastic pool databases in Premium or Business Critical service tiers.

Consuming read only replicas

We have option to configure the application indent as below and point the connections to the read only / read write replica.
Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent=ReadOnly;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;
Server=tcp:<server>.database.windows.net;Database=<mydatabase>;ApplicationIntent= ReadWrite;User ID=<myLogin>;Password=<myPassword>;Trusted_Connection=False; Encrypt=True;

Pros:

  • The read-only workload will be isolated from the main read-write workload and will not affect its performance.

Cons:

  • There may be some small latency between different replicas this latency is caused by an asynchronous transaction log redo operation.

Note: 
      TempDB database is not replicated to the read-only replicas. Each replica has its own version of TempDB database that is created when the replica is created. It ensures that TempDB is updateable and can be modified during your query execution. If your read-only workload depends on using TempDB objects, you should create these objects as part of your query script.

For more details:

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/active-secondaries-readable-secondary-replicas-always-on-availability-groups?view=sql-server-ver15
https://docs.microsoft.com/en-us/azure/sql-database/sql-database-read-scale-out

Comments

Post a Comment

Popular posts from this blog

Access tokens for several web api from one Angular application

-
Image
Scenario                      I have an angular app authenticated with azure AD, the same need to communicate with n no of azure ad authenticated Web Api. I don't want to maintain different client id, secret and tokens.  Solution         We are going to achieve this with scopes in app registration. Used technologies .net core 3.1 API, Angular 9 UI, MSAL for authentication. Step 1: Register 2 apps with single tenant - default directory only            1. Web Api             2. Angular UI Step 2: In web Api add scope from the left menu expose an API              1. set application ID URI            2. Add a scope - use consent admin and user. Step 3: In angular app add a platform from left menu Authentication          ...
Read more

Azure Front Door - Path based routing rule

-
Image
We see how can we configure the path based routing  using azure front door service within 2 different versions on API. These 2 API are hosted in 2 different azure web app. I have created 2 web api and hosted the default weather forecast api with core 3.1 with one more entity called version to identify the api version. Version one api output Version two api output Along with it I have added the version in the route of version 2 api as below [Route("V2/[controller]")] I have furnished the steps below to achieve the simple Path based routing rule Step 1: Create 2 web app versiononeapi, versiontwoapi. Step 2: Create a web api project with core 3.1 Step 3: change the response for the get method in WeatherForecastController as below, make version as V1 and right click on project à publish à Azure à Azure app service windowsà select the subscription, resource group and web app versiononeapi Step 4: change the response for the get method in WeatherForecast...
Read more

A simple introduction to SQL+ Dot Net

-
Image
Explained  the SQL+.Net with a sample project and its steps.               SQL+.Net is the ORM for C# and SQL  designed to complement the skills of SQL developers. By simply adding comments to your SQL you can generate an object oriented class library with good performance. Here is the performance comparison by sqlservercentral.com          Dapper Insert 1000 Records:3364          EF Insert 1000 Records:12794          SQL Plus Insert 1000 Records:2316 Here I have created a console application with sample SQL+ .net ORM utilization. Prerequisites: Visual studio 2017/2019 SQL+.Net Extension – Download the extension in the link -  https://marketplace.visualstudio.com/items?itemName=AHHSoftware.SQLPlusCodeGenerationUtilityDotNetFrameworkVersion1 1. Create a database project Create a table Stored procedures for insert and select ...
Read more