SharePoint Connections Session Follow Up: Create an Internet-Facing SharePoint Site

During the Create an Internet-Facing SharePoint Site session I covered the following topics.

1. Setting up forms based authentication.
2. Creating your own custom Master Pages and style sheets
3. Personalizing site content for users.
4. Leveraging the Business Data Catalog to surface and interact with back end systems.
5. Each of these topics is broken down more in depth in this blog post. All the code used during my demos can be found in the rar file linked at the end of this post.
6. Setting up forms based authentication
7. Because a brief survey revealed almost the entire audience had already attended a session that covered setting up forms based authentication for SharePoint sites, I moved quickly through the materials related to setting up forms based authentication for SharePoint sites and did not spend a lot of time on it.

At any rate, here are the step by step instructions that describe how to set up a SharePoint site to use forms based authentication.

*Note: There are also several GREAT blog posts that describe how to set up FBA with SharePoint sites available on the Internet as well

Click here for detailed information.

SharePoint 2007 Performance Optimization

Recommendation: use x64 hardware on all servers to optimize page downloads by minimizing number of round trips between client computers and server computers.

Reason: With x64 you are not limited to 2 GB of Memory.

Benefit: Reduces network traffic and latency due to FE servers hitting the memory limit and needing to recycle.

Recommendation: apply Windows Server 2003 SP2

Reason: A list of all of the reasons can be found here: http://support.microsoft.com/kb/914962/en-us

Benefit: Optimizes the Front End server’s performance

Recommendation: Defragment the Front end Server’s drives

Reason: best practice for optimal disk performance

Benefit: Optimizes the Front End server’s performance

Recommendation: apply MOSS SP1 and post SP1 rollup fixes

Reason: The service packs addresses a lot of the known bugs that currently exist.

Benefit: Reduces network traffic and latency due to FE servers hitting the memory limit and needing to recycle.

Recommendation: Use a dedicated FE crawl target that is not in NLB (i.e. NLB is not installed)

Reason: By default, Office SharePoint Server 2007 uses all Web servers to crawl content in the server farm. When your server farm is configured to use all Web servers for crawling, the index server sends requests to each Web server in the farm.

Benefit: Crawling content in your farm places a heavy load on the Web servers. This tends to cause spikes and surges in network traffic, and is CPU-intensive and memory-intensive. By pointing to a dedicated FE server, users aren’t impacted negatively.

Recommendation: All server computers in the farm are on the same network segment. There is no switching in routers at the data layer. The network link between a Web server and the database server is less than 1 milliseconds (ms) latency and located 10 or fewer miles from the database server.

Reason: Routers and switches will increase latency even if the network connection between these is very fast. If the type of load the Web server is serving is some subset of user browse requests, we expect Office SharePoint Server 2007 to tolerate some latency between the Web server and the database server. On the other hand, pages with many or custom Web Parts, Stsadm commands, and search crawls are likely to fair less well.

Benefit: Reduces latency and provides a better experience for users in remote geographic locations

Recommendation: Do not use web gardens and BLOB cache at the same time

Reason: Because only one process can acquire the lock necessary to manage the cache, successful use the cache depends on which thread services a request. If a Web garden that does not have the BLOB cache lock services a request, the content it sends in response will not have caching directives associated with it.

Benefit: Reduces the amount of requests and data sent over the network by allowing BLOB Caching to work.

Recommendation: Optimize Blob Cache by including most used file extensions and increasing the cacheabilty value

Reason: The BLOB cache enables you to configure caching directives that are associated with items served from publishing site lists, for example, the Pages library and Site Collection Images. When the browser on the client computer encounters a caching directive, it detects that the item it is retrieving can be saved locally and does not need to be requested again until the caching directive expires. When the BLOB cache is turned on, a couple of different things happen. First, each time a cacheable item is requested, MOSS searches the hard disk drive of the Web server that received the request to see if a copy exists locally. If it does, the file is streamed directly from the local disk to the user. If it isn't on the local disk yet, a copy of the item is made from the SQL database where it is stored, and then the item is sent to the user making the request. From that point forward, all requests for the item can be served directly from the Web server until the item's cacheability value indicates that it has expired. The other thing it does is append a cacheability header to the item when the item is sent to the client. This header instructs the browser how long the item should be cached. For example, if a picture had a cacheability value of three days, the browser uses the copy of the image it has in its local cache if the picture is requested again within the next three days; it does not request it from the server again.

