none
SQL Elastic Pool RRS feed

  • Question

  • Need a solution to add a database to a sql elastic pool programmatically. At the moment we have a multi tenant application which has a seaprate database for each user and we would like to automate the process where a new database gets added to a specific pool on its creation.
    Wednesday, January 3, 2018 6:45 AM

All replies

  • Hello,

    You can create a database using T-SQL.

    CREATE DATABASE db1 
    COLLATE Japanese_XJIS_140 
    (MAXSIZE = 100 MB, EDITION = ‘basic’)  
    WITH CATALOG_COLLATION = DATABASE_DEFAULT


    You can use Transact-SQL to programmatically move that newly created Azure SQL Database into an elastic pool.



    ALTER DATABASE db1   
    MODIFY ( SERVICE_OBJECTIVE = ELASTIC_POOL ( name = pool1 ) ) ;


    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com



    Wednesday, January 3, 2018 10:46 AM
  • You can also use PowerShell to automate this process. Here's a good set of examples on how to do it. This example doesn't include creating it within a pool. However, if you take a look at the New-AzureRMSqlDatabase command, all you have to do is add the ElasticPoolName as a parameter value. Combine this with the rest of the control over Azure through PowerShell and you can automate pretty much any aspect of this you need.
    Wednesday, January 3, 2018 11:50 AM