Category: SQL Server

by Fran Lens 2020-01-28

☁ Discovering Instance Pools, a new Azure SQL Database resource

For more than a year we have been working and migrating clients to Managed Instance in Azure, that’s why when last September 4, 2019 Microsoft made the news of a new option for this, called Instance Pools, we wrote it to the list ” We have to prove this as soon as possible. ”

In the consultancy, time is a precious and scarce good, that is why the “as soon as possible” of my previous sentence has happened 4 months later, having said that, let’s start!

The first concept to know before talking about Instance Pools is to know what a Managed Instance is, for that I show you the post we made in the blog a few months ago, How to Install Managed Instance in Azure.

What are Instance Pools?

It is a new option for the implementation of Managed Instance in a preview version intended to migrate small SQL Server instances, Development or Test environments that would have previously required the provisioning of an individual Managed Instance with a larger number of virtual cores.

For example, we can create an Instance Pool and provision it with 8 vCore and then internally divide it into several smaller Managed Instances, for example two instances of 2 vCore and an instance of 4 vCore.

The main differences between Instance Pools and Individual Managed Instance are the following:

• Ability to create instances of 2 vCore.
• Fast implementation of Instances within the Group, up to 5 minutes.
• Minimum allocation of IP addresses, as they share the same virtual machine, the reserved IP addresses are much smaller than those reserved by an individual Managed Instance.

It is very important to note that all Instances created within an Instance Pool have the same features and compatibility levels as an Individual Managed Instance.

Current Limitations

Although at the level of features and compatibility they are equal to the individual Managed Instances, there are some limitations in terms of resources:

• Only the implementation in Gen5 Hardware is available.
• The service level (General Purpose or Business Critical) is chosen at the Instance Pool level, so all instances within the group will have the same service level.
• CPU and RAM are dedicated within the group, so the sum of vCore of our instances can never exceed the total assigned to the Instance Pool.
• It shares the same limitations at the instance level as an individual Managed Instance.
• In addition to the limits at the instance level, there are limits at the Instance Pool level:
  • Total storage size per group of 8Tb.
  • Maximum of 100 databases per group.
• You can provision Instance Pools with the following vCore number: 8, 16, 24, 32, 40, 64 and 80.
• You can provision Managed Instance within the groups with the following vCore number: 2, 4, 8, 16, 24, 32, 40, 64 and 80.
• All sizes of Managed Instance within the groups support storage between 32Gb and 8Tb with two exceptions:
  • Instances of 2 vCore have a maximum storage of 640Gb.
  • Instances of 4 vCore have a maximum storage of 2Tb.

Limitations of the preview version

In addition to these limitations, during the preview version and until the final version comes out in Azure, there are a few additional limitations:

• Only the General Purpose service level can be used.
• The escalation of Instance Pools is not allowed.
• There is no compatibility with Azure Portal so all creation must be done with PowerShell.
• You cannot move an individual Managed Instance to an Instance Pool, nor can you transform an Instance created within a group into an individual Managed Instance.
• Prices for reserved instances are not available.

Considerations and Opinion

It is important to consider that although RAM and vCore are dedicated, the local disk (for tempdb) and network resources are shared, so if several instances within the group have a high consumption, this may affect the others, in this case the solution would be to scale the group or move those instances to individual Managed Instance.

In my opinion, this new option comes to cover an existing gap when you were considering migrating a small SQL Server Instance or when you needed a small environment to perform tests.

I also hope that when the final version arrives to Azure, it will allow for a greater configuration of the internal distribution of vCore, it would be fantastic to be able to provision a Instance Pool of 16 vCore in which 12 or 14 vCore were for the production instance and the remaining for a second instance of testing or development. This extra configuration possibility would be a key differential factor when choosing between Instance Pool or individual Managed Instance.

If you want to know more about Instance Pools, the next blog post will address the creation of this one through Powershell.

Instance Pool Series:

1. Discovering Instance Pools, a new Azure SQL Database Resource
2. Deploying Instance Pools, a new Azure SQL Database Resource