Thursday, January 27, 2011

SharePoint PrerequisiteInstaller log file location

After running the PrerequisiteInstaller you sometimes want to check the log file. At first it wasn't obvious to me where this would be stored. However, with a bit of searching I found out that it's simply stored in the %temp% directory. (duh!)

So just cd %temp% and look for the PrerequisiteInstaller.*.log files.

Sunday, January 23, 2011

Moving the User Profile Service Application Synchronization Database

So, the problems with my Sync DB as I mentioned in a previous post finally caught up to me. Running low on disk space, we decided to add another block of disk space to the server and move the Sync DB over there.

Adding the volume on Amazon EC2 was very straightforward. It was just a matter creating a new volume, attaching it to the server, enabling and then formatting the drive. This is covered quite nicely in Attaching an EBS Volume to EC2.

Now with the drive ready, I needed to move the DB files over. The instructions for Moving User Databases are pretty straightforward. However, I ran into some hiccups - I had open connections to the database (from when I was troubleshooting the database size :-) ) and the command to take the database offline simply waits until those connections are closed and you're none the wiser. Also, the new files / locations need to have the right permissions otherwise you can't bring the database back online. So, here are my updated steps.

  1. Close all connections to your database. You can check for open connections by executing an sp_who2
  2. Take the database offline by running the following:
    ALTER DATABASE [Sync DB] SET OFFLINE
  3. Move the files to the new location
  4. Update the new location in the system catalog as follows (make sure you use your own location!)
    ALTER DATABASE [Sync DB] MODIFY FILE ( NAME = 'Sync DB', FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Sync DB.mdf' );
    ALTER DATABASE [Sync DB] MODIFY FILE ( NAME = 'Sync DB_log', FILENAME = 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Sync DB_log.LDF' );
  5. Make sure that the database user has the correct permissions to the database files. I just copied the original settings which is giving the SQLServerMSSQLUser group full control access to the DATA folder (and the new database files). 
  6. Bring the database back online by running the following:
    ALTER DATABASE [Sync DB] SET ONLINE
  7. Verify the file change by running the following:
    SELECT  name, physical_name FROM sys.master_files WHERE sys.master_files.database_id = DB_ID(N'Sync DB')
I still haven't gotten to the root cause of the database file being so large. However, I just needed to buy some time for now.


PowerTab

The tab autocompletion in Powershell has, by default, the same annoying behaviour as cmd. Fortunately, Powershell has a TabExtension function. The nice folks at PowerTab have written a richer tab extension similar to the intellisense found in Visual Studio.

I just grab the latest copy of the module and put it into C:\Windows\System32\WindowsPowerShell\v1.0\Modules. This way it always loads for every user. In the first startup I just took the default settings in the configuration wizard. It took a couple of minutes to build and that was it.

Friday, January 21, 2011

User Profile Service Application Synchronization DB size

I have a SharePoint instance that is running out of disk space. Doing some poking around, I found that the User Profile Service Application Sync_DB has grown to almost 7GB. So, I ran the following SQL query to get more insight into what is going on:
exec sp_MSforeachtable @command1="EXEC sp_spaceused '?'"
Looking through the results I see that the culprits are as follows:

namerowsreserveddataindex_sizeunused
ObjectsInternal31566182089728 KB800296 KB1288776 KB656 KB
InstanceData1779974698144 KB4697048 KB296 KB800 KB

I decided to take a peek inside these tables and it looks like there are thousands of objects created every day. So what are these and do they get cleared out any time?

I found a similar question asked: User Profile Service Application SyncDB Database Size. There is an response that is marked as an answer, but it merely leads us to recommended provisioning sizes of these databases to be medium to large (100GB to 1TB). There is no explanation why. This is quite bogus as I only have 75 profiles.

Argh!

I have posted a followup here: Moving the User Profile Service Application Synchronization Database

Wednesday, January 19, 2011

Automated or remote installation of components on an Amazon instance

I am trying to figure out a way to either have an Amazon instance install various software components automatically right after launch or to execute commands to do those installations remotely.

I have done some searching and so far have found the following references.

AMAZON EC2 - Launch command on remote Windows machine, given admin credentials

Unattended Amazon EC2 Install Script - Unfortunately, this is for Linux. I would have to figure out how to write a Windows equivalent, if at all possible

Perhaps the Invoke-Command Powershell cmdlet would help.



Unfortunately, I don't have time to investigate further so I will have to come back to this later.

Thursday, January 13, 2011

Insufficient winsock resources available to complete socket connection initiation / tcp error code 10048

I have been trying to track down this problem for some time. Here are the symptoms I have seen.
There are a bunch of "TCP error code 10048: Only one usage of each socket address (protocol/network address/port) is normally permitted" messages in the Application Log
There are a bunch of "Insufficient winsock resources available to complete socket connection initiation"
I cannot connect to my SharePoint site or sometimes cannot even open central administration
Running netstat and TCPView results in thousands of connections in TIME_WAIT

I am running Windows 2008 x64 R2. According to KB929851 The default dynamic port range for TCP/IP has changed in Windows Vista and in Windows Server 2008, the number of ports has been bumped from a default range of 1025-5000 to a range of 49152-65535. So, bumping up the value to  HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\MaxUserPort does not help at all.

Also, these sockets are stuck in TIME_WAIT. By default the TIME_WAIT delay HKLM\SYSTEM\CurrentControlSet\Services\Tcpip\Parameters\TCPTimeWaitDelay is 240 so these should have been cleaned up after 4 minutes anyway. So, why are they not being cleaned up?

Anyone who has an idea of what's going on, please feel free to comment. In the meantime, I'm just going to reboot.

Another reference: Hurry Up and TIME_WAIT

Wednesday, January 12, 2011

Generating XML file from SQL SELECT statement

I needed to generate an OPML (XML) file from data from a SQL Server 2008 SELECT statement. It's a bit of a hack job, but this is pretty much what I did:

/* Generate part of the OPML file from the database */
/* Make sure max characters is 8192 */
/* Send results to text or to file */
/* Need to manually merge the rows back together */
SELECT
'<?xml version="1.0" encoding="utf-8"?><opml><head />'
+ (SELECT [col1] as text
      ,[col2] as title
      ,[col3] as type
      ,[col4] as xmlUrl
      ,[col5] as htmlUrl
  FROM [mydb1].[dbo].[mytable1] t1
  inner join [mydb1].[dbo].[mytable2] t2 on t1.id = t2.id
        for xml raw ('outline'), root ('body'))
+ '</opml>'

Reference: Constructing XML Using FOR XML.

Sunday, January 9, 2011

User Profile Service Application stuck in Synchronization state

I had a User Profile Synchronization job stuck in the Synchronizing state. Clicking Stop did nothing. I also tried stopping the User Profile Synchronization Service, but got the following error:

An update conflict has occurred, and you must re-try this action. The object UserProfileApplication Name=User Profile Service Application was updated by [farm admin id], in the OWSTIMER (7292) process, on machine [machine name].  View the tracing log for more information about the conflict.
I didn't see anything more helpful in the ULS logs, but I did see the same errors in the Application Log (Event Viewer)

Apparently, this is due to a well known problem with the cache in SharePoint 2010 (although it would be nice to know the cause). In any case, here's how you would fix it (as described in An update conflict has occurred, and you must re-try this action, but adapted to SharePoint 2010).
  1. Stop the timer service by doing the following:
    1. Click Start->Administrative Tools->Services
    2. Right click SharePoint 2010 Timer, select Stop
  2. Delete the contents of the folder: C:\ProgramData\Microsoft\SharePoint\Config\<some GUID>
  3. Start the timer service by doing the following
    1. Click Start->Administrative Tools->Services
    2. Right click SharePoint 2010 Timer, select Start
Once the timer is restarted, the cache folder will rebuild itself.

I was then able to go back into Manage my User Profile Service Application and stop the running sync job. 

Saturday, January 8, 2011

Language packs

I needed to setup a demo site, en francais. A couple of simple searches and it all seemed very straightforward. Well, there's one catch. For some reason, I didn't clue in to the fact that the instructions I found for SharePoint Foundation, which came up first in my search, were not for me. It's all obvious now - I needed My Site Host which is a feature in SharePoint Server. Therefore I should have used the instructions for SharePoint Server. Actually, the instructions are exactly the same except that the language packs they refer to are different.

In any case, if you need other languages, make sure you get the right language packs and instructions for your installation - either for SharePoint Foundation or SharePoint Server. If you need to know the differences between the two editions go to this blog entry to Compare SharePoint Editions.

Here are a few exceptions found in the ULS log - reformatted for easier reading or in case a search got you here :-)

Failed to create personal site ([username], [personal site URL]): System.ArgumentException: File or arguments not valid for site template 'SPSPERS#0'.  Parameter name: WebTemplate
     at Microsoft.SharePoint.SPWebTemplateCollection.get_Item(String strKey)
     at Microsoft.SharePoint.SPWeb.ApplyWebTemplate(String strWebTemplate)
     at Microsoft.SharePoint.Administration.SPSiteCollection.Add(SPContentDatabase database, SPSiteSubscription siteSubscription, String siteUrl, String title, String description, UInt32 nLCID, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String secondaryContactLogin, String secondaryContactName, String secondaryContactEmail, String quotaTemplate, String sscRootWebUrl, Boolean useHostHeaderAsSiteName)
     at Microsoft.SharePoint.SPSite.SelfServiceCreateSite(String siteUrl, String title, String description, UInt32 nLCID, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String contactLogin, String contactName, String contactEmail, String quotaTemplate, SPSiteSubscription siteSubscription)
     at Microsoft.Office.Server.UserProfiles.UserProfile.<>c__DisplayClass2.<CreateSite>b__0()


My Site creation failure for user '[username]' for site url '[site URL]'.  The exception was: Microsoft.Office.Server.UserProfiles.PersonalSiteCreateException: A failure was encountered while attempting to create the site.  ---> System.ArgumentException: File or arguments not valid for site template 'SPSPERS#0'.  Parameter name: WebTemplate
     at Microsoft.SharePoint.SPWebTemplateCollection.get_Item(String strKey)
     at Microsoft.SharePoint.SPWeb.ApplyWebTemplate(String strWebTemplate)
     at Microsoft.SharePoint.Administration.SPSiteCollection.Add(SPContentDatabase database, SPSiteSubscription siteSubscription, String siteUrl, String title, String description, UInt32 nLCID, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String secondaryContactLogin, String secondaryContactName, String secondaryContactEmail, String quotaTemplate, String sscRootWebUrl, Boolean useHostHeaderAsSiteName)
     at Microsoft.SharePoint.SPSite.SelfServiceCreateSite(String siteUrl, String title, String description, UInt32 nLCID, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String contactLogin, String contactName, String contactEmail, String quotaTemplate, SPSiteSubscription siteSubscription)
     at Microsoft.Office.Server.UserProfiles.UserProfile.<>c__DisplayClass2.<CreateSite>b__0()
     --- End of inner exception stack trace ---
     at Microsoft.Office.Server.UserProfiles.UserProfile.<>c__DisplayClass2.<CreateSite>b__0()
     at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass4.<RunWithElevatedPrivileges>b__2()
     at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)
     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)
     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)
     at Microsoft.Office.Server.UserProfiles.UserProfile.CreateSite(String strRequestUrl, Boolean bCollision, Int32 lcid)


