Dan Rigsby - Coding Up Style

IndyPass is the Indianapolis Professional Association for SQL Server.  Tonight they celebrated their 3rd Birthday Bash. The speaker was Tom Pizzato, Data Platform Architect, at Microsoft.

John Magnabosco kicked us off with the introduction.  He gave the usual announcement events, board elections, etc . John ended by asking for job announcements, but was interrupted by the famous Buck Foley.  Buck is a “Matt Foley” impersonator (the ‘van down by the river’ character played by Chris Farley on Saturday Night Live), and the actor is a ‘Database Guy’ by profession.  He brings his own flavor of humor and applies it to SQL technologies. Buck seemed to be under the impression that he was the main speaker and not Tom Pizzato who he called “Pizza-Toe”.  He winged his talk this time, but it was still humorous and set a light hearted tone for the rest of the meeting.

Tom’s talk was broken into two sections: a detailed talk about new features followed by a series of demos.  The talk mainly went over all of the new features and changes in SQL Server 2008.

Tom’s Talk

Amazing Journey

Tom has worked with SQL Server for close to 19 years.  He started his talk by taking us a down memory lane by showing some of the original disks for the first version of SQL Server back when it was just a OS2 port of Sybase.  He also  picked fun of the fact that there was a launch event for a product that has not yet been released. A few items of interest from his introduction were:

• Microsoft is still on track for a Q3 launch of SQL Server 2008, but skill couldn’t give us a more specific date yet.  The next version of SQL Server will be in 2010 (at least that is where it is planned).
• FileStream data cannot be saved to a separate machine in SQL Server 2008.  it is still limited to the machine running SQL Server 2008.  Microsoft knows this is a limitation, but they chose to just get the feature out there first.

Protect your Information

• Secure Access
  • Strong Authentication
  • Granular Authorization
  • Transparent Data Encryption (TDE)
  • External Key Management
• Protect System
  • Secure Deployment
  • Secure by default: This ones  is going to be a pain for some people.  Secure by default means everything is turned off by default.  Microsoft is taking this mind set into all of its major projects.  I have talked about this before in terms of Wcf, but we also see it in Windows Server 2008 where almost no features are installed by default.

Ensure Business Continuity

• Ensure Availability
  • Mirror your data: Automatic repair of primary from backups
  • Failover clustering
  • Replicate your data
• Minimize Downtime
  • Online Operations: More to help you keep your system up including, hot swapping CPUs while running in production
  • Add system resources
  • Reduce Recovery time
• Concurrent Access
  • Database Snapshots: Not many changes here
  • Snapshot Isolations

Predictable Response

• Optimize Performance
  • Efficient Data Storage: Finally Compression at the data and backup levels. in SP2 for SQL Server 2005, the vardecimal datatype was introduced which helped open the doors for this feature.
  • Prescriptive Guidance
• Analyze Performance
  • Data Collection: Collect reports instead of wiping them
• Predict Performance
  • Plan Freezing: Allow you to save query execution plans to xml that can be run on other machines.
  • Resource Governor: Set up resources into pools and assigned users to them.  Such as giving 70% CPU and memory to a power users and  rest to non-power users.

Reduce Management Cost

• Flexible Administration
  • Visual Management: Management Studio improvements, Powershell integration, etc.
  • Automate Operations
• Manage centrally
  • Enterprise Policies
  • Monitor Compliance
• Monitor Health
  • Management Reports
  • Performance Tuning Tools

Simplify Development

• Easy Access
  • Model Entities: Models can be built off multiple tables such as a “User” entity that spans multiple tables.  LINQ can then be used to query over this data. This is what is formally called LINQ to Entities.
  • Integrated Query: Most of the LINQ stuff is slightly slower, but it is a trade off of getting the code done faster development vs. performance.  This is also know as LINQ to SQL.
  • Comprehensive connectivity
• Program data logic
  • Time aware applications: Introduced separate Date and Time fields.
  • Pass data results
