What Is Azure SQL?

When we speak of modern cloud solutions, we mean much more than just data storage. Cloud technologies cover virtual services, including analytics, databases, networking, servers, and storage via the internet. Such a giant as Microsoft is among the most prominent cloud providers with its Azure platform. 

“Platform as a service” or, abbreviated as PaaS, is a popular solution for database specialists. It is a powerful database engine that allows you to perform most database management routines. Upgrading, updating, backups, and monitoring tasks are all covered by the provider. It never goes down and is always up-to-date. 

At the current stage, Azure SQL PaaS offers you the latest version of the SQL Server database engine with complete support for all features. This way, you can focus on specific database tasks only and never worry about general optimization and administration routines. 

Azure SQL Database is a part of the entire Microsoft Azure PaaS franchise – a combination of several smart products integrated with the Azure Cloud platform:

• Azure SQL Database is a product provided under the database-as-a-service model. It offers the Azure PaaS SQL capacities to build applications and websites, while Microsoft manages the infrastructure.
• Azure SQL Managed Instance is the SQL Server engine as PaaS, including familiar tools and resources to accomplish database-related jobs. You can customize the engine, configure the recovery model, run and control the maintenance, etc.  The Managed Instance is the best solution for migrating your workloads to the cloud.
• SQL Server on Azure Virtual Machines (VMs) is the infrastructure as a Service (IaaS) solution – the SQL Server that runs in a virtual machine in the cloud. You can apply the SQL Server license you already have or use the capacities of the Azure portal. This model suits both development and migration purposes and it is perfect for organizations that already work with virtual machines.

The current article will explore the Azure SQL Database.

Contents

• Azure SQL Database
• Deployment models
• Purchasing models
• Service tiers
• Advantages of the Azure SQL Database
  • Security and compliance
  • Threat protection
  • Performance monitoring, tuning, and alerting
• dbForge SQL Server tools
• Conclusion

Azure SQL Database

We have already defined the Azure SQL Database – it is a cloud-based database service for SQL Server devotees. Microsoft literally takes on the servers’ and data centers’ administration, including patches, upgrades, and backing up.

Microsoft Azure SQL Database allows you to migrate your projects/applications to the cloud – easily and securely, with minimum changes and no downtimes for databases. The application performance becomes much more stable and predictable. Also, you can have multiple Azure SQL databases and manage them all more effectively at less cost. 

The maximum size of the Azure SQL Database is now 2TB for a single database. The available storage size depends on the chosen service tier.

Deployment models

If you choose to use the Azure platform for SQL databases, there are two available options: single database and elastic pool. Both have their peculiarities. 

Single database

A single database at Azure SQL platform is an equivalent of a usually contained SQL Server database, just hosted in the cloud. This database is isolated from other databases and managed via a server. When you assign resources to every single database, they belong to it only, not shared with other databases, under any service tier.

The single database deployment model is a solution for a cloud application that requires a single data source. Also, it is possible to scale up or down resources allocated to the particular database. 

Elastic pool

This deployment model relates to multiple databases with shared resources managed altogether via a logical server. You can move a single database into this elastic pool or remove it from there whenever you need. 

The elastic pool is a solution for several databases, where each database requires resources to work effectively. If you don’t know how many resources each particular database will consume and how much you should allocate at once, then the work is challenging. The SQL pool in Azure resolves this issue in the following way: when any separate database sets unique and unpredictable resource requirements, the elastic pool allocates the necessary resources to the target database itself.  

The ability to handle resource requirements is measured in DTUs (Database Transaction Units) for single databases and eDTUs (elastic Database Transaction Units) for elastic database pools. 

The pool receives a definite number of eDTUs for a set price. Therefore, a user pays for the Azure SQL elastic pool as a single whole, not for each separate database. Within the pool, an individual database can consume more eDTUs, taking them from the overall number present, if the load grows. When the load is less or absent – no eDTUs are consumed. 

One more essential issue is storage. The elastic pool specifies the storage in GBs, and you can share it between all databases. However, you can’t exceed this storage limit. If your databases grow too large and their aggregated size goes beyond the elastic pool storage, all databases become read-only. 

You can add more eDTUs to the elastic pool. Vice versa, you can remove extra eDTUs from the pool if databases don’t consume them. It’s possible to do this at any moment and it won’t cause any negative impacts or downtime. 

Purchasing models

You can purchase the cloud-based Azure SQL Database under the vCore and DTU models. Let us review them more precisely. 

vCore purchasing model

vCore stands for “virtual core” and represents logical CPU. The model is also known as serverless. If you choose this model, you can specify the hardware characteristics you want to have in the cloud, such as memory, storage, the number of cores, etc. This option lets you “transfer” the requirements from on-premise to the cloud, ensuring that you have the required environment to work with databases most efficiently. Also, you can migrate your applications to the cloud easily. 

The price will depend on the service tier and your particular requirements for the resources, storage, and hardware configuration. The vCore-based model is available in Standard, Premium, and Hyperscale service tiers. 

DTU-based purchasing model

DTU stands for the Database Transaction Unit – a measure for the bundle of computing and storage. The DTU-based purchasing model means that you receive the preconfigured bundle that covers the CPU capabilities and memory under fixed monthly payments. It suits both single databases and elastic pools. 

This model is available in Basic, Standard, and Premium service tiers. The Standard and Premium tiers allow you to add more storage. In general, under this model, you get a certain granted level of resources for any database, and it does not matter if there are other databases. Thus, each database will perform predictably.  

