[MOSS2007/WSSv3] Using a clustered SQL named instance behind a firewall in SharePoint

[Issue]
For a customer I have been working on a new installation of SharePoint 2007. The environment has high availability requirements, so all components need to be redundant. The customer environment already contained a clustered SQL environment which we could use for SharePoint. We just needed to install a new SQL named instance, dedicated for the SharePoint 2007 installation.

For extra security, the SQL cluster was placed in a separate database VLAN, which was secured by a firewall. To enable communication between the SharePoint 2007 servers and the SQL named instance we opened UDP port 1434 and TCP port 4000 (port used by the instance).

After installing SharePoint 2007 on one server, I ran the SharePoint Products and Technologies Configuration Wizard to create a new farm/Configuration Database. When I used the <server>\<instance> format, SharePoint reported that it could not connect to the instance. It reported that the instance was not running, the server wasn’t a SQL server or a firewall was blocking the traffic.

[Reason]
After troubleshooting on the SharePoint and SQL servers, I turned to the firewall guys. With their troubleshooting tools we found out that:

• SharePoint connected to virtual SQL cluster name (ip 10.10.10.3) using the UDP protocol over port 1434 to determine the port of the SQL instance (see “Background Information”)


• The firewall allowed the traffic to the virtual SQL cluster name


• The server replied on the physical node ip address (ip 10.10.10.1) back to the server


• Because the server registered an outbound connection on the virtual SQL cluster name, it does not allow a reply on a different ip address and blocked the reply

Due to this behavior the SharePoint Config Wizard could not determine the port used by the instance and therefore could not connect to the instance.

After using Google we ran into the following article:
Unable to connect to a SQL Server named instance on a cluster

This issue is caused by the fact that the SQL Browser is not “cluster-aware”. It listens to all ip addresses of the server and replies always on the ip addresses of the physical node instead of on the ip address on which it received the request. The solution according to the blog post: Use SQL Server 2008 in which it is solved…….no real option at the moment.

[Solution]
To solve this issue, we created a ODBC System DSN which is configured to connect to the correct port directly and preventing the discovery process from happening:

• Start > Settings > Control Panel > Administrative Tools > Data Sources (ODBC)


• Tab System DSN


• Click Add


• Select SQL Server


• Enter the name you would like the DSN to have


• Enter a description


• Enter the name of the server


• Click Next


• Click “Client Configuration”
  Clear the “Dynamically determine port” checkbox and enter the port number of the SQL instance


• Click Next


• The connection is now checked. Click Finish to save the DSN

Now use the name of the DSN as the SQL server name.

[Background information]
When a named instance is used, in most cases that instance is running on a port which is chosen randomly at installation time (customizable afterwards). Any processes that would like to use that specific instance first connect to the SQL Browser (using UDP over port 1434), which supplies the port that the instance is running on. The process then connects to the specific port.

[MOSS2007] Error in search configuration after installing Infrastructure Update

The infrastructure update introduces a Search Administration page on the Shared Services. When creating a slipstream installation, this page is automatically created when creating the Shared Services. But when you install the Infrastructure Update on an existing environment, this page is not automatically created. It does change the breadcrumb trail on the Search pages of the Shared Services site, which cause some error messages. You cannot open the “Search Administration” page (http://<ssp_url>/ssp/admin/searchadministration.aspx).

To solve this, run the following commands:
stsadm.exe -o installfeature -name S2SearchAdmin
stsadm.exe -o activatefeature -name S2SearchAdmin -url http://<ssp_url>/ssp/admin -force

Usefull and Free SharePoint Tools (Part 2)

A while ago I posted a list of tools I ran into and found very usefull. Here is part 2: More usefull tools :-)

SharePoint Inspector
Using the SharePoint Inspector you can browse through the SharePoint object model, can see objects composing its structure, get their properties by reflection. This cool can be very useful when you want to check if your code does what it should do. You can use also some advanced features like activate/deactivate SharePoint features, add/remove event receivers, manage your recycle bin.

Link: SharePoint Inspector

