Browsed by
Category: SQL Server

Announcing the new ITUnity web show: BIFocal

Announcing the new ITUnity web show: BIFocal

BIFocal LogoI was sitting in the speaker room at SharePoint Fest Seattle having a Skype chat with John White. We were collaborating on some Business Intelligence content that we could deliver at conferences either together or independently, but still ensure that we were providing a consistent message. At some point during this chat one of us suggested that this amusing & informational back and forth that we were having would make for a pretty decent podcast. Dan Holme, CEO of ITUnity, overheard the comment and said “It would make an even better web show on ITUnity!” Thus an idea was born.

I am excited to announce that a mere 5 months later we are ready to go live with our new monthly web show, BI Focal. We are going to keep you up to date on all of the latest news in the BI space, provide Tips & Tricks, have interesting guests, take viewer questions, and even do some cool demos.

We hope that you will join us for our inaugural show “Select * from Hosts” on January 28th, 2016 at 1:30PM Eastern Time on ITUnity. Check out our promo below:

SQL Server 2012 SP1 is here & it’s huge for SharePoint 2013 BI

SQL Server 2012 SP1 is here & it’s huge for SharePoint 2013 BI

This is a massive deal for SharePoint 2013 BI.  Here is an excerpt from What’s New in SQL Server 2012:

Business Intelligence highlights (with SQL Server 2012 SP1, Office and SharePoint Server 2013 )

