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 didnt 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 packages.
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 database.
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 performance.
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 uninstall options.
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 applications.
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 stored procedure.
Spatial Data. Data type for storing Latitude, Longitude, and GPS entries.
Full Text Search. Native Indexes, thesaurus as metadata, and backup ability.
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 computations.
SQL Server Reporting Server. Improved memory management and better rendering.
Microsoft Office 2007. Use OFFICE as an SSRS template. SSRS to WORD.
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 2000 Express.