1

I am conducting some tests, therefore I have two instances each in a specific region, the database has been configured with a geo-replica and added to a failover group.

While trying to trigger a failover through SQL commands, it looks like it doesn't want to work, but when I am going and logging into the azure portal, and selecting the instance then to the failover group, I can trigger a failover and also a forced failover , both of them working just fine, I was wondering how would this work from a SQL perspective?

To be noted the ALTER DATABASE [DB-NAME] FAILOVER; or ALTER AVAILABILITY GROUP [AGNAME] Failover; doesn't work.

Anyone experiencing similar issues?

Dale K
  • 25,246
  • 15
  • 42
  • 71
Călimanu Loredan
  • 53
  • 7
  • 1
    `ALTER DATABASE SET FAILOVER` is for database mirroring, and `ALTER AVAILABILITY GROUP` is for AGs on regular SQL Server. To manage Azure failover, you can't do this in T-SQL, you need to use Powershell or Azure CLI or the Rest API, see https://learn.microsoft.com/en-us/azure/azure-sql/database/auto-failover-group-sql-db?view=azuresql&tabs=azure-powershell#programmatically-managing-failover-groups – Charlieface Aug 05 '22 at 12:50

1 Answers1

0

Azure Failover doesn't support T-SQL approach.

Auto-failover groups can be managed programmatically using Azure PowerShell, Azure CLI, and REST API.

To triggers failover of a failover group to the secondary server, use Switch-AzSqlDatabaseFailoverGroup Powershell command.

The above command swaps the roles of the servers in a Failover Group and switches all secondary databases to the primary role. All new TDS sessions are automatically re-routed to the secondary server after the DNS client cache is refreshed. When the original primary server is back online, all formerly primary databases in it will switch to the secondary role. The Failover Group's secondary server must be used to execute this command.

Refer Switch-AzSqlDatabaseFailoverGroup to know more.

Utkarsh Pal
  • 4,079
  • 1
  • 5
  • 14