Create and manage Azure SQL Database servers and databases

An Azure SQL database is a managed database in Microsoft Azure that is created within an Azure resource group with a defined set of compute and storage resources for different workloads. An Azure SQL database is associated with an Azure SQL Database logical server, which is created within a specific Azure region.

An Azure SQL database can be a single, pooled, or partitioned database

An Azure SQL database can be:

Tip

For valid database names, see Database Identifiers.

  • The default database collation used by Microsoft Azure SQL Database is SQL_LATIN1_GENERAL_CP1_CI_AS, where LATIN1_GENERAL is English (United States), CP1 is code page 1252, CI is case-insensitive, and AS is accent-sensitive. For more information about how to set the collation, see COLLATE (Transact-SQL).
  • Microsoft Azure SQL Database supports tabular data stream (TDS) protocol client version 7.3 or later.
  • Only TCP/IP connections are allowed.

What is an Azure SQL logical server?

A logical server acts as a central administrative point for multiple databases, including elastic pools logins, firewall rules, auditing rules, threat detection policies, and failover groups. A logical server can be in a different region than its resource group. The logical server must exist before you can create the Azure SQL database. All databases on a server are created within the same region as the logical server.

Important

In SQL Database, a server is a logical construct that is distinct from a SQL Server instance that you may be familiar with in the on-premises world. Specifically, the SQL Database service makes no guarantees regarding location of the databases in relation to their logical servers, and exposes no instance-level access or features.

When you create a logical server, you provide a server login account and password that has administrative rights to the master database on that server and all databases created on that server. This initial account is a SQL login account. Azure SQL Database supports SQL authentication and Azure Active Directory Authentication for authentication. For information about logins and authentication, see Managing Databases and Logins in Azure SQL Database. Windows Authentication is not supported.

Tip

For valid resource group and server names, see Naming rules and restrictions.

An Azure Database logical server:

  • Is created within an Azure subscription, but can be moved with its contained resources to another subscription
  • Is the parent resource for databases, elastic pools, and data warehouses
  • Provides a namespace for databases, elastic pools, and data warehouses
  • Is a logical container with strong lifetime semantics - delete a server and it deletes the contained databases, elastic pools, and data warehouses
  • Participates in Azure role-based access control (RBAC) - databases, elastic pools, and data warehouses within a server inherit access rights from the server
  • Is a high-order element of the identity of databases, elastic pools, and data warehouses for Azure resource management purposes (see the URL scheme for databases and pools)
  • Collocates resources in a region
  • Provides a connection endpoint for database access (.database.windows.net)
  • Provides access to metadata regarding contained resources via DMVs by connecting to a master database
  • Provides the scope for management policies that apply to its databases - logins, firewall, audit, threat detection, etc.
  • Is restricted by a quota within the parent subscription (six servers per subscription by default - see Subscription limits here)
  • Provides the scope for database quota and DTU quota for the resources it contains (such as 45,000 DTU)
  • Is the versioning scope for capabilities enabled on contained resources
  • Server-level principal logins can manage all databases on a server
  • Can contain logins similar to those in instances of SQL Server on your premises that are granted access to one or more databases on the server, and can be granted limited administrative rights. For more information, see Logins.

Azure SQL databases protected by SQL Database firewall

To help protect your data, a SQL Database firewall prevents all access to your database server or any of its databases from outside of your connection to the server directly through your Azure subscription connection. To enable additional connectivity, you must create one or more firewall rules. For creating and managing elastic pools, see Elastic pools.

Manage Azure SQL servers, databases, and firewalls using the Azure portal

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.

Create a blank SQL server (logical server)

To create an Azure SQL Database server (without a database) using the Azure portal, navigate to a blank SQL server (logical server) form.

Create a blank or sample SQL database

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.

create database-1

Important

For information on selecting the pricing tier for your database, see Service tiers.

Manage an existing SQL server

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.

server firewall rule

Important

To configure performance properties for a database, see Service tiers.

Tip

For an Azure portal quick start tutorial, see Create an Azure SQL database in the Azure portal.

Manage Azure SQL servers, databases, and firewalls using PowerShell

To create and manage Azure SQL server, databases, and firewalls with Azure PowerShell, use the following PowerShell cmdlets. If you need to install or upgrade PowerShell, see Install Azure PowerShell module. For creating and managing elastic pools, see Elastic pools.

Cmdlet Description
New-AzureRmSqlDatabase Creates a database
Get-AzureRmSqlDatabase Gets one or more databases
Set-​Azure​Rm​Sql​Database Sets properties for a database, or moves an existing database into an elastic pool
Remove-​Azure​Rm​Sql​Database 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-​Azure​Rm​Sql​Server​Firewall​Rule Gets firewall rules for a server
Set-​Azure​Rm​Sql​Server​Firewall​Rule Modifies a firewall rule in a server
Remove-​Azure​Rm​Sql​Server​Firewall​Rule Deletes a firewall rule from a server.

Manage Azure SQL servers, databases, and firewalls using the Azure CLI

To create and manage Azure SQL server, databases, and firewalls with the 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.

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

Manage Azure SQL servers, databases, and firewalls using Transact-SQL

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.

Important

You cannot create or delete a server using Transact-SQL.

Command Description
CREATE DATABASE (Azure SQL Database) Creates a new database. You must be connected to the master database to create a new database.
ALTER DATABASE (Azure SQL Database) Modifies an Azure SQL database.
ALTER DATABASE (Azure SQL Data Warehouse) Modifies an Azure SQL Data Warehouse.
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, I/O, 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.
Tip

For quick start tutorial using SQL Server Management Studio on Microsoft Windows, see Azure SQL Database: Use SQL Server Management Studio to connect and query data. For a quick start tutorial using Visual Studio Code on the macOS, Linux, or Windows, see Azure SQL Database: Use Visual Studio Code to connect and query data.

Manage Azure SQL servers, databases, and firewalls using the REST API

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.
Servers - List By Resource Group Returns a list of servers in a resource group.
Servers - Update Updates an existing server.
Servers - Sql Determines whether a resource can be created with the specified name.
Databases - Create Or Update Creates a new database or updates an existing database.
Databases - Get Gets a database.
Databases - Get By Elastic Pool Gets a database inside of an elastic pool.
Databases - Get By Recommended Elastic Pool Gets a database inside of a recommented elastic pool.
Databases - List By Elastic Pool Returns a list of databases in an elastic pool.
Databases - List By Recommended Elastic Pool Returns a list of databases inside a recommented 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.

Next steps