Adding and removing Azure database to failover group in elastic pool with TSQL

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?

Continue reading...