Top 10 Features of SQL 2008 R2

Introduction

Microsoft SQL Server 2008
R2 is the latest release of SQL Server. This article will introduce the top 10
features and benefits of SQL Server 2008 R2. The “R2” tag indicates this is an intermediate release of
SQL Server and not a major revision. However, there are a number of interesting new features for both DBAs and
developers alike. At the time of this article, R2 is available as a
CTP (Community Technology Preview). In addition to new features, there are two
new editions as well, SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel
Data Warehouse.

Report Builder 3.0

Report
Builder is a tool set for developing rich reports that can be delivered over
the web. Some of the features of Report Builder include the ability to create
reports containing graphs, charts, tables, and printing controls. In addition,
Report Builder also supports drill downs and sorting. If you are familiar with
the third party tool Crystal Reports, then you have good idea of what to expect
from Report Builder.

New
features in SQL 2008 R2 / Report Builder 3.0 include: Map Layers, which can
hold spatial and analytical data and will integrate with Microsoft Virtual
Earth. Indicators, these are gauges used to show the state of one value. Report
Parts, this object can be reused or shared between multiple reports. Aggregate
Calculating, this allows you to calculate the total value of other aggregate
calculated totals.

SQL Server 2008 R2 Datacenter

The new
Datacenter edition of SQL Server 2008 R2 is targeted towards Enterprise Edition
users who require a greater performance platform. The new edition will support
256 logical processors, high numbers of instances, and as much memory as the
operating system will support.

SQL Server 2008 R2 Parallel Data Warehouse

Another new SQL Server
edition, Parallel Data Warehouse, formally codenamed “Madison”,
specializes in handling extremely large amounts of data. This new version uses
massively parallel processing to spread large tables over multiple SQL nodes. The
multiple nodes are handled by a propriety Microsoft technology called Ultra
Shared Nothing. This new technology is described as a Control Node spreading
queries to Computer Nodes, evenly distributed, then collecting the results.

StreamInsight

New in
SQL Server 2008 R2 is component called StreamInsight. This interesting
component allows streaming data to be analyzed on the fly. Meaning the data is
processed directly from the source stream prior to being saved in a SQL Server
table. This could be extremely handy if you’re running a real time system and
need to analyze data but can’t afford the latency of a committed write to a
table first. Examples usually cited for this application include stock trading
streams, click stream web analytics, and industrial process controls. Multiple
input streams can be simultaneously monitored.

Master Data Services

Master
Data Services (MDS) is both a concept and a product. The concept of a Master
Data Service is that there is a central data gate keeper of core business
data. Data items such as customer billing addresses, employee/customer names, and
product names should be centrally managed so that all consuming applications
have the same information. The Microsoft example given is a company that has a
customer address record in the customer table but a different address in the
mailing table. A Master Data Service application would ensure that all tables
would have only one correct address. While an MDS can be a homegrown
application, SQL Server 2008 R2 includes an application and an interface to
manage the central data.

PowerPivot for SharePoint

PowerPivot
is an end-user tool that works in conjunction with SharePoint, SQL Server 2008
R2, and Excel 2010 to process large amounts of data in seconds. PowerPivot
works like an Excel Pivot Table, and includes analytical capabilities.

Data-Tier Application

A
Data-Tier Application (abbreviated as DAC –no idea what the C stands for, and
not to be confused with the Windows Data Access Components also abbreviated as
DAC ) is an object that stores all the needed database information for a
project, such as login, tables, and procedures into one package that can be
consumed by Visual Studio. By creating a Data-Tier Application, a SQL Server
package version could be saved with each Visual Studio build of your
application. This would allow application code builds to be married to a
database build in an easily managed way.

Unicode Compression

SQL Server
2008 R2 uses a new algorithm known as Simple Compression Scheme for Unicode
storage. This reduces the amount of disk spaced used by Unicode characters. This
new format happens automatically and is managed by the SQL Server engine so no
programming changes are required of the DBA.

SQL Server Utility

The new SQL
Server Utility is a repository object for centrally controlling multiple SQL
Server instances. Performance data and configuration policies can be stored in
a single Utility. The Utility also includes an Explorer tool where multi-server
dashboards can be created.

Multi Server Dashboards

While the SQL
Server Management Studio could always connection to multiple servers, each was
managed independently with no central view of all of them. Now with SQL Server
2008 R2, Dashboards showing combined server data can be created.

Conclusion

SQL Server
2008 R2 is the latest release of Microsoft SQL Server. The “R2” tag indicates
this is an intermediate release of SQL Server and not a major revision. There
are a number of compelling features in this version for both developers and
DBAs alike. Here are the Top 10 new features in SQL Server 2008 R2:

1. 

Report Builder 3.0

2. 

SQL Server 2008 R2
Datacenter

3. 

SQL Server 2008 R2
Parallel Data Warehouse

4. 

StreamInsight

5. 

Master Data Services

6. 

PowerPivot for
SharePoint

7. 

Data-Tier
Application

8. 

Unicode Compression

9. 

SQL Server Utility

10.   Multi Server Dashboards

»


See All Articles by Columnist
Don Schlichting

Don Schlichting
Don Schlichting
Don Schlichting is a Microsoft MSCE, MCDBA and an Oracle Certified Associate. Working at Professional Control Corporation as the IT Director,.

Latest Articles