none
Data compression on sql azure db

    Question

  • Hi All,

    We have set of large tables and wanted to make use of SQL Server Data Compression feature.
    Is data compression feature is available by default or else is it available on only specific service tier/edition?

    select @@version
    go
    Microsoft SQL Azure (RTM) - 12.0.2000.8 Jun 8 2018 18:18:54 Copyright (C) 2018 Microsoft Corporation

    Thanks,

    Sam
    Friday, July 13, 2018 9:41 AM

Answers


  • We have set of large tables and wanted to make use of SQL Server Data Compression feature.
    Is data compression feature is available by default or else is it available on only specific service tier/edition?

    Data compression should be available in all service tiers. Since you said large tables, I presume you are at least on standard tier and hence you should be good to go for it. 

    See the following for experiments with compression in Azure SQL database:

    https://www.mssqltips.com/sqlservertip/5407/azure-sql-database-table-partitioning-example/

    https://thomaslarock.com/2014/06/microsoft-azure-sql-database-data-compression/

    An excerpt from the above article:

    "In the past, table partitioning and data compression might not have been incorporated into your database design due to the cost of an on-premises Enterprise license.  With the Azure SQL database, both of these techniques are available for your use in any pricing tier."

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by Samantha v Monday, July 16, 2018 10:23 AM
    Monday, July 16, 2018 12:42 AM

All replies

  • Compression is enterprise feature till SQL Server 2014 but from SQl Server 2016 SP1 it is available in standard and web editions as well. AFAIK starting from SQL Server 2017 Azure SQL database will get compression feature I do not think it is there for SQL Server 2014 azure sql database

    https://docs.microsoft.com/en-us/sql/relational-databases/data-compression/data-compression?view=sql-server-2017


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP


    Friday, July 13, 2018 9:56 AM
    Moderator
  • AFAIK starting from SQL Server 2017 Azure SQL database will get compression feature I do not think it is there for SQL Server 2014 azure sql database

    There is no such thing as "SQL Server 2014 azure sql database". There is only Windows Azure SQL Database. Which typically equates to something that is >= the current version of the box product.

    I'm not up to speed about compression, but I would expect it to be available in all editions from P1 and up, and possibly also in Standard, but not in Basic. But typically you would settle for a P1 as a minumum for a production workload.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    Friday, July 13, 2018 9:27 PM
  • AFAIK starting from SQL Server 2017 Azure SQL database will get compression feature I do not think it is there for SQL Server 2014 azure sql database

    There is no such thing as "SQL Server 2014 azure sql database". There is only Windows Azure SQL Database. Which typically equates to something that is >= the current version of the box product.

    I'm not up to speed about compression, but I would expect it to be available in all editions from P1 and up, and possibly also in Standard, but not in Basic. But typically you would settle for a P1 as a minumum for a production workload.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    So Erland you mean to say there is NO Azure SQL database BTW I just added 2014 to make it bit clear, I believe what you are doing is nitpicking? PLus if you see Azure Wiki it says Windows Azure SQL database was old name now it is called Microsoft Azure SQL database

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    Sunday, July 15, 2018 6:25 AM
    Moderator
  • AFAIK starting from SQL Server 2017 Azure SQL database will get compression feature I do not think it is there for SQL Server 2014 azure sql database

    There is no such thing as "SQL Server 2014 azure sql database". There is only Windows Azure SQL Database. Which typically equates to something that is >= the current version of the box product.

    I'm not up to speed about compression, but I would expect it to be available in all editions from P1 and up, and possibly also in Standard, but not in Basic. But typically you would settle for a P1 as a minumum for a production workload.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    So Erland you mean to say there is NO Azure SQL database BTW I just added 2014 to make it bit clear, I believe what you are doing is nitpicking? PLus if you see Azure Wiki it says Windows Azure SQL database was old name now it is called Microsoft Azure SQL database

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP



    I believe the point was on the nomenclature you used

    For Azure SQL Database we dont use SQL 2017, SQL 2014 etc to denote the versions. 

    In this case versions are referred to like V11,V12 etc

    V12 is the latest version 


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, July 15, 2018 8:33 AM
  • So Erland you mean to say there is NO Azure SQL database BTW I just added 2014 to make it bit clear, I believe what you are doing is nitpicking? PLus if you seeAzure Wiki it says Windows Azure SQL database was old name now it is called Microsoft Azure SQL database

    Sorry, I have not been following the name changes.

    Anyway, as Visakh said, I wanted to point out, there are not different versions of SQL Azure (to use an even older name), but you are always on the top of the line. (With the exception for any old V11 databases that might be there, or if you are running a preview of a certain feature.) Your post made it seem like there are versions of Azure that aligns to the box product, but that is not the case.

    Sunday, July 15, 2018 9:36 AM

  • We have set of large tables and wanted to make use of SQL Server Data Compression feature.
    Is data compression feature is available by default or else is it available on only specific service tier/edition?

    Data compression should be available in all service tiers. Since you said large tables, I presume you are at least on standard tier and hence you should be good to go for it. 

    See the following for experiments with compression in Azure SQL database:

    https://www.mssqltips.com/sqlservertip/5407/azure-sql-database-table-partitioning-example/

    https://thomaslarock.com/2014/06/microsoft-azure-sql-database-data-compression/

    An excerpt from the above article:

    "In the past, table partitioning and data compression might not have been incorporated into your database design due to the cost of an on-premises Enterprise license.  With the Azure SQL database, both of these techniques are available for your use in any pricing tier."

    Hope this helps!


    Please remember to click "Mark as Answer" if my response answered your question or click "Vote as helpful" if it helped you in any way.

    • Marked as answer by Samantha v Monday, July 16, 2018 10:23 AM
    Monday, July 16, 2018 12:42 AM