Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Energy Policy Act of 2005 for SQL Server Notification Services


By:   |   Read Comments   |   Related Tips: More > Upgrades and Migrations

Attend our free MSSQLTips Webcast - How to Simplify Routine SQL Server Administration Tasks


Problem

In an earlier tip (Energy Policy Act of 2005 Implications on SQL Server) we outlined how SQL Server 2000 and SQL Server 2005 leverage Windows for their date and time needs.  As such, applying patches to Windows would properly support the overall SQL Server date and time needs for the Energy Policy Act of 2005.  Unfortunately, that is the not the case with SQL Server 2000 Notification Services and SQL Server 2005 Notification Services, additional steps must be taken for proper compliance.  Let's examine how Notification Services needs to be patched to comply with the date change on March 11, 2007 at 2:00 AM.

Solution

In order for Notification Services to not have issues with the daylight savings time (DST) it is necessary to perform the following:

  • Identify the impacted versions of SQL Server
  • Test the steps to comply with DST in a test environment because the [dbo].NSTimeZoneDstOffsets table will be updated (StartDateTime and EndDateTime)
  • Test and validate your Notification Services applications
  • Schedule downtime for your production Notification Services applications because the event provider, the Notification Services generator service and all the Notification Services distributor services on all computers will need to be shut down
  • Determine the steps that need to be taken when deploying new Notification Services Servers in the short term

What versions of Notification Services are affected?

  • Microsoft SQL Server 2000 Notification Services 2.0
  • Microsoft SQL Server 2000 Notification Services 2.0 Service Pack 1 (SP1)
  • Microsoft SQL Server 2005 Notification Services
  • Microsoft SQL Server 2005 Notification Services Service Pack 1 (SP1)
  • Microsoft SQL Server 2005 Notification Services Service Pack 2 (SP2)

What are the testing and deployment steps that I need to follow?

The detailed steps are outlined in the 2007 time zone update for SQL Server 2005 Notification Services and for SQL Server 2000 Notification Services KB article.  At a high level the following steps need to occur:

  1. Backup your existing Notification Services databases and perform a restore verify only for database recovery purposes
  2. Shutdown the event provider, the Notification Services generator service and all the Notification Services distributor services on all computers
  3. Create and execute a SQL Server script for the T-SQL updates i.e. DST2007.sql.
  4. Restart the Notification Services application
  5. Test your application (users, DBAs, Developers, etc.) to determine if your applications are behaving as you expected
  6. Move forward with the change or rollback the changes (and schedule a new deployment before March 11, 2007 @ 2:00 AM)

What are the implications on future Notification Services deployments?

Any new Notification Services instances that are originally deployed with Service Pack 2 will not need this patch.  Any upgraded systems will need to have the patch applied based on the steps outlined in the previous section.

Next Steps


Last Update:


signup button

next tip button



About the author
MSSQLTips author Jeremy Kadlec Since 2002, Jeremy Kadlec has delivered value to the global SQL Server community as an Edgewood Solutions SQL Server Consultant, MSSQLTips.com co-founder and Baltimore SSUG co-leader.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


SQL tips:

*Enter Code refresh code     



Learn more about SQL Server tools