Sql-server – How to create multiple azure sql database in an elastic pool using powershell script

azure-sql-databasepowershellrestoreschemasql server

I want to create a sql server and deploy the elastic pool on that server. Then create multiple azure sql database in that elastic pool using powershell script.

Best Answer

I tried to work on this question...I hope it helps who is looking for it.The solution script is as follows:

Set subscription

Set-AzContext -SubscriptionId $subscriptionId

Create a new resource group

$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

Create a new server with a system wide unique server name

$server = New-AzSqlServer -ResourceGroupName $resourceGroupName -ServerName $serverName -Location $location -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminSqlLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))

Create a server firewall rule that allows access from the specified IP range

$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName -ServerName $serverName -FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp

Create an elastic database pool:

$firstPool = New-AzSqlElasticPool -ResourceGroupName $resourceGroupName -ServerName $servername -ElasticPoolName $firstPoolName -Edition "Standard" -Dtu 50 -DatabaseDtuMin 10 -DatabaseDtuMax 20

Declare an array to store db names:

$databasename = @("DB1", "DB2")

Loop to create automatic multiple database as per requirement:

   For ($i=0; $i -lt $databasename.Length; $i++) 
        {
        $resourcegroupname = "elas-rg" 
        $location = "centralus"        
        $servername = "sirurname"

            $azuresqldb= Get-AzSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename[$i] -ErrorAction SilentlyContinue
           
     if ( -not $azuresqldb )
     {
                "Creating Azure SQL Database " + $databasename[$i] + " in Server Name sirurname"
                
                New-AzSqlDatabase -ResourceGroupName $resourcegroupname -ServerName $servername -DatabaseName $databasename[$i] -ElasticPoolName "Mysecondpool"
            }
            else
            {
                "Azure SQL Database already exists " + $databasename[$i] 
            }
        }
Related Question