Azure SQL Elastic Pool – Database SizeSolution ·
I mentioned in a past article, regarding database sizes within an elastic pool:
“No policies limit an individual database to take more storage although a database maximum size can be set on a per-database basis.”
I’m going to focus on that in this article.
An Azure SQL Database resource has a MaxSizeInBytes property. We can set it either in an ARM template (see this ARM template and the property maxSizeBytes) or in PowerShell.
An interesting aspect of that property is that:
- It takes only specific values
- Not all values are permitted, depending on the elastic pool edition (i.e. Basic, Standard or Premium)
One way to find the valid values is to navigate to the ARM schema. That documented schema likely is slightly out of date since, as of December 2016, the largest value is 500GB, which isn’t the largest possible database size (1 TB for a P15).
The online documentation of Set-AzureRmSqlDatabase isn’t fairing much better as the documentation for the MaxSizeBytes parameter refers to a parameter MaxSizeGB to know about the acceptable values. Problem is, MaxSizeGB parameter doesn’t exist.
But let’s start with the documented schema as it probably only lacks the most recent DB sizes.
Using that schema list of possible values and comparing that with the stand alone database size for given editions, we can conclude (after testing with ARM templates of course), that a Basic pool can have databases up to 2GB, for Standard we have 250GB and of course Premium can take all values.
It is important to notice that the pool can have larger storage. For instance, even the smallest basic pool, with 50 eDTUs, can have a maximum storage of 5 GB. But each DB within that pool can only grow up to 2 GB.
That gives us the following landscape:
|Maximum Size (in bytes)||Maximum Size (in GB)||Available for (edition)|
|104857600||0.1||Premium, Standard, Basic|
|524288000||0.5||Premium, Standard, Basic|
|1073741824||1||Premium, Standard, Basic|
|2147483648||2||Premium, Standard, Basic|
We can now use this maximum database as a storage policy, i.e. a way to make sure a single database doesn’t take all the storage available in a pool.
Now, this isn’t as trivially useful as the eDTUs min / max we’ve seen in a pool. In the eDTU case, that was controlling how much compute was given to a database at all time. In the case of a database maximum size, once the database reaches that size, it becomes read only. That will likely break our applications running on top of it unless we planned for it.
A better approach would be to monitor the different databases and react to size changes, by moving the database to other pool for instance.
The maximum size could be a safeguard though. For instance, let’s imagine we want each database in a pool to stay below 50 GB and we’ll monitor for that and raise alerts in case that threshold is reached (see Azure Monitor for monitoring and alerts). Now we might still put a maximum size for the databases of 100 GB. This would act as a safeguard: if we do not do anything about a database outgrowing its target 50GB, it won’t be able to grow indefinitely, which could top the pool maximum size and make the entire pool read only, affecting ALL the databases in the pool.
In that sense the maximum size still act as a resource governor, preventing noisy neighbour effect.
We can’t change a database maximum size in the portal (as of December 2016).
Using ARM template, it is easy to change the parameter. Here, let’s simply show how we would change it for an existing database.
Building on the example we gave in a previous article, we can easily grab the Pool-A-Db0 database in resource group DBs and server pooldemoserver:
Get-AzureRmSqlDatabase -ServerName pooldemoserver -ResourceGroupName DBs -DatabaseName Pool-A-Db0
We can see the size is the one that was specified in the ARM template (ARM parameter DB Max Size default value), i.e. 10 GB. We can bump it to 50 GB, i.e. 53687091200 bytes:
Set-AzureRmSqlDatabase -ServerName pooldemoserver -ResourceGroupName DBs -DatabaseName Pool-A-Db0 -MaxSizeBytes 53687091200
We can confirm the change in the portal by looking at the properties.
If the MaxSizeByte property is omitted, either in an ARM Template or a new-AzureRmSqlDatabase PowerShell cmdlet, the default behaviour is for the database to have the maximum capacity (e.g. for Standard, 250 GB).
After creation, we can’t set the property value to null to obtain the same effect. Omitting the parameter simply keep to previously set value.
We’ve looked at the maximum size property of a database.
It can be used to control the growth of a database inside a pool and prevent a database growth to affect others.