Thursday, March 08, 2007

Daylight Savings Time issues

We realized late last week that this DST change on 3/11/2007 is going to affect individual calendar entries in Lotus Notes and it can't be fixed simply by changing the clocks manually. Doh! The affected calendar entries will show meetings as one hour off their intended times when looking at them in the calendar view

Anyway, any calendar entry created from a computer that does not have the Daylight Savings Time patch installed (i.e. does not recognize the new Time Zones).

Calendar entries in Notes have a field called StartTimeZone that tells you the time zone code used when the meeting or appointment was scheduled. On unpatched machines this time zone is different than on patched machines, so this lets us spot calendar entries that will show the wrong time in the calendar view.

I added this column to the Calendar view in our mail template and now all of our users can spot these potentially wrong meetings easily. Make sure it is the right-most column which has the "Use Value as Color" setting checked, otherwise another column to the right will override these color highlights.

REM {this color column is used only if the user has not saved their mail preferences}; badtimezonelist := "Z=6$DO=1$DL=4 1 1 10 -1 1$ZX=18$ZN=Central":"Z=5$DO=1$DL=4 1 1 10 -1 1$ZX=25$ZN=Eastern":"Z=7$DO=1$DL=4 1 1 10 -1 1$ZX=41$ZN=Mountain" meeting := 194:229:255:0:0:0; appointment := 197:255:228:0:0:0; reminder := 250:236:218:0:0:0; event := 240:255:215:0:0:0; anniversary := 255:232:246:0:0:0; todo := 197:255:255:0:0:0; warning := 255:48:0:0:0:0; withindates := @If(@Elements(@Keywords(@Text(CalendarDateTime); @Text(@Explode([03/11/2007 - 03/30/2007])))) = 0; @False; @True); @If(@IsMember(StartTimeZone; badtimezonelist) & withindates; warning; @LowerCase(form) = "task" todo; AppointmentType = "0" appointment; AppointmentType = "1" anniversary; AppointmentType = "2" event; AppointmentType = "3" meeting; AppointmentType = "4" reminder; "")

As many sites recommend, we also told our uses to check all entries between March 11th and April 1st, just as a precaution.

The code above only takes into consideration calendar entries that were scheduled in the Eastern, Central, and Mountain timezones. There are a few other timezones implementing this early change, but I wasn't able to find the codes for them. You can find the codes by viewing Document Properties on any calendar entry set for another timezone, and then checking the StartTimeZone field. I'd assume if the $DL section is set to 4 1 1 10 -1, then you've found the code for an unpatched version of that time zone. Then just add that code to the text list called "badtimezonelist".

Monday, February 26, 2007

Upgrading Sharepoint v3.0 SSEE to SQL 2005 Standard

I've been working hard the past few months to prepare for our move from Lotus Notes to Exchange. One of the key tasks of that project is finding a new home for the hundreds of the Lotus Notes databases/apps we currently use.

Many of the simple Notes apps can be redesigned quickly and effectively using Microsoft SharePoint. This new version is so good, it's moved from test server to production server overnight!

Unfortunately, I loaded up SharePoint v3 as a stand-alone server which means it installed the SQL Server Express Edition, aka Windows Internal Database, for its database server. SSEE is nice in that it's free and has no size limitations. However, you can't easily back it up (no SQL agent) and with the use of SharePoint growing so rapidly at my company I decided I'd better upgrade this to SQL Server 2005 as soon as possible.

The searched all over the net for solutions, but couldn't find anyone who had written about moving from SSEE to SQL Server 2005. The best instructions I could find were for Sharepoint 2.0 on moving from WMSDE to SQL 2005

I tried these instructions thinking that they'd be close enough to get the job done. I didn't realize that a key step would get me stuck at a point of no return. The section "Reconnecting to the Configuration Database and restarting the Virtual Servers" expects that you can connect back to the configuration database using Sharepoint Central Admin, but you can't at that point. It has been moved to a different sql server instance and it won't be found. Perhaps there's a way to change that by some hack, but I wasn't about to start messing around.

After panicking for a few moments, and kicking myself for not taking backups of the data other than the mdf and ldf files, I finally did get this to work.

First I uninstalled Sharepoint entirely, and then uninstalling Windows Internal Database using these instructions from Jemm's Sharepoint Blog

Next, I reinstalled Sharepoint v3 and chose the Advanced options and setup the server as a server farm. I went through the wizard and connected the server to my SQL 2005 Standard default instance.

When I got to the screen asking about the configuration database, I thought I might be able to connect it to the previously setup database from SSEE. Using the instructions in the WMSDE to SQL 2005 technote I had gotten the SSEE databases attached to my SQL 2005 Standard edition, but the wizard wouldn't let me connect to the existing database, so I had to create a new one.

I finished the install and then started to configure the Sharepoint server using the SharePoint Central Administrator. I got to the point where you create a content database and at that point I just specified the existing one. SharePoint didn't complain and all the sites and data were in tact.

Thankfully that was all I needed to do. To sum up, the processed I followed was this:

  1. Move the SharePoint database files out of the SSEE directory and attach them to the SQL Server 2005 Standard Edition instance.
  2. Uninstall SharePoint and SSEE
  3. Reinstall SharePoint, specifying Advanced - Web Fronted server instead of stand alone server
  4. Finish install, creating a new configuration database
  5. Specify your old content database(s) using SharePoint Central Administration.