SQL Azure – Creating SQL Azure Server and Database using PowerShell


In the past two blog for SQL Azure, I have shown you how to use the Azure Management portal to create a SQL Azure Server and Database and then update the firewall rules. This blog post is going to show you how you can do the exact same thing via PowerShell.

All the different PowerShell Cmdlets for managing SQL Azure can be found at the MSDN link https://msdn.microsoft.com/en-us/library/azure/dn546726.aspx.

  • Connect to Azure
Add-AzureAccount
  • This will pop up a Microsoft sign in dialog.
  • Sign in with your Azure username and Password.
  • If you know your subscription name you can do the following, otherwise you might need to call Get-AzureSubscription to list all subscriptions you have to find the name, and ID.
$subscriptionName = 'Windows Azure MSDN – Visual Studio Ultimate'

Select-AzureSubscription –SubscriptionName $subscriptionName

#Below is if you have PowerShell Azure version greater than 0.8.14 (get-module azure).version

#subscriptionID = "a222bcff-ac30-554d-67a5-1d6664f1a555"
#Select-AzureSubscription –SubscriptionID $subscriptionID
  • Next step is to create the Azure SQL Database Server. Remember you don’t get to assign a server name.
$location = 'West Europe'
$adminLogin = 'SQLAdmin'
$adminPassword = 'MyPa55w0rd'
$sqlDB = 'DemoDatabase'
 
$sqlServer = New-AzureSqlDatabaseServer –AdministratorLogin $adminLogin –AdministratorLoginPassword $adminpassword –Location $location

  • The above screen shot appears after the SQL server has successfully been created. To view all your SQL Servers type
Get-AzureSqlDatabaseServer
  • If you now try to connect to SQL Azure, you will encounter a firewall warning.
#Either get credentials for SQL authentication by prompting
$cred = Get-Credential
$sqlctx = New-AzureSqlDatabaseServerContext –ServerName $sqlServer.Name –Credential $cred
 
#Or create a Credential object that includes your login information.
$securePassword = ConvertTo-SecureString 'MyPa55w0rd' –AsPlainText -Force
$manualCredential = new-object System.Management.Automation.PSCredential('SQLAdmin',$securepassword)
$sqlctx = New-AzureSqlDatabaseServerContext –ServerName $sqlServer.ServerName –Credential $manualCredential

  • Therefore we need to create a Firewall rule to allow connections to the database server, the IP address is shown in the message above, I’ve hidden mine. The start and end IP address can be the same, or you can put a range in.
New-AzureSqlDatabaseServerFirewallRule –ServerName $sqlServer.ServerName –RuleName 'myOffice' –StartIPAddress '111.111.111.111' –EndIPAddress '222.222.222.222'

  • The above screen shot appears after the SQL server firewall entry has successfully been created. To view all your firewall rules for this server type
Get-AzureSqlDatabaseServerFirewallRule –ServerName $sqlServer.ServerName
  • Now if we retry the step to connect you don’t get the error message. This allows us now to use the context to create a new database. Ensure you include the Edition otherwise the retired “business” edition will be created for you.
New-AzureSqlDatabase –DatabaseName $sqlDB –ConnectionContext $sqlctx –Edition Standard

  • As you can see from above the MaxSizeGB is 1. Not very big. I’m therefore going to increase this to 10GB. (As in my screenshot above I forgot to add –Edition as Standard, you can also use the Set-AzureSqlDatabase cmdlet to change Database Edition.
Set-AzureSqlDatabase –ConnectionContext $sqlctx –DatabaseName $sqlDB –MaxSizeGB 10

 

By using SQL Management studio and connecting to my server I can see that my database has been created.

If I log into the Azure Management Portal I can see:

  • SQL Database and Server

  • Firewall rules

Lastly if you wish to clean up and remove database and Server, just run the following PowerShell, you will be prompted for a confirmation for both commands.

# Delete the database
Remove-AzureSqlDatabase –ConnectionContext $sqlCtx –DatabaseName $sqlDB
 
# Delete the Server
Remove-AzureSqlDatabaseServer $sqlServer.ServerName

 

Advertisements