Today, one of my SQL Server Magazine articles went live and its topic was an interesting one…‘Is SQL Server 2008 BI worth an upgrade?’ I have had the great pleasure of working in a large variety of IT environments over the years and one trend I’ve noticed becoming more prevalent is organizations implementing Microsoft SQL Server just for Business Intelligence. By no means am I implying that SQL Server is not a great OLTP (relational database engine) as well. The facts though are that Microsoft has invested (and continues to invest) a great amount of resources into its overall platform’s BI capabilities. Some of you may have heard the informal nickname for SQL Server 2005, ‘the BI release’. These investments reflect the current status of corporate IT environments which are increasingly adopting|implementing more Business Intelligence based systems.

Microsoft SQL Server 2008 continues the trend of improving its BI capabilities. Every major BI component (including the relational engine) has been enhanced in terms of both performance (scalability) and capabilities (features). Two other items I want to mention quickly (neither of which are in the corresponding article) is that of Change Data Capture (CDC) and Resource Governor (RG). CDC is a great feature for enabling your SQL Server 2008 based OLTP systems (yes, you would have to upgrade your OLTP servers) to expose their changed data asynchronously for ETL consuming purposes! In addition, RG can be used to prioritize OTLP workloads including that of ETL processes, this makes it easier to ensure that BI requirements do not adversely affect the more important OLTP-based applications (business transactional systems). So the big question still remains…is SQL Server 2008 BI worth the cost (and effort) of an upgrade? Unfortunately, like most difficult questions in life the answer is that it depends upon your unique business intelligence environment and requirements!

Cheers

Derek Comingore

Like Joe, things have been quite busy on my end as well (which is good). I have been doing alot of research as of late in the Microsoft SQL Server Reporting Services (SSRS) 2008 visualizations. With the arrival of SSRS 2008 users will now gain out-of-the-box access to the Dundas visualizations platform. If your curious, the initial Dundas press release can be found here. SSRS 2008 (as of the February 2008 CTP build) contains both Dundas Gauge and Dundas Chart products. In addition, according to the prior mentioned Dundas press release it states that Dundas Calendar will also be included into SSRS 2008.

This fairly recent Microsoft purchase of the Dundas source code for integration into SSRS 2008 is a great move as the Dundas suite of SSRS add-ons have become the premier choice for such advanced visualization needs. By including the Dundas technologies into SSRS 2008, Reporting Services customers will not only gain access to a much improved Report Server Architecture (without requiring IIS) but also an enhanced Visualization platform. There are literally about three times as many chart types in SSRS 2008 as compared to SSRS 2005. Some of the brand new chart types include the Funnel, Range, Pyramid, and Polar. In addition to the added chart types customers will also gain access to the Dundas Gauge capabilities via a new Gauge Data Region. As of the latest SQL Server 2008 CTP there are two main gauge types: Radial and Linear. Finally, we get a few other ‘goodies’ with the inclusion of the Dundas suite including:

• Secondary Axes
• Runtime Calculated Series
• WYSIWYG Chart Editor (design-time)

Below are some sample screenshots of the new SSRS 2008 visualization capabilities…

Cheers

Derek Comingore

Been away for some time finishing up on the Upgrade and Application Compatibility testing content and tools. We’re about 98% done with just a few more tweaks before final RTM. If you’re interested in the release candidate versions, check out SQL Server 2008 Upgrade Assistant.

Managed to find some time for an extended lunch so I figured I’d come back and share some other learnings - an easy one this time: backup compression. This is not the same as data compression so don’t confuse the two. I’ll blog on the latter later when I find some time. That’s pretty cool too.

I’ve worked with a lot of customers who use 3rd party backup software primarily for the compression capabilities followed by improved speed, especially during restore. With one of the more popular 3rd party backup solutions, I’ve restored a 1.2 TB database in under 30 minutes on a beefy HP Integrity rx8640 server with very a large EMC Dx3 SAN so there was no IO or CPU bottlenecks at any point. No official hard data on Katmai’s backup compression or performance right now but I know the folks in building 35 will be working hard to at least match the popular 3rd party solutions.

From some of the tests we’ve done both internally and with a couple of customers with tens and hundreds of GB databases, both compression ratio and performance seem to map closely to the popular 3rd party software we used. At least for default compressions on the 3rd party software. You can change the level of compression with some of these 3rd parties but SQL Server 2008 only has 1 level so I didn’t bother testing against different settings. I’d imagine the delta would not be significant percentage wise but if you have tens of Terabytes to deal with, even 5% can be a noteworthy difference.
 

