You might wonder what is new and whether this is that fantastic?
Well as usual, I cannot say that Microsoft changed the world on this one either but as good followers, they release some of the new good obvious features they should address to avoid being too late.
1. In-memory OLAP
One of the main features SQL server 2008 R2 address, with Gemini / Excel 2010, is letting the user take relational sources on excel and develop a real OLAP cube with PowerPivot. It accelerates the prototyping of new cubes and enable users like marketers, managers and analysts to go faster and not wait for an entire life cycle to get the most of the data in short period.
Now, this is not fantastic either: you still need the ressources to be able to get the cube going like RAM and CPU to process. Excel can hold about 100 millions rows which is nice.
2. Optimistic unicode storage management
That might be the most underestimate feature of SQL server 2008 R2. This allows you to accelerate everything concerning reading and writing strings into columns where you need to enter your strings into unicode storage because of a few lines. Let's say your company primarily deals with non-unicode countries such as France for example and then they have a small amount like 1% of their business in Greece or Hungary and they need to use the data model everywhere, therefore need to change all their varchar storage to nvarchar storage.... great!
This is annoying... very annoying. First you will have to migrate your data from varchar to nvarchar, also the store procedures parameters, all variables, all table-valued parameters, so on and so forth.
Also, before Sql server 2008 R2, your storage required twoce the size as before... bummer. What if your storage was limited? what if your indexes suddenly jump in space and reindexing takes twice as long.... This is very annoying... and all of that for just 1% of the business...
He he! Microsoft with this new release did improve that. Even though R2 takes a tiny bit more space for the non-unicode data in a unicode column, it reduces probably by 40% the size needed for the unicde datatype.
In other words, think about having a database, with a major table with a very large varchar(512) column taking like 5GB of storage. Ouch. Suddenly you learn you need to migrate it to nvarchar(512) as your company is opening business to Russia. You need to find 5 more GB of space! Well with Sql Server 2008 R2, the non unicode data will take probably about 100 MB more only. So the migration would only be needed 100 MB only :-), these 100 MB is to store the fact that the data are non-unicode, not counting the potential index on the column or full text search catalog too...
Now you get it! finding data will also be much faster as you read way less i/o than before.
3. Master data management
It is interesting and Microsoft tries its best but this feature is not perfect! It is very not user-friendly and does not replace a good customized master data management system.
4. Logical CPU core
R2 now allow 256 cores. Before it was 64 cores. But to be honnest, unless you really need that many processors, your reaching this need means you either have not a scalable system and your data-tier is not optimized, or you have so many concurrent connections that you need to think about getting some kind of data-tier farm.
I guess 256 cores is nice when you think about datamining processing. It might be good for BI then.
5. Reporting services geospatial features
Reporting services adds new geospatial features so that you can litterally had a map of the United States and then you can click on each state to get the information, sales, costs, etc... However, this is pretty long to set up and not very user-friendly. It is worth looking at though.
There are other small features like multi-server tools.
Overall SQL server 2008 R2 is very interesting especially for BI new features but definitely not worth migrating from SQL server 2008 to 2008 R2, especially because everybody should wait for the first service pack ;-)