Find closed web parts all over your portal

For most users, it's very common to close, instead delete a web part from pages, especially the landing page of a site. As an administrator, you can go to each page and open web part maintenance page to check and removed closed ones.

However, how do we find out which site has the most and deal with them first if you have hundred of sites to manage? You can use a SQL query to geerate a list. (MIcrosoft said do not touch your database, so, this is just for your own reference and risk).

select ''+dirname+'/'+ leafname+'?contents=1'
from alldocs inner join webparts on
Alldocs.id=webparts.tp_pageurlid
where tp_isincluded=0

Find user permissions assigned in one or more site collections of all items

MOSS does not provide out-of-box interface to view assigned permissions to the same user cross site collections. Although there are tools to help you, no matter it's paid or free, if your environment does not allow free tools and you have to find it for now, what can you do?

Since everything is stored in SQL database, why not write some SQL statements to find them? That's what I have been doing before we have a nice tool to use. If you already know the structure of tables used by MOSS, then you may be able to do it in a short period of time.

If not, here is the SQL query you can use to do it:

Select 'http://yourportal/'+a.scopeurl as ObjectURL, a.title as UserID,roles.title as Permission,a.SPgroup, a.ADLogin
from roles inner join
(
select perms.scopeid, perms.scopeurl, perms.roledefwebid, alluser.title, alluser.ADLogin,alluser.SPgroup,roleassignment.roleid, roleassignment.siteid
from perms inner join
roleassignment on
perms.siteid=roleassignment.siteid
and
perms.scopeid=roleassignment.scopeid
inner join(
(
select groups.siteid, groups.id as id,userinfo.tp_login as ADLogin,userinfo.tp_title as title, groups.title as SPGroup
from groups inner join
groupmembership on groups.siteid=groupmembership.siteid
and
groups.id=groupmembership.groupid
inner join userinfo on
groupmembership.siteid=userinfo.tp_siteid
and
groupmembership.memberid=userinfo.tp_id)

union

select tp_siteid as siteid, tp_id as id,userinfo.tp_login as ADLogin, tp_title as title, 'no' as SPGroup from userinfo) as Alluser
on
roleassignment.siteid=alluser.siteid
and
roleassignment.principalid=alluser.id) as a
on
roles.siteid=a.siteid
and
roles.webid=a.roledefwebid
and
roles.roleid=a.roleid

Let me explain the columns you get:

1. frist colunmn is the whole URL of an object (site, , sub site, library/list, folder oritem) which has unique permission
2. The User ID
3. Permission assigned to this user.
4. SharePoint User Group this user is assigned to. If the permission assigned to this user individually, this column shows No.
5. User log in ID.

So, this query will tell you in a single content database, what are those objects have unique permissions, who has permission, what type permission assigned, and this permission assigned to a user group or individually.

Enjoy it.

Work Around STSADM Import/Export Failure

We have been using STSADM Import/Export features to reorganize the entire MOSS content structure. Sometimes we saw fatal or too many error during importing process, especially when we worked on site collections with large number or size of contents.

After couple tries, we found that if we choose not to compress the exported files, it really helped to draw down the chance to have errors.

If you experienced similar situation, try this work around and let me know if it really helps you.


Enjoy.

MOSS and Reporting Services Integrated with NTLM: is it possible?

Can you integrated SQL Services Reporting Services into your MOSS farm with NTLM only?

If you search around, lots of articles do not tell you exactly which one, NTLM or Kerberos, to use. Others may tell you that you have to use Kerberos and it means you have to go through lots of troubles to just get Kerberos done. Of course, if you have someone every good with this and can help you without waiting tooo long, then you do not have to read the rest of this article.

I have been looking for this information for a year and half and finally I have the answer recently: Yes, you can use NTLM to integrate these two application together.

There are two steps never documented in any MOSS and SSRS integration information and a large portion of them just assumed you have Kerberos in place.

First, the SSRS web site has to use NTLM authentication in IIS. You can check this KB article to find out whether SSRS is using mixing or NTLM. Run the cscript adsutil.vbs set command to set it to NTLM.

Second, in the Manage integration settings page of Central Administration site, you can only use Trusted instead of Windows authentication.

With these undocumented settings, we are able to design and run reports from a MOSS site now.

Hopefully this information is able to help you if it's not too late.

Best Practice Conference August 24-26, 2009

Best Pracice Conference is back to Washignton DC this Aughst. This time it's for SharePoint and SQL Server.

You can find details here.

