0

In the Azure SQL DB, running in the Hyperscale service tier, are table level partitions supported? It does not seem to be called out in the known limitations page.

While trying to create a filegroup in an Azure SQL Hyperscale Database via SSMS, which is the initial step towards creating a partition scheme, it threw an error:

Keyword or statement option 'filegroup' is not supported in this version of SQL Server.

So, either it is not feasible to do partitions or there must be some other way to create them.

  • Can someone clarify on whether partitions are supported?
  • If so, how to create them for tables?
  • How are the partition switch / new partition creation handled?
Improve this question
3
  • 1
    It sounds like it's applicable to Azure SQL databases in the partition documentation. Could you provide the T-SQL (or a screenshot if you're purely using the UI) to your post?
    – J.D.
    Commented Mar 11, 2022 at 12:51
  • This is the t-sql attempted via SSMS UI, ALTER DATABASE testDB1 ADD FILEGROUP testfg1;
    – Anand Sowmithiran
    Commented Mar 11, 2022 at 12:52
  • Are you looking for this? Slightly different link than J.D. posted. It looks like it is supported, but only on the primary file group.
    – Brendan McCaffrey
    Commented Mar 11, 2022 at 18:00

1 Answer 1

Reset to default
3

Partitioning is totally supported in Azure SQL Database, including the Hyperscale service tier.

However, adding additional filegroups is not supported in Azure SQL Database.

If you look at the docs for Files & Filegroups, the "Applies to" section says "SQL Server (all supported versions)" which actually refers to ONLY the traditional self-installed "running on a Server" flavor of SQL Server, and not Azure SQL Database. Adding additional filegroups to Azure SQL Database doesn't work.

There are a number of features/syntaxes that don't work in Azure SQL Database, which all throw Error 40517:

Keyword or statement option '%.*ls' is not supported in this version of SQL Server.

In the case of adding a filegroup, %.*ls gets replaced with filegroup when the error is thrown:

Msg 40517, Level 16, State 1, Line 4
Keyword or statement option 'filegroup' is not supported in this version of SQL Server.

For various data management reasons (spreading out IO, slower storage or archive data, piecemeal restores, etc), you may want to have separate filegroups for separate partitions. However, this isn't a requirement to use partitioning, and you can just as easily set up partitioning with everything in the PRIMARY filegroup--this ends up being your only option in Azure SQL Database.

You can just skip the step of creating new filegroups and proceed with configuring partitioning by creating partition schemes & functions, then applying those to a table.

Improve this answer
4
  • That clarifies well. But if all partitions are forced to store data in PRIMARY group, then much of the IO parallelism benefits are lost, isn't it. Are the partition range extension/maintenance done using same methods?
    – Anand Sowmithiran
    Commented Mar 12, 2022 at 10:46
  • 3
    @AnandSowmithiran, in my experience, partitioning on different filegroups is often done without merit since physical storage is usually abstracted anyway. Azure SQL Hyperscale takes the abstraction to yet another level and manages the ugliness so I suggest you don't try to micromanage IO in the PaaS world. The SQL Database engine will still parallelize and leverage partitioning as appropriate regardless of the filegroups.
    – Dan Guzman
    Commented Mar 12, 2022 at 12:44
  • SQL Hyperscale totally different architecture when compared to the other 2 Azure SQL variants. I am curious how effective partitions are(if at all) on those, if single filegroup is only allowed. Are there any perf benchmark or articles that you can point to about partitions in azure SQL (elastic pool, hyperscale and Managed instance)?
    – Anand Sowmithiran
    Commented Mar 13, 2022 at 7:10
  • 1
    The primary benefit for using partitions on Azure SQL DB (any tier including hyperscale) is that you can do bulk management of data (such as the sliding window scenario to age out old data without having to delete it row-by-row). Partitioned tables were created in the days of spinning disks and storage systems are mostly abstracted away in most modern systems, including Azure SQL DB.
    – Conor Cunningham MSFT
    Commented Apr 18, 2022 at 17:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.