info@techdevops.com | 437-991-3573 | Data Engineering Services
TechDevOps.com
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)
2016-07-03








Powershell Code:


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

Get-Module
Get-Command *Sql* -Module AzureRM.sql


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

Login-AzureRmAccount
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:$srvname.database.windows.net;Database=$dbname;User ID=$usr@$srvname;Password=$pwd;Trusted_Connection=False;Encrypt=True;"
$con = new-object system.data.SqlClient.SqlConnection($constr)
$dbDDL = Get-Content C:\Downloads\Powershell\sql1.sql
$cmd = New-Object System.Data.SqlClient.SqlCommand($dbDDL,$con)
$con.Open()
$cmd.ExecuteNonQuery()
$con.Close()

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 "xxx.47.197.xxx" -EndIpAddress "xxx.47.197.xxx"


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:





Resources:

Microsoft Azure

Get-AzureRmSqlServer

Get-AzureRmSqlDatabase