Energy Policy Act of 2005 for SQL Server Notification Services

By: Jeremy Kadlec   |   Comments   |   Related: > Upgrades and Migrations

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

• With the daylight savings time (DST) change in approximately 1 month, it is necessary to evaluate your Notification Services instances to ensure they are properly patched
• Be sure to schedule time for testing and deploying these changes prior to March 11, 2007 @ 2:00 AM
• Since the Notification Services application will need to be stopped, be sure to follow your change management procedures
• For more information about Energy Policy Act of 2005 reference these links:
  • Energy Policy Act of 2005 Implications on SQL Server
  • Preparing for daylight saving time changes in 2007
  • http://webexhibits.org/daylightsaving/b.html
  • 2007 time zone update for Microsoft Windows operating systems
  • 2007 time zone update for SQL Server 2005 Notification Services and for SQL Server 2000 Notification Services
• For more information on Notification Services reference these links:
  • http://www.microsoft.com/sql/technologies/notification/default.mspx
• Special thanks to Chad Boyd from the MSSQLTips community for this tip idea

About the author

Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips