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
|
|
|
|
|
|