Microsoft SQL Server 2008 R2

Reviewers Guide
Published: March 2010
Updated: March 2010

Summary: This guide provides key materials for evaluating Microsoft SQL Server 2008
R2, including product details, installation instructions and a guided tour.
SQL Server 2008 R2 Evaluators Guide 2

The information contained in this document represents the current view of Microsoft Corp. on the issues
discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it
should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee
the accuracy of any information presented after the date of publication.

This document may be changed substantially prior to final commercial release of the software described
herein.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.

Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights
under copyright, no part of this document may be reproduced, stored in or introduced into a retrieval
system, or transmitted in any form or by any means (electronic, mechanical, photocopied, recorded or
otherwise), or for any purpose, without the express written permission of Microsoft.

Microsoft may have patents, patent applications, trademarks, copyrights or other intellectual property
rights covering subject matter in this document. Except as expressly provided in any written license
agreement from Microsoft, the furnishing of this document does not give you any license to these patents,
trademarks, copyrights or other intellectual property.

Unless otherwise noted, the example companies, organizations, products, domain names, e-mail
addresses, logos, people, places and events depicted herein are fictitious, and no association with any real
company, organization, product, domain name, e-mail address, logo, person, place or event is intended or
should be inferred.
SQL Server 2008 R2 Evaluators Guide 3

Contents

Welcome...........................................................................................................4
What’s New in SQL Server 2008 R2?.................................................................6
SQL Server 2008 R2 Feature Review ..............................................................14
Key Features at a Glance............................................................................14
Trusted, Scalable Platform.........................................................................21
IT and Developer Efficiency........................................................................58
There has never been greater demand for IT to provide more value with
existing budgets and resources. SQL Server 2008 R2 provides new tools for
managing large multidatabase environments along with improved
capabilities to help maximize the value of consolidation efforts, and ensure
the streamlined development and deployment of data-driven applications.
...................................................................................................................58
This section will focus on how SQL Server 2008 R2 helps the IT professional
and developers become more efficient with enhancements to provide
greater visibility and control, enable resource optimization and further
drive deployment and administrative efficiencies......................................58
SQL Server 2008 R2 Reviewers Guide 4

Welcome
Welcome to the Microsoft SQL Server 2008 R2 Reviewers Guide.

This document is designed to help you understand the features and capabilities of the
new version of SQL Server 2008 R2 and take a tour of its capabilities.

Organizations are looking to compete and grow by reducing costs, reducing time to
market and identifying the highest value opportunities for their business. We’re moving
forward to rapidly address these challenges with new capabilities in development,
manageability, business intelligence and data warehousing and by delivering the first
relational database cloud offering with Microsoft SQL Azure. We have a vision for an
information platform that goes beyond storing and managing your data to help you
deliver greater value from your data across your business in the applications your
people use every day.

A key element of this vision is to focus on the people at the center, the users of SQL
Server:

• IT and database professionals who support expanding information needs through

IT services

• Business intelligence (BI) practitioners and end users who are looking to quickly
mine data for business insights to increase customer satisfaction and drive
business results

• Developers who build solutions to quickly capture business opportunities in an

increasingly competitive market

Microsoft is committed to deliver an information platform that provides you with a

complete set of enterprise-ready technologies and tools to help you realize more value
from your information at the lowest total cost of ownership.

• SQL Server 2008 R2 delivers enterprise-class reliability, scalability and security,

which is why it is already seeing rapid adoption among organizations that need
to support mission-critical scenarios.
• Customers can now achieve “private cloud” operations capabilities in their own
datacenter — by consolidating and virtualizing their datacenter, managing by
SQL Server 2008 R2 Reviewers Guide 5

policy and helping drive IT costs down while ensuring business uptime and
agility.
• Microsoft, along with its global partners, can now deliver highly scalable data
warehouse appliances based on standard reference architectures for guaranteed
performance, while giving customers choice in which partners and configurations
best suit their needs.

The key challenges we prioritized addressing with SQL Server 2008 R2 are broken down
into three main categories:

Data Scale, Quality and Compliance. Data volumes especially for decision support
systems are growing exponentially and the ability for the hardware and software to
support this growth is essential. As information becomes more readily available to the
rank and file of organizations, the ability to apply authorization rules and automate
access also becomes critical.

Administrator Efficiency. If you look at the trend of technology over time you’ll see
that we’re graduating more and more database administrators, and that’s great but the
problem is that the proliferation of software and database applications is increasing at a
greater rate than the number of database administrators on staff so what you get are
these overburdened administrators. On top of that, the increase in the hardware
computing capacity tends to leave a lot of underutilized hardware. So IT admins not
only need to become more efficient in managing a large number of applications, they
also need to ensure that resources are optimally utilized.

End-User Empowerment. This is essentially about enabling end users to do more with
less dependency on their IT departments. IT was cited by end users as being a major
bottleneck to reporting and analysis projects and from an IT perspective, they simply
could not keep up with every change an end user demands. Providing end users with
intuitive tools that enable them to build their own reports and models for analysis was a
major challenge for IT.

This guide will walk you through the major upgrades, new features and changes that
address these challenges. In addition, the second part of the guide will help you get
started in the installation and testing of the Microsoft SQL Server 2008 R2 Beta.

What’s New A summary of what’s in

the release
Features at a Glance Overview of all features
Feature Review Product details and screenshots
Getting Started Step-by-step installation
instructions
Guided Tour A hands-on tour of the
product

There are five sections in the guide:

SQL Server 2008 R2 Reviewers Guide 6

What’s New in SQL Server 2008 R2?

We have been listening to the challenges that businesses face today. The upcoming
Microsoft SQL Server 2008 R2 is designed to meet these challenges and represents a
significant evolution in scalability, manageability, data compression, application
deployment and self-service business intelligence.

Figure 1: SQL Server 2008 R2 Investment Themes

In response to the needs of our customers and partners, we have concentrated our
efforts on three investment areas with SQL Server 2008 R2:

Trusted, Scalable Platform. Supporting data consistency across heterogeneous

systems through SQL Server Master Data Services (MDS), enabling high-scale complex
event-stream processing through SQL Server StreamInsight, and supporting scale-up
scenarios for the largest available x64 and Itanium hardware (up to 256 logical
processors)

IT and Developer Efficiency. Enabling administrators to centrally monitor and

manage multiple database applications, instances or servers, accelerating the
development and deployment of applications and providing improved support for
virtualization through Hyper-V with Live Migration in Windows Server 2008 R2

Managed Self-Service BI. Expanding powerful BI tools to all users with SQL Server
PowerPivot for Excel and empowering a new class of business users to build and share
powerful BI solutions independently, while still enabling IT to monitor and manage user-
generated BI solutions
SQL Server 2008 R2 Reviewers Guide 7

Trusted, Scalable Platform

SQL Server 2008 R2 provides a comprehensive information platform delivering high
levels of performance, scalability, availability and security for mission-critical
applications at a low cost of ownership.

In this release, examples of some of the new investments we made include delivering
high scalability on a single system, enabling even more efficient data storage, and
delivering real-time insights to the business.

The Trusted, Scalable Platform investment area is broken down into three parts:

Enterprise-Level Security and Scale. SQL Server 2008 R2 provides scalability,

reliability, and security while allowing customers to take advantage of the latest
hardware innovations and computing technologies — making it capable of handling
enormous amounts of data fast, efficiently, and at a low cost. SQL Server provides the
following:

• Database redundancy in case of disaster enabling companies to quickly recover

damaged data the security companies need, such as data encryption, access
control and simplified compliance and auditing

• Predictable performance across workloads by defining resource limits and

priorities for workloads with Resource Governor

• Built-in compression features to enable companies to reduce hardware costs,

optimize storage, improve performance and reduce backup times

New capabilities delivered in SQL Server 2008 R2 provide the following benefits:

• The ability to compress Unicode data is a significant benefit for organizations

with international customer bases and for those who must store Unicode data for
other reasons.

• The capabilities help companies take advantage of the latest hardware

technology advancements and enables high scalability building on top of
industry-standard hardware for great total cost of ownership.

Data Consistency Across Heterogeneous Systems. SQL Server 2008 R2 helps

enterprises standardize the data people rely on to make critical business decisions.
Using Master Data Services, organizations can align operational and analytical data
across the enterprise and across their line of business systems and ensure the integrity
of information over time. To enable organizations to deliver trusted data assets, SQL
Server 2008 R2 includes the following:

• It includes a master data hub to provide an authoritative source for all master
data used by an enterprise, regardless of data system. Organizations can easily
align data from merged systems, new domains and even unique domains.
SQL Server 2008 R2 Reviewers Guide 8

• It includes the reporting, auditing and versioning features help to protect master
data and ensure compliance by maintaining change histories.

• It includes role-based security so administrators can enable anyone in an

organization to access and update master data directly with minimal impact on
IT. Administrators can lock down data and have granular control over what data
a given user (or group or role) may access across models, applications and
systems, even down to individual rows and columns in data sets.

• It includes powerful and agile hierarchy and attribute management capabilities,

Master Data Services supports a wide variety of hierarchy styles. It can
accommodate unique requirements and rectifies conflicts among systems. In
addition, it provides the flexibility to modify hierarchies and attributes to meet
changing needs, with built-in safeguards to insure the integrity of alterations.

High-Scale, Complex Event Processing. Gain rapid insight from streaming

information using SQL Server 2008 R2 StreamInsight. StreamInsight is a powerful
platform for developing and deploying complex event processing (CEP) applications. Its
high-throughput stream processing architecture and .NET-based development platform
enable developers to quickly implement robust and highly efficient event processing
applications. A highly optimized engine built on a lightweight streaming architecture
allows queries to be rapidly executed on high-speed data with low latency.

• Developers can write StreamInsight applications using .NET in their language of

choice. With a large community of developers working with familiar tools,
businesses can maximize the time to value of StreamInsight solutions and
reduce the time and cost required to create them.

• Flexible deployment scenarios and a central management interface help IT

deploy and manage CEP solutions efficiently while accurately tracking resource
usage.
SQL Server 2008 R2 Reviewers Guide 9

Managed Self-Service BI
SQL Server 2008 R2 unveils groundbreaking new technologies and tools, specifically
targeted at empowering users, assisting in seamless, highly secure sharing and
collaboration, and increasing IT and BI developer efficiency.

SQL Server 2008 R2 delivers a complete data infrastructure and BI platform, works with
Microsoft SharePoint Server 2010 to supply a reliable and highly secure collaboration
platform, and extends Microsoft Office 2010 to provide an intuitive and familiar user
platform with powerful analytics capabilities. The Microsoft BI solution stack is a
cornerstone in the Microsoft BI vision to provide business insight to all employees,
leading to better, faster, more relevant decisions.

Business User Platform

Self-Service Access & Insight, Data Exploration & Analysis, Predictive Analysis,
Data Visualization, Contextual Visualization,PowerPivotfor Excel 2010

Business Collaboration Platform

Dashboards & Scorecards, Excel Services, Web-based Forms & Workflows,

Collaboration, Search, Content Management, LOB Data Integration,
PowerPivotfor SharePoint 2010

Data Infrastructure & BI Platform

Analysis Services, Reporting Services, Master Data Services, Integration

Services, Data Mining, Data Warehousing

Figure 2: The Microsoft BI Solution Stack

SQL Server 2008 R2 capitalizes on the key business benefits that the Microsoft BI
solution stack provides to the organization, business users, and the IT department and
enables Managed Self-Service BI through the following areas of investments:

Empower End Users. End users will see the greatest impact from the uniform way
they now have of working with BI solutions. Using familiar Microsoft Office applications,
anyone can process vast amounts of data and obtain actionable insights without having
to rely on developers or IT:
SQL Server 2008 R2 Reviewers Guide 10

