JD Edwards & SQL 2008 R2 Unicode Compression

One of the most important new features introduced in SQL Server 2008 was database compression (row and page). JD Edwards Business Data databases in particular are great candidates for compression. Customers can expect anything from a 55% to 80% reduction in the size of their databases just by using row compression. Compression in SQL Server 2008 however is one of those gifts that just keeps on giving, not only does it result in much smaller databases but in the vast majority of cases it also results in improved performance. A smaller database means fewer IOs, the number of pages that needed to be written to disk is now smaller and every page read from disk now contains more data. This reduction in IO usually more than offsets the slight increase in CPU utilization (1-3%) needed to compress and decompress the data. SQL Server compressions provides another significant benefit, not only are the data pages compressed on disk but they are also compressed when they are read into memory. This means that the buffer pool is effectively doubled, and in some cases trebled, all without adding a single byte of RAM to the server.

You can probably guess that I am a big fan of compression, and many JD Edwards customers who are now using it are big fans of it as well. However there was one slight *** in SQL Server's Compression armour. In SQL 2008 compression only compressed non-Unicode data. From EnterpriseOne 8.9 and onwards Unicode has been enabled by default. If you upgraded from an earlier release you could get away without using Unicode. The likelihood however is if you upgraded to or installed EnterpriseOne 8.9 or higher the database is now using Unicode. You can tell by looking at the table definitions, if you see NCHAR or NVARCHAR columns then you are using Unicode. If you have a Unicode database that didn't mean however that there was no benefit in using compression, it is still possible to achieve 50%+ compression of a JD Edwards Unicode database when using SQL Server 2008's non-Unicode compression.

However with the release of SQL Server 2008 R2 Unicode compression was now included and the *** in compression’s armour removed. How much of a difference though does Unicode compression now make on a compressed JD Edwards database? To answer that question I ran some tests on a database that contained real customer data. The database was from an ERP 8 installation that was upgraded to ERP 9.0, some data was truncated from the database in order to speed up the upgrade process. The cut down database was still a healthy 371GB in size, uncompressed. The database was upgraded to ERP 9.0 using the standard JD Edwards upgrade process and after all the table conversions had completed the database was now using Unicode.

The database was first compressed (row compression) in SQL Server 2008 and this saw that size of the DB drop from 371GB to 161GB, that is a significant 56% reduction in size. I got a backup of this database and restored to a SQL 2008 R2 server, I then recompressed the database to take advantage of the Unicode compression now available in SQL 2008 R2. The addition of Unicode compression resulted in the database dropping to 118GB that is 68% smaller than the uncompressed database. Even though those numbers were great I decided to go in search of just a little bit more, so I uncompressed the whole database returning it to its original size of 371GB. I then compressed the whole DB again, using row compression, and the result was a database that was 111GB in size or 70% smaller than the uncompressed database.

These numbers demonstrate the kind of benefits you can achieve when using SQL Server 2008 R2 compression on your JD Edwards databases, and remember these numbers were achieved using real customer data. The test highlighted two things. The first is that Unicode compression in SQL 2008 R2 works and practically justifies an upgrade on its own. The second is that if you are using compression in SQL 2008 today, and you are planning an upgrade to SQL 2008 R2, then all you have to do is restore/attach the database to SQL 2008 R2 and recompress and you will get practically the same results as uncompressing the DB and recompressing it completely from scratch. The only question that remains to be answered is when are you planning your upgrade?

	Uncompressed	SQL 2008 Row Compression non-Unicode	SQL 2008 R2 Row Compression Unicode (recompressed)	SQL 2008 R2 Row Compression Unicode (from scratch)
Size in GB	371	161	118	111
% Smaller		56%	68%	70%