Insufficient data from Andrew Fryer

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

August, 2013

  • Spruce Up your Data Centre

    The summer holidays can generally be a quiet time for some IT Pros, depending on the industry you work in, so I wondered if this would be a good time for a bit of a tidy up in the data centre.  The easy bit of that is to actually tidy up the physical environment such as cabling left lying around or temporarily put in place but has now become “live”.  Actually I would love to see some photos of server room chaos, and I am sure Sara can organise a T-Shirt for the messiest.

    What I actually meant was tidying up the data on the servers. At the highest level you might have whole test or evaluation setups that you don’t really need anymore which might make up several VMs.  There might be individual random VM on there as well. The challenge is can they be stopped and archived and that depends on what the owner feels about them, and so a key technique for efficient data centre management is chargeback or at least showback, as waste is a lot lower when you are paying for something!

    VMs are very easy to snapshot/checkpoint and hopefully you are aware of the impact of rolling back/reverting to a checkpoint on any given VM, and if you can’t revert to that checkpoint is there any point in keeping it?

    Then there is a question of what is in those VMs, You might worry about whether they are all properly licensed, and actually if the licenses are expired is the VM any use anymore anyway?  You might also get some licenses back if you can’t shut redundant stuff down.

    Looking at the software that’s on all those VMs,  are they patched and up to date?  Even if it’s a test setup that should be patched to a desired configuration to match the thing that you are testing which actually might be the application of a patch.

    Then there is the data that’s on there: Is that dev, test or production data, and what protection should be accorded it?    The VM itself may well have backups inside it which could be redundant and hopefully you environment will let you reclaim space if you shrink VMs to reclaim that space.

    Those are some of the problems you might want to address in your summer spruce up but how to find the problems in the first place?  In the Microsoft world there are a couple of tools:

    • System Center. If this is being used as intended then you’ll know some key things about your services, VMs and data..
      • Who owns them,
      • Are they compliant with your desired configurations for production, dev and  test.
      • What resources they are using and how close to any thresholds are they
      • What software they are running (I am assuming here you managing servers via Configuration Manager)

    so you know where to start looking to clean things up, and possibly if you are using self service then VMs that are end of life will automatically be decommissioned

    • Microsoft Assessment & Planning Toolkit.  This is a free tool which you run as required against your data centre that  reports back what you have, and this can include non-Microsoft stuff as well.  You’ll need to give it various credentials for the discovery methods

    The next thing is to ensure you have a good backup strategy and see then get rid of the deadwood safe in the knowledge you do have a backup.  Of course you might then want to revisit retention of the backup if no one notices that you got rid of loads of stuff.

  • SQL Server Spruce up

    Landrovers, will take a lot of pounding and neglect, but when my wife drove hers to Australia she made very sure it was properly set up for 2 years on the road


    Similarly SQL Server is also often out in the wild far from DBA’s and inspection from maintenance tools, like System Center.  However now might be a good time for a bit of TLC if things are quiet for you in August. In my last post I dealt with Servers in general, so today I want to look at a SQL Server spruce up, particularly for those who are not full time DBAs. 

    As per that post you may well be able to decommission whole VMs running SQL Server, but what I want to cover here is what you might want to check at the on instances and the databases themselves.  Books have been written on this but I would be interested in:

    • Compatibility level – the ability to get a shiny new copy of SQL Server look like an older one, should only be set where you actually need to have backward compatibility.  Note that by default this is kept at the old level after an upgrade from an older version. 
    • Memory reservation should be set less than what’s available and not left blank. The question is how much less, and the answer is what else is running on that server but at least 768Mb for the operating system itself
    • Where and how many TempDBs there are, as per TechNet guidance here.

    For individual databases you might want to check that

    • Statistics are up to date for all your tables
    • Checking for and repairing index fragmentation
    • There is no extraneous fluff in your databases such as copies of tables, and indexes as well as remnants of dev code like spare views and procedures.

    Rather than perform all of these sort of checks yourself,  you could deploy System Center Advisor which is a free cloud based service.  It matches your installation and database against best practice from Microsoft Premier Feld Engineers and tells you every day what you need to worry about.  It can be securely deployed behind a gateway in your infrastructure so your actual SQL Servers don’t need to be internet facing and I have posts on how to do that here.

    Finally you might also want to benchmark your database by putting a known load on it that you can use as a reference when making any changes to it, such as virtualise,  it upgrade it etc.  To be honest I couldn’t find too much on TechNet/MSDN/Codeplex to help with this so you may want to resort to third party tools such as  Dell(Quest), Idera, RedGate, SQL Sentry,  and dare I mention PowerShell (as per this article by Aaron from SQL Sentry) etc.

  • Microsoft Valuable Penguin

    I sometimes think the IT industry is a bit like a load of penguins on the pack ice, each one checking the others out to see who going to brave the ocean first.  In IT it’s nervousness about when to jump onto a new technology and if there is one technology that makes IT professionals really nervous then that would be cloud.  So when Microsoft launched the Cloud OS, there was always going to be a some nervousness about it and a reluctance to jump in and try it no matter what it did or how good or bad it was.  However there are some pioneers out there who have already implemented the cloud OS and some of these  are MVPs (Microsoft Valued Penguins Professionals), as they not only try out the new stuff like the Cloud OS, they share their knowledge ad experience as early adopters. Moreover unlike me they are independent, and although well connected into Microsoft they can on occasion be very vocal when they feel there is a real problem or missing feature in a new product.

    There are about 200 MVPs based in the UK and some of the best of them have decided to run a series on the Cloud OS where they can share what they are doing with it and what they have learned by implementing this in production.  The event runs 9th-13th Septembers in Microsoft's London Offices and the agenda looks like this..


    Monday 9th September

    Please register to attend either track 1 or track 2:

    · Track 1 will focus on building the modern enterprise data platform. In a series of three presentations we will tackle the issues of architecture, application frameworks, data integration and data exchange; learning all about the challenges faced by the modern data tier developer. Most importantly, we will learn how to creatively overcome them by enhancing our processing efficiency and analytical capability. Register to attend

    · Track 2 will focus on the creation of Business Intelligence and advanced analytics solutions that utilise both structured and un-structured data. We will demonstrate the use of data mining and predictive analytics technologies and also demonstrate how advanced visualisation technologies can be used by business users to deliver the insight and action required to drive real value from data.

    Register to attend


    Tuesday 10th September

    Each session will demonstrate how to:

    · Deliver best practices with Windows Server 2012 R2 and System Center 2012 R2

    · Lower costs through effective management of VMware and Hyper-V

    · Enable management of datacentres of any size!

    · Drive automation of complex applications with service templates

    Register to attend


    Wednesday 11th September

    Sessions will include:

    · Windows Azure Service Bus

    · Windows Azure BizTalk Services

    · Microsoft BizTalk Server (both on-premise and Cloud Virtual Machine)

    Register to attend


    Thursday 12th September at the Microsoft Office, London, Victoria

    Join leading MVPs for a one day event to understand how to manage your client devices in a single tool while reducing costs and simplifying management. Best of all, you can leverage your existing tools and infrastructure.

    Sessions will include:

    · Helping with data security and compliance

    · Unified device management

    · What powers people-centric IT with Cloud OS?

    · Real World customer examples

    Register to attend



    Friday 13th September

    The explosion in devices, connectivity, data and the Cloud is changing the way we develop and deliver software.  New infrastructure services permit existing server applications to be “lifted & shifted” into the Cloud.  Attend a one day event to hear from MVPs about how Microsoft’s data platform and development tools enable you to develop, test, and deploy applications faster than ever.

    Sessions will include:

    · Infrastructure services,

    · Media services,

    · Service Bus  & Mobile services

    Register to attend



    So please register and pppick up an MVP, and learn about what this Cloud OS is really all about.

  • Green IT Fatigue

    I recently got asked to do an interview on  for TechWeek Europe about green initiatives in the IT industry. However let’s be honest, computers burn a lot of power, require a lot of power to make and are made of some nasty exotic compounds and chemicals, so they aren’t going to save the planet by themselves.  However a few years back everyone was talking about Green IT, and more properly sustainable IT, and while that topic is no longer trending, we don’t seem to have done anything about it and Green Fatigue has set in across IT . 

    Looking at what has been happening in the data centre then good work has been done, but not in the name of green IT. For example server consolidation has meant physical servers are better utilised now; they are typically running 10+ VMs each rather than idling at 10%.  We have also got better at cooling those servers, but this has sometimes been driven not by a green initiative but because of the cost of power and the capacity available from the power supplier in a particular location.

    Later versions of virtualisation technologies always make best use of the latest hardware but swapping out server hardware to get the benefits of the latest CPU or networking has to be balanced against the cost of making the new server and disposing of the old one, so you’ll want to focus on how you can extend the life of your servers possibly by just upgrading the software.

    Virtualisation by itself can also cause more problems for the environment than it solves because while you  have achieved some consolidations you may well end up with a lot more VMs that aren’t doing much useful work.  Effective management of those VMs is the key to efficiency for example:

    • Elimination of  Virtual Machine sprawl.  Typically this shows itself as a spread of numerous dev and test environments, and the only way I can think of to check this over use of resources is to charge the consumer for them on the basis of what they have committed to use so chargeback or at least showback.
    • Dynamically optimising a workloads based on demand – Reducing the capacity of low priority under used services or stopping them altogether to free up resources for busy services without needing more hardware.  
    • Extreme Automation to reduce the number of IT guys per VM, these reduces the footprint per VM as each member of IT uses energy to do their work and often has to travel to work so if this can be distributed across more VMs than that is more efficient.

    These three things are actually all key characteristics of clouds so my assertion is that cloud computing is more environmentally efficient, without necessarily being Green IT per se.  Given the fact that public clouds operate at much greater scale and efficiencies than what is possible in many internal data centres1 plus they are often located specifically to take account favourable environmental conditions all of which means they are greener than running services in house. 

    So we are getting greener, it’s just we don’t call it that, and no doubt no that we are fed up with the word cloud as applied to IT we’ll change that to something else as well.  





    1 Internally a Microsoft Global Services defines a data centre having more than 60,000 physical servers

  • UK Data in the Cloud

    There is still a lot of inertia in the UK about storing data in the cloud for entirely valid reasons and rather vague uncertainties and doubt.  For a few organisations keeping data in the cloud is exactly the right thing to do because those organisations want to actively share their information, the most obvious is the UK government with their initiative.  Commercial companies may also want to sell their data and rather than opening up fat pipes into their data centres the logical approach is to have this hosted on a public cloud as well. 

    I mention this because one aspect of Azure that Microsoft rarely talks about is the Windows Azure Marketplace (WAM), a portal where for the sharing and consumption of large data sets.  Originally this was just US based like a lot of Microsoft services, but over the last year or two it has grown steadily so that there are now a significant number of UK relevant data sets on there, most notably is the Met Office Open Weather Data (and actually part of

    Some of this data you will be paying for based on how many times you query it and so one way to minimise that cost would be to download it then create my own  internal data market  which would also include sets of data from in house systems for users to mash up using tools like PowerPivot.

    You can of course connect PowerPivot etc. to the WAM, and the good thing about this approach rather than just pulling down a .csv file is the connection location is remembered, and this is useful for several reasons:

    • you know where the data came from so it’s verifiable
    • you can easily refresh your analytical model with the attest data form a given source
    • if you wish to scale up your model either to SharePoint in house SharePoint, Online in Office 365 or to a BI Semantic Model in SQL Server 2012, the source is preserved so the model can still be refreshed.

    So the Azure Data Market works well with self service BI to allow analysts to develop models based on external and internal data, say for mapping the weather to sales to develop models to predict demand as I have posted before.

    The other way this data can be consumed is to use it inside an application.  I can see a case for this sort of thing on a property search site where additional local information is bought in alongside the details of the house/flat you are looking for such as schools and their stats, hospital metrics, rail commute times,  and so on.  This will typically incur a cost but would give this site an edge over its competitors and possibly be recouped through advertising.  There are also applications you can integrate with such as translation services and Bing.

    It’s also worth bearing in mind that you could be making money out of your data, by selling it via WAM as well.  Obviously this would not be personal data, so things like market research house price information, trends in the UK job market from a recruitment agency which have been anonymised. 

    Finally there’s extensive help on how to use all aspects of WAM, such as code snippets, samples and hot to videos, and it’s changing all the time so even if there’s nothing of interest right now there may well be next time you look.