• Maximize utilization of familiar Excel features, such as the Microsoft Office Fluent
user interface, PivotTables, PivotCharts and the new Slicers feature for
interactive analysis

• Improve accuracy in decisions by combining and analyzing massive amounts of

data from a multitude of sources, including relational databases,
multidimensional sources, cloud services, data feeds, Excel files, and text files,
in the corporate network and on the Internet; and sort, filter and scroll through
millions of rows of data with about the same performance as a few thousand
rows.

• Intuitive authoring and publishing of business reports with rich visualizations,

flexible layouts, re-use of insights and pixel-perfect rendering with Report
Builder 3.0

Share and Collaborate. The BI capabilities of SQL Server 2008 R2 help you quickly
create, share and automatically refresh powerful analytical applications using
SharePoint 2010 as a familiar collaboration platform. All employees can realize benefits:

• Capitalize on standard SharePoint features; benefit from all the available

SharePoint features, such as role-based security, workflows and versioning
• Provide a simplified way for users can upload and find analyses, work seamlessly
via Web browser, and enjoy an attractive user interface in SharePoint —
browsing at its best — and the same performance and features as in the Excel
client
• Make decisions based on most-recent data; schedule automatic data refresh for
workbooks in SharePoint to ensure the data remains current automatically
• Maximize business insight; reuse available shared applications as data sources
and use SharePoint permissions and workflows to coordinate how users share
and collaborate on their own solutions

Improve IT Efficiency. Administrators benefit from interoperability with SharePoint

Managed Services to provide comprehensive management and monitoring of self-
service BI activities:

• Fewer dependencies on IT for quick and easy reporting and analysis and to
compress decision cycles

• Centralized managed of BI assets using familiar tools within SharePoint with a

management dashboard to centrally monitor user-generated applications and
ensure that BI solutions are continuously available, up to date and highly secure

• Track usage patterns over time, drill down to obtain more details, discover
mission-critical solutions and make sure appropriate server resources are
provisioned to ensure high availability and performance
• Security enhanced, seamless delivery of data to business users through report-
based data feeds to encapsulate enterprise systems that are notoriously hard to
get to
SQL Server 2008 R2 Reviewers Guide 11

IT and Developer Efficiency

SQL Server 2008 R2 provides new tools for managing large multidatabase
environments along with improved capabilities to help maximize the value of
consolidation efforts, and ensure the streamlined development and deployment of data-
driven applications.

Investments in multiserver management will help organizations proactively manage

database environments more efficiently at scale through centralized visibility into
resource utilization and streamlined consolidation and upgrade initiatives across the
application lifecycle — all with tools that make it fast and easy. SQL Server 2008 R2
delivers the following:1

Centralized Visibility and Control. SQL Server 2008 R2 delivers enhancements to

enable management of database instances and applications from a single location:

• Quickly identify potential issues or exceeded threshold via a unified dashboard

view of the overall health of applications and instances

• Gain insights into utilization and capacity to allow administrators to quickly

identify consolidation opportunities with detailed application and database views

Resource Optimization. Use new enhancements to gain insights for improved

consolidation management to reduce server sprawl:

• Streamline consolidation efforts with insight into resource utilization through

policy evaluation and historical analysis

• Make the best use of your server hardware investments through virtualization

• Maintain high availability targets when moving virtual machines from one host to
another with Windows Server 2008 R2 Hyper-V Live Migration — without any
disruption or perceived loss of service.

Administrative Efficiencies. SQL Server 2008 R2 simplifies the development,

deployment, and upgrades of data-driven applications:

• Eliminate the guesswork in deploying new applications by packaging the

application components and deployment requirements into a single “container”
for more efficient, low-risk deployment

• Streamline the process of updating or upgrading database applications or

moving changes from test to production

• Rapidly deploy and configure SQL Server with SQL Server 2008 R2 Sysprep

1
SQL Server 2008 R2 Reviewers Guide 12

SQL Azure
Microsoft SQL Azure Database is a cloud-based relational database service built for
Windows Azure platform. It provides a highly available, scalable, multitenant database
service hosted by Microsoft in the cloud. SQL Azure Database enables easy provisioning
and deployment of multiple databases. Developers do not have to install, setup, patch
or manage any software. High Availability and fault tolerance is built-in and no physical
administration is required. SQL Azure supports Transact-SQL (T-SQL). Customers can
leverage existing tools and knowledge in T-SQL-based familiar relational database and
Windows Server technologies. It provides the following benefits:

• It helps developers to quickly build or extend applications to run in the cloud

environment.
• It helps maintain low-cost management with a highly scalable infrastructure.
• It helps maintain business continuity with reliability, security and availability.

Self-Managing. SQL Azure Database offers the high availability and functionality of an
enterprise datacenter without the administrative overhead that is associated with an
on-premises solution. This self-managing capability enables organizations to provision
data services for applications throughout the enterprise without adding to the support
burden of the central IT department or distracting technology-savvy employees from
their core tasks to maintain a departmental database application.

• By using a cloud-based solution such as SQL Azure, you can provision your
data-storage needs in minutes and respond rapidly to changes in demand. This
reduces the initial costs of data services by enabling you to provision only what
you need, secure in the knowledge that you can easily extend your cloud-based
data storage if required at a future time.
• The service replicates multiple redundant copies of your data to multiple
physical servers to ensure data availability and business continuity. In the case
of a disaster, SQL Azure provides automatic failover to ensure maximum
availability for your application.
• Published service level agreements (SLAs) guarantee a business-ready
service. When you move to SQL Azure, you no longer need to back up, store,
and protect data yourself.

Highly Scalable. A key advantage of the cloud computing model is the ease with which
you can scale your solution. Using SQL Azure, you can create solutions that meet your
scalability requirements, whether your application is a small departmental application or
the next global Web success story.

• A pay-as-you-grow pricing model allows you to quickly provision new

databases as needed or scale down the services without the financial costs
associated with unused capacity.
• With a database scale out strategy your application can utilize the processing
power of hundreds of servers and store terabytes of data.
SQL Server 2008 R2 Reviewers Guide 13

• You can harness this global scalability to build the next generation of
Internet-scale applications that have worldwide reach, but without the
infrastructure costs and management overhead.
• SQL Azure provides the flexibility that ISVs need to segregate customer data
and implement multitenant billing, which enables you to build a global software
plus services solution quickly and easily.

Developer Empowerment. SQL Azure is built on top of the T-SQL language and is
designed to be compatible with SQL Server with a few changes, so developers can use
their existing knowledge and skills. This helps reduce the cost and time that is usually
associated with creating a cloud-based application.

• SQL Azure provides the same Tabular Data Stream (TDS) interface as SQL
Server, so developers can use the same tools and libraries to build client
applications for data that is in the cloud.
• SQL Azure data is stored in a way that is very familiar to developers and
administrators who use SQL Server. Within each server, you can create multiple
databases that have tables, views, stored procedures, indices and other familiar
database objects.
• The familiar data model ensures that your database developers can use their
existing relational database design and T-SQL programming skills to easily
migrate existing on-premises database applications to the cloud.
• SQL Azure is part of the rich Microsoft data platform which is interoperable
with the Microsoft Sync Framework to support occasionally connected
synchronization scenarios.
SQL Server 2008 R2 Reviewers Guide 14

SQL Server 2008 R2 Feature Review

Key Features at a Glance
• Existing = SQL Server 2008
• Improved/New = SQL Server 2008 R2

Trusted, Scalable Platform

New,
Improved or
Feature Existing Description Page
Enterprise-Level Security and Scalability

Reduce the complexity of developing applications that require

encrypted data by performing all encryption transparently at the
database level through a security enhanced database encryption
Transparent Data key. Enable application developers to access encrypted data
Encryption Existing without changing existing applications.

The Resource Governor provides enterprise customers the

Resource ability to both monitor and control the way different workloads
Governor Existing use CPU and memory resources on their SQL Server instances.

Achieve greater single system scale with support for up to 256

Support for >64 logical processors when deploying SQL Server 2008 R2 on
Cores New Windows Server 2008 R2. 24

The data compression feature released in SQL Server 2008

helps compress the data inside a database, and it can help
reduce the size of the database. Apart from the space savings,
data compression provides another benefit: Because
compressed data is stored in fewer pages, queries need to read
fewer pages from the disk, thereby improving the performance
of input/output intensive workloads. SQL Server 2008 provides
Data two levels of data compression — row compression and page
Compression Existing compression.

Unicode compression in SQL Server 2008 R2 uses an

implementation of the Standard Compression Scheme for
Unicode (SCSU) algorithm to compress Unicode values that are
stored in row or page compressed objects. For these
compressed objects, Unicode compression is automatic for
nchar(n) and nvarchar(n) columns. The SQL Server Database
Engine stores Unicode data as 2 bytes, regardless of locale.
This is known as UCS-2 encoding. For some locales, the
Unicode implementation of SCSU compression in SQL Server 2008 R2
Compression New can save up to 50 percent in storage space. 24
Data consistency across heterogeneous systems
Master Data Services
Microsoft SQL Server 2008 R2 Master Data Services helps an organization manage master data. Source
systems import business data, such as data from transactions, into the Master Data Services system,
where it can be standardized, streamlined, and organized into relationships for analysis and preparation to
send to subscribing systems.
MDS New Master data hub that provides central management of 25
Management master data entities and hierarchies. A master data hub
Hub provides an authoritative source for all master data
SQL Server 2008 R2 Reviewers Guide 15

used by an enterprise, regardless of data system.

Organizations can easily align data from merged
systems, new domains, and even unique domains. In
addition, Master Data Services can serve both as a
System of Entry or System of Record.
Master Data Manager is a Web application that serves
Master Data as a stewardship portal for business users and a
Manager New management interface for administrators. 26
In MDS, you can create business rules to describe the
MDS Business conditions to ensure the validity and integrity of the
Rules New data. 27
The reporting, auditing and versioning features help to
protect master data and ensure compliance by
maintaining change histories and logging user, date,
and time for each master data update as well as
MDS Version pertinent audit details, such as type of change, member
Management New code and prior versus new value. 27
With role-based security administrators can lock down
data and have granular control over what data a given
user (or group or role) may access across models,
MDS Role- applications, and systems, even down to individual rows
based Security New and columns in data sets. 28
With powerful and agile hierarchy and attribute
management capabilities, Master Data Services provides
the flexibility to modify hierarchies and attributes to
meet changing needs, with built-in safeguards to insure
the integrity of alterations. It maintains consistency by
automatically propagating approved updates across
MDS Hierarchy operational systems (e.g., DW, ERP, BI, CRM,
Management New analytics).
The Web services API is an option for creating custom
applications that integrate MDS with an organization’s
existing applications and processes. This API provides
MDS Web access to the master data model definitions as well as
Services API New to the master data itself. 26
Complex Event Processing
StreamInsight
Microsoft StreamInsight is a powerful platform for developing and deploying complex event processing
(CEP) applications. Its high-throughput stream processing architecture and .NET-based development
platform enable developers to quickly implement robust and highly efficient event processing applications.
StreamInsight implements a lightweight streaming
architecture that supports highly parallel execution of
continuous queries over high-speed data. The use of in-
Lightweight memory caches and incremental result computation
architecture provide excellent performance with high data
New throughout and low latency. 31
.NET Developers can write their CEP applications using
development Microsoft’s .NET language such as Visual C#, leveraging
environment the advanced language platform LINQ (Language
New Integrated Query) as an embedded query language. 31
The input adapter reads the incoming events in the
format in which they are supplied and translates this
Input Adapters data into the event format that is consumable by the
New CEP server. 32
You create an output adapter template to receive the
Output events processed by the CEP server, translate the
Adapters events into a format expected by the event target, and
New emit the data to that device. 32
SQL Server 2008 R2 Reviewers Guide 16

