Uncategorized

Intermittent Database Server Connectivity and Microsoft SharePoint Products and Technologies

Intermittent connectivity to a database server hosting Microsoft SharePoint Products and Technologies databases can occur for a number of reasons.  In some cases it may be related to a simple database connection timeout configuration setting in Windows SharePoint Services or in other cases it may be one or more operating system and/or hardware configurations.  In this post we’ll examine several options and configuration settings that can be adjusted to help isolate or mitigate such occurrences.


Before we begin my apologies in advance for the flow, this started initially as  a collection of notes and thoughts.


Monitoring


Consider monitoring the following conditions to establish a history of database connectivity issues that can help to identify any potential patterns that can be useful in troubleshooting.  Correlate these events to the ULS, Event, and any other logging implementations.


Event Id 3760, Event Id 3355, and event Id 5586 are commonly related to database connectivity issues in Microsoft SharePoint Products and Technologies.  While other events can be raised at the application level, these are the most common.


Event Id information (Windows SharePoint Services Health Model)


Event Id 3760


http://technet.microsoft.com/en-us/library/cc561036.aspx


Event Id 3355


http://technet.microsoft.com/en-us/library/cc561047.aspx


Event Id 5586


http://technet.microsoft.com/en-us/library/cc561042.aspx


Troubleshooting


Let’s examine some possible areas of configuration at the various layers that can be evaluated or modified to mitigate issues with database connectivity.


Database Timeout (Windows SharePoint Services)


Consider incrementally increasing the database connection timeout setting used by Windows SharePoint Services depending on your latency variants between the application and database layer.  The default value is 15 seconds.  See http://technet.microsoft.com/en-us/library/cc263314.aspx.


TCP Offloading


TCP Offloading can lead to intermittent issues when enabled, consider disabling TCP Offloading through configuring the Registry on the front-end Web and application servers.  See http://support.microsoft.com/kb/904946/en-us.


TCP Chimney


