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" }
}
);