MOSS and High CPU Usage - a rare case

Earlier this week in the afternoon we noticed that the production MOSS was running very slow. Our team found out the CPU usage of the WFE server was reaching 100% constantly. The WFE had 8 cores with 4 GB memory and hard to believe it's normal.

I was on the way home and driving, so I could only help with my mouth bfore I could open my computer and connect to the server. We reset IIS couple times and the same situation would come back after 10 to 15 minutes. We could not reboot at the time due to the users from the west still using it.

So the next thing we did was to find out what may cause this CPU spike. When we traced the time line back, the major difference was that one site collection was restored with DocAve earlier in the afternoon. We decided to narrow down the range by restoring the same site collection to our development environment.

Then the same situation happened in the development sever and we knew that's the source. But why? By going through some emails and conversation with different users, we thought it has something to do with InfoPath form. But we still did not have anything clear.

In order to put production MOSS back to normal status, we communicated to the site owners with the situation and our work around solutions: backed up the site collection and removed it from the system until we know how to open it again without problem. Very soon we had approval from the owner and proceeded.

We backed up the site collection, removed it from production, reset IIS and restarted coupld services. Everything looked fine and CPU usage staied low for 30 to 40 minutes. We knew the crisis in production was over and we can concerated on finding the answers.

Our techinical lead took another site collection which had InfoPath from and restored it with DocAve to development enevironment. The same situation came back right away. Now we knew something wrong with DocAve and InfoPath form.

We sent our finding and a sample to the vendor and they could reproduce the same problem in their lab. So, DocAve can not deal with site which has InfoPath form?

Not really. This is a very rare case: in order to have this problem, you have to have a InfoPath 2003 form template saved in a library which was migrated from WSS 2 (not sure the same would happen to 2003 Portal). When you restore the site with DocAve, you will have the same problem. However, if you restore the entire site collection with STSADM, no problem at all.

We received a patch from the vendor and tested it. So far it looks good. So, case closed.

STSADM Export/Import Problems

In the past 1 1/2 month, our team is working on to re-organize the portal site structure. Before we migrated last December, there were more than 150 site collections and more than 700 sub sites. In our plan, we are going to put them into one portal and 9 departmental site collections. From 150 plus to 10 site collections, I believe this is a big efforts to move 85 GB database around multiple databases.

The first tool we consider is, of course, STSADM. This is the out-of-box tool from Microsoft, support by Microsoft and what can go wrong, right?

Actually, lots things can go wrong and so far I haven't found lots information from on line sources. So far the problems we have seen include:

1. Not all document versions are imported correctly. I did some research through database and so far I have one clue. There is a column called DocFlag in both AllDocs and AllDocVersions tables. If any record in AlldocVersions has a different DocFlag values than the current version, import process skipped this one.
2. Sometimes all of the versions are replaced by the current one, which means the date and author are also replaced.
3. Import operation can not process the Project Tracking list properly and I have no clue.
4. Sometimes not all sub sites, lists, libraries or items are not imported and I have no clue how to predicted. If I redo import or export/import, I can get everything correctly.
5. The import log does not show you all information. For example, you can find each version of a document is exported from the log, the same information is missing from the import log.
6. Do I mention all of these problems are hard to predicted? I have no idea if I wrong export/import to the same site collection multiple time, when do I get and which problems.
7. The biggest one and also no way to predict it: sometime when you import from an site collection to a group of sub sites, the original top-level site does not import to the correct sub site. The whole contents go to the top-level site of the new URL and overwrite the one. I have no clue or information why and how it happened.

After very intensive testing and evaluation, we decided not to use STSADM as the tool to do this re-organization. One major concern is we have no way to predict when and where the problems will strike us.

If you have any way to resolve the problems I just mentioned, I am all ears. Thanks in advanced.

Where to find the thrid site collection administrator?

When either the server or site collection administrator (depends on you have self-service turn on or not) creates a new site collection, the administrator has two field to enter at least the primary site collection administrator. This information is saved in the OwnerID and SecondaryContactID.

After the site collection is created, the site collection administrator is able to remove, change or add more than two site collection administrators to the list. If you remove a name from the list, MOSS will remove it from one of the column. When you add the same name back, or add the 2nd administrator to the list, you will not see the column updated by the system.

On the other hand, since there are just two columns, where the setting is saved if you add another administrator?

This is actually what happened when you make changes to the site collection administrator list:

