Azure SQL - Hyperscale service tier - are table partitions supported



  • 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 https://docs.microsoft.com/en-us/azure/azure-sql/database/service-tier-hyperscale 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?


  • https://docs.microsoft.com/en-us/sql/relational-databases/partitions/partitioned-tables-and-indexes?view=sql-server-ver15 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 https://docs.microsoft.com/en-us/sql/relational-databases/databases/database-files-and-filegroups?view=sql-server-ver15 , 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.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2