Adding and removing Azure database to failover group in elastic pool with TSQL or Azure API C#

Question

Creating database and adding to failover group

I have an Azure elastic pool and I have created a failover group with another elastic pool (with the same name) on a different Azure region, during the creation of the failover group I selected the elastic pool and it went through and added all the databases in the elastic pool to the elastic pool on the secondary server.

I have a script setup to automatically create new databases using the following TSQL:

CREATE DATABASE databaseName ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = "Elastic pool name" ) );

However, the above script does not add the database to the failover group and therefore it does not get added to the other SQL server in my other Azure region. I don't want to have to manually add any new databases to the failover group via the Azure portal each time so is there a TSQL script I can use at the point of creation to add the database to the failover group?

Deleting database, removing from failover group and from secondary server

Following on from the above I also have the following TQL which deletes a database:

DROP DATABASE databaseName;

Running the above deletes the database from the primary server and it removes it from the failover group but the database still exists on the secondary server. Is there a way to remove it from the secondary server using TSQL, is it as simple as running the above script again but pointing to the secondary server or is there a better way of doing this?

EDIT

As it seems there is no way to do this with TSQL then is this possible in C# using the Azure API with something like the following?

var dbResponse = client.FailoverGroups.Update("resourceGroupName", 
            "serverName", 
            "failoverGroupName", 
            new Microsoft.Azure.Management.Sql.Models.FailoverGroupUpdate() { 
                    Databases = new List<string>() { "databaseName" } 
                }
            );

Answer 1

I'm afraid no, there isn't a TSQL script which you can use at the point of creation to add the database to the failover group.

Only the two ways Azure provides for us can manage the failover group: Portal and Powershell.

If you want to delete the secondary database, remove it from the failover group before deleting it. Ref here:

We still need do these database adding or deleting options with Portal or PowerShell.

Answer 2

Running some tests it seems you can use the Azure Rest API to do this and I have written the following method to test this, although it looks like you have to always pass through all the database names because this does not append what is already there but actually replaces the list of databases in the failover group.

I have not tested this in production to see if this would cause any issues such as effectively removing and re-adding the databases each time.

Hopefully someone else might be able to provide a better solution, my ideal solution would still be to use TSQL if possible because it means not having to setup a user account to access the REST API.

 public async Task AddDatabasesToFailoverGroupAsync(List<string> databaseNames)
    {
        // Variables
        string domainName = "XXXX"; /* Tenant ID or AAD domain */
        string username = "XXXX";
        string password = "XXXX";
        string resourceGroupName = "XXXXX";
        string serverName = "XXXX";
        string failoverGroupName = "XXXXX";
        string clientId = "XXXXX"; /* Active Directory APP Client ID */
        string subscriptionId = "XXXXXX";
        string databasePrefix = $"/subscriptions/{subscriptionId}/resourceGroups/{resourceGroupName}/providers/Microsoft.Sql/servers/{serverName}/databases/";
        List<string> databases = new List<string>();

        // Add prefix to database names
        foreach (var dbName in databaseNames)
            databases.Add($"{databasePrefix}{dbName}");

        // Login to Azure
        var credentials = await UserTokenProvider.LoginSilentAsync(clientId, domainName, username, password);

        // Create client
        SqlManagementClient client = new SqlManagementClient(credentials)
        {
            SubscriptionId = subscriptionId
        };

        // Set parameters
        var parameters = new FailoverGroupUpdate()
        {
            Databases = databases
        };

        // Update failover group
        client.FailoverGroups.Update(resourceGroupName, serverName, failoverGroupName, parameters);
    }