Don’t follow the instructions

Ever purchased an IKEA flatpack and tried to assemble it yourself? Often, it results in a little bit of cursing, rarely does it work out perfectly the first time.

At what point did you pull out the instructions?

In this process driven world, we are led to produce endless reams of step by step instructions for our daily actions; how to prepare that report, how to install a toner cartridge, how to build IKEA furniture…

But what happens when we ignore the instructions?

Hell happens. Screws get installed into the wrong holes, shelves installed the wrong way around, everything is misaligned…

Embrace this hell!

You’re experiencing failure and using problem solving skills. And learning what to do when you find issues…You’re developing your knowledge with experience.

In my day to day job, I’ve recently been designing and building a solution for a customer, which required me to install some software on a number of servers. I spent some time reading some blog posts and instructions how to install the various components, and jumped off to go and build it out for myself in a testing environment.

I encountered a LOT of errors.

Every error I encountered resulted in some further investigation; I relied on my memory and knowledge of the software and other components first and foremost to fix the errors — reviewing logs and error messages to make sense of them and see if I could fix it on my own. This further resulted in many Google queries…sometimes resulting in fruitful results — and reminding me that I missed a step in the instructions.

Other times I referred back to the instructions…They had a place after all. What step(s) had I missed? What were the errors or issues I encountered if I skipped a step?

It’s with great confidence now, that I can go off and install the aforementioned software components; and when I see issues or errors, I’m reminded of the steps I missed. Through the experience of failure, and troubleshooting, I have a better understanding of what happens when you don’t follow the instructions. I am more useful to my colleagues and customers who might follow the instructions but encounter an error and don’t know why.

By all means, follow the instructions. But if you want to become more valuable, keep on your toes and push the instructions to the side sometimes.

So you created a Windows Server Failover Cluster on Azure…and now it won’t start

It pays to pay attention to the detail!

I created a Windows Server Failover Cluster (WSFC) on a bunch of Windows 2012 R2 virtual machines running in Azure, and at first, it all just worked. I wrote a quick little PowerShell script to install the cluster features, and then used the MMC console to do what I’ve done a hundred times before; create a basic cluster and add my nodes to it.

Except this time I didn’t do one thing…

Being a bunch of test VMs on Azure, I shutdown my cluster nodes and domain controller at the end of the day, and proceeded to boot everything back up the next day.

Today, I was going to install a clustered SQL Server 2016 instance…Loaded up the ISO, run through the installer, and ruh-roh, SQL says it can’t validate my cluster.

I jumped back in to the Failover Cluster management console and try to connect to my cluster…won’t connect.

I had a similar issue a few days prior, so loaded up PowerShell, imported the failoverclusters module and tried to start the cluster node with the -fixquorum option…. no deal

At this point I was stumped. I jumped on to one of my other cluster nodes and validated the basics; could I talk to the domain controller, file share witness and each of the other cluster nodes?

Everything seemed to be working fine.

Except the original cluster node I was logged on to; I could not for the life of me, ping the other cluster nodes. From the other cluster nodes, I could ping it just fine…what’s going on?!

I loaded up the Failover Cluster management console on one of the other nodes, and low and behold, it connected. Weird. I scratched my head thinking we had setup some weird Network Security Groups or Windows Firewall was acting up, but everything pointed to “you’re going crazy, man”.

It was then that I realised I could ping the original cluster node from my secondary; how could I ping that IP, but not ping it back from the other node? I had covered the firewall side of things, so there’s something odd going on here.

Something odd, or something silly…

You see, we had setup reserved private IPs on Azure to assign internal IP addresses to each of the cluster nodes. And this was working great. Node 1 was using 10.2.1.10, and node 2 was using 10.2.1.6. On top of that, I had setup the cluster to also use a DHCP address, blindly assuming Azure would give it one.

NO NO NO NO NO. This was not the case.

When the cluster was first setup, it used DHCP on the active node to get the address of 10.2.1.10 – the same address as the cluster node itself. So when that node failed over as a result of me shutting down the servers, the cluster tried to renew the lease for 10.2.1.10; and subsequently assigned it to the active cluster node, now node 2.

Mystery solved.

Pro tip: don’t use DHCP to assign your cluster IP on Azure. Set a static IP address!!!!

Microsoft SQL Server 2016 on Linux

One of the most exciting updates in SQL Server 2016 is the ability to run it on Linux! Currently working on some designs which incorporate SQL Server 2016 AlwaysOn Availability groups and had to ask; does it work on Linux? Well, there’s a Channel9 video for that. The host briefly mentions AlwaysOn Availability Groups, and demos the creation of a SQL Server failover cluster instance, and the use of SQL Server TDE (Transparent Database Encryption). Simples.

Office 365 – Day to Day Migration Commands

Connect to Remote Powershell

$UserCredential = Get-Credential

