In the previous blog posts in the SQL MI How-Tos we have already touched on the aspect of SQL MI reserved and available Disk Space, but as in everything - there is so many things to add and expand. In this post we shall focus on the General Purpose service tier and the remote disk storage that is used in this service tier. Besides the explicit limits of the addressable space that is connected to the number of CPU vCores, there are important aspects of the remote storage that will limit the number of database files that can be located there.
If you are interested in other posts on how-to discover different aspects of SQL MI - please visit the http://aka.ms/sqlmi-howto, which serves as a placeholder for the series.
General Purpose service tier uses 2 types of storages - the fast local storage that is used for the [tempdb] database with 24 GB per each of the CPU vCores, and the remote storage that is used for the user databases and which is reserved and controlled by the customer. The minimum size for each of the user data & log files internally is 128 GB, because under the hood a single managed disk is allocated for each of the user database files. The possible sizes for each of the files are 128 GB, 256 GB, 512 GB, 1024 GB, 2048 GB, 4096 GB & 8192 GB (current maximum), and each managed disk besides the size will also provide a different amount of IOPS and different throughput in MB/s.
The image below shows the current values associated with each of the managed disk:
This means that if your data or log file is 5 TB in size, then a single managed disk (P60) with storage capacity up to 8 TB will be allocated, and if you have a 1.5 TB file - then the next biggest managed disk (P40) with storage capacity up to 2 TB will be allocated. The upgrades and changes are done automatically for the customers.
The total maximum addressable space with managed disks for a General Purpose service tier remote storage is capped to 35 TB, where it will be split between the data files. This means that the maximum number of managed disks per SQL Managed Instance allocated with 128 GB (P10) would be 280, and for the user database file between 1 and 2 TB will be a 2TB managed disk (P40) that will have the total maximum of 15 user database files, mainly because of the total size is capped at 16 TB and 15 database files of over 1 TB will occupy this space if they are still have not reached 16 TB in sum.
This gets even more complicated because the total reservable space has a limit associated with the allocated number of CPU vCores, as presented on the picture below:
To make it more simple for the customers, we decided to share the following script that will provide key details on the current SQL MI reservable & addressable storage offering:
Posted at https://sl.advdat.com/3ESG21z
