PolyMon

One of the features for PolyMon 2.x will be Availability Calendars for Operators. Essentially this will allow the creation of custom calendars for each operator where their Available/Non-Available times will be specified.

Alerting will then alert and escalate, based on these and other settings. So, if someone is say off for a week, their calendar can easily be updated to reflect that they are not available during these times. If alerts are triggered an unavailable operator would then be taken out of the alert/escalation mix.

The ON/OFF calendars will be set up the following way:

• Master Record: Define a Calendar master record which contains:
• Calendar ID
• Calendar Name
• Calendar Time Zone
• On/Off Precedence - this is used in overrides when conflicting ON/OFF settings are found. This will determine whether ON or OFF should take precedence.
  
• Base Calendar Times: Specify On times for any day of the week (possibly multiple times per day of week, possibly even overlapping - for example, M-1am-6am, 4pm-6pm, T-All day, etc). Any time that is not ON is considered OFF.
  
• Overrides: The base calendar defined above can then be overridden as in one of two ways (with any conflicting ON/OFF settings resolved using the On/Off precedence setting of the calendar):
• Recurrence Override: Defines a recurrence scheme based on a Start Date + Every N Days. Can specify whether the override is ON/OFF.Can be multiple and overlapping.
  
• Specific Override: Defines an ON/OFF setting for specific dates and times. Can be multiple and overlapping.
  

A resulting ON/OFF status is then determined by applying overrides to the base times (resolving any conflicts using the On/Off precedence setting).

All date time values specified in calendar settings are in the local time zone specified in the calendar master record. However, all times will be handled internally by PolyMon in UTC.

The sql script to generate the tables and associated functions are available here. Note that you will also need the DOW and Time Zone schemas/functions as well to use these.

One issue I have run across in the past is how the Day of Week numeric representation is calculated by SQL Server. The problem stems from the fact that different countries have different interpretations of what consitutes the first day of the week. For example, Monday is considered the first day of the week for some while others consider Sunday the first day of the week.

To handle this, SQL Server has a global variable @@DateFirstSetting that can be used to specify the first day of the week.

Of course, when creating an application that uses day of week logic and can be used internationally, this can lead to some problems.

I have standardized PolyMon 2.x on using Monday as the first day of the week. However, people could still be using different @@DateFirstSetting values, so I needed a way to avoid using SQL's internal day of week function.

To this end I created a small table (DayOfWeek) that stores the day of the week along with their internal id and other bits of information such as Long, Medium and Short text representations that will be used in the PolyMon front-end. Then, a function (dt_StdDOW) is provided that will calculate the day of the week for any specified date based on that table, irrespective of the @@DateFirstSetting value.

A script to generate the table and function, as well as populate data into the table is available here.

One thing I really wanted to have in the 2.x version was proper time zone handling.
In part this is due to having operator calendars where fully customizable calendars can be set up for each individual operator indicating their On/Off times (i.e. when can an operator be notified) which will also tie into escalation procedures associated with monitor alerts.

I initially thought this would be easy given SQL 2008's new datetimeoffset data type. However, things are never as easy as you think...

This article explains some of the issues and solutions quite well:
blogs.msdn.com/sqlprogrammability/archive/2008/03/18/using-time-zone-data-in-sql-server-2008.aspx

I have used this approach for PolyMon and the following tables and user defined functions are available:

• TZMapping: Table that contains a list of UTC zone offsets (no DST)
• TZZones: Table that contains the DST offsets for each time zone per date range (since DST rules change over time).
• tx_UTCOffset: Function that calculates the UTC offset for a date time + zone (the offset can be used as follows: UTC + Offset --> Local)
• tx_LocalToUTC: Converts a Local date time to UTC (given time zone)
  
• tc_UTCToLocal: Converts UTC to Local time (given time zone)
  

You can download a SQL script that creates the tables and functions as well as populates the time zone data here.

I ran across this podcast of a talk by Jensen Huang, co-founder and CEO of NVIDIA given at Stanford courtesy of the Stanford Technology Venture Program.

It is an excellent talk well worth listening to.

In fact, the entire series of talks available there are excellent - no matter whether you are yourself in a startup or working for an established business - absolutely fantastic content.

I have just started using SQL Server 2008. A really nice feature is the IntellliSense in SQL Server Management Studio (SSMS).

But, I hit a minor snag that took me a little while to figure out.

If you create a stored procedure or user-defined function, and in the same SSMS session write some T-SQL script that references it, IntelliSense doesn't pick up the newly create proc/function.

Reason appears to be that IntelliSense uses a caching mechanism. So, unless you want to keep closing SSMS and re-opening it just to get IntelliSense to recognize your latest proc/function, the simplest is to use the Edit|IntelliSense|Refresh Local Cache menu option (or Ctrl+Shit+R).

This refreshes the cache and IntelliSense now recognizes your new proc/function.