SQL Server 2008 R2 Compression on SAP - so what's new?

[Former Member]

I've got to say that since writing by blog last night, SQL Server 2008 Compression on SAP - how to cut your database size from 90Gb to 30Gb, I have been feeling very dissatisfied. In the process of writing the article I learnt lots about what I thought was called Microsoft SQL Server 2010 and which is actually called Microsoft SQL Server 2008 R2. From what i can gather this is because it doesn't have enough benefits in it to consitute a 2010 release - in the vein of Windows 2003 R2.

So what's in it from a SAP perspective?

• UCS2 compression - reduced disk storage for Unicode data types. Microsoft are talking about 15-20% reductions.
• Reduced deadlocks via a new hashkey algorithm,
• Star Join Query Optimization (should help on BW)
• Partitioned Table Parallelization

There's a lot in there for non-SAP customers and it seems we mostly just get compression and performance improvements, which I will gladly take. It's not a free upgrade from 2008 to 2008 R2 but of course if you license MSSQL through SAP and have a support agreement, you are likely to have product upgrades included in your arrangement.

So, I had a word with our IT team and they found me a copy of SQL 2008 R2, which I hear should be released shortly and supported by SAP soon afterwards. I did everything you're going to read on a non-productive environment but we'll be going live with it as soon as it's released - IT are chomping at the bit to liberate all the disk space that I talked about in my last article!!!

The Upgrade.

Obviously there's no documentation on this yet, but I won't let this stop me. Men don't read manuals anyhow... the upgrade guide for 2008 is available at http://service.sap.com/instguides --> Database Upgrades --> MS SQL Server.

From what I can see, having the latest DB and Kernel drivers is important. So I updated those from SAP Service Marketplace - both kernel files, and the lib_dbsl file which has the latest MSSQL database drivers. I don't know if this is necessary but it sounds sensible, because the kernel I had, was released prior to any release of SQL 2008 R2. I'm thinking Microsoft probably did some updates to the database libraries, though I can't find any references in the release notes.

Past this the DB guys went through the upgrade. It's wizard based and didn't seem to cause any challenges. Looking through the upgrade guide, there's nothing more that seems relevant provided you are upgrading from 2008 and not 2000/2005. If you are on those versions then read the upgrade guide in detail, there's important things to do!

Compression.

Now, if you have already compressed with SQL 2008, you will need to download and run the latest version of sp_use_db_compression from Microsoft. This is because the script isn't setup to recompress - this latest version brings @force_rebuild_same, which lets you do that.

http://blogs.msdn.com/saponsqlserver/archive/2010/03/17/updated-version-of-sp-use-db-compression-ver... 

Then you can go back into program RSDD_MSSQL_CUBEANALYZE in SE38 and set up your parameters again. Settings -> Export Mode On/Off enables the Page Compression, tick that and (optionally and unsupported!) tick Index Compresion.

Click "Start Checks" to check the table contents before compressing. In DB02 at this point, my database size is 28,423Mb and I'm hoping we will save a bit more. The database checks are finished (keep pressing refresh until CheckJobActiv goes back to No) and I'm ready to compress. Select all the rows (click in the middle and select Ctrl-A) and then click "Compress Selected Table(s)".

I left it running overnight and I'm shocked. SAP now takes up 23,436Mb, which is nothing short of incredible. Performance appears not to have taken a further hit.

Conclusion

In this world of having to do less with more, Microsoft appear to have led the way by reducing the amount of aggregate space you need to run SAP, with only a reasonable increase in CPU (most SAP systems I see have plenty of CPU headroom anyhow).

This isn't great news for the storage vendors obviously, but for the rest of us, this kind of compression is a no brainer.