Quickstart: Create a single database - Azure SQL Database

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.

Note

Try Azure SQL Database free of charge and get 100,000 vCore seconds of serverless compute and 32 GB of storage every month.

Note

Microsoft Entra ID is the new name for Azure Active Directory (Azure AD). We are updating documentation at this time.

Prerequisites

• An active Azure subscription. If you don't have one, create a free account.
• The latest version of either Azure PowerShell or Azure CLI.

Create a single database

This quickstart creates a single database in the serverless compute tier.

Portal

To create a single database in the Azure portal, this quickstart starts at the Azure SQL page.

1. Browse to the Select SQL Deployment option page.

2. Under SQL databases, leave Resource type set to Single database, and select Create.

   

3. On the Basics tab of the Create SQL Database form, under Project details, select the desired Azure Subscription.

4. For Resource group, select Create new, enter myResourceGroup, and select OK.

5. For Database name, enter mySampleDatabase.

6. 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's available or not.

   • Location: Select a location from the dropdown list.

   • Authentication method: Select Use SQL authentication.

   • Server admin login: Enter azureuser.

   • Password: Enter a password that meets requirements, and enter it again in the Confirm password field.

   Select OK.

7. Leave Want to use SQL elastic pool set to No.

8. For Workload environment, specify Development for this exercise.

   The Azure portal provides a Workload environment option that helps to pre-set some configuration settings. These settings can be overridden. This option applies to the Create SQL Database portal page only. Otherwise, the Workload environment option has no impact on licensing or other database configuration settings.

   • Choosing the development workload environment sets a few options, including:
     • Backup storage redundancy option is locally redundant storage. Locally redundant storage incurs less cost and is appropriate for pre-production environments that do not require the redundance of zone- or geo-replicated storage.
     • Compute + storage is General Purpose, Serverless with a single vCore. By default, there is a one-hour auto-pause delay.
   • Choosing the Production workload environment sets:
     • Backup storage redundancy is geo-redundant storage, the default.
     • Compute + storage is General Purpose, Provisioned with 2 vCores and 32 GB of storage. This can be further modified in the next step.

9. Under Compute + storage, select Configure database.

10. This quickstart uses a serverless database, so leave Service tier set to General Purpose (Most budget-friendly, serverless compute) and set Compute tier to Serverless. Select Apply.

11. Under Backup storage redundancy, choose a redundancy option for the storage account where your backups will be saved. To learn more, see backup storage redundancy.

12. Select Next: Networking at the bottom of the page.

    

13. On the Networking tab, for Connectivity method, select Public endpoint.

14. For Firewall rules, set Add current client IP address to Yes. Leave Allow Azure services and resources to access this server set to No.

    

15. Under Connection policy, choose the Default connection policy, and leave the Minimum TLS version at the default of TLS 1.2.

16. Select Next: Security at the bottom of the page.

    

17. On the Security page, you can choose to start a free trial of Microsoft Defender for SQL, as well as configure Ledger, Managed identities and Transparent data encryption (TDE) if you desire. Select Next: Additional settings at the bottom of the page.

18. 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. You can also configure database collation and a maintenance window.

19. Select Review + create at the bottom of the page:

    

20. On the Review + create page, after reviewing, select Create.

Azure CLI

The Azure CLI code blocks in this section create 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.

If you don't have an Azure subscription, create an Azure free account before you begin.

Prepare your environment for the Azure CLI

• Use the Bash environment in Azure Cloud Shell. For more information, see Quickstart for Bash in Azure Cloud Shell.

  

• If you prefer to run CLI reference commands locally, install the Azure CLI. If you're running on Windows or macOS, consider running Azure CLI in a Docker container. For more information, see How to run the Azure CLI in a Docker container.

  • If you're using a local installation, sign in to the Azure CLI by using the az login command. To finish the authentication process, follow the steps displayed in your terminal. For other sign-in options, see Sign in with the Azure CLI.

  • When you're prompted, install the Azure CLI extension on first use. For more information about extensions, see Use extensions with the Azure CLI.

  • Run az version to find the version and dependent libraries that are installed. To upgrade to the latest version, run az upgrade.

