As a DBA working at a global financial services company, I face a tough task each year – making sure that SQL Server hosts around the globe change time correctly whenever there is a Daylight Saving Time (DST) event in the US or EU (luckily AS does not observe DST for now). Incorrect time on a server that hosts trading databases screws up transactions and irritates end users. However, to create such a general script, we need to consider all the time zones in the US and EU, and their individual DST starting and ending dates. To make things even more complicated, the Energy Policy Act of 2005 changed the dates for DST in the US. Beginning in 2007, DST starts three weeks earlier and ends one week later. Therefore, the task to verify time becomes more critical. As you might know, although Microsoft stated the Windows DST patch did not require a reboot (http://support.microsoft.com/kb/928388), in some cases, hosts just did not change time on the new dates without the magic of a reboot. With all this being said, we need a script that can be re-used every year to report the wrong time on any SQL Server hosts. The script I am going to show you takes the year 2007 as an example. If you want to use it for another year, you simply need to change the values of five variables in the script that represent the DST changing dates in the US and EU for that year.
Our script dstcheck_global.ps1 takes only one parameter – current Eastern Standard Time (EST). This is very convenient for the companies that are headquartered in the east coast, for example, NYC. However, our script can be used for any company as long as you know the current EST time. To compare the local time you get from any SQL Server hosts with this standard time, we need to know four things.
1. The normal time difference between the time zone of the SQL Server and the Universal Time (UT). Because the SQL Server data engine gets its time from the operating system, the bias value under the registry key HKLMSYSTEMCurrentControlSetControlTimeZoneInformation is the normal time difference in minutes.
2. The DST observance or non-observance at the time zone. We can determine this based on the StandardName value under the registry key HKLMSYSTEMCurrentControlSetControlTimeZoneInformation. This value has the name of the time zone. If the time zone observes DST, then the actual time difference is the normal time difference (bias) minus 60 minutes.
3. The normal time difference between the EST and the UT, which is 5 hours (300 minutes).
4. The DST observance or non-observance at the EST. We can compare the given EST time with the DST starting and ending dates in the EST. In our example of year 2007, at 1:59am EST on 3/11/07, clocks sprang forward to 3am. At 1:59am EST on 11/4, clocks fell back to 1am. Therefore, if the given EST time was after 3am on 3/11/07 and before 1am on 11/4/07, then we assumed the EST observed DST. However, if the given EST time was after 1am but before 2am on 11/4/07, then it could be either that clocks in EST were going to fall back to the normal time or they had already fallen back. Our script then needed to ask a question “Does DST end in EST?”. Depending on the user’s response, we could decide if the EST observed DST or not. If the EST observed DST, then the actual time difference between the EST and the UT is 240 minutes. If not, then it is 300 minutes.
Once we know these four things, we can calculate the accuracy of the local time of a SQL Server.
If the local time is correct, then:
UT = EST time + EST time difference
= local time + local time difference
In our script, the given EST time is stored in a variable $baseEST, and the EST time difference in $ESToffset. The local time is retrieved from the SQL Server using the getdate() function. The actual local time difference is stored in a variable $actualbias. Therefore,
UT = $baseEST + $ESToffset = getdate() + $actualbias
However, if the local time is off, then the inaccuracy from the correct time is:
$diffminutes = $baseEST + $ESToffset – getdate() – $actualbias
We also need to include the execution duration of the script because going through hundreds of SQL Server instances can take tens of minutes. The EST time is given at the beginning of the execution and is no longer accurate after the script runs for, say, 20 minutes. The execution duration is stored in a variable $elaspedminutes. Therefore,
$diffminutes = $baseEST + $ESToffset + $elaspedminutes – getdate() – $actualbias
For example, when $baseEST was ‘2007-10-27 20:00’, EST observed DST and $ESToffset was 240 minutes. We got a local time ‘2007-10-27 19:10’ from a Chicago SQL Server instance after the script ran for 20 minutes. The actual time difference on the Chicago SQL Server host was 300 minutes because CST observed DST too. Therefore, the inaccuracy of the Chicago time was:
$diffminutes = DateDiff(‘2007-10-27 20:00’, ‘2007-10-27 19:10’) + 240 + 20 – 300
= 10
The Chicago time was 10 minutes behind the correct time.
Our script allows a 30-minute inaccuracy, i.e., if the local time is less than 30 minutes behind or ahead of the EST time, then it is considered ok. Therefore, if the absolute value of $diffminutes is less than 30, then the SQL Server passed the check. Otherwise, the server is reported as an exception. If you need a better accuracy, you can simply change this “if” statement in the script.
if (( $diffminutes -gt 30 ) -or ( $diffminutes -lt -30 ))
Our script gets a list of SQL Server instances from a serverfile.txt file. We put the file and the script dstcheck_global.ps1 in the same directory, for example, C:UsersYanDocumentsDST.
PS C:> Set-Location
PS
Directory: Microsoft.PowerShell.CoreFileSystem::C:UsersYanDocumentsDST
Mode
—- ————- —— —-
-a— 2/10/2008 11:00 AM
-a— 2/10/2008 11:13 AM
A sample of the server file is shown here.
PS C:UsersYanDocumentsDST> Get-Content serverfile.txt
lnhost01LNINSTANCE1,7001
lnhost02LNINSTANCE2,7001
nyhost01NYINSTANCE1,7002
nyhost02NYINSTANCE2,7001
nyhost03NYINSTANCE3,7002
nyhost04,1433
hkhost01HKINSTANCE1,7001
…
To run the script, type:
PS C:UsersYanDocumentsDST> dstcheck_global.ps1 -baseEST ‘2007-10-27 20:05’
Directory: Microsoft.PowerShell.CoreFileSystem::C:UsersYanDocumentsDST
Mode LastWriteTime Length Name
—- ————- —— —-
-a— 2/10/2008 11:21 AM 0 dstcheck.exception.1696
After the script executes, look at the exception file dstcheck.exception.1696 for any exceptions. If there are exceptions, then the file looks like this:
PS C:UsersYanDocumentsDST> Get-Content dstcheck.exception.1696
Cannot connect with lnhost02LNINSTANCE2,7001
nyhost04,1433 is 44 minute(s) behind
…
Please note that the script is intended to verify time before or after DST events. It should not be executed while a DST event is occurring. Otherwise, some servers may be falsely reported as an exception.
For this year 2008, you just need to change the values of these five variables to reflect the DST changing dates.
$ESTDSTStart=’2008-03-09 03:00′
$ESTDSTEnd=’2008-11-02 01:00′
$ESTDSTEndChange=’2008-11-02 02:00′
$GMTDSTStart=’2008-03-29 21:00′
$GMTDSTEnd=’2008-10-25 21:00′
Conclusion
This article has presented a solution to verify time on SQL Server globally after DST events. The accompanying script can be reused every year with minor changes.
» See All Articles by Columnist Yan Pan
