Wednesday, January 08, 2020

Data Platform Tips 42 - Azure Cloud Shell and Azure SQL Database

Azure SQL Database allow users to create and manage servers, instances, databases and pools using Powershell. Azure provides an interactive shell environment named "Azure Cloud Shell" where users can use Powershell to manage Azure Services using a browser.

a) Start Azure Cloud Shell by navigating to https://shell.azure.com and select your Azure subscription.













b) Select bash or Powershell option. In this post, we will be looking at Powershell.












c) Create a storage for Azure Cloud Shell if one is not provisioned already.












d) The Cloud Shell is now initialized as shown below.

















e) Now you can start executing Powershell script to create a Azure SQL Database and set firewall settings by copying the below code in the Azure Cloud Shell window.

# Connect-AzAccount
# The SubscriptionId in which to create these objects
$SubscriptionId = '[YOUR SUBSCRIPTION ID HERE]'

# Set the resource group name and location for your server
$resourceGroupName = "RGDemo-$(Get-Random)"
$location = "australiaeast"

# Set an admin login and password for your server
$adminSqlLogin = "[SQL ADMIN USERNAME HERE]"
$password = "[SQL ADMIN PASSWORD HERE]"

# Set server name - the logical server name has to be unique in the system
$serverName = "demosqlserver-$(Get-Random)"

# The sample database name
$databaseName = "demosqldatabase"

# The ip address range that you want to allow to access your server
$startIp = "10.0.0.0"
$endIp = "10.0.0.0"

# Set subscription 
Set-AzContext -SubscriptionId $subscriptionId

# Create a resource group
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location

# Create a 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 a blank database with an S0 performance level
$database = New-AzSqlDatabase  -ResourceGroupName $resourceGroupName `
    -ServerName $serverName `
    -DatabaseName $databaseName `
    -RequestedServiceObjectiveName "S0" `
    -SampleName "AdventureWorksLT"

# Clean up deployment 
# Remove-AzResourceGroup -ResourceGroupName $resourceGroupName

f) Once executed, you can see the Resource Group, SQL Server and Azure SQL Database created on the Azure Portal.






No comments:

Post a Comment