Insufficient data from Andrew Fryer

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

May, 2013

  • Server 4024 part 1 – Virtualisation

    A database is only as good as the operating system it resides on. SQL Server only runs on Windows and given that Windows Server 2012 (WS2012) is a huge change from what there was in Server 2008 R2 what does this new OS mean for the DBA.  The reference to Server 4012 is because that would be WS2012 + SQL 2012 which is what you want be running to get the most out of your modern hardware and push that through to your database engine.

    In this particular post I wanted to look at what Hyper-V in Windows Server 2012  does for SQL Server.

    It used to be that SQL Server was limited by what the operating system could surface but in a virtual world this becomes what the hypervisor provides to the virtual machine (VM). In WS 2012 all of the limits for VM’s have gone up by at least 4X..



    In many cases these limits are higher than the specifications of many physical servers that you are running SQL Server on today.  There’s also lots of use made of the latest developments in hardware that you may not have on your servers yet, for example:

    • NUMA support , so you can pass through NUMA nodes to a VM for optimal VM performance or allow your VMs to span NUMA nodes..
    • SR-IOV is away of making a PCI card look like multiple PCI devices each of which can be presented to a particular virtual machine.  In WS2012 there is SR-IOV support for network cards (NICs) so that the virtualisation is handled by the card not the hypervisor.  The clever bit that’s currently unique to WS 2012 is that you can live migrate a VM using SR-IOV to server that doesn’t have it , i.e. you don’t have to stop the VM.
    • IPsec can also be offloaded to NICs that support it
    • Support for 4 x Fibre Channel HBAs in a VM.  Note this doesn’t prevent live migrations either but the setup of the Virtual SAN must be the same on the source and target hosts.

    While I am on about hardware, Hyper-V also introduces a new hard disk format, VHDX which can be up to 64TB (Note the VHD format is still supported) which also allows you to efficiently use the 4KB sector size on the newer larger hard disks by having a logical sector size of 4KB as well.

    On a physical server running lots of VMs you’ll want to ensure that SQL Server gets a predictable set of resources, such as CPU, RAM.  Hyper-V has always allowed you to set and prioritise all these, and in WS2012 you can also set Network bandwidth maximum and minimum for each Virtual NIC.

    In the past SQL Server hasn’t been virtualised because of concerns about performance. As I have shown that doesn’t really apply to WS2012, but you will need to follow best practice for setting it up and confirm that you are getting the performance you are expecting.  The best practice is on the SQL CAT (Customer Advisory Team) blog, and your testing should show that you are getting about 93% of the performance you had on your equivalent physical hardware.

    So please  ensure your SQL Server is given a good home and try it on Windows Server 2012


    note: I’ll be discussing this during my sessions at SQL Relay (in Glasgow, Leeds, Birmingham & Norwich)

  • Read it and keep

    I have to confess to having quite a few real books in my house. Some of these books are reproductions of originals or have really big hi definition pictures in so they don’t work well as e-books.  I also find that on occasion technical books work better in print than they do on a small ebook reader.  Having said that I only have finite shelf space so I only keep the good (for me) stuff.

    When I got  my review copy of Windows Server 2012 Hyper-V Installation and Configuration Guide written three of my MVP friends ( Aidan Fynn, Damian Flynn and Patrick Lownds), I honestly thought I'll do a quick post on it then give it away as a prize as I thought I was reasonably competent with this new OS.  It turns out I was wrong!

    Firstly the book goes deep in to corners that I have only briefly looked at like how virtual (aka software defined) networks work in Hyper-V.  This is hard because it is only surfaced in Windows Server 2012 through PowerShell cmdlets.  If you want a UI then you’ll need to use System Center Virtual Machine Manager 2012 sp1.Actually the PowerShell in this book is another reason I like it, as it shows the art of the possible and that may help some of you get over the hurdle of learning it because you can see all the good stuff you can do once you have mastered the basics.

    Other examples of good deep technical content are the numerous grey boxes in the text e.g. Linux considerations on NUMA, what’s the right number for simultaneous live migrations, and Backup and Virtual Machine Mobility.

    Secondly there’s a lot of good discussion on when to use what, for example

    • Anti-Virus software for the physical hosts,
    • typical uses of file shares and SMB3 to host VM’s and what this means for high availability
    • How to use the new Fibre channel support in Hyper-V

    Finally it’s well written. By that I mean it’s been written from the ground up not a bad rehash of an earlier version. Like any good book it takes you on a journey in each section from simple to complex, so initially you’ll what something is like NIC team and learn to do something via the UI and then you’ll get all the nuances and best practices and real world stuff.

    So if you want a copy to help you get the most out Windows Server 2012  get your own, your not having mine!

  • Microsoft Free Software – a personal top ten

    You might think this will would make for a really short article post but actually  there’s a  huge amount of free tools and resource out there and I have had to restrict myself to a top ten across the server and client,  based on what Simon and I have used.  So please feel free to comment with your own and I’ll see what we can do about maintaining a list somewhere and rewarding good suggestions.


    Hyper-V Server

    Yes Microsoft does have a free operating system, although it’s just restricted to the ability to run highly available virtual machines.  With Hyper-V Server you are limited to running 8,000 virtual machines on a 64 node cluster and you can only put 64 logical processors in a virtual machine. Also note that there is no graphical interface as this OS is very like Server core and is designed to be remotely managed.  (I have a separate post just on Hyper-V Server here).


    Microsoft Security Essentials

    If your organisation has less than ten PCs then this is the FREE antivirus for you, and it’s also free to use at home.  Security Essentials uses the same signatures as System Center Endpoint Protection and has won a slew of awards for being very user friendly.  You can install this on XP and Windows 7, but for Windows 8  and Windows RT Windows Defender does the same thing and  included and


    System Center Advisor

    This is a lightweight best practice analyser for Windows Server and SQL Server environment.  It uses the same agent System Center Operations Manager agent to collect telemetry about your servers and then sends this every day to Advisor Service.  The Advisor Service then provides reports on error and warnings you need be aware of.  It uses your own certificates so it’s secure.  Like Operations Manager you can configure a gateway to collect the information from other internal servers and then send this daily to the Advisor service.  (I have posts on  how to set it up and how to use it).

    SQL Server Express

    If you only need a small database server the there’s quite a lot you can do with SQL Server Express.  The tools are essentially the same as its bigger brother and you get reporting services if you need it to deliver rich reporting of your local database. If you don’t need all the tooling and just want a slimmed down engine behind your application then there’s an option LocalDB



    The Microsoft Assessment and Planning Toolkit seems to be universally ignored, despite being really useful in planning any kind of upgrade or migration, or jut to make sense of what you have got already – given that you might be new into post. What it does is to crawl your datacentre with various credential you supply and tells you what you have.  This might be nothing more than how many servers do you have and what OS are they running, and even that in a world of virtual machine sprawl can be useful.  However if you were then to use it plan a Windows Server 2012 migration project it would allow you get reports and plans on how to do that and what you’ll need.  It’s constantly updated so always be sure to get the latest version.

    One other thing to note while it will allows you to assess your licensing estate the data is NOT reported back to Microsoft, so you won’t be getting loads of phone calls once you’ve run it, but you will at least know where you are.


    Data Classification Toolkit

    Knowing about your infrastructure is one thing, what matters more is the data that’s in it and to make sense of that there’s the Data Classification Toolkit. Like other solution accelerators it’s continually updated and in this case is now aware of the latest tools in Windows Server 2012 like Dynamic Access Control (My post on getting started is here).

    Please note the small print : Use of the Microsoft Data Classification Toolkit for Windows Server 2012 does not constitute advice from an auditor, accountant, attorney or other compliance professional, and does not guarantee fulfilment of your organization’s legal or compliance obligations. Conformance with these obligations requires input and interpretation by your organization’s compliance professionals.


    Windows Assessment and Deployment Kit (Windows ADK)

    Simon and I still see a lot of weird and wonderful ways to deploy operating systems at scale, which is odd when Microsoft have two free of tools the first being Windows ADK.  Actually the ADK should count as several free things itself as it contains a number of useful utilities such as:

    Microsoft Deployment Toolkit

    This is another tool that’s kept up to date, in this case it too support scale deployment of Windows 8 and Server 2012.  It may seem like an overlap with the Windows ADK but that toolset requires knowledge of a lot of command line utilities like DISM, where the MDT is a UI driven process.  The


    The Office Environment Assessment Toolkit (OEAT) scans client computers for add-ins and applications that interact with all versions of Office back to Office 97. It’s designed for detecting compatibility issues but I have seen it used to track down large spreadsheets which means someone in your organisation is using Excel instead of a proper database, which at best might mean there could be data quality issues in some of your reporting and at worst might mean you are storing customer and confidential information where it is not being properly controlled 


    Windows Live

    I still use three utilities from Windows Live to get key tasks done

    Note none of these run on Windows RT

    I also wanted to share my also rans that didn’t make my top ten..

    Windows Sysinternals:

    ZoomIT A Windows Sysinternals tool to make areas of your screen bigger

    BGInfo Also part of the Windows sysinternals tools which shows key informatio0n about  your servers on their desktops.

    RDCMan to manage remote desktops' great for managing Windows 8 / Server 2012 as it’s not based on RDP8 and so the charms etc. don’t work

    and finally for a bit of fun Ordnance Survey Maps - I occasionally need to get out of the office and off-road.  Street View is fine but what if there are no streets and you need to get from A to B for fun on foot or by cycle.  In this instance Ordnance Survey maps are your friend and they are free on Bing Maps if you are in the UK (just select Ordnance Survey from the left hand drop down list of map types)e.g.


    You can print them as well if you don’t want to take your slate, tablet, phone with you

  • So You want to be an Evangelist

    With my job title of Evangelist I often get asked about what my roles, both inside and outside of the Microsoft firewall.  In the 2 weeks I have presented to the Leeds VM User Group (, done a careers chat at a Science college, produced and presented at TechDays Online nd attended SQL Bits 11..


    So it’s a lot of presenting, blogging and explaining stuff.  Of course you can only blog about what you know about, so Simon and I spend a lot of time learning how Microsoft technology works, and talking to IT Professionals about what they are doing and the challenges they face.  I hope this gives some credibility both online and in person and given you are reading this that seems to be working.  Our knowledge acquisition goes on all the time but occasionally it's good to commit longer periods of time to it and so we get the opportunity to go to things like TechEd in Madrid.  For me this checks all the boxes and gives me the chance to hang out with the Microsoft product teams who present at this sort of event. 

    As an Evangelist I don’t have to pay for my flights, hotel or entry fee, Microsoft pick all of that up for me. In return I will write posts, work up ideas for future presentations and share stories good and bad about how our products are actually being used. 

    The question is do you fancy being evangelist for a week and come out with our team to Madrid on Microsoft expenses?  if you do then you’ll want to enter the TechEd Challenge



    From here you can either enter a draw for a place or compete for one of three further places by writing a blog post to show off your evangelist skills.  Full details of the prizes are here, good luck and hopefully see you there