SQL Server, VSDBPro No comments yet

SQLPASS Conference Day 2

By on October 14th, 2011

Today started out with a keynote from Quentin Clark and he talked about what he termed the “Fantastic 12 of 2012” reasons :

  1. Required 9′s & Protection – Integration Services is now Integration Server; HA for StreamInsight; AlwaysOn
  2. Blazing-Fast Performance – Column Store Indexes; improvements across the board
  3. Rapid Data Exploration – Power View + PowerPivot; Admin from SharePoint; Reporting Alerts
  4. Managed Self-Service BI – same as #3
  5. Credible Consistent Data – BI Semantic Model (BISM); Data Quality Services (DQS);Master Data Services (MDS)
  6. Organize Compliance – Expanded Audit (user defined and filtering); User Defined Server Roles
  7. Peace of Mind – Production-simulated distributed testing; System Center Advisor & Management Packs; Expanded Support – Premier Mission Critical
  8. Scalable Data Warehouse – SQL Server Appliances; HW + SW + Support – Just add Power; choice of hardware
  9. Fast Time to Solution – Same as #8
  10. Extend Any Data, Anywhere – Greater Interoperability, New Drivers for PHP, Java and Hadoop; ODBC Drivers for Linux & CDC for SSIS and Oracle; Beyond Relational: FileTable, 2D Spatial, Semantic Search
  11. Optimized Productivity – SQL Server Data Tools (SSDT); Unified Across Database & BI; Database & Targeting Freedom
  12. Scale on Demand – AlwaysOn; Deployment across Public & Private; Elastic Scale

We saw demos of AlwaysOn, Column Store Index, Data Quality, and Semantic Search, plus they showed off the SQL Server Appliances.  The thing that really stood out to me in the keynote was the Semantic Search Demo.  The presenter took two pdf files and utilizing TSQL was able to relate them conceptually.  Looked very cool, you can go here for more info.

After that I attended Matt Masson’s (twitter | blog) session on Top 10 Things You Didn’t Know About SSIS in SQL Server Code Name “Denali”.  These were items that are post CTP3.  To kill some time while waiting for the session to start Matt went through a mock scenario of utilizing SSIS to create virtual friends to party with him and his cat (Mr. Wiggles) on Facebook using Facebook Services and StreamInsight Transform – it was absolutely hilarious!

  1. Change Data Capture – as someone who has done CDC in SSIS this was very cool
  2. ODBC Support
  3. Connections Managers – Shared In-Memory Cache Across Packages using the shared project level connection manager
  4. Flat File Source Improvements – supports varying number of columns and can parse embedded qualifiers
  5. Package Format Changes – doesn’t persist default values; doesn’t use lineage id’s, instead uses ref id’s; sorted by name
  6. Visual Studio Configurations
  7. Scripting Improvements – can now set a break point in a script task/component and see data
  8. Troubleshooting & Logging – Built-in reporting; Component Timing & Row Counts; Server level logging
  9. Data Taps – can write out data to a csv location without code change (doesn’t log binary)
  10. Server Management using PowerShell
  11. Honorable Mention – Project Barcelona Metadata Discovery – crawler that crawls entire MS stack to determine Impact Analysis and Data Lineage

These were on top of all the other things we new about for SSIS in SQL 2012, and I have to say that I’m really looking forward to it!

Next I went to the Lighting Talks which is a cool idea.  The premise is to have a number of speakers, this one has seven.  Each speaker however is limited to 5 minutes and there is someone utilizing a timer and the crowd is encouraged to clap people off stage that go to long.  It was a lot of fun.  The two that really stood above the rest (in my opinion anyway) were Grant Fritchey’s (twitter | blog) “Backup Testing, The Rant ” and Niko Neugebauer’s (twitter | blog) “Build Up”.

Last was Database Development with SQL Server Data Tools Code-Named “Project Juneau” by Gert Drapers (blog).  As someone who has used Database Professional extensively, I was pretty skeptical going in since in that Unit Tests aren’t in SSDT.  As you would expect one of the big features is that it has support for Azure.  However some of the stuff that I did see was really cool:

  • A True Debugger with break points and a Call Stack, with the ability to start in the .NET app
  • Refactor can now reach into the application
  • True Language Services – can right click to go to reference, etc.
  • Drift Detection – if working in a connected stated it will poll database to see if any changes happened behind you and you can just drag and drop changes into project
  • Easier way to save specific deployed versions
  • Table designer where you see both code and the designer window
  • SQL CLR is within the same project, no longer have to create a separate CLR project

Some things I still have to think if I like or not:

  • Renamed Deploy to publish so that Projects are now Published
  • Multiple objects are now in a single file, for example the indexes, keys are now defined in the create table script file

And the things I don’t like, until vNext Visual Studio + 3 months we’re missing:

  • Data comparison
  • Data Generation
  • Unit Testing

Now for the great news.  It’s completely FREE!!  It will be delivered as part of the SQL Server install as well as part of the Visual Studio install or you can get it through  the Web Platform Installer to get it!  We’ve come a long way from requiring TFS 2005 Team System.  Looks like we are getting closer to a standard SQL Server development environment.  That was it for day 2, next up is the 3rd and final day.

SQL Server 2 comments

