Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Feb 21, 2008

DST checking with Windows Powershell

By Yan Pan

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 HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation 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 HKLM\SYSTEM\CurrentControlSet\Control\TimeZoneInformation. 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:\Users\Yan\Documents\DST.

PS C:\> Set-Location C:\Users\Yan\Documents\DST
PS C:\Users\Yan\Documents\DST>  Get-ChildItem

    Directory: Microsoft.PowerShell.Core\FileSystem::C:\Users\Yan\Documents\DST

    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:\Users\Yan\Documents\DST> Get-Content serverfile.txt
lnhost01\LNINSTANCE1,7001
lnhost02\LNINSTANCE2,7001
nyhost01\NYINSTANCE1,7002
nyhost02\NYINSTANCE2,7001
nyhost03\NYINSTANCE3,7002
nyhost04,1433
hkhost01\HKINSTANCE1,7001
...

To run the script, type:

PS C:\Users\Yan\Documents\DST> dstcheck_global.ps1 -baseEST '2007-10-27 20:05'
 
    Directory: Microsoft.PowerShell.Core\FileSystem::C:\Users\Yan\Documents\DST
 
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:\Users\Yan\Documents\DST> Get-Content dstcheck.exception.1696
Cannot connect with lnhost02\LNINSTANCE2,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



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM