[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] "Database Default Locations" ignored by SharePoint 2007

PROBLEM:
While installing a new environment today, I changed the "Database Default Locations" setting in SQL Server 2005 to make sure the database files and log files were created on a certain location. Testing by creating a database manually in the SQL Management Studio worked, but when I ran the SharePoint Configuration Wizard the databases were created in the old location.

CAUSE
After some troubleshooting I found out that SharePoint somehow retrieves the old setting until SQL Server 2005 has been restarted (reboot of the server or restart of the services). Why this is I have no clue.

RESOLUTION
Just restart SQL Server 2005 and try again. The new databases are now created in the correct place.

MOSS2007 Installation guide

Over the past few weeks I have been working on an installation guide for MOSS2007. I have updated a document I created earlier with new insights and am glad to announce that it is available for download!!

Available here:Microsoft Office SharePoint Server 2007 Installation Guide

[UPDATE: A new version has been released. More info, see this post]

Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007

A few days ago Microsoft released Service Pack 2 for SQL Server 2005. This Service Pack enables deep integration with Microsoft Office Sharepoint Server 2007 (MOSS2007) and Windows SharePoint Services v3 (WSSv3).

On the blog of the Microsoft SharePoint Products and Technologies Team a new post describes the ins and outs of this integration:
Microsoft SQL Server 2005 SP2 Reporting Services integration with WSS 3.0 and MOSS 2007

Restore via SPSBackup might fail when back-end is running SQL 2005

Since SPS SP2 the use of SQL Server 2005 as back-end database server is supported for Sharepoint (WSS and SPS). But when a backup is made using SPSBackup and restored again, the following message might appear both in SPSBackup as in the Eventlog: "SQL Server restore failed. Error: The operation failed on server <servername>.".

In the log which has been created in the Program Files\Sharepoint Portal Server\Logs directory the following is logged:

00012E8C CRI 00000000 00000E5C Connecting to SQL Server <servername> ...
00012ED2 CRI 00000000 00000E5C Connected to SQL Server <servername>
00012ED2 CRI 00000000 00000E5C Connected To Server
00012ED2 CRI 00000000 00000E5C Entering RestoreDB for database <portalname>1_SITE ...
00012ED2 CRI 00000000 00000E5C Entered Critical Section in RestoreDB for database <portalname>1_SITE ...
00012ED2 CRI 00000000 00000E5C Left Critical Section in RestoreDB for database <portalname>1_SITE ...
00012FCC UNK 00000000 000007F8 Overall % Completed: 0%
00013008 CRI 80045510 00000E5C GetDatabaseByName Failed. Trying to obtain path from master database.
0001313F CRI 00000000 00000E5C Command Text sent to SQL is: RESTORE DATABASE [<portalname>1_SITE] FROM DISK = N'\\backup\bk-<servername>-<portalname>1_SITE.SPB' WITH FILE = 1, NOUNLOAD , STATS = 5, RECOVERY , REPLACE , MOVE N'<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF', MOVE N'<portalname>1_SITE_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Log.LDF', MOVE N'sysft_ix_<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF'
0001313F CRI 00000000 00000E5C Database <portalname>1_SITE does not exist, proceeding to restore it.
0001313F CRI 00000000 00000E5C Restoring database <portalname>1_SITE ...
000131A3 CRI 80040C68 00000E5C Failed to restore the database.
000131A3 UNK 00000000 00000E5C No error information available
000131A3 UNK 00000000 00000E5C No error information available
000131A3 UNK 00000000 00000E5C Exception in BackupSQL: Source=SQLBkMgd, Message=The operation failed on server <servername>. For more information, see the Microsoft Windows Event Log on that server.
000131A3 UNK 00000000 00000E5C BackupSQL Thread setting its status to completed and exiting

After copying and pasting the query into the SQL Management Studio, SQL returns the following error:

Msg 3176, Level 16, State 1, Line 1
File 'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF' is claimed by 'sysft_ix_<portalname>1_SITE'(65537) and '<portalname>1_SITE'(1). The WITH MOVE clause can be used to relocate one or more files.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

The above information indicates that two parameters of the query point to the same name. Investigation of the query turns out that this is the case. Both the SITE database as the index are being restored to the same MDF file:
MOVE N'<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF',
MOVE N'sysft_ix_<portalname>1_SITE' TO N'C:\Program Files\Microsoft SQL Server\MSSQL\data\<portalname>1_SITE_Data.MDF'

The solution to this problem is installing the hotfix KB914445. Unfortunatelly this hotfix cannot be downloaded from the Microsoft site, so you have to call Microsoft to ask them to send it to you by email. Or if you have access to the Premier site, download it there.

"Restore operation is not completed successfully when you use the SharePoint Portal Server Backup and Restore tool (Spsbackup.exe) to restore a portal site from backup

You use the SharePoint Portal Server Backup and Restore tool to back up the portal site. However, when you try to restore the portal site from backup, the operation is not completed successfully. This problem occurs if the following conditions are true:

You use Microsoft SQL Server 2005 as the database back end for SharePoint Portal Server 2003.

You configure the Enable full-text search and index component option for content that is hosted on the portal site.

In this situation, the restore operation is not completed successfully because of a file naming conflict that occurs in SharePoint Portal Server 2003."

[UPDATE] The mentioned hotfix has been included in the SharePoint Portal Server 2003 post-Service Pack 2 hotfix package: June 16, 2006 (KB919175)