Microsoft Azure SQL Database Data Compression

Back in March there was an announcement that data compression was available for Microsoft Azure SQL Database (MASD). This was met with much rejoicing. You can read more about the announcement here.

Oh, wait…that’s right…Microsoft pulled the announcement back for some reason.

Three months later, and I still don’t see an announcement regarding data compression. This page lists the feature limitations for MASD and data compression is included there. But that page was last updated in February, roughly two weeks before the announcement that was pulled back.

I decided to take a few minutes today to see if compression was available inside of MASD, but just not supported at this time. Using an on-premises instance of SQL Server 2014, I created a database (named comp_test) with one table and populated that table using the SalesOrderDetail table from AdventureWorks2012. I then created a clustered index on that table, because every table in MASD needs a clustered index. Here’s the quick code:

SELECT * INTO comp_test.dbo.SalesOrderDetail
FROM AdventureWorks2012.Sales.SalesOrderDetail
GO

USE comp_test
CREATE CLUSTERED INDEX [CI_SalesOrderID] 
        ON [dbo].[SalesOrderDetail]
(       [SalesOrderID] ASC
)
GO

After that completed I needed to deploy this database to my MASD instance. I can do this quickly using SQL Server 2014 Management Studio by right-clicking on the database name, going to ‘Tasks’, and selecting the ‘Deploy Database to ‘Windows Azure SQL Database’ option as such:

Screen Shot 2014-06-09 at 1.03.41 PMThis will bring up the Deploy Database wizard. From here I entered in my MASD connection details by pressing the ‘Connect’ button:

SSMS_deploy_MASD_database_connectI entered in my connection details and clicked Connect:

Screen Shot 2014-06-09 at 1.07.58 PMOne thing to note here is that this instance of MASD already existed and was placed in the U.S. East datacenter. The nature of Azure is such that service changes and enhancements may not be rolled out everywhere at the same time. So it is always worth mentioning what datacenter you are using for your tests.

I’m going to take the default setting on the subsequent Deployment Settings screen (Web edition, 1GB size, database name of comp_test) and just click ‘Next’. This brings me to the Summary page, and I will click ‘Finish’ here. This will begin the deployment process and eventually you should see the following:

SSMS_deploy_MASD_database_successOne thing to note here is that it won’t always be this easy to deploy databases to MASD. As part of the pre-conference seminar I delivered at TechEd in 2013 I covered a handful of deployment techniques and the obstacles you may face. If your on-premises database contains features that are not supported in MASD then this deployment method will fail and it can be a tiresome experience to try to resolve the errors listed in SSMS. It’s better to use Visual Studio for MASD deployments, trust me.

At the end of the day, though, if you know that your on-premises database satisfies all the MASD requirements (such as every table needs a clustered index) then it is very easy to right-click and deploy from SSMS, and that’s the method I chose here today for this simple test with Microsoft Azure SQL Database Data Compression.

After the deployment is complete I will verify the table size using this script:

SELECT so.name,
SUM(dps.row_count) AS [Row Count],
SUM(dps.reserved_page_count) * 8.0 / 1024 AS [TableSize_MB]
FROM sys.dm_db_partition_stats dps INNER JOIN
        sys.objects so ON dps.object_id = so.object_id
WHERE so.type = 'U' -- user table       
GROUP BY so.name
ORDER BY [TableSize_MB] DESC

What I find is this:

find_table_size_in_MASDOK, my table is about 14.25 MB in size. Now I will cross my fingers, cover my bacon, and try to enable row compression:

ALTER TABLE SalesOrderDetail REBUILD PARTITION = ALL
WITH 
(DATA_COMPRESSION = ROW
)

alter_database_enable_row_compression_MASDSuccess! Well, wait. Let’s see what the first script reports back for space used now:

row_compression_savings_in_azure_sql_databaseGreat! What about page compression? I can’t imagine that row compression would work, but page would not…but since we are here let’s test anyway. I will enable page compression and then run the script again:

page_compression_savings_in_azure_sql_databaseLooks like it works, too!

So, yes, data compression is in MASD (at least in the U.S. East datacenter, that is) but it is not officially supported as of the writing of this post (June, 2014). I expect it will be fully supported by the end of the year, and would encourage you to experiment with data compression in MASD starting today. And by “experiment” I don’t mean “deploy to production immediately”. I mean to try it out, see if it works as expected, etc. One thing I’d like to have you pay attention to is your billing. I’d be interested to know any user stories where compression resulted in significant savings.

If it provides you with a benefit then you’ll be ready to flip the switch once it is fully supported.

5 thoughts on “Microsoft Azure SQL Database Data Compression”

  1. I think the ability to save money using compression reduced a little with the new tiers. You now pay per day and the size of 250GB is included with standard and 500GB is included with premium. If you can get the database size under 2GB you might save money by just using Basic, but I don’t think many people will be running production databases on Basic. Of course those are all in preview so things could shake out a little differently before they go live.

    Reply
    • Mike,

      Exactly. I was looking at the pricing calculator, trying to see what thresholds would make sense for significant savings. But you also pay for egress, and if fewer data pages are leaving the data center you could get some savings there, too.

      So that’s why I decided to just ask for examples instead of trying to calculate at what point and for what workloads would compression offer significant cost savings.

      Tom

      Reply
      • Hi.
        In west eur it works gr8.
        I wrote about it last week in my blog
        http://Www.sqlazure.co.il
        Its in hebrow but you can translate.

        I also compressed huge table reduce from 80gb to 20gb.
        The issue is the rebuild.
        I had to build new table and push it the data.
        10x

        Pini

        Reply

Leave a Comment

This site uses Akismet to reduce spam. Learn how your comment data is processed.