Azure SQL Database is a PaaS deployment option of Azure SQL that abstracts both the OS and the SQL Server instance. An Azure SQL database is a fully managed service. Azure SQL Managed Instance is ideal for customers interested in instance-scoped features, such as SQL Server Agent, Common language runtime (CLR), Database Mail, Distributed transactions, and Machine Learning Services. You can use SQL Managed Instance to do lift-and-shift migrations to Azure without having to redesign your applications.
- It’s a highly scalable, intelligent, relational database service built for the cloud with the industry’s highest availability SLA.
- SQL Database is the only deployment option that supports scenarios that require very large databases (currently up to 100 TB) or autoscaling for unpredictable workloads (serverless).
- You can create a SQL Database elastic database pool, where all databases in the pool share the same set of compute and storage resources. Each database can use the resources it needs, within the limits you set, depending on current load.
- There are two primary pricing options for SQL Database: DTU and vCore. A serverless option is also available for a single database.
Key Factors
Designing a SQL database in Azure involves considering the following key factors:
- Data modeling: The first step is to identify the entities and relationships between them. This can be done using an entity-relationship diagram (ERD). An ERD helps you identify the tables, columns, and relationships between them.
- Normalization: Once the entities are identified, it’s essential to normalize the data to ensure data integrity, minimize data redundancy, and avoid anomalies. Normalization involves splitting tables into smaller tables and defining relationships between them.
- Data types: Choose the appropriate data types for the columns to ensure data accuracy and optimize storage.
- Indexes: Indexes improve query performance by providing a faster way to search and retrieve data. Choose the appropriate indexes for the tables to ensure optimal query performance.
- Partitioning: Partitioning tables can help improve query performance, manage large data sets, and reduce storage costs. Choose the appropriate partitioning strategy for the tables based on the data access patterns.
- Security: Ensure that the database is secured by setting up the appropriate permissions for users and roles. This includes setting up firewalls, SSL encryption, and authentication protocols.
- Backup and recovery: Set up a backup and recovery strategy to ensure that the database can be restored in the event of a disaster. This includes setting up automated backups, disaster recovery options, and monitoring.
Overall, designing a SQL database in Azure requires careful planning and consideration of several key factors to ensure optimal performance, data accuracy, and security.
Azure SQL deployment options, comparison and recommended usage scenarios.
| Compare | SQL Database | SQL Managed Instance | SQL Server on Azure Virtual Machines |
|---|---|---|---|
| Scenarios | Best for modern cloud applications, hyperscale or serverless configurations | Best for most lift-and-shift migrations to the cloud, instance-scoped features | Best for fast migrations, and applications that require OS-level access |
| Features | Single database – Hyperscale storage (for databases up to 100 TB) – Serverless compute – Fully managed service Elastic pool – Resource sharing between multiple databases for price optimization – Simplified performance management for multiple databases – Fully managed service | Single instance – SQL Server surface area (vast majority) – Native virtual networks – Fully managed service Instance pool – Pre-provision compute resources for migration – Cost-efficient migration – Host smaller instances (2vCore) – Fully managed service | Azure Virtual Machines – SQL Server access – OS-level server access – Expansive version support for SQL Server – Expansive OS version support – File stream, Microsoft Distributed Transaction Coordinator (DTC), and Simple Recovery model – SQL Server Integration Services (SSIS), SQL Server Reporting Services (SSRS), and SQL Server Analysis Services (SSAS) |