With StreamInsight, event processing is organized into

queries based on query logic that you define. These
queries take a potentially infinite feed of time-sensitive
Event input data (either logged or real time), perform some
Processing computation on the data, and output the result in an
New appropriate manner. 32

Event The StreamInsight server provides built-in aggregations

Aggregation for sum, count and average that typically operate on
New time windows. 32

The StreamInsight server provides a powerful join

Event Matching operation that matches events from two sources if their
New times overlap. 33
The management interface and diagnostic views that
are provided in the CEP server allow the administrator
to monitor and manage the CEP application. The
manageability framework also allows for ISVs and
system integrators to remotely monitor and support
Manageability CEP-deployed systems at manufacturing and other
New scale-out installations. 33

Managed Self-Service BI
New,
Improved or
Feature Existing Description Page
Empower End Users
PowerPivot Add-in for Excel
Assemble and analyze large scale, heterogeneous PowerPivot data in Excel 2010 workbooks. Create
relationships between tables to join data from a variety of sources into a new composite data source.
Use a rich expression language to create relational queries for custom aggregations, calculations, and
filters. Add data visualization and interaction through PivotTables, PivotCharts, Slicers and filters in
Excel reports.
Excel worksheets provide all data visualization and
interaction. PivotTables, PivotCharts, filters and Slicers
Excel worksheet provide the presentation of PowerPivot data you create
integration New in the PowerPivot window. 35
A new formula language that extends the data
Data Analysis manipulation capabilities of Excel to enable more
Expression sophisticated and complex grouping, calculation and
Language (DAX) New analysis. 37
VertiPaq is fast, column oriented processing for
published PowerPivot datasets in a SharePoint farm and
Data Compression for offline access while building or modifying large-scale
and VertiPaq mode New PowerPivot data in an Excel workbook. 36
You can create relationships between data sources by
matching columns that contain similar or identical data.
Column-to-column mapping across the full set of tables
Relationship allows you to build a multidimensional data source
Definition Tool New between tables that are from different data sources.
PowerPivot for Excel includes built-in support for
consuming data feeds as a source of data. Data feeds
can be one-time or repeatable data imports from URL
Data Feeds New addressable data sources that return XML tabular data. 32
SQL Server 2008 R2 Reviewers Guide 17

You can use the Table Import Wizard to add business

data from your corporate network, your local computer,
or ad hoc data sources on the Web. You can connect to
external data sources directly, or use predefined
connection objects such as Office Data Connection
(ODC) to get the data. You can also enter or copy in
Table Import Wizard New data from other worksheets, documents or text files.
Report Builder 3.0
Report Builder 3.0 is an intuitive, Office-optimized report authoring environment for business users who prefer
to work in the familiar Microsoft Office environment. You can use Report Builder to work with data, define a
layout, preview a report, and publish a report to a report server or SharePoint site.
Report parts are report items that you store on a report
server, or on a SharePoint site that is integrated with a
report server. You can reuse report parts in multiple
Report Parts New reports, and you can update them on the server. 51

The Report Part Gallery enables users to search and

Report Part access pre-built report parts and re-uses them to create
Gallery New or enhance reports. 52
Report Builder 3.0 provides a Map Wizard and Map
Mapping & Layer Wizard to add maps and map layers to your
Geospatial report to help visualize data against a geographic
Visualization New background.
Sparklines and databars are simple charts that convey a
lot of information in a little space, often in line with text.
Sparklines and data bars are often used in tables and
matrices and have the same basic chart elements of
categories, series, and values, but they have no legend,
Sparklines, axis lines, labels or tick marks.
Databars New 39
Indicators are minimal gauges that convey the state of
a single data value at a glance. The icons that represent
indicators and their states are visually effective, even
Indicators New when they are used in small sizes. 41
You can create expressions that calculate an aggregate
of an aggregate. By using this feature for charts and
gauges that are nested in a table, you can align
horizontal and vertical axes for charts and scales for
gauges. You do this by calculating the maximum and
Calculating minimum of aggregated values so the nested items use
Aggregates of the same ranges.
Aggregates New 42
PowerPivot Excel When you publish a PowerPivot workbook to the
Workbooks as data PowerPivot Gallery library, you can use the New Report
source for Report option to launch Report Builder and start a new report
Builder that uses the PowerPivot workbook as a data source.
New
Share and Collaborate
PowerPivot for SharePoint 2010
PowerPivot for SharePoint 2010 adds analysis services to a SharePoint 2010 farm, providing server-side
query processing and collaboration features for PowerPivot workbooks that you publish to a farm.
Rich preview and document access is provided through
a new PowerPivot Gallery library that includes accurate
thumbnails of the full range of sheets in a published
PowerPivot workbook. Users can preview a workbook
PowerPivot Gallery New before opening it. 46
PowerPivot workbook owners can schedule data refresh
to get updated data from any connected data sources
that provided original data to the workbook. Users can
PowerPivot Data view PowerPivot data refresh status and history for
Refresh New each PowerPivot workbook. 47
SQL Server 2008 R2 Reviewers Guide 18

The authentication methods and authorization model in

SharePoint extends to PowerPivot workbooks. There is
no separate authentication or authorization model to
Authentication implement. Permissions in your SharePoint deployment
and Access determine access to the document, and flows back to
Control the data source connections managed by the PowerPivot
New service application.
Reporting Services Add-in for SharePoint
Reporting Services with SharePoint integration has several new features. These include support for multiple
SharePoint zones, support for the SharePoint Universal Logging service, and a query designer for SharePoint
Lists as a data source.
S
You can use the alternate access mapping functionality in
your SharePoint environment to access report server items
Supports Multiple from one or more of the following SharePoint zones:
SharePoint Zones New default, Internet, intranet, extranet or custom. 49
Supports
SharePoint
Universal Logging
Services New 49
The SharePoint list data extension lets you specify a
SharePoint list as a data source. The associated
SharePoint list query designer provides a graphical
SharePoint List Data interface that lets you select the data you want from a
Extension New hierarchical view of lists and their fields. 50
Shared datasets are a new type of report server item
that can retrieve data from shared data sources that
connect to external data sources. A shared dataset
provides a way to share a query to help provide a
Shared Datasets New consistent set of data for multiple reports. 53
Increase IT Efficiency
PowerPivot Content
Storage and Centralized data access and document management is
Document enabled through SharePoint sites and libraries.
Management in the You can use content types and workflows to enforce
Farm New retention policies or processes. 46
Because embedded PowerPivot data is an integral part
of an Excel workbook, all queries for data and all
Excel Services subsequent rendering of that data is through Excel
Integration New Services.
In Central Administration, farm and service
administrators can view consolidated reporting data
PowerPivot about PowerPivot processing in the farm. Usage reports
Management can reveal data consumption patterns that show how
Dashboard New PowerPivot data is being used. 55
SQL Server 2008 R2 Reporting Services includes a new rendering
extension to support exporting report data to an Atom service
Reporting Services document. An Atom service document can be used by any
ATOM Feed New application that consumes data feeds, such as PowerPivot. 56

IT and Developer Efficiency

New,
Improved
or
Feature Existing Description Page
Centralized Visibility and Control

A SQL Server Utility groups multiple SQL servers

SQL Server Utility New as a pool of resources. 59
SQL Server 2008 R2 Reviewers Guide 19

The UCP provides the central reasoning point for the

SQL Server Utility using SQL Server Management Studio
to organize and monitor SQL Server resource health.
The UCP collects configuration and performance
Utility Control Point information from managed instances of SQL Server
(UCP) New every 15 minutes. 59
Utility Explorer, a component of SQL Server
Management Studio, connects to Database Engine
instances to provide a tree view of all the objects in the
SQL Server Utility. The Utility Explorer content pane
provides several ways to view summary and detailed
data, and a user interface to view and manage policy
Utility Explorer New definitions. 59
The dashboard includes summary and detail data from
all managed instances of SQL Server and all data-tier
applications in the SQL Server Utility. It allows you to
view the health of your resources from a single
Utility Dashboard New dashboard. 62
Optimize Resources
Administrators can bolster their consolidation efforts
through their use of SQL Server Utility dashboards and
viewpoints, which easily identify underutilized and over
utilized SQL Server resources across the SQL Server
Consolidation Management New Utility. 65
SQL Server 2008 R2 is part of Microsoft’s virtualization
stack. It is tightly interoperable and complements other
tools, servers, solutions to provide a comprehensive and
Virtualization Improved compatible virtualization experience. 65
With Hyper-V Live Migration, you can move running
virtual machines from one Hyper-V physical host to
another without any disruption of service or perceived
downtime. Live Migration is integrated with Windows
Server 2008 R2 Hyper-V and Microsoft Hyper-V Server
Hyper-V Live Migration New 2008 R2. 65
Administrative Efficiencies
Resource utilization policies can be defined globally for
all data-tier applications and managed instances of SQL
Server in the SQL Server Utility, or they can be defined
individually for each data-tier application and for each
managed instance of SQL Server in the SQL Server
Define Global Policies New Utility. 67
Policy-Based Management is a system for managing one
or more instances of SQL Server 2008. When SQL
Server policy administrators use Policy-Based
Management, they use SQL Server Management Studio
to create policies to manage entities on the server, such
Policy-Based as the instance of SQL Server, databases, or other SQL
Management Existing Server objects.
Allows you to administer multiple servers by designating
Central Management Servers and creating server
groups. Transact-SQL statements and Policy-Based
Central Management Management policies can be executed at the same time
Server Existing against server groups.
Interoperability with the Microsoft Visual Studio
development system introduces a new project template
called Data-Tier Application Component (DAC). This
project template captures the database application
schema (tables, stored procedures, and so forth) and
Data-Tier Application packages it with application deployment requirements,
Component (DAC) New enabling a single unit of deployment. 64
SQL Server 2008 R2 Reviewers Guide 20

Data-tier application — the data-tier application is an

unpacked DAC file deployed on an enrolled SQL Server
instance within a managed server group. The deployed
data-tier application is connected with the SQL Server
Utility Control Point enabling utilization data to be
Data-Tier Application New collected and capacity policies evaluated. 64
The SQL Server Sysprep functionality allows you to
install files and then configure at a later point. SQL
Server Sysprep can be used with Windows Sysprep to
create an operating system image that includes an
SQL Server Sysprep New unconfigured SQL Server installation. 66
SQL Server 2008 R2 Reviewers Guide 21

Trusted, Scalable Platform

Over the years companies have faced tough decisions on how to store and help secure
increasing amounts of structured and unstructured data. They have made it a priority to
take the necessary steps to protect their data assets — especially customer details,
human resources data, and financial information. With the growing number of users
accessing the data, they also must make sure that the database performs reliably and
quickly with little or no downtime.
SQL Server 2008 R2 provides scalability, reliability and security all in one
comprehensive information platform. It allows customers to take advantage of the
latest hardware innovations and computing technologies and is capable of handling
enormous amounts of data fast, efficiently, and at a low cost. SQL Server provides
database redundancy in case of disaster enabling companies to quickly recover
damaged data. It also provides the security companies need like data encryption,
access control, and simplified compliance and auditing.
With SQL Server 2008 R2, companies can achieve 99.999 percent availability —
increasing competitiveness and confidence of their customers.
SQL Server 2008 R2 Reviewers Guide 22

Enterprise-Level Security and Scale

The database platform is intimately related to the operating system. Because of this
relationship, Microsoft has designed Windows Server 2008 R2 to provide a solid IT
foundation for business-critical applications such as SQL Server 2008 R2. With these
two products, an organization can achieve maximum performance, scalability, reliability,
and availability, while at the same time helping reduce the total cost of ownership
associated with its database platform.

Maximum Scalability on Industry-Standard Hardware

