Create and manage servers and single databases in Azure SQL Database
You can create and manage servers and single databases in Azure SQL Database using the Azure portal, PowerShell, the Azure CLI, REST API, and Transact-SQL.
Note
Try Azure SQL Database free of charge and get 100,000 vCore seconds of serverless compute and 32 GB of storage every month.
Prerequisites
- An active Azure subscription. If you don't have one, create a free account.
Permissions
To create databases via Transact-SQL: CREATE DATABASE permissions are necessary. To create a database a login must be either the server admin login (created when the Azure SQL Database logical server was provisioned), the Microsoft Entra admin of the server, a member of the dbmanager database role in master. For more information, see CREATE DATABASE.
To create databases via the Azure portal, PowerShell, Azure CLI, or REST API: Azure RBAC permissions are needed, specifically the Contributor, SQL DB Contributor, or SQL Server Contributor Azure RBAC role. For more information, see Azure RBAC built-in roles.
The Azure portal
You can create the resource group for Azure SQL Database ahead of time or while creating the server itself.
Tip
For an Azure portal quickstart, see Quickstart: Create a single database.
Create a server
To create a server using the Azure portal, create a new server resource from Azure Marketplace. Alternatively, you can create the server when you deploy an Azure SQL Database.
Create a blank or sample database
To create a single Azure SQL Database using the Azure portal, choose the Azure SQL Database resource in Azure Marketplace. You can create the resource group and server ahead of time or while creating the single database itself. You can create a blank database or create a sample database based on Adventure Works LT.
Important
For information on selecting the pricing tier for your database, see DTU-based purchasing model and vCore-based purchasing model.
Manage an existing server
To manage an existing server, navigate to the server using several methods - such as from a specific database page, the SQL servers page, or the All resources page.
To manage an existing database, navigate to the SQL databases page and select the database you wish to manage. The following screenshot shows how to begin setting a server-level firewall for a database from the Overview page for a database.
Important
To configure performance properties for a database, see DTU-based purchasing model and vCore-based purchasing model.
PowerShell
Note
This article uses the Azure Az PowerShell module, which is the recommended PowerShell module for interacting with Azure. To get started with the Az PowerShell module, see Install Azure PowerShell. To learn how to migrate to the Az PowerShell module, see Migrate Azure PowerShell from AzureRM to Az.
Important
The PowerShell Azure Resource Manager module is still supported by Azure SQL Database, but all future development is for the Az.Sql module. For these cmdlets, see AzureRM.Sql. The arguments for the commands in the Az module and in the AzureRm modules are substantially identical.
To create and manage servers, single and pooled databases, and server-level firewalls with Azure PowerShell, use the following PowerShell cmdlets. If you need to install or upgrade PowerShell, see Install Azure PowerShell module.
Tip
For PowerShell example scripts, see Use PowerShell to create a single database and configure a server-level firewall rule and Use PowerShell to monitor and scale a single database in Azure SQL Database.
| Cmdlet | Description |
|---|---|
| New-AzSqlDatabase | Creates a database |
| Get-AzSqlDatabase | Gets one or more databases |
| Set-AzSqlDatabase | Sets properties for a database, or moves an existing database into an elastic pool |
| Remove-AzSqlDatabase | Removes a database |
| New-AzResourceGroup | Creates a resource group |
| New-AzSqlServer | Creates a server |
| Get-AzSqlServer | Returns information about servers |
| Set-AzSqlServer | Modifies properties of a server |
| Remove-AzSqlServer | Removes a server |
| New-AzSqlServerFirewallRule | Creates a server-level firewall rule |
| Get-AzSqlServerFirewallRule | Gets firewall rules for a server |
| Set-AzSqlServerFirewallRule | Modifies a firewall rule in a server |
| Remove-AzSqlServerFirewallRule | Deletes a firewall rule from a server. |
| New-AzSqlServerVirtualNetworkRule | Creates a virtual network rule, based on a subnet that is a Virtual Network service endpoint. |
Azure CLI
To create and manage the servers, databases, and firewalls with Azure CLI, use the following Azure CLI commands. Use the Cloud Shell to run Azure CLI in your browser, or install it on macOS, Linux, or Windows. For creating and managing elastic pools, see Elastic pools.
Tip
For an Azure CLI quickstart, see Azure CLI samples for Azure SQL Database. For Azure CLI example scripts, see Create a single database and configure a firewall rule using the Azure CLI and Monitor and scale a single database in Azure SQL Database using the Azure CLI.
| Cmdlet | Description |
|---|---|
| az sql db create | Creates a database |
| az sql db list | Lists all databases and data warehouses in a server, or all databases in an elastic pool |
| az sql db list-editions | Lists available service objectives and storage limits |
| az sql db list-usages | Returns database usages |
| az sql db show | Gets a database or data warehouse |
| az sql db update | Updates a database |
| az sql db delete | Removes a database |
| az group create | Creates a resource group |
| az sql server create | Creates a server |
| az sql server list | Lists servers |
| az sql server list-usages | Returns server usages |
| az sql server show | Gets a server |
| az sql server update | Updates a server |
| az sql server delete | Deletes a server |
| az sql server firewall-rule create | Creates a server firewall rule |
| az sql server firewall-rule list | Lists the firewall rules on a server |
| az sql server firewall-rule show | Shows the detail of a firewall rule |
| az sql server firewall-rule update | Updates a firewall rule |
| az sql server firewall-rule delete | Deletes a firewall rule |
Transact-SQL (T-SQL)
To create and manage the servers, databases, and firewalls with Transact-SQL, use the following T-SQL commands. You can issue these commands using the Azure portal, SQL Server Management Studio, Visual Studio Code, or any other program that can connect to a server in SQL Database and pass Transact-SQL commands. For managing elastic pools, see Elastic pools help you manage and scale multiple databases in Azure SQL Database.
Tip
For a quickstart using SQL Server Management Studio on Microsoft Windows, see Quickstart: Use SSMS to connect to and query Azure SQL Database or Azure SQL Managed Instance. For a quickstart using Visual Studio Code on the macOS, Linux, or Windows, see Quickstart: Use Visual Studio Code to connect and query Azure SQL Database or Azure SQL Managed Instance.
Important
You can't create or delete a server using Transact-SQL.
| Command | Description |
|---|---|
| CREATE DATABASE | Creates a new single database. You must be connected to the master database to create a new database. |
| ALTER DATABASE | Modifies a database or elastic pool. |
| DROP DATABASE | Deletes a database. |
| sys.database_service_objectives | Returns the edition (service tier), service objective (pricing tier), and elastic pool name, if any, for Azure SQL Database or a dedicated SQL pool in Azure Synapse Analytics. If logged on to the master database in a server in SQL Database, returns information on all databases. For Azure Synapse Analytics, you must be connected to the master database. |
| sys.dm_db_resource_stats | Returns CPU, IO, and memory consumption for a database in Azure SQL Database. One row exists for every 15 seconds, even if there's no activity in the database. |
| sys.resource_stats | Returns CPU usage and storage data for a database in Azure SQL Database. The data is collected and aggregated within five-minute intervals. |
| sys.database_connection_stats | Contains statistics for SQL Database connectivity events, providing an overview of database connection successes and failures. |
| sys.event_log | Returns successful Azure SQL Database connections and connection failures. You can use this information to track or troubleshoot your database activity with SQL Database. |
| sp_set_firewall_rule | Creates or updates the server-level firewall settings for your server. This stored procedure is only available in the master database to the server-level principal login. A server-level firewall rule can only be created using Transact-SQL after the first server-level firewall rule has been created by a user with Azure-level permissions |
| sys.firewall_rules | Returns information about the server-level firewall settings associated with your database in Azure SQL Database. |
| sp_delete_firewall_rule | Removes server-level firewall settings from your server. This stored procedure is only available in the master database to the server-level principal login. |
| sp_set_database_firewall_rule | Creates or updates the database-level firewall rules for your database in Azure SQL Database. Database firewall rules can be configured for the master database, and for user databases on SQL Database. Database firewall rules are useful when using contained database users. |
| sys.database_firewall_rules | Returns information about the database-level firewall settings associated with your database in Azure SQL Database. |
| sp_delete_database_firewall_rule | Removes database-level firewall setting from a database. |
REST API
To create and manage the servers, databases, and firewalls, use these REST API requests.
| Command | Description |
|---|---|
| Servers - Create or update | Creates or updates a new server. |
| Servers - Delete | Deletes a SQL server. |
| Servers - Get | Gets a server. |
| Servers - List | Returns a list of servers in a subscription. |
| Servers - List by resource group | Returns a list of servers in a resource group. |
| Servers - Update | Updates an existing server. |
| Databases - Create or update | Creates a new database or updates an existing database. |
| Databases - Delete | Deletes a database. |
| Databases - Get | Gets a database. |
| Databases - List by elastic pool | Returns a list of databases in an elastic pool. |
| Databases - List by server | Returns a list of databases in a server. |
| Databases - Update | Updates an existing database. |
| Firewall rules - Create or update | Creates or updates a firewall rule. |
| Firewall rules - Delete | Deletes a firewall rule. |
| Firewall rules - Get | Gets a firewall rule. |
| Firewall rules - List by server | Returns a list of firewall rules. |