Increased storage limit to 16 TB for SQL Managed Instance General Purpose

[NevenaNikolic]

What is new?

For many database workloads with significant data volume, the previous limit for SQL MI GP storage size of 8 TB has been a blocker. Microsoft is constantly working on improving and increasing SQL MI service and now the storage limit for GP is doubled - you can create or upgrade your GP Instances to host up to 16 TB of data.

Are there any requirements?

There are 2 requirements for having more than 8TB of storage: the minimum number of vCores and the minimum number of files.

Minimum number of vCores

Previously, maximum storage size for SQL MI GP was 8 TB, which was available for every instance that had at least 8 vCores (Figure 1). Now, with GP 16 TB in preview, this experience changes such that the maximum storage size of 16 TB becomes an option for every instance that has at least 16 vCores (Figure 2). One more thing that is different is a notice that appears as soon as a customer moves the slider to 16 vCores (Figure 3).

Figure 1- Old Portal experience (maximum storage size of 8TB)

Figure 2- New Portal experience (maximum storage size of 16TB for every instance with minimum 16vCores)

Figure 3- New notice on the portal about the preview

Minimum number of files

General Purpose is a SQL MI architecture where compute layer (which runs the SQL Server Database Engine process and is being used for query processing & data caching) is separated from the storage layer where all data & log files (.mdf, ldf, .ndf) are placed. The data layer is implemented using Azure Storage that has built-in replication and redundancy that guarantee that the data will be preserved. For more details about the SQL MI GP architecture take a look at this post. 

Every database file is placed on a separate Azure Storage disk (one file per disk). These disks that are used in storage layer have fixed sizes (Figure 4), and SQL MI uses minimal disk size that is required to fit the database file. It is important to note that at this time, the maximum file size supported by the underlying storage is 8 TB, which means that you will need at least two files to exceed 8 TB of the storage size.

Figure 4 - File sizes

This is not expected to be a permanent requirement as we are looking forward to increasing the maximum file size in the future.

Recommended steps

If you have only one file and the data volume hit the file size limit of 8TB, you will get an error message:

Msg 1105, Level 17, State 2, Line 86
Could not allocate space for object 'dbo.test' in database 'testfilelimit' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.

Therefore, you might want to consider adding a new file once you reach 8TB of an existing file. To help you deal with this, here are a couple of useful scripts you may want to reuse:

1.      To add a new file

You may want to add a new file before hitting the file size limit of 8TB.

Code template:

ALTER DATABASE <database_name>
ADD FILE
(
    NAME = <file_name>,
    SIZE = <file_size>,
    MAXSIZE = <max_file_size>,
    FILEGROWTH = <filegrowth>
)

To set more parameters when creating the new file, take a look at how to add a file to a new database with T-SQL.

2.      To monitor the file size(s)

You may want to monitor the used file space to act before hitting the file size limit of 8TB.

Code example:

SELECT DB_NAME() AS database_name,
    name,
    type_desc,
    size/128.0/1024.0 AS reserved_size_gb,
    CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0/1024.0 AS used_space_gb
FROM sys.database_files

3.      To send  an alert using db_mail

You may want to send yourself an email if the file size is approaching 8TB. You can enable db_mail feature and send the email alerts directly from Managed Instance once you are close to resource limits. If you haven’t configured emails on Managed Instance, take a look at this post.

Here is a code example to send an alert when the file size is approaching 8TB (above 90% used):

declare @used_file_size INT;
declare @instance nvarchar(200) = @@SERVERNAME;

SELECT TOP 1
    @used_file_size = CAST(FILEPROPERTY(name, 'SpaceUsed') AS INT)/128.0/1024.0
FROM sys.database_files

if(@used_file_size > .9*8192)
begin
    declare @msg nvarchar(max) = CONCAT('You are reaching the file size limit of 8TB for instance ', @@SERVERNAME, ':
    Used file size ', @used_file_size, '.
    Consider upgrading the instance.');
    exec msdb.dbo.sp_notify_operator
     = N'AzureManagedInstance_dbmail_profile',
    @name = N'DevOps team',
    @subject = N'Azure SQL Instance - Storage limit alert',
    @body = @msg;
end

4.      To schedule a SQL Agent job for alerting

You can easily automate db_mail alerts and copy/paste the previous query into SQL Agent job that will run every 15 minutes and send the email if you are reaching the file size limit of 8TB (Figure 5).

 Figure 5 - New Job step to monitor used file size

Summary

In this article, we discussed the requirements for the increased storage limit to 16 TB for SQL Managed Instance General Purpose and recommended steps to monitor the used file sizes. Thank you for reading and enjoy using SQL Managed Instance with increased storage capacity.