Skip to main content

Flavors of SQL inside Azure (Azure SQL)

Just realized a few days ago that there are so many flavors of SQL inside Microsoft Azure that you need to be careful when you decide which one to use. In this post, I want to present the most important flavors of SQL that you can have inside Azure and to define a demarcation line between them.

1.0 SQL Server inside Azure VMs (IaaS)
The classical IaaS approach where customer needs to manage the VMs and the SQL Server instance that is running on top of Azure. The list of tasks that you need to do when you run your SQL instances on top of Azure VMs are the same as you would have the SQL Server instances inside your own data-centers. From license, to backup and restoration procedures, all of this needs to be managed by yourself.

2.0 SQL Database (PaaS)
The current offer of Azure related to SQL on the PaaS environment is more complex than you would expect. In this moment there are 3 options available, that cover most of the uses cases that you might have.
  • SQL Database (Singleton)
  • Elastic Pool
  • Managed Instance
A short overview on each of one can be found below.

2.1 Azure SQL Database (Singleton)
It’s part of SaaS category from industry perspective, but it falls in both categories SaaS and PaaS. It’s fully hosted and managed by Azure, client is responsible only for the content that is pushed inside the storage. All the maintenance and complex procedures to have a high SLA and good backup and restore procedures and fully managed by the platform.
You just specified the tier and the DB size and from that moment, you don’t care about anything else. You pay based on your use and you can scale up or down dynamically.
There is no management of infrastructure cost, being out of the box fault tolerance. Features like Point-In-Time restoration, geo-restoration and replication are included. It’s a good option when you start from scratch and you want to focus on your business value.
Each instance of database has its own virtual resources. It is important to remember that even if each tier has a specific number of resources reserved this does not mean that physical servers are dedicated to your own SQL database instance. Because of this, you cannot share available resources between different SQL database instances. In comparison with this, Elastic Pool allows us to share resources between database instances.

2.2 Elastic Pool
In comparison with Azure SQL Databases, elastic pool is a little bit different. Even if you still create SQL Database instances, you put all of them in the same pool, where all resources are shared. These means that all your DB instances that are part of the same Elastic pool are using and sharing the same CPU, IO and memory.
These is a model that works great if you have multiple DBs, where the pick of each instance is in different time periods. When you are using Elastic Pool don’t forget that all the DB instances are having the same tier and performance of database instances can be affected by the other databases that are in the same pool.
I compare Elastic Pool with farm with steroids. You can reserve computation power that it is shared between database instances, but without caring about backup, pooling and other stuff that are coming for free.
Just do not forget that things like SQL Agents, SSIS and Service brokers are not available inside Elastic Pool.

2.3 Managed Instances
This new service is part of Azure SQL family. In comparison with Elastic Pool you have all the features that you have on-premises (like SQL Agents, SQL CLR, cross-database querying), but in the same time you can use the full power of Azure SQL offering you out of the box support for automatic backups, high-availability and many more.
An interesting thing of Managed Instance is how you can configure it. You can put it inside a VNET, using his own private IP, without direct access to internet. It’s like having an Azure SQL Database with all the features inside a private network.
In comparison with Azure SQL Database, the DB size can reach 35TB without having to do any custom configuration. It is the right place when you start a migration from on-premises to Azure.

Conclusion
In conclusion I would like to share with you a comparison between SQL Server running on Azure VMs and SQL Database offered as PaaS (Azure SQL Database Singleton)

Comments

Popular posts from this blog

Windows Docker Containers can make WIN32 API calls, use COM and ASP.NET WebForms

After the last post , I received two interesting questions related to Docker and Windows. People were interested if we do Win32 API calls from a Docker container and if there is support for COM. WIN32 Support To test calls to WIN32 API, let’s try to populate SYSTEM_INFO class. [StructLayout(LayoutKind.Sequential)] public struct SYSTEM_INFO { public uint dwOemId; public uint dwPageSize; public uint lpMinimumApplicationAddress; public uint lpMaximumApplicationAddress; public uint dwActiveProcessorMask; public uint dwNumberOfProcessors; public uint dwProcessorType; public uint dwAllocationGranularity; public uint dwProcessorLevel; public uint dwProcessorRevision; } ... [DllImport("kernel32")] static extern void GetSystemInfo(ref SYSTEM_INFO pSI); ... SYSTEM_INFO pSI = new SYSTEM_INFO(

Azure AD and AWS Cognito side-by-side

In the last few weeks, I was involved in multiple opportunities on Microsoft Azure and Amazon, where we had to analyse AWS Cognito, Azure AD and other solutions that are available on the market. I decided to consolidate in one post all features and differences that I identified for both of them that we should need to take into account. Take into account that Azure AD is an identity and access management services well integrated with Microsoft stack. In comparison, AWS Cognito is just a user sign-up, sign-in and access control and nothing more. The focus is not on the main features, is more on small things that can make a difference when you want to decide where we want to store and manage our users.  This information might be useful in the future when we need to decide where we want to keep and manage our users.  Feature Azure AD (B2C, B2C) AWS Cognito Access token lifetime Default 1h – the value is configurable 1h – cannot be modified

What to do when you hit the throughput limits of Azure Storage (Blobs)

In this post we will talk about how we can detect when we hit a throughput limit of Azure Storage and what we can do in that moment. Context If we take a look on Scalability Targets of Azure Storage ( https://azure.microsoft.com/en-us/documentation/articles/storage-scalability-targets/ ) we will observe that the limits are prety high. But, based on our business logic we can end up at this limits. If you create a system that is hitted by a high number of device, you can hit easily the total number of requests rate that can be done on a Storage Account. This limits on Azure is 20.000 IOPS (entities or messages per second) where (and this is very important) the size of the request is 1KB. Normally, if you make a load tests where 20.000 clients will hit different blobs storages from the same Azure Storage Account, this limits can be reached. How we can detect this problem? From client, we can detect that this limits was reached based on the HTTP error code that is returned by HTTP