How it’s done

Taking advantage of backup compression once you’ve upgraded to Katmai is just a matter of adding an extra option (in bold below) in your backup scripts 

– My Backup Job with compression 

BACKUP DATABASE MyBigDatabase
TO  DISK = ‘N:\Tier1Backups\MyBigDatabase.bak’
WITH
   NOFORMAT,
   NOINIT, 
   NAME = ‘Full DB Backup - MyBigDatabase’,
   SKIP,
   NOREWIND,
   NOUNLOAD,
   COMPRESSION;
GO

or selecting the compression option if you are using Management Studio GUI.  Note that you can change the global setting at the instance level with

SP_CONFIGURE ‘backup compression default’, 1;

so that all backup commands will always compress by default. This can be changed at the command level also when you execute your backup so you can backup with compression for specific databases only and not for everything in the instance. This is too easy.

Note that the default setting is ‘no compression’ so your DBA team does not get alarmed by the sudden reduction in the backup file sizes; just in case not everyone is keeping close tabs Katmai enhancements. Last thing you want is the graveyard shift operator re-running backup all night and finally paging you at 3am because “the backup file looks too small so it can’t be good…”. Don’t laugh (unless it’s someone else’s turn to be on-call).
 

Sample results

Simple tests with the AdventureWorks OLTP database yielded the following:

Path	Backup time	Restore time	File size
Original database	n/a	n/a	174,080 MB
NO COMPRESSION backup	23s	23s	171,093 MB
WITH COMPRESSION backup	11s	14s	40,097 MB

This was done on a VPC with 1.5GB of dedicated RAM and its own dedicated 7200rpm EIDE disk (8MB cache) on a USB 2 interface. The host machine is a notebook with an Intel Core 2 Duo 2.2GHz CPU (T7500), 4GB RAM and a 7200rpm SATA disk. The times are based on averages from 3 runs - all were with a couple of seconds. Other tests with real customer databases ranging from 5GB to 800GB in size yielded similar levels of compression and percentage elapsed time reduction. Sorry, can’t publish any of the customer data but those tests were run on x64 workstations with local SATA drives and IA64 servers with enterprise class SANs.

Not bad at all. Note the benefits from a performance perspective since you have much less to send to disk or retrieve when restoring. There was some impact on CPU utilization but it’s hard to say what the real impact would be when doing hot backups in a production environment since I don’t have other workloads running. You can expect some CPU load but the level will really depend on your database. Looks like it’ll be low to moderate. So long as you’re not already facing CPU pressure in your current system, I expect this to work fine but don’t take my word for it; test (no really, test). This is important because while few environments do full DB backups during peak hours, many companies have other system jobs or reports run during maintenance windows so make sure you don’t saturate the CPU. All that aside, the numbers are very decent especially since it’s still in beta.

Of course, with any compression technology, database or file system, your mileage can vary significantly. Character data types typically compress very well while binary types not as well (or very poorly in some cases). However, most traditional OLTP systems (e.g. accounting, sales, HR, etc…) will probably benefit from this since the bulk of the data is of character types.I expect this will be a widely used feature both to improve backup/restore performance and to save disk space (enterprise storage is NOT cheap).

One thing to remember is that smaller backup files also benefit some disaster recovery scenarios (basically those that don’t have good DR plans). Companies that do not have a SAN replication solution or some kind of Log Shipping/Database Mirroring solution in place, often transfer backup files to their warm DR site. Smaller files means faster transfer time which means better chance of recovery (you have a smaller window of exposure).

Password lock is available as with regular backups but there is no encryption capability with this release as far as I know. Now if you’re thinking, “hey, I’ve got Transparent Data Encryption, I can mix and match!!“. Well, don’t. Backup compression and TDE, at least based on the current release, do not compliment each other. You get next to nothing in space savings with backup compression on a TDE enabled database but you will burn the extra CPU cycles trying. As far as I can tell, this is true regardless of whether you use SQL Server’s backup compression or some 3rd party backup software with compression. However, some 3rd party solutions do offer backup compression with encryption but that’s a whole different deal. It’s a compressed backup that’s stored encrypted on disk with a non-TDE database.

Chalk this up as one of the “little to no effort” benefits when you move to SQL Server 2008. Just be aware that while it does great at the intended task, it doesn’t have all the bells & whistles that popular common 3rd party solutions do. For example, having different levels of compression and encrypting backups of non-TDE database are the most common I hear from our customers. Got something you want the SQL Server product group to add something for the next release? Go to SQL Server Connect

joe.