Users who seek preconfigured resources usually prefer going with the DTU-based model. It is simpler. The vCore-based model requires more effort from the customer’s side, but it provides more flexibility and control. For instance, the serverless compute tier available under the vCore model lets you scale the compute resources, pause the databases automatically during periods of inactivity, resume them when necessary, etc. 

Microsoft recommends the vCore-based model for customers, but you can choose the option that suits you best. Switching from the DTU-based model to the vCore-based one is possible at any moment.

Service tiers

Azure SQL Database offers several service tiers to target different workloads: Basic, Standard (General Purpose), Premium (Business Critical), and HyperScale. They all grant you appropriate performance, security, and business continuity. 

• The Basic service tier is the simplest option that supports one active operation at a time. It usually suits databases for small and rarely used applications. We won’t pay close attention to it and proceed to more popular service tiers

• The Standard (General Purpose) service tier is a default option for both the Database and Managed Instance in Azure SQL. The Standard service tier can be used by most cloud apps perfectly. The storage size varies from 1GB to 4TB and Azure takes care of all upgrades and patching

• The Premium (Business Critical) service tier is designed for powerful applications that demand low-latency responses, fast recovery in case of any infrastructure failures, analyzing data loads, etc. If an application is critical for your business, you should go with the Premium service tier. The storage size is the same as for the General Purpose service tier – from 1Gb to 4 TB

• The Hyperscale service tier is a new option that is present in the vCore model only. It offers much more scalable storage with significantly more power for computing, more than in any other service tier. The size of a database can be up to 100 TB in the Hyperscale service tier and databases don’t have a definite maximal size initially. It grows when you need it. Backups and restores are performed much faster and the overall performance is much higher, no matter which data volumes it operates

The choice depends on your business requirements for storage and performance. The billing is hourly for any service tier. You can change a type of the service tier at any moment from the Azure portal or by executing the dedicated SQL query. 

Advantages of the Azure SQL Database

Microsoft was among the major players who launched and shaped modern cloud platforms, creating a comprehensive infrastructure to manage data and databases. Azure SQL Databases are perfect replacements for “standard” SQL Server databases on-premise. Besides, the Azure cloud offers additional benefits.

Security and compliance

Azure cares about the customers’ data safety. The Firewall prevents any unauthorized access to databases and virtual network rules ensure accepting requests only from selected subnets. It tracks malicious activities that threaten the safety of databases and sends alerts to customers.

Threat protection

Advanced Threat Protection provided by Microsoft Azure delivers a new level of database security. The tool detects and fixes any anomalies and vulnerabilities. You can receive alerts and email notifications to inform you about suspicious activities, SQL attacks, abnormal database access, and query patterns.

Performance monitoring, tuning, and alerting

Azure SQL Database monitors CPU and IO according to the service tier and lets customers view the Query Performance Insights. The system provides recommendations on performance optimization, and you can configure the implementation automatically. An AI is used to troubleshoot issues and help you maximize database performance.

dbForge SQL Server tools

Devart provides quite a wide range of tools to work with the Azure SQL Database. Let’s review each of them.

dbForge Studio for SQL Server

dbForge Studio for SQL Server is a multifunctional tool that allows you to move your work with databases to a higher level of performance. With the tool, you can complete database-related tasks in a fast way: design databases, write SQL code, compare databases, synchronize schemas and data, create data reports and pivot tables, implement database CI/CD, and much more.

dbForge SQL Complete

dbForge SQL Complete is an add-in for Visual Studio and SSMS with powerful autocompletion and code formatting capabilities. The tool can come in handy when you need to significantly increase your coding speed and productivity, and reduce the number of random syntax errors.

dbForge Schema Compare for SQL Server

dbForge Schema Compare for SQL Server is a database comparison tool that provides the possibility to work with live SQL Server databases, snapshots, script folders, and native backups. The tool allows you to compare and synchronize SQL databases and generate interactive HTML reports based on the comparison result. Thus, you can analyze differences and perform schema synchronization without errors using generated SQL scripts.

dbForge Data Compare for SQL Server

dbForge Data Compare for SQL Server is a tool for comparing and synchronizing data of Azure SQL databases. The top feature of the tool is the capability to compare custom query results. Also, dbForge Data Compare for SQL Server can save your time and effort by automating data comparison and synchronization tasks.

dbForge Query Builder for SQL Server

dbForge Query Builder for SQL Server is designed to simplify the development of different types of SQL queries. It is the right tool for those who want to create SQL queries of any complexity with the least effort. You can build database queries, add and edit tables, analyze and process a lot of data via a neat and intuitive UI.

dbForge Data Generator for SQL Server

dbForge Data Generator for SQL Server is an indispensable tool when you need to populate databases with any amount of test table data. The tool includes 200+ predefined data generators with sensible configuration options that allow you to emulate column-intelligent random data. Also, Data Generator produces demo data for databases already filled with data and creates your own custom test data generators.

Conclusion

Nowadays, the development sphere has opened up new opportunities and prospects for us. We can choose what tools to work with, what platforms to use for applications’ deployments, and what cloud provider to entrust our infrastructure to.

Devart monitors modern software market trends and delivers profitable database development and management solutions. The company provides a range of reliable and efficient products designed to meet specific needs for developing, managing, and deploying databases. You can try any product absolutely free by installing its 30-day trial version. Download our products and evaluate their effectiveness when working with Azure.