Insufficient data from Andrew Fryer

The place where I page to when my brain is full up of stuff about the Microsoft platform

November, 2009

  • Report Builder 3.0 SparkLines

    As a graphic designer and geek with a masters around cognitive psychology I am intrigued at how little use is made of the wonderful visualisations in many modern BI tools be they from Microsoft, Oracle IBM or SAP.

    Ignoring this trend, I wanted to show a new visualisation tool in SQL Server 2008 R2 called SparkLines during my session on Report Builder 3 at SQL Bits, but my shuttle was having disk issues and I hadn’t fully got everything back before I was on (but thanks to Darren Green, Chris Testa- O’Neill and Rob Farley for trying).

    Anyway my demos are back in business and here’s a SparkLine report I created earlier..


    SparkLines being the mini graphics which you can also do in excel. BTW I have used various sub-types in the report above to show what you can do, but you would obviously stick to one in a report like this. 

    I have put created a walkthrough and samples that you can follow so please have a go (it should only need half an hour at the very most).   

  • Getting Started with SparkLines in ReportBuilder 3

    First you’re going to need the download and install SQL Server 2008 R2 November CTP.  My step by step guide on doing that is here (which stil works with the November CTP)

    Now you’re going to need to have a copy of the adventure works database  from Codeplex I have used the version designed for use with SQL Server 2008 R2.  You’ll need to enable FileStream support in SQL Server configuration manger if you use this one…


    (right click on the instance to bring up this properties page).

    You should now be able to go to  Report Manager which is the web portal that comes with reporting services and should be at http://myserver/reports and will look like this..



    except that yours will have no content.

    From the toolbar in the portal you’ll need to select New Data Source to make a connection to get the data from..


    I have not followed best practice here because I have used a very privileged account, but this is just a demo!

    I have also created a folder called data sources to keep all of these in, but this is optional.

    Now we can start to use Report Builder 3, by selecting that from the toolbar..



    and this in turn launches a wizard.  One of the new things in Reporting Services in SQL Server 2008 R2 is the ability to share data sets as well as data sources, and I am going to do that as part of this walkthrough be selecting the create shared dataset option I’ve highlighted.

    I can now choose which columns I want to have in my dataset..


    But be careful if you include calendar and sales in your query as I have done as the friendly query designer in Report Builder will try and join these table on three fields.. so just use the one join i.e. cut out the stuff in red..

      INNER JOIN FactResellerSales
        ON DimReseller.ResellerKey = FactResellerSales.ResellerKey
      INNER JOIN DimTime
        ON DimTime.TimeKey = FactResellerSales.OrderDateKey AND DimTime.TimeKey = FactResellerSales.DueDateKey AND DimTime.TimeKey = FactResellerSales.ShipDateKey
      INNER JOIN DimProduct
        ON DimProduct.ProductKey = FactResellerSales.ProductKey
      INNER JOIN DimProductSubcategory
        ON DimProductSubcategory.ProductSubcategoryKey = DimProduct.ProductSubcategoryKey
      INNER JOIN DimProductCategory
        ON DimProductCategory.ProductCategoryKey = DimProductSubcategory.ProductCategoryKey

    Run the query (the exclamation mark) to check it’s returning meaningful data. Now you can save this be selecting the disk tool (just like in any office 2007 product), and you ‘ll be asked to give it a name so call it ResellerSales.  

    This can now be used in a new report so from the the Report Builder icon (top left – again like office) select New  bring back the startup wizard again.  This time select new Table or Matrix Wizard..


    Browse to find the DataSet you just made..


    and click next to bring up the layout..


    click on the image above to make it larger so you can see how to arrange your data exactly as I have. Click Next when you have


    go with the defaults here and for the next screen (the theme)..


    Click finish and you should see the layout you have created in the design view of RB3..


    To sharpen this up you’ll want to

    highlight all the numbers and set there format to C2 or N2 (currency or a number to 2 decimal places)..

    widen all the columns

    and rename the product columns to Category, Sub Category and Product..


    If you are already familiar with Report Builder we can now start the new stuff by adding SparkLines to this..

    First create a new column by selecting the product column right clicking and add column –> right like this..


    Now select the insert ribbon and you’ll see the new SaprkLines..


    Click on it and then click on the lightest blue cell in the new column which will bring up the SparkLines Wizard


    I’m going to choose this one..


    and I’m simply going to click OK. You should see a little preview in the cell you selected ..


    But before it will work you need to tell it what data to use so click on it..


    and click on the plus signs to add the fields you want to use. I have selected OrderQuantity for the values and Calendar Year for the Category Groups.

    Now for the clever bit. highlight that cell and select copy and then paste it to the three rows below..


    No you can run it..


    Notice that as it expands each level of the hierarchy has a SparkLine for the quantity sold each year.

    I have uploaded the report and the shared dataset to Skydrive  for you, but you’ll have to create your own datasource to the adventure works database to use them.

  • Reporting on spatial data in SQL Server 2008 R2

    Report Builder 3 the latest version of the end user reporting tool in SQL Server 2008 R2  has built in support for Bing Maps and you can try this for yourself in the November CTP.

    To help you along I have posted a 9 minute screencast to TechNet Edge and published a step by step guide here.  BTW don’t worry if you haven’t got any spatial data to hand as I have included guidance on how to get some.

    My example simply shows the attendance at a few SQL Server events in the UK that I have been to..


    ..and is actually very straightforward. So an easy way to impress your boss or users in the many organisations that are becoming interested in understanding where things are happening.

    Addendum 27th November.

    I have just found out that you don't need to license Bing Maps if you you use Reporting Services as I have done here, i.e. for either aerial or the road view.  However you would have to have a license if you used Bing Maps in any other way outside of reporting services for business use.

    You can access the license agreement for Bing Maps through the map control when you add the Bing Maps layer on a report  

  • Using SQL Server 2008 R2 Report Builder 3 with Bing Maps

    This article shows you how to get started with Report Builder 3 (RB3) and Bing Maps.

    I could use adventure works to show you this but I thought you might want to see a little bit of our green and pleasant land instead. To do this I have put  the previous SQLBits venues into a venue table..

    CREATE TABLE [dbo].[Venues](
        [VenueID] [int] IDENTITY(1,1) NOT NULL,
        [VenueName] [nvarchar](50) NULL,
        [VenueLocation] [geography] NULL,
        [VenueURL] [nvarchar](100) NULL,
        [Attendees] [int] NULL)

    and got the spatial locations into this by using this application from my fellow evangelist Mike Ormond.


    Now I have some spatial data so I can use the new report builder 3 in SQL Server 2008R2 to make a report. As with SQL server 2008, this is either a click once install from report manger or a separate .msi install you can get from the SQL Server 2008 R2 feature pack.

    When you open RB3 you get confronted with various wizards..


    Following the map wizard will bring this up


    While Cecil Rhodes remarked that

    "Remember that you are an Englishman, and have consequently won first prize in the lottery of life."

    this doesn’t apply to spatial data so there are only shape files for the US, however you can play with Bing Maps right out of the box by selecting the last option; SQL Server spatial query.You’ll need to specify the data source where this data is


    ..and the query to retrieve it ..


    note the venue location column returning spatial data then you’ll get something like this..

    image  once

    the orange arrows show where the tool has picked up the points form my query.  Now I can embed map data in my report and add a Bing Maps layer..


    and choose what sort of Bing Map I want (here I went for aerial) and you can stil see my data points.

    Now I can define how I want to present my data points..









    I am going for a bubble map, and now I need some analytical data for that..


    which for me is just the same data set as I used before, but could be a separate one if you want.



    which in my case makes the next screen look odd as I am joining a data set to itself on VenueName!


    There are also sorts of options you can then experiment with to get the effect you want, I have gone for colour to denote attendance and have use VenueName as the label with an ocean them for the whole map.

    You can then edit the map further in Report Builder by clicking on the map layers and then using the dropdon arrow for the layer you want to edit. In this case I changed the point title font to white..



    and I have also dragged the legends of the map so I can see my data.

    That’s about it now I can save and deploy my report. One thing to note here is that the end user doesn’t need an internet connection to see the Bing maps as I embedded the relevant map tiles in the report.

  • SQL Server 2008 R2 image preparation

    With the rise of the virtual machine, the quick creation of a sandbox in which developers and testers can work without fear of compromising the production environment becomes a much simpler exercise. You will see built in support for this lab management in the ultimate edition of Visual Studio Team System (VSTS) through the use of System Center Virtual Machine Manager (SCVMM).

    One of the problems posed by these sorts of operations is that while it is easy to make a copy of a machine , be it physical or virtual, it’s a bit more difficult to change its identity. While you can quickly change the name of a machine you need to sysprep it to change it’s unique Security Identifier (SID). SID’s are how active directory identifies individual machines so you can’t have two on the same domain even if they have different names.

    This gets more annoying when you include SQL Server in the mix. If you rename a machine that’s running SQL Server or sysprep it, it will upset the SQL Server permissions service accounts etc.  There are workarounds for this but they are tricky and not supported.

    However If you use the advanced installation options in

    SQL Server 2008 R2 ..


    .. you can prepare an image of SQL Server which will survive a sysprep and subsequent domain join.  One you have done that you can come back to this interface and complete the install.  That’s a very manual process so what’s useful is to script all of that.  I have a separate step by step cook book to show you how to do this.

    I have also posted a video on TechNetEdge so you can watch me do it (I have compressed down the installation so it only lasts 10 minutes).

    The real power of this process comes into its own when you use it with a deployment technology such as Windows Deployment Services (WDS), System Center Configuration Manager (SCCM) or SCVMM as both of these allow you to run scripts as part of the deployment process, thus fully automating it. This is a topic in it’s own right which I will cover later.

    To summarise SQL Server 2008 R2 is now sysprep friendly and you can try this out by downloading the November CTP

  • SQL Server 2008 R2 Prepare Image Cook Book

    This article walks you through the steps to prepare and complete an image installation of SQL Server 2008R2.  I am assuming you’ll be doing this in a virtual machine so that you can make copies of it for development and test.

    If you are running your virtual machine on Windows Server 2008R2 you should install the application role which is the simplest way to install the .net framework 3.5 .

    To prepare a SQL Server image from the command line you run setup with the following switches..








      • This command should all be on one line I have split it out for clarity
      • /IACCEPTLSQLSERVERLICENSETERMS is a new mandoatory switch for ALL types of command line installs of SQL Server 20087 R2.
      • These are just the mandatory commands for a full list refer to this section of books on line.
      • InstanceID is not the InstanceName which you can specify when you complete the image, but you need to know what it is so you can enter it for that step
      • The example above includes all of the feature you can prepare an image for i.e. you cannot do this for analysis services or integration services

    Having done that you can then sysprep the box ..


    Check the generalize box and select shutdown.

    Once you have done this in a virtual machine you can make copies of it as you need to.

    To use one of the copies..

    Start the VM and you’ll see it go into the out of box install i.e. you’ll be asked to confirm the locale and enter the license agreement. 

    You will probably want to rename the virtual machine (the initial boot will give it  a random name) and optionally you may wish to join the virtual machine to a domain.

    You can now complete the SQL Server installation with this




    /INSTANCEID=“from prepare image step e.g.TechNet”


    /SQLSVCACCOUNT=“domain\sql service account"


    /RSSVCACCOUNT=“domain\sql service account"



    /AGTSVCACCOUNT= =“domain\sqlagent service account"









    • SAPWD is mandatory if you select mixed mode security (/SECURITYMODE=SQL)
    • I have optionally set  /BROWSERSVCSTARTUPTYPE to automatic to make instances discoverable on multi instance installations
    • I have optionally set /TCPENABLED=1 to allow remote access over TCP/IP
    • the /RSSVCACCOUNT and /RSSVCPASSWORD are mandatory because I optionally selected reporting services when I prepared the image.
    • This instance will need to be remotely managed as you cannot install the client tools as part of this process
  • SQL Server MVPs

    The best thing about working with SQL Server is working with the other people that use it.  The SQL server experts who do most for their colleagues in the community are recognised as MVPs, so not only do they know their stuff they are willing to share their knowledge.

    Of course you could argue that all this furthers their careers and/or businesses and while this might be true up to a point I would counter this by suggesting that you buy SQL Server MVP Deep Dives 


    For two simple reasons, it’s an excellent example of what MVP’s are capable of producing, and because they have donated all their royalties to the WarChild Charity

    War Child

    Thanks to Kimberley , Paul R, Paul N , Rob, Kalen, Greg and Adam for this. and thanks to Richard Siddaway in the UK for the PowerShell chapter and for letting me know the background.

    Technorati Tags: ,,
  • A small problem with Managed Service Accounts

    I am very fortunate to always have the latest versions of Microsoft stuff to play with such as SQL Server 2008 R2, but occasionally something doesn’t work, and so I need to tell the relevant product team what the problem is.

    In this case I was trying to get the SQL Server 2008 R2 November CTP to run with the new Managed Service Accounts in Windows Server 2008 R2 Active Directory.  They are a bit like the NTAUTHORITY/LocalService account in that they don’t have passwords that you need to specify to the service, however they are a totally different beast in that they are domain accounts  designed to be used for services, like IIS 7.5. Essentially the passwords are managed internally by active directory and are designed for use with Kerberos. For more on this have a look at this article on TechNet 

    However they are so new there is no way you can create one without using PowerShell (with the active directory module loaded)..

    New-ADServiceAccount SQLService

    Then you tell AD where you want to use the account on the server SQL08R2Demo..

    Add-ADComputerServiceAccount -Identity SQL08R2DEMO -ServiceAccount SQLService

    finally you deploy it to the computer that will use it

    Install-ADServiceAccount -Identity SQLService

    Now you can see this account in Active Directory under the new managed service account folder..

    ad service account 2

    I can now use an account like this to run IIS by going to services and running IIS with the new account (note you don’t supply a password for this account).

    However when Iu try and use it to run a SQL Server service, it doesn’t show up in the SQL Server configuration tool as it doesn’t see this kind of account at all even though you can search the managed service accounts folder.  Of course if I go into services proper I can change SQL Server services to use it…

    ad service account 9

    but that is not supported, as it will foul up the permissions needed to do things like backup. BTW I didn’t supply the dollar sign suffix

    so I have found something wrong and now I need to tell someone to fix it and the place where you do that for Microsoft stuff , irrespective of whether or not you work for them is on Connect (

    You register suggestions and bugs by first searching to see if it’s there already and then you can create your own..


    Here’s that bug, and notice that no one’s voted on it yet.  Votes are primarily there to influence feature requests, but bear in mind that what you or I consider to be a bug, maybe there by design and so could in fact really be a feature request. Anyway the more votes there are coupled with the severity of the problem the more likely it will be fixed

    So if you think something needs to be changed then use the site as the product teams are pretty good but are not known for their telepathic abilities.

    In the meantime I hope that this gets fixed for the release of SQL Server 2008 R2.


    Update 28 November 2011

    Managed Service Accounts didn't get supported in SQL Server 2008 R2, but are a feature in SQL Server 2012 as per this TechNet article.  The method is exactly the same



  • SQL Bits Friday session

    I promise this is the last time I mention SQL Bits this week, but I wanted to let you know I have got 25 free tickets for the paid for event this Friday which would normally cost £149 to attend. 

    The venue is the Celtic Manor in Newport…

    Map picture

    and is probably the next best thing to going to TechEd. There are four tracks;

    • Data warehousing,
    • Database,
    • BI,
    • Development,

    specifically on SQL Server 2008 and R2, with an all star cast headed by Donald Farmer, and Thomas Kejser, the details of which are here.

    If you can make the date and want to come please e-mail me ( with SQLBits in the title and I’ll get the tickets to the first 25 to respond.

  • TechEd 09 Ask the Experts

    I have been at TechEd Berlin last week and when I wasn’t speaking I spent most of the time in the Learning Centre (TLC) or ask the experts. I must confess I felt a bit of a non-expert this year for two reasons;

    • I have been spending time getting up to speed on the new Windows releases
    • There were some real experts on the stand..

    In the snap below we have Bob Beachemin from SQL Skills, Gopal Ashok and Torsten Grabs from the SQL product team in Redmond. Gopal is mr Replication and HA, while Torsten is mr StreamInsight. 


    Joanne is also from Redmond while Roger is from Microsoft in Switzerland. On other days we had Omri Bahat (DBEngine), Michael Rys (XML and Spatial) and Donald Farmer (BI and specifically PowerPivot).

    AJF 17/11/2009  I forgot to mention the other product superstar on the stand, Chris Baldwin who is Reporting Services.

    So going to TechEd isn’t just about going to sessions it’s an opportunity to share experiences with the guys who develop the product as well as having time to do a few labs..


    However it’s a tough world out there and so getting your manager to agree to letting you go was hard (however 7,300 managed it this year), so the next best thing if you’re interested in SQL Server is SQL Bits this Thursday – Saturday.

    Donald farmer is presenting at this event on the Friday, and in addition there will be a whole crop of MVP’s there and actually these guys have more influence with the product team than I do.

    The Saturday community day is free, while the Friday is a paid for event focusing on SQL Server 2008 and R2. Either of these would be my top tip if you need to be looking for your next career move, as the contacts you meet like the MVP’s are often aware of vacancies through the projects they are engaged on.

    So I’ll hopefully see you in Cardiff!  

  • Wir fahr'n fahr'n fahr'n auf der Autobahn

    James and I are on our way to TechEd Berlin, but we’re driving because we’re both a little bit eccentric.  Not only that we have a bit of stuff to take..


    four laptops, a shuttle, a hub assorted drives, cables so that we can demo a bunch of interesting stuff in our session.

    We’ll be showing as many of the following as we can cram in 75 minutes:

    • Our SQL Server on Server Core (but not supported) cookbook
    • Using the advanced installation options in SQL Server 2008 R2 to prepare a virtual machine which can be quickly bought on line
    • Live Migration in Hyper-V2 to move a SQL Server virtual machine from Wallace-R2 to Grommit-R2, the 2 laptops in the picture above.
    • Managing these virtual machines in the latest version of System Center Virtual Machine Manager (SCVMM).
    • Using the enterprise policy management framework from codeplex to ensure all the SQL Servers above are configured the way we want them.

    All of this will be done from the command line / powershell, and the only thing we can’t share with you is our internal iSCSI target software which so you’ll need something like StarWind or iSCSI Cake.

    If you’re going to be there then the session is DAT01-IS on Monday afternoon, but if not then James and I will be making a series of 5-10 minute videos from each demo, when we get back as IS sessions aren’t recorded.

  • An Interesting VDI case, Holmes.

    Last June I didi a series of interviews with Alun Rodgers of Risual about the cool stuff in Windows Server 2008. In one of these..


    Alun is telling me about how Terminal Services (now known as Remote Desktop Services, RDS, in Windows Server 2008 R2) could be used to provide a secure desktop for police forces and the like.

    This would mean that rather than having 2 separate systems on an officer’s desk, one for the classified network e.g. for Holmes (Home Office Large Major Enquiry System) and one for normal administrative duties, everything could be done over one network from one desktop. The upshot of this is that it saves a large pile of infrastructure, maintenance and running costs without compromising the integrity of any of the secure systems.

    I did call this out again at the session that Jamie Burgess and I did on RDS at the Wembley tri-launch on 6th October. What I didn’t realise was that Alun had actually bought along Lancashire Police (the customer) to this event to talk about exactly this situation in the closing keynote session at the same event..


    Ward Ralston with Colin Fizsimons & Michael Wood from Lancashire Police

    If you couldn’t get there on the day, the videos and decks from that event can be found from these links..

    There is also a case study that has been published about the work that Risual has done for Lancashire Constabulary.

    Footnote: Alun is the AL bit of Risual,  the others being RIchard Proud & SUe Denton ;they are a small but influential system integration partner.

  • IIS 7.5 and Windows Server Core

    There is so much stuff on Windows7 & 2008 R2 features out there, but not so much on IIS 7.5.  The other reason it slipped off my radar was that reporting services in SQL Server 2008 directly uses http.sys for its portal and there is no longer a need to install IIS at all.  Anyway it’s now time for me to look at this in more detail as part of my series on server core.

    Firstly IIS 7.5 like every other new bit of the Microsoft platform has embedded PowerShell support, meaning you can now directly manage  IIS 7.5.  Also you turn on IIS7.5 features using DISM rather than the complexity of the setup in the original server core..

    where a basic install would have been

    start /w pkgmgr /iu:IIS-WebServerRole;WAS-WindowsActivationService;WAS-ProcessModel

    using DISM the equivalent command is

    DISM –online /enable-feature /featurename:IIS-WebServerRole

    From there you can then add the extra individual features you need, as from IIS 7 it is a very modular solution. For example to turn on you would use to turn on three features..

    DISM –online /enable-feature /featurename:IIS-ISAPI Filter

    DISM –online /enable-feature /featurename:IIS-NetFxExtensibility

    DISM –online /enable-feature /featurename:IIS-ASPNET

    Having done all of this the best approach would then be to remotely manage it from another machine.  Form there you can start use some of the new stuff in IIS 7.5

    My top feature would be the best practices analyser.  This is primarily concerned with making sure your configuration is as secure as possible,  e.g.

    • Hiding custom errors from displaying remotely, the more the attacker knows about your site the quickest and easier it is to attack, for example one of mates always rubs his hands with glee when he says the site is talking to Oracle as opposed to SQL (he wouldn’t tell me why though).
    • Ensuring ssl certificates are up to date
    • Ensuring you are using ssl if you have basic authentication set.

    For more on this refer to this article in the Windows Server TechCenter

    Other new stuff includes

    • Application pre-load, so your application is already running when the user access it. To do this you need to add the following to the applicationHost.config file:

    <application path="/myapp" preloadEnabled="true">
    <virtualDirectory path="/" physicalPath="c:\inetpub\myapp"/>

    and this to the web.config file

    <add value="default.aspx"/>

    There’s an article here to get you started with this

    • Configuration Log Tracking.  This pulls in all the changes you make and logs them. BTW this is off by default and you need to turn it by going to  “Application and Service Logs->Microsoft->Windows->IIS-Configuration” in event viewer and enable tracing. BTW  IIS generates 4 kinds of events. These are administrative, operational, analytic and debug. Right click on areas in event viewer and select “enable log” for categories you want to enable tracing.
    • The ability to work with multiple versions of the .net framework. While one application might need .net 2 another might need 3.5
    • Built in FTP and WebDAV support.

    I could go on and on here, but I would suggest you might want to check up on all the new stuff in IIS 7.5 here, and hopefully try it out by installing the role on a full or server core installation of windows server 2088 R2.  It’s also an opportunity to confuse the web developers you might be working with by understanding their world a little better, and offering to help them deploy their applications more quickly.

  • Server Core in Windows Server 2008 R2 – part 3

    If I have convinced you to look at server core there some things you need to know to get started.  There’s a simple step by step guide on the initial release of server core here, most of which still applies to server core in R2.

    • First and foremost as part of Microsoft’s 3D security philosophy Windows Server 2008 R2 (and therefore core) is secure by default i.e. locked down so you can’t remotely manage it initially.
    • netsh advfirewall set currentprofile settings remotemanagement enable

      Once you have done this you can then remotely manage the firewall to allow other traffic in, so you can then remotely manage the server. The step by step guide has all this detail and command lines for joining the a domain setting up dhcp and so on.

    • Enable Powershell.   The reason you want to do this is called James O’Neill.  He’s taken the pain out of configuring server core with his configurator on codeplex.  But before you can use this you need to turn on PowerShell using the DISM (Deployment Image Servicing and Management) tool :
    • dism /online /enable-feature /featurename:MicrosoftWindowsPowerShell

      You’ll also need to allow PowerShell to run your scripts (more secure by default stuff)

      set execution-policy remotesigned

      James’ stuff will now allow you to do pretty much anything in that step by step guide but more easily.  You might also want to install the .Net framework..

      dism /online /enable-feature /featurename:NetFx3-ServerCore

      If 32-bit support is needed:

      dism /online /enable-feature /featurename:ServerCore-WOW64

      and then

      dism /online /enable-feature /featurename:NetFx3-ServerCore-WOW64

      Note DISM is CASE SenSitiVE

    You’ll need to do all this if you want to install SQL Server 2008 or R2 and then do a command line install along the lines of ..


    /SQLSVCACCOUNT="<DomainName\UserName>" /SQLSVCPASSWORD="<StrongPassword>"


    The details of this are on Books OnLine here, but as I have said before this is NOT supported.

    Next post is how to setup and manage IIS 7.5 on Server Core.