Exception while creating personal site for 'EASTCLOUD\administrator': Microsoft.Office.Server.UserProfiles.PersonalSiteCreateException: A failure was encountered while attempting to create the site. ---> System.ArgumentException: File or arguments not valid for site template 'SPSPERS#0'.  Parameter name: WebTemplate
     at Microsoft.SharePoint.SPWebTemplateCollection.get_Item(String strKey)
     at Microsoft.SharePoint.SPWeb.ApplyWebTemplate(String strWebTemplate)
     at Microsoft.SharePoint.Administration.SPSiteCollection.Add(SPContentDatabase database, SPSiteSubscription siteSubscription, String siteUrl, String title, String description, UInt32 nLCID, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String secondaryContactLogin, String secondaryContactName, String secondaryContactEmail, String quotaTemplate, String sscRootWebUrl, Boolean useHostHeaderAsSiteName)
     at Microsoft.SharePoint.SPSite.SelfServiceCreateSite(String siteUrl, String title, String description, UInt32 nLCID, String webTemplate, String ownerLogin, String ownerName, String ownerEmail, String contactLogin, String contactName, String contactEmail, String quotaTemplate, SPSiteSubscription siteSubscription)
     at Microsoft.Office.Server.UserProfiles.UserProfile.<>c__DisplayClass2.<CreateSite>b__0()
     --- End of inner exception stack trace ---
     at Microsoft.Office.Server.UserProfiles.UserProfile.<>c__DisplayClass2.<CreateSite>b__0()
     at Microsoft.SharePoint.SPSecurity.<>c__DisplayClass4.<RunWithElevatedPrivileges>b__2()
     at Microsoft.SharePoint.Utilities.SecurityContext.RunAsProcess(CodeToRunElevated secureCode)
     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(WaitCallback secureCode, Object param)
     at Microsoft.SharePoint.SPSecurity.RunWithElevatedPrivileges(CodeToRunElevated secureCode)
     at Microsoft.Office.Server.UserProfiles.UserProfile.CreateSite(String strRequestUrl, Boolean bCollision, Int32 lcid)
     at Microsoft.Office.Server.UserProfiles.UserProfile.CreatePersonalSite(Int32 lcid)