Windows Server 2008 R2 supports up to 256 logical processors and 2 terabytes of

memory in a single operating system instance. When SQL Server 2008 R2 runs on
Windows Server 2008 R2, the two products together can support more intensive
database and business intelligence workloads than ever before.

Unicode Compression

Unicode compression in SQL Server 2008 R2 uses an implementation of the Standard

Compression Scheme for Unicode (SCSU) algorithm to compress Unicode values that
are stored in row or page compressed objects. For these compressed objects, Unicode
compression is automatic for nchar(n) and nvarchar(n) columns. The SQL Server
Database Engine stores Unicode data as 2 bytes, regardless of locale. This is known as
UCS-2 encoding. For some locales, the implementation of SCSU compression in SQL
Server 2008 R2 can save up to 50 percent in storage space.

The storage space that compression saves depends on the characteristics of the data
that is being compressed and the locale of the data. The following table lists the space
savings that can be achieved for several locales.

Locale Compression percent

English 50%

German 50%

Hindi 50%

Turkish 48%

Vietnamese 39%

Japanese 15%
SQL Server 2008 R2 Reviewers Guide 23

Data Consistency Across Heterogeneous

Systems
As an organization grows, the number of line of business applications tends to increase.
Furthermore, data from these systems flow into reporting and analytical solutions.
Often, the net result of this proliferation of data is duplication of data related to key
business entities, although each system may maintain only a subset of all possible data
that an organization might have available for any particular entity type. Master Data
Services is an extensible master data management platform that includes applications
for developing, managing and deploying master data models.

Master Data Services

Master Data Services helps enterprises standardize the data people rely on to make
critical business decisions. With MDS, IT organizations can centrally manage critical
data assets companywide and across diverse systems, enable more people to securely
manage master data directly, and ensure the integrity of information over time. Top
features in MDS include:

Master Data Hub

The goal of MDS is to address the challenges of both operational and analytical master
data management by providing a master data hub to centrally organize, maintain and
manage your master data. By centralizing the master data in an external system, you
can more easily align all business applications to this single authoritative source.

Because the master data hub is not specific to any domain, you can organize your
master data as you see fit, rather than force your data to conform to a predefined
format. You can easily add new subject areas as necessary or make changes to your
existing master data to meet unique requirements as they arise. The master data hub is
completely metadata-driven, so you have the flexibility you need to organize your
master data.
SQL Server 2008 R2 Reviewers Guide 24

Master Data Manager

Master Data Manager is a Web application that serves as a stewardship portal for
business users and a management interface for administrators. Master Data Manager
includes the following five functional areas:
Explorer. Use this area to edit the master data model objects and master data details,
apply business rules to validate master data, review and correct data
quality issues, annotate master data, monitor changes, and reverse
transactions.
Version Management. Use this area to create a new version of your master data
model and underlying data, uncover all validation issues in a model,
prevent users from making changes, assign a flag to indicate the current
version for subscribing systems, review changes, and reverse transactions.
Integration Management. Use this area to create and process batches for importing
data from staging tables into the MDS database, view errors arising from
the import process, and create subscription views for consumption of
master data by operational and analytic applications.
System Administration. Use this area to create a new model and model objects,
define business rules, configure notifications for failed data validation and
deploy a model to another system.
• User and Group Permissions. Use this area to configure security for users and
groups to access functional areas in Master Data Manager, to perform specific
functions, or to restrict or deny access to specific model objects.

Web Services

MDS includes a Web services API as an option for creating custom applications that
combine MDS with an organization’s existing applications and processes. This API
provides access to the master data model definitions as well as to the master data
itself. As an example, using this API, you can completely replace the Master Data
Manager Web application.
SQL Server 2008 R2 Reviewers Guide 25

Business Rules

One of the goals of a master data management system is to set up data correctly once
and to propagate only valid changes to downstream systems. To achieve this goal, the
system must be able to recognize valid data and to alert you when it detects invalid
data. In MDS, you create business rules to describe the conditions which render data
invalid. For example, you can create a business rule that specifies the required
attributes (also known as fields) for an entity. A business entity is likely to have multiple
business rules which you can sequence in order of priority, as in Figure 3.

Figure 3. Product entity’s business rules

Figure 4 is an example of a simple condition which simply identifies the required fields
for the Product entity. If you omit any of these fields when you edit a product member,
MDS notes a validation issue for that member and prevents you from using the master
data model until you supply the missing values.

Figure 4. Required fields business rule

Versioning Management

MDS uses a versioning management process to support multiple copies of a model and
the data it contains. With versioning, you can maintain an official working copy of
master data which no one can change alongside historical copies of master data for
reference and a copy for work-in-progress copy as you prepare the master data for
changing business requirements.
SQL Server 2008 R2 Reviewers Guide 26

MDS creates the initial version when you create a model. Anyone with the appropriate
permissions can populate the model with master data and make changes to the model
objects in this initial version until you lock the version. At that point, only users with
Update permissions can continue to modify the data in the locked version to add
missing information, fix any business rules validation, or revert changes made to the
model. If necessary, you can temporarily unlock the version to allow other users to
correct the data.

When all data validates successfully, you can commit the version. Committing a version
prevents any further changes to the model and allows you to make the version available
to downstream systems through subscriptions. You can use a flag, as shown in Figure 5,
to identify the version to use so that subscribing systems do not need to track the
current version number themselves. If you require any subsequent changes to the
model, you create a new version by copying a previously committed version and allow
users to make their changes to the new version.

Figure 5. Model versions

Role-Based Security

MDS uses a role-based security model that allows you to configure security both by
functional area and by object. For example, you can restrict one user exclusively to the
Explorer area of Master Data Manager, as shown in Figure 6, while granting access to
another user only to the Version Management and Integration Management areas.
Then, within the functional area, you must grant a user access to one or more models
to control which data the user can see and which data the user can edit. You must
assign a user permission to access at least one functional area and one model in order
for the user to open Master Data Manager.
SQL Server 2008 R2 Reviewers Guide 27

Figure 6. Functional area permissions

You can grant a user either Read-Only or Update permissions on a model. That
permission level applies to all objects in the model unless you specifically override the
permissions for a particular object, and then the new permission cascades downward to
lower level objects. Similarly, you can grant permissions on specific members of a
hierarchy and allow the permissions to cascade to members at lower levels of the
hierarchy.
SQL Server 2008 R2 Reviewers Guide 28

Complex Event Processing

While typical relational database applications are query-driven, event-driven
applications have become increasingly important. Event-driven applications are
characterized by high event data rates, continuous queries, and millisecond latency
requirements that make it impractical to persist the data in a relational database for
processing. They use complex event processing (CEP) technology with the goal of
identifying meaningful patterns, relationships and data abstractions from among
seemingly unrelated events and trigger immediate response actions.

Microsoft StreamInsight is a powerful platform for developing and deploying CEP

applications. Its high-throughput stream processing architecture and .NET-based
development platform enable developers to quickly implement robust and highly
efficient event processing applications.

You can achieve the following tactical and strategic goals for your enterprise by
developing your CEP applications using StreamInsight.

• Monitor your data from multiple sources for meaningful patterns, trends, exceptions
and opportunities.
• Analyze and correlate data incrementally while the data is in flight — that is, without
first storing it — yielding very low latency. Aggregate seemingly unrelated events
from multiple sources and perform highly complex analyses over time.
• Manage your business by performing low-latency analytics on the events and
triggering response actions that are defined on your business key performance
indicators (KPIs).
• Respond quickly to areas of opportunity or threat by incorporating your KPI
definitions into the logic of the CEP application, thereby improving operational
efficiency and your ability to respond quickly to business opportunities.
• Mine events for new KPIs.
• Move toward a predictive business model by mining historical data to continuously
refine and improve your KPI definitions.
SQL Server 2008 R2 Reviewers Guide 29

Lightweight Architecture

The StreamInsight runtime is the CEP server. It consists of the core engine and the
adapter framework. The adapter framework allows developers to create interfaces to
event stores such as Web servers, devices or sensors, and stock tickers or news feeds
and event sinks such as pagers, monitoring devices, KPI dashboards, trading stations,
or databases. Incoming events are continuously streamed into standing queries in the
CEP server, which processes and transforms the data according to the logic defined in
each query. The query result at the output can then be used to trigger specific actions.

Figure 7: StreamInsight Architecture

Development Models

StreamInsight provides three development models that support CEP application

development. While the tasks defined above remain the same, actual implementation of
the tasks varies with the development model . Sample applications demonstrating each
of these models is available in the StreamInsight Samples Software Development Kit
(SDK) available at [Link]

• The explicit server development model provides a full-service CEP application

environment by allowing the application developer to explicitly create and
register all of the objects required to transform and process events coming into
and going out of the CEP server. This gives the developer complete control of his
or her CEP application and development environment by using the client-side
object model API.
• The implicit server development model provides an easy-to-use environment
that hides much of the complexity associated with the explicit server
development model . It does this by allowing the CEP server to act as the
implicit host creating and registering most of the objects required to transform
and process events coming into and going out of the CEP server. This allows the
developer to focus his or her efforts on developing the query logic needed to
process and analyze the events within the CEP server. The server object itself is
"anonymous" and cannot be accessed directly through the object model.
SQL Server 2008 R2 Reviewers Guide 30

• The IObservable/IObserver development model provides an alternative method

of implementing input and output adapters as the producers and consumers of
event sources and sinks. This model is based on the IObservable/IObserver
design pattern in which an observer is any object that wishes to be notified when
the state of another object changes, and an observable is any object whose
state may be of interest, and in whom another object may register an interest.
For example, in a publication-subscription application, the observable is the
publisher, and the observer is the subscriber object. More information is
available on MSDN at [Link]
us/library/Ee817669(pandp.10).aspx.

Input Adapters

An input adapter instance accepts incoming event streams from external sources such
as databases, files, ticker feeds, network ports and so on. The input adapter reads the
incoming events in the format in which they are supplied and translates this data into
the event format that is consumable by the CEP server.

Output Adapters

You create an output adapter template to receive the events processed by the CEP
server, translate the events into a format expected by the event target, and emit the
data to that device. Designing and creating an output adapter is similar to designing
and creating an input adapter.

The Actions pane helps management actions to be discovered without the need for
extensive right mouse clicks.

Event Processing

With StreamInsight, event processing is organized into queries based on query logic
that you define. These queries take a potentially infinite feed of time-sensitive input
data (either logged or real time), perform some computation on the data, and output
the result in an appropriate manner. Developing the query logic you need to process
and analyze incoming events is the core task in developing your CEP application.

Aggregation

When you do not care about each single event, you might want to look into aggregate
values such as averages, sums or counts instead. The CEP server provides built-in
aggregations for sum, count, and average that typically operate on time windows.
SQL Server 2008 R2 Reviewers Guide 31

Matching Events From Different Streams

A common use case is the need to reason about events received from multiple streams.
For example, because event sources provide timestamps in their event data, you may
want to make sure that you only match events in one stream with an event in the
second stream if they are closely related in time.

In addition, you may have additional constraints on which events to match, and when
to match them. The CEP server provides a powerful join operation that performs both
tasks: first, it matches events from the two sources if their times overlap and second, it
executes the join predicate specified on the payload fields.

The result of such a match contains both the payloads from the first and the second
event. In the following example, events in stream stream1 are compared with events
in stream stream2. Events in the stream that meet the equality criteria defined in the
on clause are joined and output into a new event that contains the payload fields i and
j from event e1 and field j from event e2.
var equiJoin = from e1 in stream1
join e2 in stream2
on e1.i equals e2.i
select new { e1.i, e1.j, e2.j };

StreamInsight Manageability

The management interface and diagnostic views that are provided in the CEP server
allow the administrator to monitor and manage the CEP application. The manageability
framework also allows for ISVs and system integrators to remotely monitor and support
CEP-deployed systems at manufacturing and other scale-out installations.

