Monday, August 31, 2009

Upgrading MS BI to SQL 2008: Do not underestimate the Power of the Dark Side

For our BI setup running under Microsoft BI 2005, we have an environment that comes straights from scholar manual. We have a development environment (DEV), composed of 4 servers: two are SQL 2005 database servers (staging database and a data warehouse ), one server is a SharePoint 2007 server hosting also the SharePoint database (SQL 2005) and a Visual Studio server. Similarly, we have a testing environment (TEST) composed of 3 servers: a staging database, a data warehouse, and a SharePoint server hosting the SharePoint database as well. Both the DEV and the TEST environments are virtualized. Then we have our production environment (PROD) composed of 4 physical servers: a staging server, a data warehouse, a SharePoint server, and a SharePoint database server. The only difference being that in production, we have separated the SharePoint front layer from the database layer for performance reasons.

Developers develop in the DEV environment and technical approvals are done there. Testers test in the TEST environment, which is also used for the functional approvals. Production data run in the production environment. So far so good and nothing new. I just described an out-of-the-manual architectural setup. All databases are up-to-date SQL 2005 and the SharePoint version is 2007.

We had planned to upgrade this environment to Microsoft SQL 2008 and thought it was quite well planned. We had waited for SQL 2008 SP1 to be released. We had waited for our sub-contractor to accumulate some upgrade experience and finally decided to migrate progressively. During last July, we migrated our DEV environment. Everything went smoothly. Some of us then went on vacations. The migration of our TEST environment was planned for mid-August and took place as expected. All functional tests we had prepared were passed without any problem. As this point, and as all lights were green, I decided to authorize the migration of the PROD environment.

And guess what ? Everything went fine with only one unexplained manual reboot. We later even noticed a small but noticeable performance gain with SQL 2008.

On the next days, I got an alarming phone call from the IT operations department. They reported that since the SQL upgrade all production servers kept rebooting every 2 to 12 hours without apparent reasons.

We then immediately started our investigations but quickly discovered that there was no logged Windows event or error message. A closeup monitoring of SQL did not give us any clue. A couple of days were necessary to find out that the reboots were caused by the monitoring systems because the servers were freezing for more than 10 minutes. Still, we had no idea why they were freezing. It only happened on the physical PROD servers, not on the virtual DEV or TEST servers.

The main difference between virtual and physical machines concerns the underlying drivers. After a couple of days, we upgraded several drivers on one of the blade servers: HBA StorPot card, Ilo2 card firmware, blade power management control and various other minor drivers. It worked and fixed the problem.

Conclusion of the story ? You cannot foresee everything and even if you work by the book, shit may happen. The good news is that we only lost 3 days with a very limited impact on end-users.

Wednesday, August 5, 2009

Picturing SharePoint Lists

For one of my recent SharePoint projects, I have been asked to create lists where an image could be included to illustrate the content of each list entry. As each entry will have a picture attached to it, the picture upload process had to be quite straightforward and user-friendly.

I had first pointed my thoughts toward a picture library but soon discovered that it did not correspond totally to my needs. After some more googling, I found the Enhanced picture custome type on CodePlex. Here then comes the necessary complete instructions to install and parametize this nice add-on.

1/ As prerequisite, you need to install ASP.NET AJAX on your SharePoint (MOSS 2007 or WSS 3.0). This operation is made extremely easy thanks to the detailed instructions on Mike Ammerlaan's blog post regarding Installing ASP.NET AJAX on SharePoint. Ultimately, it all comes down to downloading an EXE file, running it, then copying/pasting XML from the blog post into the web.config file of your SharePoint and finishing with an iisreset command.

2/ Once AJAX has been installed, download the Enhanced picture custome type WSP file and install it using the following commands:
stsadm -o addsolution -filename .wsp
stsadm -o deploysolution -name .wsp -allowgacdeployment -url http://sitename -immediate -force

(The -force attribute is necessary if some elements are already installed on your SharePoint.)

3/ Go to your site settings and then under site collection features, activate the ImageEnhancer feature.

4/ Go to your custom list, and add another field of the newly available type Enhanced Image.

5/ On your site, create a picture library. It will be used to store the pictures but you do not need to manage it or create any specific fields for it.

6/ Go back to your list and under the general settings, click on Enhanced Image Picture Library. Select the picture library you created before and click ok.

7/ That's it, you're ready to upload picture inside your custom list with maximal effects and minimal efforts.