Azure SQL Database failover faillback

Question

Azure SQL Database failover faillback

Ashwan 536

hi guru

We have Azure SQL database and sitting on price tier Premium P1: 125 DTUs. We are in the process of testing failover and fail back scenarios . We have thing to clarify on

how to check HA status
How do we ensure failover has been done or successfully (I know azure logs can see ) I mean to say any server names has been changed?
how to check the failover modes(manual /automatic )

thank you

SSingh-MSFT 16,461 Reputation points Moderator

2023-08-25T06:19:06.23+00:00

Hi Ashwan •,

Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

1 answer

Your answer

SSingh-MSFT 16,461 Reputation points Moderator

2023-08-25T06:19:06.23+00:00

Hi Ashwan •,

Following up to see if the below suggestion was helpful. If this answers your query, do click Accept Answer and Mark Helpful for the same. And, if you have any further query do let us know.

Answer 1

Alberto Morillo 35,506 MVP Volunteer Moderator

Question #1: To monitor the HA status you can use below query. It shows replication lags and last replication time of secondary databases.

SELECT   
     link_guid  
   , partner_server  
   , last_replication  
   , replication_lag_sec   
FROM sys.dm_geo_replication_link_status;

Question #2: You can use the queries/tools I provided on this StackOverflow forum thread.

Question #3. You can initiate a planned or unplanned failover usisng the always familiar Transact-SQL as explained here.

Ashwan 536 Reputation points

2023-08-10T01:40:27.32+00:00

hi Alberto Morillo Thank you so much for the reply . however the solution on Q1 &Q2 you suggested come out as null out put. however I can see the Azue SQL Database is sitting on price tire Premium (DTU). any idea?

meantime referring Q3 document you referred, I tried to excute following command and end up with following error

ALTER DATABASE [sqldb1234] FAILOVER;

The operation cannot be performed since the database 'sqldb1234' is not in a replication relationship.

do you have any idea?

meantime I tried to failover using AzureCLI as follows

Invoke-AzSqlDatabaseFailover -ResourceGroupName " " -ServerName " " -DatabaseName "sqldb-1234" . end up with permission issue and working on to resolve

Q3: i am requesting how to check whether failover mode whether manual or automatic .

thank you so much for your advice
Alberto Morillo 35,506 Reputation points MVP Volunteer Moderator

2023-08-10T04:20:07.0866667+00:00

Could you please try to failover using the portal or PowerShell using this tutorial? This way you will now if you are missing permissions. As mentioned on the article,
Ashwan 536 Reputation points

2023-08-10T06:09:43.7533333+00:00

hi Alberto Morillo, Thank you for reply. you are saying change the price price tier Premium P1: DOES NOT mean we are enabled HA? is that right ? As you mention ,do we have to configure second server ? however command bellow worked and Azure log says failed over has been initialled However we dont know any HA secondary server information can see from SQL you referred to check and out come as empty .Invoke-AzSqlDatabaseFailover -ResourceGroupName " " -ServerName " " -DatabaseName "sqldb-1234" any idea ? many thanks
SSingh-MSFT 16,461 Reputation points Moderator

2023-08-24T05:16:37.7433333+00:00
Hi Ashwan •,

Premium and Business Critical service tiers use the local storage availability model, which integrates compute resources (database engine process) and storage (locally attached SSD) on a single node. High availability is achieved by replicating both compute and storage to additional nodes.

The underlying database files (.mdf/.ldf) are placed on the attached SSD storage to provide very low latency IO to your workload. High availability is implemented using a technology similar to SQL Server Always On availability groups. The cluster includes a single primary replica that is accessible for read-write customer workloads, and up to three secondary replicas (compute and storage) containing copies of data. The primary replica constantly pushes changes to the secondary replicas in order and ensures that the data is persisted on a sufficient number of secondary replicas before committing each transaction. This process guarantees that if the primary replica or a readable secondary replica crash for any reason, there is always a fully synchronized replica to fail over to. The failover is initiated by the Azure Service Fabric. Once a secondary replica becomes the new primary replica, another secondary replica is created to ensure the cluster has a sufficient number of replicas to maintain quorum. Once a failover is complete, Azure SQL connections are automatically redirected to the new primary replica or readable secondary replica.

As an extra benefit, the local storage availability model includes the ability to redirect read-only Azure SQL connections to one of the secondary replicas. This feature is called Read Scale-Out. It provides 100% additional compute capacity at no extra charge to off-load read-only operations, such as analytical workloads, from the primary replica.

Follow https://learn.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-configure-sql-db?view=azuresql&tabs=azure-portal&pivots=azure-sql-single-db to create and test failover.

To illustrate the change sequence, we will assume server A is the primary server, server B is the existing secondary server, and server C is the new primary in the third region. To make the transition, follow these steps:

Perform a planned geo-failover to switch the primary server to B. Server A will become the new secondary server. The failover may result in several minutes of downtime. The actual time will depend on the size of failover group.

Create additional secondaries of each database on server B to server C using active geo-replication. Each database on server B will have two secondaries, one on server A and one on server C. This will guarantee that the primary databases remain protected during the transition.

Delete the failover group. At this point login attempts using failover group endpoints will be failing.

Re-create the failover group with the same name between servers B and C.

Add all primary databases on B to the new failover group. At this point the login attempts will stop failing.

Perform a planned geo-failover of the failover group to switch B and C. Now server C will become the primary and B the secondary. All secondary databases on server A will be automatically linked to the primaries on C. As in step 1, the failover may result in several minutes of downtime.

Delete server A. All databases on A will be deleted automatically.

Note:

Removing a failover group for a single or pooled database does not stop replication, and it does not delete the replicated database. You will need to manually stop geo-replication and delete the database from the secondary server if you want to add a single or pooled database back to a failover group after it's been removed. Failing to do either may result in an error similar to The operation cannot be performed due to multiple errors when attempting to add the database to the failover group.

Auto-failover group name is subject to naming restrictions.

Read here for failover modes https://learn.microsoft.com/en-us/sql/database-engine/availability-groups/windows/failover-and-failover-modes-always-on-availability-groups?view=sql-server-ver16

Thanks

Share via

Azure SQL Database failover faillback

1 answer

Your answer