Benefits and Features of Azure SQL
IT Tips & Insights: A Softensity SRE breaks down Microsoft Azure SQL managed cloud databases, from the evolution of features and capabilities to current benefits.
By Damilare Lawale, Site Reliability Engineer
Microsoft has been focusing more on cloud computing and storage in a bid to take a substantial market share in this competitive and lucrative market. To this end, it has come up with many products designed for the cloud, and one such product is Azure SQL.
What is Azure SQL?
Azure SQL Database is a relational database provided as Database-as-a-Service, which is a part of Microsoft’s Cloud Infrastructure Platform, Microsoft Azure. It is also called SQL Azure.
Azure SQL provides database-backed managed service in the cloud with a petabyte-scale infrastructure, it is based on the latest version of the Microsoft SQL Server database engine, and also receives timely and frequent updates. It is a fully managed service by Microsoft, so the user just has to provide the data and can be free of managing the infrastructure, security, servers and maintenance. The managed database services take care of scalability, backup, and high availability of the database.
The Evolution of Azure SQL
The development of Azure SQL began when Ray Ozzie, the Chief Technology Architect at Microsoft, had the idea to turn Microsoft enterprise products such as Windows and SQL into Cloud services. They first announced Windows Azure at the Professional Developers Conference (now known as the Build Conference) in 2008.
Prior to this, in 2006, Azure SQL had the project name of CloudDB. Azure SQL services and Windows Azure were launched in 2010. Following this, Azure SQL Database was released in 2012, and the development of Azure Virtual machines in 2013 was a significant development in the evolution of Azure SQL as this provided IaaS platform/capabilities.
In 2015, Microsoft launched Azure SQL Database v12, which changed the architecture underneath the covers to provide a lot more resiliency and performance for customers in the cloud.
In 2016, Microsoft added the elastic pooling feature which provided a simple, cost-effective solution for managing and scaling multiple databases.
In 2018, the feature Azure SQL Database managed instance was added because some Microsoft customers were struggling to lift their applications and databases to the cloud because the surface area provided by Azure SQL DB was not enough.
Major milestones were also met in 2019 with the addition of Azure SQL DB Hyperscale.This highly scalable storage and compute performance tier leverages the Azure architecture to scale out storage and compute resources for an Azure SQL Database as well as serverless, which is a compute tier for single databases in Azure SQL Database that automatically scales compute based on workload demand.
Also added in 2019, Azure SQL instance pools is another factor of Azure managed instance which provides a PAAS offering.
Benefits of Azure SQL
There are many benefits of Azure SQL, from reliability to advanced security. Here’s a look at some of the biggest benefits.
Advanced, Automated Management
Azure SQL Database allows users to avoid many aspects of management so they can focus on their data’s value. Built-in features include smart performance tuning based on learned usage patterns, automatic systems for improving reliability and data security, and various adaptive processes to keep hosted databases fast and available.
For businesses looking to modernize their data pipeline, Azure SQL Database managed instances provide frictionless, secure migration of apps from on-premises to the cloud, along with access to the Azure Resource Manager API for dynamic provisioning — all without the need to pay for additional hardware.
Azure SQL Database leverages all the benefits of a cloud platform: it promises virtually no downtime for databases it hosts, with a stated goal of 99.99% availability.
The high availability architecture insures data against failures and relieves users from having to think about maintenance or potential outages. This architecture is also highly resilient, allowing businesses to build robust apps using retry logic, which prevents the loss of functionality or connectivity in the event of errors.
Patches and upgrades are automatic and intelligently managed by instances, and Microsoft helps customers plan their own maintenance events, if needed.
Backed-Up and Durable
Azure SQL Database offers two distinct database backup options. Point in Time Restore (PITR) saves a dynamically-sized history of databases with configurable retention periods. In case of mistakes or failures, this option allows users to fall back to a full version of their database as far as a month in the past.
You can also set up long-term retention (LTR) policies to preserve backups for as long as 10 years, again with various options for scheduling and retention periods.
Backup storage is supported for free by Azure in intervals of 7 to 35 days. Users can pay for expanded storage if they find a need for more granular or longer-term data retention.
Integrated with Microsoft and Azure Ecosystems
Azure SQL Database integrates with other tools and platforms, including Microsoft offerings such as Azure Active Directory, the Microsoft Azure Portal, SQL Server Management Studio, Visual Studio Code, and Microsoft System Center. It can also work in tandem with open source tools like sql-cli, Cheetah, and Hadoop.
Microsoft provides REST APIs for Azure SQL Database to connect databases with a variety of web services and applications. Additionally, most features found in the Microsoft SQL Server codebase are available in Azure SQL Database.
Azure Resource Manager offers users templates to create and manage related services and resources from a single platform, including databases, servers, storage, and more.
Azure SQL Database scales both horizontally and vertically. Users can scale up instances and increase performance with a premium availability model, or scale out by adding database units or sharding, a process of horizontal partitioning that makes database management faster and easier.
Azure can automate this process for users, but also provides tools and APIs for fine-grained manual provisioning.
Azure SQL Database includes protective systems and tools to keep stored information secure and permissions well-managed, from the network level to individual table rows.
Users can customize database firewall rules to prevent unauthorized traffic or incursion threats, set multiple layers of authentication and user authorizations, configure encryption and secure keys, and dynamically mask data to protect private and sensitive information.
Azure SQL Database also employs anomaly detection to discover attempted exploitation of or harm to data. Automatic classification systems send alerts that flag data that needs to be secured or better managed, and auditing tools provide an overview of database and user events.
Flexible Pricing and Service Tiers
Customers can use a standalone database or Elastic Pools to store and manage their data.
Standalone is the typical cloud database, with overall processing, memory, and input/output usage calculated as Database Transaction Units (DTU). The DTU-based purchase model is the simplest option for users hoping to get started quickly with Azure SQL Database.
In Elastic Pools, multiple databases can share DTUs. Elastic Pools are best-suited for databases that have low activity but occasional usage spikes, so the cost savings/DTU reduction of Elastic Pools is dependent on how many of these databases a user can place within them.
Hey! I'm Vinicius Deschamps. I've been a System Administrator and Cloud Engineer for over a decade, and for the last 4 years I’ve worked as a DevOps Engineer. Most of my experience is in supporting, troubleshooting, configuring and maintaining diverse ecosystems on-premises and in the cloud. I specialize in the Microsoft stack.
Join Our Team