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;
CREATE TABLE tblPerson
INSERT INTO tblPerson VALUES
('firstname.lastname@example.org', 'Ben', 'WJ')
SELECT * FROM tblPerson;
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;
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:
The environment consists of the following virtual guest images:
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.