DST checking with Windows Powershell

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 C:UsersYanDocumentsDST
PS C:UsersYanDocumentsDST> Get-ChildItem

Directory: Microsoft.PowerShell.CoreFileSystem::C:UsersYanDocumentsDST

Mode LastWriteTime Length Name
—- ————- —— —-
-a— 2/10/2008 11:00 AM 10587 dstcheck_global.ps1
-a— 2/10/2008 11:13 AM 6212 serverfile.txt


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

Yan Pan
Yan Pan
Yan Pan (MCITP SQL Server 2008, MCITP SQL Server 2005, MCDBA SQL Server 2000, OCA Oracle 10g) is a Senior DBA for a leading global financial services firm, where her daily duties include administering hundreds of SQL Server and Oracle servers of every possible version, working with business units on software development, troubleshooting database issues, and tuning database performance. She has written a Wrox book titled “Microsoft SQL Server 2008 Administration with Windows PowerShell” with MAK who is also a columnist for DatabaseJournal.com. You can check out the book at many book stores, such as Barnes & Noble, Borders. Previously, Yan worked as a SQL Server DBA and a .NET developer at Data Based Ads, Inc., and developed two .NET Web applications. Before that, she worked at AT&T Research Labs. She designed OLAP cubes with SQL Server Analysis Services and developed PivotTable reports for regional managers. She has master’s degrees in Computer Science and Physics.

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles