dcsimg
Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Tips Database Forum Rss Feed

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
SQL Scripts & Samples
Tips
» Database Forum
» Slideshows
Free Newsletters:



News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted April 20, 2020

Introduction to Azure SQL Database Instance Pools

By Marcin Policht

In one of the article, Considerations for Deploying Azure SQL Database Managed Instances, recently published on this site, basic characteristics of Azure SQL Database Managed Instance were described. Its biggest selling points include nearly full compatibility with on-premises SQL Server deployments, support for SQL Server Agent, and additional security associated with its Azure virtual network integration.

However, Azure SQL Database Managed Instance does not provide the same level of flexibility and agility as other Platform-as-a-Service SQL Server-based offerings in Azure. To address these shortcomings, Microsoft introduced Azure SQL Database instance pools.

Before this new offering is explored in more detail, it is important to note that, at the time of authoring this article, Azure SQL Database instance pools were in public preview. Effectively you should not deploy public preview versions to host your production workloads. In addition, it is likely that some of the current characteristics will change once the General Availability stage is reached, especially in regard to provisioning process and some of the more significant functional and capacity limitations.

One of the primary complaints regarding Azure SQL Database Managed Instance is its provisioning time (with 90% of operations during provisioning of the first instance within a subnet finishing within 4 hours). Another restriction that some customers find inconvenient are the minimum resource requirements for deployment of individual instances (8 vCores with Gen4 hardware and 4 vCores with Gen5 hardware). This commonly forces customers migrating from on-premises to Azure to perform potentially complex capacity planning and consolidation activities. With instance pools, these considerations are to large extent mitigated.

Azure SQL Database instance pools are essentially groupings of SQL Server instances running within the same virtual cluster. The size of the virtual machines within the cluster determines the maximum amount of combined resources available to individual instances within the same pool. The amount of resources allocated to all instances cannot exceed that maximum. You have the option of assigning between 2 and 80 vCores to a single instance, however, at this point, you must use Gen5 hardware (Gen4 is not supported). The amount of memory can range, in general, between 32 GB and 8 TB, although these limits are to some extent dependent on the number of vCores assigned to an instance. In particular, the amount of memory of 2 vCore instances can range between 32 GB and 640 GB, and for 4 vCore instances between 32 GB and 2 TB. The total amount of storage available for the entire pool is 8 TB, which can be divided across up to 100 instances. From the resource allocation standpoint, it is also worth noting that each pool uses the total of 9 IP private addresses within the subnet you designate for its deployment, regardless of the number of instances in the pool. This is particularly beneficial in environments with limited IP address space.

Isolation between instances within the same pool is implemented by using Windows Job Objects. This ensures proper allocation of the compute and memory resources. However, it is important to point out that, in some scenarios, you might encounter interference between instances due to the fact that they all reside on the same virtual machine, with shared local disk and network resources. That resource sharing might also warrant additional consideration when using security sensitive features such as CLR integration. In such cases, you should consider either disabling these features or deploying your databases into separate managed instances. There are additional consideration resulting from the shared model used by instance pools. In particular, all instances share the same service tier (during preview, only General-Purpose service tier is available). However, each instance has its own, distinct configuration of collation, time zone, public endpoint for the data plane, and failover groups, as well its own instance of SQL Server Agent.

Regarding provisioning, the time it takes to deploy a pool is equivalent to that of an Azure SQL Database Managed Instance, however, once the pool is deployed, you can implement individual instances within a few minutes. During preview, provisioning experience is not available from the Azure portal but requires Azure PowerShell (you can use the Azure portal once managed instances are part of a pool). Note also that the license model (allowing you to take advantage of Azure Hybrid Benefit or default to the license included option) for the pool can only be set at the provisioning time. Finally, keep in mind that, at this point, license pools cannot be scaled post-deployment and that individual instances cannot be moved between pools or transitioned to a managed instance, so proper planning is important.

This concludes our overview of Azure SQL Database Instance Pools. This topic will be revisited once the offering reaches General Availability, at which point it is likely that a number of the limitations we mentioned earlier will be either minimized or completely eliminated.

# # #

See All Articles by Marcin Policht



MS SQL Archives




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM