Automate Elastic pool change for SQL DB

Whenever a new SQL database is imported into the Azure using bacpac , by default there is no elastic pool . If the user forgets to move it to elastic pool , it will cause unwanted costs. How can we avoid this cost and save money for the client ?

Below is the script I developed for this. You can wrap it in a runbook or schedule via PowerShell . I will scan all the databases in the Azure SQL server and move the DBs in the elastic pool .

Declare @Counter INT = (SELECT count(*) FROM sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id WHERE d.Name <> 'master' and dso.elastic_pool_name is NULL)

If @Counter<> 0

BEGIN

Declare @SQL_STATEMENT varchar(200)

Declare @correct_Elastic_pool varchar(50) = ( SELECT top 1 elastic_pool_name FROM sys.elastic_pool_resource_stats)

Declare @DBNAME varchar(50)

DECLARE db_cursor CURSOR FOR SELECT d.name FROM sys.databases d inner join sys.database_service_objectives dso on d.database_id = dso.database_id WHERE d.Name <> 'master' and dso.elastic_pool_name is null

OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DBNAME WHILE @@FETCH_STATUS = 0

BEGIN Print 'Elastic pool is missing for '+ @DBNAME+ '.Running the updated statement for elastic pool change'

SET @SQL_STATEMENT = 'ALTER DATABASE' + ' '+ '['+@DBNAME+']' + ' '+ 'MODIFY (SERVICE_OBJECTIVE = ELASTIC_POOL (NAME = ['+@correct_Elastic_pool+']));' print @SQL_STATEMENT

EXEcute (@SQL_STATEMENT) ;

FETCH NEXT FROM db_cursor INTO @DBNAME

END

Print 'All Databases missing the elastic pool have been updated.'

CLOSE db_cursor

DEALLOCATE db_cursor

END

ELSE

Print 'All Databases are in Elastic pool.No action required'