What I do find annoying is why Microsoft keeps changing the edition naming conventions with every release:

  • SharePoint Team Services (STS) / SharePoint Portal Server (SPS)
  • Windows SharePoint Services (WSS) / Microsoft Office SharePoint Server (MOSS)
  • SharePoint Foundation / SharePoint Server

What's next?

Thursday, January 6, 2011

Creating predefined groups in site collection created by Powershell

Here's another discrepancy between what the UI does and what the Powershell scripts seem to do. As I'm in the process of scripting a manual creation process, one of the things I want to do is create a site collection. Looking at Technet you get instructions to Create a site collection (SharePoint Server 2010).

However, doing it via Central Administration vs via Powershell has a slight difference. If you create your site collection via the web UI you get a handful of SharePoint groups automatically created, but not if you create it via Powershell.

With the Powershell approach, there are "No predefined Groups in sitecollection created with Powershell". All you need to do is to call the CreateDefaultAssociatedGroups() method of the SPWeb object.

Here's the code I used:

New-SPSite -Url $sp_sc_url -Name $sp_sc_name -Template $sp_sc_template -OwnerAlias $sp_sc_owner -SecondaryOwnerAlias $sp_sc_owner2 -Language $sp_sc_language
$sp_web = Get-SPWeb $sp_sc_url
$sp_web.CreateDefaultAssociatedGroups($sp_sc_owner, $sp_sc_owner2, "")


