Seven reasons to care about SQL Server 2008 R2

With Microsoft SQL Server 2008 R2, Microsoft begins to fully realize its vision of SQL Server as an information platform and not "just" a database. Hence the main theme for this release -- at least according to Microsoft -- is self-service BI. The PowerPivot plug-ins for Excel 2010 and SharePoint 2010 are easily going to make the biggest splash of all the new features, not least because they're the most complete. But then, SQL Server 2008 R2 isn't strictly necessary for PowerPivot for Excel, which works with plenty of other data sources.

A number of other features make SQL Server 2008 R2 worth a close look -- and they don't require Office 2010 or SharePoint 2010. Features such as StreamInsight and Master Data Services should gain traction right away, while others such as SQL Server SysPrep and DACPAC need some further baking. Overall, SQL Server 2008 R2 is a solid interim release, even if some key additions aren't quite what they could be.

[ Also on InfoWorld: From powerful productivity enhancers to important security safeguards, the new Microsoft Office has a number of features that businesses will love. See "Top 10 Office 2010 features for business" ]

Apart from new features, another change that may impact your shop is the addition of the new Datacenter edition. Along with introducing the new SKU, Microsoft has downgraded the Enterprise edition to support only 8 CPUs. If you have a SQL Server 2008 Enterprise server, you really need to be aware of this before you upgrade to R2.

Frankly, I think the levels for Enterprise have been set way too low. Shops that have 16-CPU servers aren't going to upgrade to R2 if it's going to cost them roughly $60,000 per processor. A more realistic break point for Datacenter edition would be greater than 64 CPUs.

But assuming you're not caught up in the horns of the Enterprise vs. Datacenter dilemma, R2 offers a handful of reasons to upgrade. Two or three of those reasons could even be compelling.

Reason No. 1 to upgrade to SQL Server 2008 R2: Self-service BI
Microsoft's new concept of managed self-service BI mostly refers to combining SQL Server 2008 R2 with the new PowerPivot plug-ins to push hefty row sets to Excel 2010 or to SharePoint 2010. The key to making this work is the use of column-level compression in the data set, which allows PowerPivot to process millions of rows in the same amount of time it would ordinarily take Excel to process only thousands of rows.

It's easy to see that financial professionals will love PowerPivot, but in fact PowerPivot will be useful to all kinds of Excel users. It's difficult to crunch any sizable data set in Excel; even if you're able to get all the rows into memory, processing them can take forever. I've seen this problem countless times when trying to analyze perfmon data, so I know many Excel users will welcome PowerPivot.

Note that PowerPivot for Excel can pull data not only from SQL Server but from almost any database. SQL Server 2008 R2 is required only to feed PowerPivot workbooks shared via SharePoint 2010. The PowerPivot plug-ins for Excel 2010 and SharePoint 2010 are freely downloadable.