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?

CodePudding user response：

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.