Excel Calculation Services for Cross-Forest Deployments

While on vacation, yes working ;-), I was asked to look into an issue where Excel Calculation Services was deployed in a cross-forest environment; specifically where Excel Services is deployed on server A residing in Forest 1 and the clients requesting workbooks reside in Forest 2. Forest 2 does not trust Forest 1; however, Forest 1 trusts Forest 2. The Excel Calculation Services topology in this scenario was fairly basic where Excel Web Access, Web Services, and the Calculation Services are distributed across two network load-balanced web front-end servers and Excel Calculation Services and any UDF assemblies are hosted on a separate application server also servicing the Office SharePoint Server Search indexing service.


The Excel Calculation Services topology is supportive of a cross-forest deployment and only needed one minor adjustment. In a scenario such as that described above to allow workbooks in trusted file locations to be access across domains [in this scenario, the Windows SharePoint Services Web application is defined as a Trusted File Location and includes all children in the trust] Excel Calculation Services must be configured to allow cross domain access using the SharePoint administration tool (STSADM). To allow access across domains you will need to run the following STSADM operation on the application server hosting the Excel Calculation Services component:


stsadm.exe -o Set-EcsSecurity -Ssp <SSP name> -AllowCrossDomainAccess true


About the Components

The Excel Calculation Services component is the “engine” of Excel Calculation Services and handles loading, calculation, session management, and external data refresh for workbooks.

The Excel Web Access component is a Web part used to display and enable interaction with Microsoft Office Excel workbooks in a browser.

The Excel Web Services component is a Web service hosted in Microsoft Office SharePoint Server 2007 that provides methods that a developer can use as an API to build custom applications based on Microsoft Office Excel workbooks.

Basic Component Distribution

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!

Site Collection Sizing Considerations

Site collection sizing is an important consideration in an overall capacity planning and governance solution.  This article details the considerations when planning for capacity and determining how site collections should be sized.

SharePoint Administration Tool (STSADM)