1. When you add up to two names to the fields during site collection creation, both names are saved in the Sites table.
2. When you remove anyone from the list after the site collection is created, MOSS will remove it from the Sites table.
3. If you add a new name, no matter this is the 2nd , 3rd or more, MOSS will not update the Sites table.
4. All the changes made to the site collection administrator list go to UserInfo table. There is a column called SiteAdmin and this is the place you can do your search.

DocAve 5 needs more and better QA

We are using DocAve 4 to backup WSS 2 environment for about a year. It's not perfect and the interface sometimes corky, according to one of my team member, but it does the backup and restore jobs well.

We migrated from WSS 2 to MOSS 2007 early last month and star using DocAve 5 as our main disaster and recovery solutions and to our surprise, there are bugs or features do not look hard to identify and fix. Especially DocAve 5 has been out there for more than a year.

For example, in the Content Manager screen when you want to move site or site collections from one URL to another, pretty much like using STSADM Export and Import commands. In the tree view, there is a green icon next to the designated site and you can change it to point up, down or level. What is this for? We did not find any explanation in the user guide. When we had conference with AvePoint, there own people does not have good idea how and when to use it.

When we tried to move site collection during the conference call with their support, the end results looked pretty bad and they admitted they had to go back to do more testing.

Another example is also related to move site to new URL. If use DocAve's feature, any inactive user accounts do not import to the new site with the contents and they name changed to System Account. This looks like the using STSADM to do exporting and importing without using the -includeusersecurity parameter. However, I can choose to include user security or not but can not find any place in DocAve to choose it.

AvePoint's response was this is by design and it's not a bug. But they will consider to add a check box or something to provide different options.

If you use DocAve before, you know in the job status screen, the bar to show the progress may stop at certain percentage for while, then moves very fast to another number and stops again. In this version, it still acts like this and we can see it stops at 99% for a long time. So the progress bar does not give the users the real progress since from 99% to finish takes longer than moving from 5% to 71%. Then why not making it more realistic to the real progress? Especially this is pretty much the same behavior from the previous version.

The user guide is huge with lots of do this, click here type of instruction. However, for a user does not use DocAve before, it does not tell me why and when to use a feature or a particular option. List all commands and features do not give users a good idea of what they should do with different environments and circumstances.

I think the biggest surprise we had is: DocAve can not performance incremental or differential backup well. When we tested it in a development environment with migrated data, the daily differential backup process generated backed up file size around 40 to 50% of a full backup. However, it was a development and only 5 people to use it and not much changes daily. After couple long talks, AvePoint gave us a hot fix to deal with this problem. They said that because we had migrated sites and some of the URLs did not match the new server farm's URL and DocAve can not handle those contents.

Are we the first and few users with migrated contents and new URLs in MOSS 2007? I don't think so. Then how come this issue never raised any attention?

Anyway, I know DocAve has pretty good reputation to the SharePoint backup and restore solutions. However, this new version also wants to provide migration and administration functions which would be very helpful.. However, we felt that the product needs better and more quality control plus more information to how, why and when to use them.

Integrate Project Server with MOSS and SSP

If you are going to integrate Project Server 2007 with MOSS, you may want to use a seperated Shared Services Provider to host it.

During our pre-migration testing, we setup an integrated Project Server 2007 and move databases from the pilot environment. Couple days later the entire project server crashed and we had no clue why it happened.

After we discussed the whole situation with Microsoft, we were told die to the nature of Project Server, we should use a separated SSP to host.

Since we already created a SSP for the entire MOSS farm, the process included:

• Create two new SSPs.
• Move portal, mysite, search and indexing to one of the new SSPs.
• Create PWA under the 2nd new SSPs.
• Other than hosting PWA, do not use the 2nd SSP to do profile importing, content crawling or any other services if it's not required.

We implemented suggested settings to our server farm and so far so good. If you plan to do so, I will recommend to find a development/staging environment and document all the steps to create these two SSPs for your MOSS environment.

Move on to a new position

Since late July I was moved to (long story) an external government contract. I was kind getting the messages and started looking around.

Fortunately, I received a good offer to work on over all MOSS and Project Server architect, deployment and development. So I moved on to the new one starting Thanksgiving week. Pretty exciting and looking forward to learn lots new knowledge.

Happy Thanksgiving.

Access is denied when I schedule content crawling

I worked on the search crawling schedule in a server farm and received an error message like:

Access is denied (Exception from HRESULT:....)

However, I was able to start a manual content crawling without difficulties. So what happened?