Benefit: That results in better performance in the server farm by reducing contention on the database server. In a geographically distributed environment, this is critically important because it reduces the number of items requested and sent over the network.

Please note that you need to ensure that the “Office Sharepoint Server Publishing” feature is activated on all Publishing sites. Also, you should update the location to the best performing drive, add in extra paths, and add in the max-age to expire based on your situation. For example:

Current setting: <BlobCache location="C:\blobcache" path="\.(gif|jpg|png|css|js)$ " maxSize="10" enabled="false"/>

Example Setting: <BlobCache location="E:\blobcache" path="\.(gif|jpg|png|css|js|htc|)$ " maxSize="10" max-age="86400" enabled="true"/>

Recommendation: Optimizing IIS Compression by changing the level of IIS compression from 0 to 9 and adding in certain dynamic files

Reason: When MOSS is installed, setup configures IIS to compress the static file types .htm, .html and .txt; it compresses the dynamic file types .asp and .exe. After a site has been hit by a few users, you can verify that compression is working by viewing the %WINDIR%\IIS Temporary Compressed Files directory on a Web server. It should contain multiple files, which indicates that static files have been requested and IIS has compressed a copy of them and stored them on the local drive. When that file is requested again, whether it's the same user or not, the compressed version of the file is served directly from this folder. Dynamic files can be compressed as well, but they are always compressed on the fly; copies are not kept on the local Web server. It may be advantageous to compress additional file types. For example, it probably makes sense to also compress the static file types .css and .js; it may also make sense to compress the dynamic file types .axd and .aspx.

Benefit: The IIS Compression can result in significant bandwidth savings. For example, the core.js file is included on every SharePoint page. When it's uncompressed, it is 257 KB; after compression, the file is only 54 KB without performing additional tuning to IIS compression.

Please note: Before applying IIS Compression, you will need to ensure that you only include files that are well-suited to being compressed. For example, .jpg files are not a good candidate for compression because the file format is inherently compressed already. Also, 2007 Microsoft Office system file types such as docx, xlsx and pptx are not a good choice for compression because the files are not served directly from the server; instead, they are routed through the different ISAPI filters that are used to manage the rich integrated end user experience for Microsoft Office content. In addition, in the 2007 Microsoft Office system, these file types are inherently compressed.

Ref: http://technet.microsoft.com/en-us/library/cc263099(office.12).aspx

and

http://blogs.technet.com/b/waynemo/archive/2008/03/19/moss-server-performance-considerations.aspx

MOSS Architecture and SSP

MOSS adopts a typical 3-tier model with web servers at the front, application servers in the middle and a database server at the back where all the data and config is stored.

The front-end web servers are simple web servers that can be network load balanced to achieve additional performance and fault tolerance (like 2003). The backend database is a typical SQL 2005 database service and can be clustered as you’d expect.

So what is different?

The interesting bit is what goes on in the middle. With MOSS, it is mandatory to have a Shared Service Provider. This is a collection of application servers that provide shared services out to any portals or sites that need them. These services include:

• Search
• Index
• Audience compilation
• User profiles database
• My Sites
• Business Data Catalogue
• Excel Services

Any of the above services can exist on any number of servers within the SSP. For example, for a small-ish deployment you could have them all on one box, or you could scale them out onto different boxes as you see fit. There are no rules that govern where these services reside within the SSP or how many servers you have servicing them (with the exception of the Index server - only one per SSP). For example if you want 10 search servers – got for it ... “fill your boots” as we’d say here in England!

This model is very similar to the 2003 approach with one crucial difference,  the SSP has no portal affiliation. This means that you are not forced to have a specific parent/child style portal topology in order to use Shared Services which was one of the big sticking points with Sharepoint 2003 shared services.

So what does the ‘Portal’ actually do now?

For starters, it is now called ‘Collaboration Portal', not ‘portal’ and it is just another type of site collection that is hosted on an IIS site (called web applications in MOSS). Portals no longer contain any application services such as search, my site etc – all these services now have to come from an SSP. This means that all you need to host a portal is a web server and a place to put the content database.

Different portals can live on their own hardware which is completely isolated from any other hardware other than the fact that it consumes services from a centrally managed SSP. Alternatively, you can put some of your portals on shared hardware and some on dedicated.

I always hear requirements around different business units wanting their own portal which has its own visual style, custom webparts, different SLAs etc . With this model, there does not have to be any affiliation between any portals or sites if it is not required.

This diagram outlines the logic of how SSP provide services that are consumed by several site collections:

So does this mean that ‘Supported Farm configurations’ have gone?

In short, yes!

This model means that you can scale any element of the system out as you see fit. However, there are several recommended server layouts that will meet most scenarios. These broadly follow the small, medium and large models of Sharepoint 2003, but should be considered as starting points only. You can easily deviate to a different setup depending on your requirements.

So how does all this map to physical servers?

All three tiers (web, application, database) of the SharePoint model can be hosted on a single machine or scaled out to a huge collection of servers to meet the requirements.

Most organisations will want some level of fault tolerance and separation between server roles. Typically these kind of organisations will have at least 2 web servers running your portals and sites, at least one application server hosting all services (maybe a second for fault tolerance) and one database cluster. This diagram shows this model:

Larger organisations may want to have separate web servers for each of their portals, sites and my sites. They may also have multiple application servers as part of the SSP. This diagram describes this model:

I hope this was usefull.

Power of Excel, BI.

What is BI?

Business intelligence (BI) is the process of aggregating, storing, analyzing, and then reporting that data in order to make decisions and future forecasting, current business state, Risk involve, predict,manage business performance and take business decisions.

Tools Selection for BI.

In current market there are lots of BI tools available for analysis. While selecting BI tools it is advised that to select the tool which can interact with all the versions of Microsoft Excel effectively and also should be able to interact with other MS Office products. 

Why excel support is required in BI Tools?

Lots of BI vendors say that "Our Bi Product offer analysis". But if you check in the market there
are lots of BI vendors who have changed their tool to support Excel or use the power of Excel in analysis and reporting, like Cognos, which unveiled Go! Office; Business Objects, which introduced Live Office; and Information Builders, which recently introduced Quick Data for WebFocus. Excel is used widely because Excel has lots of in built spreadsheet functions, analytical features, and universal presence on desktop.

The current king of Business Intelligent (BI) tools is Microsoft Excel. Microsoft states that there
are over 150 million excel users, with many of them using Excel for BI-reporting and analysis of corporate data.

The spreadsheet program that some experts have called “KING OF BI”, “Number One BI
Tool” and I Say “Heart of BI” because without excel its very difficult to get BI reports and analysis. Excel is more widely used for planning purposes. To prepare plans, one should have to starts from actuals, and that actuals comes from BI Report. In the absence of a planning system, there is not better place to enter plan data than in excel.It's perfect for entering data, doing what-if-analysis or producing forecasts based on formulas, thanks to its analytical features and its universal presence on desktop.

Also in the perspective of the software development or IT professionals rarely prefer the Excel as BI Tool but BI reports are very dynamic and the tool which is developed for the BI should be very flexible which can adopt the change immediately and produce report.

Frequent changes in Business Logic and tracking Metrics require the power of the change
at end-user. Most of the time the changes require is very simple but critical from business perspective. In case of BI tools the end user/ management have to take help of the IT/ BI specialist developer for customization, this involves to go through whole SDLC, Find sponsor for project, request for requirement change then management will assign project to one development team, and then after some period of time the reports added to existing tool or new tool is developed on the basis of the requirement. The whole process for additional changes will
become very long for Business. However if Excel is used as BI, Excel is the Smart tool which allows users to change reports in a few minutes or hours and which is the most important feature for the BI Tool that enable users to rapidly develop and maintain dashboards, including dynamic, interactive data capabilities such as drill-down, filter, and sort. Second thing is that customization can be done by the User him self, it doesn’t required any programmer. If user is aware of some
coding (VBA) skills then he can do automation of the report and reduce manual input work.
With Excel BI User can analyze and report data from any number of sources on one page.

