Seven reasons to care about SQL Server 2008 R2
New BI and reporting features in SQL Server 2008 R2 hit the mark, but other additions leave SQL Server shops wanting more
Reason No. 5 to upgrade to SQL Server 2008 R2: Multiserver monitoring
The SQL Server Utility Control Point, which is the heart of R2's new multiserver management capabilities, allows you to monitor resource health across multiple SQL servers, but it currently doesn't support actions on out-of-policy items. In other words, Control Point is read-only for the measurements it shows you. Another limitation: Only 25 managed instances are supported in the Enterprise edition; you'll have to jump to the Datacenter edition to manage more. Here's another area where the Enterprise SKU is set too low; I've never been a part of an enterprise where 25 is an acceptable cutoff for monitoring multiple nodes.
Which controls are measured? Currently you can see server CPU and instance CPU, along with the more important storage stats. Server CPU versus instance CPU is a very handy distinction; quite often when you see a sustained CPU issue, you want to make sure it's related to the database instance before you continue troubleshooting. Another interesting capability here is that you can see CPU on individual databases; if you have a lot of databases on the server, it's good to know which one is causing the trouble.
On the storage front, you can see volume storage utilization at the drive level as well as space stats at the database, file group, and file (data and log) levels. Other than I/O and queue stats, these are the storage stats that DBAs most care about, so a lot of work is already done for you here. While the Utility Control Point has some way to go to provide actual control, it offers a good level of monitoring functionality and will get users thinking about this tool and what they'd like to see from it.
Reason No. 6 to upgrade to SQL Server 2008 R2: DACPAC
DACPAC, or Data-tier Application Component Packages, is a much touted feature that allows your developers to package database changes into a single file in Visual Studio and send it to the DBAs for deployment. This is a significant improvement over how changes are being distributed now. Today they're either sent as a series of .SQL files with deployment instructions, or as a set of Team Foundation Server paths for the DBA to fetch with the same deployment document. Either way, the process leads to deployment problems because there's just too much human interaction and opportunity for misinterpretation.
With DACPAC, DBAs get a single deployment file from the developers that contains all the changes; there's nothing to misunderstand or forget. But in this first incarnation DACPAC has some problems. For starters, to make even minor changes in the database, DACPAC completely re-creates the database and all of its objects, then moves all of the data over to the duplicated structures. In the final step, it drops the original database and gives the new one the proper name. You can easily see that creating an entirely new copy of the database for a small code change won't fit into most situations. Further, DACPAC doesn't copy user permissions or work with service broker or replication objects.
DACPAC is a great idea, but is currently suitable only for very small databases with limited structures. However, keep an eye on this feature; I suspect many of these limitations will be ironed out in future versions. DACPAC should only get better and better.