• Sync your data
  • Local data storage
  • Offline Synchronization

Store your information

• Unstructured Information
  • File Storage
  • Search Text
  • Extensible Types
• Geospatial Information
  • Spatial Standards
  • Visualize Location
• Structured information
  • Model complex hierarchies: No more need to use CTEs to recursively query data to build hierarchy trees.  The new Hierarchy data type can store these values
  • Enhanced Partitioning

Integrate and Manage data

• Build Faster
  • Connect to data
  • Cleanse Data
  • Integrate data
• Manage Efficiently
  • Efficient Data Storage
  • Mange mixed workloads
  • Streamline Aggregates
• Scalable Performance
  • DW Query Optimizations: Lots of optimizations in performance of Star joins
  • Enhanced Partitioning: Easier to align partitions with their indices.

Deliver Relevant Reports

• Author reports
  • Enhanced Designers: Licensed technology from Dundas to create better and more professional looking reports.
  • Flexible Layout: Introduced Tablixs.
• Manage reports
  • Enterprise Platform: SSRS was rewritten to not require IIS and scale better in the enterprise
  • Server-based Deployment
• Deliver reports
  • Rich Visualizations
  • Render in office
  • Share reports

Drive Actionable Insight (Analysis Services)

• Build Analytical Solutions
  • Scalable Platform
  • Prescriptive Guidance
• Extend Reach
  • Manage KPIs: How many Microsoft tools report KPIs?
  • Predictive Analysis
• Deliver Insight
  • Work in Office: Data mining add-ins for office allow you to mine and report on data directly from Office tools
  • Business view of data

Tom’s Demos

Demos were done using SQL Server 2008 CTP 6.  Tom had 9 demos to show but only got to 6 of them.  The demos were run from a custom application that had the queries and results all displayed in a custom window.  This was obviously a pre-canned set of demos or at least a pre-canned tool, but it was a great way to display these features.  I know it took a lot of time to put together,

Auditing

• Auditing performance can eat up to 10%
• Audit logs are stored in a separate file on the disk.  These files are not xml or directly human readable, but at least they can’t be queried like any other table.
• Audit logs can be maintained by setting up rollover files and file sizes

Compression

• Compression types can be set at the row or page levels

Powershell

• Only works for the relational engine
• Can right click on teh database in management studio and say “Open Powershell”
• Once in powershell you can say “dir”and get the list of database objects including being able to drill into tables, users, etc. 
• You could even run more complex Powershell queries such as the ability to get tables where row count is > 1000 like:
  • dir | where < $._RowCount -gt 1000
• You can pipe results out to HTML or other mediums

Freezing Plans

• Gives you the ability to take a query execution plan and save it to XML
• Can then specify when plan to use when executing a query

New DateTime Datatypes

• Separate Date and Time datatypes
• A new DateTimeOffset datatype supports years 0001 to 9999 as well as storing time zone information
• A new DateTime2 datatype supports years 0001 to 9999

Table Value Parameters and Merge

• This was the last feature dropped from SQL Server 2005
• A new MERGE statement allows merging of rows from multiple tables. You can choose to update matched rows, insert unmatched rows, or delete unmatched rows from the primary table.

Prizes

I know a lot of people probably only came here for the prizes, and there were some great giveaways again this year.  There was an Xbox, a 30 gig Zune, two 8 gig Zunes, and a slew of books and other swag.  As usual I didn’t win anything, even though the odds were pretty good.  But I came here for the event more than the prizes

Final Thoughts

I do miss having Bill Baker at the Birthday Bashes, but Tom has been filling in nicely. He is a good speaker, is able to answer most questions, and is very competent in his skills. He was asked some detailed questions about LINQ to SQL and LINQ to Entities that he couldn’t answer, but he wasn’t afraid to punt on them and commit to getting Joe Healy to come to talk about LINQ at a future meeting.  I am looking forward to future events at IndyPASS.