Learn Azure SQL Database terminology
If you are starting with Azure cloud and Azure SQL Database, you are probably facing with a number of new cloud-specific terms. Here you can find a short introduction and glossary of terms that can help you understand the concepts in Azure SQL Database.
Azure SQL Database
Azure SQL Database is a fully managed database engine service based on the latest stable code of SQL Server Enterprise Edition and hosted in Microsoft Azure cloud. Azure SQL Database combines the best Database Engine features with the PaaS capabilities such as automated backups, disaster recovery, easy scale, performance tuning, advanced security. Learn more about Azure SQL Database here.
Azure SQL Database — Singleton (Single Database)
Azure SQL Database (singleton), or Single Database is a fully managed Database-as-a-Service that provides the most important database features available in SQL Server and includes the Azure SQL database PaaS capabilities. Some instance-level features such as SQL Agent, CLR, Service Broker are not supported in Single Database. Learn more about Single Database here.
Azure SQL Database — Managed Instance
Managed Instance is fully managed SQL Server Database Engine Instance hosted in Azure cloud. It provides almost full feature parity with the latest SQL Server Database Engine and enables extremely easy migration from SQL Server to Azure cloud. Learn more about Managed Instance here.
Azure SQL Database — Logical Server
Logical server is a “logical” boundary that ties several Single Databases and provide them the same server name that can be, server-level login, firewall/rule settings, performance tuning policies, etc. Logical server is not charged. Learn more about logical server here.
Azure SQL Database — Elastic pool
Elastic pool is a group of Single Databases that share the resources. The amount of CPU power, memory, bandwidth, and storage are assigned to the group and database in the group compete for the resources. Learn about Elastic pools here.
Service tier
Service tiers are architectural modifications of SQL Server Database engine required to make it fully managed PaaS service in the Azure cloud. Performance and capabilities of databases depend on service tiers they have. In vCore model there are three service tiers — General Purpose, Business Critical, and HyperScale. In DTU model you can use Basic, Standard, and Premium tiers. Learn more about service tiers here.
General purpose service tier
General purpose architectural tier is the architecture designed for a variety of generic SQL workloads. It should match performance of SQL Server IaaS deployed on Azure VM in most of the cases. Learn more about General Purpose service tier here. Learn more about General Purpose service tier here.
Business critical service tier
Business critical service tier is the architecture designed for performance sensitive workload that require low storage latency. In addition, Business critical tier provides one free-of-charge readable replica of the database that can be used to run read-only queries such as reports and analytics. Learn more about Business Critical tier here.
Hyperscale service tier
Hyperscale service tier is an architecture designed to support a very large databases (up to 100TB) with minimal impact of maintenance operation such as automated backups on the workload, and ability to create additional readable replicas. Learn more about Hyperscale tier here.
Premium service tier
Premium service tier is equivalent to Business Critical tier. This is the name of Business critical-compatible service tier in the old DTU purchasing model. Learn more about Premium/Business Critical tier here.
Standard service tier
Standard service tier is equivalent to General purpose tier. This is the name of General purpose compatible service tier in the old DTU purchasing model. Learn more about Standard/General Purpose service tier here.
Basic service tier
Basic service tier is equivalent to General purpose tier. This is the name of General purpose compatible service tier with small number of resources and low prices in the old DTU purchasing model.
PremiumRS service tier
PremiumRS is old deprecated service tier that was available in DTU model. See how to migrate from Premium RS service tier if you are still using it.
Resource purchasing model
Purchasing model is a model that defines how you are purchasing resources for your databases, instance or pool. Thow models are available in Azure SQL Database vCore resource purchasing model and (old) DTU resource purchasing model. See DTU-based purchasing model and vCore-based purchasing model for more information.
vCore resource purchasing model
The vCore model enables you to independently choose compute power (vCores) and storage. IO bandwidth is proportional either to compute power or size of storage in different service tiers. It is designed to give customers flexibility, control, transparency, and a straightforward way to translate on-premises workload requirements to the cloud. It also allows customers to scale their compute and storage resources based upon their workload needs. Single database and elastic pool options using the vCore model are also eligible for up to 30 percent savings with the Azure Hybrid Benefit for SQL Server and you can get the additional saving if you Prepay for reserved capacity.
Virtual core (vCore)
A virtual core represents the logical CPU offered with an option to choose between generations of hardware. Gen 4 Logical CPUs are based on Intel E5–2673 v3 (Haswell) 2.4-GHz processors and Gen 5 Logical CPUs are based on Intel E5–2673 v4 (Broadwell) 2.3-GHz processors.
Hardware generation
vCore model enables you to choose underlying hardware generation where the service will be executed.
DTU resource purchasing model
DTU-based purchasing model is a resource purchasing model where you can choose bundled compute&storage packages balanced for common workloads. Logical servers in Azure SQL Database offer both DTU-based purchasing model and vCore-based purchasing model. Within this purchasing model, you can choose single databases or elastic pools. Managed Instances in Azure SQL Database only offer the vCore-based purchasing model.
You can find more information how to migrate from DTU model to vCore model here.
Database transaction unit (DTU)
DTU is a unit of measure for the resources assigned to databases in DTU resource purchasing model. DTU is a virtual resource container that defines maximum amount of compute power, storage, and IO bandwidth for a database. Ratios for compute, storage, and IO are balanced for most of the generis workloads. By choosing higher DTU you are purchasing more power and better performance. Lear more about DTU here.
Azure Hybrid Benefit for SQL Server
AHB enables you to get the discounts for the Azure SQL resources that you are purchasing if you have active Software assurance licenses for SQL Server on premises. The Azure Hybrid Benefit for SQL Server helps you maximize the value from your current licensing investments and accelerate their migration to the cloud. Azure Hybrid Benefit for SQL Server is an Azure-based benefit that enables you to use your SQL Server licenses with Software Assurance to pay a reduced price (“base price”) on SQL Database. You may apply this benefit even if the SKU is active but note the base rate is applied from the time you select it in the Azure portal. No credit will be issued retroactively.
Base Price
BasePrice is the minimal price of the Azure SQL Database service that you need to pay even if you are bringing your own licenses from on-premises and using Azure Hybrid Benefit. Base price covers that cost of the underlying infrastructure (for example Azure VMs) used to run Azure SQL Database. Base price is applicable only in vCore model.
License Included price
LicenseIncluded price is the price that includes the license for SQL Server on top of the Base price. Use this purchase option if you don’t have SQL Server on-premises license or you don’t want to use it to get the discount as part of the Azure Hybrid Benefit.
Reserved SQL Database capacity
Reserving capacity enables you save money with Azure SQL Database by prepaying for Azure SQL Database compute resources compared to pay-as-you-go prices. Learn how to Prepay for reserved capacity.
PaaS capabilities
Azure SQL database has a number of PaaS capabilities that bring the additional values to the SQL Server Database Engine features. Additional pass capabilities are automated backups that Azure takes without your involvement, ability to restore database to any point in time in the past (where backups are available), easily scale-up or scale down your resources, etc. Learn more about PaaS capabilities here.
Automated backups
Azure SQL Database automatically takes backups of your database every five minutes. Full database backups are taken every 7 days, and incremental/diff backups every 12 hours. Learn more about automated backups here.
Point in time restore (PITR)
Azure SQL Database enables you to create a copy of the existing database from some point in time in the past by restoring some of the automatically taken backups. Learn more about point-in-time restore here.
Short-term backup retention
Azure SQL Database enables you to choose how many days automatically taken backups should be retained. The value that you can choose is between 7 and 35 days. Learn more about backup retention here.
Long-term backup retention
Azure SQL Database (Singleton) enables you to keep automatic backups up to 10 years. Learn more about long-term backup retention here.
Restoring deleted/dropped database
Azure SQL Database enables you to restore a database that you have accidentally deleted. Learn more about deleted database restore here.
Geo-restore
You can restore a SQL database on any server in any Azure region from the most recent geo-replicated backups. Geo-restore uses a geo-redundant backup as its source and can be used to recover a database even if the database or datacenter is inaccessible due to an outage. Learn more about geo-restore here.
Native restore
Managed Instance supports built-in T-SQL RESTORE command that enables you to take the backup of your SQL Server backup (.bak file) and restore that backup directly to Managed Instance. Backup file can be restored if it is place on Azure Blob Storage using RESTORE DATABASE FROM URL command. Find how to restore database here.
User-initiated backups
Managed Instance enables you to backup your database to Azure Blob Storage using native T-SQL BACKUP statement. You can create only COPY_ONLY backups. For troubleshooting a backup to a URL, see SQL Server Backup to URL Best Practices and Troubleshooting. Single Database and Elastic pool don’t support user initiated backups so you should use BACPAC instead of backup.
BACPAC Import/Export
BACPAC is platform-independent format that you can use to export any database (both schema and data) and import it to another SQL Server or Azure SQL instance. BACPAC is supported in all versions of SQL Servers and all flavors of Azure SQL Database. BACPAC file is not compact as database backups so you should use it for databases with the size less than a couple of hundreds GB. Learn more about importing schema and data using bacpac here.
Scale-up
Scale-up is PaaS ability of Azure SQL database to dynamically add more resources (CPU, memory, storage) if there are needed for the current workload. Adding more resources increases the price of the service. Learn more about the scalability here.
Scale-down
Scale-down is ability of Azure SQL Database to dynamically release the resources that are not needed in order to decrease the price of the service. Learn more about the scalability here.
Read scale-out
Read Scale-Out is a feature of database that enables you to use additional read-only or readable replica of your database to run the queries that just read from the database (for example analytic or reports) without affecting primary writable database. Read-scale out is built-in into Business Critical/Premium tiers, while in General purpose/Standard can be configured using geo-replication.
Readable replica
Readable replica is asynchronous copy of your database where you can run the queries that just read from the database (for example analytic or reports). Business critical/premium tier provide one built-in free-of-charge readable replica, while in General purpose/Standard can be configured using geo-replication.
Geo-replication
Geo-replication is Azure SQL Database feature that allows you to create readable secondary databases of individual databases on a logical server in the same or different data center (region). Readable secondary replica can be used for disaster recovery or to run the queries that just read from the database (for example analytic or reports) without affecting primary writable database. Learn more about geo-replication here.
Auto-failover groups
Auto-failover groups is a SQL Database feature that allows you to manage replication and failover of a group of databases on a logical server or all databases in a Managed Instance to another region (currently in public preview for Managed Instance). Learn more about auto-failover groups here.
Zone redundancy
Azure SQL Database — Single database and Elastic pool allows you to provision Premium or Business Critical databases or elastic pools across multiple availability zones. Because these databases and elastic pools have multiple redundant replicas for high availability, placing these replicas into multiple availability zones provides higher resilience, including the ability to recover automatically from the datacenter scale failures without data loss. Learn about zone-redundant configuration here.
Advanced Security Protection
Azure SQL Database provides a set of advanced security features that protects your database and discover vulnerabilities, such as Threat detection, Vulnerability assessment and Data discovery and classification. Learn more about the advanced security here.
Threat detection
SQL Threat Detection enables you to detect and respond to potential threats as they occur by providing security alerts on anomalous activities. Users receive an alert upon suspicious database activities, potential vulnerabilities, and SQL injection attacks, as well as anomalous database access and queries patterns.
Transparent data encryption (encryption at rest)
Transparent data encryption performs real-time encryption and decryption of the database, associated backups, and transaction log files at rest without requiring changes to the application.
Vulnerability assessment
SQL Vulnerability Assessment is an easy to configure service that can discover, track, and help you remediate potential database vulnerabilities. Use it to proactively improve your database security.
Data discovery and classification
Data Discovery & Classification provides advanced capabilities built into Azure SQL Database for discovering, classifying, labeling & protecting the sensitive data in your databases. Discovering and classifying your most sensitive data (business, financial, healthcare, personally identifiable data (PII), and so on.) can play a pivotal role in your organizational information protection stature.
Azure VNet (Virtual Network)
Azure Virtual Network is networking boundary for the resources (for example Managed Instance) that you can deploy in Azure that provides private IP addresses to your network resources. Managed Instance is your private resource placed on a private IP address and deployed in your Azure VNet. You need to prepare and configure your Azure VNet and create a subnet where the Managed Instance will be placed. Read the ultimate guide for creating and configuring azure sql managed instance environment for more details.
Subnet
Subnet is a set of IP addresses placed in predefined range. Managed Instance is your private resource placed on a private IP address in the subnet within your Azure VNet. Subnet defines a network range of IP addresses where the instance will be placed, and cannot contain any resource other than Managed Instance. There are other requirements needed when you setup the subnet that you can find in the ultimate guide for creating and configuring azure sql managed instance environment.
Virtual cluster
Virtual cluster in a security boundary that wraps Managed Instances in a subnet. Virtual cluster is created when the first Managed Instance is created in subnet, and deleted 12 hours after the last Managed Instance from the subnet is deleted.
Service endpoint
Service endpoint in Azure SQL Database logical server allows you to isolate connectivity to your logical server from only a given subnet or set of subnets within your virtual network. The traffic to Azure SQL Database from your VNet will always stay within the Azure backbone network. This direct route will be preferred over any specific routes that take Internet traffic through virtual appliances or on-premises. Service endpoint is not available in Managed Instance.
VNet Peering
Peering is process of connection two VNets that enables resources from one VNet to access the resources in the another VNet. After virtual networks are peered, resources such as applications in VMs or Azure Web App serviced in one virtual network can directly connect with Managed Instances that are placed in the peered VNet. This is required if you have Managed Instances placed in one network and resources that should access the instance in the another VNet.
Firewall
Firewall is a component that prevent access from some IP addresses and/or port to Single Databases on Logical Server. See how to configure firewall rules Logical Server. Managed Instance has built-in firewall that cannot be re-configured.
Automatic query plan correction (forcing last good plan)
Azure SQL Database constantly monitors execution of the queries and detects queries that are running slower than before. In these cases, it will automatically apply previous better query plan without your intervention if you enable this feature.
Automatic index creation
Azure SQL Database may automatically create recommended indexes if you enable this feature. If the created index degrades performance or makes no difference it would be immediately dropped.
Automatic index cleanup (drop unused indexes)
Azure SQL Database may automatically drop unused indexes if you enable this feature.
Performance insights
Azure SQL Database provides Intelligent insights into performance, Tuning actions and recommendations that can help you optimize performance of your database and trouble shoot performance issues.
In-memory columnstore
In-memory columnstore technology is enabling you to store and query a large amount of data in the tables. Columnstore technology uses column-based data storage format and batch query processing to achieve gain up to 10 times the query performance in OLAP workloads over traditional row-oriented storage. You can also achieve gains up to 10 times the data compression over the uncompressed data size.
In-memory OLTP (Hekaton)
In-memory OLTP technology provides extremely fast data access operations by keeping all data in memory. It also uses specialized indexes, native compilation of queries, and latch-free data-access to improve performance of the OLTP workload.
Multi-model capabilities
Azure SQL Database enables you to combine relational database features with non-relational concepts such as JSON, Graphs, Spatial, and XML. This way, you can use any data format without need to move parts of your data to NoSQL databases. Learn more about multi-model capabilities here.
Transactional replication
Transactional replication enables you to send the updates made in one database table to another table in remote database. This way you can synchronize data in multiple tables and keep copies of data. You can also use transactional replication to move your database from SQL server to Azure SQL database.