Monitoring the state of a CEP server involves tracking the overall health of the system
and query performance. The state of a CEP server is captured by monitoring the CEP
queries running on the server; evaluating how the entities that compose a CEP query
are utilizing system resources.

Monitoring information can be obtained by creating diagnostic views using the

ManagementService API. You can create diagnostics views that return attributes at
the server level and at the query level. Query-level diagnostics are available for the
query template definition and the query instance itself. Server-level diagnostics are
available for two server components by using the Event Manager and Plan Manager.
SQL Server 2008 R2 Reviewers Guide 32

Managed Self-Service BI
Microsoft SQL Server 2008 R2 expands on the value delivered in SQL Server 2008 to
make actionable business intelligence accessible to all employees, leading to better,
faster, more relevant decisions. More than ever, organizations can empower individuals
to gain deeper insight into all aspects of their business and to share their findings
effortlessly and more securely.

SQL Server 2008 R2 unveils groundbreaking new technologies and tools, specifically
targeted at empowering users, assisting in seamless, more secure sharing and
collaboration, and increasing IT and BI developer efficiency. Innovations, such as
PowerPivot for Excel 2010 and SharePoint 2010, don’t just tackle typical enterprise BI
challenges — they change the game.
SQL Server 2008 R2 Reviewers Guide 33

Empower End Users

SQL Server 2008 R2 helps organizations empower their users by means of the following
tools: Office applications and add-ins, PowerPivot for Excel 2010, and Report Builder
3.0. Standard Office applications, specifically Excel, have long been the preferred data
analysis tools of business users. Excel includes a formidable formula engine, a familiar
user interface, and extensive data manipulation, analytics, and data mining capabilities,
including PivotTables, PivotCharts, and SQL Server Data Mining Add-ins.

PowerPivot for Excel 2010

PowerPivot for Excel 2010 is a data analysis tool that delivers unmatched computational
power directly within the application users already know and love — Microsoft Office
Excel. It provides users with the ability to analyze mass quantities of data and IT
departments with the capability to monitor and manage how users collaborate by
working seamlessly with Microsoft SharePoint Server 2010 and Microsoft SQL Server
2008 R2.

This innovative Excel add-in enables Excel power users to easily create powerful BI
solutions by streamlining the integration of data from multiple sources enabling
interactive modeling and analysis of massive amount of data and by supporting the
seamless sharing of data models and reports through Microsoft SharePoint 2010.

PowerPivot for Excel supports self-service business intelligence in the following ways.
• Current row-and-column limitations in Excel are removed so that you can import
much more data.
• A data relationship layer lets you integrate data from different sources and work
with all of the data holistically. You can enter data, copy data from other
worksheets, or import data from corporate databases. You can build
relationships among the data to analyze it as if it all originated from a single
source.
• Create portable, reusable data. Data stays inside the workbook. You do not need
manage external data connections. If you publish, move, copy, or share a
workbook, all the data goes with it.
• PowerPivot data is fully and immediately available to the rest of the workbook.
You can switch between Excel and PowerPivot windows to work on the data and
its presentation in PivotTables or charts in an interactive fashion. Working on
data or on its presentation are not separate tasks. You work on both together in
the same Excel environment.
SQL Server 2008 R2 Reviewers Guide 34

Figure 8. Sample PowerPivot for Excel Application

User Interface Customizations

PowerPivot implements its own assembly to enhance the Excel user experience through
ribbon customizations and spreadsheet templates, and overrides the default PivotTable
field list to implement its own task pane.

VertiPaq Engine for Advanced Data Analysis

VertiPaq makes the most of multicore processors and gigabytes of memory to process
enormous quantities of data with incredible speed. Processing millions of rows takes
about the same time as thousands.

Column-Based Compression

VertiPaq compresses the data users import into a PowerPivot workbook with efficient
column-based compression algorithms and maintains its database in a custom XML part
directly in the Excel workbook.
SQL Server 2008 R2 Reviewers Guide 35

In-memory data compression and processing describes VertiPaq mode, a new class of
Analysis Services processing capability for PowerPivot data that is embedded in Excel
workbooks. Processing metrics for VertiPaq can vary considerably depending on the
degree of redundancy in the original data sources: the higher the redundancy in the
data, the faster the processing. On average, you can expect to see 10-fold compression
of the original data.
Data can be processed in three ways: on demand from the client computer, on demand
in a SharePoint farm, or on a schedule via data refresh.

Broad Support for Data Sources

PowerPivot users can enjoy broad support of data sources to load and combine a great
variety of data for massive analysis on the desktop, including relational databases,
multidimensional sources, cloud services, data feeds, Excel files, text files and data
from the Web.

Data Analysis Expressions

Data Analysis Expressions (DAX) is a new formula language that extends the data
manipulation capabilities of Excel to enable more sophisticated and complex grouping,
calculation, and analysis.
SQL Server 2008 R2 Reviewers Guide 36

By using DAX in addition to standard Excel features, PowerPivot users can quickly
create advanced workbook applications. These applications can rely on data
relationships between tables as in a database, include calculated columns and
measures, and aggregate over billions of rows. In many cases, PowerPivot for Excel
2010 can establish the table relationships automatically.

DAX formulas are very similar to the formulas you type in Excel tables, but there are
some key differences.

• In Microsoft Excel you can reference individual cells or arrays; in PowerPivot you
can reference only complete tables or columns of data.
• DAX formulas do not support the same data types as Microsoft Office Excel, and
perform implicit type conversions on some data, depending on the operation.
More information is available at [Link]
us/library/ee634969(SQL.105).aspx.

A DAX formula always starts with an equal sign (=). After the equals sign, you provide
any or all of the following:

• References to columns or tables. The DAX language always uses tables and
columns as inputs to functions, never an array or arbitrary set of values.
• Operators, constants, and values provided as part of an expression.
• A function and any required arguments:
Most PowerPivot functions require one or more arguments, which can include
table, columns, expressions, and values. However, some functions, such as PI,
do not require any arguments, but always require parentheses to indicate the
null argument. For example, you must always type PI(), not PI. You can also
nest functions within other functions.
• Expressions. An expression can contain any or all of the following:
Operators
Constants
References to columns
For example, the following are all valid formulas:

Formula Results
=3 3
="Sales" Sales
='Sales'[Column The value of Column 1 in the Sales table.
1] If there is a relationship between the current table and the sales table, the value
will depend on the relationship.
=(0.03 Three percent of the value in the amount column of the current table.
*[Amount]) Although this formula can be used to calculate a percentage, the result is not
=0.03 * shown as percentage unless you apply formatting in the table.
[Amount]
=PI() The value of the constant pi.
='FALSE' = 0 TRUE
=(FALSE = 0) Because 0 and FALSE are different data types, DAX converts the values to a
SQL Server 2008 R2 Reviewers Guide 37

=('FALSE' = 0) common type and compares the results.

SQL Server 2008 R2 Reviewers Guide 38

Report Builder 3.0

Report Builder 2.0 was released with SQL Server 2008 and gave the user expanded
capabilities for importing queries from other report definition files or for writing a query
on any data source supported by Reporting Services. In addition, Report Builder 2.0
included support for all layout options of Report Definitional Language (RDL). Report
Builder 3.0 is the third iteration of this tool. It supports the new capabilities of SQL
Server 2008 R2 RDL including map, sparklines and databars. Report Builder 3.0 also
supports two improvements intended to speed up the report development process —
edit sessions and the Report Part Gallery.

Figure 9. Report Builder 3.0 Interface

Data Bars

A data bar is a special type of chart that you add to your report from the Toolbox
window. A data bar shows a single data point as a horizontal bar or as a vertical
column. Usually you embed a data bar inside of a TABLIX to provide a small data
visualization for each group or detail group that the TABLIX contains. After adding the
data bar to the TABLIX, you configure the value you want to display, and you can fine-
tune other properties as needed if you want to achieve a certain look.

By placing data bars in a TABLIX, you can compare each group’s value to the minimum
and maximum values within the range of values across all groups, as shown in Figure
9-1. In this example, Accessories 2005 is the minimum sales amount, and Bikes 2007 is
the maximum sales amount. The length of each bar allows you to visually assess
whether a group is closer to the minimum or the maximum or some ratio in between,
SQL Server 2008 R2 Reviewers Guide 39

such as the Bikes 2008 group, which is about half of the maximum sales.

Figure 9-1: Data bars

Sparklines

Like data bars, sparklines can be used to include data visualization alongside the
detailed data. Whereas a data bar usually shows a single point, a sparkline shows
multiple data points over time, making it easier to spot trends.

You can choose from a variety of sparkline types such as columns, area charts, pie
charts, or range charts, but most often sparklines are represented by line charts. As
you can see in Figure 9-2, sparklines are pretty bare compared to a chart. You do not
see axis labels, tick marks, or a legend to help you interpret what you see. Instead, a
sparkline is intended to provide a sense of direction by showing upward or downward
trends and varying degrees of fluctuation over the represented time period.

Figure 9-2: Sparklines

Indicators

Another way to display data in a report is to use indicators. In previous versions of

Reporting Services, you could produce a scorecard of key performance indicators by
SQL Server 2008 R2 Reviewers Guide 40

uploading your own images and then using expressions to determine which image to
display. Now you can choose indicators from built-in sets, as shown in Figure 9-3, or
you can customize these sets to change properties such as the color or size of an
indicator icon, or even by using your own icons.

Figure 9-3: Indicator types

Maps

A map element is a special type of data visualization that combines geospatial data with
other types of data to be analyzed. You can use the built-in Map Gallery as a
background for your data, or you can use Environmental Systems Research Institute
(ESRI) shapefile, and the map in Reporting Services also supports SQL Server spatial
data types and functions so you can store your polygons, points, and routes in the
database for use in your reports. In addition, you can even add Bing Maps tile layers as
a backdrop for your data. For more advanced customization, you can create your own
polygons to represent geographical areas or points on a map.
SQL Server 2008 R2 Reviewers Guide 41

Figure 9-4: Map using colors to show population distribution

Aggregation

The aggregate functions available in Reporting Services since its first release with the
SQL Server 2000 platform provided all the functionality most people needed most of the
time. However, if you needed to use the result of an aggregate function as input for
another aggregate function and weren’t willing or able to put the data into a SQL Server
Analysis Services cube first, you had no choice but to preprocess the results in the
dataset query.

In other words, you were required to do the first level of aggregation in the dataset
query, and then you could perform the second level of aggregation by using an
expression in the report. Now, with SQL Server 2008 R2 Reporting Services, you can
nest an aggregate function inside another aggregate function. Put another way, you can
aggregate an aggregation. The example table in Figure 9-5 shows the calculation of
average monthly sales for a selected year. The dataset contains one row for each
product, which the report groups by year and by month while hiding the detail rows.
SQL Server 2008 R2 Reviewers Guide 42

Figure 9-5: Aggregation of an aggregation

Here is the expression for the value displayed in the Monthly Average row:

=Avg(Sum(Fields![Link],"EnglishMonthName"))
SQL Server 2008 R2 Reviewers Guide 43

Share and Collaborate

There are at least three compelling reasons to use SharePoint 2010 in an enterprise BI
environment to establish a business collaboration platform: Seamless sharing and
collaboration with flexible security down to the individual item level; centralization of
farm and infrastructure administration; and automated server-based processing
through Windows services and SharePoint Timer jobs.

Power users play an important role as producers of insights at the team and personal BI
level. Complementing BI developers who create organizational solutions, power users
create and share workbook applications and reports by using PowerPivot for Excel 2010
and Report Builder 3.0.

Ultimately, power users decide whether to publish self-service BI solutions to a

