In this blog post, I’ll go through the steps to create an Azure SQL Database using Azure PowerShell.
Introduction to Azure SQL Database
The SQL database services provided by Microsoft on the cloud are now grouped under the name of Azure SQL.
The Azure SQL family contains services that I will briefly summarize;
- Azure SQL Database – DBaaS (Database-as-a-Service)
- Azure SQL Managed Instance – PaaS (Platform-as-a-Service)
- SQL Server on Azure VMs – IaaS (Infrastructure-as-a-Service)
In this blog post, I will use Azure SQL Database.
Azure SQL Database offers the following deployment options:
- Single database – a fully-managed, isolated database
- Elastic pool – a collection of single databases with a shared set of resources
I will not describe in detail this service but basically, it is a fully managed SQL database similar to a contained database in SQL Server.
All the steps below can be done on the Azure Portal. For this blog post, I’ll only use Azure PowerShell which you can install on your operating system or use online with Azure Cloud Shell.
1) Install and Import Az module
First, we need to install Azure PowerShell which provides a set of commands to manage your Azure resources from your favorite operating system; Windows, macOS, and Linux.
PS C:\> Install-Module Az PS C:\> Get-InstalledModule -Name Az | select Name, Version Name Version ---- ------- Az 4.1.0 PS C:\> Import-Module Az
2) Sign in to Azure
Connect to your Tenant using your Tenant ID.
You can find your Tenant ID in the Azure Portal under “Azure Active Directory”.
PS C:\> Connect-AzAccount -Tenant 'b9c70123-xxx-xxx-xxx-xxxx' Account SubscriptionName TenantId Environment ------- ---------------- -------- ----------- [email protected] Visual Studio Ent b9c70978-xxx-xxx-xxx-xxxx AzureCloud PS C:\>
Then, if you use multiple Azure subscriptions, select the one you want to work with.
PS C:\> Set-AzContext -SubscriptionId '891f5acc-xxx-xxx-xxx-xxxx'
3) Create a Resource Group
Let’s start with creating a Resource Group. A resource group is a container that holds related resources for an Azure solution.
PS C:\> New-AzResourceGroup -Name 'SQLFailover-lab-rg' -Location 'France Central' ResourceGroupName : SQLFailover-lab-rg Location : francecentral ProvisioningState : Succeeded Tags : ResourceId : /subscriptions/891f5acc-xxx-xxx-xxx-xxxx/resourceGroups/SQLFailover-lab-rg
To list all your Resource Groups use the Get-AzResourceGroup command:
Get-AzResourceGroup | select ResourceGroupName
4) Create an SQL Server
Create a logical server with a unique server name to host our SQL databases.
New-AzSqlServer -ResourceGroupName 'SQLFailover-lab-rg' ` -ServerName 'snasqlsrv-lab-01' ` -Location 'France Central' ` -SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList "LabSqlAdmin", $(ConvertTo-SecureString -String "MyPassword" -AsPlainText -Force))
The last parameter defines the credentials I will use to connect as an administrator to my SQL Database server.
Once the server is created you get the FQDN that will be used for connections.
PS C:\> Get-AzSqlServer | select FullyQualifiedDomainName FullyQualifiedDomainName ------------------------ snasqlsrv-lab-01.database.windows.net
5) Create a Server Firewall Rule
To access the server and all the databases from my client computer I need to create a server firewall rule.
Here I use a WebRequest to get my public IP into a variable and then create the server firewall rule.
$myIp = (Invoke-WebRequest ifconfig.me/ip).Content New-AzSqlServerFirewallRule -ResourceGroupName 'SQLFailover-lab-rg' ` -ServerName 'snasqlsrv-lab-01' ` -FirewallRuleName "AllowedIPs" -StartIpAddress $myIp -EndIpAddress $myIp
6) Connect to the SQL Server from SSMS
The SQL Server is now accessible from my computer client on port 1433. I can connect to it using SSMS.
7) Create a database
The following command will create a database named “DB01” with an S0 performance level and using the sample schema “AventureWorksLT”.
New-AzSqlDatabase -ResourceGroupName 'SQLFailover-lab-rg' ` -ServerName 'snasqlsrv-lab-01' ` -DatabaseName 'DB01' ` -RequestedServiceObjectiveName "S0" ` -SampleName "AdventureWorksLT"
This is it. We just a created an Azure SQL Database with a few commands.
Bonus: Creating a Copy of the database
I just want to mention a nice T-SQL command with Azure SQL Database that doesn’t exist on-premise: “CREATE DATABASE AS A COPY”.
This command creates a copy of a database with a new name. This replace the backup/”restore with move” that we do sometimes on SQL Server.
Cleanup
When you’re done with your tests you can delete all resources in the resource group (firewall rules, server, databases) with a single command;
PS C:\> Remove-AzResourceGroup -ResourceGroupName 'SQLFailover-lab-rg'