SharePoint and SQL Server with Greg Low at PSPUG

Sezai’s been doing a fantastic job of attracting some high-end speakers to the Perth SharePoint User Group and we had the immense pleasure of attending Greg Low‘s presentation on SQL Server optimisation for SharePoint admins this month.

Greg is a god among men in my mind and one of those can’t-miss speakers; apart from being a SQL Server MVP and Microsoft Regional Director, he really knows his stuff and delivers a nuanced presentation. I’d seen Greg in Adelaide on previous occasions and since he’d made his way all the way to Perth just for us, there’s no way I’d miss him.

There were some key take-aways from Greg’s talk I’m paraphrasing here for future reference… note I’m not a DBA and don’t necessary understand all of this—in other words, don’t apply this advice blindly without additional research!

• The autogrow settings should be configured appropriately on both content databases and the tempdb—the SQL Server defaults are inappropriate. In general, this means turning off autogrow and managing database file size manually. Alternatively, autogrow can be left enabled for contingency but should be reconfigured to grow using an appropriate value (in MB, by ~100MB).
• tempdb is rebuilt to the configured autogrow value every time the sqlserver process restarts; the start value should be large enough to avoid file system fragmentation.
• Never, ever auto shrink a SharePoint database as it will only have to regrow again and may increase fragmentation.
• Use DBCC CHECKDB but beware fixing problems may incur data loss.
• Whack your disk subsystem using benchmarking utilities like SQLIO and Iometer. Reasonable latency falls between 5-15ms, for example.
• Instant file initialisation allows SQL to write to files that have not been zeroed out by the OS, thereby avoiding the performance hit incurred by that activity. The MSSQLSERVER service account must be granted the SE_MANAGE_VOLUME_NAME right by virtue of being added to the Perform Volume Maintenance Tasks security policy.
• Multiple HBAs can lead to write ordering and disk subsystem issues; SQL Server 2005 introduced page checksums to help with this issue but the feature is turned off by default; it should be enabled.
• Virtualising database servers faced a lot of bad press in the past but those days are behind us. Hyper-V R2 and ESX are the way forward.
• Don’t even think about SQL Server 2000.
• The gap between SQL Standard and Enterprise is getting wider with SQL Server 2008.
• As most SQL Server instances are disk and memory-bound, consider enabling table/row/page compression in 2008 to reduce IO and memory usage at the expense of CPU. You mileage may vary as this will obviously depend on the content to be stored as photos and Office documents are already compressed. Backup compression is also possible.
• Configure databases with one file per CPU core.
• Different databases have different IO profiles; tempdb should be hosted on a dedicated spindle (see http://technet.microsoft.com/en-us/library/cc262731.aspx for more info about locating the various SharePoint databases).
• Ensure statistics are configured correctly.
• Index fill factor should typically be ~70% for a typical SharePoint environment.
• The default Windows Server 2003 sector size is too small; set it to 64k when formatting drives.
• Clustering SQL won’t boost performance like mirroring will. Clustering happens at the server level whereas mirroring must be configured for every database.
• If configuring a SQL alias, use TCP/IP.
• Create a separate SQL Server instance if an existing database server’s collation doesn’t match SharePoint’s specific requirements. Changing an incorrect collation is a lot of work.

Can’t reinstall Flash after failed v10 update

The silly Flash player failed to update in IE8 the other day; clicking the Get Flash link would take me to the installer page but the “gold bar” would never appear for me tell IE to download and install the player.

Chrome and Firefox are easier to deal with as the Flash plugin is installed through Add/Remove Programs.

Although forum reply suggested resetting IE fixed the problem for him I’m not that kind of guy ;-). For the record, Tools > Internet Options, Advanced tab and click the Reset… button.

In my case I resolved the problem by running the Flash uninstaller from the Flash Player Support Center

http://kb2.adobe.com/cps/141/tn_14157.html

The MOSS setup account must be a member of db_owner role of the content database

One of the first things I normally do after creating a new web application and site collection is backup the content database using stsadm -o backup. If you're running in a least privilege scenario, however, there's an extra step required to configure access to the content database.

Whenever I'm working interactively with MOSS in an administrative capacity, I'm logged in as the MOSS setup account. This is the account used to install MOSS and create the farm; it’s also a member of the local admin and WSS_ADMIN_WPG groups, a member of the Farm Administrators group, and I've even got it configured as the primary site collection administrator for my content databases. In general, this account gives me the permissions I need to do everything I need to do while keeping my MOSS install functional.

Despite the wealth of permissions granted to this account, I was surprised when I tried running the stsadm -o backup command and came up against this error:

Cannot open database "WSS_Content_APP" requested by the login. The login failed.
Login failed for user 'domain\moss_setup'.

A similar, dodgier message was echoed in the Application event log.

To work around this, the setup account must be added to the db_owner role of the content database being backed up:

Navigate to the {WSS_Content DB}\Security\Roles\Database Roles\db_owner role and view its properties
Add the Setup account (eg. domain\moss_setup) as a Role Member

I've found no other role will suffice. Note in a least privilege scenario, the setup account should not be a member of the local admin group on the database server.

I'd love to know how to do this automatically whenever a new content database is added although I can understand why MOSS doesn’t do this for me… sort of ;-)

Database not found

While attempting to run the stsadm -o backup command as the MOSS setup user for the first time in a new environment today, I came across an error message in the application event log:

Event Type:    Error
Event Source:    Windows SharePoint Services 3
Event Category:    Database
Event ID:    3760
Description:
SQL Database 'WSS_Content_APP' on SQL Server instance 'MOSSSQL' not found. Additional error information from SQL Server is included below.

Cannot open database "WSS_Content_APP" requested by the login. The login failed.
Login failed for user 'domain\moss_setup'.

I wasn't careful when I first read the message and only read the first line. As MOSSSQL is actually a SQL Server alias to a physical server and this is the first time I've used aliases, I initially wondered if the backup command or stsadm was unable to deal with alias. That's silly, of course, because I've never told MOSS which physical server is underlies the alias and it only knows about the alias.

I'm not sure exactly how MOSS and SQL together track down the WSS_Content_APP database but the crux of this error message lies in the second part as flagged by SQL Server. In my case, the moss_setup account required access to the content database--but that's another blog post.

MOSS Calculated Columns

The Microsoft Office Online site has some useful pages relating to calculated columns, formulas, and formatting in SharePoint. I can't help but wonder if this is yet another Microsoft pseudo-language but the reference may come in handy.

• Introduction to data calculations
  http://office.microsoft.com/en-us/sharepointtechnology/HA101215881033.aspx
• Examples of common formulas
  http://office.microsoft.com/en-us/sharepointtechnology/HA011609471033.aspx

Access is denied. Check that the Default Content Access Account has access to this content

I ran into this error in the crawl log for the Local Office SharePoint Server sites content source:

Access is denied. Check that the Default Content Access Account has access to this content, or add a crawl rule to crawl this content.

I had configured the default content access account within the search administration and granted Full Read access to the same account from Central Admin\Application Management\Policy for Web application; the site to be crawled also had an include crawl rule configured. As a test, I also added the default content access account to the [SiteName] Visitors group with no effect. Oddly enough, some of the site content was being crawled (well a single document anyway) but I just don’t like error messages ;-)

The environment in question is a Windows 2003 SP1 machine with MOSS 2007 SP2 and .NET 3.5 SP1. It’s a dev environment and it’s also configured with the Active Directory and DNS roles and runs SQL Server 2005 SP2. Site-wise, MOSS is hosting a single test site configured to use a host header on port 80 with a DNS A record pointing to 127.0.0.1. And therein lay the problem…

Apparently Windows 2003 SP1 introduces a “loopback check security feature […] designed to help prevent reflection attacks.”

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

The KB article also describes two work arounds for the problem, the first being to list the host names mapped to the loopback address (127.0.0.1) and the second disabling the security check.

Method 1: Specify host names

Specify the host names that are mapped to the loopback address:

• Click Start, click Run, type regedit, and then click OK.
• In Registry Editor, locate and then click the following registry key:
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa\MSV1_0
• Right-click MSV1_0, point to New, and then click Multi-String Value.
• Type BackConnectionHostNames, and then press ENTER.
• Right-click BackConnectionHostNames, and then click Modify.
• In the Value data box, type the host name or the host names for the sites that are on the local computer, and then click OK.
• Quit Registry Editor, and then restart the IISAdmin service.

Method 2: Disable the loopback check

• Click Start, click Run, type regedit, and then click OK.
• In Registry Editor, locate and then click the following registry key:
  HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Control\Lsa
• Right-click Lsa, point to New, and then click DWORD Value.
• Type DisableLoopbackCheck, and then press ENTER.
• Right-click DisableLoopbackCheck, and then click Modify.
• In the Value data box, type 1, and then click OK.
• Quit Registry Editor, and then restart your computer.

Note: In my case this took effect without resetting the server; although other suggestions recommended restarting IIS, I didn’t have to do that either.

SSP Access Denied

It's quite easy to be faced with an Access Denied screen after creating a Shared Service Provider (SSP) but it's not always so easy to fix the problem.

In most cases, treat your SSP admin sites like any other SharePoint site and ensure the correct users have been granted the necessary access permissions (yoursite/ssp/admin/_layouts/settings.aspx and then People and Groups). You'll generally want to access the SSP site to configure these permissions using the account used to create the SSP in the first place; if that fails, try the account used to run the IIS app pool of the SSP admin site. If that fails, read on...