SharePoint environment. To ensure they do, the managed BI collaboration environment
must enable them to publish their solutions effortlessly and highly securely. They must
also be able determine security roles indicating who can access uploaded self-service BI
solutions and who can modify these solutions with minimal dependency on IT.
SharePoint 2010 fulfills these requirements by means of a flexible role-based security
model that supports delegation of administrative control to groups and individual user
accounts, as well as permission inheritance and explicit permission assignments.

SharePoint SiteCollections, Sites,

Libraries, and Galleries

UploadingWorkbooks and Report Definitions Viewing or Downloading

as Shared Self-Service BI Applications Shared Self-Service BI Applications

PowerPivot for Internet

Excel 2010 Explorer
Producer of Consumers of
Report Excel Services SharePoint- PowerPivot for
Insights Insights
Builder3.0 integrated Excel 2010
(Power User) (Business Users)
Reporting
PowerPivot for
Services
SharePoint

IT Admin SharePoint Farmand BI Infrastructure

SQL Server 2008 R2 Reviewers Guide 44

PowerPivot for SharePoint 2010

PowerPivot for SharePoint 2010 takes advantage of Excel Services to render PowerPivot
workbooks directly on the application server. BI consumers can seamlessly access
shared workbook applications directly in the browser without having to download all the
data to their workstations.

PowerPivot Gallery

PowerPivot Gallery is a special type of SharePoint document library that is available

when you install Microsoft SQL Server PowerPivot for SharePoint in a Microsoft
SharePoint Server 2010 farm or on standalone server. The PowerPivot Gallery combines
an accurate preview of the file contents with facts about document origin. You can see
immediately who created the document and when it was last modified.
PowerPivot Gallery uses a snapshot service to create thumbnails of a larger, multipage
document. It can read PowerPivot workbooks and Reporting Services report definition
(.rdl) files. The snapshot is based on how the workbook is rendered by Excel Services.
The representation in PowerPivot Gallery should be identical to what you see when you
view a PowerPivot workbook in a browser.

Data Requests and Excel Services and PowerPivot Processing

SQL Server 2008 R2 Reviewers Guide 45

When you view a PowerPivot workbook from a SharePoint library, the PowerPivot data
that is inside the workbook is detected, extracted, and processed separately on Analysis
Services server instances within the farm, while Excel Services renders the presentation
layer. You can view the fully-processed workbook in a browser window or in an Excel
2010 desktop application that has the PowerPivot for Excel add-in installed.
Because PowerPivot data is part of an Excel 2010 workbook, a request for query
processing occurs when a user opens an Excel workbook from a SharePoint library and
interacts with a PivotTable or PivotChart that contains PowerPivot data.

Excel Services and PowerPivot for SharePoint components process different parts of the
same workbook (.xlsx) file. Excel Services detects PowerPivot data and requests
processing from a PowerPivot server in the farm. The PowerPivot server allocates the
request to an Analysis Services service instance, which retrieve the workbook from the
content library and saves it to disk. The Analysis Services service extracts the
PowerPivot data from the workbook and loads it into memory. Data that is stored in
memory is merged back into the rendered workbook, and passed back to Excel Web
Access for presentation in a browser window.
Not all data or objects in a PowerPivot workbook are handled by PowerPivot for
SharePoint. Excel Services processes tables and cell data in a worksheet. Only
PivotTables, PivotCharts, and slicers that go against PowerPivot data are handled by the
PowerPivot service.

Data Refresh

Scheduled data refreshes ensure the data in managed self-service BI solutions remains
current automatically, even when accessed over the Internet.
SQL Server 2008 R2 Reviewers Guide 46

Power users can also manage the data refresh settings for uploaded self-service BI
solutions, including refresh schedule, user credentials for the update process, and the
portions of the datasets that should be updated to ensure accurate analysis and
decision-making. SharePoint 2010 then performs the data refresh automatically.
SharePoint accomplishes this task based on a PowerPivot Data Refresh timer job, which
the SQL Server 2008 R2 Setup program adds to the SharePoint configuration when
installing PowerPivot for SharePoint 2010. This timer job runs every minute on the
SharePoint server to determine if a data refresh job needs to be scheduled and
executed.

PowerPivot for SharePoint 2010

Poweruser specifies data Businessusersenjoyup-to-
refreshingsettings. dateinformation and analysis.

PowerPivot for Excel 2010 Internet Explorer

PowerPivot
DataRefresh
Timer Job
Power User Business Users

Microsoft Analysis Reporting Excel

Teradata IBM DB2
SQL Azure Services Services Files

Office SQL Text Others

Oracle Sybase Informix
Access Server Files (OLE DB/ODBC)
Data Sources
SQL Server 2008 R2 Reviewers Guide 47

Reporting Services in SharePoint-Integrated Mode

Report Builder 3.0 fully exploits the advantages of Reporting Services in SharePoint-
integrated mode to store, manage, and render reports directly within the SharePoint
collaboration environment. Uploaded report definitions automatically open in the Report
Viewer Web Part, which supports page navigation, search, print, and export features.
You can also connect the Report Viewer Web Part to a Filter Web Part or a Library Web
Part in dashboards or site pages.

SQL Server 2008 R2 Reporting Services continues to improve interoperability with

SharePoint. In this release, you will find better options for configuring SharePoint 2010
for use with Reporting Services, working with scripts to automate administrative tasks,
using SharePoint lists as data sources, and integrating Reporting Services log events
with the SharePoint Unified Logging Service.

Improved Configuration

The first improvement affects the initial implementation of Reporting Services in

SharePoint integrated mode. After you have all components installed and configured on
both the report server and the SharePoint server, you need to use SharePoint 2010
Central Administration to configure the General Application settings for Reporting
Services. As part of this process, you can choose to apply settings to all site collections
or to specific sites, which is a much more streamlined approach to enabling Reporting
Services integration than was possible in earlier versions.

Support for Multiple SharePoint Zones

In previous releases of Reporting Services, report server items were available from only
the default SharePoint zone, which restricted the ability to access such items from other
SharePoint zones. In this latest release of Reporting Services, you can use the alternate
access mapping functionality in your SharePoint environment to access report server
items from one or more of the following SharePoint zones: default, Internet, intranet,
extranet, or custom.

This is useful when you have a SharePoint environment that can be accessed by users
from multiple zones. For example, your SharePoint site might be available to users from
the Internet and an intranet. By using alternate access mapping, you can ensure that
users from the Internet and intranet zones can access the same report server items
from your SharePoint site.

You can configure up to five different URLs to access a single Web application that
provides access to Reporting Services content, with each URL using a different
authentication provider. This functionality is important when you want to use Windows
authentication for intranet users and Forms authentication for Internet users.
SQL Server 2008 R2 Reviewers Guide 48

RS Utility Scripting

Report server administrators frequently use the [Link] utility to perform repetitive
administrative tasks, such as bulk deployment of reports to the server and bulk
configuration of report properties. Lack of support for this utility in integrated mode had
been a significant problem for many administrators, so having this capability added to
integrated mode is great news.

SharePoint Lists as Data Sources

More and more companies use SharePoint lists to store information that needs to be
shared with a broader audience or in a standard report format. Although there are some
creative ways you could employ to get that data into Reporting Services, custom code
was always part of the solution.

SQL Server 2008 R2 Reporting Services has a new data extension provider that allows
you to access SharePoint 2007 or SharePoint 2010 lists. After you create the data
source using the Microsoft SharePoint List connection type and provide credentials for
authentication, you must supply a connection string to the site or subsite in the form of
a URL that references the site or subsite.

That is, use a connection string such as [Link] or

[Link] A query designer is available with
this connection provider, as shown in Figure 10-1, allowing you to select fields from the
list to include in your report.

Figure 10-1: SharePoint list Query Designer

SQL Server 2008 R2 Reviewers Guide 49

Reusability in Reporting

SQL Server 2008 R2 Reporting Services has several new features to support reusability
of components. Report developers with advanced skills can build shared datasets and
report parts that can be used by others. Then, for example, a business user can quickly
and easily pull together these pre-constructed components into a personalized report
without knowing how to build a query or design a matrix.

To help the shared datasets run faster, you can configure a cache refresh schedule to
keep a copy of the shared dataset in cache. Last, the ability to share report data as an
Atom data feed for PowerPivot extends the usefulness of data beyond a single source
report.

Report Parts

After developing a report, you can choose which report items to publish to the report
server as individual components that can be used again later by other report developers
who have permissions to access the published report parts. You can publish any of the
following report items as report parts: tables, matrices, rectangles, lists, images,
charts, gauges, maps and parameters.

You can publish report parts both from Report Designer in Business Intelligence
Development Studio and from SQL Server 2008 R2 Report Builder 3.0. In Report
Designer, the Report menu contains the Publish Report Parts command. In the Publish
Report Parts dialog box, shown in Figure 10-2, you select the report items that you
want to publish. You can replace the report item name and provide a description before
publishing. You can also choose which parts to publish.
SQL Server 2008 R2 Reviewers Guide 50

Figure 10-2: The Publish Report Parts dialog box

When you first publish the report part, Reporting Services assigns it a unique identifier
that persists across all reports to which it will be added. Note the option in the Publish
Report Parts dialog box in Report Designer (Figure 10-2) to overwrite the report part on
the report server every time you deploy the report.

In Report Builder, you have a different option that allows you to choose whether to
publish the report item as a new copy of the report. If you later modify the report part
and publish the revised version, Reporting Services can use the report part’s unique
identifier to recognize it in another report when another report developer opens that
report for editing. At that time, the developer receives a notification of the revision and
can decide whether to accept the change.

Be aware that you can only publish report parts in Report Designer. To find and use
those report parts, you must use Report Builder 3.0. More information about Report
Builder 3.0 can be found in the Report Builder 3.0 section.

The Report Part Gallery

Report Builder 3.0 includes a new window, the Report Part Gallery that you can enable
from the View tab on the ribbon. Use the Report Part Gallery to collaborate and reuse
report items. At the top of this window is a search box in which you can type a string
value, as shown in Figure 10-3, and search for report parts published to the report
server where the name or the description of the report part contains the search string.
You can also search by additional criteria, such as the name of the creator or the date
created. To use the report part, simply drag the item from the list onto the report body.
The ability to find and use report parts is available only within Report Builder 3.0. You
can use Report Designer to create and publish report parts, but not to reuse them in
other reports.

Figure 10-3: The Report Part Gallery

SQL Server 2008 R2 Reviewers Guide 51

Shared Datasets

A shared dataset allows you to define a query once for reuse in many reports, much like
you can create a shared datasource to define a reusable connection string. This reduces
the need for the average user to know how to write queries to pull common data. The
main requirement when creating a shared dataset is to use a shared data source. In all
other respects the configuration of the shared dataset is just like the traditional
embedded dataset used in earlier versions of Reporting Services.

You can check the server to see which reports use the shared dataset when you need to
evaluate the impact of a change to the shared dataset definition. Simply navigate to the
folder containing the shared dataset, click the arrow to the right of the shared dataset
name, and select View Dependent Items, as shown in Figure 10-4.

Figure 10-4: The shared dataset menu

Combining Data From More Than One Dataset

To display data from more than one source in a table (or in any data region, for that
matter), you must create a dataset that somehow combines the data, because a data
region binds to one and only one dataset. You could create a query for the dataset that
joins the data if both sources are relational and accessible with the same
authentication. But what if the data comes from different relational platforms? Or what
if some of the data comes from SQL Server and other data comes from a Microsoft
Office SharePoint Server list? And even if the sources are relational, what if you can
access only stored procedures and are unable to create a query to join the sources?
These are just a few examples of situations in which the new Lookup functions in the
Reporting Services expression language can help.