No other BI system provides such analytical power.
Excel 2007 is the new version of Excel with more analytical and reporting features. It provides
more spontaneous access to OLAP data, conditional formatting based on the KPI. Excel 2007 is more powerful because of the feature of integration with Microsoft SQL Server 2005 Analysis Services to provide a rich end-user analysis, newly designed PivotTable reports, a new formula-based model for accessing Analysis Services data, improved sorting and filtering capabilities, and a completely redesigned visualization engine. Bi user can share Excel workbook to browser based environment using SharePoint Excel services. It can fully interactive, data-bound workbooks that include charts, tables, and PivotTable reports as parts of a portal, dashboard, or business scorecard, without requiring any help from IT or development team, and that present a "single version of the truth."

Alex Payne, group product manager, Microsoft office Business Applications, says “We have different approach when we say BI features are just part of Excel.” It is not separate add-on that you pay for. We are providing BI natively inside Excel.” 

Excel Services for BI 

It is great to use excel to create BI Reports and analysis, but if you give this copy of excel which
contains report to many people and in future if process or functionality needs to be changed,then its very difficult to redistribute the new version of Excel report to the whole user group. “One version of truth”…. Where excel based BI system fails.There is also a chance of exposing the business proprietary formulas to people or user group who are not meant to see it. The problem we all face is “HOW DO YOU GET THIS DATA PUBLISHED SO OTHERS CAN USE AND INTERACT WITH THE DATA WHILE KEEPING PROPRIETARY INFORMATION HIDDEN.” Answer is Excel services.

Excel Services is a new server technology that was designed from the ground up to be scalable and robust with web based interface which uses Excel Web Access which is created by using client application Excel 2007. Workbook authors no longer need to send them in e-mail messages. Sending workbooks in e-mail messages often results in multiple copies of a workbook, essentially creating conflicting the "single version of truth" and exposing proprietary formulas and logic.

Another advantage is in a multi user scenario, each user has own web session. The server loads the workbook in memory according to the user permission of access read-only, (or targeted
user group defined by author).If a user applies a filter to a workbook, other users are not affected by those changes, and their interactions do not modify the original file.

To create a PivotTable report, user can interact with Microsoft SQL server analysis services data, select the cubes, fields.The new cube functions added to the Excel 2007 and Excel Services which add on to the business intelligence. Using that You can convert PivotTables directly into cube functions which enables general users to rapidly model data when needed, to support business decisions.

With the Excel 2007 business user can ascertain how to respond change needs without the involvement of the IT or Programmer. Using cubes Users can pull in data from SQL server analysis services and various other data sources, which greatly increases the flexibility to combine external data points and create reports and analyse the data effectively.

In Excel 2007, a user has full control over the content, whereas in Excel Services, the workbook author can control the level of functionality that is available to the workbook consumer, in addition to controlling the visibility of formulas and the ability to edit data values.

Following snap of Excel 2007 shows the Pivot table with different types of the KPI presented in various ways from cube data.  

Bringing Data Together in Dashboards

You can use the new features in Excel 2007 and Excel Services to access, manipulate, and display data in new and different ways. When data is ready for publication, information workers can combine their data with other data in dashboards. Dashboards enable visualization and reporting on large amounts of data in an easily consumable format. Excel Services and SharePoint Server 2007 have the ability to create business dashboards is a key business intelligence capability. Below figure shows a dashboard that was created by using Excel Services, KPIs, and filtered Web Parts from Office SharePoint Server 2007. It focuses on identifying trends, anticipating challenges and opportunities, and supporting business decisions.

Dashboard-using Excel Services