Before diving in I want to mention this issue has been covered before in various places with the most notable items being Faraz Khan's post; the post includes a plausible explanation of the problem provided by "Scott" in the post's comments. A social.technet thread also points to the same post and some some additional solutions are reported. When I encountered this problem I found both sources cumbersome to wade through and difficult to decipher so I'm summarising the steps I took to fix the problem.

Also, a disclaimer of sorts. SharePoint and MOSS are finicky beasts so while the solution described below may seem to work, I expect a better solution would be to delete the problem SSP after creating a replacement and making it the default SSP (see below to find out what’s causing the problem before you recreate). I’m not certain the solution described won’t introduce new problems (but so far so good in the proof-of-concept environment I’ve built).

To setup, let's assume the following:

• You've created a new SSP named Default SSP
• As part of the SSP provisioning process, you've created a new SSP admin site with an app pool named Default SSP
• The app pool of the SSP admin site account is configured to use the SSP_APP account
• The SSP itself is configured to run as the SSP_SVC account
• When you try to access the site using the account used to create the SSP, the SSP_APP app pool account, or the SSP_SVC service account, you receive an Access Denied error; you can however access /ssp/admin/_layouts/settings.aspx as the SSP creator
• Inspecting the Default SSP app pool, you find the identity is set to run as SSP_SVC instead of SSP_APP as specified above. Changing the identity back to SSP_APP sticks for a few seconds but quickly reverts to SSP_SVC.
• Nothing is reported in the event logs

According to legendary "Scott", the problem occurs because the application pool configured for use by the SSP's admin web application has the same name as the SSP itself (Default SSP). Apparently MOSS independently creates another app pool named after the SSP and running with the SSP credentials (SSP_SVC) for use by the “Office Search site”. The Office Server Web Services web site does have a child web application named Default SSP that is also configured to use the Default SSP app pool. It makes sense that since an app pool with the same name already exists, MOSS either recreates it or magically updates it on a scheduled basis.

To resolve the problem:

• From IIS Manager, right-click the app pool intended for use by the SSP admin site (eg. Default SSP)
• All Tasks -> Save Configuration to a File… and specify a location where you can easily find the output file
• Open the saved .xml file in notepad and change the two references to “Default SSP” (or whatever you’ve named your SSP/SSP admin site account):
  1. The Location attribute of the IISApplicationPool element - change Default SSP to Default SSP App
  2. The Location attribute of the IISInheritedProperties element - change Default SSP to Default SSP App
• Save the file
• Right-click on the Application Pools folder in IIS Manager
• New -> Application Pool (from file)...
• Locate and select the file, click the Read File button, select the configuration, Ok
• Configure the Default SSP web site to use the new app pool (Properties -> Home Directory tab, Application pool drop down)
• Repeat for the /images and /inc applications below the _layouts child site (Default SSP/_layouts/Images, inc)
• Reset IIS for good measure

You’ll now have two application pools, Default SSP and Default SSP App. Don’t delete the original as it’s still in use by Office Server Web Services web site and may get recreated anyway.

IE8 Slow to Load, Slow to Create New Tabs

I don’t use IE much ‘cause I’m a huge Chrome fanboy and Firefox is the obvious tool of choice for most things web dev. That said, with integrated authentication IE just works better with SharePoint and TFS to the point where I’m pretty much forced to leave it set as my default browser.

I had very high hopes for IE8 and the latest version of the IE Developer Toolbar; I now feel let down. Apart from the default install being horribly slow to load and create new tabs (I timed it around 8-9s), I’ve found the IE Dev Toolbar to be less intuitive and very ugly compared to the old standalone IE Dev Toolbar and of course Firebug.

The one thing I really love is the ability to force new windows and pop-ups into new tabs—again because of the way SharePoint won’t let you right-click/open in new tab when viewing a page and the TFS web interface sprawls new windows.

I’m on an ancient XP machine that sucks at everything so at first I thought the speed was just me. Googling “IE8 slow” however quickly pointed me to Ed Botts’ post that suggests registering a specific DLL and rebooting. I’m not alone after all, I thought to myself, and promptly registered the DLL and rebooted with no change.

I gave up for a while at that point until I came back to the issue today. Raphael’s post countering Ed Bott’s post, which I’d also read previously, is now full of comments suggesting different remedies. One particular comment suggested disabling add ons and Java in particular.

So I dove in and disabled all of my unused add ons, noting the load time value against the Java item. I imagine Doing so fixed the problems and I can load new tabs in roughly half a second.

I hate Java apps and generally have no need for the add on so I guess I’ll just leave it disabled. The same commenter mentioned also indicated updating Java to the latest version didn’t help.

It seems like each new IE8 tab fires off its own process so I can see how add ons would need to be loaded every time a new tab is created but compare the listed load time of 2.49s versus the others at 0.01s and it’s both shocking that IE will allow this to happen and Sun think they can get away with this.