How to Deploy an Azure SQL Database Using PowerShell – Part 2
In my first blog I went over the steps on how to create an Azure SQL Database using the Azure Portal. In this post I will go over a PowerShell script that deploys a SQL Database. I am starting with the PowerShell script example Microsoft provides at Use PowerShell to create a single Azure SQL database and configure a firewall rule.
Here, Microsoft provides a pretty good start for creating a database. I will break the script down and discuss some of the modifications I made to create a database.
At the top of the script some parameters are set.
SubscriptionID
Resource Group Name
Location – Data Center Location
Database Admin – SQL Login, this would be the equivalent to the sa account
Server Name – Logical Server Name for the database
Database Name
IP Range – Range of IP addresses in an Hash Table to create the Firewall Rules on the server level
Here is a sample of the code so far
Select-AzureRmSubscription -Subscription "<subscriptionID>"
# Set the resource group name and location for your server
$resourcegroupname = "<ResourceGroupName>"
$location = "North Central US"
# Set an admin login and password for your server
$adminlogin = "AzureSA"
$password = ""
# Set server name - the logical server name has to be unique in the system
$servername = "<ServerName>"
# The sample database name
$databasename = "<DatabaseName>"
Next, create the server for the database. Even though you are just creating a database, Azure still uses a logical server in the background. Here you can give the logical server a name to follow your naming standards.
Microsoft made it pretty easy in Powershell, the command is New-AzureRmSqlServer. All you need to provide is the resource group name, server name, location and the admin credentials.
# Create a server with a system wide unique server name $server = New-AzureRmSqlServer -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -Location $location ` -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $adminlogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
Next step is to set the firewall rules. Here is where I stray from the sample Microsoft script. For my environment, we need to setup a range of internal IP addresses in addition to enabling the Allow Azure Services option.
Here is where I am setting the Hash table with the range of IP addresses I need to configure. IPRange1 is the list of internal addresses that I need to allow to have access to the database. It may also be necessary to Enable to Allow Access To Azure Services. This option basically sets a range of 0.0.0.0 for both the start and end. This is useful for scripting this.
# The ip address range that you want to allow to access your server
$IPRangeList = @{
IPRange1 = "192.168.1.180","192.168.1.185"
IPRange2 = "0.0.0.0","0.0.0.0"
}
Once I set the Hash Table with the list of IP Ranges, you can setup a ForEach loop to set each range. The Powershell command New-AzureRmSqlServerFirewallRule makes it pretty easy to do this. Once again, just give it the resource group name and server name. The next parameter sets the Firewall Rule Name. In this one I just reference the Hashtable to create the name and set the Start and End addresses.
ForEach ($IPRange in $IPRangeList.Keys){
# Create a server firewall rule that allows access from the specified IP range
$serverfirewallrule = New-AzureRmSqlServerFirewallRule -ResourceGroupName $resourcegroupname `
-ServerName $servername `
-FirewallRuleName "AllowedIPs" $IPRange -StartIpAddress $IPRangeList[$IPRange][0] -EndIpAddress $IPRangeList[$IPRange][1]
}
Once this is set, the script then creates the database using the New-AzureRmSqlDatabase. The RequestedServiceObjectiveName is basically the pricing tier.
# Create a blank database with an S0 performance level $database = New-AzureRmSqlDatabase -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -DatabaseName $databasename ` -RequestedServiceObjectiveName "S0"
And with those 3 sections, you can create a basic SQL Database in Azure. There are some other options that can be configured in the script to completely automate this. For example, Auditing and the Advanced Threat Protection can be enabled and configured in PowerShell. The logical SQL Server for the database can also be configured with an Active Directory Admin.
Here is an example of setting the Active Directory Admin. This will add the Active Directory Group called DBATeam.
#Set the Active Directory Admin for the Logical SQL Server Set-AzureRmSqlServerActiveDirectoryAdministrator -ResourceGroupName $resourcegroupname ` -ServerName $servername -DisplayName "DBATeam"
To enable the Advance Threat Protection and Auditing, you will need to create a storage account first. Enabling auditing and the Advanced Threat Protection feature on the server level will automatically enable them for every database created under that server.
# Create a Storage Account $storageaccount = New-AzureRmStorageAccount -ResourceGroupName $resourcegroupname ` -AccountName $storageaccountname ` -Location $location ` -Type "Standard_LRS" # Set an auditing policy on the server level Set-AzureRmSqlServerAuditing -State Enabled ` -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -StorageAccountName $storageaccountname # Set a threat detection policy Set-AzureRmSqlServerThreatDetectionPolicy -ResourceGroupName $resourcegroupname ` -ServerName $servername ` -StorageAccountName $storageaccountname ` -NotificationRecipientsEmails $notificationemailreceipient ` -EmailAdmins $true
In the Set-AzureRmSqlServerThreatDetectionPolicy command there is a new parameter called NotificationRecipientsEmails. This can be a semicolon list of email addresses that can be alerted when an event is kicked off.
#Email Recipient list $notificationemailreceipient = "dba@DBAInTheCloud.com;secteam@DBAInTheCloud.com"
There are many other features that can be set using PowerShell. The example I provided above creates a basic database with some standard features to get someone up and running fairly quickly while adhering to any standards required by your organization. To view some of the other things you can do in PowerShell for the Azure SQL Database, Microsoft has plenty of examples at Azure PowerShell samples for Azure SQL Database .
Hope this was helpful.