SQLPASS Conference Day 1

By on October 13th, 2011

First day of the official conference started off with Rushabh Mehta (blog | twitter) talking about where SQLPASS is on their 5 year goals:

  • Provide 1 Million hours of education, 430k hours provided so far
  • Grow to 250k members, 80k so far
  • 5 Global regions, 1 so far

PASS Summit is the largest event focused on SQL and BI:

  • 189 Sessions, 5 tracks (57 MS, 11 CAT)
  • 204 speakers (93 MVPS, 11 MCM)
  • SQL Server Clinic : SQLCAT architects, CSS engineers
  • Expert Pods: MS Engineers, MVPs, 9 focus areas
  • Over 5000 registered attendees

Ended by stating the purpose it to

Build connections that will last you a lifetime

He then introduced Ted Kummert with guests Denny Lee (blog | twitter) and Amir Netz (twitter).  Announcements:

  • Denali will be released the first half of 2012 and will be called SQL 2012
  • Project Juneau office title is SQL Server Data Tools
  • Project Crescent official title is Power View
  • Apache Hadoop-based distribution for Windows Server and Windows Azure
  • ODBC Drtiver and Add-in for Excel, both for Apache Hive
  • JavaScript Framework for Hadoop
  • SQL Server and SQL Server PDW connectors for Apache Hadoop
  • Partnering with hortonworks

Ted went on to describe the focus going forward:

  • Any data, any size, anywhere
  • Manage and process data of all types
  • Mission-critical scale from on premises to cloud
  • Common  management and development

Lastly they demoed a product codenamed “Data Explorer” which was simply described as a way to enable data discover, enrichment and publishing.  They actually demoed it utilizing the Metro UI and showed the ability to do a 5 way join between SQL Azure, Excel, Data Market and WCF calls.  The other thing that made this compelling was that it enables interactive mobile reporting with demo’s on an iPad, Samsung Table, Windows Phone and Windows Slate.

After that I went to a Hands on Lab to look at the new SQL 2012 Always on functionality (yes it is turned off by default).  Got my copy of SQL MVP Deep Dives vol 2 and ended up in Adam Jorgensen (blog | twitter) session called Zero to Cube – Fast Track to SSAS Development.  Adam is just awesome.  He’s come up to our SQLSaturday in Chicago and presented and if you have a chance to see a session or just talk to him, do it.  Here’s his picture:

Image

T-SQL No comments yet

PASS Summit PreCon 2

By on October 12th, 2011

Day 2 of the SQLPass Summit I attended Advanced T-SQL for SQL Server 2008 and Denali presented by Itzik Ben-Gan (twitter | site), this was rated a 499 Session, so not for the faint of heart.  For those of you that don’t know who Itzik is just take a look at this site and what books he authors. In short he’s one of the premier T-SQL experts.

Here was the Agenda:

  • APPLY Magic
  • Grouping Sets
  • TOP / OFFSET-FETCH
  • Sequences
  • Windows Functions
  • Intervals
  • Other T-SQL Improvements in Denali

First a truism – SQL is a set based language, the concepts and logic that exist are designed for set based activities.  He also went over how the Query is written:

  1. SELECT
  2. FROM
  3. WHERE
  4. GROUP BY
  5. HAVING
  6. ORDER BY

vs. how it’s processed:

  1. FROM
  2. WHERE
  3. GROUP BY
  4. HAVING
  5. SELECT
  6. ORDER BY

The session was, as you can imagine, pretty intense.  Itzik obviously loves what he was talking about and is very passionate.  He talked about how to improve standard SQL as well for migrations between other SQL implementations.

As far as some of the new features of T-SQL in Denali that was mentioned:

  • OFFSET / FETCH (think paging)
  • Sequences (independent identity objects that can be referenced and keep proper seeding)

Image

  • Windows Functions (this set functions) – these have been expanded tremendously to include things like LAG, LEAD, FIRST_VALUE, LAST_VALUE, distribution functions (PERCENT_RANK, etc.), ROWS and RANGE 
  • Conversion – PARSE, TRY, TRY_CONVERT (returns NULL if can’t convert), TRY_PARSE
  • Date and Time – EOMONTH (end of month), DATEFROMPARTS and similar functions to build date, time and datetime data from different parts
  • Logical – CHOOSE, IIF – these were added to improve Access migrations
  • String – CONCAT this is used for string concatenation, similar to the + operator, however it will automatically convert NULL to to strings; FORMAT – this brings in .NET format functionality, however it’s slower than native SQL formatting (probably because it utilized CLR behind the scene)
  • Mathematical – LOG now supports the ability to indicate the base
  • Improved Error Handling – THROW, this finally allows us re-throw the original error to be bubbled up.  Until now we’ve had to utilize user errors
  • EXECUTE WITH RESULT SETS – This allows us to call an SP and guarantee the shape of result set.  We submit an expected result set shape which if the return set doesn’t match SQL will try to implicitly convert to, however it could fail if it can’t return what is specified.
  • Metadata discovery – removes the SET FMTONLY – this allows us to interrogate and return the metadata
  • FORCESCAN and FORCESEEK – this allows us to force an index seek or index scan for a particular query

Another great session and it’s cool to see the advancements being made within T-SQL.  More to come later.