Microsoft has a KB article to explain this situation and the link is:
http://support.microsoft.com/kb/926959. I was glad to find out a solution so quick. But then I wondered, which server I should apply this change to?

Since the environment I deployed has separated front-ends, index and query servers and this article and other web site did not give me clear direction.

After I analyzed the situation and found out I should apply this to the index server, since index server is the one performing this scheduled task.

I followed the KB article and made changes to the index server, everything worked as it should be.

Perhaps next time when we publish some thoughts or instructions and we are using a server farm, includes which server we apply changes to fix the problems may be very help.

Just a thought.

SharePoint Best Practice Conference - washington DC

If you knew the new SharePoint Best Practice book by Ben and Bills, this is kind like a extension to the book and concepts.

There are lots talks regarding how great SharePoint can do technology wise, how about the business needs? How about a good start to deploy it? What do we, as a technical person to work on this product, miss and could learn from other experts?

Here is the web site for more details. Looking forward to hear any opinions.

Deployed a Large Server Farm Today

Today is a good day to me since I worked with our client to finish 2 days server farm deployment.

We have 8 servers in this farm: two web front end, one index server, one query server, one project server, one Excel service server and two SQL clustered server.

I joked to my client that I will bump my head to the wall three times since I misunderstood that we have to use Kerberos to deploy a farm like this. So today's reault gave me one good and one bad thing:

1. It approved that I was wrong and I learned from it with well documented steps.

2. I have to find out why my previous test deployment failed with authentication errors,which definitely has nothing to do with Kerberos.

I will put my experience here since I believe some points should be cleared.

This is definitely a good feeling to enjoy the whole weekend after some unpleasant issues, especially I learned more today.

Infrastructure Update for Microsoft Office Servers

Microsoft recently released a new update targeting Office Servers, including MOSS and WSS 3.

One of the feeture I was looking for answers was Federated Search. In the description of this update, Microsoft added this functionality to MOSS's search features. This makes me pretty exciting to know and want to try ASAP.

The details of the update can be found here:
Install the Infrastructure Update for Microsoft Office Servers (Office SharePoint Server 2007)

Description of the Microsoft Office Servers Infrastructure Update: July 15, 2008

Issues that are fixed in Microsoft Office Servers by the Microsoft Office Servers Infrastructure Update

I will add more infromation and reactions from the community later.

Have fun.

Book: SharePoint 2007 Best Pracrice

This is the latest book from Bill English. Click here to find more details on Amazon.

Microsoft SharePoint Developer Site

Microsoft just announced their latest SharePoint development resource web site: Microsoft SharePoint Developer Site.

I haven't really explore the whole site but you will be able to find vidoes, whait papaers, demo and other things.

Enjoy the site.

How to Move Customized Workflow Between Sites

If you have been using SharePoint Designer 2007 to develop customized workflow, you may already know the official documentations told us we could not re-deploy it to another site. If you are working in a development environment, it would be pretty hard to recreate the same workflow with lots If loops and conditions.

So, what can you do? I will show you how to move your workflow from site to site or, server to server.

After you complete workflow development and testing and ready to move, go to the list setting pages and save this list as a template.

In the save page, remember to save it with contents. Yes, workflow is part of the contents and can be saved into the template file.

Move the template to the site or sever you like to use. Create the list from the template to the new site.

If your design includes other lists to save history or interact with this new list, remember to create them to the same new site.

Once you are ready with all the creation, open the site with SharePoint Designer. Open the workflow and check the list name is shown correctly or you only see the GUID number.

This extra step is especially important when you create the new list with a different name. Make changes to the list name and update the workflow.

Now you should be able to use the workflow now.

Enjoy it.

Good Article About How to Imstall MOSS SP1

SP1 for MOSS and WSS have been out for a while. I recently implemented to a virtual environment very close to the production system and under testing. Microsoft released lots information for any of you out there to read (after you finish all the SharePoint blogs, tips, technical info and codes).

If you do not have time to finish the documents and you have a simple environment, then Shane Yang published a very clear article to guide you through the installation. Read it before you start and you should be fine.

Remember, you have to install SP1 for WSS first, do not run configuration wizard and the install SP1 for MOSS. I heard some one only installed MOSS SP1 and had to re-build the virtual server. So, read it before you start.

Enjoy it.

Steps to Configure MOSS 2007 Server Farm

Jose Barreto wrote a good and simple list of steps to perform basic configuration of MOSS server farm. If you need something as quick reference, his article may be very handy sometime.

His article is here.