Microsoft Azure SQL is an intelligent, relational database server which allows you to migrate your SQL server databases without changing your apps.
As well as Azure SQL Database, there are currently three further deployment options which include elastic pools, Azure managed instances and Azure virtual machines.
So before we dive into this, let’s define the different deployment options.
Azure SQL Database is a single database that is associated to a server whereas an elastic pool is a collection of databases that are grouped together to share the same resources. And an Azure Managed Instance is the latest Azure SQL offering that can have a collection of databases and is equivalent to an on-premises SQL Server that provides server level access and the ability to manage the SQL Agent and Jobs.
Still with me?
Several features are available within an on-premises SQL Server deployment including cross-database joins and SQL Jobs, but neither are available within the PaaS offerings. As part of migrating solutions to the cloud, it would be advisable to consider alternative more modern solutions to these problems or you could choose the Azure Virtual Machine deployment option which would be better suited.
So now down to the all-important pricing. Pricing is based on a Service Tier and performance level that is specified when creating an Azure SQL database or elastic Pool. The performance level is specified in a measure of DTUs (Database Transaction Units). Azure Managed Instances use a different pricing model called virtual cores or vCores (a VCore is basically the logical CPU with the option to choose the type of hardware).
Elastic Pools are typically used over an Azure SQL Database when you need to manage and scale multiple databases with varying usage demands and they allow you to share compute resources more efficiently than having each database separate.
Say for instance, you have a Sage deployment with a separate database for each Sage Company, but not all resources are utilised at the same time. Having separate Azure SQL Databases would mean that you are paying unnecessary costs for resources that are underutilised most of the time. However, elastic pools allow you to share these resources across multiple databases, therefore providing a more cost-effective solution.
A Managed Instance is the closest option for feature parity with an on-premises SQL Server deployment. Managed Instances still benefit from the PaaS capabilities such as automatic patching, version updates, backup, high availability and a reduction in management overhead and ownership costs.
It’s important to note that when running SQL on an Azure Virtual machine, you will be responsible for upgrades, patches, backups, high availability and disaster recovery meaning you won’t benefit from the management overhead and financial efficiencies experienced using the other approaches.
However, one of the main capabilities that you will benefit from with Azure SQL over SQL Server installed on a physical server is that you don’t need to manage a server and resources allowing you to gain additional benefits from services such as Azure SQL auditing, Azure SQL advanced threat detection (ADT) and performance recommendations.
ATP can be utilised within Azure SQL to discover and classify sensitive data, mitigate potential database vulnerabilities and detect anomalous activities that could indicate a threat to your database.
As part of ATP, you will be able to access the SQL Vulnerability Assessment which is a scanning service enabling you to check for security vulnerabilities and highlight deviations from best practices. Assessments can be scheduled, the output report visable in the Azure Portal or downloadable where recommendations can then be implemented.
I may have thrown a lot of info at you there but in summary, whether you are developing new cloud native apps that have a requirement to store relational and transaction data, or if you are migrating an existing on-premises SQL Server, Azure SQL provides various options to meet these requirements. This will then allow you to experience reductions in management overheads, financial efficiencies and benefit from a whole host of new features.
I’ve summarised the key features and benefits below so be sure to check them out:
Key features and benefits of Azure SQL
- Scalable, cost-effective service plans
- Familiar management tools such as SQL Server Management Studio, SQL Server Data Tools in Visual Studio and the Azure Portal
- Migration tools to migrate on-premise databases to the cloud
- Inbuilt auditing for compliance and security
- Automatic backups, tuning and query processing
- Performance recommendations
- High availability
Look out for my next blog around application insights to find out how you can continually improve performance and usability.
Posted by Tom Barrand