Wednesday, January 5, 2011

Adding site collection administrators via Powershell

I was setting up a SharePoint server using Powershell scripts. Since my team manages the server I wanted to add them all as Site Collection Administrators on the top site, My Site and the Search Center. This turned out to be fairly straightforward but searching for a reference proved quite frustrating.

What I wanted to do is to use Powershell to do the equivalent of going to Site Actions->Site Settings,
Users and Permissions: Site collection administrators and then adding my team members to that list. If you are following along with your installation you will see that it does indeed call these users "Site Collection Administrators". However, do any search and you will come across articles such as "Add or remove site collection administrators (SharePoint Server 2010)" or "Add or remove site collection administrators (SharePoint Foundation 2010)" both of which clearly state "A site collection can only have two administrators ...".

Major head scratching commences. Lots of searching, swearing ...

Then I figured why not look at how the web UI does it. You will notice that the page that manages this is /_layouts/mngsiteadmin.aspx. Looking carefully I couldn't help but wonder why it's called Manage Site Admin. Subtle, but it's a clue.

Doing some searching and poking around I found that the SPWeb object has a SiteAdministrators property which as an Add() method. So, I tried calling SiteAdministrators.Add() with a user reference. There was no error, but nothing happened. So, I did more searching and eventually I came across this post: Adding more than two site collection administrators programmatically. Bingo!

Here's my code:
$sp_web = Get-SPWeb <URL>
$sp_web.AllUsers.Add("<username-1>", "<email-1>", "<name-1>",  "<notes-1>")
$sp_web.AllUsers.Add("<username-2>", "<email-2>", "<name-2>",  "<notes-2>")
...
$sp_web.AllUsers | where { "<username-1>", "<username-2>", ... -contains $_.UserLogin } | foreach { $_.IsSiteAdmin = "True"; $_.Update() }

Here's the lesson learned. What Microsoft has been calling Site Collection Administrators really has 2 meanings:
  1. Site Owners and Secondary Owners
  2. Site Administratrators
These are quite different and a good post that describes it is: Difference between Site Administrators and Site Collection Administrators and Site Collection Owners.


So clear now! What I was really trying to do is to add Site Administrators.