Insufficient data from Andrew Fryer

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

April, 2009

  • Missing Snipping Tool in Windows 7

    I live my little snipping tool to capture bits of screen to share tips and stuff on this blog and in my decks, but since I moved to in Windows 7 it’s gone aaarrghh! Annoyingly all my mates (both of them) have got it on their machines so where did it go?

    Apparently it’s part of the tablet specific optional features and I didn’t have this selected. So to fix it (and now I have the tool installed I can show you!) go to programs and features (you can just type that in on the search bar to go straight to it) and from there select turn windows features on or off ..


    to get this dialog


    ..and check the option for Tablet PC Components.

    For my blog I also use a lot of the picture editing tools in Windows Live Writer especially crop  and border style you can get the latest version of this free blogging tool here.

  • Windows Server 2008 Foundation - a new home for SQL Server

    A new edition of Windows Server 2008 got launched yesterday Windows Server 2008 Foundation which will only be shipped on a range of appropriate servers.  It supports 15 users, 8gb RAM and 1 processor  (but no core limit). 

    I can see that many small business might want to use this as their central server where they may have some of their data in the cloud (the obvious example being e-mail) but want some information such as the company accounts on premise. I can also see Foundation as being a logical home for SQL Express or Workgroup edition, probably bought along with the aforementioned accounts or a small ERP system.

    Companies wanting to do a little bit more on premise would probably be better off with Small Business Server (SBS) which has exchange and SQL Server built in as well as a basic set of tools for monitoring the health and security of the server and the PC attached to it.

    As a final thought even though April 1st was yesterday, I wonder if this would be a good platform to host LAN parties!


  • MetaData in the Microsoft BI stack

    Metadata is “data about data” and in the BI world this means two things:

    • Definitions of terms, particularly calculations so that business users can understand what they are looking whether on screen or on paper. 
    • Lineage to understand where and how the data in a report was derived.

    There are a number of placeholder in all parts of SQL Server to support this, from extended properties in the database engine , actions in analysis services can take you to a website describing each calculation, and integration services extensive logging capabilities.

    To help make sense of your options there is a Metadata toolkit containing a whitepaper and and a number of tools:

    • DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.
    • DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.
    • Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.
    • Lineage RepositoryA database called SSIS_META that can be used to house metadata from nearly any system.
    • Reports Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.
    • Report ModelA report model that you can use with Report Builder to allow end-users to create ad-hoc reports.
    • Integration Services SamplesA few sample packages to start auditing and viewing lineage on.

    This is all designed to work on SQL Server 2005 and should be fine with SQL Server 2008.  My only word of caution is that report models are not being developed further and that the whitepaper only refers to Report Builder v1. 

    Anyway it’s a useful (and free) set of tools and you have the source code to hack it around to get it do what you need. e.g. compliance requirements (Sarbannes Oxley, Data Protection, Basel II etc.) or simply to make your BI project more accessible to your users and easier to maintain.

  • DeepZoom InSecurity

    DeepZoom is a cool technology for er  zooming into photographs.  Some fun loving security guys (yes we do exist) have come up with a security game using this technology..


    To play go to IT Insecurity, on the TechNet site and if you can find the five security issues in the image you can win a full copy of Windows 7 (and be kept up to date with the pre-releases until it comes out).

    I have to confess we’ve only found four so far so this is not like the standard easy answer thing to enter a prize draw.

    Technorati Tags: ,
  • Getting Safe OffLine by going green

    Hopefully the Getting Safe OnLine message is getting out there and you are ensuring that all computers are properly protected both at work and at home. But what about security worries when you’re offline, we all get statements from banks and utilities like gas electricity plus council tax and these can easily be used to clone you identity and get a new credit card.  Even worse than these are credit card cheques and semi completed credit card applications. 

    My top tip is to shred them and then compost them..


    Apart from being secure (OK somebody could go through your compost heap and stick all the bits back together but it’s not likely), it can really help make your garden greener .  The shredded stuff is added in to the grass cutting which allows air in and prevents the grass going smelly and slimy. From a technical point of you if you don’t get this right you end up releasing a load of methane into the atmosphere which is 100 time more efficient as a greenhouse gas than carbon dioxide is.

    6-8 weeks after you have done this it all goes back on the garden with your flowers or in my case the vegetable, which is a lot tastier than trying to just eat all the paper.

  • Reporting Services - Nothing to see please move along

    Sometimes when you run a report it’s not going to return any rows, perhaps because the user selected a filter for which there is no data, and that needs to be handled properly.

    Fortunately there’s a simple way to  control the messaging when this occurs to ensure your users know what’s happened and don’t assume it’s all your fault.

    For the tablix data region, chart or sub-report go to the properties for it and enter an appropriate message as shown below..


    If you don’t see the properties in Report Builder 2 got to view and check the properties option..


    So like any kind of development (in BI analysis services would be a good example) it’s important to review the properties and ensure you get the behaviour you need .

  • Spatial Data in SQL Server 2008

    All editions of SQL Server 2008 hav the new spatial data type geometry and geography (including express) and if you want to know more about doing interesting stuff with spatial data you really need to get to Microsoft’s offices…

    Map picture

    on 16 April for a dedicated SQL Server community event on this subject given by spatial experts Johannes Kebeck and Simon Sabin. It’s being run twice , once in the afternoon and then again in the evening so hopefully you can make one of these (please follow the links to register). 

    However that may not be possible, for example I will be in Sofia at a Bulgarian Microsoft event, so my other top tip is to check out the new Appress book on this subject as I found it very comprehensive and it is the only dedicated work on this I know of.

  • FileStream Data and Deletion

    Deletion on computers is often a bit of a sham, as I found out when I got in at the ground floor of forensic computing a long long time ago.  Back then file systems were very lazy so when you deleted something all that happened was the file system marked the blocks occupied by that file as being available, in its catalogue.The process didn’t actually change the blocks themselves, that was left until the the blocks were next reused by another file.  This meant I could snoop around and recover them and if necessary produce them in evidence.

    I mention this because the new SQL Server FileStream (sort of) data type works in much the same kind of way. 

    Note:For those that don’t know FileStream allows for large files to be stored in a special filegroups so that for backup and transactions they behave as part of the database.  However they actually still exist as individual files (pictures , videos CAD drawings or whatever) on the file system albeit in SQL Server controlled paths and with generated names as part of that special filegroup.  This means they can also be streamed in and out of SQL Server nearly as quickly as though they were ordinary files.

    If you delete a row with a FileStream column the row itself gets deleted, but the FileStream file seems to stay behind even after the transaction is committed.  That can’t be good otherwise you’d soon fill up your hard disk and you’d have all those orphaned files lying around which might be a security risk.

    The answer is garbage collection, which unlike in SharePoint is an automatic background process that follows on behind checkpoints,  for more information check Books Online (written by one Paul Randall!).

    The other interesting thing I picked up here is that virus checkers could check these raw files (because they will still have their signatures in them but just have odd names and paths).  That’s a good thing for security, but the virus checker will have permissions to delete infected files than it is going to start punching holes in your referential integrity so it might be best to mark them as quarantined and then look at the anti-virus log and DBCC CheckDB can be used to work out what’s missing so you can clear up the mess.

    While I am on the subject FileStream data cannot be compressed with SQL Server compression and Transparent Data Encryption doesn’t apply to this data, rather you are encouraged to use the Encrypted Files System (EFS).

  • SQL Server 2008 sp1

    SQL Server 2008 sp1 has now been released.  For some this will mean access to the three key features of the release:

    • Slipstream allows administrators to install SQL Server 2008 and Service Pack 1 in a single instance. This decreases the total time for an installation, including a fewer number of reboots thereby increasing productivity and deployment availability.
    • Service Pack Uninstall allows administrators to uninstall the service pack separately from the database release.  This feature also improves DBA productivity, reduces the cost of deployment and improves overall supportability.
    • Report Builder 2.0 Click Once improves the existing SQL Server end-user report authoring application by easing deployment to business users.
    • Not a lot else, just minor tweaks and fixes rolled up form the previous cumulative updates.

    For many others the release of sp1 signifies that SQL Server 2008 is now stable enough for them to begin to deploy in their organisation.  I have always found this approach to releases somewhat flawed as any patch fix service pack or release can have flaws in it, and surely you would expect that a major product release would be more thoroughly tested than a service pack.  In this case both SQL Server 2008 and sp1 had an extensive testing cycle internally on Microsoft’s 5,000+ database servers and externally via very wide community technology previews (CTPs). 

    Given that there have been over 3 million downloads of SQL Server 2008 since it was released a better litmus test of its reliability is simply the buzz on the forums (e.g. the UK SQL Server community), because if it was flawed in some way the community would not be shy in saying so!

    The really sensible thing to do is to form your own opinion and you can start doing that by downloading an evaluation copy  or the full version from your TechNet subscription.

  • Microsoft Security Intelligence Report vol 6

    Every 6 months Microsoft publishes a freely available Security Intelligence Report (SIR), looking at what’s happening to threaten the security of our personal and corporate computing.  Mostly it‘s written in English and if you can’t find the time to read it all I would at least recommend the summary.

    All this work is part of Microsoft’s commitment to security. Another part of this commitment is Vista. It���s the security in Vista that means that some applications don’t work (they want resources and access levels that are no longer allowed), it meant that many drivers had to be rewritten (for much the same reasons) and it shows up again as User Account Control whenever you install something.  All of this affects IT Professional like me more than my mum, because I’m always installing stuff (like SQL Server), playing with new devices (or really old ones!) and trying out software. 

    However the benefit of that security investment in Vista shows up well in this graph from the SIR report..


    ..where the fewer the problems the better.  The benefits of applying the latest service packs can also be seen especially for XP and it’s also interesting to see that Vista scores better than Windows 2000.  Windows Server 2008 scores best of all, and this is because of the core it shares with Vista, plus the way the modularisation of it’s features and roles reduces its attack surface to only what is needed.

    My parting shot is that my wife’s 3 month old office laptop (dual core 2Gb RAM) runs XP and runs like a snail, because of all of the add-ons to make it secure such as third party firewalls, VPN software and disk encryption etc. 

  • Learning Bulgarian SQL



    I am presenting in Sofia for a few days at Microsoft Days 09 and I don’t speak any Bulgarian!  Fortunately Microsoft and English seem to go hand in hand, and I have been sitting in on sessions about Windows 2008 r2 and SQL Server when I am not presenting and I can understand most of the conversations.

    I got asked one odd question why do we say “sequel” and not “S.Q.L”? My Bulgarian colleague said it was because it was originally known as “Structured English QUEry Lenaguage” and was coined by the IBM research centre in 1974, which turns out to be annoyingly correct.

    BTW this event is paid for, and I wonder how that would go down with the TechNet audience in the UK?

    Technorati Tags: ,
  • SQL Server standard edition in a virtual world

    One of the limitations of SQL Server is that it only supports 4 processors (as you can see here).  That translates into 4 virtual processors if you run standard edition in a virtual machine.  If there are more than four processors available whether physical or virtual then standard edition will only use 4.

    However if virtualisation is something you are serious about then you also need to be aware of how SQL server standard edition is licensed when virtualised.  Essentially each running VM that has SQL Server standard edition installed on it needs to be licensed as though it were a physical machine.  The same applies to Workgroup edition, while Enterprise edition is licensed differently.

    With Enterprise edition you  license the physical server, and you are then free to run as many instances virtual machines as you want on that physical server.  This could actually be cheaper than buying standard on larger server particularly with six and eight core CPU’s on the way (remember you license CPU’s not cores).

    Also please be aware that all of this applies to any of the virtualisation platforms you can run SQL Server on, not just Hyper-V.

    I Hope this helps but if you need more clarification then my good friend Emma Healey aka “Lady Licensing” has more on this at

  • Windows 7 DirectAccess my new best friend.

    It’s tough trying to work from home with the feeble broadband speeds that exist in many parts of the UK. I get 0.5mbps when I am paying for 8 (which is still poor even allowing for how far I am form the exchange) and so I often have to travel just to download data or do live meetings.

    However I have just been trying out a little ray of sunshine in Windows 7/ Windows Server 2008 r2 called DirectAccess.  It’s essentially a VPN without all of the grief about extra software and the poor network performance that often plagues these solutions.

    To a user like me all I get now is this in my network connections..


    Behind the scenes there is also Network Access Protection (NAP) which checks the health of my machine before letting me anywhere near any corporate resources.  For example my anti-virus software needs to be up to date as well as all my software updates.


    All is well so I have corporate access, and to give an example I can look at mysite for me ..


    The presence for all my team mates is on the right but it’s 07:20 so they are all off line at the moment!

    The best thing for me about this setup is that any internet access is not routed through the VPN and also the VPN doesn’t slow down Live Meeting and Office Communicator so I can collaborate without having to travel.

    The best thing for well connected users is that this doesn’t require any special ports to be open so if you have internet access at a hotel airport lounge or a customer site this will all work. 

    To find out more there is a technical overview of how it works here.