Sunday, 20 June 2010

Administration: Supported Database Operations in SharePoint 2010

Edit (13/08/2010)

@joeloleoson Expands this article by explaining how vendors use Microsoft’s protocol documentation to "do things not readily exposed in the object model" on his blog. I recommend you have a read.


I have seen a few queries recently around which database operations are supported in SharePoint. As SharePoint administrators, we need to be aware of which operations should be run on farm databases in order to ensure good farm health and performance - and which operations we should avoid at all costs in order to keep databases in a supported state.

Whilst I might take a slightly more relaxed approach in development environments with regard to database operations (I don't expect support from Microsoft for my VPC), my rule in a production environment is clear: never, ever perform an unsupported operation unless specifically asked to by Microsoft support. That includes reading from a content database - which might surprise some.

That's right - reading from a SharePoint DB will leave it in an unsupported state. That's confirmed in this KB article that was updated in April of this year and applies to pretty much any SharePoint product since WSS 2.0 up to and including SharePoint Server 2010 (with the exception of the logging database, where customisations are fully supported).

If you a take moment and think about this, prohibiting changes to the DB schema actually makes a lot of sense. Microsoft tests SharePoint based on a specific database schema and could not possibly predict changes that third parties are likely to make. Therefore, it could not practically support those changes. I think of this as analogous to purchasing a new BMW Z4, fitting a supercharger and then expecting the dealer to honour any warranty they may have promised - it just would not be practical nor economically viable.

As regards to preventing read operations (e.g. SELECT statements), I suppose that it's analogous to breaking the warranty sticker on an electrical appliance. OK, you may not have touched anything but it implies you may have done. Similarly, if Microsoft discover that you have been taking a look through a content database they would be inclined to think you have made a few changes along the way.

So we know that reading from a SharePoint database is prohibited. Here are a few other operations that Microsoft specifically list in the above KB article as leaving a database in an unsupported state:

Adding database triggers

Adding new indexes or changing existing indexes within tables

Adding, changing, or deleting any primary or foreign key relationships

Changing or deleting existing stored procedures

Calling existing stored procedures directly

Adding new stored procedures

Adding, changing, or deleting any data in any table of any of the databases for the products that are listed in the "Applies to" section

Adding, changing, or deleting any columns in any table of any of the databases for the products that are listed in the "Applies to" section

Making any modification to the database schema

Adding tables to any of the databases for the products that are listed in the "Applies to" section

Changing the database collation

Running DBCC_CHECKDB WITH REPAIR_ALLOW_DATA_LOSS (However, running DBCC_CHECKDB WITH REPAIR_FAST and REPAIR_REBUILD is supported, as these commands only update the indexes of the associated database.)

I recommend you read the KB article linked above for the most up to date list of (un)supported operations.

So which operations can we perform on a SharePoint database? The SQL DBA's out there will know that routine maintenance is essential for optimum performance - and few areas are as important to SharePoint performance than the SQL back end. Whilst I have not yet seen an equivalent resource for SharePoint 2010, Microsoft published a very useful white paper back in November 2009 called "Database Maintenance for Office SharePoint Server 2007". I encourage you to take a look if you haven't seen this already and are involved in MOSS administration (although I imagine it will be relevant to SharePoint 2010 also).

According to this white paper, the following maintenance tasks are recommended:

Checking database integrity (ensures that your data and indexes are not corrupted).

Defragmenting indexes by either reorganizing them or rebuilding them (applies mainly to indexes; fragmentation can be reduced by setting a large database start size).

Setting the fill factor for a server (allows index performance to be fine tuned).

Shrinking databases to recover unused disk space (white space caused by deleting documents, libraries, lists etc.).

Note that Microsoft do not recommend configuring auto-shrink operations, and advise only to shrink content databases due to the fact that other types of databases (e.g. SSP) do not typically undergo a large number of deletions.

In conclusion, things haven't really changed much since MOSS with regard to which DB operations are supported - the main message is to keep well away from them! I imagine Microsoft will release a similar resource for SharePoint 2010 with an updated list of recommended DB maintenance operations. For now, I will be implementing the above MOSS maintenance plans on my SharePoint 2010 test environment.

Saturday, 19 June 2010

SharePoint 2010 and .NET 4: confused?

I have seen a few queries on the Technet forums recently regarding the use of ASP.NET 4 and SharePoint, an example being this post here.

There appears to be some confusion (myself included) around whether or not ASP.NET 4 is supported for both MOSS and SharePoint 2010 - in short, the answer is that it is not currently supported in either product.

Whilst it is possible to use ASP.NET 3.5 in MOSS by making some modifications to the SharePoint site web.config (instructions for doing that in MOSS here; SP2010 uses 3.5 by default), this is unfortunately not the case with version 4.

The reason? ASP.NET 4 uses a new version of the Common Language Runtime (CLR). Whilst .NET 2.0, 3.0 and 3.5 all used version 2.0 of the CLR, .NET 4 uses version 4. I'm not entirely sure what happened to version 3. There is a decent post on stack overflow with further details.

For those that are wondering, the fact that .NET 3.0 and 3.5 do not show up as options within IIS is also related to the CLR version. IIS knows about that - it does not know about framework and compiler versions and the aspnet_isapi.dll from the version 2.0 is used for version 2.0 through 3.5 for this reason. This post explains that in more detail. Does that mean we will get a new option in IIS for .NET 4? You bet, due to the use of CLR v4.

So does that mean we are destined to use .NET 3.5 for the lifetime of SharePoint 2010? Not necessarily. Aside from the possibility of Microsoft adding support for version 4 in a future service pack, those using ASP.NET Web forms in separate virtual directories will still be able to use .NET 4 so long as said virtual directories are in a separate application pool. I have confirmed this is the case with Microsoft in this post, and believe this to be the case for SharePoint 2010.

I hope that helps clarify things a little.

Subscribe to the RSS feed

Follow me on Twitter

Follow my Networked Blog on Facebook

Thursday, 17 June 2010

Extranets: SharePoint 2010 Gateway and Firewall Products

While there is already an abundance of information available online regarding SharePoint 2010, I had not seen much related information on gateway products until this afternoon. Considering the improvements made to Web hosting in SharePoint 2010, I thought it worth taking a look at the various supported options available and determine whether there is much difference when compared to those available at the time MOSS was released back in 2007. The most notable changes since then are that TMG 2010 is effectively the replacement for ISA server 2006 (although ISA 2006 is still tested and supported in SharePoint 2010), and ForeFront UAG 2010 replaces IAG 2007.

Wikipedia defines an extranet by stating that "An extranet can be viewed as part of a company's intranet that is extended to users outside the company, usually via the Internet". In the context of SharePoint, I interpret this as the ability to securely publish SharePoint resources over the Internet in order to provide access to users outside of the company infrastructure.

There are numerous reasons that companies may wish to use a firewall or gateway product to publish SharePoint resources; in my experience this has typically been to provide secure remote access to external staff and customers including those using mobile devices. One product that I have worked with in the past to facilitate this is ISA Server. As with MOSS, Microsoft have tested ISA server with SharePoint 2010 and provide a useful comparison between this, ForeFront TMG and ForeFront UAG here. One area that caught my attention was Web hosting: in SP2010, hosting providers are supposedly "first class citizens" with the introduction of data partitioning and service subscriptions to facilitate multi-tenancy. With this in mind, it seems likely that hosted service provides will want to consider a secure method of publishing their server farm to remote users using a gateway product such as Forefront UAG 2010.

Looking at the comparison provided by Microsoft in the above document, one might wonder why businesses would bother upgrading to Forefront TMG when they are already using ISA server 2006. After all, the only additional feature advertised appears to be "DirectAccess" - and even that is only partially supported for TMG 2010.

John Wettern made some some interesting points back in 2009 that might lead us to think that perhaps TMG is a significant release, as opposed to the difference between ISA server 2004 and 2006 which (as John rightly points out) was little more than a service pack in terms of new features. Looking on the Forefront TMG Technet Blog reveals a couple of other documented benefits of TMG over ISA server 2006 - integrated anti malware protection and encrypted traffic inspection. DirectAccess is a new feature in Windows 7 and Windows Server 2008 R2 that reportedly allows access to shared resources such as folders, e-mail servers and intranets without the need for a VPN.

Although the benefits here in using TMG over ISA are not really that relevant to securing a Web hosting service (which would likely benefit from the use of a reverse proxy server deployed in an edge firewall topology), I think the new features described in the above articles would certainly be useful to companies looking to deploy a gateway product in a back to back perimeter scenario for securing internal resources against the dangers faced online and improve accessibility to internal resources through DirectAccess. Indeed, Microsoft describe TMG as "a comprehensive, secure Web gateway that helps protect employees from Web-based threats" as opposed to UAG which "delivers secure, anywhere access to messaging, collaboration, and other resources, increasing productivity while maintaining compliance with policy".

So aside from the obvious benefit of an extended support period, there appear to be few compelling reasons for MOSS Web hosting providers to move from an existing reverse proxy scenario using ISA server 2006 to TMG 2010 when it comes to upgrading to SharePoint 2010 - indeed, you would only use TMG to protect internal users from Web based threats.

Lets move on to Forefront UAG 2010 - Microsoft's flagship gateway product that extends the features offered by TMG to include dedicated interfaces for mobile devices, health based authorisation and information leakage mitigation.

Microsoft provide a useful lists of benefits UAG brings to SharePoint here. This time the feature comparison appears to offer a more compelling reason to upgrade from ISA server 2006 in order to publish SharePoint 2010 resources - lets take a look at these features in the context of a provider looking to publish an extranet in a remote access scenario.

First up - information leakage mitigation. Having been through numerous penetration tests with prospective clients recently this one certainly sparked my interest. UAG deletes all cached files, temporary files and cookies which from a security perspective offers a compelling new feature when compared to ISA and TMG. Similarly, endpoint health-based authorisation offers an interesting corporate security improvement - particularly the capability to prevent unsupported (and potentially insecure) browser versions from accessing a published SharePoint site. It seems that these new security features will allow hosting service providers to proactively protect their sites in more ways than ever before.

Microsoft's inclusion of "Granular access policies" as an exclusive UAG feature in the comparison threw me slightly - having used ISA server 2006 extensively as an edge firewall I didn't see this is a new feature given that the ISA policy manager is extensive. I dug a little further on technet and can conclude that this feature mainly refers to the ability to target policies at specific platforms - such as Windows, Macintosh or Linux. I think that Microsoft's consideration for platforms other than Windows here demonstrates their change in approach toward competitors - a welcome change for customers that is consistent with the cross-browser support for SharePoint 2010.

In conclusion, customers planning to move to SharePoint 2010 that are looking to implement a gateway product to securely publish their server farm have more choice than ever before. While there are few obvious reasons to move from an existing ISA server 2006 based topology to TMG 2010, the extended features offered by UAG 2010 are a definite improvement and will allow providers to offer a very secure, policy based SharePoint installation to customers. Customers wishing to upgrade should note that UAG 2010 requires Windows Server 2008 R2 64-bit.

Subscribe to the RSS feed

Follow me on Twitter

Follow my Networked Blog on Facebook

Saturday, 5 June 2010

Administration: Stopping a SharePoint 2007 farm

I noticed today that a fair number of people have found this blog by searching for something along the lines of "sharepoint stop farm procedure" or "how to stop sharepoint farm", particularly from one very well known search engine. I briefly described how to go about doing that in my post titled "Administration: Migrating to a new SQL Server in MOSS (SQL 2005 to 2008)".

I thought I would clarify this procedure in a little more detail given that it seems to be a common query.

Technet describes the procedure for stopping a SharePoint 2007 (MOSS) farm as follows:

Stop the farm by performing the following steps:

  1. On the server that is running the Central Administration Web site, in the Services snap-in, stop the following services:
    • Windows SharePoint Services Administration service
    • Windows SharePoint Services Search service
    • Windows SharePoint Services Timer service
    • Windows SharePoint Services Tracing service
    • Windows SharePoint Services VSS Writer service
  2. On the server that is running the Central Administration Web site, at the command prompt, type iisreset /stop.
  3. Repeat step 6 on each server in the farm.

As you can see, the procedure involves first stopping all MOSS related services on your server(s) hosting the Central Administration Web site, stopping IIS then repeating the process on all WFE servers.

Whenever I have had to carry out this procedure I have created a simple batch script to automate the process and ensure I don't miss any services out. This is easily achieved by pasting the following into a notepad file and saving it as a .bat:

net stop "Windows SharePoint Services Administration" /y

net stop "Windows SharePoint Services Search" /y

net stop "Windows SharePoint Services Timer" /y

net stop "Windows SharePoint Services Tracing" /y

net stop "Windows SharePoint Services VSS Writer" /y

net stop "IIS Admin Service" /y

One other precaution I take to ensure the farm is inaccessible is to create a rule on the proxy server (e.g. the ISA Server array) to effectively block all Web access and present users with a useful error page.

I think it goes without saying that this process will prevent all access to a SharePoint server farm so should only be performed outside of peak hours with prior user notification.

I hope that helps!

Benjamin Athawes

Subscribe to the RSS feed

Follow me on Twitter

Follow my Networked Blog on Facebook