Category Archives: SQL Server

SSRS Speaks ! New Feature in Reporting Services 2014

SSRSNewFeaturesByVersionFullAn old colleague recently asked… “What’s new in Reporting Services 2014 bro?”.   Asides from official support for Chrome Browsers, Reporting Services 2014 remains unchanged feature wise compared to 2012.

It appears focus is predominantly on PowerBI and the acquisition of Datazen.  The following chart illustrates Approximate number of outstanding cool new features by version since Reporting Services 2005.  Of course there were a few discontinued or depreciated items along the way too…

So is this it for the trusty reporting tool ?? Can we show Report Builder some hipster love please MS ??? ;(  Read about the Reporting Services 2014 New Feature here

Sharepoint BI Demo Machine Full Download

Shortcut your Sharepoint Business Performance experience with this pre-packaged virtual machine available for partners and customers, pre installed with industry specific Sharepoint dashboards like Education, Energy and Communications.

contoso dashboard

If you get along to you will find some online demo’s but this one is a full downloadable virtual machine – ok it is only Sharepoint 2010 on SQL 2012, it has a cool footer but for the tragic Windows Phone 7,  and it is only a Generation 1 VHD Hyper V machine.    If you are a Virtual Machine Jedi with an extremely high Midi-chlorian count, you may have a chance of converting the image to run on virtual machine clients like VMWare Client and Virtual Box.  I had best luck using Hyper V host on Windows 8.1.  Download is approximately 20Gb, so if you are having problems, try a Download Manager.

Here is the actual link to the Virtual Machine –  Instructions for how to mount the Virtual machine under Hyper V are included in the page comments including administrator password, domain and ip addresses.  If you are running Hyper-V in Windows 8.1 or Windows Server 2012 R2, you will notice the instructions are written for the older version of Hyper-V – the terminology has changed slightly.   Instead of importing the Virtual Machine, you will find it easier to create a Generation 1 Virtual Machine, assign the internal network that you created using the Virtual Switch Manager, then add the add virtual hard disk from the uncompressed image files. Once the virtual machine starts, you are almost there.  Login with these initial login details, the following the remaining steps in the setup procedure :

User: administrator

Password: pass@word1


Link to Free Download Manager below, which I am no way affiliated with, it just solved my headaches with Akamai download manager and Internet Explorer.   This worked well to download this particular file but had no luck with resuming interrupted MSDN downloads.

SSRS Migration: Data Sources Supported in Reporting Services 2012/2014

Migrating your Reporting Services instances to SQL Server 2012/2014 has a few key considerations.

First, the oldest version you can upgrade from is Reporting Services 2005 and that must be patched to SP4 (9.00.5000).

Next, any SQL Server 2005 report data sources are still supported in SQL Server 2012/2014. Here are the full list of data sources supported in SQL Server 2014 However your Report Server databases themselves can’t be hosted on SQL Server 2005 database instances

Importantly, those pesky Report Models are depreciated in Reporting Services 2012/2014 They will migrate but you won’t be able to make changes after the migration – so at some point they will need to have their datasets rewritten. Getting a handle on how many reports hang off Report Models and giving your customers some options is a good way to manage this issue.

Finally, the full migration process is outlined here step by step There are risks in any major version migration, but running side by side environments and getting a business acceptance sign-off is the best way to mitigate those risks.

Always On Availability Groups: Do I need a File Share Witness or No ?

This blog sets about doing a shallow dive – to simply understand how a File Share Witness is used and required in an Always On Availability Group cluster.

An Always On Availability Group cluster requires no shared storage – Windows 2012 supports up to 16 nodes, SQL Server 2012 supports up to 5 nodes in an Availability Group, and SQL Server 2014 supports up to 9 nodes.  A cluster can sustain itself  as it loses nodes and Quorum is described as the state of the cluster.

The basic rule for determining how many nodes your cluster can sustain losing is a “majority” …. I love a good analogy… here is one … Your cluster is like a bar stool … quorum is its ability to remain standing… or rather, how many legs it can lose before it falls over.

So… File Share Witness, Yes or No ? … Mathematically speaking the short answer is …

…Even number of legs = Yes

…Odd number of legs = No

A bar stool with an odd number of legs isn’t an issue, an odd number of legs means a majority can always be determined, but for an even number of legs, losing half of the legs creates an issue – a majority can’t be determined, unless you have a witness (a backup leg).

Lets take a really simple example – a bar stool with 2 legs – this is your 2 node cluster.  Each leg is considered a vote, until it is removed, in which case its vote doesn’t count.

The state of the bar stool (quorum) is determined by how many legs are still standing (votes).  It would be pretty pointless setting up 2 node Always On cluster if you had no redundancy.  The mathematical  formula which explains how many legs a bar stool can afford to lose is… (legs/2)-1 … that’s (2/2)-1 … which equals zero legs.  Without a backup leg (file share witness), the bar stool could not sustain losing any legs… so it has no redundancy.


File Share Witness -> With a backup leg however (file share witness), the bar stool can lose half its legs (1), form a majority and meet the number of Votes required to keep the bar stool standing.


Let take a 4 leg bar stool example…because lets face it, they usually do … how many legs can a bar stool afford to lose to keep standing ? …The formula is (legs/2)-1 … that’s (4/2)-1 … which is equal to 1 leg.  If a 4 leg bar stool loses 1 leg … this still forms a majority and the stool maintains its quorum…


But if it loses two legs, (half of its legs), we are in trouble… a count of standing legs won’t reach a majority and the quorum can’t be sustained… unless…


File Share Witness -> For a bar stool with an even number of legs, a bar stool CAN lose up to half its legs, as long as a backup leg (file share witness) is used.   You can see when half of the nodes are missing, a majority can still be determined and satisfied, when a file share witness is used.


In advanced Always On architecture, a node can be configured so that it as no vote.   It still participates in the cluster and the availability group, but for the purposes of counting a majority, the vote doesn’t count.  So a 4 node cluster, with 1 node set to no vote, gives a 3 node vote.  A majority can be determined with an odd number of votes so a file share witness is not needed.   Locating your file share witness in a third datacentre can also provide another level of resilience.


Location of RSReportServer.config for SSRS Web Service URL Sharepoint Integrated

You want to find the rsReportServer.config file for your SQL Server Reporting Services 2012 Installation.

Basically….for a Sharepoint Integrated Mode install of Reporting Services look in …

<Drive Letter>:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\15\WebServices\Reporting

Typically the location of your reportserver url is going to look something like this if your sharepoint web url is …

<a href=""></a>

and for a Native Mode Reporting Services installation look in …

<Drive Letter>:\Program Files\Microsoft SQL Server\MSRS1111.MSSQLSERVER\Reporting Services\ReportServer

Can I run a PowerPivot 2012 (v2) Excel 2010 document to Sharpoint 2013

You can run a PowerPivot 2012 (v2) Excel 2010 document up on Sharepoint 2013 server.  The following read outlines a the pros and cons. Note Excel 2013 Powerpivot documents support Interactive refresh within the open workbook however PowerPivot v2 for Excel 2010 only supports Unattended Refresh.   At this stage I havn’t any luck getting data refresh to work with Excel 2012 v2 Powerpivot documents and a bug is mentioned here PowerPivot  v1 does not support interactive nor unattended data refresh so you are best converting these.