Insufficient data from Andrew Fryer

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

November, 2011

  • SQL Server 2012 – Always On

    There have always been several ways to do high availability in SQL Server, but choosing the right one has always been difficult as each approach has obvious benefits coupled with unavoidable limitations:

    Clustering looks after a whole instance of SQL Server containing many databases and is completely transparent to an application. However shared storage adds cost and complexity and there is only the one copy of the database(s) on that shared storage.

    Mirroring creates a continuously updating replica of a given database, failover is really fast and it’s easy for a DBA to setup. However mirroring has several significant limitations:

    • A special connection (SQL Server native client )is needed to mirroring so not all applications can work with it
    • Protecting multiple databases so that if one fails they all fail over is not really possible.
    • There is only one mirror of the database
    • The mirror is not directly usable it just sits there unless you are prepared to work with snapshots.

    Log shipping is sort of manual mirroring which allows more than one replica to be kept; perhaps a local one and a remote one.  This is more difficult to setup and failover is not automatic you have to reset all of this yourself.

    To build a better SQL Server mousetrap, you would want a solution that:

    • Looks like a cluster to any application i.e. there is a DNS entry to the cluster to which the application connects without ‘knowing’ which node SQL Server is running on
    • You would want to treat a group of databases as an object so that they can be failed over etc. as needed in one go. 
    • As with log shipping, there wouldn’t just be one other node behind the primary there would be multiple mirrors/secondaries
    • The mirror could be read only and therefore available for reporting
    • You could opt to have some nodes connecting asynchronously and thus have a remote replica of your databases without needlessly slowing down the primary.

    Up until know that meant that we would have use more than one feature in concert e.g. mirroring and clustering together to achieve the high availability we wanted. What SQL Server 2012  AlwaysOn  does is to provide this combination in one single feature:

    It uses the Windows Failover Cluster feature in Windows Server but doesn’t use any shared storage. A normal install of SQL Server 2012 is then done on each node and the SQL Server 2012 service is then configured to use the cluster..


    Having done that you then tell the SQL Server service on each node to use the cluster the new AlwaysOn High Availability tab in the properties for the service..


    However AlwaysOn is actually doing something very similar to mirroring under the covers, in that there are replicated copies of the databases being protected not just one copy on shared storage as there is for clustering – and AlwaysOn doesn’t need to use shared storage. You’ll also notice that for databases to be protected by AlwaysOn they need to be in full recovery mode and backed up (preferably to a share that’s visible from the other nodes). However with AlwaysOn you can have multiple secondaries and you create availability groups, which are sets of the databases you want to keep together.

    There’s a wizard in SQL Server Management Studio for this where you can specify the nodes, the databases and the options for accessing each node. Note this uses TCPIP ports like mirroring does (so port 5022 by default) and these need to be opened in the firewall for this all to work.

    There’s a dashboard to confirm all is well ..


    There is also an option to create a TCPIP listener which provides an address and DNS entry for the cluster.  If you set this up you can  connect directly to the cluster from any tool that can connect to SQL Server, in this case I have connected to the TechNet cluster from management studio in the same way I would connect to any other instance or cluster..


    However you can also connect directly to the primary or secondary as well and for a read only secondary that’s how you would do reporting.

    I have a short (8 min) AlwaysOn screen cast if you want to know more or have a guide to help you try it yourself.


    Finally be aware that this is not replacing clustering, mirroring or log shipping but it is only going to be available in SQL Server 2012 Enterprise edition.

  • SQL Server 2012 – TSQL

    I have to confess I hadn’t paid too much attention to the changes to T-SQL in SQL Server 2012, and it was only a question at NextGenUG in Abingdon last night that prompted me to have a look.  It turns out there are some really useful new commands, which I could certainly have done with when I was allowed near production code.  So here’s what I found:

    TRY_CONVERT().  If I had a penny for every time I feel foul of trying to convert to dates or numeric from random string data I had loaded up in my BI projects… Anyway this is better solution than ISNUMERIC() and ISDATE() and typically looks like this

    SELECT TRY_CONVERT(datetime2, '12/31/2010') AS Result

    returns 2010-12-31 00:00:00.0000000

    SELECT TRY_CONVERT(datetime2, '11/31/2010') AS Result

    return NULL

    so the same syntax as Convert, and you don’t have to do an initial test but you’ll want to include additional processing to handle the NULL when TRY_CONVERT() fails.There is also a new PARSE() & TRY_PARSE() which deal with converting dates and currency formats across different locales


    differs from CAST() and CONVERT() by providing localised output from localised input from a different locale e.g.

    with the Current date is 15/11/2011 with my machine set to a locale of EN_GB


     SELECT FORMAT( @d, 'dd/MM/yyyy', 'en-US' ) AS Result

    returns 11/15/2011

    so the same date but in US format

    Note This uses the CLR (like HierarchyID and other newer data types) but it doesn't need to be turned on:

    sp_configure 'show advanced options', 1;
    sp_configure 'clr enabled', 1;

    DATEFROMPARTS() builds a date from separate variables from year month day..

    SELECT DATEFROMPARTS ( 2010, 12, 31 )

    returns 2010-12-31

    This is one of a set of functions to build up dates and times to various formats e.g TIMEFROMPARTS(), DATETIMEFROMPART()CHOOSE() allows you to specify which item to pick in a list of items e.g.

    SELECT CHOOSE( 2, 'IT Professional', 'DBA', 'Developer', 'Tester' ) AS Result

    returns DBA

    IIF() works in the same way as in Excel – IIF(condition, value if true, value if false)

    CONCAT () joins strings together to one output e.g.

    SELECT CONCAT ( 'DEEP', 'FAT',’’,’FRYER’, NULL, 50 ) AS Result

    returns DEEPFAT FRYER50

    Note: the output data type will vary according to what is put in and if one of the inputs is NULL then CONCAT() will just ignore the NULL value and join the non NULL values togetherTHROW allows you to raise an error in a TRY.. CATCH block e.g.

    THROW 51000, 'we have a problem Houston.', 1;


    This is a modification of the ORDER BY clause in a SELECT statement..

    SELECT DepartmentID, Name, GroupName

    FROM HumanResources.Department

    ORDER BY DepartmentID


    This allows to you to limit which part of the results are returned based on the ordering clause


    This isn’t an exhaustive list of all this the new T-SQL commands there is other new T-SQL for accessing the other new features in SQL Server 2012 e.g. table valued functions for semantic statistical search, file table, security enhancements etc.

    Finally these new commands are all in the current beta of SQL Server 2012 (RC0),   if you want to try them out.

  • Anti-Virus and Hyper-V, Yes or No?

    The parent operating system in Hyper-V is windows server and it’s a relatively simple matter to install your standard anti-malware tools on it, but is this a good idea? You can in fact install all sorts of applications and roles in the parent/physical operating system, and the guidance from Microsoft for production environments is not to. However this article doesn’t advise for or against installing anti-virus it just tells you what to do if you decide to implement it. 

    In this post I wanted to give you my thoughts on it so you can make an informed decision

    The case for not installing anti-Virus

    Anti-Virus is one part of a suite of processes and technology to ensure your applications aren’t corrupted or prevented from working.  Assuming this is a high priority, you’ll want to also consider the following:

    • Ensure your hyper-V servers are constantly kept up to date with the latest patches.  clustering a and live migration mean that your guest virtual machines should never have to be off line while this is done.
    • User Hyper-V server or a server core installation for Hyper-V.  This has a much smaller attack surface, e.g. there is no browser or graphical interface, and cuts patching in half.
    • As mentioned above don’t run anything else at all in the parent operating system, not even additional server roles and features.

    Having done all of that what exactly is the anti virus going to check for?  It can’t protect against zero day attacks, and it can’t be set to monitor the virtual machines files (VHDs etc.) and services associated with hyper-V as this will cause it to fail. Note you will certainly have anti-virus agents running in the guest virtual machines to protect them.


    The case for installing anti virus

    You have done a detailed risk assessment and have established that in your own environment there is a need for anti-virus alongside hyper-V.

    The most common argument in favour I hear is that it is company policy, and even though that was not made with Hyper-V in mind you may have no alternative but to do so.


    What I would not recommend, is doing this or not doing this just because you read it on a random post, or picked it up as hearsay – make in informed decision as you would for anything involving the security of your production infrastructure 

    Finally  If you do decide to implement anti-virus alongside hyper-V the exclusions you’ll need to make for Hyper-v to work are here, and you may also want to refer to Microsoft’s best practice for securing Hyper-V so you don’t even have to take my word for this!

  • Getting Started with SCOM 2012 beta

    System Center Operations Manager (SCOM) 2012 is now in beta along with all of the other System Center 2012 suite, and because it’s a beta the installation process is a little tricky. Having spent a morning on it I thought it would be good if I told you what I did so you don’t have to go through this pain yourselves.

    The hardest part of the installation is setting up SQL Server for SCOM and realistically you are going to need to dedicate an instance of SQ Server to SCOM as it uses a legacy collation order which you won’t have enabled on other instances and may not be able to change.

    SCOM needs the database engine, full-text search and optionally reporting services..

    1 scom sql feature selection

    When you get to the server configuration screen,ensure that the browser and SQL Server Agent are set to automatic startup (you can fix this later if you need to in SQL Server Configuration manager)

    2 scom sql account config

    The the really important bit, click on the collation tab ..

    3 scom sqlcollation

    and select the Latin1_CP1_CI_AS.  If you don’t do this SCOM can’t use the instance and you are probably best off uninstalling and reinstalling. the command line to install SQL Server like this is..

    /AGTSVCACCOUNT="[your account]"
    /SQLSVCACCOUNT="[your account]"
    /SQLSYSADMINACCOUNTS="[accounts with sys admin privileges]"
    /RSSVCACCOUNT="[your account]"

    Having SQL Server installed you’ll then need the  .Net framework 4

    If you want the the web console you’ll then need to turn on the IIS role in Server Manager.  If you miss a few options here the prerequisite installer will tell you what to add, but won’t do it for you!

    Finally If you want reporting turned on you’ll need to install the reporting services report viewer package as well.

    Having done all of that you can then start the install.  I did a one Virtual Machine setup and went with the defaults although I do use dedicated domain account for all my System Center services.

    If you haven’t installed SQL server as above then you wont be able to select it in this step of the SCOM installer..

    4 scom select sql server

    (Note if you went with the default instance then you should enter servername\MSQLSERVER and leave the port at 1433)

    You’ll get the same screen again for the data warehouse that SCOM will use for reporting.  If you installed reporting services and you selected the option for SCOM reporting then you should get past this screen OK and you’ll see it set like this

    5 scom select sql reporting

    The final thing to be aware of is to use the right accounts for SCOM, I have a dedicated account (contoso\scservice) I use for my demos..


    6 scom accounts


    The new console looks very similar to the old one, but there are numerous changes..

    8 scomconsole

    and there is also a shiny Silverlight based web console to avoid having to deploy the full fat version to delegated users to see what’s gong on

    7 scom web console

    In subsequent posts I want to start to show you that there is actually quite a lot to the new version.

  • SQL Server 2012 and the private cloud

    Putting up a marketing slide that says SQL Server is a good private cloud citizen is good marketing, but what’s in the box to back the claim up?

    My top three would be:

    Windows Server core.  Patching is a major maintenance problem with lots of virtual machines, and server core cuts that in half.  Getting the most out of the physical server is also important and with windows server core being the bare minimum of windows needed to run SQL Server then that leaves more resources for SQL Server.  If you want to check this out I have a series of posts on getting it going.

    Always On which combines clustering and mirroring to create a highly available set of databases (an Availability Group), without the need to muck about with shared storage which is not recommended by Vmware and only works with i-scsi storage on Hyper-V.

    Contained Database Security, means that moving databases around doesn’t also require digging out the associated logins from the instance and then stuffing them into the instance where you are moving the database to.

    This is on top of two other private cloud friendly features in SQL Sever 2008 R2:

    Image prepare and Image complete allow SQL Server to be installed (prepared ) on a VM which can then be used as a template for example in System Center Virtual Machine Manager. Part of creating these templates is sysprep so that when a new VM is created from it it will have a unique SID for Active Directory.  SQL Server doesn’t like sysprep but image prepare gets around this by doing a preinstall which can then be completed from a script once the VM has been sysprepped, named and joined to a domain.

    Data Tier Applications.  This allows the design of a database to be abstracted from SQL Server, and allows the database to be moved as part of the new Server Application Virtualisation feature in System Center Virtual Machine Manager 2012 and to move the database to SQL Azure i.e. the public cloud.

    I will be going into these into more detail in the coming weeks, now I am back from my travels, but in the meantime, if you want to do your own evaluation, there are three things you’ll need to get started, exploring these features.

  • SQL Server 2012 editions and dependencies

    You may have seen that there are going to be some changes to the way SQL Server 2012 is licensed and the editions available, if you haven’t then the detail is here.  What I want to cover off in this post is what the various new features depend on so you are clear on what else you need besides the new licenses of SQL Server 2012.


    The three components of BI in Microsoft are SQL Server, Office and SharePoint, and this has been true since the integration of Performance Point in SharePoint 2007. SQL Server 2008 R2 add PowerPivot to SharePoint to allow excel power users to share the analytical mashups they created with their colleagues.  This relied on Excel Services in SharePoint enterprise to create a PowerPivot gallery where other business users could slice and dice the PowerPivots created by their more technical peers.

    SQL Server 2012 adds in two new BI features, Power View and Report Alerting in the new Business Intelligence edition and in the top end Enterprise edition:

    • Power View (formerly known as Project Crescent) is a new ad hoc reporting tool that builds on PowerPivot in that its reports are designed to be previewed in the PowerPivot gallery and so it is also reliant on SharePoint Enterprise edition.
    • Report Alerting allows users to put a rule on any report and be notified when the rule is met. The rules are defined in a simple interface can reference any value or field on the report.  This capability is only available if Reporting Services is in SharePoint integrated mode. However that only requires SharePoint Foundation edition.

    Also not to get these new features to work you’ll need SharePoint 2010 sp1 or later

    Windows Server

    One of the other, if not the, top feature in SQL Server is AlwaysOn.  This builds on windows failover clustering services (but with no shared storage necessary) to provide mirroring like functionality across multiple databases with multiple secondaries which can be read only if necessary. This feature is only available in SQL Server Enterprise edition and because it relies on failover clustering  it will only work on Windows Server Enterprise editions and above (as per this editions datasheet). 

    As for which version of Windows will run SQL Server 2012, it’s the same as for SQL Server 2008 R2 - Windows Server 2008 sp2, and on the desktop Vista Sp2 or later.  I am guessing  Windows Server 2008 is still supported as this is the last 32 bit server operating system but clearly the clock is ticking for 32 bit server support so you’ll want to start thinking about removing that as a dependency in your infrastructure 

  • BCDBoot–one of my new best friends

    I am getting ready for a series of interactive events next month and one of the things I wanted to show was a hyper-V cluster.  No doubt Simon and  could have blagged some kit, but we decided to use what we had lying around in the office.  This meant I had to muck about with my shiny Orange Dell Laptop so I eventually decided to dual boot so that I could run my old demos or use the laptop for these new events.

    Currently when I run my demos I am actually booting to a VHD running windows Server 208 R2 with the hyper-V role enabled, not that boot to VHD is not virtualisation , you are just using the VHD as your hard disk.  This means you can back up the environment by just copying that VHD. What I wanted to do today was to setup another VHD for these new events and the easiest way to do that is to break out  of the windows server installation process by hitting control F10 to bring up a control screen and then use DISKPART to create and attach a VHD which you can then install the operating system to. you’ll enter command like this..

    select disk 0

    select partition 1

    list volumes (to see which drive you want to create the VHD on)

    create vdisk file=”c:\deepfat.vhd” maximum=20000 type=fixed

    select vdisk file=”c:\deepfat.vhd”

    attach vdisk

    exit diskpart and refresh the list of volumes and you’ll see the vhd you just created and then you can select it and  install to it (this work in Windows 7 or Windows server 2008 R2). The nice thing about this is that it creates a boot entry for it.  However if the worst happens and your disk dies and you want to rest your environment on a new hard disk what do you do?  In my case I had my first boot VHD on a volume I wanted to combine with another so I needed a simple way to put the VHD on a volume and then get an entry for it in the boot menu so I could boot from it.

    If you have used BCDEdit by itself you’ll be aware of how fiddly it is and if you haven’t trust me.  However Simon pointed out a related command BCDBOOT which is a lot simper to use..

    simply mount the VHD so it appears as a drive letter (

    select vdisk file=”c:\deepfat.vhd”

    attach vdisk

    assign letter v

    and then run bcdboot to add an entry for it ..

    bcdboot v:\windows

    the only problem you might end up with (and this happened to me) is that the description for each entry will be the same so you won’t know which one to select at boot time.  You’ll have to use BCD edit for this by running bcdedit with no arguments look for the bit of the output that has the entry to your new VHD and copy its GUID to the clipboard. Now you can run

    bcdedit /set (paste your GIUD here) description “meaningful description in double quotes

    Update [1/12/11]

    Having made these changes my original demo rig wouldn’t run hyper-V and it turned out this was because of the boot store (which is what BCDEdit writes into), so I also had to run another BCDEdit command to start the hyper-V service as part of the boot process..

    bcdedit /set "{default}" hypervisorlaunchtype auto

    the {default} refers to the default entry in the boot store which in my case is my demo rig, you could of course specify the GUID for a particular entry.

    Hopefully this is useful but to be honest I wanted to post it so I could refer back to it later.