I just finished up a three week tour of Canada teaching Microsoft Partners about the new SQL Server 2008 Developer and Business Intelligence features. My tour consisted of four day classes starting in Calgary, moving on to Toronto with a final stop in Montreal and was part of Microsoft Corporate Metro early adopter program. The SQL 2008 Dev/BI features resonating most profoundly with the classes are as follows:
- TSQL - The speed and functionality of the new TSQL capabilities such as Merge, Table Value Parameters and Row Constructors. These enhancements allow for consolidation from what used to take multiple TSQL statements into a much more efficient and powerful command stream.
- Non-relational extensions - Sparse columns allow for flexible storage of values as a solution for name value pair scenarios. The Filestream type is another non-relational extension that makes storage of large blobs efficient. Most of these extensions are implemented through SQLCLR types and also include Spatial data types, Hierarchy types and improvements in the XML type.
- Client programming – The Synch services in SQL Server 2008 allow for synching client apps in a centralized or peer to peer manner without the overhead of Merge Replication. By using the new Change Data Capture engine capabilities in conjunction with Synch Services a less obtrusive merge technology is available. The new SQLCE 2008 plays into this scenario. LINQ to SQL and the Entity Framework, which will ship simultaneously with SQL 2008 as part of Visual Studio 2008 SP1, are also compelling client programming features.
- Reporting Services (SSRS)
1. Design – Major overhaul of the Report Designer built into Visual Studio 2008. Even though it is completely new, it is consistent with the previous designer so that the learning curve is not steep at all. The designer is much better organized so that groupings can be easily edited and data, parameters, images and built-in fields are all part of a single data window. Property builders, been completely rewritten in an Office 2007 style and are much easier to use. Data Sources and Datasets interfaces follow this theme and allow for accessing datasets from other reports along with Report Models as data sources. The Tablix control is now a central core for the Table, Matrix and List controls and allows for very flexible placement of groups and totals. You can start with a Matrix and turn it into Table or List and vice versa, they are simply a Tablix control now with different property settings. Rich data visualizations have been added with a beefed up Chart and new Gauge/Indicator controls. A new Report Designer client will be available sporting an Office 2007 interface and giving local report generation with about the same power as the Visual Studio designer, however, it is stand alone.
2. Administration – SSRS can now optionally be run as HTTP Endpoints within SQL so that Internet Information Services (IIS) is no longer a necessary component. This allows for better memory management and creates several new interfaces to administer what used to be part of IIS. There are also new memory throttling settings, improvements in the rendering engine (including rendering to Word). Management tools of SQL Management Studio, Report Manager and SSRS Configurations are now more focused in their implementation.
3. Programmability – The Report Viewer Control has new capabilities such Examples in Expressions, Print extensions and is central to a new Visual Studio 2008 Report Application template. The Web Service and URL Access have been modified for rendering improvements and there is a new Windows Management Instrumentation interface
- Integration Services (SSIS) – The integration pipeline has been streamlined and performance enhanced with several new Tasks, Sources, Destinations and Transformations added. The Script Task is now a Visual Studio Tools for Applications (VSTA) implementation with full support for .Net references including Web Services and is now pre-compiled for speed. There is a Cache Transform that allows for caching lookups including putting lookups into flat files that can be distributed with the package. New ADO.Net Source and destinations extend the Extract and Load functions. A new Data Profiler Task really extends the Data Inspection function. You can now go way beyond Data Viewers to analyze your Extracts with a Profiler Viewer that gives a plethora of data pattern analysis. Additionally, guidance is given on how to use the new SQL 2008 Change Data Capture (CDC) and Merge technologies to create Slowly Changing Dimensions (SCD).
- Analysis Services (SSAS) – Building on the usability of Analysis Service Cube technology there are new Cube and Dimension Wizards that enhance the cube design process. The Cube designer has a new tab for Aggregations, with a new Aggregation Designer allowing for multiple saved Aggregation designs and a much more granular settings capability of what and how much gets aggregated. The Dimensions Designer has a new tab for Attribute Relationships with its own powerful visual designer with visualization of Rigid and Flexible relationships, no more setting these up inline in the Attributes list. Attribute key columns are easier to access through the properties window. All along the way there are Best Practice Warnings guiding you on how best design the cube, however, don’t worry because you can turn the warnings off if you don’t agree with them. There is a new Personalization Extensions (ASPE) capability through the creation of Analysis Services Stored Procedure assemblies allowing for a secure personalization of individual Users/Groups access to specific objects.
This is just a few of the favorite things, there are even more! I barely touched on Engine enhancements. We’re investigating into turning the above into a two Hands On Seminar that might be coming your way shortly.