Part 1 Part 2

It’s not everyday that you find a company or IT department that keeps a structured and updated “update log” for their systems, let alone Sharepoint. Updating your software is as easy as clicking a few buttons and trusting that everything will work as advertised. And for the most part, application updates really do what they are supposed to without causing outages. With communities, blogs and wikis for just about every application in existence, developers find it much easier to distribute test builds to the masses and receive responses rather quickly. This translates to quicker bug fixes and an overall better experience for end-users. But this can also lead to an ultra-trusting mentality when it comes to updating your software with major upgrades and/or patches. It only takes one patch to create hours of work for you and your team to get the system back online when incompatibilities rear their ugly heads.

This three part article will aim to give you my thoughts for an effective update log for your Sharepoint servers.

NOTE: I’ve written this article with respect to Sharepoint specifically. It is part of a larger concept that could be incorporated into all LOB systems.

The first thing that we need to do is figure out where we should store our data. Depending on your needs and organizational size this may be a very simple setup, but it still helps to think it through first.

The table below summarizes a few pros and cons for 4 possible scenarios.

[TABLE=2]

This is a pretty self-explanatory table, but I want to touch on one of these a little bit deeper. When thinking of an update log for Sharepoint servers, the first thing that comes to mind would be an easy to maintain Sharepoint list. It is by far the quickest way to get data in a nice format that others can read/edit. But this option does not accomplish enough independence from the platform itself. If your Sharepoint farm is down, then you’ve negated the update log completely. So although Sharepoint may be an option for you (if you aren’t worried about a severe failure), I would recommend not storing your data in a native Sharepoint list.

With that said, I’ve chosen to store my data in a custom table on MS SQL Server 2005. The below query will get you started with a simple update log table to store your data. You can change the structure below to match your naming system, but make sure that you are not adding this table to any of your existing Sharepoint content databases. It can be tempting to store this data in your default Sharepoint content database for ease of use, but that comes along with a higher security/corruption risk. And although I haven’t tried this long enough to see if it will break something in Sharepoint (like a restore process), play it safe and create a separate database. I’ve called mine “admin” to keep things simple.

USE [admin]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[sp_updates](
[id] [int] IDENTITY(1,1) NOT NULL,
[developer] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
–Identifies the developer of the update
[application] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
–Identifies the application affected
[type] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
–Identifies the type of update applied
[number] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
–Identifies the update number or KB number for Microsoft updates
[change_log] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
–Refers to a UNC path or HTML link to a change log file
[date] [datetime] NOT NULL
) ON [PRIMARY]

Stay tuned for the next article where I will show you a small custom aspx site that will access this table. Furthermore, in the third and final article on this subject, I will display the best of both worlds. It will cover the usage of Sharepoint as a front-end display for your update log, while still maintaining a level of data independence.

Your feedback is always welcome!