Benjamin Wright-Jones

Exploring data and distributed systems [I also cross-post to]

June, 2012

  • Benjamin Wright-Jones

    Testing SQL Server HA using Hyper-V in Windows 8 Release Preview


    I often find the need to build and test SQL Server clusters to support native two-node or N+1 (multi-instance) scenarios and also validate SQL Server 2012 Always On configurations.  I was previously running Windows Server 2008 R2 on my laptop but I a few issues using this as a day to day operating system e.g. no support for standby when hyper-v was enabled, no dual boot (hyper-v on/off) with bitlocker enabled, no Bluetooth support etc.  Fortunately, Windows 8 meets all my needs and the experience has been excellent to date.

    Note: for rapid provisioning of other images, I created a fully patched base OS image which was sysprep’d (this is incredibly easy).  I now just copy the image if I want to create other server roles e.g. System Center 2012.

    The only issue I have found to date is that host internet connectivity is affected (delayed) after defining multiple internal network adapters, therefore I switched to using Private network adapters.   I only need internal adapters for host to guest connectivity e.g. copying files etc.

    My hardware and software is defined below:

    • Laptop HP 8540w (16GB memory, 4 cores hyper-threaded)
    • Two internal SATA disks (hybrid)
    • Windows 8 Release Preview (x64) with Hyper-V enabled

    The environment consists of the following virtual guest images:

    • 1 x Domain Controller (also hosts the virtual storage)
    • 1 x Primary Node (node 1)
    • 1 x Secondary Node (node 2)

    All servers are running Windows Server 2008 R2 Enterprise Edition x64 with Service Pack 1.  The cluster role is enabled on node 1 and node 2. 


    The shared storage is provisioned using the iSCSI Software Target which I defined on the domain controller.  I didn’t find a need to dedicate a specific storage server.  The screenshot of the virtual storage is presented below




    Cluster Validation is good, the only warning was inconsistent OS patch levels on both cluster nodes as shown below.



    After running cluster validation, I created a cluster and the final configuration is shown below. 

    Below is a screenshot of the raw cluster configuration.


    I’ll blog more following the SQL Server install.  I also plan to repeat this for Window Server 2012 and SQL Server 2012.

  • Benjamin Wright-Jones

    Transposing Columns onto Rows


    After a long period of absence, I have returned to posting some blog articles.  This one popped up last week from an Oracle DBA while I was onsite with a customer.  The conversation went something along the lines of:

    Oracle DBA: “How can I place columns values on rows? I have a limited page width and need to alter the output”

    Microsoftie “ah.. well, that’s easy..”

    Consider the output below:



    but we want it to appear like this:



    The magic is really in the UNPIVOT function as shown below.

    CREATE DATABASE sandbox;
    USE sandbox;
    CREATE TABLE tblPerson
        Email_Address varchar(50),
        First_Name varchar(50),
        Last_Name varchar(50)
    ('', 'Ben', 'WJ')
    SELECT * FROM tblPerson;
    SELECT tblPivot.Property, tblPivot.Value FROM (SELECT CONVERT(sql_variant,Email_Address) AS Email_Address, CONVERT(sql_variant,First_Name) AS First_Name, CONVERT(sql_variant,Last_Name) AS Last_Name FROM tblPerson) Person UNPIVOT (Value For Property In (Email_Address, First_Name, Last_Name)) as tblPivot;
Page 1 of 1 (2 items)