No code is required to create dashboards. SharePoint Server 2007 includes a set of Web Parts that enable users to develop and maintain dashboards, including dynamic, interactive data capabilities such as drill-down, filter, and sort with familiarities with Excel by using PivotTables reports, Excel charts, and formulas. Dashboards can also take advantage of SharePoint Server 2007 KPI web parts which are external to Excel, which are highly visual representations of important business factors.

One big limitation with excel services is the inability to utilize the excel object model and a spreadsheet with the VBA code behind cannot be published to Excel Server.

SQL server reporting-services Integrated with Windows SharePoint Services v3 to enable publishing, viewing and management of reports.

In SharePoint, the data for KPIs can come from any SharePoint list, an excel workbook, SQL server analysis services or it can be manually entered.

The data is exposed using a KPI list and KPI appropriate data. The KPI web parts then expose that data in to SharePoint site.

Conclusion
Excel Services enables end users to create robust solutions without involving developers, and it  gives workbook authors, database administrators, and developer’s greater control over the environment. The combination of the Excel Web Access and the Excel Web Services API provides rich and versatile access to workbooks. At the same time, the security model is the same whether you access the workbook through Excel Web Access or programmatically through the Excel Web Services interface.

Workbook authors use Excel 2007 to create workbooks and accompanying business logic, including connecting directly to back-end data sources for live data. Those authors, including non-technical users, can create workbooks, reports, and dashboards with dynamic data, more securely sharing that information across the enterprise and making it available to other users and applications. Developers can take advantage of business logic created by those business users without needing to re-create it in application code. The physical infrastructure that underlies Excel Services is highly scalable and configurable. This enables you to support changing business needs as they arise and provide guaranteed availability for mission-critical calculations.

The combination of Excel Services and Excel 2007 enables both technical and non-technical users to securely share, manage, and reuse data and business logic, creating a true enterprise solution for workbooks.

WSP Builder: ERROR: Could not load file or assembly 'CabLib, Version=6.9.26.0, Culture=neutral, PublicKeyToken=85376ef9a48d191a' or one of its dependencies. An attempt was made to load a program with an incorrect format.

PROBLEM:

I'm running the latest release of WSPBuilder and extnsions for Visual Studio 2008. I installed the MSI. I am running on a Windows Server 2003 system with SharePoint on it.

When I was trying to build the WSP then I was getting below error:

"Could not load file or assembly 'CabLib, Version=6.9.26.0, Culture=neutral, PublicKeyToken=85376ef9a48d191a' or one of its dependencies. An attempt was made to load a program with an incorrect format."

I searched for cablib.dll in the WSPBuilder folder, found two versions of cablib.dll one is marked as x64 and x86.

Solution:

I copied the cablib.dll from the "C:\Program Files\WSPTools\WSPBuilderExtensions\Resources\x86"

directory and paste it in the root "C:\Program Files\WSPTools\WSPBuilderExtensions\"

Then everything works fine.

Use the ‘Comments’ form to share your thoughts.

SharePoint Capacity Planning

