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.
1 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]
}
}