Launch Azure Cloud Shell

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, 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.

When Cloud Shell opens, verify that Bash is selected for your environment. Subsequent sessions will use Azure CLI in a Bash environment. Select Copy to copy the blocks of code, paste it into the Cloud Shell, and press Enter to run it.

Sign in to Azure

Cloud Shell is automatically authenticated under the initial account signed-in with. Use the following script to sign in using a different subscription, replacing <Subscription ID> with your Azure Subscription ID. If you don't have an Azure subscription, create an Azure free account before you begin.

subscription="<subscriptionId>" # add subscription here

az account set -s $subscription # ...or use 'az login'

For more information, see set active subscription or log in interactively

Set parameter values

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.

Change the location as appropriate for your environment. Replace 0.0.0.0 with the IP address range that matches your specific environment. Use the public IP address of the computer you're using to restrict access to the server to only your IP address.

# Variable block
let "randomIdentifier=$RANDOM*$RANDOM"
location="East US"
resourceGroup="msdocs-azuresql-rg-$randomIdentifier"
tag="create-and-configure-database"
server="msdocs-azuresql-server-$randomIdentifier"
database="msdocsazuresqldb$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
# Specify appropriate IP address values for your environment
# to limit access to the SQL Database server
startIp=0.0.0.0
endIp=0.0.0.0

echo "Using resource group $resourceGroup with login: $login, password: $password..."

Create a resource group

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:

echo "Creating $resourceGroup in $location..."
az group create --name $resourceGroup --location "$location" --tags $tag

Create a server

Create a server with the az sql server create command.

echo "Creating $server in $location..."
az sql server create --name $server --resource-group $resourceGroup --location "$location" --admin-user $login --admin-password $password

Configure a server-based firewall rule

Create a firewall rule with the az sql server firewall-rule create command.

echo "Configuring firewall..."
az sql server firewall-rule create --resource-group $resourceGroup --server $server -n AllowYourIp --start-ip-address $startIp --end-ip-address $endIp

Create a single database

Create a database with the az sql db create command in the serverless compute tier.

echo "Creating $database in serverless tier"
az sql db create \
    --resource-group $resourceGroup \
    --server $server \
    --name $database \
    --sample-name AdventureWorksLT \
    --edition GeneralPurpose \
    --compute-model Serverless \
    --family Gen5 \
    --capacity 2

Azure CLI (sql up)

The Azure CLI code blocks in this section use the az sql up command to simplify the database creation process. With it, you can create a database and all of its associated resources with a single command. This includes the resource group, server name, server location, database name, and login information. The database is created with a default pricing tier of General Purpose, Provisioned, standard-series (Gen5), 2 vCores.

If you don't have an Azure subscription, create an Azure free account before you begin.

Prepare your environment for the Azure CLI

• Use the Bash environment in Azure Cloud Shell. For more information, see Quickstart for Bash in Azure Cloud Shell.

  

• If you prefer to run CLI reference commands locally, install the Azure CLI. If you're running on Windows or macOS, consider running Azure CLI in a Docker container. For more information, see How to run the Azure CLI in a Docker container.

  • If you're using a local installation, sign in to the Azure CLI by using the az login command. To finish the authentication process, follow the steps displayed in your terminal. For other sign-in options, see Sign in with the Azure CLI.

  • When you're prompted, install the Azure CLI extension on first use. For more information about extensions, see Use extensions with the Azure CLI.

  • Run az version to find the version and dependent libraries that are installed. To upgrade to the latest version, run az upgrade.

Launch Azure Cloud Shell

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, 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.

When Cloud Shell opens, verify that Bash is selected for your environment. Subsequent sessions will use Azure CLI in a Bash environment. Select Copy to copy the blocks of code, paste it into the Cloud Shell, and press Enter to run it.

Sign in to Azure

