| 437-991-3573 | Data Engineering Services
Resources Tools
Experts in Microsoft SQL Server on Windows, Linux, Containers | Clusters, Always On, FCI | Migrations, Cloud, Performance

Create Microsoft Azure SQL Database using Powershell
by BF (Principal Consultant; Architecture; Engineering)

Powershell Code:

1.Powershell Module Check (ensure AzureRM.sql is present):

Get-Command *Sql* -Module AzureRM.sql

2. Authenticate to Microsoft Azure Portal & Select your Subscription(Environment):

Select-AzureRmSubscription -SubscriptionName "COMPANYXYZ"

3. Create Azure SQL Server & Database in a Resource Group:

$rg = "RG_MSSQL_DEV"
$pwd = ConvertTo-SecureString 'xxxxxxx' -AsPlainText -Force;
$cred = New-Object System.Management.Automation.PSCredential -ArgumentList 'Admin#1', $pwd

Create new server(logical) & then confirm it is created.

$srv = New-AzureRmSqlServer -ServerName "azr-nyc-mssql" -location "East US 2" -ServerVersion "12.0" -ResourceGroupName $rg -SqlAdministratorCredentials $cred -Verbose
$srvname = $srv.ServerName
Get-AzureRmSqlServer -ServerName $srvname -ResourceGroupName $rg

Create new Azure SQL Database & then confirm it is created:

$db = New-AzureRmSqlDatabase -DatabaseName "CompanyDB" -ServerName $srvname -ResourceGroupName $rg -Edition Standard
$dbname = $db.DatabaseName
Get-AzureRmSqlDatabase -ServerName $srvname -ResourceGroupName $rg

4. Execute a .sql script to create a table and insert some data (you can also so this from SSMS):

$usr = "xxxxxxx"
$pwd = "xxxxxxx"
$constr = "Server=tcp:$;Database=$dbname;User ID=$usr@$srvname;Password=$pwd;Trusted_Connection=False;Encrypt=True;"
$con = new-object$constr)
$dbDDL = Get-Content C:\Downloads\Powershell\sql1.sql
$cmd = New-Object System.Data.SqlClient.SqlCommand($dbDDL,$con)

Note: You will receive this error regarding Firewall Rule not set. Execute the cmdlet below to set that, then re-run Step 4 code again.

Create a new Firewall Rule:

New-AzureRmSqlServerFirewallRule -FirewallRuleName "FWRule1" -ServerName $srvname -ResourceGroupName $rg -StartIpAddress "" -EndIpAddress ""

To remove Server & Database:

Remove-AzureRmSqlDatabase -DatabaseName $dbname -ServerName $srvname -ResourceGroupName $rg -Force
Remove-AzureRmSqlServer -ServerName $srvname -ResourceGroupName $rg -Force

File: sql1.sql (T-SQL DDL):

Microsoft SQL Server Management Studio connected to Microsoft Azure Cloud:


Microsoft Azure