SQL Server 2005

Last week I attended the Microsoft Architect Council where several speakers presented new/updated MS products built on SQL Server 2005.  Here are the highlights.

SQL Server Mobile – Kevin Collins

• Syncs w/ SQL Server  using Merge Replication w/ column tracking (e.g. pictures don’t sync every time a row changes) – actually a feature of SQL Svr 2005 and usable by SQL Express
• Syncs over HTTP/S with compression
• Multi-user (more than one app at a time, background sync)
• Based on .Net Compact Framework 2.0, ADO.Net 2.0
• Histograms for indexed columns – improved performance over SQL Server CE
• SQLCEResultSet – direct cursor mechanism – don’t use DataSet b/c causes double/triple buffering
• Create large DBs on desktop & dist to devices via file rather than long sync for each client

SQL Server Integration Server (www.sqlis.com) – Donald Farmer

• Collect/Analyze/Report
• Motivations: 24/7 business, greater volumes of data (e-commerce, RFID, call-center)
• Before Integration Services – doesn’t scale
  • separate, staged operations for different sources
  • different applications for each type of data
  • then warehousing, then data mining
• With Integration Service
  • data transformation, cleansing, merging, mining all done in single auditable process
  • warehouse separately for auditing
  • gen reports and access via mobile out of merged data, parallel w/ warehousing
• Components: sources, transformations, destinations
  • Source Adapters: .Net code writes app data into internal buffer format (effectively a row set)
  • Transformations: row set operations (union, intersection, sorting, etc.) and *fuzzy grouping*
  • Destination Adapters: translate buffer format to external apps
• Performance: proven to scale to 117 GB/Hr, 64bit allows operations on enormous datasets (e.g. MSN click-tracking)
• Case Study – Barnes & Noble: 1.6TB raw data => 50% less dev time vs. Oracle, 400% improved perf, more meaningful, up-to-date info for stock levels
• Graphical description of data processing flow, transformations, output, visualization w/ drill-down to composite flow charts
• Interactive selection/data profiling
• Note: isn’t designed for streaming information such as live click-stream.  Can use a polling mode and get small batches.  Also can pull data from SQL server using a built-in SQL Server adapter w/o .Net coding.

SQL Server 2005 Reporting Services - Brian Walker, Program Manager

• Uses ADO.Net driver and any DB supported by that
• Nice drag-n-drop interface to building a queries and setting up filtering, ditto for visual design of reports
• New data-picker control in next beta
• Sorting can be done with nested groups – re-sorts with a click of the sort control appearing on the report
• Headers stay on page with scrolling through the report
• Improved expression editor with syntax checking using a .Net expression language (defaults to VB.Net)
• SP2 will contain a set of WebParts for use with Sharepoint (ReportExplorer and ReportViewer, which can be linked together automatically)
• Visual Studio 2005 will have a report viewer control so apps can connect to Reporting Server or local databases (doesn’t require SQL Server)
• Direct-print (via ActiveX control) where server gens report and browser routes to printer (adjustable margins, etc. during run-time)
• ReportBuilder: design tool for business user
  • click-once app running as low-privileged user
  • template-based with drag-n-drop table/field assignment
  • auto-totaling and sub-totaling is easy
  • the “model schema” is sent to server and translated into a query by the server
  • Model Designer tool can public models to the Report Server
  • Part of SQL Server license but require Report Server running