There was a bit of chat today on the SQL Community slack about UTC and Daylight Saving Time. It reminded me I have a post in draft somewhere about the complications of dealing with timezones in SQL that I should get around to finishing.
It also reminded me of a fact I found in an old Paul Randall post from 2007 that not a lot of people seem to know so I thought it was worth sharing again.
I discovered it when I was working on a kind of hand-cranked version of log shipping with a twist for SQL Server. Before you ask me why… don’t! I promise it was for a good reason. At least it seemed like a good idea at the time…
Anyway, I was aware that I needed to work out what I was going to do when the clocks go back in October of each year.
I decided to try and find out what the out of the box log shipping does to cater for this. I started with Google and came across this 2007 article by Paul Randall:
How does daylight savings time affect disaster recovery? – Paul S. Randal
Apparently SQL Server Agent pauses for an hour after the clocks go back. In the UK the clocks go back at 2 AM on the last Sunday in October. So all the times from 1 AM to 2 AM effectively happen twice. SQL Server Agent will run jobs from 1 AM to 2 AM as usual, then when at 2 AM the clocks go back to 1 AM, SQL Agent will pause for an hour (i.e. no new jobs will be started) and only resume the second time it gets to 2 AM.
Sounds a bit crazy in some ways, but I tend to believe most things Paul Randall says and it does seem to have been proved since in practice.
It means that you don’t get a confusing sequence of times in your job history and other events that don’t really make sense, and it also means that jobs won’t happen twice e.g. if you had an index rebuild in that time.
It could have issues for DR though, imagine that this period is actually an active one for your application and that you have an RPO (recovery point objective) of a maximum of (let’s say) 5 minutes data loss. During that hour, no LOG backups are being taken. Suddenly you’re vulnerable to not being able to meet your DR service agreement. Could be something to watch out for.
For me though (at the time I learnt this) it was great as I didn’t need to worry about coding a workaround in my log restore code!