In general, the three new functions, Lookup, MultiLookup, and LookupSet, work
similarly by using a value from the dataset bound to the data region (the source) and
matching it to a value in a second dataset (the destination). The difference between the
functions reflects whether the input or output is a single value or multiple values.
SQL Server 2008 R2 Reviewers Guide 52

You use the Lookup function when there is a one-to-one relationship between the
source and destination. The Lookup function matches one source value to one
destination value at a time, as shown in Figure 10-5.

Figure 10-5: Lookup function results

In the example, the resulting report displays a table for the sales data returned for
Dataset2, but rather than displaying the StateProvinceCode field from the same
dataset, the Lookup function in the first column of the table instructs Reporting Services
to match each value in that field from Dataset2 with the StProv field in Dataset1 and
then to display the corresponding StProvName. The expression in the first column of the
table is shown here:

=Lookup(Fields![Link], Fields![Link],
Fields![Link], "Dataset1")

The MultiLookup function also requires a one-to-one relationship between the source
and destination, but it accepts a set of source values as input. Reporting Services
matches each source value to a destination value one by one, and then returns the
matching values as an array. You can then use an expression to transform the array
into a comma-separated list, as shown in Figure 10-6.
SQL Server 2008 R2 Reviewers Guide 53

Figure 10-6: MultiLookup function results

The MultiLookup function in the second column of the table requires an array of values
from the dataset bound to the table, which in this case is the StateProvinceCode field in
Dataset2. You must first use the Split function to convert the comma-separated list of
values in the StateProvinceCode field into an array. Reporting Services operates on
each element of the array, matching it to the StProv field in Dataset1, and then
combining the results into an array that you can then transform into a comma-
separated list by using the Join function. Here is the expression in the Territory column:

=Join(MultiLookup(Split(Fields![Link], ","), Fields![Link],

Fields![Link], "Dataset1 "), ", ")

When there is a one-to-many relationship between the source and destination values,
you use the LookupSet function. This function accepts a single value from the source
dataset as input and returns an array of matching values from the destination dataset.
You could then use the Join function to convert the result into a delimited string, as in
the example for the MultiLookup function, or you could use other functions that operate
on arrays, such as the Count function, as shown in Figure 10-7.
SQL Server 2008 R2 Reviewers Guide 54

Figure 10-7: LookupSet function results

SQL Server 2008 R2 Reviewers Guide 55

Increase IT Efficiency

IT administrators primarily care about usage statistics for published workbook

applications, as well as statistics regarding the availability and performance of their
Web applications, service applications, and databases hosted on front-end servers,
application servers, and database servers in the SharePoint farm.

To provide this information, SQL Server 2008 R2 includes a customizable PowerPivot

Management Dashboard that visualizes server and usage statistics with animated
charts. The dashboard information helps IT administrators oversee their BI
environments with operational efficiency, and it provides the basis to help ensure that
user-generated BI solutions are continuously available, up to date and highly secure.

PowerPivot Management Dashboard

The PowerPivot Management Dashboard enables IT administrators to monitor activities

and performance of shared workbook applications, track usage patterns over time, drill
down to reveal hidden details, and detect solutions that have become mission-critical.

The dashboard reveals the largest and most popular solutions, their data sources, top
users, and query-performance per workbook, and helps in this way to ensure high
availability, scalability, and reliability for mission-critical applications. In addition, server
administrators can keep an eye on CPU and memory utilization, system capacity and
performance per server, response times, and current server state to provision
SQL Server 2008 R2 Reviewers Guide 56

appropriate server resources to ensure high availability and performance according to

QoS requirements.

Most Popular Workloads Published in SharePoint

Leverage the PowerPivot Management Dashboard to monitor your shared applications.

Track usage patterns over time, drill down to reveal hidden details, discover mission-
critical solutions and make sure appropriate server resources are provisioned to ensure
high availability and performance.

Reporting Services Atom Data Feed

SQL Server 2008 R2 Reporting Services includes a new rendering extension to support
exporting report data to an Atom service document. An Atom service document can be
used by any application that consumes data feeds, such as PowerPivot. You can use this
feature for situations in which the client tools that users have available cannot access
data directly or when the query structures are too complex for users to build on their
own. Although you could use other techniques for delivering a data feeds to users,
Reporting Services provides the flexibility to use a common security mechanism for
reports and data feeds, to schedule delivery of data feeds, and to store report
snapshots on a periodic basis.

To export a report to the Atom data feed, you click the last button on the toolbar in the
Report Viewer, as shown in Figure 11-1.
SQL Server 2008 R2 Reviewers Guide 57

Figure 11-1: Atom Data Feed

The Atom service document is an XML document containing a connection to each data
feed that is defined as a URL, as shown in the following XML code:

<?xml version="1.0" encoding="utf-8" standalone="yes" ?>

<service xmlns:atom="[Link]
xmlns:app="[Link] xmlns="[Link]
<workspace>
<atom:title>Reseller Sales</atom:title>
<collection
href="[Link]
&rs%3aCommand=Render&rs%3aFormat=ATOM&rc%3aDataFeed=xAx0x0">
<atom:title>TABLIX1</atom:title>
</collection>
</workspace>
</service>

SQL Server 2008 R2 Reporting Services-based data feeds provide the following
advantages:

• Virtually unlimited data access. Users can get highly secure access to mission-
critical information systems via server-based reports that access the source
systems using a system account.
• Consolidated heterogeneous data sources. Reporting Services supports a wide
range of data sources in reports that can serve as data feeds for analysis.
Consolidating and preparing data helps to decrease analysis complexity,
increases consistency, helps to compress decision cycles, and contributes to
more accurate business insights.
• Increased power user productivity. With data-encapsulating and -consolidating
reports readily available in the managed BI collaboration environment, users do
not waste time importing data from a variety of internal and external sources.
SQL Server 2008 R2 Reviewers Guide 58

IT and Developer Efficiency

There has never been greater demand for IT to provide more value with existing
budgets and resources. SQL Server 2008 R2 provides new tools for managing large
multidatabase environments along with improved capabilities to help maximize the
value of consolidation efforts, and ensure the streamlined development and deployment
of data-driven applications.

This section will focus on how SQL Server 2008 R2 helps the IT professional and
developers become more efficient with enhancements to provide greater visibility and
control, enable resource optimization and further drive deployment and administrative
efficiencies.
SQL Server 2008 R2 Reviewers Guide 59

Visibility and Control

The proliferation of software and database applications is increasing at a greater rate
than the number of database administrators on staff so what you get is these
overburdened administrators. On top of that, the increase in the hardware computing
capacity tends to leave a lot of underutilized hardware. So IT admins not only need to
become more efficient in managing a large number of applications, they also need to
ensure that resources are optimally utilized.

Investments in application and multiserver management will help organizations

proactively manage database environments efficiently at scale through centralized
visibility into resource utilization and streamlined consolidation and upgrade initiatives
across the application lifecycle — all with tools that make it fast and easy.

SQL Server Utility

This is a new manageability feature used to centrally monitor and manage database
applications and SQL Server instances from a single management interface known as a
Utility Control Point (UCP). Instances of SQL Server, Data-Tier Applications, database
files, and volumes are managed and viewed within the SQL Server Utility.

Utility Control Point

As the central reasoning point for the SQL Server Utility, the UCP collects configuration
and performance information from managed instances of SQL Server every 15 minutes.

After data has been collected from the managed instances, the SQL Server Utility
dashboard and viewpoints in SQL Server Management Studio provide database
administrators with a health summary of SQL Server resources through policy
evaluation and historical analysis.

Utility Explorer  

The Utility Explorer within the SQL Server Management Studio user interface provides a
hierarchical tree view for navigating through and managing the entities in the SQL
Server managed server group. This contrasts with SQL Server Management Studio
Object Explorer as Object Explorer displays each instance as a completely independent
object at the top of the hierarchy.

Utility Explorer Dashboards 

The dashboards in the SQL Server Utility offer database administrators

tremendous insight into resource utilization and health state for managed
instances of SQL Server and deployed Data-Tier Applications across the
enterprise.
SQL Server 2008 R2 Reviewers Guide 60

Before the introduction of the SQL Server Utility, database administrators did not
have a powerful tool included with SQL Server to assist them to monitor
resource utilization and health state. Most organizations purchased third-party
tools, which resulted in additional costs associated with the total cost of
ownership of their database environment. The new SQL Server Utility
dashboards also assist with consolidation efforts. Figure 1-2 illustrates SQL
Server Utility dashboard viewpoints for providing superior insight into resource
utilization and policy violations.
SQL Server 2008 R2 Reviewers Guide 61

Optimize Resources
The widespread practice of running one major application and operating system per
server has caused many server resources to be severely underutilized.

It has also resulted in server sprawl with many servers running at only 20 percent to 30
percent of their total system capacity. Companies have quickly realized that
consolidating applications onto a fewer number of servers can provide tremendous
economic benefits.

The database platform is intimately related to the operating system. Because of this
relationship, Microsoft has designed Windows Server 2008 R2 to provide a solid IT
foundation for business-critical applications such as SQL Server 2008 R2.

The combination of the two products produces an impressive package. With these two
products, an organization can achieve maximum performance, scalability, reliability,
and availability, while at the same time helping reduce the total cost of ownership
associated with its database platform.
SQL Server 2008 R2 Reviewers Guide 62

Consolidation Management 

Organizations can maximize their investments by consolidating SQL Server

resources onto fewer systems. Database administrators, in turn, can bolster their
consolidation efforts through their use of SQL Server Utility dashboards and
viewpoints, which easily identify underutilized and over utilized SQL Server
resources across the SQL
Server Utility. As illustrated
in Figure 12-3, dashboards
and viewpoints make it
simple for database
administrators to realize
consolidation opportunities,
start the process toward
eliminating underutilization,
and resolve overutilization
issues to create healthier,
pristine environments.

Figure 12-3:
Identifying
consolidation
opportunities with the
SQL Server Utility
dashboard viewpoints

Hyper-V Virtualization

The Hyper-V virtualization technology improvements in Windows Server 2008 R2 were

the most sought-after and anticipated enhancements for Windows Server 2008 R2. It is
now possible to virtualize heavy SQL Server workloads, as Windows Server 2008 R2
scales far beyond its predecessors.

Hyper-V Virtualization Live Migration

By leveraging Live Migration and comma separated values (CSVs) — two new
technologies included with Hyper-V and failover clustering on Windows Server 2008 R2
— it is possible to move virtual machines between Hyper-V hosts within a failover
cluster without downtime.

This is achieved via an intricate process. First, all VM memory pages are transferred
from the source Hyper-V physical host to the destination Hyper-V physical host.
Second, any VM modifications to the VMs memory pages on the source Hyper-V
physical host are tracked. These tracked and modified pages are transferred to the
SQL Server 2008 R2 Reviewers Guide 63

physical Hyper-V target computer. Third, the storage handles for the VMs’ virtual hard
disk files are moved to the Hyper-V target computer. Finally, the destination VM is
brought online.

Figure 12-4 below illustrates a four-node Hyper-V failover cluster with two CSVs and
eight SQL Server guest operating systems. With Live Migration, running SQL Server
VMs can be seamlessly moved between Hyper-V hosts.

Figure 12-4: A Hyper-V cluster and Live Migration

SQL Server 2008 R2 Reviewers Guide 64

Administrative Efficiencies
For centralized SQL Server management to provide incremental value, database
administrators and developers need a single unit of deployment for their database
applications to accelerate changes, upgrades, and deployments. Having the ability
to more easily package and move database applications is especially important for
streamlining the tasks associated with consolidation initiatives.

Data-Tier Application

To simplify the development and deployment of the data-tier, Microsoft has

