| title | description | services | ms.service | ms.subservice | ms.custom | ms.devlang | ms.topic | author | ms.author | ms.reviewer | manager | ms.date |
|---|---|---|---|---|---|---|---|---|---|---|---|---|
Create, manage Azure SQL servers and single databases | Microsoft Docs |
Learn about creating and managing logical servers and single databases. |
sql-database |
sql-database |
single-database |
conceptual |
CarlRabeler |
carlrab |
craigg |
12/03/2018 |
You can create and manage Azure SQL Database logical servers and single databases using the Azure portal, PowerShell, Azure CLI, REST API, and Transact-SQL.
You can create the Azure SQL database's resource group ahead of time or while creating the server itself. There are multiple methods for getting to a new SQL server form, either by creating a new SQL server or as part of creating a new database.
To create an Azure SQL Database server (without a database) using the Azure portal, navigate to a blank SQL server (logical server) form.
To create an Azure SQL database using the Azure portal, navigate to a blank SQL Database form and provide the requested information. You can create the Azure SQL database's resource group and logical server ahead of time or while creating the 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.
To create a Managed Instance, see Create a Managed Instance
To manage an existing server, navigate to the server using a number of methods - such as from specific SQL database page, the SQL servers page, or the All resources page.
To manage an existing database, navigate to the SQL databases page and click 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. [!TIP] For an Azure portal quickstart, see Create an Azure SQL database in the Azure portal.
To create and manage Azure SQL logical servers, single and pooled databases, and logical server 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 Azure SQL database and configure a logical server firewall rule and Monitor and scale a single SQL database using PowerShell.
| Cmdlet | Description |
|---|---|
| New-AzureRmSqlDatabase | Creates a database |
| Get-AzureRmSqlDatabase | Gets one or more databases |
| Set-AzureRmSqlDatabase | Sets properties for a database, or moves an existing database into an elastic pool |
| Remove-AzureRmSqlDatabase | Removes a database |
| New-AzureRmResourceGroup | Creates a resource group |
| New-AzureRmSqlServer | Creates a server |
| Get-AzureRmSqlServer | Returns information about servers |
| Set-AzureRmSqlServer | Modifies properties of a server |
| Remove-AzureRmSqlServer | Removes a server |
| New-AzureRmSqlServerFirewallRule | Creates a server-level firewall rule |
| Get-AzureRmSqlServerFirewallRule | Gets firewall rules for a server |
| Set-AzureRmSqlServerFirewallRule | Modifies a firewall rule in a server |
| Remove-AzureRmSqlServerFirewallRule | Deletes a firewall rule from a server. |
| New-AzureRmSqlServerVirtualNetworkRule | Creates a virtual network rule, based on a subnet that is a Virtual Network service endpoint. |
To create and manage Azure SQL server, databases, and firewalls with Azure CLI, use the following Azure CLI SQL Database commands. Use the Cloud Shell to run the 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 Create a single Azure SQL database using the Azure CLI. For Azure CLI example scripts, see Use CLI to create a single Azure SQL database and configure a firewall rule and Use CLI to monitor and scale a single SQL database.
| 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 |
To create and manage Azure SQL server, 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 an Azure SQL Database server and pass Transact-SQL commands. For managing elastic pools, see Elastic pools.
Tip
For a quickstart using SQL Server Management Studio on Microsoft Windows, see Azure SQL Database: Use SQL Server Management Studio to connect and query data. For a quickstart using Visual Studio Code on the macOS, Linux, or Windows, see Azure SQL Database: Use Visual Studio Code to connect and query data. [!IMPORTANT] You cannot 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 (Azure SQL Database) | Modifies an Azure SQL database. |
| DROP DATABASE (Transact-SQL) | Deletes a database. |
| sys.database_service_objectives (Azure SQL Database) | Returns the edition (service tier), service objective (pricing tier), and elastic pool name, if any, for an Azure SQL database or an Azure SQL Data Warehouse. If logged on to the master database in an Azure SQL Database server, returns information on all databases. For Azure SQL Data Warehouse, you must be connected to the master database. |
| sys.dm_db_resource_stats (Azure SQL Database) | Returns CPU, IO, and memory consumption for an Azure SQL Database database. One row exists for every 15 seconds, even if there is no activity in the database. |
| sys.resource_stats (Azure SQL Database) | Returns CPU usage and storage data for an Azure SQL Database. The data is collected and aggregated within five-minute intervals. |
| sys.database_connection_stats (Azure SQL Database) | Contains statistics for SQL Database database connectivity events, providing an overview of database connection successes and failures. |
| sys.event_log (Azure SQL Database) | Returns successful Azure SQL Database database connections, connection failures, and deadlocks. You can use this information to track or troubleshoot your database activity with SQL Database. |
| sp_set_firewall_rule (Azure SQL Database) | Creates or updates the server-level firewall settings for your SQL Database 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 (Azure SQL Database) | Returns information about the server-level firewall settings associated with your Microsoft Azure SQL Database. |
| sp_delete_firewall_rule (Azure SQL Database) | Removes server-level firewall settings from your SQL Database server. This stored procedure is only available in the master database to the server-level principal login. |
| sp_set_database_firewall_rule (Azure SQL Database) | Creates or updates the database-level firewall rules for your Azure SQL Database or SQL Data Warehouse. 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 (Azure SQL Database) | Returns information about the database-level firewall settings associated with your Microsoft Azure SQL Database. |
| sp_delete_database_firewall_rule (Azure SQL Database) | Removes database-level firewall setting from your Azure SQL Database or SQL Data Warehouse. |
To create and manage Azure SQL server, 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. |
- To learn about migrating a SQL Server database to Azure, see Migrate to Azure SQL Database.
- For information about supported features, see Features.