Skip to content

Random WAITFOR DELAY time

by Dan Thompson on May 13th, 2013

A Random WAITFOR DELAY – Shortest straw wins!

Introduction

With the advent of AlwaysOn, we now have a dilemma when it comes to backups. Potentially (depending on our site configuration) any node in an AlwaysOn Availability Group could be the primary. That’s fine as you can get your backup job to push out to all the servers (via MSX/TSX if you like) and you can determine whether the instance it is being executed on is the Primary or not.

If it is, it can execute the backup routine against the server so you have backups on your primary DC. Which is great for the Primary DC, but how do you take a backup of one of the Secondaries on the Secondary DC? You can’t say “if you’re a secondary and you’re on the secondary DC, take a backup” because if you have multiple secondaries, they will all take backups. So I decided I would let them pull straws. Shortest straw wins.

With a WAITFOR DELAY @RandomTime they can all execute the backup procedure and when the WAITFOR DELAY @RandomTime is complete, they first do a check to see if one of the other secondaries has started a backup. If it has, nothing to do – you didn’t get the shortest straw (this time). If there’s no backup there…start the regular backup routine.

Simples.

Here’s the code to generate a random WAITFOR DELAY:

DECLARE @RandomTime datetime = '1900-01-01 00:00:00.000'
DECLARE @RandomNumber int = 0
DECLARE @MaxRandomNumber int =20

SET @RandomNumber = @MaxRandomNumber*RAND()

SET @RandomTime = DATEADD(second, @RandomNumber,@RandomTime)

WAITFOR DELAY @RandomTime

SELECT @RandomNumber

 


 

It’s important to note here, my entire solution is to take backups from our secondaries (of which we have multiple and they could be on any DC). With SQL Server your per core licencing model covers you for a the primary server, and one secondary:

“The secondary server used for failover support does not need to be separately licensed for SQL Server as long as it is truly passive. If it is serving data, such as reports to clients running active SQL Server workloads, or performing any “work” such as additional backups being made from secondary servers, then it must be licensed for SQL Server.

Primary server licenses include support for one secondary server only, and any additional secondary servers must be licensed for SQL Server. Note: The rights to run a passive instance of SQL Server for temporary support are not transferable to other licensed servers for purposes of providing multiple passive secondary servers to a single primary server.”

Taken From: (Microsoft) SQL Server 2012 Quick Reference Licensing Guide

If anyone is interested in the full solution to check for secondary/primary let me know and I’ll post up a generalised one.

From → T-SQL