Automate Elastic pool change for SQL DB

Question

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 schedule a script to check the databases and if it in not in elastic pool , it should move it to elastic pool ?

Answer 1

Below is the script I developed for this :-

    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'

Answer 2

Hi @jatin pathak , welcome to Microsoft Q&A forum.

To move a database to elastic pool, you can use below script:

ALTER DATABASE db1 MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL (name = pool name ));  
-- Move the database named db1 to a pool   

Referenced Article: Monitor and manage an elastic database pool with Transact-SQL

Please let us know if this helps or else we discuss further.

----------

If answer helps, please mark it as 'Accept Answer'