Benjamin Wright-Jones

Exploring Information, Infrastructure and Solution Architecture

June, 2012

Posts
  • Benjamin Wright-Jones

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

    • 1 Comments

    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. 

    Storage

    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

    image

     

    image

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

     

    image

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

    Below is a screenshot of the raw cluster configuration.

    image

    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

    • 3 Comments

    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:

     

    image

    but we want it to appear like this:

    image

     

    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)
    );
    
    INSERT INTO tblPerson VALUES
    ('ben@test.com', '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)