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!”

What’s New in Windows Server 2012 Hyper-V

I recently attended TechEd 2012 on the Gold Coast, and was pretty spoilt for choice as to what technologies I wanted to learn more about. I have an infrastructure background, and recently completed the MCITP Virtualisation Administrator certification for Windows Server 2008 R2, so Hyper-V was still fresh in my mind and one of those technologies I really enjoy using.

I didn’t expect much from the Hyper-V sessions at TechEd. I thought, hey, Hyper-V in Windows Server 2012 really can’t have that much extra, surely… Was I wrong or what!?!?

My first session on Hyper-V, I was flabbergasted by the sheer number of new features and improvements introduced in 2012. As someone who’s always played with VMWare and recently shifted allegiance to the Microsoft side, it comes as no surprise that I continue to be impressed by Microsoft’s hard work in the virtualisation space. Hyper-V continues to innovate and add to it’s enterprise ready feature set, and it truly offers an alternative to other virtualisation platforms. Continue reading “What’s New in Windows Server 2012 Hyper-V”

System Centre Orchestrator 2012: why you should be using it

Firstly, what is Orchestrator? Orchestrator is a new member of the Microsoft System Centre suite, previously badged System Centre Opalis or Opalis Integration Services, and commonly referred to as SCO or SCORCH. Orchestrator is quite simply, a workflow engine. You define the workflow, it goes off and executes it.

Workflow? Isn’t that a business thing? It was. In recent years, numerous workflow engines have appeared out of nowhere, most notably in document management and incident management systems. Workflows creation is somewhat complex for some, and it’s interest is generally left to those who rely on the systems that utilise it. Not the IT guys.

Why is workflow important? Workflow is a precursor to automation, and automation is what you should be striving to develop in your IT environment. Automation allows for you to do more important things, like drinking more coffee and focusing on your next big IT project. As a professional, business understanding IT guy, you should be investigating automation for everything in your environment – pro-active management is key.

Workflows are key to this automation. Orchestrator gives you this capability.

So, what can you use it for?

  • Cloud management (Microsoft’s flagship reasoning for Orchestrator)
  • Desktop deployment
  • Service provisioning
  • Proactive and reactive management of servers/desktops or even network devices
  • Incident management
  • much much much much more

Orchestrator workflows are defined as “runbooks”, which can be started manually, scheduled to run every 5 mins, or  triggered from an SNMP trap, file creation/edit, registry entry update, or generic monitoring statistics such as CPU usage. Orchestrator runbooks define the processes, and workflow, of your automation tasks.

At the heart of Orchestrator, you have PowerShell. If you don’t know PowerShell yet, you’re probably going to want to learn the basics before using Orchestrator.

The most important thing about Orchestrator, is you, the admin. Ask yourself:

  • What mind numbing tasks do you today? Ugh, the programmers want me to refresh UAT with the latest production database again, I hate this.
  • Is an investment of a days work, worth not having to complete that task again, every day/week/month? Think of all that extra coffee time
  • Is that task often prone to errors? Automating that task will ensure the task runs the same time, every time, and reduces the risk of error.
  • Most importantly, can you actually automate the task? Perhaps you think you can’t… But think about the end to end process. PowerShell has a lot of neat tricks, and the Orchestrator functionality is pretty extensible.