Azure SQL Database is a fully managed relational database service offered by Microsoft Azure, designed to provide high performance, scalability, and security for cloud applications. Based on the latest version of the SQL Server engine, Azure SQL Database allows you to manage and optimize databases without the need to physically manage the underlying infrastructure and is an ideal solution for companies looking to modernize their applications and data infrastructures in the cloud. In this article, we'll take a closer look at what it is, how it works, what options it offers for data preservation and what are the essential elements to know to navigate its rates.
In today's era dominated by data, companies face the challenge of managing huge amounts of information generated by various devices, and as companies transfer their data flows to the cloud, they experience the advantages of cloud storage and computing, such as flexible scalability, cost savings thanks to the outsourcing of internal IT infrastructures and the maximization of data availability.
Microsoft created its Azure specifically to allow organizations to take advantage of these advantages in their transition “to the clouds” and, in the field of data, Azure SQL Database is a key component of the Redmond company's cloud computing platform.
Azure SQL Database of Microsoft Azure is a reliable and scalable Database as a Service (DaaS) service, with fully automated solutions powered by artificial intelligence. The service allows you to host and use a relational SQL database in the cloud without the need to install any hardware or software.
This robust platform, designed for modern web applications, is renowned for its efficiency and is among the best database management systems. Many applications are now successfully using Azure SQL Database as part of their data strategy.
But how does it work? What are its characteristics? Let's see it together in the next sections.
Azure SQL Database is a cloud-native SQL database accessible to individuals and businesses through a Platform as a Service (PaaS) model, backed by Microsoft's robust contractual SLAs and with an impressive 99.99% availability.
Unlike a typical self-managed SQL database, which requires configuration, management, security, and a series of other processes to ensure that the database remains in an adequate state of health, the service automatically manages all critical database management functions, such as updates, pre-configurations, patching, management of log files and truncations, without the intervention of the end user. IT teams can then focus on critical business operations, rather than spending time and resources managing and monitoring a managed SQL database.
Azure SQL Database, when it comes to the allocation of storage, behaves very differently than an on-premises database or even a self-managed cloud native SQL database. Through the service, it is possible to create an elastic and scalable storage layer, highly available, consumed as needed and that maintains the same high level of performance as any self-managed SQL database.
SQL Database offers automatic updates, patching, and automated backups without the need for user intervention or configuration. By default, it uses the latest stable version of SQL, however, for users who want to take advantage of SQL's rich configuration capabilities, these are still available. Services such as Alter Database (Transact-SQL) allow users to modify certain database configuration options.
In addition, users don't have to worry about over-allocating SQL resources or dealing with complex capacity planning exercises. Rather, SQL storage is dynamically allocated based on the database storage needs for an application. Therefore, for those looking for a simple, cost-optimized approach to SQL, Azure SQL Database can be an incredibly affordable option.
There are four levels in the Azure SQL architecture:
If you choose to use the Azure platform for SQL databases, there are two options available: single database and Elastic Pool. Both have their peculiarities and we will see them better in the following sections.
A single database is the equivalent of an SQL Server database, but hosted in the cloud. This is isolated from other databases and is managed through a server. When resources are assigned to each individual database, they belong only to the specific database to which they were assigned and are not shared with others, regardless of service level.
The single database distribution model is a solution for a cloud application that requires a single source of data and it is also possible to increase or decrease the resources allocated to the individual database.
The ability to manage resources is measured in DTU (acronym for Database Transaction Units) for individual databases. DTUs represent a combination of computing power, memory, and I/O (Input/Output) needed to perform operations on a database.
This deployment model refers to multiple databases with shared resources, managed together through a logical server. You can move a single database to this elastic pool or remove it when needed.
If you don't know how many resources each specific database will consume and how many should be allocated beforehand, the work can become challenging. This model allows databases to dynamically draw on the resources available in the pool as needed, adapting to load peaks without having to oversize each individual database.
So, when a single database requires unique and unpredictable resources, the elastic pool assigns the necessary resources to the target database.
The ability to manage resources in the case of elastic pools is measured in EdTu (elastic Database Transaction Units). Unlike DTUs, which apply to a single database and represent dedicated resources such as computing power, memory, and I/O, EDTUs represent aggregated resources shared between multiple databases within a pool.
The pool receives a defined number of eDTUs for a fixed price. Therefore, the user pays for the Azure SQL Elastic Pool as a single unit, not for each separate database. Within the pool, a single database can consume more EDTUs, taking them from the total number available, if the load increases. When the load is low or absent, eDTUs are not consumed.
More EDTUs can be added to the elastic pool. Conversely, it is possible to remove the extra EDTUs from the pool if the databases do not consume them and it is possible to do so at any time, without causing negative impacts or downtime.
The elastic pool specifies storage space in GB, and this can be shared across all databases. However, exceeding this storage limit is not allowed. If your databases grow too large and their aggregate size exceeds the storage space of the elastic pool, all databases become read-only.
We have created the Infrastructure & Security team, focused on the Azure cloud, to better respond to the needs of our customers who involve us in technical and strategic decisions. In addition to configuring and managing the tenant, we also take care of:
With Dev4Side, you have a reliable partner that supports you across the entire Microsoft application ecosystem.
Databases are more stable and reliable than in the past, but there is still the possibility of losing data due to a bug in the database engine. Another concern is the reliability of the infrastructure that is being used to host the database. There are several solutions for backing up SQL databases on Microsoft Azure that users can resort to and the backups are managed internally by the cloud platform for their PaaS database products such as, in fact, SQL Database.
The service supports 3 types of backups, similar to those of SQL Server, to achieve a restore to a specific point in time:
The full backup is a full copy of your database, which includes everything in the database and in the transaction log file. The differential backup captures only the changes made since the last full backup, while the transaction log backup captures the records from the transaction log file. The latter is also used to restore to a specific point in time. The transaction log backup captures the log details in an incremental format, so if you lose a backup of the previous log, you won't be able to recover the database beyond that point.
Automatic backups for these databases are performed with a weekly full backup, a differential backup every 12 or 24 hours, and a transaction log backup every 5 or 10 minutes. You don't need to configure anything for this, as Azure does it automatically every time you deploy an SQL database. After this first full backup, differential and transaction log backups will start running in the background.
The system will decide when to perform each type of backup and their intervals based on the workload on the database. These backups can be used to restore to a specific point in time, to restore databases to another Azure location or region, or to restore a database from a very old backup kept under a long-term retention policy.
When you store data in Microsoft Azure, regardless of the type of storage, it is stored somewhere in Microsoft's data centers and, to ensure its availability and preservation, Azure creates and stores copies of the data in multiple locations with the goal of providing redundancy to protect your information from hardware failures, power or network interruptions.
Backup storage redundancy is also supported for automatic backups, with three options available. From a cost or pricing perspective, each option is priced differently, so costs will be affected by changes to the type of backup storage redundancy. The three types available are:
The GRS is the default configuration. Create multiple copies of your backup files in associated regions to ensure that the backup is secure and always available, very useful in case the primary region becomes inaccessible or if you want to restore your database to another region. It can also be one of the most cost-effective disaster recovery solutions for databases.
The LRS option keeps all backup copies locally in the same data center, while the ZRS keeps the backup files in different availability zones in the same region. These two options are suitable if you want to keep backup copies close to your primary site.
Azure SQL databases support two types of backup retention policies:
The former is used to manage restores to a specific point in time, while the second is used to manage restores from long-term or older backups, for auditing and compliance purposes. We may also keep these backup files as part of the short-term retention policy for a period of between 7 and 35 days. The default backup retention is 7 days, but it may vary depending on the level of service.
The long-term retention policy is applicable if you want to keep your backup files for a longer period and maintain a long-term retention period. With the application of this policy, the service performs a weekly backup and saves copies of the backups in Azure Blob storage for a maximum of 10 years. All backups are encrypted, both when they are kept in storage and during transit, in order to ensure maximum security.
It is now practically a fact that the enormous amount of services provided by Azure can cause confusion among users who want to approach Microsoft's cloud computing platform.
Even in this case, users less familiar with Azure environments may have a few moments of confusion, so we'll take a moment to talk about the difference between SQL Database and another Azure service dedicated to databases: Managed Instance.
Azure Managed Instance and Azure SQL are both solutions designed to provide a scalable and secure database platform but with some key differences. Managed Instances is, like SQL Database, a PaaS service, but it is designed to offer higher compatibility with SQL Server, Microsoft's old on-premise solution for managing relational databases.
The service provides a database environment similar to the on-premises one, with the advantage of automatic management of resources and maintenance operations and the ability to install custom applications and configure advanced security features, while maintaining greater compatibility with advanced SQL Server features such as database queries and CLR integration.
SQL Database, on the other hand, is designed for modern applications and workloads that do not require all the advanced functionality of SQL Server and, as we have already seen, provides automatic management of resources and maintenance, including updates, patches and backups. All of these features make it ideal for new projects or for applications that do not require advanced configuration or full compatibility with SQL Server.
With Azure SQL, companies have limited control over the database instance, since the service is, for better or for worse, fully managed by Microsoft. Companies therefore have limited access to the underlying operating system and cannot install custom applications or configure advanced security features, unlike Managed Instance.
Another difference between the two is the level of compatibility with on-premises SQL Server workloads. Managed Instance is, as we said before, designed to be highly compatible with on-premises SQL Server workloads, making it a good option for companies that want to migrate their SQL Server workloads to the cloud. Azure SQL, on the other hand, has some limitations in terms of compatibility with on-premises SQL Server workloads and is generally preferred for new workloads.
Even in terms of pricing, the differences are substantial and although both Azure Managed Instance and Azure SQL offer a wide variety of options based on the type of use, the former is generally more expensive than the second, since it offers a whole series of additional features and greater control over the database instance.
So when to use one or the other? The answer, as in many of these cases, is: it depends.
Managed Instance, due to its wider range of functionalities and higher costs, is a more specialized solution, more oriented to the environments of large organizations and which it is advisable to use only if you have specific needs to control your relational databases or if you want to migrate your on-premise databases from SQL Server to the Cloud. SQL Database, by virtue of its ease of use and speed at the cost of more limited functionality, is instead a better choice for new applications or scenarios with simpler requirements, suitable for any type of business.
In the context of Azure SQL Database, pricing may seem complex, but it's structured to offer flexibility and adapt to different workload and scalability needs. Below, we'll explore the main pricing options and models.
First, let's look at purchasing models, which determine how costs for computing and storage resources are allocated and billed. The two main models are vCore (Virtual Core) and DTU (Database Transaction Unit), each with specific characteristics that adapt to different needs.
Service Tiers determine the level of performance, scalability and availability of the database and allow you to optimize resources and costs based on the specific characteristics of the workloads.
These options determine how the costs for computing and storage resources are calculated, and include different purchasing and billing models that adapt to the specific needs of the workloads.
In addition to the base costs, there are additional options that may affect the final price. Backup and restore operations are generally included, but there may be additional costs for long-term or external geolocation backups, and high availability and resilience options, such as geographic replicas and availability zones, may involve extra expenses. Additional services such as advanced monitoring and security features may also have additional costs.
For a complete evaluation and to choose the additional options that best suit your needs, please refer to the page of Azure pricing calculator, where you can obtain more detailed information on the prices and features available and make a first estimate of the service costs for your organization.
With Microsoft's continuous investment in its Azure platform, the Redmond company's cloud computing solution offering is now one of the leading reference points for all those organizations that wish to exploit the potential of 'clouds' for their digital infrastructures.
And among the more than 200 services made available by the platform, Azure SQL Database proves to be not only one of the best database solutions offered by Azure but also as one of the best options for managing relational databases in the cloud currently on the market.
With its ability to adapt to the varying needs of companies and its integration with the rest of Azure services, SQL Database offers effective tools to optimize performance in the administration of its databases and ensure the security of the information contained within them, while reducing management and maintenance costs and greatly simplifying the use even for all those users less accustomed to technical complexities.
In essence, a solid offer that can make a difference in today's landscape, where the correct management of your data is as important as the correct management of your capital. But why not try it and see if it's the right choice for your needs?
Azure SQL Database is a fully managed relational database service provided by Microsoft Azure. It supports SQL Server and offers built-in features such as scalability, high availability, and security.
Azure SQL Database ensures high availability through built-in automatic backups, geo-replication, and failover mechanisms. This ensures that your database remains accessible even in the event of a hardware or network failure.
Azure SQL Database offers several performance tiers, including Basic, Standard, and Premium. Each tier provides different levels of performance, storage, and availability to meet various application requirements.
Azure SQL Database offers robust security features, including encryption at rest and in transit, advanced threat protection, and vulnerability assessments. It also supports compliance with various industry standards.
Yes, Azure SQL Database allows for seamless scaling both vertically (increasing resources) and horizontally (adding more databases) to handle increased workloads. This flexibility ensures that the database can grow with your application's demands.
In Azure SQL Database, a single database is an isolated database optimized for workloads with stable performance needs. An elastic pool, on the other hand, allows multiple databases to share resources, making it cost-effective for managing variable workloads.
Azure SQL Database automatically performs full, differential, and transaction log backups without user intervention. These backups are stored securely, and you can restore your database to any point within the retention period.
Azure SQL Database provides several monitoring tools, including Azure Monitor, SQL Analytics, and Query Performance Insights. These tools help you track performance, detect issues, and optimize database operations.
Yes, Azure SQL Database supports migration from on-premises SQL Server databases using tools like Azure Database Migration Service and SQL Server Management Studio. These tools help ensure a smooth transition with minimal downtime.
Azure SQL Database integrates seamlessly with other Azure services like Azure Functions, Azure Logic Apps, and Power BI. This integration enables the development of comprehensive cloud-based solutions that leverage multiple Azure offerings.
The Infra & Security team focuses on the management and evolution of our customers' Microsoft Azure tenants. Besides configuring and managing these tenants, the team is responsible for creating application deployments through DevOps pipelines. It also monitors and manages all security aspects of the tenants and supports Security Operations Centers (SOC).
