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.
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 analyse 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.
Reason No. 2 to upgrade to SQL Server 2008 R2: Report Parts
One of my absolute favourite R2 features is the new Report Parts, found in Report Builder 3.0. Report Parts allow you to publish the different sections of your report to a centralised library, where others can grab them to include in their own reports. Charts, tables, and other segments become components that you can plug into any report you wish.
Let's say you create a chart with complex calculations embedded. Maybe it incorporates business rules or complex layout formulas. If you publish it as a Report Part, everyone else will be able to call upon the same chart in their reports. Best of all, this makes me giddy inside, all reports referencing this chart are merely calling an instance of the library object itself. If you change the logic in the library copy of the chart, the change is automatically propagated to all the reports that use it. (Downstream users can disconnect a Report Part from the library if they don't want it to update.)
That's a lot of power at your fingertips. Publishing these Report Parts was so easy I actually thought I did something wrong. I love this feature.