SQL 2008, with a scheduled release of Quarter 1 in 2008,
includes a variety of new features, improvements, and additions. This article
will review new Data Integration features, enhancements to Analysis Services,
Reporting Server additions, and Office integration.
SQL Server Integration Services
SSIS (SQL Server Integration Services) is a built in
application for developing and executing ETL (extraction, transformation, and
load) packages. SSIS replaced SQL 2000 DTS. Integration Services includes the
necessary wizards, tools, and tasks for creating both simple import export
packages, as well very complex data cleansing operations. SQL Server 2008 SSIS
includes a number of improvements and enhancements such as better parallel
execution. In SSIS 2005, the pipeline didn’t scale past two processors. SSIS
2008 will scale past two processors on multiprocessor machines. Also, the
newly redesigned pipeline improves performance on large packages that contain
long sub-trees. In addition, the SSIS engine is reported to be more stable
with fewer incidents of deadlocks.
The Lookup component has been improved. Lookups are a very
common SSIS operation that fetches a related piece of information. Such as a
lookup obtaining the Customer Name from the CustomerID and brining that value
into the dataset being worked on. Because Lookups are very common in SSIS and
can be performed on large million row datasets, performance could be poor.
Improvements have been made in SQL 2008 to increase performance. In addition, Lookups can be done
on a variety of data sources including ADO.NET, XML, OLEDB, and other SSIS
SQL 2008 includes the TSQL command MERGE. Using this
statement allows a single statement to UPDATE, INSERT, or DELETE a row
depending on its condition. The example below demonstrates the MEGRE being
used to combine a new list of Inventory Item descriptions into the existing
Inventory Master. In addition to new Descriptions, there are some new parts
included in the NewInventory table. Without the Merge statement, two commands
would need to run. The first would look for a match then upgrade the
Description. The second statement would look for non matches and then INSERT.
With MERGE, one statement can perform both tasks as shown below.
MERGE InventoryMaster AS im
USING (SELECT InventoryID, Descr FROM NewInventory) AS src
ON im. InventoryID = src. InventoryID
WHEN MATCHED THEN
UPDATE SET im.Descr = src.Descr
WHEN NOT MATCHED THEN
INSERT (InventoryID, Descr) VALUES (src. InventoryID, src.Descr);
A number of improvements and enhancements have been made to
SSAS (SQL Server Analysis Server). The BI Stack has been improved for
increased performance. Commodity hardware can be by utilized by scale out
management tools. Also, Block Computation can provide significant performance
improvements in cube analysis.
Processing and performance have been improved in SSRS (SQL
Server Reporting Server). Large reports will no longer consume all available
memory. In addition, there is greater consistency between layout and render. Also,
the TABLIX, a cross between a table and a matrix is included in SQL SSRS 2008.
Application Embedding allows URLs in reports to point to a calling application.
Microsoft Office 2007
SQL Server 2008 can tightly integrate with Microsoft Office
2007. For example, in SQL Server Reporting Server reports can now export
directly to Word. In addition, both Word and Excel can be used as templates
for SSRS reports by using the Report Authoring tool. Excel SSAS performance
has been improved and there is a data mining add-in.
SQL Server 2008 contains many new features and enhancements,
a large numbers of which were not covered in this series. Additional
information can be found at the main SQL 2008 Microsoft page: http://www.microsoft.com/sql/2008/default.mspx.
Listed below is a concise bulleted list of the SQL Server 2008 features
reviewed in this series.
Transparent Data Encryption. The ability to encrypt an entire
Backup Encryption. Executed at backup time to prevent tampering.
External Key Management. Storing Keys separate from the data.
Auditing. Monitoring of data access.
Data Compression. Fact Table size reduction and improved
Resource Governor. Restrict users or groups from consuming high
levels or resources.
Hot Plug CPU. Add CPUs on the fly.
Performance Studio. Collection of performance monitoring tools.
Installation improvements. Disk images and service pack
Dynamic Development. New ADO and Visual Studio options as well
as Dot Net 3.
Entity Data Services. Line Of Business (LOB) framework and
Entity Query Language (eSQL)
LINQ. Development query language for access multiple types of
data such as SQL and XML.
Data Synchronizing. Development of frequently disconnected
Large UDT. No size restriction on UDT.
Dates and Times. New data types: Date, Time, Date Time Offset.
File Stream. New data type VarBinary(Max) FileStream for
managing binary data.
Table Value Parameters. The ability to pass an entire table to a
Spatial Data. Data type for storing Latitude, Longitude, and GPS
Full Text Search. Native Indexes, thesaurus as metadata, and
Reporting Server. Improved memory management.
SQL Server Integration Service. Improved multiprocessor support
and faster lookups.
MERGE. TSQL command combining Insert, Update, and Delete.
SQL Server Analysis Server. Stack improvements, faster block
SQL Server Reporting Server. Improved memory management and
Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to
SQL 200 Support Ends. Mainstream Support for SQL 2000 is coming to an end.
Feature and benefits included in Mainstream Support include the ability to
submit requests for product feature changes, Security Updates, Non Security Hotfixes,
Complimentary support, and Paid Support. This Mainstream Support will expire on
4/8/2008 for SQL Server 2000 64-bit Edition, SQL Server 2000 Developer, SQL
Server 2000 Enterprise, SQL Server 2000 Standard, SQL Server 2000 CE, and SQL
Server 2000 Workgroup Edition. Extended Support, consisting of Security Updates
and Paid Support will continue until 2013. A full description
of support phases can be found at these Microsoft URLs: “Microsoft
Support Lifecycle” and “Microsoft
Support Lifecycle Policy FAQ”. Many resellers will discontinue selling SQL 2000 in December of
2007. Also, no version of SQL 2000 will be supported on Vista, including SQL