The SharePoint administration tool, STSADM is most commonly used by SharePoint Products and Technologies administrators to backup and restore site collections or perform a variety of administrative tasks. Typically as the site collection size grows, the ability to use STSADM to backup and restore the site collection diminishes. Performance issues, including unscheduled recycling of an application pool result in failure of the site collection backup/restore and are often the result of resource contention when a large amount of data is backed up; the specifications as provided with SharePoint Portal Server 2003 were 2GB (http://support.microsoft.com/kb/889236). Though much improved in Microsoft Office SharePoint Server 2007/Windows SharePoint Services 3.0, the opportunity of resource contention exists nonetheless and as a result STSADM should be considered a supplemental tool to your overall backup and recovery solution.

Site collections should be sized in a manner permitting manipulation of their content and host. A site collection whose size is compatible with the limitations of STSADM allows SharePoint Products and Technologies administrators to manipulate the site collection including its movement across content databases or even database servers/server farms.

Fewer & Larger Site Collections

Many businesses may have a need to have fewer site collections to provide a better overall view in respect to their environment where a site collection of 0-15GB may not be suitable to host the amount of necessary content. Large site collections are often selected to take advantage of aggregation and specific workflow capabilities. In these situations to permit simple recovery and management, it is often a best practice to isolate those site collections in their own content database. This allows the SharePoint Products and Technologies administrator(s) to easily recover the site collection and/or content. A consideration before making a decision on fewer larger site collections is the potential performance implications if the site collection will host a large amount of content, either at its root level or within residing webs. Typically performance will become progressively worsened as the number of total objects exceeds 2,000. As a site collection grows to a point to where performance suffers, a SharePoint Products and Technologies administrator can use the STSADM export and import operations to manipulate the fastest growing webs into their own site collection, thereby reducing the overhead associated with maintaining it side-by-side with other webs within a site collection. As with the site collection recommendation, this web should reside in a dedicated content database; however, the move should occur when the web is still within the parameters of the SharePoint administration tool (STSADM).

Another consideration of enabling large site collections is the ability to delete a site collection/web both through the web user interface and the SharePoint administration tool. The SharePoint Products and Technologies delete process is most often an end-user request resulting from submission of the request to the web front-end computer through the user interface. The request arrives at the SQL database server at which point the stored procedure Proc_DeleteSite is executed. Proc_DeleteSite execution results in a batch transaction consisting of many nested transactions dependent on the number of items in the ownership chain. The batch transaction is executed against the lowest level of ownership and works upward on row by row basis. The SQL database server will instantiate a ‘deleted’ table in memory to host the requests, in the event the memory allocation for the operation is exceeded, the SQL database server will use TempDB to commit the transaction. Each nested transaction within the batch transaction is confirmed and then committed against each item in the ownership chain. An item in this case refers to a document, list item, etc. In the event a nested transaction fails; the batch transaction is rolled back to the outermost begin transaction requiring the SQL database server to instantiate an ‘insert’ table in memory to host the requests; as with the ‘deleted’ table mentioned above, in the event the memory allocation for the operation is exceeded, the SQL database server will use TempDB to commit the transaction. The results can cause SQL blocking in the event a large number of items must be removed in the ownership chain prior to completing the request and occasionally jobs within the enclosing transaction may not be successfully rolled back. Large site collections hosting a significant number of documents and/or list items are particularly susceptible to this issue as a result of the number of transactions occurring on the SQL database server.

Data change rate can also impact a large site collection in which SQL log growth for the site collection when isolated to an individual content database should be closely monitored and maintained. SQL log growth should also be closely monitored for a content database hosting a large site collection in the event database mirroring and/or log shipping are selected as a disaster recovery option for the server farm; greater churn rates can significantly impact the performance of these technologies.

An additional consideration in maintaining a large site collection is the maintenance of permissions and subsequent inheritance.

In the event a large site collection is selected to host content and serve an aggregation performance, a number of search scopes may need to be defined to support providing relative search results to the site collection consumers. An aggregation portal is the most recommended implementation in this scenario to avoid the requirement to navigate several Windows SharePoint Services site collections to retrieve content based upon search results or alert notifications.

Smaller Site Collections

Where the number of site collections is not a concern; site collections are beneficial in that they offer true ownership, storage, and usage analysis reporting which can drive governance and manageability and in addition provide insight into what areas of a business are growing at varying paces. Site collections where growth is limited to a maximum of 15GB provide both ease of management and overall sustainability in terms of resources the ability to manipulate the site collection. Maintaining an environment with many site collections can be achieved through a proper governance plan, leveraging Site Directory taxonomy and the Windows SharePoint Services search service. In the situation where aggregation is desired, Microsoft Office SharePoint Server 2007 can be leveraging establishing an aggregation portal making the results from all site collections hosted on a Web application to be available to the portal through the Office Server search service and properly defined scopes.

Recommendations


  1. Establish a single aggregation portal based on the available SharePoint Products and Technologies Enterprise site templates.
  2. Establish search scopes relative to content sources and business units enabling efficient consumer queries and accurate result sets.
  3. Establish a maximum site collection quota template supportive of the SharePoint administration tool (STSADM); 15GB is the recommendation based upon performance and scale results.
  4. Limit site collection creation to a unique security group to provide oversight and management of the environments content. This allows a group of users to offer content approval prior to the introduction of a site collection to the server farm.
  5. Make site collection templates unavailable through self-service site creation to reduce the number of site collection templates and maintain consistency within the server farm.
  6. Enable usage and advanced usage analysis to gauge the growth of site collections; fast-growing webs may be candidates to become stand-alone site collections. This is easily accomplished when the web is within the limitation of the SharePoint administration tool (STSADM) through the export and import operations.
  7. Establish and maintain a Site Directory taxonomy that is relative to regional, organizational, and business unit aspects at a minimum to provide a concise overview of the environment and ease locating content by taxonomy assignment.
  8. Establish a life cycle management plan using site confirm and usage reporting and/or an out of the box solution. Life cycle management can be tuned according to individual growth, retention, and data management planning.
  9. Establish site collections as document repositories to host content for associated site collections.

Resources

SharePoint Governance and Manageability

I’m pleased to announce the availability of Microsoft IT Team Site Life Cycle Management Beta 1.0 for SharePoint Products and Technologies.

I’m pleased to announce the availability of Microsoft IT Team Site Life Cycle Management (MSIT TSLCM) Beta 1.0 for SharePoint Products and Technologies.


Microsoft IT Team Site Life Cycle Management is a custom solution that provides lifecycle management for Windows SharePoint Services site collections and webs through write locking, backing up and recovering site collections/webs and is based on the out-of-box Site Usage and Confirmation feature.


Microsoft IT Team Site Life Cycle Management Beta 1.0 will be a community driven effort and managed under CodePlex source control.  Discussion boards and bug management will be also provided by the CodePlex workspace.  I look forward to your feedback!


To learn more about Microsoft IT Team Site Life Cycle Management Beta 1.0 visit http://www.codeplex.com/governance.


The SharePoint Governance workspace is intended provide governance and manageability samples and tools designed to help IT Professionals management SharePoint Products and Technologies deployments.  Upcoming tools include an implementation of site/web lifecycle management based on the Site Delete and Confirmation feature, the Microsoft IT Site Delete Capture 1.0 feature, and additional out-of-the-box functionality.  A sample auditing configuration solution deployment package will be introduced in the September-October timeframe that provides guidance on auditing and the management of content types across site collections.