If you need to make changes to a SQL database, you could open SQL Server Management Studio, click around a little bit and make it happen. But what happens when you need to create an Azure SQL database 10 or 100 times or in some automation script? You need to use PowerShell!
Not a reader? Watch this related video tutorial!In this tutorial, you will learn how to create Azure SQL database and a SQL server firewall rule all in PowerShell!
Let’s get going!
Prerequisites
To follow along with the demos in this tutorial, be sure you have the following:
- A computer to run PowerShell – This tutorial uses Windows 10 using PowerShell v7.1.
- A code editor like Visual Studio (VS) Code.
- The Az PowerShell module – The tutorial will use v5.9.0.
- The dbatools PowerShell module – The tutorial will use v1.0.145.
- An Azure resource group – This tutorial will use a resource group called rg-dbaautomation in the westeurope region.
- You’re authenticated to Azure in PowerShell.
Creating the Azure SQL Server
Before you can create an Azure SQL database, you must create an Azure SQL server to host it on. Assuming you’re already authenticated to Azure:
Open PowerShell on your local computer and create the Azure SQL server that will host the Azure SQL database.
The command below is creating an Azure SQL server called sqlestate in the prerequisite resource group with a SQL admin username of SqlAdministrator and a password of AVeryStrongP@ssword0. The command is saving the output of the New-AzSqlServer cmdlet to use attributes from the server created later.
You can create a SQL admin username and password of your choosing as long as it meets the database requirements.
The SQL Server name must be globally unique.
## Convert the password to a secure string since creating a PSCredential
## object requires it
$pw = ConvertTo-SecureString -String 'AVeryStrongP@ssword0' -AsPlainText -Force
## Create the PSCredential object to pass to the New-AzSqlServer cmdlet
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList 'SqlAdministrator',$pw
## Create the Azure SQL Server
$azSqlServer = New-AzSqlServer `
-ServerName 'sqlestate123' `
-ResourceGroupName 'rg-dbaautomation' `
-Location 'westeurope' `
-SqlAdministratorCredentials $credentialCreating the Azure SQL Server Firewall Rule
By default, the Azure SQL server does not allow access to any outside entity. To connect to the server, you must next create a server firewall rule to access the SQL server from your local machine.
To create the server firewall rule, find the public IP address of your local computer and invoke the New-AzSqlServerFirewallRule cmdlet to create the rule.
The command below is using the website http://ipinfo.io to find your computer’s public IP address. It’s then creating the firewall rule in the rg-dbaautomation resource group called FirewallRule_Access and specifying that IP address for the entire IP range.
## Find the local public IP address by querying a website
$ip = Invoke-RestMethod <https://ipinfo.io/json> | Select-Object -ExpandProperty IP
## Create the server firewall rule using the public IP address
New-AzSqlServerFirewallRule -ResourceGroupName 'rg-dbaautomation' -ServerName $azSqlServer.ServerName -FirewallRuleName 'FirewallRule_Access' -StartIpAddress $ip -EndIpAddress $ip
How to Create Azure SQL Database
Finally, once you’ve created the Azure SQL server and firewall rule, now create the database using the New-AzSqlDatabase cmdlet.
The command below creates an Azure SQL database called Estate with only a Basic edition hosted on the server just created.
To find all available editions run the
Get-AzSqlServerServiceObjectivePowerShell cmdlet.
New-AzSqlDatabase -ResourceGroupName 'rg-dbaautomation' -ServerName $azSqlServer.ServerName -DatabaseName 'Estate' -Edition 'Basic'
Connecting to the Azure SQL Database
You should now have an Azure SQL database running in your Azure subscription ready to work with. Now, confirm you can connect to it using the Connect-DbaInstance PowerShell cmdlet.
Using the previously-created Azure SQL server’s FQDN, the name of the database, and the SQL admin credential, test connection to the database using the code below.
## Test connecting to the instance
Connect-DbaInstance -SqlInstance $azSqlServer.FullyQualifiedDomainName -Database $azSqlDatabase.DatabaseName -SqlCredential $credentialIf you can connect to the database, you will see the following output:
Wrapping Up
If you’d like to save all of these steps shown above into a single PowerShell script, create a new PowerShell script and copy and paste the below snippet.
$rg = New-AzResourceGroup -Name 'rg-dbaautomation' -Location 'westeurope'
## Convert the password to a secure string since creating a PSCredential
## object requires it
$pw = ConvertTo-SecureString -String 'AVeryStrongP@ssword0' -AsPlainText -Force
## Create the PSCredential object to pass to the New-AzSqlServer cmdlet
$credential = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList 'SqlAdministrator',$pw
## Create the Azure SQL Server
$azSqlServer = New-AzSqlServer `
-ServerName 'sqlestate123' `
-ResourceGroupName $rg.ResourceGroupName `
-Location $rg.Location `
-SqlAdministratorCredentials $credential
## Find the local public IP address by querying a website
$ip = Invoke-RestMethod <https://ipinfo.io/json> | Select-Object -ExpandProperty IP
## Create the server firewall rule using the public IP address
New-AzSqlServerFirewallRule -ResourceGroupName $rg.ResourceGroupName -ServerName $azSqlServer.ServerName -FirewallRuleName 'FirewallRule_Access' -StartIpAddress $ip -EndIpAddress $ip
New-AzSqlDatabase -ResourceGroupName $rg.ResourceGroupName -ServerName $azSqlServer.ServerName -DatabaseName 'Estate' -Edition 'Basic'
## Test connecting to the instance
Connect-DbaInstance -SqlInstance $azSqlServer.FullyQualifiedDomainName -Database $azSqlDatabase.DatabaseName -SqlCredential $credentialConclusion
Using PowerShell to create an Azure SQL database makes the process much smoother than using the Azure Portal. PowerShell allows you to automate the process to quickly create Azure SQL servers and databases.
Where do you see your newfound ability to create Azure SQL databases with PowerShell fitting into your daily routine?