introduced the concept of a data-tier application (DAC). A DAC is a single unit of
deployment that captures data objects and data-tier application artifacts. In other
words, it is a container that includes server and database schema objects that are
used by an application (for example., tables, views, logins and users), as well as
deployment prerequisites that set the requirements on the instances of SQL Server
where DACs can be deployed. The output file for a DAC is a Data-tier Application
Component (.dacpac), this file is unpacked and deployed to a managed instance.

Database administrators can create a DAC package file from within an existing
database. The DAC package can then be opened and edited by Visual Studio
developers in Visual Studio 2010 to make changes to the data-tier. They can then
build an updated DAC package file and send the changes to database
administrators, who in turn deploy the updates using SQL Server Management
Studio.

Figure 12-5: Extracting a DAC from an existing database.

DAC extraction and deployment are also extremely useful for upgrading databases
to the SQL Server 2008 platform. Users can move databases by extracting a DAC
from an instance of SQL Server 2000, SQL Server 2005, or SQL Server 2008, and
then deploy the extracted DAC to an instance of SQL Server 2008 R2, through either
SQL Server Management Studio or the Windows PowerShell command-line interface.
SQL Server 2008 R2 Reviewers Guide 65

Because the DAC contains server objects that the database depends upon, this
process is extremely straightforward.

Global Monitoring Policies

Control Point health policies can be configured for data-tier applications and managed
instances of SQL Server. Health policies can be defined globally for all data-tier
applications and managed instances of SQL Server in the Utility Administration from
within Utility Explorer, or they can be defined individually for each data-tier application
and for each managed instance of the managed server group.

Using the Policy tab within Utility Administration, you can define global policies. For
example, you could set the maximum and minimum values for managed instance
processor utilization. This defines the levels at which instances are reported as over or
under-utilized. These settings are defaults and each individual instance can have a
specific setting. Furthermore, you can set global and individual policies for data-tier
applications.

Advantages of Registering a Data-Tier Application to the Control Point Utility

The key benefit of using DACs is that they are designed to offer data-tier automation,
including:
• Collecting large numbers of entities into one DAC that can be managed as a
single unit through the full lifecycle of an application, including versioning.

• Automating the lifecycle of the data-tier by enabling developers to make

changes to a data-tier application component, package it, and then pass it to
database administrators for final deployment.
• Including policies that capture the intent of the developers, as well as
deployment requirements on the instances of SQL Server where DACs can be
deployed.

DAC Upgrade Efficiencies

DACs enable database administrators to upgrade the schema of SQL Server databases
and instances from SQL Server 2000, SQL Server 2005, and SQL Server 2008 to SQL
Server 2008 R2. Furthermore, the DAC contains any server level objects that the
database depends upon such as logins.

To upgrade, users can point to a SQL Server 2000, SQL Server 2005, or SQL Server
2008 database, extract a DAC and create the corresponding DAC package file, and then
deploy the DAC to a target instance of SQL Server 2008 R2. Then, data can be
transferred from the source database to the target database by using SQL Server
Integration Services, the bulk copy utility, or many other data migration techniques.
SQL Server 2008 R2 Reviewers Guide 66

Figure 12-6: Upgrading to SQL Server 2008 R2 by extracting a DAC

SQL Server 2008 R2 Reviewers Guide 67

SQL Server Sysprep

Organizations have been using the System Preparation tool (Sysprep) for many
years now to automate the deployment of operating systems. SQL Server 2008 R2
introduces this technology to SQL Server.

Installing SQL Server with Sysprep involves a two-step procedure that is typically
conducted by using wizards on the advanced page of the Installation Center. In the
first step, a stand-alone instance of SQL Server is prepared. This step prepares the
image; however, it stops the installation process after the binaries of SQL Server are
installed. To initiate this step, select the Image Preparation of a stand-alone instance
for Sysprep Deployment option on the advanced page of the installation center.

The second step completes the configuration of a prepared instance of SQL Server
by providing the machine, network, and account-specific information for the SQL
Server instance. This task can be carried out by selecting the Image Completion of a
prepared stand-alone instance step on the advanced page of the installation center.
SQL Server 2008 R2 Sysprep is recommended for database administrators seeking
to automate the deployment of SQL Server while investing the least amount of their
time.
SQL Server 2008 R2 Reviewers Guide 68

SQL Azure

Microsoft SQL Azure Database is a cloud database service from Microsoft. SQL Azure
provides Web-facing database functionality as a utility service. Cloud-based database
solutions such as SQL Azure can provide many benefits, including rapid provisioning,
cost-effective scalability, high availability, and reduced management overhead. This
paper provides an architectural overview of SQL Azure Database, and describes how
you can use SQL Azure to augment your existing on-premises data infrastructure or as
your complete database solution.

Introduction

Companies that provide Internet-based applications are facing many challenges today.
Users expect access to ever-increasing amounts of data from anywhere, at any time,
and from any device. The size, scale of use, and variety of forms of data are expanding
rapidly. Developers must build and deploy applications quickly to keep up with these
growing demands. Using the traditional on-premises data management model, meeting
these needs demands constant investment in and management of servers, operating
systems, storage, and networking. IT and operational staff must constantly monitor the
infrastructure to ensure that capacity, performance, and availability are maintained as
data volumes and user loads increase.

Cloud database services, such as Microsoft SQL Azure Database, provide an improved
way to respond to these challenges. SQL Azure is built on three key tenets:
manageability, scalability and developer agility.

From a developer's perspective, SQL Azure offers the well-known rich relational
programming model, and uses a familiar data access protocol and simple deployment
options. SQL Azure simplifies the process of creating, prototyping, and deploying
applications that integrate data across the enterprise. SQL Azure removes infrastructure
obstacles, thereby giving developers more freedom to innovate and experiment with
new ways of sharing data.

From the IT management perspective, SQL Azure offers a systematic and highly secure
cloud-deployed solution that works with your on-premises assets and gives the IT
organization oversight and control of distributed data assets. SQL Azure is built on the
same Microsoft SQL Server technologies that have already been used and proven in on-
premises deployments to provide high availability, reliability, and security.

From the business perspective, SQL Azure offers a cost-effective approach for managing
data, with a flexible consumption-based pricing plan, near-zero capital and operational
expenditures, and the ability to quickly and easily scale up or down as your needs
change.

If you are planning to build applications on large or shared data sets, provide on-
demand scalable data storage, or augment your on-premises data infrastructure with
low-cost, rapidly provisioned cloud-based storage, SQL Azure can provide a robust and
cost-effective solution.
SQL Server 2008 R2 Reviewers Guide 69

Manageability

SQL Azure Database offers the high availability and functionality of an enterprise
datacenter without the administrative overhead that is associated with an on-premises
solution. This self-managing capability enables organizations to provision data services
for applications throughout the enterprise without adding to the support burden of the
central IT department or distracting technology-savvy employees from their core tasks
to maintain a departmental database application.

Low-Friction Provisioning. When you use the traditional on-premises data

infrastructure, the time that it takes to deploy and secure servers, network
components, and software can slow your ability to prototype or roll out new data-driven
solutions.

However, by using a cloud-based solution such as SQL Azure, you can provision your
data storage needs in minutes and respond rapidly to changes in demand. This reduces
the initial costs of data services by enabling you to provision only what you need,
secure in the knowledge that you can easily extend your cloud-based data storage if
required at a future time.

High Availability. SQL Azure is built on robust and proven Windows Server and SQL
Server technologies, and is flexible enough to cope with any variations in usage and
load. The service replicates multiple redundant copies of your data to multiple physical
servers to ensure data availability and business continuity. In the case of a disaster,
SQL Azure provides automatic failover to ensure maximum availability for your
application.

Published service level agreements help to ensure a business-ready service. When you
move to SQL Azure, you no longer need to back up, store and protect data yourself.

Scalability

A key advantage of the cloud computing model is the ease with which you can scale
your solution. Using SQL Azure, you can create solutions that meet your scalability
requirements, whether your application is a small departmental application or the next
global Web success story.

Global Scalability. A pay-as-you-grow pricing model allows you to quickly provision

new databases as needed or scale down the services without the financial costs
associated with unused capacity. With a database scale out strategy your application
can utilize the processing power of hundreds of servers and store terabytes of data.

SQL Azure runs in worldwide datacenters, so you can reach new markets immediately.

If you want to target a specific region, you can deploy your database at the closest
datacenter. You can harness this global scalability to build the next generation of
Internet-scale applications that have worldwide reach, but without the infrastructure
costs and management overhead.
SQL Server 2008 R2 Reviewers Guide 70

Multitenant Support. ISVs who develop software plus services offerings must provide
adequate isolation for individual customers’ data. ISVs must be able to charge each
customer the right price for the data storage services that they have consumed. SQL
Azure provides the flexibility that ISVs need to segregate customer data and implement
multitenant billing, which enables you to build a global software plus services solution
quickly and easily.

Developer Empowerment

One of the potential obstacles to building great cloud-based applications is the

requirement for developers to learn new tools, programming platforms, and data
models. However, SQL Azure is built on top of the T-SQL language and is designed to
be compatible with SQL Server with a few changes, so developers can use their existing
knowledge and skills. This reduces the cost and time that is usually associated with
creating a cloud-based application.

Familiar Client Development Model. When developers create on-premises

applications that use SQL Server as a data store, they employ client libraries that use
the Tabular Data Stream protocol to communicate between client and server. There is a
large global community of developers who are familiar with SQL Server and have
experience of using one of the many client access libraries that are available for SQL
Server, such as Microsoft [Link], Open Database Connectivity (ODBC), Java
Database Connectivity and the SQL Server driver for PHP. SQL Azure provides the same
TDS interface as SQL Server, so developers can use the same tools and libraries to
build client applications for data that is in the cloud.

Proven Relational Data Model. SQL Azure data is stored in a way that is very familiar
to developers and administrators who use SQL Server. You can create a SQL Azure
Server which is a group of databases that are spread across multiple physical machines.
This SQL Azure Server is in some ways conceptually analogous to a SQL Server instance
and acts as an authorization boundary just as in SQL Server. You can also set geo-
location at this level. Windows Azure and SQL Azure data centers are located
worldwide; if your application is relevant to a specific region, you can increase
performance by geo-locating it there.

Within each server, you can create multiple databases that have tables, views, stored
procedures, indices, and other familiar database objects. This data model ensures that
your database developers can use their existing relational database design and T-SQL
programming skills, and easily migrate existing on-premises database applications to
the cloud.

SQL Azure servers and databases are logical concepts that do not correspond to
physical servers and databases. This abstraction enables the flexible provisioning that
was described earlier in this paper. Administrators and developers can concentrate on
data model design because SQL Data Services insulates them from the physical
implementation and management.

Synchronization and Support for Offline Scenarios. SQL Azure is part of the rich
Microsoft data platform that works with the Microsoft Sync Framework to support
occasionally connected synchronization scenarios. For example, by using SQL Azure and
the Sync Framework, on-premises applications and client devices can synchronize with
each other via a common data hub in the cloud.
SQL Server 2008 R2 Reviewers Guide 71

Conclusion

The groundbreaking new tools and technologies included in SQL Server 2008 R2 enable
organizations to empower their users, facilitate seamless and highly secure sharing and
collaboration on user-generated BI solutions, and help to increase IT and BI developer
efficiencies. Tools such as PowerPivot for Excel 2010 and Report Builder 3.0, and
innovative technologies such as PowerPivot for SharePoint 2010, Analysis Services in
SharePoint on VertiPaq or traditional storage modes, Reporting Services, and Master
Data Services, not only tackle all typical BI challenges in the enterprise, they truly
change the game of BI. The focus shifts from IT delivering organizational BI solutions to
a managed BI collaboration environment that gives users the power to get timely and
reliable information to make more relevant decisions.
Thanks to SQL Server 2008 R2 technology, it is becoming easier for business users to
get accurate answers in seconds and to ask even more questions, drill down into
details, discover new information, and new questions, and new answers.