Windows 7, Wired and Wireless networks

I've been on Windows 7 (64-bit) for a few months now, and just recently moved to a new Lenovo T510. The move, using Windows backup and restore, was downright painless and just worked.

One issue I had with the new setup that was frustrating me was that, when docked, the laptop would keep a strong preference for wireless connections over wired ones. Apparently, Windows 7 defaults to using some customized route preference calculation metric, and had my wireless at 90 and my wired connection at 900-ish. (Lower scores indicate stronger affinity.)

You can see your current, auto-assigned routing metrics by typing netstat -r from a command prompt, and looking in the 'Metric' column of hte IPv4 Route Table.

When I'm docked, I want wired to take precedent, as the wired connection has a static IP, and makes certain dev and admin tasks possible that cant' be done around here on a regularly-shifting DHCP address on wireless - things like sending SMTP mails without credentials, the way servers get to.

So, using an articles from Lifehacker and Palehorse as a starting point, I set forth to change my interface metrics to suit my needs.

1. Starting with Control Panel\Network and Internet\Network and Sharing Center, click the wired connection under Connections:
   
   
2. Then click 'Properties'.
3. Then select 'Internet Protocol Version 4 (TCP/IPv4)' and click 'Properties'.
4. Click 'Advanced' at the bottom of the window.
5. Un-check the 'Automatic Metric' box, and enter a new routing metric here. Lower numbers are higher priority. I used a routing metric of 10 for my wired connection and 200 for my wireless, and everything seems to be behaving exactly as I want.
   
6. Repeat from #1 selecting your wireless network connection.
7. Reboot when done to have the new assignments take effect.

As above, you can verify your assignments by typing netstat -r from a command prompt, and looking in the 'Metric' column of hte IPv4 Route Table.

A word of warning: you may want to check with your networking people before you do this. Routers can assign this metric, and overriding what they assign may have unintended consequences for how you access your networks.

Bill

Daylight Savings Time in SQL Server

We're running a vendor system that stores every date/time object in GMT. This isn't a bad thing, but it does require converting GMT -> Local time in every place we used it: select statements, where clauses, corelated subqueries, etc. To do this we started out years ago with a simple Function plus a database table to store DST dates.

That function looked like this:

CREATE FUNCTION [dbo].[ConvertGMT]

(@MYDATE DATETIME)

RETURNS DATETIME AS

BEGIN

DECLARE @OFFSET INT

DECLARE @YEAR INT

DECLARE @DST_BEGIN DATETIME

DECLARE @DST_END DATETIME

DECLARE @RETURNED_DATE DATETIME

SET @YEAR = YEAR(@MYDATE)

SET @DST_BEGIN = (select DST_BEGIN from dbo.DST_LOOKUP where YEAR_LOOKUP = @YEAR)

SET @DST_END = (select DST_END from dbo.DST_LOOKUP where YEAR_LOOKUP = @YEAR)

IF (@MYDATE BETWEEN @DST_BEGIN AND @DST_END)

SET @OFFSET = -4

ELSE

SET @OFFSET = -5

SET @RETURNED_DATE = DATEADD(hour,@OFFSET,@MYDATE)

RETURN @RETURNED_DATE

END

and the associated table looked like this:

DST_BEGIN DST_END YEAR_LOOKUP

4/4/1999 2:00:00 AM 10/31/1999 3:00:00 AM 1999

4/2/2000 2:00:00 AM 10/29/2000 3:00:00 AM 2000

4/1/2001 2:00:00 AM 10/28/2001 3:00:00 AM 2001

4/7/2002 2:00:00 AM 10/27/2002 3:00:00 AM 2002

4/6/2003 2:00:00 AM 10/26/2003 3:00:00 AM 2003

4/4/2004 2:00:00 AM 10/31/2004 3:00:00 AM 2004

4/3/2005 2:00:00 AM 10/30/2005 3:00:00 AM 2005

4/2/2006 2:00:00 AM 10/29/2006 3:00:00 AM 2006

3/11/2007 2:00:00 AM 11/4/2007 3:00:00 AM 2007

3/9/2008 2:00:00 AM 11/2/2008 3:00:00 AM 2008

3/8/2009 2:00:00 AM 11/1/2009 3:00:00 AM 2009

3/14/2010 2:00:00 AM 11/7/2010 3:00:00 AM 2010

3/13/2011 2:00:00 AM 11/6/2011 3:00:00 AM 2011

This worked well enough for a while, but it cost a couple of table hits for every conversion. That adds up when you're doing 6 conversion per row returning a couple hundred rows a few times a minute. This benchmarked at ≅15seconds to return 45,000 at 3 conversions / row in a simple select.

Our next step was to eliminate the table lookup. That was simple and inelegant, and looked like this:

ALTER FUNCTION [dbo].[ConvertGMT] (@MYDATE DATETIME)

RETURNS DATETIME AS

BEGIN

DECLARE @OFFSET INT

DECLARE @YEAR INT

DECLARE @RETURNED_DATE DATETIME

SET @YEAR = YEAR(@MYDATE)