After you install Windows Server 2003 Service Pack 2 (SP2) or Windows Server 2003 Scalable Networking Pack (SNP) on a computer that has a TCP/IP Offload-enabled network adapter, you may experience many network-related problems.  Review the Knowledge Base article here (http://support.microsoft.com/kb/948496/en-us) for options and instructions on how to disable SNP features in Windows Server 2003 (see also below for additional information).


Scalable Networking Pack (SNP)


After you install Windows Server 2003 Service Pack 2 (SP2) or Windows Server 2003 Scalable Networking Pack (SNP) on a computer that has a TCP/IP Offload-enabled network adapter, you may experience many network-related problems.  Consider disabling SNP features on front-end Web and application servers.


Update Information
This update turns off default SNP features. After you install this hotfix, you can manually re-enable these features by modifying registry values. The following files are available for download from the Microsoft Download Center:


· Download the update for Windows Server 2003, x86-based versions (KB948496) package now. (http://www.microsoft.com/downloads/details.aspx?FamilyId=062E954C-FDEC-45AF-A09C-5A05B8F010A5)


· Download the update for Windows Server 2003, x64-based versions (KB948496) package now. (http://www.microsoft.com/downloads/details.aspx?FamilyId=38E66572-5D47-4219-82D7-DB0C57478950)


High Stress Scenarios and SQLOLEDB


You cannot access a SQL Server database by using the OLE DB provider for SQL Server when your application is in a high-stress scenario (KB7264) – Explains why you may receive “General Network Error” error messages when a large enterprise application that is in a high-stress scenario tries to access a SQL Server database.
http://support.microsoft.com/kb/907264/en-us


Ephemeral Ports and MaxUserPort


MaxUserPort can be critical in a couple of places for example when a client connects to a server.


Let’s first examine ephemeral ports – there are 5,000 ephemeral ports by default starting at 1025. With Internet Explorer when a connection is established it is on a port > 1025 (for outbound, client port) for a port 80 request and the server subsequently checks if a port in the 5,000 range is available.  If there are a substantial number of Web Parts deployed on a source page that result in additional calls, particularly authenticated connections, there is a time to live element that comes into play before the port can be reused. Once the ceiling of 5,000 is reached, we circle back around and start re-using ports, even if they are in use – this condition will basically appear as the inability to make an outbound connection or the connection simply disappears. On the front-end Web or application server where call is made we can increase MaxUserPort to help mitigate these occurrences. If you cannot leverage *connection pooling in most cases you will require more ports so you will likely have to increase MaxUserPort.


*Description of TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled (328476) – Describes certain TCP/IP settings that you may have to adjust when SQL Server connection pooling is disabled. You may have to change those TCP/IP settings for the operating system to deal with the higher stress levels.
http://support.microsoft.com/kb/328476/en-


Basically anything that can cause the client to fail to open a TCP/IP socket to the computer that is running SQL Server can also cause Event Ids 3760, 3355, and 5586; however, with a stress related socket issue, the problem will occur intermittently as the stress increases and decreases.  In this scenario a front-end Web or application server may appear normal for hours or more with no errors, then the error occurs one or two times, and the front-end Web or application server then runs for several more hours with no errors. 


Basically in this context when you are experiencing this problem general database server connectivity will be normal at one point only to fail the next and subsequently works again at a subsequent point.


In summation stress-related socket issues in most circumstances occur sporadically where conversely actual connectivity problems with SQL Server will generally not exhibit this behavior, in most cases its all or none.


This is one of the most least common occurrences I’ve come across to date.  Most are related either to 1) a combination of SNP and interface features or 2) a SQL Server Connection Alias implementation (see below).


SQL Server Connection Aliases


If you are using SQL Server Connection Aliases to compliment Microsoft SQL Server Database Mirroring or optionally to make the database instance portable in support of migrations or operational functions, you should verify that Dynamically Determine Ports is not enabled in the SQL Server Client Network Utility.  When no port number is stored for the alias entry the DBNETLIB attempts to contact the server through a known UDP port to obtain the correct connection information to establish the connection, under certain scenarios this can result in losses of connectivity.


Additional Considerations and Knowledge Base References


The following references and knowledge base articles provide information and/or supporting documentation related to general connectivity issues that may be useful in isolating and resolving issues with intermittent database connectivity.


KB196271 When you try to connect from TCP ports greater than 5000 you receive the error ‘WSAENOBUFS (10055)’


http://support.microsoft.com/default.aspx?scid=kb;EN-US;196271


KB120642 TCP/IP and NetBT configuration parameters for Windows 2000 or Windows NT


http://support.microsoft.com/default.aspx?scid=kb;EN-US;120642


KB314067 How to troubleshoot TCP/IP connectivity with Windows XP


http://support.microsoft.com/default.aspx?scid=kb;EN-US;314067


KB193602 Configuration options for WLBS hosts connected to layer 2 switches


http://support.microsoft.com/default.aspx?scid=kb;EN-US;193602


KB227812 Only TCP/IP Should Be Bound to Virtual Network Adapter in WLBS Host


http://support.microsoft.com/default.aspx?scid=kb;EN-US;227812


KB816792 How to configure TCP/IP Filtering in Windows Server 2003


http://support.microsoft.com/default.aspx?scid=kb;EN-US;816792


KB914841 How to simplify the creation and maintenance of Internet Protocol (IPsec) security filters in Windows Server 2003 and Windows XP


http://support.microsoft.com/default.aspx?scid=kb;EN-US;914841


KB287932 INF: TCP Ports Needed for Communication to SQL Server Through a Firewall


http://support.microsoft.com/default.aspx?scid=kb;EN-US;287932


KB889647 How to change the TCP port that SharePoint Portal Server or SharePoint Server uses to connect to SQL Server


http://support.microsoft.com/default.aspx?scid=kb;EN-US;889647


KB942957 Security rules for Windows Firewall and for IPsec-based connections in Windows Vista and in Windows Server 2008


http://support.microsoft.com/kb/942957


KB904046 You experience intermittent communication failure between computers that are running Windows XP or Windows Server 2003


http://support.microsoft.com/kb/904946/en-us


KB830471 You experience intermittent connectivity when you connect to a network from a computer that is running Windows XP or Windows Server 2003


http://support.microsoft.com/kb/830471


BLOG Chris Gideon’s Blog [#50070: Unable to connect to the database <Database Name>]


http://blogs.msdn.com/cgideon/archive/2006/05/24/605454.aspx


BLOG Todd Carter’s Blog [Database Disconnect Issues with SharePoint]


http://blogs.msdn.com/toddca/archive/2008/03/23/database-disconnect-issues-with-sharepoint.aspx

Standard
Uncategorized

Renaming Content Databases

I received an inquiry this morning on what procedural steps are required to rename a Windows SharePoint Services 3.0 content database and decided to make the information more broadly available.

Step 1 Detach Content Database

Detach the Windows SharePoint Services 3.0 content database from its associated Web application in either Windows SharePoint Services 3.0 or Microsoft Office SharePoint Server 2007 using the steps as outlined below:

  1. On a Web front-end computer open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.
  2. Enter STSADM -o deletecontendb -url <http://<virtualServer&gt; -databasename <databaseName> -databaseserver <databaseServer> and press Enter to detach the content database (Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007).  For additional information on the addcontentdb operation see http://technet.microsoft.com/en-us/library/cc287664(TechNet.10).aspx.

NOTE Content databases optionally can be detached from their associated Web application using the SharePoint 3.0 Central Administration interface.  For additional information on managing and removing content databases using the SharePoint 3.0 Central Administration user interface see http://technet.microsoft.com/en-us/library/cc262440(TechNet.10).aspx#section3.  In Microsoft Office SharePoint Server 2007 you should run the STSADM preparetomove operation prior to detaching content databases.  This prevents the synchronization service from failing and losing user privacy settings for those sites after the sites are moved.

Step 2 Rename Content Database

Rename the content database (Microsoft SQL Server 2005 instructions)

  1. Connect to your database server and open SQL Server Management Studio.
  2. In Object Explorer connect to an instance of the SQL Server 2005 Database Engine, and then expand that instance.
  3. Set the database to single-user mode.  See http://msdn.microsoft.com/en-us/library/ms345598.aspx for instructions on how to set single-user mode using the SQL Server Management Studio.
  4. Expand the Databases node, right-click the database to rename, and then click Rename.
  5. Enter the new database name, and then click OK.

NOTE Renaming databases using SQL Server Management Studio will only rename the database, to rename the data and transaction log files you must use Transact-SQL following the steps as documented in the SQL Server 2005 Books Online at http://msdn.microsoft.com/en-us/library/ms174269.aspx.  Any time a database is renamed you should consider backing up the master database.

Step 3 Attach Content Database

After the database has been renamed on the database server, reattach the content database to its associated Web application be following the steps below:

  1. On a Web front-end computer open a command prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.
  2. Enter STSADM -o addcontentdb -url <http://<virtualServer&gt; -databasename <databaseName> -databaseserver <databaseServer> and press Enter to attach the content database (Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007).  When using a SQL login you supply the appropriate credentials using the -databaseuser and -databasepassword arguments.  To reconfigure the site warning and site maximum count for the content database, pass the -sitewarning and -sitemax arguments.  For additional information on the addcontentdb operation see http://technet.microsoft.com/en-us/library/cc288692(TechNet.10).aspx.

NOTE Content databases optionally can be attached to their associated Web application using the SharePoint 3.0 Central Administration interface.  For additional information on managing and adding content databases using the SharePoint 3.0 Central Administration user interface see http://technet.microsoft.com/en-us/library/cc262440(TechNet.10).aspx#section1.

Ensure all dependencies are updated to reference the new database name to include monitoring, backups, mirroring, etc.

Standard
Uncategorized

Tip of the Day, April 25, 2008

Tip of the day:  Deleting orphaned Timer Jobs

Open SharePoint Central 3.0 Central Administration and click Operations.

Select Timer job definitions under Global Configuration.

image

Select the orphaned Timer Job from the list of Timer Jobs.

Locate the Globally Unique Identifier (GUID) for the Timer Job in the browser address bar.

image

Copy the GUID to the clipboard and replace %2D in the GUID with hyphens.  For example the entry in the illustration above should appear as fc42d1d3-1e94-4dee-998d-e0086b6f8300.

Open a Command Prompt and change directories to %commonprogramfiles%Microsoft SharedWeb Server Extensions12BIN.

Run stsadm -o deleteconfigurationobject {guid} where {guid} is the GUID captured in the previous steps.

NOTE Use this process to manage other orphaned configuration objects in addition to Timer Jobs!

Standard
Uncategorized

Productivity on the go…

Of the many benefits of Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007, often overlooked is the native support of mobile devices through embedded mobile site URLs and mobile views.


Mobile views permit both viewing and updating Lists and Document Libraries from a mobile device. 


When considering leveraging mobile views, you should carefully consider the restrictions on length and size of some parts of a list or library. 


Limitations


Rendering and performance are considered in mobile views, therefore it is important to understand the limitations imposed by these considerations when implementing Lists and/or Document Libraries, for instance many common fields should have a character limitation of no greater than 20 applied, these include List and Document Library titles and names, and List and column name titles.  When the 20 character limit is exceeded, mobile device users will be presented with an ellipses representing the additional characters.  In example, a List title ThisListHasOverCharacters will be rendered on the mobile device as ThisListHasOverChara… See below for a complete list of limitations to consider when designing Lists and Document Libraries settings for mobile views.


























































Item Limit
Characters in the Web title of a list or library 20
Characters in a list or library name 20
Number of mobile views 10
Number of items displayed in a view 100
Characters in a list item title 20
Characters in a column name 20
Single-line text field type 256
Multiple-line text field type 256
Each choice in a choice field type 10
Number of options in a choice field type 10
Characters in each item in a lookup field 20
Number of options in a lookup list 20
Characters in a hyperlink or picture field 20
Characters in an attachment file name 20
Number of attachments (to list items) displayed 3
Characters in a calculated field 20

NOTE Discussion Boards, the Currency, Yes/No, and Person or Group column types are not supported by mobile views.


Accessing Mobile Site URLs and Views


Mobile site URLs and views are native to Windows SharePoint Services 3.0/Microsoft Office SharePoint Server 2007, to access a mobile site URL or view simply append the URL with /m.  In example, <A href="http://%3cserver%3e/%3Cpath%3E/%3Csite%3E's&quot; mce_href="http:/// /’s”>http://<server>/<path>/<site>’s mobile URL can be accessed through <A href="http://%3cserver%3e/%3Cpath%3E/%3Csite%3E/m&quot; mce_href="http:/// &#010://m”>http://<server>/<path>/<site>/m.



Standard Site URL View



Mobile Site URL View



Creating Mobile Views


In addition to the embedded mobile views you can also create mobile views for Lists and Document Libraries.


To create a mobile view, select the List or Document Library source and select Settings, and then select Document Library Settings or List Settings from the menu.



Select Create view and select Standard View under the Choose a view format section.


NOTE Calendar, Access, Datasheet, and Gannt views are not supported for mobile devices.


Using the table in the section labeled Limitations as a guide, complete the required fields on the Create View:  <List/Document Library> page.


Expand Mobile on the Create View:  <List/Document Library> page.



Select Make this a mobile view (Applies to pubic views only) and optionally Make this the default mobile view (Applies to public views only).


Click OK.


Now that you understand mobile site URLs and views in Windows SharePoint Services 3.0/Microsoft Office SharePoint Services 2007, go collaborate…on the go!

Standard