What is a logical SQL server in Azure SQL Database and Azure Synapse?

Applies to: Azure SQL Database Azure Synapse Analytics

In Azure SQL Database and Azure Synapse Analytics, a server is a logical construct that acts as a central administrative point for a collection of databases. At the server level, you can administer logins, firewall rules, auditing rules, threat detection policies, and auto-failover groups . A server can be in a different region than its resource group. The server must exist before you can create a database in Azure SQL Database or a data warehouse database in Azure Synapse Analytics. All databases managed by a single server are created within the same region as the server.

This server is distinct from a SQL Server instance that you may be familiar with in the on-premises world. Specifically, there are no guarantees regarding location of the databases or data warehouse database in relation to the server that manages them. Furthermore, neither Azure SQL Database nor Azure Synapse expose any instance-level access or features. In contrast, the instance databases in a managed instance are all physically co-located - in the same way that you are familiar with SQL Server in the on-premises or virtual machine world.

When you create a 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 and Azure Synapse Analytics support 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.

A server in SQL Database and Azure Synapse:

• 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 warehouse database
• Is a logical container with strong lifetime semantics - delete a server and it deletes its databases, elastic pools, and SQK pools
• Participates in Azure role-based access control (Azure RBAC) - databases, elastic pools, and data warehouse database within a server inherit access rights from the server
• Is a high-order element of the identity of databases, elastic pools, and data warehouse database 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 (<serverName>.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, and such
• 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 or vCore 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 in your on-premises environment that are granted access to one or more databases on the server, and can be granted limited administrative rights. For more information, see Logins.
• The default collation for all databases created on a server 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.

To create a managed instance, see Create a managed instance

Manage servers, databases, and firewalls

You can manage servers, databases, and firewalls by using the Azure portal, Azure PowerShell, the Azure CLI, Transact-SQL (T-SQL) and REST API.

Portal

You can create the resource group for a server 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 server

To create a server (without a database, elastic pool, or data warehouse database) using the Azure portal, navigate to a blank SQL server (logical SQL server) form.

Create a blank or sample database in Azure SQL Database

To create a database in SQL Database using the Azure portal, navigate to a blank SQL Database form and provide the requested information. You can create the resource group and 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.

Manage existing server

To manage an existing server, navigate to the server using a number of methods - such as from 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.

For example, to configure a firewall rule, follow these steps:

1. Navigate to your database resource in the Azure portal. Select Set server firewall on the toolbar.

   

2. Set Public network access to Selected networks to reveal the virtual networks and firewall rules. When set to Disabled, virtual networks and firewall rule settings are hidden.

3. Choose Add a firewall rule to configure the firewall.

   

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 a database in SQL Database in the Azure portal.

PowerShell

You can configure your database using Azure 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, 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, 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-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.

Tip

For a PowerShell quickstart, see Create a database in Azure SQL Database using PowerShell. For PowerShell example scripts, see Use PowerShell to create a database in Azure SQL Database and configure a firewall rule and Monitor and scale a database in Azure SQL Database using PowerShell.

Azure CLI

To create and manage servers, 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 managed by 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
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

Tip

For an Azure CLI quickstart, see Create a database in Azure SQL Database using the Azure CLI. For Azure CLI example scripts, see Use the CLI to create a database in Azure SQL Database and configure a firewall rule and Use Azure CLI to monitor and scale a database in Azure SQL Database.

Transact-SQL

To create and manage 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 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 in Azure SQL Database. You must be connected to the master database to create a new database.
CREATE DATABASE (Azure Synapse)	Creates a new data warehouse database in Azure Synapse. You must be connected to the master database to create a new database.
ALTER DATABASE (Azure SQL Database)	Modifies database or elastic pool.
ALTER DATABASE (Azure Synapse Analytics)	Modifies a data warehouse database in Azure Synapse.
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 a database. If logged on to the master database for a server, returns information on all databases. For Azure Synapse, you must be connected to the master database.
sys.dm_db_resource_stats (Azure SQL Database)	Returns CPU, IO, and memory consumption for a database in Azure SQL 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 a database in Azure SQL Database. The data is collected and aggregated within five-minute intervals.
sys.database_connection_stats (Azure SQL Database)	Contains statistics for database connectivity events for Azure SQL Database, providing an overview of database connection successes and failures.
sys.event_log (Azure SQL Database)	Returns successful database connections and connection failures for Azure SQL Database. You can use this information to track or troubleshoot your database activity.
sp_set_firewall_rule (Azure SQL Database)	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 (Azure SQL Database)	Returns information about the server-level firewall settings associated with a server.
sp_delete_firewall_rule (Azure SQL Database)	Removes server-level firewall settings from a 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 a database in Azure SQL Database. Database firewall rules can be configured for the master database, and for user databases in SQL Database. Database firewall rules are useful when using contained database users. Database firewall rules are not supported in Azure Synapse.
sys.database_firewall_rules (Azure SQL Database)	Returns information about the database-level firewall settings for a database in Azure SQL Database.
sp_delete_database_firewall_rule (Azure SQL Database)	Removes database-level firewall setting for a database of yours in Azure SQL Database.

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.

REST API

To create and manage 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 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.
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.

Next steps

• To learn about migrating a SQL Server database to Azure SQL Database, see Migrate to Azure SQL Database.
• • For information about supported features, see Features.