| title | description | services | ms.service | ms.subservice | ms.custom | ms.devlang | ms.topic | author | ms.author | ms.reviewer | ms.date |
|---|---|---|---|---|---|---|---|---|---|---|---|
Create a single database |
Create a single database in Azure SQL Database using the Azure portal, PowerShell, or the Azure CLI. |
sql-database |
sql-database |
single-database |
contperf-fy21q1, devx-track-azurecli |
quickstart |
stevestein |
sstein |
09/03/2020 |
In this quickstart, you create a single database in Azure SQL Database using either the Azure portal, a PowerShell script, or an Azure CLI script. You then query the database using Query editor in the Azure portal.
- An active Azure subscription. If you don't have one, create a free account.
This quickstart creates a single database in the serverless compute tier.
To create a single database in the Azure portal this quickstart starts at the Azure SQL page.
-
Browse to the Select SQL Deployment option page.
-
Under SQL databases, leave Resource type set to Single database, and select Create.
-
On the Basics tab of the Create SQL Database form, under Project details, select the desired Azure Subscription.
-
For Resource group, select Create new, enter myResourceGroup, and select OK.
-
For Database name enter mySampleDatabase.
-
For Server, select Create new, and fill out the New server form with the following values:
- Server name: Enter mysqlserver, and add some characters for uniqueness. We can't provide an exact server name to use because server names must be globally unique for all servers in Azure, not just unique within a subscription. So enter something like mysqlserver12345, and the portal lets you know if it is available or not.
- Server admin login: Enter azureuser.
- Password: Enter a password that meets requirements, and enter it again in the Confirm password field.
- Location: Select a location from the dropdown list.
Select OK.
-
Leave Want to use SQL elastic pool set to No.
-
Under Compute + storage, select Configure database.
-
This quickstart uses a serverless database, so select Serverless, and then select Apply.
-
Select Next: Networking at the bottom of the page.
-
On the Networking tab, for Connectivity method, select Public endpoint.
-
For Firewall rules, set Add current client IP address to Yes. Leave Allow Azure services and resources to access this server set to No.
-
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. This creates an AdventureWorksLT sample database so there's some tables and data to query and experiment with, as opposed to an empty blank database.
-
Select Review + create at the bottom of the page:
-
On the Review + create page, after reviewing, select Create.
The Azure Cloud Shell is a free interactive shell that you can use to run the steps in this article. It has common Azure tools preinstalled and configured to use with your account.
To open the Cloud Shell, just select Try it from the upper right corner of a code block. You can also launch Cloud Shell in a separate browser tab by going to https://shell.azure.com. Select Copy to copy the blocks of code, paste it into the Cloud Shell, and press Enter to run it.
The following values are used in subsequent commands to create the database and required resources. Server names need to be globally unique across all of Azure so the $RANDOM function is used to create the server name. Replace the 0.0.0.0 values in the ip address range to match your specific environment.
# Set the resource group name and location for your server
resourceGroupName=myResourceGroup
location=eastus
# 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 with the az group create command. An Azure resource group is a logical container into which Azure resources are deployed and managed. The following example creates a resource group named myResourceGroup in the eastus location:
az group create --name $resourceGroupName --location $location
Create a server with the az sql server create command.
az sql server create \
--name $serverName \
--resource-group $resourceGroupName \
--location $location \
--admin-user $adminlogin \
--admin-password $password
Create a firewall rule with the az sql server firewall-rule create command.
az sql server firewall-rule create \
--resource-group $resourceGroupName \
--server $serverName \
-n AllowYourIp \
--start-ip-address $startip \
--end-ip-address $endip
Create a database with the az sql db create command. The following code creates
az sql db create \
--resource-group $resourceGroupName \
--server $serverName \
--name mySampleDatabase \
--sample-name AdventureWorksLT \
--edition GeneralPurpose \
--compute-model Serverless \
--family Gen5 \
--capacity 2
You can create a resource group, server, and single database using Windows PowerShell.
The Azure Cloud Shell is a free interactive shell that you can use to run the steps in this article. It has common Azure tools preinstalled and configured to use with your account.
To open the Cloud Shell, just select Try it from the upper right corner of a code block. You can also launch Cloud Shell in a separate browser tab by going to https://shell.azure.com. Select Copy to copy the blocks of code, paste it into the Cloud Shell, and press Enter to run it.
The following values are used in subsequent commands to create the database and required resources. Server names need to be globally unique across all of Azure so the Get-Random cmdlet is used to create the server name. Replace the 0.0.0.0 values in the ip address range to match your specific environment.
# Set variables for your server and database
$resourceGroupName = "myResourceGroup"
$location = "eastus"
$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
Create an Azure resource group with New-AzResourceGroup. A resource group is a logical container into which Azure resources are deployed and managed.
Write-host "Creating resource group..."
$resourceGroup = New-AzResourceGroup -Name $resourceGroupName -Location $location -Tag @{Owner="SQLDB-Samples"}
$resourceGroup
Create a server with the New-AzSqlServer cmdlet.
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 with the New-AzSqlServerFirewallRule cmdlet.
Write-host "Configuring server firewall rule..."
$serverFirewallRule = New-AzSqlServerFirewallRule -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-FirewallRuleName "AllowedIPs" -StartIpAddress $startIp -EndIpAddress $endIp
$serverFirewallRule
Create a single database with the New-AzSqlDatabase cmdlet.
Write-host "Creating a gen5 2 vCore serverless database..."
$database = New-AzSqlDatabase -ResourceGroupName $resourceGroupName `
-ServerName $serverName `
-DatabaseName $databaseName `
-Edition GeneralPurpose `
-ComputeModel Serverless `
-ComputeGeneration Gen5 `
-VCore 2 `
-MinimumCapacity 2 `
-SampleName "AdventureWorksLT"
$database
Once your database is created, you can use the Query editor (preview) in the Azure portal to connect to the database and query data.
-
In the portal, search for and select SQL databases, and then select your database from the list.
-
On the 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.
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.
To delete myResourceGroup and all its resources using the Azure portal:
- In the portal, search for and select Resource groups, and then select myResourceGroup from the list.
- On the resource group page, select Delete resource group.
- Under Type the resource group name, enter myResourceGroup, and then select Delete.
To delete the resource group and all its resources, run the following Azure CLI command, using the name of your resource group:
az group delete --name $resourceGroupName
To delete the resource group and all its resources, run the following PowerShell cmdlet, using the name of your resource group:
Remove-AzResourceGroup -Name $resourceGroupName
Connect and query your database using different tools and languages:
[!div class="nextstepaction"] Connect and query using SQL Server Management Studio
Want to optimize and save on your cloud spending?
[!div class="nextstepaction"] Start analyzing costs with Cost Management