Quickstart: Create an Azure SQL Database single database
10 minutes to read
In this quickstart, you use the Azure portal, a PowerShell script, or an Azure CLI script to create a single database in Azure SQL Database. You then query the database using Query editor in the Azure portal.
A single database is the quickest and simplest option for Azure SQL Database. You manage a single database within a server, which is inside an Azure resource group in a specified Azure region. In this quickstart, you create a new resource group and server for the new database.
You can create a single database in the provisioned or serverless compute tier. A provisioned database is pre-allocated a fixed amount of compute resources, including CPU and memory, and uses one of two purchasing models. This quickstart creates a provisioned database using the vCore-based purchasing model, but you can also choose a DTU-based model.
The serverless compute tier is only available in the vCore-based purchasing model, and has an autoscaled range of compute resources, including CPU and memory. To create a single database in the serverless compute tier, see Create a serverless database.
In this step, you create a logical SQL server and a single database that uses AdventureWorksLT sample data. You can create the database by using Azure portal menus and screens, or by using an Azure CLI or PowerShell script in the Azure Cloud Shell.
All the methods include setting up a server-level firewall rule to allow the public IP address of the computer you're using to access the server. For more information about creating server-level firewall rules, see Create a server-level firewall. You can also set database-level firewall rules. See Create a database-level firewall rule.
From the Search bar, search for and select Azure SQL.
On the Azure SQL page, select Add.
On the Select SQL deployment option page, select the SQL databases tile, with Single database under Resource type. You can view more information about the different databases by selecting Show details.
Select Create.
On the Basics tab of the Create SQL database form, under Project details, select the correct Azure Subscription if it isn't already selected.
Under Resource group, select Create new, enter myResourceGroup, and select OK.
Under Database details, for Database name enter mySampleDatabase.
For Server, select Create new, and fill out the New server form as follows:
Server name: Enter mysqlserver, and some characters for uniqueness.
Server admin login: Enter azureuser.
Password: Enter a password that meets requirements, and enter it again in the Confirm password field.
Location: Drop down and choose a location, such as (US) East US.
Select OK.
Record the server admin login and password so you can log in to the server and its databases. If you forget your login or password, you can get the login name or reset the password on the SQL server page after database creation. To open the SQL server page, select the server name on the database Overview page.
Under Compute + storage, if you want to reconfigure the defaults, select Configure database.
On the Configure page, you can optionally:
Change the Compute tier from Provisioned to Serverless.
Review and change the settings for vCores and Data max size.
Select Change configuration to change the hardware generation.
After making any changes, select Apply.
Select Next: Networking at the bottom of the page.
On the Networking tab, under Connectivity method, select Public endpoint.
Under Firewall rules, set Add current client IP address to Yes.
Select Next: Additional settings at the bottom of the page.
On the Additional settings tab, in the Data source section, for Use existing data, select Sample.
Select Review + create at the bottom of the page.
After reviewing settings, select Create.
You can create an Azure resource group, server, and single database using the Azure command-line interface (Azure CLI). If you don't want to use the Azure Cloud Shell, install Azure CLI on your computer.
To run the following code sample in Azure Cloud Shell, select Try it in the code sample title bar. When the Cloud Shell opens, select Copy in the code sample title bar, and paste the code sample into the Cloud Shell window. In the code, replace <Subscription ID> with your Azure Subscription ID, and for $startip and $endip, replace 0.0.0.0 with the public IP address of the computer you're using.
Follow the onscreen prompts to sign in to Azure and run the code.
You can also use the Azure Cloud Shell from the Azure portal, by selecting the Cloud Shell icon on the top bar.
The first time you use Cloud Shell in the portal, select Bash in the Welcome dialog. Subsequent sessions will use Azure CLI in a Bash environment, or you can select Bash from the Cloud Shell control bar.
The following Azure CLI code creates a resource group, server, single database, and server-level IP firewall rule for access to the server. Make sure to record the generated resource group and server names, so you can manage these resources later.
#!/bin/bash
# Sign in to Azure and set execution context (if necessary)
az login
az account set --subscription <Subscription ID>
# Set the resource group name and location for your server
resourceGroupName=myResourceGroup-$RANDOM
location=westus2
# Set an admin login and password for your database
adminlogin=azureuser
password=Azure1234567
# Set a server name that is unique to Azure DNS (<server_name>.database.windows.net)
servername=server-$RANDOM
# Set the ip address range that can access your database
startip=0.0.0.0
endip=0.0.0.0
# Create a resource group
az group create \
--name $resourceGroupName \
--location $location
# Create a server in the resource group
az sql server create \
--name $servername \
--resource-group $resourceGroupName \
--location $location \
--admin-user $adminlogin \
--admin-password $password
# Configure a server-level firewall rule for the server
az sql server firewall-rule create \
--resource-group $resourceGroupName \
--server $servername \
-n AllowYourIp \
--start-ip-address $startip \
--end-ip-address $endip
# Create a gen5 2 vCore database in the server
az sql db create \
--resource-group $resourceGroupName \
--server $servername \
--name mySampleDatabase \
--sample-name AdventureWorksLT \
--edition GeneralPurpose \
--family Gen5 \
--capacity 2 \
For more Azure SQL Database Azure CLI samples, see Azure CLI samples.
You can create a resource group, server, and single database using Windows PowerShell. If you don't want to use the Azure Cloud Shell, install the Azure PowerShell module.
Note
This article has been updated to use the new Azure PowerShell Az
module. You can still use the AzureRM module, which will continue to receive bug fixes until at least December 2020.
To learn more about the new Az module and AzureRM compatibility, see
Introducing the new Azure PowerShell Az module. For
Az module installation instructions, see Install Azure PowerShell.
To run the following code sample in the Azure Cloud Shell, select Try it in the code title bar. When the Cloud Shell opens, select Copy in the code sample title bar, and paste the code sample into the Cloud Shell window. In the code, replace <Subscription ID> with your Azure Subscription ID, and for $startIp and $endIp, replace 0.0.0.0 with the public IP address of the computer you're using.
Follow the onscreen prompts to sign in to Azure and run the code.
You can also use Azure Cloud Shell from the Azure portal, by selecting the Cloud Shell icon on the top bar.
The first time you use Cloud Shell from the portal, select PowerShell on the Welcome dialog. Subsequent sessions will use PowerShell, or you can select it from the Cloud Shell control bar.
The following PowerShell code creates an Azure resource group, server, single database, and firewall rule for access to the server. Make sure to record the generated resource group and server names, so you can manage these resources later.
# Set variables for your server and database
$subscriptionId = '<SubscriptionID>'
$resourceGroupName = "myResourceGroup-$(Get-Random)"
$location = "West US"
$adminLogin = "azureuser"
$password = "Azure1234567"
$serverName = "mysqlserver-$(Get-Random)"
$databaseName = "mySampleDatabase"
# The ip address range that you want to allow to access your server
$startIp = "0.0.0.0"
$endIp = "0.0.0.0"
# Show randomized variables
Write-host "Resource group name is" $resourceGroupName
Write-host "Server name is" $serverName
# Connect to Azure
Connect-AzAccount
# Set subscription ID
Set-AzContext -SubscriptionId $subscriptionId
# Create a resource group
Write-host "Creating resource group..."
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}
$resourceGroup
# Create a server with a system wide unique server name
Write-host "Creating primary server..."
$server = New-AzSqlServer -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-Location $location `
-SqlAdministratorCredentials $(New-Object -TypeName System.Management.Automation.PSCredential `
-ArgumentList $adminLogin, $(ConvertTo-SecureString -String $password -AsPlainText -Force))
$server
# Create a server firewall rule that allows access from the specified IP range
Write-host "Configuring firewall for primary server..."
$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
$serverFirewallRule
# Create General Purpose Gen4 database with 1 vCore
Write-host "Creating a gen5 2 vCore database..."
$database = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $databaseName `
-Edition GeneralPurpose `
-VCore 2 `
-ComputeGeneration Gen5 `
-MinimumCapacity 2 `
-SampleName "AdventureWorksLT"
$database
Once your database is created, you can use the built-in Query editor in the Azure portal to connect to the database and query the data.
In the portal, search for and select SQL databases, and then select your database from the list.
On the SQL Database page for your database, select Query editor (preview) in the left menu.
Enter your server admin login information, and select OK.
Enter the following query in the Query editor pane.
SELECT TOP 20 pc.Name as CategoryName, p.name as ProductName
FROM SalesLT.ProductCategory pc
JOIN SalesLT.Product p
ON pc.productcategoryid = p.productcategoryid;
Select Run, and then review the query results in the Results pane.
Close the Query editor page, and select OK when prompted to discard your unsaved edits.
Clean up resources
Keep the resource group, server, and single database to go on to the next steps, and learn how to connect and query your database with different methods.
When you're finished using these resources, you can delete the resource group you created, which will also delete the server and single database within it.
