MSDN Documentation

Azure SQL Database Reference

This section provides comprehensive reference documentation for Azure SQL Database, covering its features, functionalities, limitations, and best practices.

Core Concepts

Understand the fundamental building blocks and concepts of Azure SQL Database:

Database Objects

Azure SQL Database supports a rich set of database objects similar to SQL Server. Key objects include:

  • Tables: Structured data storage.
  • Views: Virtual tables based on the result-set of a stored query.
  • Stored Procedures: Precompiled SQL statements stored in the database.
  • Functions: Reusable code blocks that return a value.
  • Indexes: Data structures that improve the speed of data retrieval operations.
  • Triggers: Special stored procedures that execute automatically in response to certain events on a table or view.

Compute Tiers and Performance Levels

Azure SQL Database offers various compute tiers to suit different performance needs and budgets:

  • DTU (Database Transaction Unit): A blended measure of CPU, memory, and I/O resources. Available in Basic, Standard, and Premium tiers.
  • vCore (Virtual Core): A more granular model allowing independent scaling of compute and storage. Available in General Purpose, Business Critical, and Hyperscale service tiers.

DTU-based purchasing model

Tier DTUs Max Data Size I/O
Basic 5-10 2 GB Low
Standard 10-1000 Up to 500 GB Moderate
Premium 125-4000 Up to 1 TB High

vCore-based purchasing model

Service Tier Compute Generation Max vCores Max Data Size
General Purpose Gen5 80 Up to 100 TB
Business Critical Gen5 80 Up to 100 TB
Hyperscale Various 100+ Up to 100 TB

Storage Options

Azure SQL Database provides flexible storage options:

  • Managed Disks: Utilized by the underlying infrastructure for robust and scalable storage.
  • Data Files: Stored in Azure Blob Storage, providing durability and availability.
  • Log Files: Also stored in Azure Blob Storage, ensuring transaction integrity.
  • TempDB: Stored on local SSDs for high-performance temporary storage.

Maximum data size varies by service tier. For the most up-to-date limits, refer to the Azure SQL Database resource limits documentation.

Networking and Connectivity

Securely connect to your Azure SQL Database:

  • Firewall Rules: Control access to your database server at the server and database levels.
  • Virtual Network Service Endpoints: Restrict network access to a specific virtual network.
  • Private Link: Provides private connectivity to Azure SQL Database from your virtual network.
  • Connection Strings: Required by applications to establish a connection.

Example connection string (SQL Authentication):

Server=tcp:your_server_name.database.windows.net,1433;Initial Catalog=your_database_name;Persist Security Info=False;User ID=your_username;Password=your_password;MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Security Features

Protect your data with robust security measures:

  • Authentication: SQL authentication and Azure Active Directory authentication.
  • Authorization: Role-based access control (RBAC) and database-level permissions.
  • Encryption: Transparent Data Encryption (TDE) for data at rest, Always Encrypted for sensitive data, and SSL/TLS for data in transit.
  • Auditing: Track database events and log them to a storage account, Log Analytics, or Event Hubs.
  • Threat Detection: Advanced security features to detect and respond to potential threats.

Key SQL Language Features and Differences

While largely T-SQL compliant, Azure SQL Database has some specific behaviors and features:

  • Support for columnstore indexes for analytical workloads.
  • Availability of in-memory OLTP for high-performance transactional workloads.
  • Integration with Azure Machine Learning.
  • Use of Managed Identity for authentication.
  • Limitations on server-level objects like linked servers (use Elastic Database tools or other strategies).

Further Resources