Cloud Shell is automatically authenticated under the initial account signed-in with. Use the following script to sign in using a different subscription, replacing <Subscription ID> with your Azure Subscription ID. If you don't have an Azure subscription, create an Azure free account before you begin.

subscription="<subscriptionId>" # add subscription here

az account set -s $subscription # ...or use 'az login'

For more information, see set active subscription or log in interactively

Set parameter values

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.

Change the location as appropriate for your environment. Replace 0.0.0.0 with the IP address range that matches your specific environment.

# Variable block
let "randomIdentifier=$RANDOM*$RANDOM"
location="East US"
resourceGroup="msdocs-azuresql-rg-$randomIdentifier"
tag="create-and-configure-database"
server="msdocs-azuresql-server-$randomIdentifier"
database="msdocsazuresqldb$randomIdentifier"
login="azureuser"
password="Pa$$w0rD-$randomIdentifier"
# Specify appropriate IP address values for your environment
# to limit access to the SQL Database server
startIp=0.0.0.0
endIp=0.0.0.0

echo "Using resource group $resourceGroup with login: $login, password: $password..."

Note

az sql up is currently in preview and doesn't currently support the serverless compute tier. Also, the use of non-alphabetic and non-numeric characters in the database name aren't currently supported.

Create a database and resources

Use the az sql up command to create and configure a logical server for Azure SQL Database for immediate use. Make sure to record the generated resource group and server names, so you can manage these resources later.

Note

When running the az sql up command for the first time, Azure CLI prompts you to install the db-up extension. This extension is currently in preview. Accept the installation to continue. For more information about extensions, see Use extensions with Azure CLI.

1. Run the az sql up command. If any required parameters aren't used, like --server-name, that resource is created with a random name and login information assigned to it.

   az sql up \
       --resource-group $resourceGroup \
       --location $location \
       --server-name $server \
       --database-name $database \\
       --admin-user $login \
       --admin-password $password
   

2. A server firewall rule is automatically created. If the server declines your IP address, create a new firewall rule using the az sql server firewall-rule create command and specifying appropriate start and end IP addresses.

   startIp=0.0.0.0
   endIp=0.0.0.0
   az sql server firewall-rule create \
       --resource-group $resourceGroup \
       --server $server \
       -n AllowYourIp \
       --start-ip-address $startIp \
       --end-ip-address $endIp
   

3. All required resources are created, and the database is ready for queries.

PowerShell

You can create a resource group, server, and single database using Azure PowerShell.

Launch Azure Cloud Shell

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, 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.

When Cloud Shell opens, verify that PowerShell is selected for your environment. Subsequent sessions use Azure CLI in a PowerShell environment. Select Copy to copy the blocks of code, paste it into the Cloud Shell, and press Enter to run it.

Set parameter values

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 resource group

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

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 firewall rule

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 PowerShell

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

Query the 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.

1. In the portal, search for and select SQL databases, and then select your database from the list.

2. On the page for your database, select Query editor (preview) in the left menu.

3. Enter your SQL authentication server admin login information or use Microsoft Entra authentication.

   

4. 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;
   

5. Select Run, and then review the query results in the Results pane.

   

6. 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.

Portal

To delete myResourceGroup and all its resources using the Azure portal:

1. In the portal, search for and select Resource groups, and then select myResourceGroup from the list.
2. On the resource group page, select Delete resource group.
3. Under Type the resource group name, enter myResourceGroup, and then select Delete.

Azure CLI

Use the following command to remove the resource group and all resources associated with it using the az group delete command - unless you have an ongoing need for these resources. Some of these resources may take a while to create, as well as to delete.

az group delete --name $resourceGroup

Azure CLI (sql up)

Use the following command to remove the resource group and all resources associated with it using the az group delete command - unless you have an ongoing need for these resources. Some of these resources may take a while to create, as well as to delete.

az group delete --name $resourceGroup

PowerShell

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

Related content

Connect and query your database using different tools and languages:

• Connect and query using SQL Server Management Studio
• Connect and query using Azure Data Studio

Next step

Want to optimize and save on your cloud spending?

Start analyzing costs with Cost Management