Business Intelligence highlights (with SQL Server SP1, Office and SharePoint Server 2013 Preview) are:

  • Enable self-service BI as a natural part of users day-to-day activities in Excel 2013:

  • Access and mash-up data from any source (PowerPivot). Documentation related to PowerPivot in Excel 2013 (

  • Stunning visualizations and data discovery (Power View). Documentation related to Power View in Excel (

  • Work with hundreds of millions of rows of data (powered by xVelocity in-memory technologies).

  • Discover, assess and audit user created spreadsheets via SharePoint Server 2013 Preview.

  • A new version of the Reporting Services add-in for SharePoint and an updated SharePoint mode report server that supports SharePoint 2013. For more information, see the following:

  • A new architecture for SQL Server 2012 SP1 CTP4 PowerPivot that supports a PowerPivot server outside a SharePoint 2013 farm. A Windows Installer package (spPowerpivot.msi) that enhances the PowerPivot for SharePoint experience. Additional features include PowerPivot Gallery, schedule data refresh, and management dashboard. For more information, see the following:

  • Share and collaborate on self-service BI assets via SharePoint Server 2013 Preview and SQL Server 2012 SP1.”

The key takeaway and true game changer is that the SQL SSAS engine for PowerPivot is no longer required to be on a SharePoint box.  This means you no longer need a SQL license for your SharePoint App server to be able to run PowerPivot.  Don’t miss understand, you still need to license SQL to run PowerPivot, but you can do it on a separate box that can support multiple SharePoint farms and multiple Tabular BISM solutions.  Pretty huge shift.

You can download SQL Server 2012 SP1 & SQL Server 2012 SP1 Feature Pack from these links.

Enjoy… I know I will!

The SQL Server 2012 Launch is almost here!

The SQL Server 2012 Launch is almost here!

We are a mere 2 days away from the Virtual Launch of SQL Server 2012 and there is a ton of great information & free training (and some pay for) that is primed and ready to go for us to digest. Here is a list of some of the events that you can partake in:


This is it people! What we have been waiting so long for has finally arrived. There is a full day of speakers in multiple tracks to enlighten us on all that is new and wonderful in this version of SQL Server.

Date & Time: March 7th, 2012 @ 11a Eastern Time
Cost: Free
Registration Link:
Keynotes: Ted Kummert & Quentin Clark
Full agenda:

A few of the highlights:

Powerful Self Service Analysis with PowerPivot

Julie Strauss

Empowering End User Insight Through Rapid Data Exploration & Visualization with Power View

Carolyn Chau

Going Under the Hood with the New BI Semantic Model

Ashvini Sharma

A Technical Best Practices Tour with ColumnStore Index

Susan Price & Eric Hanson

Introduction to SQL Server 2012 AlwaysOn, New High Availability Solution

Santosh Balasubramanian


A 12 City Tour featuring 4 of the SQL Server Product group of highly-skilled experts who have come to divulge the most revealing facts and features around SQL Server 2012.

Date & Time: Various
Cost: Free
Registration Link:


Join SQL Server experts as they take an in-depth look at the hottest SQL Server topics over 24 consecutive (and free!) technical webcasts.

Date & Time: March 21, 2012 beginning at 00:00 GMT (8pm Eastern Time)
Cost: Free
Registration Link: Big long ugly link

A few of the highlights:

Performance Tuning for Pirates!

John Sterrett

VertiPaq Under the Hood

Marco Russo

SQL Server First Responder Kit

Kendra Little

Moves Like Jagger – Upgrading to SQL Server 2012

Mark Broadbent

Automate Policy-Based Management Using PowerShell

Allen White



During the Spring Connections Conference in Las Vegas the in-person SQL Server Launch event will be taking place.

Date & Time: March 21, 2012 beginning at 00:00 GMT (8pm Eastern Time)
Cost: See website
Registration Link:
Keynotes: Shawn Bice

A few of the highlights:
SQL Server Connections happens to be going on alongside Visual Studio Connections, ASP.Net Connections, SharePoint Connections (where I will be speaking), Silverlight Connections, HTML5 Connections, and Cloud Connections.


PASS SQLSaturday’s are free 1-day training events for SQL Server professionals that focus on local speakers, providing a variety of high-quality technical sessions, and making it all happen through the efforts of volunteers.

Date & Time: Various
Cost: Free
Registration Link:

PowerPivot & Claims Based Authentication–Is there hope in SQL 2012?

PowerPivot & Claims Based Authentication–Is there hope in SQL 2012?

I promised an update on this in my previous article and it is time to share what I have learned.

The issue:

As many of us have painfully found, PowerPivot v1 on a Claims Based Authentication web application is not supported in SharePoint 2010.  I had a case open with Microsoft last year that had the aspirations of rectifying that situation.  We had hoped that working with the PowerPivot team (a part of the SQL Server Product team) and the SharePoint Product team that we would be able to find a way for Microsoft to change their stance and provide us a way to allow PowerPivot v1 to be supported. 

The answer for v1:

Sadly, the changes that would have to be made were too drastic for Microsoft to make in the middle of PowerPivot v1’s existing product lifecycle. 

The new and improved question:

That begs the question: Is there hope that PowerPivot v2 will be able to work on a Claims Based Authentication web application?

The caveated answer:

Keeping in mind that the product has not launched yet and things can still change (they won’t, but lets keep hoping) and that all of the SQL 2012 TechNet that are posted currently have the following statement at the top of them:

[This documentation is for preview only, and is subject to change in later releases. Blank topics are included as placeholders.]

The answer is still no.  In the TechNet article entitled “Hardware and Software Requirements (PowerPivot for SharePoint and Reporting Services in SharePoint Mode)” you will find the section under PowerPivot Sofware Requirements says in reference to SharePoint web applications:

PowerPivot for SharePoint only supports SharePoint web applications that are configured for classic-mode authentication. If you are adding PowerPivot for SharePoint to an existing farm, be sure that the web application you plan to use it with is configured for classic-mode authentication. For instructions on how to check authentication mode, see the section “Verify the Web application uses Classic mode authentication” in Deploy PowerPivot Solutions to SharePoint.“

Well damn.  Is there any hope that this is ever going to change?

Keep your chins up.  Rumor in the interwebs is that there is going to be a new version of SharePoint coming in sometime soon.  I can tell you that Microsoft has heard and continues to hear our cries out about this matter.  The more we raise this up as an issue, the more real it will be to Microsoft and the more likely they will be to invest in making the change.  If there is no perception that this is an issue, then there is no attention that will be paid and no investment dollars that will be spent.

If this is an issue for you, make sure that your Microsoft reps hear about it.  I talk about this with every Product Team member that I can get to listen to me for 5 minutes and I have never met with anything other than a warm reception and a sincere desire to know the feedback on how to make the product better.

Is this going to stop you from deploying PowerPivot v2 and Power View?

Not a snowman’s chance in the hot Jamaica sun.  I cannot wait for the final bits to drop so that I can get this amazing new product suite into the hands of every customer who wants to do BI.  The new BI story is too game changing not to get on the bus, and if I am riding it why not be driving smile

SQL Server 2012 Release Candidate is here!

SQL Server 2012 Release Candidate is here!

SQL2012We are creeping slowly toward the release of SQL Server 2012 and all of its new, fun features for SharePoint 2010!  The first Release Candidate of SQL Server 2012, known as RC0, dropped today and is publicly available for download.  

Here are a some links worth checking out:

Download SQL 2012 RC0 – Get the software here!

SQL Server 2012 Release Candidate is now Available! – SQL Server Reporting Services Team Blog

Announcing Microsoft SQL Server 2012 Release Candidate 0 (RC0) – Analysis Services and PowerPivot Team Blog

What’s New In Power View – SQL Server Reporting Services Team Blog

The Pivot Transform – Now with UI! – SSIS Team Blog

SSIS and PowerShell in SQL Server 2012 – SSIS Team Blog

Microsoft SQL Server 2012 Training Kit Now available for Download

Microsoft® SQL Server® 2012 Semantic Language Statistics RC0

Microsoft® SQL Server® 2012 Release Candidate 0 (RC0) Manageability Tool Kit

Microsoft® SQL Server® 2012 Express RC0

Microsoft® SQL Server® 2012 RC0 Master Data Services Add-in For Microsoft® Excel®

Microsoft® SQL Server® 2012 PowerPivot® for Microsoft® Excel® 2010 Release Candidate 0 (RC0)

I am still digesting all of the changes that the teams were able to bring into RC0 and am excited to spend the weekend working with my team to examine what they all are going to mean for SharePoint 2010 integration with Microsoft BI.

More to come as the weeks go on…

SQL Server 2012 Announcements & a new Blogger

SQL Server 2012 Announcements & a new Blogger

The big news of the day in the technology world… no, not the Blackberry outage… no, not iOS 5 being released…  the big news about SQL Server Code Name: “Denali” becoming official SQL Server 2012!  Product release is slated for the first half of 2012 (not specifically Q1 as many of us had hoped)

Project Crescent has been rebranded as Power View and announced that there will be a browser based version that will work on iOS & Android devices, but will not be full function.  The other great thing is that the story board to PowerPoint functionality was stated to be in the RTM release.

Project Juneau has been rebranded as SQL Server Data Tools as well.

The other big news is that my great friend, Dave Feldman, that I have quoted here on a number of occasions, is a co-creator of SPFLogger, and one of the smarter Microsoft BI & general troubleshooters that I know has finally started a blog.  Check out his take on the announcements from SQL PASS as well as some videos that he was able to get posted from the keynote.  His new blog is

Lastly, you can check out the Official Press Release from Microsoft, the SQL Server 2012 Analysis Services information, the SQL Server 2012 Reporting Services info, and the SQL Server 2012 Enterprise Information Management info for updated information.  SQL Pass’s site is also a resource, but I have found that following @MicrosoftBI and @SQLServer on twitter have been more useful thus far.

More BI goodness when I hear it!

What’s new in SQL Server Code Name “Denali” CTP3

What’s new in SQL Server Code Name “Denali” CTP3

Be sure to check out the What’s New (SQL Server “Denali”) site for full details, but here are the highlights in this PUBLIC release (no TAP NDA material here!) are:

SQL Server Installation

  1. Installing Prerequisites During SQL Server Code-Named “Denali” Setup
    1. Windows PowerShell 2.0 is a prerequisite, but not installed by the SQL Server Setup wizard
    2. .NET Framework 3.5 SP1 is a requirement, but is not installed by the SQL Server Setup wizard, it  requires you to download and install manually
    3. .NET Framework 4 is a prerequisite and is installed as a part of the SQL Server Setup wizard
    4. Minimum OS configuration is now Windows 7 SP1 & Windows Server 2008 R2 SP1
      1. check out the Hardware and Software Requirements for Installing SQL Server “Denali”
  2. You can now install Data Quality Services (DQS) using the SQL Server Setup wizard
  3. Product Update is a new setup feature which allows you to download the latest updates and apply them during install
  4. Server Core Installation is now supported
  5. SQL Server multi-subnet clustering makes it so that you can have failover nodes on different subnets
  6. Local Disk is now a supported storage option for tempdb for SQL Server failover cluster installations
  7. BUILTINadministrators and Local System (NT AUTHORITYSYSTEM) are not automatically provisioned in the sysadmin fixed server role
  8. Setup now offers default accounts for the SQL Server services whenever possible
  9. The Active Directory Helper service is no longer installed because it is no longer needed
  10. SQL Server Itanium editions are no longer supported

Database Engine (too much here to list all of the good ones, this is a MUST READ)

  1. AlwaysOn SQL Server Failover Cluster Instances
  2. AlwaysOn Availability Groups
  3. Indirect Checkpoints

Manageability Enhancements

  1. The Database Engine Query Editor introduces enhanced functionality for Transact-SQL debugging and IntelliSense.
  2. The Upgrade a Data-Tier Application wizard has been updated to perform an in-place upgrade, which replaces the side-by-side upgrade process
  3. Users import the sqlps module into PowerShell, and the module then loads the SQL Server snap-ins.
  4. The bcp Utility and sqlcmd Utility utilities now have the -K switch, which allows you to specify read-only access to a secondary replica in an AlwaysOn availability group.
  5. Database Engine Tuning Advisor Enhancements allow you to use the query plan cache to avoid having to create manual workloads from a script or trace.

Programmability Enhancements

  1. You can no longer use CREATE ENDPOINT or ALTER ENDPOINT to add or modify SOAP/HTTP endpoints
  2. The FileTable feature leverages FILESTREAM to allow files and documents to be stored in special tables and accessed using Windows applications, as though they were stored on the file system without making changes to the applications
  3. Semantic search builds upon the existing full-text search feature in SQL Server but enables new scenarios that extend beyond syntactical keyword searches
  4. Full Text Search now supports property-scoped searching on properties emitted by IFilters, Customizable NEAR option of the CONTAINS predicate or the CONTAINSTABLE function
  5. New Word Breakers and Stemmers (this one has a a bit baffled, I thought I was doing well understanding the Programmability stuff until I got to this one…)
  6. The EXECUTE statement can now specify the metadata returned from the statement by using the WITH RESULT SETS argument
  7. You can specify a range of rows returned by a SELECT statement based on row offset and row count values that you provide
  8. Three new sub-data types for geometry and geography data types can be used to store circular arc segments, CircularString,CompoundCurve, and CurvePolygon. (and my ITPro brain just imploded…)
  9. A sequence object is a user-defined schema-bound object that generates a sequence of numeric values according to the specification with which the sequence was created. It operates similar to an identity column, but sequence numbers are not restricted to use in a single table.
    • I know lots of Oracle DBAs who are excited by this one, personally I haven’t wrapped my head around  it completely yet…  Going to have to read more on Sequence Numbers
  10. The THROW statement can be used to raise an exception and transfer execution to a CATCH block of a TRY…CATCH construct
  11. 14 new functions:
  12. 1 changed function:
  13. New and Enhanced Query Optimizer Hints
  14. New XEvent Enhancements
  15. New Analytic Functions:

Scalability and Performance Enhancements

  1. New data warehouse query acceleration feature called columnstore indexes
  2. Indexes containing varchar(max), nvarchar(max), and varbinary(max) columns can now be rebuilt as an online operation
  3. 15k partitions are now supported by default instead of 1k

Security Enhancements

  1. User-defined server roles are now available.  This allows a role to be defined at the server level and grant or deny access across all databases
  2. You can now define a default schema for a Windows group
  3. Significant SQL Server Audit Enhancements
  4. Access to contained databases is permitted through contained database users which do not require logins
  5. The HASHBYTES function now supports the SHA2_256, and SHA2_512 algorithms
  6. The RC4 algorithm is only supported for backward compatibility
  7. The maximum length of private keys imported from an external source is expanded from 3,456 to 4,096 bits
  8. SMK and DMK encryption changes from 3DES to AES
SQL Server Default Trace

SQL Server Default Trace

In every version since SQL 2005 there has been a “background trace” enabled by default on all SQL Server installations.  This was a bit of a shocking revelation to me, as I am not a DBA, since every time I have called Microsoft dozens upon dozens of times and talked to tier 3 support / product team members for SQL Server and none of them have mentioned this fact when we have been diagnosing issues in SQL.  We have always had to set up a new trace.

Use the following command to validate that the default trace is enabled:


This will return something that looks like:




1 1 2
1 2 E:MSSQL10_50.instanceMSSQLLo​glog_560.trc  
1 3 20
1 4 NULL
1 5 1

From MSDN we get a legend that helps us under stand the above output:

Column name Data type Description



ID of the trace.

property int

Property of the trace:

1= Trace options. For more information, see @options in sp_trace_create (Transact-SQL).

2 = File name

3 = Max size

4 = Stop time

5 = Current trace status. 0 = stopped. 1 = running.

value sql_variant Information about the property of the trace specified.

The question I have heard most often since this discovery is “What does it matter?  Microsoft has it turned on by default so that must mean they intended it that way.  It must not cause a performance impact.  Right?”

After I stopped laughing at the idea that because Microsoft turned it on by default it must be ok (see Windows 2000 and before where everything was turned on and wide open upon install) I explained that the default trace is dropped into the installed Logs directory.  This cannot be changed.  You can stop the default trace and create your own, but that will break some of the built in reports that rely upon it.

The key is knowing that this is there.  While it may be lightweight and designed to be non-impactful, if you have anything else with a write intensive load directed at the same spindles you can expect to see some contention.

The positive of it is that now that we know it is there, it is extremely useful in troubleshooting.  The default trace can be queried by using the following command to pull back the most useful data (querying * pulls back WAY too much data for my taste):



SharePoint 2010 & SQL 2008 R2 build numbers and helpful patching links

SharePoint 2010 & SQL 2008 R2 build numbers and helpful patching links

In an effort to make life simpler I have compiled a short list of useful link to build numbers and patching sites for SharePoint 2010 and SQL 2008 R2.

The Microsoft SharePoint Updates PagesSharePoint 2010 –
SharePoint 2007-
This is your official sites for downloading the SharePoint updates from Microsoft.  Highly useful rather than waiting on the blogger community to send you a link, or get the TechNet bulletin, your carrier pigeon to arrive, or any of the other 120+ ways to get your SharePoint update information!

Cornelius J. van Dyk’s Blog on Versions for SharePoint
Highly useful link if you find yourself a CU or two behind and you want to know what version of the CU is currently applied to your farm.  Simply visit your Central Admin Manage Patch Status page http://localhost:####/_admin/PatchStatus.aspx to check what version number your farm is on.

SQL 2008 R2 build numbers
This has proven to be the most reliable site I have found for listing the latest build numbers for SQL Server since 2008 R2 dropped.  To find out what what build you are running do the following:

  1. Open SQL Management Studio
  2. New Query
  3. type and then execute the following command
    select @@version 

    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode, .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode pre
    {font-size:small;color:black;font-family:consolas, “courier new”, courier, monospace;background-color:#ffffff;}
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode pre
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .rem
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .kwrd
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .str
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .op
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .preproc
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .asp
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .html
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .attr
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .alt
    .externalclassaf644619e5194a77b8e785ba7f262756 .csharpcode .lnum

We are working on getting a SQL Server version’s page up on CvD’s blog as well, but for now I here is a table with the info:

April 2010 RTM
May 2010 CU1
June 2010 CU2
August 2010 CU3
October 2010 CU4
December 2010 CU5
All PowerPivot Features supported only in Classic Mode Authentication

All PowerPivot Features supported only in Classic Mode Authentication

Surprised?  I was this week as well.  I have had a case open with Microsoft since October that we have been playing with back and forth trying to figure out why automated refreshing of a PowerPivot workbook wasn’t working.We let the case go off and on for a quite a while because in the opening days we found that if we ran the PowerPivot Service Application Pool in the context of the farm admin account, that manually refreshing the data would work.  Having a work around in hand threw this issue to the back of the queue for a while.

After much diagnosis, discussion, and many weeks of thinking that the Secure Store Service wasn’t working properly, along came a very simple explanation: PowerPivot requires Classic Mode Authentication.  FBA and Claims are not supported for doing data refresh or usage data collection scenarios.

The answer to our question came via 3 references:

PowerPivot Data Refresh – Everything you always wanted to know  – Page 14 is a good place to start.  Section called “Anatomy of PowerPivot Data Refresh” – written by Mariano Teixeira Neto

Plan PowerPivot Authentication and Authorization – “To support data refresh and usage data collection scenarios, PowerPivot for SharePoint requires Classic mode authentication. PowerPivot requires a Windows domain user to be the identity behind the SharePoint security token, which it will use to create a history of user activity and document ownership, and to connect to external data sources during data refresh.” – quoted from Technet

Why PowerPivot requires ‘classic-mode’ web applications – “The second one (i.e. ‘b’ above) is a bit trickier and it is the core issue for this blog entry. In PowerPivot, when connecting through our front-end web services (aka, the PowerPivot Web Service, or PWS in our architectural design) the underlying protocol is the same as the one that earlier versions of Analysis Services used for the ‘data pump’ feature ( That protocol does not know about claims – it relies on getting a Windows identity.” – quoted from Dave Wickert, aka PowerPivotGeek

One of the answers that we got regarding this is that PowerPivot is a part of SQL Server 2008 R2 and not a part of SharePoint 2010, so it wasn’t really a SharePoint authentication problem.  ((Does that sound right?))

We have gone back to Microsoft and are looking to see if this is something they are going to be able to fix so that Claims Based Authentication can truly be the end-all-be-all solution that we are all hoping that it can be, or if this is just one of many things that we are going to run into that aren’t yet supported in CBA mode.  For those keeping count we are now up to 2 major issues that have come back to CBA as the root cause, the other being the Site Directory issue (which we are still waiting to hear if they are going to accept our hotfix request or not… last update from the engineer was yesterday).

I have to give a ton of thanks to the MS TAM (Mike Mitchell) that I work with, the engineer (Jason Haak) who worked on this case with us, and the 2 guys who seem to be the experts in the world on PowerPivot, Dave Wickert and Lee Graber.  We spent so much time focused on the technology of the platform and how cool PowerPivot was to work with, that we forgot rule #1 (which they kindly reminded us of)… RTFM.