IF @YEAR = 2010 AND @MYDATE between 'Mar 14 2010 2:00AM ' and 'Nov 7 2010 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2009 AND @MYDATE between 'Mar 8 2009 2:00AM ' and 'Nov 1 2009 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2008 AND @MYDATE between 'Mar 9 2008 2:00AM ' and 'Nov 2 2008 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2007 AND @MYDATE between 'Mar 11 2007 2:00AM ' and 'Nov 4 2007 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2006 AND @MYDATE between 'Apr 2 2006 2:00AM ' and 'Oct 29 2006 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2005 AND @MYDATE between 'Apr 3 2005 2:00AM ' and 'Oct 30 2005 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2004 AND @MYDATE between 'Apr 4 2004 2:00AM ' and 'Oct 31 2004 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2003 AND @MYDATE between 'Apr 6 2003 2:00AM ' and 'Oct 26 2003 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2002 AND @MYDATE between 'Apr 7 2002 2:00AM ' and 'Oct 27 2002 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2001 AND @MYDATE between 'Apr 1 2001 2:00AM ' and 'Oct 28 2001 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2000 AND @MYDATE between 'Apr 2 2000 2:00AM ' and 'Oct 29 2000 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 1999 AND @MYDATE between 'Apr 4 1999 2:00AM ' and 'Oct 31 1999 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2011 AND @MYDATE between 'Mar 13 2011 2:00AM ' and 'Nov 6 2011 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2012 AND @MYDATE between 'Mar 11 2012 2:00AM ' and 'Nov 4 2012 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2013 AND @MYDATE between 'Mar 10 2013 2:00AM ' and 'Nov 3 2013 3:00AM ' SET @OFFSET = -4

ELSE IF @YEAR = 2014 AND @MYDATE between 'Mar 9 2014 2:00AM ' and 'Nov 2 2014 3:00AM ' SET @OFFSET = -4

ELSE SET @OFFSET = -5

SET @RETURNED_DATE = DATEADD(hour,@OFFSET,@MYDATE)

RETURN @RETURNED_DATE

END

The performance boost with this version was amazing. It processed the same 45,000 x 3 conversion / row query in ≅3 seconds. Across all our GUI and report calls, that was a noticeable performance boost. But, it looked like something someone in CS101 would write, and it still required occasional updates to add new years and keep current.

The final step - and current solution - started with a DST discussion at MSSqlTips that used some pre-calculated offsets to dynamically calculate DST start and stop dates based on the DOW of the start and end months. This worked well for dates 2007 and later, but earlier dates had a different date pattern. I continued the same model that Tim Cullen started with the MSSqlTips post and used a set of precalculated offsets for 2006 and earlier starts, and dynamically calculated DST end dates.

The final function looks like this:

CREATE FUNCTION [dbo].[ConvertGMT]

(@MYDATE DATETIME)

RETURNS DATETIME AS

BEGIN

DECLARE @OFFSET INT

DECLARE @YEAR INT

DECLARE @DST_BEGIN DATETIME

DECLARE @DST_END DATETIME

DECLARE @RETURNED_DATE DATETIME

SET @YEAR = YEAR(@MYDATE)

declare @DSTStartWeek smalldatetime, @DSTEndWeek smalldatetime

if @YEAR >= 2007

BEGIN

set @DSTStartWeek = '03/01/' + convert(varchar,@YEAR)

SET @DST_BEGIN = case datepart(dw,@DSTStartWeek)

when 1 then

dateadd(hour,170,@DSTStartWeek)

when 2 then

dateadd(hour,314,@DSTStartWeek)

when 3 then

dateadd(hour,290,@DSTStartWeek)

when 4 then

dateadd(hour,266,@DSTStartWeek)

when 5 then

dateadd(hour,242,@DSTStartWeek)

when 6 then

dateadd(hour,218,@DSTStartWeek)

when 7 then

dateadd(hour,194,@DSTStartWeek)

end

set @DSTEndWeek = '11/01/' + convert(varchar,@Year)

SET @DST_END = case datepart(dw,dateadd(week,1,@DSTEndWeek))

when 1 then

dateadd(hour,2,@DSTEndWeek)

when 2 then

dateadd(hour,146,@DSTEndWeek)

when 3 then

dateadd(hour,122,@DSTEndWeek)

when 4 then

dateadd(hour,98,@DSTEndWeek)

when 5 then

dateadd(hour,74,@DSTEndWeek)

when 6 then

dateadd(hour,50,@DSTEndWeek)

when 7 then

dateadd(hour,26,@DSTEndWeek)

end

END

ELSE

BEGIN

set @DSTStartWeek = '04/01/' + convert(varchar,@YEAR)

SET @DST_BEGIN = case datepart(dw,@DSTStartWeek)

when 1 then

dateadd(hour,2,@DSTStartWeek)

when 2 then

dateadd(hour,146,@DSTStartWeek)

when 3 then

dateadd(hour,122,@DSTStartWeek)

when 4 then

dateadd(hour,98,@DSTStartWeek)

when 5 then

dateadd(hour,74,@DSTStartWeek)

when 6 then

dateadd(hour,50,@DSTStartWeek)

when 7 then

dateadd(hour,26,@DSTStartWeek)

end

SET @DSTEndWeek = '11/01/' + convert(varchar,@Year)

SET @DST_END = dateadd(hh,3,dateadd (dd,(1 - datepart(dw,@DSTEndWeek)),@DSTEndWeek))

END

IF @MYDATE between @DST_BEGIN and @DST_END

SET @OFFSET = -4

ELSE

SET @OFFSET = -5

SET @RETURNED_DATE = DATEADD(hour,@OFFSET,@MYDATE)

RETURN @RETURNED_DATE

END

This code has one change from Tim's code - it now ends at 3am in November, for 2007 and later. This is what we have in production now. It's just as performant as the previous version (≅3 sec for the benchmark query), but it has the advantage of never needing updating for new years. It's more elegant than it's predecessors, but I have no doubt that it could be done better.

Use, share, comment.

-Bill

(Thanks to the Wikipedia DST Article for clarity on this.)