SharePoint Manager
The SharePoint Manager 2007 is a SharePoint object model explorer. It enables you to browse every site on the local farm and view every property. It also enables you to change the properties (at your own risk). It is a very powerfull tool for developers that like to know what the SharePoint holds of secrets.

Link: SharePoint Manager

MS IT Site Delete Capture
Every had the unpleasant question of users if you could restore a site or site collection they accidentally deleted? The only way to do this by default is to perform a restore to a separate environment and use stsadm to extract the site and restore into your environment again. Even though SharePoint 2007 does have a recycle bin, this does not capture site or site collection deletions.

After you installed the Microsoft IT Site Delete Capture Feature 1.0 SharePoint administrators can intercept both site/web delete requests and archive the site/web to a resource local to the web front-end computer or UNC path before the site/web is removed from the configuration and content databases.

The Microsoft IT Site Delete Capture Feature 1.0 also exposes functionality allowing SharePoint administrators to send e-mail messages to the end-user indicating the site has been archived and deleted, any failure in the event receiver will generate an e-mail message to the end-user indicating that the site/web has not been deleted. The message format, text, and language are stored in a flexible, culture-independent extensible markup language configuration file.

Link: MS IT Site Delete Capture

SharePoint 2007 Test Data Population Tool
The SharePoint 2007 Test Data Population Tool is a capacity planning and performance testing tool that populates data for testing SharePoint deployments. The SharePoint 2007 Test Data Population Tool is available as a command-line executable program that extracts information about how to populate the server from an XML configuration file.

Use this tool to populate test data for SharePoint capacity planning. Also included are some sample test scripts that can be used for performance and load testing SharePoint and Excel Services with the test data. These tools are being provided as is and there is no ongoing support for these tools.

Link: SharePoint 2007 Test Data Population Tool

SharePoint Logging Spy
A lot of SharePoint 2007 environments consist of multiple servers. Each server is producing logs and in case of issues you always have to dig through multiple server logs. The SharePoint Logging Spy allows you real time diagnostics of multiple servers in a SharePoint farm using a single console view.

SharePoint Logging Spy is a real time diagnostic application for MOSS 2007 which allows a SharePoint administrator to view (and save to disk) the ULS Log & Event log entries from multiple machines in a SharePoint farm through a single console. NOTE - There is a known issue with Event Log Entries not being displayed from remote machines. SharePoint Logging Spy also allows the administrator to view the historic ULS logs on the farm using the same interface.
SharePoint Logging Spy also has a treeview which allows a developer or administrator to view the state of the current SharePoint enviroment. The Plug-in architechture allows for extensibility of the core application.

The application also provides drill downs for information regarding the thread or process which generated the log entries which are reported in the main screen. The thread and process information is collected on demand (when you click the menu) and is NOT historical therefore it is feasable that the process/thread may no longer exist when you ask for the information and you will get an empty result set.
To easy configuration there is a discovery wizard which will discover on-line servers in the farm and allow selective monitoring to begin.

Link: SharePoint Logging Spy

SharePoint SUSHI
SUSHI is a powerful, user-friendly SharePoint application enabling you to accomplish common SharePoint administrative and development tasks. You can think of SUSHI as a Swiss army knife for SharePoint.

SUSHI = SharePoint Utility with a Smart, Helpful Interface

SUSHI Tools
Administration
Security Reports: View all sites and lists a user has access to (VERY USEFULL!!!)
Profile Images Import: Upload Profile Images and Apply them to User Profiles.
Backup: Backup a site. Restore: Restore site from backup.
Email Test: Send a test email to make sure outbound email is set up correctly.

Lists
Copy View: Copy a view from one SharePoint list to another. Meta Data: Get detailed information about metadata in a list. Move metadata from one column to another for all items in a list. And more.
Bulk List: Creation Create a large number of subsites based on a site template.
Bulk Site Columns: Create Site Columns. Import Documents: Upload entire file structures from a file share into a document library.

Sites
Bulk Site Creation: Create a large number of subsites based on a site template.
Themes: Apply a Theme to many sites at once.

Link: SharePoint SUSHI