$Session = New-PSSession -ConfigurationName Microsoft.Exchange -ConnectionUri https://outlook.office365.com/powershell-liveid/ -Credential $UserCredential -Authentication Basic -AllowRedirection

Import-PSSession $Session

Create a new move request

(with an existing remote powershell open)

$OPCred = Get-Credential ### use CORP\user.name.1

New-MoveRequest -Remote -RemoteHostName mrs.onpremdomain.com -RemoteCredential $OPCred -TargetDeliveryDomain tenant.onmicrosoft.com -SuspendWhenReadyToComplete -Identity user.name@fqdn.com -BadItemLimit 5 -WhatIf

IF this proceeds, re-run the new move request cmdlet without -WhatIf to queue an SWRC.

Create a new migration batch

https://technet.microsoft.com/en-us/library/jj219166(v=exchg.150).aspx

(with an existing remote powershell open)

New-MigrationBatch -name “Batch of Users” -SourceEndpoint mrs.onpremdomain.com -TargetDeliveryDomain tenant.onmicrosoft.com -BadItemLimit 5 -CSVData ([System.IO.File]::ReadAllBytes(“C:\Path\To\UserList.csv”))

CSV file should have a single column, named EmailAddress, and should contain each user’s primary SMTP address, one per line

Cutover a single mailbox

The mailbox should have an existing move request to do this, and in the AutoSuspended state.

get-moverequest <upn/username> | get-moverequeststatistics

If everything is okay:

Resume-moverequest <upn/username>

CASE WHEN “I need to learn more T-SQL” THEN GoogleTheResult() END

One of my favorite recent discoveries in my SQL Server work recently, is the awesome T-SQL “CASE…WHEN…THEN” statement. I can’t describe how much I love this thing.

It’s pretty simple really:


SELECT
CASE
WHEN fruit = 'apples' THEN 'not oranges'
WHEN fruit = 'oranges' THEN 'not apples'
ELSE 'neither apples nor oranges'
END AS fruit
FROM fruits

As can be expected, for every row where the column is set to “apples”, this will output “not oranges”. For those with the value “oranges”, it will output “not apples”, and of course, any other value (such as “bananas”) would output “neither apples nor oranges”.

To make things more interesting, you can combine the comparison portion (i.e. x = y) with any WHERE based comparisons, such as:

  • X IS NULL
  • X IN (value1,value2)
  • X >= 7
  • X =1 AND >=27
  • X+Y>27 OR ISNULL(Z,0) = 0
Using the comparison, you can even output different columns as the result. I.e.
  • WHEN columnA is null THEN columnB
  • WHEN columnA+columnB > 27 THEN columnB/(SELECT MAX(columnA) FROM TABLE)

Thanks Google. I really love this statement, it’s allowed for some pretty funky complex T-SQL scripts recently, and made my life alot easier!

Backing up all SQL Server user databases to file

The ultimate lazy backup script. I wasn’t feeling to selective about which databases to backup, and I figured, it’d be best to back them all up, even that testing database I occasionally use for data import/export. Never know when I might need it again…

This sits inside a SQL Server Agent job, and runs nightly. Simple enough really.

All works as expected on SQL Server 2012.

DECLARE @Database varchar(max)
DECLARE @path varchar(max)
SET @path = concat(N'D:\BACKUP\',convert(varchar,getdate(),112),' ALL DATABASES BACKUP.bak')
DECLARE my_cursor CURSOR FOR
select name from sys.databases where database_id>4
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Database
WHILE @@FETCH_STATUS = 0
BEGIN
BACKUP DATABASE @Database TO DISK = @path WITH NOFORMAT, NOINIT, NAME = N'SQL Server all user databases backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
FETCH NEXT FROM my_cursor
INTO @Database
END
CLOSE my_cursor
DEALLOCATE my_cursor

I’m sure there’s a better way to do this, but there doesn’t appear to be a column in the sys.databases table which denotes a database as a system or user database. Instead, I’ve used the WHERE database_id > 4 – which removes master, model, msdb, and tempdb from the backup.

Server Posterpedia Windows 8 Application

You’re designing a new SQL Server infrastructure for a client, but can’t remember if that high availability solution topology requires shared storage or not, and need to quickly look it up without searching through TechNet. How? With this application! Some genius has combined all of Microsoft’s server architecture posters into a handy Windows 8 application which is now available on the Windows App Store here

Windows 8 Without a Tablet? Nonsense!

Today, Microsoft launched Windows 8 to the masses. And I’m frustrated.

Over the past year, I have had the opportunity to play with Windows 8, blowing away my daily work laptop and installing the new operating system at each release – beta, release preview, consumer preview, and RTM.

Yes. A laptop. Not a touch screen tablet. A laptop with a keyboard and an external mouse. My experience could be likened to running Windows 8 on a standalone desktop PC, with a separate external mouse and keyboard. This is how I work on my laptop, this is how I work on my desktop. And Windows 8 lets me do that with very little change. Continue reading “Windows 8 Without a Tablet? Nonsense!”