SharePoint Capacity Planning There is no hard limit enforced by the system, the most useful parementes for the capacity design are listed in the table below.  The capacity planning for MOSS 2007 from Microsoft can be found at http://technet2.microsoft.com/Office/en-us/library/9994b57f-fef8-44e7-9bf9-ca620ce207341033.mspx?mfr=true and is summarized here Object Scope Guideline for optimum performance Comment Site collections Database 50,000 Total throughput degrades as the number of site collections increases. Web sites Web site 2,000 The interface for enumerating subsites of a given Web site does not perform well much beyond 2,000 subsites. Web sites Site collection 250,000 You can create a very large total number of Web sites by nesting the subsites. For example, 100 sites each with 1000 subsites is 100,100 Web sites. Documents Folder 2,000 The interfaces for enumerating documents in a folder do not perform well beyond a thousand entries. Documents Library 2 million You can create very large document libraries by nesting folders. Security principals Web site 2,000 The size of the access control list is limited to a few thousand security principals, in other words users and groups in the Web site. Users Web site 2 million You can add millions of people to your Web site by using Microsoft Windows security groups to manage security instead of using individual users. Items List 2,000 The interface for enumerating list items does not perform well beyond a few thousand items. Web Parts Page 100 Pages with more than 100 Web Parts are slow to render. Web Part personalization Page 10,000 Pages with more than a few thousand user personalizations are slow to render. Lists Web site 2,000 The interface for enumerating lists and libraries in a Web site does not perform well beyond a few thousand entries. Document size File 50 MB The file save performance degrades as the file size grows. The default maximum is 50 MB. This maximum is enforced by the system, but you can change it to any value up to 2 GB (2047 MB) if you have applied Windows SharePoint Services Service Pack 1. Object Guidelines for acceptable performance Notes Scope of impact Site collections per Web application 50,000 Total farm throughput degrades as the number of site collections increases. Farm Web sites per site collection 250,000 You can create a very large total number of Web sites by nesting the subsites. For example, 100 sites each with 1000 subsites is 100,000 Web sites. Site collection Subsites per Web site 2,000 The interface for enumerating subsites of a given Web site does not perform well as the number of subsites surpasses 2,000. Site view Documents per library 5 million You can create very large document libraries by nesting folders, using standard views and site hierarchy. This value may vary depending on how documents and folders are organized, and by the type and size of documents stored. Library Items per container 2,000 Testing indicates a reduction in performance beyond two thousand items. Using indexing on a flat folder view can improve performance. List view Document file size 50MB (2GB max) File save performance is proportional to the size of the file. The default maximum is 50 MB. This maximum is enforced by the system, but you can change it to any value up to 2 GB (2047 MB) if you have applied Windows SharePoint Services Service Pack 1. For more information, see Configuring large file support in Installing and Using Service Packs for Windows SharePoint Services. Library, file save performance Lists per web site 2,000 Testing indicates a reduction in list view performance beyond two thousand entries. List view Field types per list 256 This is not a hard limit, but you may experience list view performance degradation as the number of field types in a list increases. List view Web parts per page 100 This figure is an estimate based on simple web parts. The complexity of the web parts dictates how many web parts can be used on a page before performance is affected. Page Web Part personalization per page 10,000 Testing indicates a reduction in performance beyond a few thousand user personalizations. Page Features per Web application 250 As the number of activated features per Web application increases, so does the memory usage of the w3wp process. This value is an estimate; your environment may support more or fewer activated features for a given Web application. Farm People objects Object Guidelines for acceptable performance Notes Users in groups per web site 2 million You can add millions of people to your Web site by using Microsoft Windows security groups to manage security instead of using individual users. Security principals per web site 2,000 The size of the access control list is limited to a few thousand security principals, in other words users and groups in the Web site. User profiles per farm 5,000,000     Personalization and Search objects Object Guidelines for acceptable performance Notes Search indexes 1 per SSP*   Content sources 250   Search scopes 250   Best bets 25,000   Audiences 10,000   Audience memberships 5,000,000   Indexed documents per content index 5,000,000   Indexed documents 20,000,000   Thesaurus entries 10,000   Alerts 1,000,000    Logical architecture objects Object Guidelines for acceptable performance Notes Shared service providers 3 (20 max)   IIS app pools 8 Maximum number is determined by hardware capabilities. Web applications 4 Maximum number is determined by hardware capabilities. Web applications per SSP 99   Site collections per Web application 50,000   Zones per farm 5* The number zones defined for a farm is hard coded to 5. Content databases per Web application 100   Site collections per database 50,000   Physical objects Object Guidelines for acceptable performance Notes Index server 1 per SSP*   Excel calculation server No limit   Search server No limit   Query server 1 per database*   WFE servers per database server 8*   WFEs per Domain Controller 3 Depending on how much authentication traffic is generated, your environment may support a greater number of WFEs per domain controller. Level Performance limits Site collection 2,000 subsites of any site is the recommended limit. The same content database is used for an entire site collection. This may affect performance in operations such as backup and restore. Site 2,000 libraries and lists is the recommended limit. Library 10,000,000 documents is the recommended limit. 2,000 items per view is the recommended limit. Folder 2,000 items per folder is the recommended limit