What's new in SQL
Server 2017
SQL Server 2017 represents a
major step towards making SQL Server a platform that gives you choices of
development languages, data types, on-premises or cloud, and operating systems
by bringing the power of SQL Server to Linux, Linux-based Docker containers,
and Windows. This topic summarizes what is new for specific feature areas and
includes links to additional details. For more information related to SQL
Server on Linux, see SQL Server on Linux
Documentation
Note
In addition to the changes
below, cumulative updates are released at regular intervals after the GA
release. These cumulative updates provide many improvements and fixes. For information
about the latest CU release, see SQL Server 2017 Cumulative updates.
SQL
Server 2017 Database Engine
SQL Server 2017 includes
many new Database Engine features, enhancements, and performance improvements.
·
CLR
assemblies can now be added to a
whitelist, as a workaround for the clr
strict security feature described in CTP
2.0. sp_add_trusted_assembly, sp_drop_trusted_assembly, and sys.trusted_asssemblies are added to support the white list of
trusted assemblies (RC1).
·
Resumable
online index rebuild
resumes an online index rebuild operation from where it stopped after a failure
(such as a failover to a replica or insufficient disk space), or pauses and
later resumes an online index rebuild operation. See ALTER
INDEX and Guidelines
for online index operations.
(CTP 2.0)
·
The IDENTITY_CACHE
option for ALTER DATABASE SCOPED CONFIGURATION allows you to avoid gaps in the
values of identity columns if a server restarts unexpectedly or fails over to a
secondary server. See ALTER
DATABASE SCOPED CONFIGURATION.
(CTP 2.0)
·
A
new generation of query processing improvements that will adapt optimization
strategies to your application workload's runtime conditions. For this first
version of the adaptive query processing feature family, we have three
new improvements: batch mode adaptive joins, batch mode memory grant
feedback, and interleaved execution for multi-statement table valued
functions. See Intelligent
query processing in SQL databases.
·
Automatic
database tuning provides insight into
potential query performance problems, recommends solutions, and can
automatically fix identified problems. See Automatic
tuning. (CTP 2.0)
·
New graph
database capabilities for modeling many-to-many relationships include new CREATE
TABLE syntax for creating node
and edge tables, and the keyword MATCH for queries. See Graph
Processing with SQL Server 2017.
(CTP 2.0)
·
An
sp_configure option called clr
strict security is enabled by default to
enhance the security of CLR assemblies. See CLR
strict security.
(CTP 2.0)
·
Setup
now allows specifying initial tempdb file size up to 256 GB (262,144 MB)
per file, with a warning if the file size is set greater than 1GB with IFI not
enabled. (CTP 2.0)
·
The modified_extent_page_count
column in sys.dm_db_file_space_usage tracks differential changes in each database
file, enabling smart backup solutions that perform differential backup or full
backup based on percentage of changed pages in the database. (CTP 2.0)
·
SELECT
INTO T-SQL syntax now supports
loading a table into a FileGroup other than the user's default by using the ON
keyword. (CTP 2.0)
·
Cross
database transactions are now supported among all databases that are part of an
Always On Availability Group, including databases that are part of same
instance. See Transactions
- Always On Availability Groups and Database Mirroring (CTP 2.0)
·
New Availability
Groups functionality includes clusterless support, Minimum Replica Commit
Availability Groups setting, and Windows-Linux cross-OS migrations and testing.
(CTP 1.3)
·
New dynamic
management views:
o sys.dm_db_log_stats exposes summary level attributes and
information on transaction log files, helpful for monitoring transaction log
health. (CTP 2.1)
o sys.dm_tran_version_store_space_usage tracks version store usage per database,
useful for proactively planning tempdb sizing based on the version store usage
per database. (CTP 2.0)
o sys.dm_db_log_info exposes VLF information to monitor, alert,
and avert potential transaction log issues. (CTP 2.0)
o sys.dm_db_stats_histogram is a new dynamic management view for
examining statistics. (CTP 1.3)
o sys.dm_os_host_info provides operating system information for
both Windows and Linux. (CTP 1.0)
·
The Database
Tuning Advisor (DTA) has additional options and improved performance. (CTP
1.2)
·
In-memory
enhancements include support for
computed columns in memory-optimized tables, full support for JSON functions in
natively compiled modules, and the CROSS APPLY operator in natively compiled
modules. (CTP 1.1)
·
New string
functions are CONCAT_WS, TRANSLATE, and TRIM, and WITHIN GROUP is now
supported for the STRING_AGG function. (CTP 1.1)
·
There
are new bulk access options (BULK INSERT and OPENROWSET(BULK...) ) for
CSV and Azure Blob files. (CTP 1.1)
·
Memory-optimized
object enhancements
include sp_spaceused and elimination of the 8 index limitation for
memory-optimized tables, sp_rename for memory-optimized tables and natively
compiled T-SQL modules, and CASE and TOP (N) WITH TIES for natively compiled
T-SQL modules. Memory-optimized filegroup files can now be stored, backed up
and restored on Azure Storage. (CTP 1.0)
·
DATABASE
SCOPED CREDENTIAL is
a new class of securable, supporting CONTROL, ALTER, REFERENCES, TAKE
OWNERSHIP, and VIEW DEFINITION permissions. ADMINISTER DATABASE BULK OPERATIONS
is now visible in sys.fn_builtin_permissions. (CTP 1.0)
·
Database
COMPATIBILITY_LEVEL 140 is added. (CTP 1.0).
For more information, see What's
new in SQL Server 2017 Database Engine.
SQL
Server 2017 Integration Services (SSIS)
·
The
new Scale Out feature in SSIS has the following new and changed
features. For more info, see What's
New in Integration Services in SQL Server 2017. (RC1)
o Scale Out Master now supports high
availability.
o The failover handling of the execution logs
from Scale Out Workers is improved.
o The parameter runincluster of the
stored procedure [catalog].[create_execution] is renamed to runinscaleout
for consistency and readability.
o The SSIS Catalog has a new global property to
specify the default mode for executing SSIS packages.
·
In
the new Scale Out for SSIS feature, you can now use the Use32BitRuntime
parameter when you trigger execution. (CTP 2.1)
·
SQL
Server 2017 Integration Services (SSIS) now supports SQL Server on Linux,
and a new package lets you run SSIS packages on Linux from the command line.
For more information, see the blog
post announcing SSIS support for Linux. (CTP 2.1)
·
The
new Scale Out for SSIS feature makes it much easier to run SSIS on
multiple machines. See Integration
Services Scale Out.
(CTP 1.0)
·
OData
Source and OData Connection Manager now support connecting to the OData feeds of
Microsoft Dynamics AX Online and Microsoft Dynamics CRM Online. (CTP 1.0)
For more info, see What's
New in Integration Services in SQL Server 2017.
SQL
Server 2017 Master Data Services (MDS)
·
Experience
and performance are improved when upgrading from SQL Server 2012, SQL Server
2014, and SQL Server 2016 to SQL Server 2017 Master Data Services.
·
You
can now view the sorted lists of entities, collections and hierarchies in the Explorer
page of the Web application.
·
Performance
is improved for staging millions of records using the staging stored procedure.
·
Performance
is improved when expanding the Entities folder on the Manage Groups
page to assign model permissions. The Manage Groups page is located in
the Security section of the Web application. For more information about
the performance improvement, see https://support.microsoft.com/help/4023865?preview. For more information about assigning
permissions, see Assign
Model Object Permissions (Master Data Services).
SQL
Server 2017 Analysis Services (SSAS)
SQL Server Analysis Services
2017 introduces many enhancements for tabular models. These include:
·
Tabular
mode as the default installation option for Analysis Services. (CTP 2.0)
·
Object-level
security to secure the metadata of tabular models. (CTP 2.0)
·
Date
relationships to easily create relationships based on date fields. (CTP 2.0)
·
New Get
Data (Power Query) data sources, and existing DirectQuery data sources
support for M queries. (CTP 2.0)
·
DAX
Editor for SSDT. (CTP 2.0)
·
Encoding
hints, an advanced feature for optimizing data refresh of large in-memory
tabular models. (CTP 1.3)
·
Support
for the 1400 Compatibility level for tabular models. To create new or
upgrade existing tabular model projects to the 1400 compatibility level,
download and install SQL Server Data Tools (SSDT)
17.0 RC2. (CTP 1.1)
·
A
modern Get Data experience for tabular models at the 1400 compatibility
level. See the Analysis
Services Team Blog.
(CTP 1.1)
·
Hide
Members property to hide blank
members in ragged hierarchies. (CTP 1.1)
·
New Detail
Rows end-user action to Show Details for aggregated information. SELECTCOLUMNS and DETAILROWS functions for creating
Detail Rows expressions. (CTP 1.1)
·
DAX IN
operator for specifying multiple values. (CTP 1.1)
For more information, see What's
new in SQL Server Analysis Services 2017.
SQL
Server 2017 Reporting Services (SSRS)
SQL Server Reporting
Services is no longer available to install through SQL Server setup. Go to the
Microsoft Download Center to download
Microsoft SQL Server 2017 Reporting Services.
·
Comments
are now available for reports, to add perspective and collaborate with others.
You can also include attachments with comments.
·
In
the latest releases of Report Builder and SQL Server Data Tools, you can create
native DAX queries against supported SQL Server Analysis Services tabular data
models by dragging and dropping desired fields in the query designers. See the Reporting
Services blog.
·
To
enable development of modern applications and customization, SSRS now supports a
fully OpenAPI compliant RESTful API. The full API specification and
documentation can now be found on swaggerhub.
For more information, see What's
new in SQL Server Reporting Services (SSRS).
Machine
Learning in SQL Server 2017
SQL Server R Services has been
renamed SQL Server Machine Learning Services, to reflect support for
Python in addition to the R language. You can use Machine Learning Services
(In-Database) to run R or Python scripts in SQL Server, or install Microsoft
Machine Learning Server (Standalone) to deploy and consume R and Python
models that don't require SQL Server.
SQL Server developers now
have access to the extensive Python ML and AI libraries available in the
open-source ecosystem, along with the latest innovations from Microsoft:
·
revoscalepy - This Python equivalent of RevoScaleR
includes parallel algorithms for linear and logistic regressions, decision
tree, boosted trees and random forests, as well as a rich set of APIs for data
transformation and data movement, remote compute contexts, and data sources.
·
microsoftml - This state-of-the-art package of machine
learning algorithms and transforms with Python bindings includes deep neural
networks, fast decision trees and decision forests, and optimized algorithms
for linear and logistic regressions. You also get pre-trained models based on
ResNet models that you can use for image extraction or sentiment analysis.
·
Python
operationalization with T-SQL -
Deploy Python code easily by using the stored procedure sp_execute_external_script. Get great performance by streaming data from
SQL to Python processes and using MPI ring parallelization.
·
Python
in SQL Server compute contexts -
Data scientists and developers can execute Python code remotely from their
development environments to explore data and develop models without moving data
around.
·
Native
scoring - The PREDICT function in
Transact-SQL can be used to perform scoring in any instance of SQL Server 2017,
even if R isn't installed. All that's required is that you train the model
using one of the supported RevoScaleR and revoscalepy algorithms and save the
model in a new, compact binary format.
·
Package
management - T-SQL now supports the
CREATE EXTERNAL LIBRARY statement, to give DBAs greater management over R
packages. Use roles to control private or shared package access, store R
packages in the database and share them among users.
·
Performance
improvements - The stored procedure sp_execute_external_script has been optimized to support batch mode
execution for columnstore data.
For more information, see What's
new in SQL Server Machine Learning Services.
What's new in SQL
Server 2016
·
APPLIES TO: SQL Server (starting with
2016) Azure SQL Database Azure SQL Data Warehouse Parallel Data Warehouse
With SQL Server 2016, you
can build intelligent, mission-critical applications using a scalable, hybrid
database platform that has everything built in, from in-memory performance and
advanced security to in-database analytics. The SQL Server 2016 release adds
new security features, querying capabilities, Hadoop and cloud integration, R
analytics and more, along with numerous improvements and enhancements.
This page provides summary
information and links to more detailed SQL Server 2016 what's new information
for each SQL Server component.
Top 10 Features
of SQL 2008 R2
Introduction
Report Builder
3.0
SQL Server 2008
R2 Datacenter
SQL Server 2008
R2 Parallel Data Warehouse
StreamInsight
Master Data
Services
PowerPivot for
SharePoint
Data-Tier
Application
Unicode
Compression
SQL Server
Utility
Multi Server
Dashboards
Conclusion
What's New in
SQL Server 2008 R2
November Community Technology Preview (CTP)
Analysis
Services
PowerPivot for
Excel
PowerPivot
Field List Enhancements
PowerPivot Data Source Editing
Support for More Types of Data
Sources
Enhanced Data Analysis
eXpressions (DAX)
PowerPivot for
SharePoint
Management Dashboard
Enhanced Infrastructure
Database Engine
— Manageability Enhancements
Connectivity to
SQL Azure
Data-tier
Application Upgrade
SQL Server
Utility
Network
Connectivity
Reporting
Services
SharePoint
Integration
Shared Datasets
and Cache Refresh Plans
New Data
Visualization Report Items
Enhancements to
Reporting Services Features
Business Intelligence
Development Studio Support for
SQL Server 2008 Reports and Report Server projects
New Web Service
Endpoint
Service Broker
Master Data
Services
StreamInsight
What's New in SQL Server 2005
Feature Summary
Topic
Description
Notification Services is
a new platform for building highly-scaled applications that send and receive
notifications. Notification Services can send timely, personalized messages
to thousands or millions of subscribers using a wide variety of devices.
Reporting Services is a
new server-based reporting platform that supports report authoring,
distribution, management, and end-user access.
Service Broker is a new
technology for building database-intensive applications that are secure,
reliable, and scalable. Service Broker provides message queues the
applications use to communicate requests and responses.
The Database Engine
introduces new programmability enhancements such as integration with the
Microsoft .NET Framework and Transact-SQL enhancements, new XML
functionality, and new data types. It also includes improvements to the
scalability and availability of databases.
SQL Server 2005
introduces improvements in the programming interfaces used to access data in
SQL Server databases. For example, the SQL Native Client data access
technology is new, and the .NET Framework Data Provider for SQL Server, also
referred to as SqlClient, is enhanced.
Analysis Services
introduces new management tools, an integrated development environment and
integration with the .NET Framework. Many new features extend the data mining
and analysis capabilities of Analysis Services.
Integration Services
introduces a new extensible architecture and a new designer that separates
job flow from data flow and provides a rich set of control flow semantics.
Integration Services also provides improvements to package management and
deployment, along with many new packaged tasks and transformations.
This section describes
the programmability enhancements that have been made to Full-Text Search.
These enhancements include the data definition language for Full-Text Search
and the ability to specify languages in queries other than the default
language, and the manageability enhancements, such as side-by-side installs,
backup and restore full-text catalogs, and attach and detach full-text catalogs.
Replication offers
improvements in manageability, availability, programmability, mobility,
scalability, and performance.
SQL
Server 2005 introduces an integrated suite of management and development
tools that improve the ease-of-use, manageability, and operations support for
large scale SQL Server systems.
x
Try SQL Server today!
·
Download
the free SQL
Server 2016 Developer edition!.
·
Download
the latest version of SQL
Server Management Studio (SSMS).
·
Have
an Azure account? Spin up a Virtual
Machine with SQL Server 2016 already installed.
SQL
Server 2016 Database Engine
·
You
can now configure multiple tempDB database files during SQL Server
installation and setup.
·
New Query
Store stores query texts, execution plans, and performance metrics within
the database, allowing easy monitoring and troubleshooting of performance
issues. A dashboard shows which queries consumed the most time, memory or CPU
resources.
·
Temporal
tables are history tables which
record all data changes, complete with the date and time they occurred.
·
New
built-in JSON support in SQL Server supports JSON imports, exports,
parsing and storing.
·
New PolyBase
query engine integrates SQL Server with external data in Hadoop or Azure Blob
storage. You can import and export data as well as executing queries.
·
The
new Stretch Database feature lets you dynamically, securely archive data
from a local SQL Server database to an Azure SQL database in the cloud. SQL
Server automatically queries both local and remote data in the linked
databases.
·
In-memory
OLTP:
o Now supports FOREIGN KEY, UNIQUE and CHECK
constraints, and native compiled stored procedures OR, NOT, SELECT DISTINCT,
OUTER JOIN, and subqueries in SELECT.
o Supports tables up to 2TB (up from 256GB).
o Has column store index enhancements for
sorting and Always On Availability Group support.
·
New
security features:
o Always Encrypted: When enabled, only the application that has
the encryption key can access the encrypted sensitive data in the SQL Server
2016 database. The key is never passed to SQL Server.
o Dynamic Data Masking: If specified in the table definition, masked
data is hidden from most users, and only users with UNMASK permission can see
the complete data.
o Row Level Security: Data access can be restricted at the database
engine level, so users see only what is relevant to them.
See Database
Engine.
SQL
Server 2016 Analysis Services (SSAS)
SQL Server 2016 Analysis
Services provides improved performance, authoring, database management,
filtering, processing, and much more for tabular model databases based on the 1200
compatibility level.
·
SQL
Server R Services
integrate the R programming language, used for statistical analysis, into SQL
Server.
·
New Database
Consistency Checker (DBCC) runs internally to detect potential data
corruption issues.
·
Direct
Query, which queries live
external data rather than importing it first, now supports more data sources,
including Azure SQL, Oracle and Teradata.
·
There
are numerous new DAX (Data Access Expressions) functions.
·
New Microsoft.AnalysisServices.Tabular namespace manages tabular mode instances and
models.
·
Analysis Services Management
Objects (AMO) is
re-factored to include a second assembly, Microsoft.AnalysisServices.Core.dll.
SQL
Server 2016 Integration Services (SSIS)
·
Support
for Always On Availability Groups
·
Incremental
package deployment
·
Always
Encrypted support
·
New ssis_logreader
database-level role
·
New custom
logging level
·
Column
names for errors in the data flow
·
New connectors
·
Support
for the Hadoop file system (HDFS)
SQL
Server 2016 Master Data Services (MDS)
·
Derived
hierarchy improvements,
including support for recursive and many-to-many hierarchies
·
Domain-based
attribute filtering
·
Entity
syncing for sharing entity data
between models
·
Approval
workflows via changesets
·
Custom
indexes to improve query
performance
·
New permission
levels for improved security
·
Redesigned
business rules management experience
SQL
Server 2016 Reporting Services (SSRS)
Microsoft has thoroughly
revamped Reporting Services in this release.
·
New web
Report Portal with KPI feature
·
New Mobile
Report Publisher
·
Redesigned
report rendering engine
that supports HTML5
·
New
treemap and sunburst chart types
What's
New in SQL Server 2014
This topic summarizes detailed links to new features
in SQL Server 2014 and summarizes services packs for SQL Server 2014
Try it out: Have an Azure account?
Then go Here to spin up a Virtual
Machine with SQL Server 2014 Service Pack 1 (SP1) already installed.
SQL Server 2014 has not introduced significant new features
to the following:
SQL Server 2014 Service Pack 1 (SP1)
SQL Server 2014 (SP1) did not introduce significant
new features.
SQL Server 2014 Service Pack 2 (SP2)
SQL Server 2014 (SP2) Includes the following
improvements:
Performance and Scalability Improvements
·
Automatic Soft NUMA partitioning: With SQL Server 2014
SP2, Automatic Soft NUMA is enabled when Trace Flag 8079 is turned on during
instance startup. When Trace Flag 8079 is enabled during startup, SQL Server
2014 SP2 will interrogate the hardware layout and automatically configure Soft
NUMA on systems reporting 8 or more CPUs per NUMA node. The automatic, soft
NUMA behavior is Hyperthread (HT/logical processor) aware. The partitioning and
creation of additional nodes scales background processing by increasing the
number of listeners, scaling, and network and encryption capabilities. It is
recommended to first test the performance workload with Auto-Soft NUMA before
turning it in production. See the Blog for more information.
·
Dynamic Memory Object Scaling: SQL Server 2014 SP2
dynamically partitions memory objects based on number of nodes and cores to
scale on modern hardware. The goal of dynamic promotion is to automatically partition
a thread safe memory object (CMEMTHREAD) if it becomes a bottleneck.
Un-partitioned memory objects can be dynamically promoted to be partitioned by
node (number of partitions equals number of NUMA nodes), and memory objects
partitioned by node can by further promoted to be partitioned by CPU (number of
partitions equals number of CPUs). See the blog for more information.
·
MAXDOP hint for DBCC CHECK* commands: This improvement
addresses connect feedback (468694). You can now run DBCC
CHECKDB with the a MAXDOP setting other than the sp_configure value. If MAXDOP
exceeds the value configured with Resource Governor, the Database Engine uses
the Resource Governor MAXDOP value, described in ALTER WORKLOAD GROUP
(Transact-SQL). All semantic rules used with the max degree of parallelism
configuration option are applicable when you use the MAXDOP query hint. For
more information, see DBCC CHECKDB (Transact-SQL).
·
Enable >8TB for Buffer Pool: SQL Server 2014 SP2
enables 128TB of virtual address space for buffer pool usage. This improvement
enables SQL Server Buffer Pool to scale beyond 8TB on modern hardware.
·
SOS_RWLock spinlock Improvement: The SOS_RWLock is a
synchronization primitive used in various places throughout the SQL Server code
base. As the name implies, the code can have multiple shared (readers) or
single (writer) ownership. This improvement removes the need for spinlock for
SOS_RWLock and instead uses lock-free techniques similar to in-memory OLTP.
With this change, many threads can read a data structure protected by
SOS_RWLock in parallel without blocking each other and thereby providing
increased scalability. Prior to this change, the spinlock implementation
allowed only one thread to acquire the SOS_RWLock at a time, even to read a
data structure. See the blog for more information.
·
Spatial Native Implementation: Significant
improvement in spatial query performance is introduced in SQL Server 2014 SP2
through native implementation. For more information, see the knowledge base article KB3107399.
Supportability and Diagnostics Improvements
·
Database Cloning: Clone database is a
new DBCC command that enhances troubleshooting existing production databases by
cloning the schema and metadata without the data. The clone is created with the
command DBCC
clonedatabase('source_database_name', 'clone_database_name'). Note: Cloned
databases should not be used in production environments. Use the following
command determine if a database has been generated from a cloned database: select
DATABASEPROPERTYEX('clonedb', 'isClone'). The return value of 1
indicates the database is created from clonedatabase while 0 indicates
it is not a clone.
·
Tempdb supportability: A new errorlog
message that indicates the number of tempdb files and the size/autogrowth of
tempdb data files present at server startup.
·
Database Instant File Initialization Logging: A new errorlog
message that indicates on server statup, the status of Database Instant File
Initialization (enabled/disabled).
·
Module names in callstack: The Xevent callstack
now includes modules names + offset instead of absolute addresses.
·
New DMF for incremental statistics: This improvement
addresses connect feedback (797156) to enable tracking
the incremental statistics at the partition level. A new DMF
sys.dm_db_incremental_stats_properties is introduced to expose information
per-partition for incremental stats.
·
Index Usage DMV behavior updated: This improvement
addresses connect feedback (739566) from customers where
rebuilding an index will not clear any existing row entry from sys.dm_db_index_usage_stats
for that index. The behavior will now be the same as in SQL 2008 and SQL Server
2016. See the blog for more information.
·
Improved correlation between diagnostics XE and DMVs: This improvement
addresses connect feedback (1934583). Query_hash and query_plan_hash
are used for identifying a query uniquely. DMV defines them as varbinary(8),
while XEvent defines them as UINT64. Since SQL server does not have
"unisigned bigint", casting does not always work. This improvement
introduces new XEvent action/filter columns equivalent to query_hash and
query_plan_hash except they are defined as INT64 which can help correlating
queries between XE and DMVs.
·
Support for UTF-8 in BULK INSERT and BCP: This improvement
addresses connect feedback (370419) where support for
export and import of data encoded in UTF-8 character set is now enabled in BULK
INSERT and BCP.
·
Lightweight per-operator query execution profiling: While troubleshooting
query performance, although showplan provides lot of information on the query
execution plan and cost of operator in the plan but it has limited information
on actual runtime statistics like (CPU, I/O Reads, elapsed time per-thread).
SQL 2014 SP2 introduces these additional runtime statistics per operator in the
Showplan as well as an XEvent (query_thread_profile) to assist troubleshooting
query performance. See the blog for more information.
·
Change Tracking Cleanup: A new stored
procedure sp_flush_CT_internal_table_on_demand is introduced to
cleanup change tracking internal tables on demand.
·
AlwaysON Lease Timeout Logging Added new logging
capability for Lease Timeout messages so that the current time and the expected
renewal times are logged. Also a new message was introduced in the SQL Errorlog
regarding the timeouts. See the blog for more information.
·
New DMF for retrieving input buffer in SQL Server: A new DMF for
retrieving the input buffer for a session/request (sys.dm_exec_input_buffer) is
now available. This is functionally equivalent to DBCC INPUTBUFFER. See the blog for more information.
·
Mitigation for underestimated and overestimated memory
grant: Added new query hints for Resource Governor through
MIN_GRANT_PERCENT and MAX_GRANT_PERCENT. This allows you to leverage these
hints while running queries by capping their memory grants to prevent memory
contention. For more information, see knowledge base article KB310740
·
Better memory grant/usage diagnostics: A new extended event
was added to the list of tracing capabilities in SQL Server
(query_memory_grant_usage) to track memory grants requested and granted. This
provides better tracing and analysis capabilities for troubleshooting query
execution issues related to memory grants. For more information, see knowledge base article KB3107173.
·
Query execution diagnostics for tempdb spill:- Hash Warning and
Sort Warnings now have additional columns to track physical I/O statistics,
memory used and rows affected. We also introduced a new hash_spill_details
extended event. Now you can track more granular information for your hash and
sort warnings (KB3107172). This improvement is
also now exposed through the XML Query Plans in the form of a new attribute to
the SpillToTempDbType complex type (KB3107400). Set statistics on
now shows sort worktable statistics. .
·
Improved diagnostics for query execution plans that
involve residual predicate pushdown: The actual rows read
will now be reported in the query execution plans to help improve query
performance troubleshooting. This should negate the need to capture SET
STATISTICS IO separately. This now allows you to see information related to a
residual predicate pushdown in a query plan. For more information, see knowledge base article KB3107397.
New SQL
Server 2012 Features
So what does SQL Server 2012 have to offer over SQL
Server 2008 R2? Following is an overview of the new features provided in SQL
Server 2012:
·
New
storage features—Columnstore
indexes, FileTable storage.
·
New
Transact-SQL (T-SQL) constructs—Sequence
objects, THROW statement, new conversion, logical, string, and
date and time functions, and ad hoc query paging.
·
New
scalability and performance features—Indirect
checkpoints, FORCESCAN table hint, number of table partitions increased
to 15,000.
·
New
security features—Database
Audit, user-defined server roles, contained databases.
·
New
availability features—A number
of high-availability enhancements known as AlwaysOn, which include AlwaysOn
Availability Groups and AlwaysOn Failover Cluster Instances.
·
Statistical
Semantic Search—Statistical Semantic Search
builds upon the existing full-text search feature in SQL Server by querying the
contextual meaning of terms within a document repository.
·
Data
Quality Services—This new
feature allows you to build a knowledge base of data rules and use those to
perform a variety of critical data quality tasks, including correction,
enrichment, standardization, and de-duplication of your data.
The following sections take a closer look at each
of these new features and, where appropriate, provide references to subsequent
chapters where you can find more information and detail about the new features.
New and
Improved Storage Features
SQL Server 2012 provides a few new features related
to data storage, primarily intended to improve performance.
SQL Server 2012 provides an enhancement to
FILESTREAM storage by allowing more than one filegroup to be used to store
FILESTREAM data. This can improve I/O performance and scalability for
FILESTREAM data by providing the ability to store the data on multiple drives.
FILESTREAM storage, which was introduced in SQL Server 2008, integrates the SQL
Server Database Engine with the NTFS file system, providing a means for storing
unstructured data (such as documents, images, and videos) with the database storing
a pointer to the data. Although the actual data resides outside the database in
the NTFS file system, you can still use T-SQL statements to insert, update,
query, and back up FILESTREAM data, while maintaining transactional consistency
between the unstructured data and corresponding structured data with same level
of security. For more information on using FILESTREAM storage, see Chapter 20,
“Creating and Managing Tables.”
A new storage features introduced in SQL Server
2012 is FileTable storage. FileTable is the new type of table that was added in
SQL Server 2012. The FileTable builds upon the FILESTREAM capability that was
introduced in SQL Server 2008. Like FILESTREAM, FileTable storage provides SQL
Server access to files that are stored in the Windows file system. FileTable
differs from FILESTREAM in that FileTable allows for Windows applications to
access the files that are part of the FileTable definition. This file access is
nontransactional, and it comes without having to make changes to the Windows
program that is accessing the files. For more information on FileTable storage,
see Chapter 44, “What’s New for Transact-SQL in SQL Server 2012.”
SQL Server 2012 also introduces a new data
warehouse query acceleration feature based on a new type of index called an
xVelocity memory optimized columnstore. This new index, combined with enhanced
query processing features, improves data warehouse query performance by
hundreds to thousands of times in some cases and can routinely give a tenfold
speedup for a broad range of decision support queries. Columnstore indexes can
limit or eliminate the need to rely on prebuilt aggregates, including
user-defined summary tables and indexed (materialized) views. Unlike typical
row storage, which stores multiple rows of data per page, a columnstore index
stores the values for a single column in its own set of disk pages. The
advantage of columnar storage is the ability to read the values of a specific
column of a table without having to read the values of all the other columns.
For more information on the structure of columnstore indexes, see Chapter 30,
“Data Structures, Indexes, and Performance.” For more information on creating
and using columnstore indexes, see Chapter 57, “Parallel Data Warehouse
Appliance.”
New
Transact-SQL Constructs
What would a new SQL Server release be without new
T-SQL commands and constructs to further expand the power and capabilities of
the T-SQL language? SQL Server 2012 is no exception. The new constructs
provided in SQL Server 2012 include the following:
·
Sequence
objects—Sequence objects can be used to
generate a sequence of numeric values according to the definition of the
object. Sequence objects provide an alternative for generating autosequencing
values, similar to an Identity column but independent of any specific table.
Sequence objects provide more flexibility and allow applications to apply a
sequence value across multiple tables.
·
THROW statement—The THROW
statement can be used to raise an error condition and transfer execution to a CATCH block of
a TRY...CATCH construct or to re-raise the error condition that invoked the CATCH block.
·
New
conversion functions—PARSE, TRY_PARSE, and TRY_CONVERT.
·
New
logical functions—CHOOSE and IIF.
·
New
string functions—CONCAT and FORMAT.
·
New date
and time functions—DATEFROMPARTS, DATETIME2FROMPARTS, DATETIMEFROMPARTS, DATETIMEOFFSETFROMPARTS, SMALLDATETIMEFROMPARTS, TIMEFROMPARTS.
·
New ROWS and RANGE Clauses—Provides support for support for
windows framing, which can be used to restrict the results to a subset of rows
by specifying the start and end points within the partition of the OVER clause.
·
New
window offset functions—LAG and LEAD functions used in conjunction with OVER clause
let you return a value from a specific row that’s a certain offset from the
current row.
·
New
window rank distribution functions—PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, CUME_DIST.
·
Ad hoc
query paging—Provides a mechanism to implement
paging using the relatively simple syntax provided by the ANSI standard ORDER BY ... OFFSET / FETCH clause.
For more information and examples of the new T-SQL
features and enhancements, see Chapter 44.
New
Scalability and Performance Features
SQL Server provides some new scalability and
performance features including indirect checkpoints and the FORCESCAN table hint.
Indirect checkpoints provide a mechanism for
configuring checkpoint intervals at the individual database level. Indirect
checkpoints can provide potentially faster, more predictable recovery times for
your critical databases than automatic checkpoints. For more information on
indirect checkpoints, see Chapter 27, “Transaction Management and the
Transaction Log.”
The new FORCESCAN table hint complements the FORCESEEK hint by allowing you to specify that the query optimizer use an index
scan operation as the access path to the table or view referenced in the query.
For more information on the FORCESCAN table hint, see Chapter 31, “Understanding Query
Optimization.”
New
Security Features
SQL Server 2012 introduces Database Audit. Similar
to SQL Server Audit, Database Audit is based on the new Extended Events feature
and enables you to audit database-level events or groups of events. For more
information on Database Audit, see Chapter 16, “Security and Compliance.”
SQL Server 2012 also adds user-defined server
roles. These new server roles provide the same type of flexibility that is
available with user-defined database roles, but they are instead defined at the
server level and improve manageability of the permissions at the server level,
giving administrators an option that goes well beyond the rigid fixed server
roles. For more information on creating user-defined server roles, see Chapter
17, “Security and User Administration.”
Contained database support was added in SQL Server
2012 along with the related changes to support authentication to this type of
database. Users can now be added to a database without having a corresponding
SQL Server login. Authentication instead takes place at the database level.
Contained databases can be easily moved from one server to another without the
authentication issues that make noncontained database migrations more difficult.
For more information on configuring contained databases, see Chapter 19,
“Creating and Managing Databases.”
New
Availability Features
One of the key new features in SQL Server 2012 is
the AlwaysOn availability features. The new AlwaysOn features provide SQL
Server administrators more power and flexibility in their efforts toward
providing both high availability and disaster recovery. The AlwaysOn features
consist of AlwaysOn Availability Groups and AlwaysOn Failover Cluster
Instances.
AlwaysOn Availability Groups support a failover
environment for a discrete set of user databases that fail over together. A
single SQL Server instance can host multiple availability groups. In the event
of a failure, each availability group can be configured to fail over to
different SQL Server instances. For example, one availability group can fail
over to instance 2, another availability group to instance 3, and so on. You no
longer need to have a standby server that is capable of handling the full load
of your primary server. An availability group consists of a set of one or more
read-write primary databases and from one to four remote secondary copies. The
remote secondary databases can be set up as read-only copies that you can run
certain backup operations and reporting activity against, taking significant
load off the primary server without the maintenance and overhead of creating
snapshots of the secondary databases.
The other feature that is part of the AlwaysOn
Availability offering is AlwaysOn Failover Cluster Instances. This feature is
an enhancement to the existing SQL Server failover clustering, which is based
on Windows Server Failover Cluster (WSFC). AlwaysOn Failover Clustering
provides higher availability of SQL Server instance after failover. Some of the
improvements of AlwaysOn Failover Clustering over the existing SQL Server
failover clustering include the ability to set up multisite failover clustering
for improved site protection, more-flexible failover policies, and improved
diagnostics capabilities.
For more information on the AlwaysOn Availability
features of SQL Server 2012, see Chapter 43, “SQL Server 2012 AlwaysOn
Features.”
Statistical
Semantic Search
Statistical Semantic Search builds upon the
existing full-text search feature in SQL Server but enables new scenarios that
extend beyond keyword searches. While full-text search lets you query the words
in a document, Semantic Search lets you query the meaning of the document.
Semantic Search attempts to improve document searches by understanding the
contextual meaning of the terms and tries to provide the most accurate answer
from a given document repository. If you use a web search engine like Google,
you are already familiar with Semantic Search technology.
SQL Server Semantic Search builds its indexes using
the indexes created by full-text search. With Semantic Search, you can go
beyond just searching for specific words or strings in a document. Solutions
are possible that include automatic tag extraction, related content discovery,
and hierarchical navigation across similar content. For example, you can query
the index of key phrases to build the taxonomy for an organization, or you can
query the document similarity index to identify resumés that match a particular
job description.
For more information on Statistical Semantic
Search, see Chapter 51, “SQL Server Full-Text Search.”
Data
Quality Services
Within Master Data Services (MDS), SQL Server 2012
adds Data Quality Services. Data Quality Services complements MDS and is usable
by other key data manipulation components within the SQL Server environment.
This feature enables you to build a knowledge base of data rules and use those
to perform a variety of critical data quality tasks, including correction,
enrichment, standardization, and de-duplication of your data. This also
includes the ability to do some basic data profiling to better understand the
integrity and overall data quality state of your core data.
Data Quality Services (DQS) consists of a Data
Quality Server and a Data Quality Client. The DQS Server consists of three SQL
Server catalogs with data-quality functionality and storage. The Data Quality
Client is a SQL Server shared feature that business users, information workers,
and IT professionals can use to perform computer-assisted data quality analyses
and manage their data quality interactively. These DQS Cleansing components can
also be used in Integration Services and MDS.
For more
information on working with Data Quality Services, see Chapter 56, “Master Data
Services and Data Quality Services.”
By Don
Schlichting
Microsoft SQL
Server 2008 R2 is the latest release of SQL Server. This article will introduce
the top 10 features and benefits of SQL Server 2008 R2. The “R2” tag indicates
this is an intermediate release of SQL Server and not a major revision.
However, there are a number of interesting new features for both DBAs and
developers alike. At the time of this article, R2 is available as a CTP
(Community Technology Preview). In addition to new features, there are two new
editions as well, SQL Server 2008 R2 Datacenter and SQL Server 2008 R2 Parallel
Data Warehouse.
Report Builder is
a tool set for developing rich reports that can be delivered over the web. Some
of the features of Report Builder include the ability to create reports
containing graphs, charts, tables, and printing controls. In addition, Report
Builder also supports drill downs and sorting. If you are familiar with the
third party tool Crystal Reports, then you have good idea of what to expect
from Report Builder.
New features in SQL
2008 R2 / Report Builder 3.0 include: Map Layers, which can hold spatial and
analytical data and will integrate with Microsoft Virtual Earth. Indicators,
these are gauges used to show the state of one value. Report Parts, this object
can be reused or shared between multiple reports. Aggregate Calculating, this
allows you to calculate the total value of other aggregate calculated totals.
The new
Datacenter edition of SQL Server 2008 R2 is targeted towards Enterprise Edition
users who require a greater performance platform. The new edition will support
256 logical processors, high numbers of instances, and as much memory as the
operating system will support.
Another new SQL
Server edition, Parallel Data Warehouse, formally codenamed “Madison”,
specializes in handling extremely large amounts of data. This new version uses
massively parallel processing to spread large tables over multiple SQL nodes.
The multiple nodes are handled by a propriety Microsoft technology called Ultra
Shared Nothing. This new technology is described as a Control Node spreading
queries to Computer Nodes, evenly distributed, then collecting the results.
New in SQL Server
2008 R2 is component called StreamInsight. This interesting component allows
streaming data to be analyzed on the fly. Meaning the data is processed
directly from the source stream prior to being saved in a SQL Server table.
This could be extremely handy if you’re running a real time system and need to
analyze data but can’t afford the latency of a committed write to a table
first. Examples usually cited for this application include stock trading
streams, click stream web analytics, and industrial process controls. Multiple
input streams can be simultaneously monitored.
Master Data
Services (MDS) is both a concept and a product. The concept of a Master Data
Service is that there is a central data gate keeper of core business data. Data
items such as customer billing addresses, employee/customer names, and product
names should be centrally managed so that all consuming applications have the
same information. The Microsoft example given is a company that has a customer
address record in the customer table but a different address in the mailing
table. A Master Data Service application would ensure that all tables would
have only one correct address. While an MDS can be a homegrown application, SQL
Server 2008 R2 includes an application and an interface to manage the central
data.
PowerPivot is an
end-user tool that works in conjunction with SharePoint, SQL Server 2008 R2,
and Excel 2010 to process large amounts of data in seconds. PowerPivot works
like an Excel Pivot Table, and includes analytical capabilities.
A Data-Tier
Application (abbreviated as DAC –no idea what the C stands for, and not to be
confused with the Windows Data Access Components also abbreviated as DAC ) is
an object that stores all the needed database information for a project, such
as login, tables, and procedures into one package that can be consumed by
Visual Studio. By creating a Data-Tier Application, a SQL Server package
version could be saved with each Visual Studio build of your application. This
would allow application code builds to be married to a database build in an
easily managed way.
SQL Server 2008
R2 uses a new algorithm known as Simple Compression Scheme for Unicode storage.
This reduces the amount of disk spaced used by Unicode characters. This new
format happens automatically and is managed by the SQL Server engine so no
programming changes are required of the DBA.
The new SQL
Server Utility is a repository object for centrally controlling multiple SQL
Server instances. Performance data and configuration policies can be stored in
a single Utility. The Utility also includes an Explorer tool where multi-server
dashboards can be created.
While the SQL
Server Management Studio could always connection to multiple servers, each was
managed independently with no central view of all of them. Now with SQL Server
2008 R2, Dashboards showing combined server data can be created.
SQL Server 2008
R2 is the latest release of Microsoft SQL Server. The “R2” tag indicates this
is an intermediate release of SQL Server and not a major revision. There are a
number of compelling features in this version for both developers and DBAs
alike. Here are the Top 10 new features in SQL Server 2008 R2:
1. Report
Builder 3.0
2. SQL
Server 2008 R2 Datacenter
3. SQL
Server 2008 R2 Parallel Data Warehouse
4.
StreamInsight
5. Master
Data Services
6.
PowerPivot for SharePoint
7.
Data-Tier Application
8. Unicode
Compression
9. SQL
Server Utility
10.
Multi Server Dashboards
This document
describes what's new in the November CTP of Microsoft® SQL Server® 2008 R2. The
following components have new or enhanced features:
·
Analysis
Services
·
Database
Engine—Manageability
·
Reporting
Services
·
Service
Broker
·
Master
Data Services
·
StreamInsight
If you installed
and used the SQL Server 2008 R2 August CTP, you can use the information in this
document to read about enhancements in the November CTP.
Note Some PowerPivot for
SharePoint features were previously named using the "Gemini"
codename. The codename is being replaced by final names that will be used in
the product release.
For more
information about PowerPivot, see the topics "PowerPivot Overview,"
"PowerPivot Features," "PowerPivot Concepts," and
"PowerPivot Components and Tools" in SQL Server 2008 R2 Books Online.
Numerous
enhancements have been added to the PowerPivot field list. These include the
following:
·
Automatic
detection of relationships when columns from unrelated tables are used in a
PivotTable.
·
Support
for searching fields by name.
·
Support
for named sets.
Support is
available for the following data source editing tasks:
·
Refreshing
data for table, for all tables from a data source, or for all the tables in the
PowerPivot window.
·
Editing
data source information such as server name, database name, and credentials.
·
Editing
table settings such as data source table or query used, columns imported, and
filters.
Targeted support has
been introduced for importing data from the following sources:
·
Microsoft
Access 2003, 2007, 2010
·
Microsoft
SQL Server 2005, 2008, 2008 R2 Analysis Services
·
PowerPivot
workbooks published to Analysis Services running in SharePoint Integrated Mode
·
Text
files
·
Excel
97-2003, 2007, 2010
·
Microsoft
SQL Azure
·
Oracle
9i, 10g, 11g
·
Teradata
V2R6, V12
·
Sybase
·
Informix
·
IBM
DB2 relational databases 8.1
DAX functionality has
been significantly expanded in the following ways:
·
User
interface enhancements include better propagation of errors and easier creation
of DAX formulas.
·
Several
functions have been added, including a set of functions that operate on textual
data, as well as those that provide common Date and Time manipulations.
·
Several
performance and functional capabilities have been introduced, including
automatic recalculation.
·
Support
for handling referential integrity violations between related tables has been
introduced.
·
Automatic
selection of data types for expressions is available.
For more
information, see the topic "Data Analysis Expression Language
Reference" in SQL Server 2008 R2 Books Online.
DAX is also
documented in the online Help of the PowerPivot for Excel add-in. For more
information, see the Help file that is installed with the add-in.
A management
dashboard for IT has been introduced. This dashboard provides visibility into
PowerPivot use on a SharePoint farm that includes information about the
following areas, as well as a rich PowerPivot workbook that can be used to
build custom reports:
·
Published
PowerPivot workbooks, including number of queries executed, number of users and
size per workbook in the dashboard.
·
Hardware
resource utilization for the PowerPivot service, including CPU and memory, is
reported on a daily basis to the dashboard.
·
Data
refresh activity is reported for all PowerPoint workbooks that are accessed
through the same PowerPivot service application.
For more
information about this feature, see the topic "PowerPivot Management
Dashboard" in SQL Server 2008 R2 Books Online.
The following
enhancements to infrastructure are available:
·
Claims-aware
support to enable a user's identity to flow between SharePoint components in a
secure manner.
·
Parallelized
refresh of PowerPivot data.
·
Performance
optimizations.
The ability to
connect to Microsoft SQL Azure Database from the client utilities has been
introduced:
·
The
Generate and Publish Scripts Wizard can use SQL Azure as both the source and
destination for the scripts it publishes.
The November CTP
introduces several new features for the data-tier applications that were introduced
in the August CTP:
·
You
can upgrade a deployed DAC from one version to another.
·
You
can unpack a DAC package to review the contents before using the package to
deploy or upgrade the DAC. Also, the DAC package file is now a zipped file
containing multiple XML files.
·
You
can now register a DAC for an existing database, creating a DAC instance.
The November CTP
provides enhanced functionality for SQL Server Utility features that were introduced
in the August CTP:
·
A
Getting Started page in SQL Server Management Studio.
·
Improved
performance and scale.
·
Improved
workflow for removing a managed instance of SQL Server from the SQL Server
Utility.
·
Sample
scripts for create UCP, enroll instance, and remove instance operations.
The VIA protocol has
been deprecated.
SharePoint
Integration has several new features that include the following:
·
Support
for multiple SharePoint Zones.
·
Support
for the SharePoint Universal Logging service.
·
A
new data extension.
·
A
query designer for SharePoint Lists as a data source.
·
Support
for right-to-left text with Arabic and Hebrew in the SharePoint user interface.
·
In
a report, the data extension supports include data from SharePoint lists for
SharePoint Foundation 2010, SharePoint Server 2010, Windows SharePoint Services 3.0, and Office SharePoint Server 2007.
·
Report
Parts.
Shared datasets
are a new type of report server item that can retrieve data from shared data
sources that connect to external data sources.
Cache refresh
plans let you cache reports or shared dataset query results on first use or
from a schedule.
The November CTP
introduces several new report items that depict data:
·
Sparklines
and data bars are simple charts that convey a lot of information in a little
space. These are often used in tables and matrices. Their impact comes from
viewing many of them together and being able to quickly compare them, instead
of viewing them singly.
·
Indicators
are minimal gauges that convey the state of a single data values at glance.
The November CTP
provides the following enhancements to Reporting Services features that were
released in earlier SQL Server 2008 R2 CTPs or SQL Server 2008:
·
Configuring
a Map Layer—The Map wizard displays data from both the spatial data source and
the analytical data source to help you choose the field on which to match.
·
Calculating
Aggregates of Aggregates—You can create expressions that calculate an aggregate
of an aggregate.
·
Enhanced
Support for Expressions—New globals and a new property for report variables
provide support for overall page numbering, naming pages, and information that
is specified in the renderer.
·
Rotating
Text 270 Degrees—Text boxes can now be rotated 270 degrees.
·
Report
Pagination—Page breaks on tablix data regions (table, matrix, and list),
groups, and rectangles give you better control of report pagination.
·
Naming
Excel worksheet tabs—You can set properties on the report to name worksheet tab
when you export the report to Excel.
Business
Intelligence Development Studio supports working with both SQL Server 2008 and
SQL Server 2008 R2 reports, and with Report Server projects in the SQL Server 2008
R2 version of Business Intelligence Development Studio.
The report server
includes a new management endpoint named T:ReportService2010.ReportingService2010. This endpoint merges the functionalities
of both the T:ReportService2005.ReportingService2005 and T:ReportService2006.ReportingService2006 endpoints, and can support management
operations of the report server in both native mode and SharePoint integrated
mode. It also includes new features that are introduced in SQL Server 2008 R2,
such as shared datasets and cache refresh.
The November CTP
introduces the option to turn on and off poison message handling on a queue. A
queue that has poison message turned off will not be disabled after consecutive
transaction rollbacks. With this feature, a custom poison message handing
strategy can be defined by an application if you want. The Transact-SQL CREATE
QUEUE and ALTER QUEUE statements have been updated, and the IsPoisonMessageHandlingEnabled property has been added to the Microsoft.SqlServer.Management.Smo.Broker.ServiceQueue API.
Master Data
Services is comprised of a database, configuration tool, Web application, and
Web service that you use to manage your organization's master data and maintain
an auditable record of that data as it changes over time. You use models and
hierarchies to group and organize data to prepare it for further use in
business intelligence and reporting tools, data warehouses, and other
operational systems. Master Data Services integrates with source systems and
incorporates business rules to become the single source of master data across
your organization.
Microsoft
StreamInsight is a powerful platform that you can use to develop and deploy
complex event processing (CEP) applications. Its high-throughput stream
processing architecture and the Microsoft .NET Framework-based development
platform enable you to quickly implement robust and highly efficient event
processing applications. Event stream sources typically include data from
manufacturing applications, financial trading applications, Web analytics, and
operational analytics. By using StreamInsight, you can develop CEP applications
that derive immediate business value from this raw data by reducing the cost of
extracting, analyzing, and correlating the data; and by allowing you to
monitor, manage, and mine the data for conditions, opportunities, and defects
almost instantly. StreamInsight samples and documentation can be installed from
the StreamInsight folder on the SQL Server 2008 R2 media.
The
following topics discuss the new features in the SQL Server 2005 components.
Top 10 Features of SQL 2008 R2
Introduction
Report Builder 3.0
SQL Server 2008 R2 Datacenter
SQL Server 2008 R2 Parallel Data Warehouse
StreamInsight
Master Data Services
PowerPivot for SharePoint
Data-Tier Application
Unicode Compression
SQL Server Utility
Multi Server Dashboards
Conclusion
What's New in SQL Server 2008 R2
November Community Technology Preview (CTP)
Analysis Services
PowerPivot for Excel
PowerPivot Field List Enhancements
PowerPivot Data Source Editing
Support for More Types of Data Sources
Enhanced Data Analysis eXpressions (DAX)
PowerPivot for SharePoint
Management Dashboard
Enhanced Infrastructure
Database Engine — Manageability Enhancements
Connectivity to SQL Azure
Data-tier Application Upgrade
SQL Server Utility
Network Connectivity
Reporting Services
SharePoint Integration
Shared Datasets and Cache Refresh Plans
New Data Visualization Report Items
Enhancements to Reporting Services Features
Business Intelligence Development Studio Support for
SQL Server 2008 Reports and Report Server projects
New Web Service Endpoint
Service Broker
Master Data Services
StreamInsight
What's New in SQL Server 2005
Feature Summary
Topic
|
Description
|
Notification Services is
a new platform for building highly-scaled applications that send and receive
notifications. Notification Services can send timely, personalized messages
to thousands or millions of subscribers using a wide variety of devices.
|
|
Reporting Services is a
new server-based reporting platform that supports report authoring,
distribution, management, and end-user access.
|
|
Service Broker is a new
technology for building database-intensive applications that are secure,
reliable, and scalable. Service Broker provides message queues the
applications use to communicate requests and responses.
|
|
The Database Engine
introduces new programmability enhancements such as integration with the
Microsoft .NET Framework and Transact-SQL enhancements, new XML
functionality, and new data types. It also includes improvements to the
scalability and availability of databases.
|
|
SQL Server 2005
introduces improvements in the programming interfaces used to access data in
SQL Server databases. For example, the SQL Native Client data access
technology is new, and the .NET Framework Data Provider for SQL Server, also
referred to as SqlClient, is enhanced.
|
|
Analysis Services
introduces new management tools, an integrated development environment and
integration with the .NET Framework. Many new features extend the data mining
and analysis capabilities of Analysis Services.
|
|
Integration Services
introduces a new extensible architecture and a new designer that separates
job flow from data flow and provides a rich set of control flow semantics.
Integration Services also provides improvements to package management and
deployment, along with many new packaged tasks and transformations.
|
|
This section describes
the programmability enhancements that have been made to Full-Text Search.
These enhancements include the data definition language for Full-Text Search
and the ability to specify languages in queries other than the default
language, and the manageability enhancements, such as side-by-side installs,
backup and restore full-text catalogs, and attach and detach full-text catalogs.
|
|
Replication offers
improvements in manageability, availability, programmability, mobility,
scalability, and performance.
|
|
SQL
Server 2005 introduces an integrated suite of management and development
tools that improve the ease-of-use, manageability, and operations support for
large scale SQL Server systems.
|
x