SharePointChick.com

Copy SharePoint production data to a test environment

2010-03-01T15:02:43Z

In a lot of SharePoint projects you will be required to deploy customizations after the customer has started using the solution. In cases like this it’s very important to test all customizations extensively on a test and/or user acceptance environment before deploying them to production.
In order to be able to do tests that, to a certain degree, can guarantee that both the current and the new functionality will work on your production environment you need an environment that matches your production environment in a lot of ways.

As similar a hardware setup as possible
Same Windows Server and SQL Server versions and setup
Same SharePoint version and setup
Same third party tools installed

All of the above are fairly straightforward to implement using standard tools and processes. However in order to be able to properly test your solution you will also need very similar data on your test, user acceptance and production environment. This is a bit more difficult to achieve. There are several third party tools that you could buy that will help you to move around SharePoint data, but you can also achieve this using free and standard tools and scripts.

If you want to copy data from you production environment to your test environment and you want to environment to still function as it’s supposed to as well you need to take four different steps that, apart from the actual copying of the data also involve preparing your environments for the data move and finalizing them after the data has been copied.

Create the Active Directory accounts that are used in the production environment in the test domain or OU
Backup the SharePoint data from the production environment
Restore SharePoint data on the test environment
Migrate the users in your SharePoint environment from their production version to their test equivalent

To assist with the Active Directory export and import of accounts the first thing I did was download the Quest Free PowerShell Commands for Active Directory here. Next step was to write a PowerShell script to export the users from the Production Active Directory to an XML file. Install the ActiveRolesManagementShell on the domain controller and make sure the add-in is loaded in PowerShell. Now run the export command:

add-PSSnapin quest.activeroles.admanagement 
get-QADUser -SearchRoot "OU=Company,OU=ContainerName,DC=DomainName,DC=com" 
   -SerializeValues -SizeLimit 0 | Export-Clixml ADUsers.xml

The exported XML looks like this:

<Employees xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
	<Employee>
		<S N="objectClass">top,person,organizationalPerson,user</S>
		<S N="objectSid">0105000000000005150000002E2314E71F387BEE9F955DED490E0000</S>
		<S N="name">JSmith@Company.com</S>
		<S N="displayName">John Smith</S>
		<S N="givenName">John</S>
		<S N="whenCreated">09/20/2009 16:11:21</S>
		<S N="objectGUID">84576687B7A0CE49BB67FD71B1BAD046</S>
		<S N="primaryGroupID">513</S>
		<S N="sAMAccountName">JSmith</S>
		<S N="whenChanged">09/25/2009 10:04:52</S>
		<S N="userPrincipalName">JSmith@Company.com</S>
		<S N="mail">john.smith@company.com</S>
		<S N="sn">Smith</S>
	</Employee>
	<Employee>
		<S N="objectClass">top,person,organizationalPerson,user</S>
		<S N="objectSid">0105000000000005150000002E2314E71F387BEE9F955DED0D1A0000</S>
		<S N="name">JDoe@Company.com</S>
		<S N="displayName">Jane Doe</S>
		<S N="givenName">Jane</S>
		<S N="whenCreated">09/20/2009 17:01:04</S>
		<S N="objectGUID">A631C68C086D5A488565F99898F1E5CD</S>
		<S N="primaryGroupID">513</S>
		<S N="sAMAccountName">JDoe</S>
		<S N="whenChanged">01/13/2010 15:47:55</S>
		<S N="userPrincipalName">JDoe@Company.com</S>
		<S N="mail">jane.doe@company.com</S>
		<S N="sn">Doe</S>
	</Employee>
</Employees>

The XML will get a bit easier to process if you transform it slightly by using a short XSLT script:

<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
	<Employees>
	<xsl:apply-templates />
	</Employees>
</xsl:template>

<xsl:template match="Employee">
	<Employee>
		<xsl:apply-templates/>
	</Employee>
</xsl:template>
<xsl:template match="S">
	<xsl:text disable-output-escaping="yes">&lt;</xsl:text>
	<xsl:value-of select="@N"/>
	<xsl:text disable-output-escaping="yes">&gt;</xsl:text>
	<xsl:value-of select="."/>
	<xsl:text disable-output-escaping="yes">&lt;/</xsl:text>
	<xsl:value-of select="@N"/>
	<xsl:text disable-output-escaping="yes">&gt;</xsl:text>
</xsl:template>

</xsl:stylesheet>

After we transformed the XML we also do some text replacements. In my case the test environment uses the same domain as the production environment is. Because of this I have to create new usernames. Because I don’t want to send the users email from the test environment I change the email addresses to non-existing or test email addresses.
The XML now looks like this:

<Employees>
	<Employee>
		<objectClass>top,person,organizationalPerson,user</objectClass>
		<objectSid>0105000000000005150000002E2314E71F387BEE9F955DED490E0000</objectSid>
		<name>JSmith@CompanyTest.com</name>
		<displayName>John Smith</displayName>
		<givenName>John</givenName>
		<whenCreated>09/20/2009 16:11:21</whenCreated>
		<objectGUID>84576687B7A0CE49BB67FD71B1BAD046</objectGUID>
		<primaryGroupID>513</primaryGroupID>
		<sAMAccountName>JSmith_Test</sAMAccountName>
		<whenChanged>09/25/2009 10:04:52</whenChanged>
		<userPrincipalName>JSmith@CompanyTest.com</userPrincipalName>
		<mail>doesnotexist@companyTest.com</mail>
		<sn>Smith</sn>
	</Employee>
	<Employee>
		<objectClass>top,person,organizationalPerson,user</objectClass>
		<objectSid>0105000000000005150000002E2314E71F387BEE9F955DED490F0000</objectSid>
		<name>JDoe@CompanyTest.com</name>
		<displayName>Ricardo Hooijmaijers</displayName>
		<givenName>Ricardo</givenName>
		<whenCreated>09/20/2009 16:35:52</whenCreated>
		<objectGUID>AB161693EC231D41A0C9F72F1279411C</objectGUID>
		<primaryGroupID>513</primaryGroupID>
		<sAMAccountName>JDoe_Test</sAMAccountName>
		<whenChanged>09/25/2009 10:04:53</whenChanged>
		<userPrincipalName>JDoe@CompanyTest.com</userPrincipalName>
		<mail>doesnotexist@companyTest.com</mail>
		<sn>Doe</sn>
	</Employee>
</Employees>

In order to be able to use all user accounts for testing purposes I use the same password for all users on the test environment.
We can now run the PowerShell script that creates the users in the test environment. The script adds all users to a group called AllUsers@Company – Test right away. The new-QADUser cmdlet returns a user and that user is fed into the add-QADGroupMember cmdlet by using the | (pipe) symbol.

add-pssnapin quest.activeroles.admanagement

cd "C:\ADUsers"
[xml]$userfile = Get-Content ADUsers.xml

foreach ($user in $userfile.Employees.Employee)
{
   new-qaduser -ParentContainer "OU=Company - Test,OU=ContainerName - Test,DC=DomainName,DC=com" -Name $user.name 
-UserPassword "Password1" -Email $user.mail -FirstName $user.givenName -LastName $user.sn 
-samAccountName $user.sAMAccountName -UserPrincipalName $user.userPrincipalName -DisplayName $user.DisplayName 
| add-QADGroupMember -identity CN=AllUsers@Company - Test,CN=_Private,OU=Company - Test,OU=Container - Test,DC=DomainName,DC=com  
   Write-Host $user.AccountName
}

The next step is to backup the data from the production environment. Before we can simply backup the content database we have to “unhook” it from the production environment. We do this by running the STSADM preparetomove command.

STSADM –o preparetomove –contentdb SQLServerInstance:ContentDBName

Now just make a SQL Server backup.
Next we have to undo the preparetomove command, or else the user profile info won’t sync to the user info lists in the site collections in that particular content database anymore.

STSADM –o preparetomove –contentdb SQLServerInstance:ContentDBName –undo
We can now copy the database backup file from the production environment to the test environment and restore it on our SQL Server test environment. Next we go into the SharePoint test environment. You might want to delete the current content database in the test environment, before adding the newly restored one.

STSADM –o deletecontentdb –url http://testportalurl –databasename ContentDBName

Running the STSADM deletecontentdb command won’t delete your database from SQL, it will only delete it from SharePoint. This means that the data is still on the SQL Server, the sites just aren’t accessible anymore through SharePoint.

Now we are ready to add the restored content database to our SharePoint test environment.

STSADM –o addcontentdb –url http://testportalurl –databasename ContentDBName

Go into Central Administration, browse to Application Management and click on Policy for Web Application. Give yourself Full Control on the web application where you just added the content database, so you can test whether it’s working.
Now all we have to do is tell SharePoint how to map the production users accounts to the test user accounts, to make sure that the permissions for all accounts will work like they do on the production environment. We use the STSADM migrateuser command for this. Be aware that you need to use the sAMAccountName for this, using the userPrincipalName won’t work. For generating the migrateuser statements I have written a small XSLT script that uses the ADUsers.xml file.
```
<?xml version="1.0" encoding="UTF-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
<xsl:template match="/">
	<xsl:apply-templates />

</xsl:template>

<xsl:template match="Employee">
<xsl:apply-templates select="sAMAccountName" />
</xsl:template>
<xsl:template match="sAMAccountName">
	<xsl:text disable-output-escaping="yes">stsadm -o migrateuser -oldlogin domainname\</xsl:text>
	<xsl:value-of select="."/>
	<xsl:text disable-output-escaping="yes"> -newlogin domainname\</xsl:text>
	<xsl:value-of select="."/>
	<br />
</xsl:template>

</xsl:stylesheet>
```
This will generate a statement that looks like this:

stsadm -o migrateuser -oldlogin domainname\JSmith_Test -newlogin domainname\JSmith_Test –ignoresidhistory
stsadm -o migrateuser -oldlogin domainname\JDoe_Test -newlogin domainname\JDoe_Test –ignoresidhistory

This is almost what we want. The only thing left to do is a text replacement to make sure that the old login is actually the production account. So we replace “_Test –newlogin” with “ –newlogin”.

Wrap up

The above 4 steps will help you to restore you production data on your test environment. While the process described in this post might look difficult and elaborate it is actually fairly straight forward and fast. Most of the fiddling is with transforming XML and the longest running operations are (by far) the export and import of AD users.

SharePoint Must Read: SharePoint 2007 Memory Leak

2010-02-08T23:23:01Z

I usually don’t do link blogs, but this is a must read for everyone implementing SharePoint.

PFE and MCM instructor Todd Carter posted the details of his findings on his blog.
Todd's Blog | SharePoint’s Sasquatch Memory Leak

Oh and it will be worth keeping an eye out for Todd’s future posts as well..

SharePoint Connections Amsterdam Wrap-Up

2010-02-06T18:44:18Z

I know it’s been a while since the conference, but I still wanted to write a short wrap up.

The conference was great. It was sold out, another sign of the fact that SharePoint is a booming business and people love to hear about what’s new in SharePoint 2010.
The goal of the conference was to redeliver some of the content from the SharePoint Conference in Las Vegas in October. Since this one only lasted two days it was impossible to cover all the content, but overall I think there was a lot of great content and the most important areas were covered.

Also, Matthijs Hoekstra arranged for all sessions to be recorded and put up on Channel 9 (developer sessions) and TechNet TV (IT Pro sessions).

The complete list of sessions is up on Matthijs’ blog, or you can search on SPC10 (http://channel9.msdn.com/tags/spc10/ and http://edge.technet.com/Tags/spc10/).

My sessions are on there as well. I still find it weird and uncomfortable to look at and listen to myself, but it’s pretty cool to have content on Channel 9 and TechNet, so I’ll just suck it up and try to get used to it.
These were the sessions that I delivered at the conference:

Overview and What's New for SharePoint 2010 IT Pros
This is an overview session. The session will go through IT Pro-related features in SharePoint 2010. It will cover the new IT Pro experience including deployment and upgrade, management and operation, backup and restore, availability and scalability.

Growing SharePoint from Small Libraries to Large Scale Repositories and Massive Archives
SharePoint allows you to store many documents in an environment and end users will want to store, find and retrieve documents and information quickly and easily. In this session, a lot of new SharePoint 2010 features will be demonstrated that will help them do just that. Expect to see enterprise content types, the term store, managed metadata, records management and lots more!

Upgrading your SharePoint 2007 solutions to SharePoint 2010 (this one was a duo presentation with Serge van den Oever)
With SharePoint 2010 a lot will change for developers, but of course you will still want to keep using some of the cool solutions that you built for SharePoint 2007. This session will explain how you can best upgrade the custom code that you wrote using Visual Studio 2008 for SharePoint 2007 to work with Visual Studio 2010 and SharePoint 2010. The session will demonstrate the project upgrade tools, show you the APIs that are involved in migrating and what new APIs you should consider using, and will tell you when you will be bound to have to upgrade your code manually.

Thanks to the SharePoint Connections crew and Microsoft Netherlands for putting up a good show and thanks everyone that attended for showing up and making the conference a success. I’m looking forward to seeing you all there next time!

Free eMagazine on SharePoint 2010

2010-01-18T22:24:28Z

The Dutch Information Worker User Group (DIWUG) and the Software Development Network (SDN) produced a free SharePoint eMagazine with a lot of interesting articles on a wide variety of topics from an impressive group of international authors.

The SharePoint 2010 Enterprise - Sjoerd van Lochem
Customizing the SharePoint Ribbon - Marianne van Wanrooij
Visual Studio extensions or a manually build solution? - Niels Loup
Working With Data in SharePoint Designer 2010 - Laura Rogers
Sandboxed Solutions in SharePoint 2010 – Mirjam van Olst
New ECM features in SharePoint 2010 – Robert van Son
Creating new Visual Experiences with Visio Services - Toni Frankola
How to guides - Visio Services 55
A SharePoint User eXperience – Sandra de Ridder
Introduction to the Business Connectivity Services – Nick Swan
SharePoint 2010 Client Object Models – Ton Stegeman
Understanding Identity in SharePoint 2010 - Michiel van Otegem
Happy Together in 2010 - Dux
SharePoint 2010 Chart Web Part - Agnes Molnar
Enrich your SharePoint 2010 portal by integrating SAP applications - Cyrille Visser, Johan Kroese and Huub Montanus
SharePointComic – Dan Lewis

The magazine can be downloaded in high-res or for use on ereaders here.

General PowerShell tips for SharePoint gurus and PowerShell newbies

2009-12-19T21:01:56Z

If you’ve been working with SharePoint for a while and you’ve been happily coding and clicking and STSADM-ing away you need to get skilled up in writing PowerShell scripts as well now.

In my last post I described my first useful PowerShell script. While writing that I bumped in to a couple of things that took me a while to figure out, so I decided to list them in this post to try and save others some time.

The first one is loading another PowerShell file in the current script. This can be useful if you have to load an existing file into your script, like SharePoint.ps1, but it can also be useful if you want to save parts of your script to separate files so you can reuse the different pieces more easily.
Loading a file into a PowerShell script can be done by typing .\ followed by the filename. If the file you want to load is stored in a different directory than the file your currently working on you need to tell PowerShell what directory to look in by using the cd command that should be familiar from the old school command prompt.

    cd 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\POWERSHELL\Registration'
    .\SharePoint.ps1

Another nice little trick is creating a variable of the type Array. The first thing you probably noticed is that PowerShell variables aren’t declared. They are just created on first assignment. Something else that stands out is that all variables start with a $. PowerShell variables are polymorphic by default. This means that you can store any type of object in them.In my script I assign strings to the variable, separated by commas. This will get us and array of strings. If I would have assigned numbers separated by commas it would have been an array of integers.

    #An array of strings
    $users = "john", "paul", "mirjam"

    #An array of integers
    $users = 5, 6, 7

In the above example you can see that you can write comments by putting a # in front of the text.

The next tip was also explained in my last post. If you put a string in PowerShell in single quotes it is seen as a “Literal string”. This means that the string will be represented exactly as it is displayed within the quotes. A string in double quotes is an “Expandable string”. So a string in double quotes can contain variables and special characters such as newlines and they will be expanded when you run the script.

    Suser = "Mirjam"

    #String in double quotes
    Write-Output "Site created for $user" 
    
    #String in single quotes
    Write-Output 'Site created for $user'

The output for the above script will be:
Site created for Mirjam
Site created for $user

This doesn’t only work when writing stuff to the screen, it also works in expressions.

The next question of course is how do you know what PowerShell commands are available to you if you want to manage SharePoint using PowerShell.
A very good place to start is here http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=045f7af5-b226-4a05-8ace-4e17cfdef856&utm_source=feedburner&utm_medium=feed&utm_campaign=Feed%3A+MicrosoftDownloadCenter+%28Microsoft+Download+Center%29. This page offers links to downloads of .chm files that describe the PowerShell cmdlets for SharePoint.
You can also get some help from the PowerShell console itself. For instance if you know that the command for creating a new site is New-SPSIte, but you don’t know what parameters you can use on that particular object, you can call the PowerShell help. It doesn’t matter whether you use capitals or not, PowerShell cmdlets are not case-sensitive.

	get-help New-SPSite

This line of script will return the following information:

NAME
    New-SPSite

SYNOPSIS
    Creates a new site collection at the specified URL.

SYNTAX
    New-SPSite -Url <String> -OwnerAlias <String> [-AssignmentCollection <SPAssignmentCollection>] [-Confirm [<SwitchParameter>]] [-Con
    tentDatabase <SPContentDatabasePipeBind>] [-Description <String>] [-HostHeaderWebApplication <SPWebApplicationPipeBind>] [-Language
     <UInt32>] [-Name <String>] [-OwnerEmail <String>] [-QuotaTemplate <SPQuotaTemplatePipeBind>] [-SecondaryEmail <String>] [-Secondar
    yOwnerAlias <String>] [-SiteSubscription <SPSiteSubscriptionPipeBind>] [-Template <SPWebTemplatePipeBind>] [-WhatIf [<SwitchParamet
    er>]] [<CommonParameters>]

DESCRIPTION
    The New-SPSite cmdlet creates a new site collection with the URL and owner specified by the Url and OwnerAlias parameters.

A PowerShell script to automate the creation of SharePoint site collections

2009-12-17T15:06:03Z

I must admit it. I’ve never really done anything with PowerShell.
Sure, the Macaw Solutions Factory is largely based on PowerShell, but I usually just use that as-is, or make minor changes to existing scripts.
So starting to work with PowerShell for SharePoint is all new. I read a PowerShell book over the summer holidays to get a little bit of an understanding about PowerShell, but as everyone knows, reading about it is very different from actually using it and doing it hands on. So last week I took my first baby steps in using SharePoint and PowerShell. If you’re a seasoned PowerShell user this post is probably not for you, but if you are, like me, just starting to get familiar with PowerShell you might find the information in here useful.

The goal was to create site collections for everyone in our department. The urls of the site collections are the first names/account names of my team members and of course I didn’t want to type in the same lines 25 times.

  1 $snapin = Get-PSSnapin | Where-Object {$_.Name -eq 'Microsoft.SharePoint.Powershell'}
  2 
  3 if ($snapin -eq $null)
  4 {
  5     cd 'C:\Program Files\Common Files\Microsoft Shared\Web Server Extensions\14\CONFIG\POWERSHELL\Registration'
  6     .\SharePoint.ps1
  7 }
  8 
  9 $users = "john", "paul", "mirjam"
 10 
 11 foreach ($user in $users)
 12 {
 13     New-SPSite -Url "http://iws.macaw.nl/sites/$user" -OwnerAlias "DOMAIN\$user" -Name "Portal" -Template "STS#0" 
 14     Write-Output "Site created for $user" 
 15 }

Before we can use the SharePoint PowerShell cmdlets we need to load the Microsoft.SharePoint.PowerShell snapin. We can accomplish this by loading the SharePoint.ps1 file or by adding the directly. If you try to add a snapin that is already added PowerShell will throw an error telling you that. Even though the script will continue after throwing the error without problems I didn’t like seeing the error, so I build in a test that checks if the snapin was already added. There might be a better way to test this, but I couldn’t find one, so I ended up filtering the collection of registered snapins on snapins with the name “Microsoft.SharePoint.PowerShell”. If the resulting collection contains an item (has more than 0 items in it) the snapin is already registered.

[Update]: I adjusted the “if” statement according to the statement Leon posted in the comments (thanks Leon!). This test first gets the snapin where the name of the snapin equals Microsoft.SharePoint.PowerShell. It adds the results to the @snapin variable. Next the if statement checks whether there is actually a snapin in the @snapin variable, or whether it is NULL. As you can see in Leon’s script you can also choose to add the Microsoft.SharePoint.PowerShell snapin directly, but there is some other magic in the SharePoint.ps1 file and since I didn’t know what that was for I decided to just load the .ps1 file completely.

Next I define an array of users. There are far more spiffy ways to do this, like for instance reading the names from a file, but I decided that this would do for now.

Now we can loop through all users in the array and create a new site for each user. The urls of each site are http://iws.macaw.nl/sites/[[username]], so the url of my site is http://iws.macaw.nl/sites/mirjam. Note how I use double quotes around the url. Because I use double quotes PowerShell replaces the variable name $user by its contents and uses that in the string. If you use single quotes PowerShell will leave the string alone and the url would be http://iws.macaw.nl/sites/$user. The same principle is used for the owneralias and the output stating that the site for this user was created.

I hope this helps people. If you have improvements or additions to the script feel free to post them in the comments!

Site Locale and Calculated Columns in SharePoint 2007

2009-11-25T17:29:21Z

I was looking into a problem with a calculated column in a SharePoint 2007 site.
On one environment the formula worked fine. On another, it kept throwing “The formula contains a syntax error or is not supported” errors.
The formula does something like:

=IF([Status]="Done",[Date Done]-[Due Date],"")

Because I got this great error that told me exactly what was wrong I started troubleshooting by stripping the formula to eliminate possible causes till the formula looked like:

=IF([Status]="Done",1,2)

This still generated the same error message.

After searching for a while I found the following post:
http://www.hezser.de/blog/archive/2008/10/12/programmatically-creating-a-spfieldcalculated.aspx

In this post by René Hézser mentions the fact that he had a problem with an IF statement in a calculated column because his site locale was German. So I went back to check and indeed, on the environment where the formula threw the error the locale of the sites was Dutch. Changing the locale to English solved the problem, so with that the cause of the problem was clear. Unfortunately the locale of the environment is Dutch for a reason and the customer doesn’t want an English locale on their sites.

But not to worry, after all, all I had to do was find out what the Dutch equivalent of =IF([Status]="Done",[Date Done]-[Due Date],"") was. After doing a Bing search I found out that the Dutch equivalent of IF in Excel is ALS, so I tried the formulas =ALS([Status]="Done",[Date Done]-[Due Date],"") and =ALS([Status]="Done";[Date Done]-[Due Date];""), which both didn’t work.

When I was about ready to give up I decided to change the site locale back to English, fill in the original formula and change the locale back to Dutch. Brilliant brainwave…after opening up the site column again the formula changed to:

=IF([Status]="Done";[Date Done]-[Due Date];"")

So, if anyone is looking for the Dutch, or Nederlands, or 1043 (just trying to help out people searching for this here) equivalent of the IF statement in SharePoint 2007 it’s IF, but it uses ; instead of ,.

Public betas of SharePoint 2010 and Office 2010 released

2009-11-18T18:04:55Z

Today at the PDC in Los Angeles Kurt DelBene has announced the release of the public betas of SharePoint 2010 and Office 2010.
The betas can be downloaded at http://bit.ly/ZC2010 and include:

SharePoint Server 2010
Office 2010
Visio 2010
Project 2010
Office Mobile 2010
Office Web Applications

There are two things that are new to these releases (apart from a s**t-load of fixes compared to Technical Preview versions).

The Outlook Social Connector feeds information about people directly into Outlook. Integration with SharePoint 2010 is out-of-the-box, user profile information, a contact’s photo and information from the My Site of the author is shown when an email is selected. The social connector also comes with an SDK allowing developers to build connectors with third party social networks. The LinkedIn connector (build by LinkedIn) is expected to be released early next year.
Office Mobile 2010 public beta is part of the Microsoft campaign to deliver a great user experience of Microsoft products across the PC, phone and browser and offers a version of Office optimized for use on Windows Mobile 6.5 phones.

Personally I’m most interested in the SharePoint 2010 beta. If you are too, make sure you read these guidelines by Jie Li before you start installing!
The most important fact in there is that, at the moment, SharePoint 2010 doesn’t work on Windows Server 2008 R2 without a hotfix. Unfortunately the hotfix hasn’t been released yet, and without it the SharePoint Service Applications won’t work. This means that for the moment, you have to choose between waiting for the hotfix to become available and installing SharePoint 2010 on Windows Server 2008 SP2 for now.

Having said that, there are plenty of reasons to install SharePoint 2010 and start playing around with it.
Some of the coolest features are:

Document sets
Enterprise wide metadata
Lots of new search features (even without the FAST integration)
Great new development tools in Visual Studio 2010 for SharePoint 2010 (and I do mean great!)
Social networking with, among other things, tagging, rating and improved people search
Off-loading blob storage using SQL Server Remote Blob Storage (RBS)
New client object models
Business Connectivity Services, this is the old 2007 Business Data Catalog, that has had a major makeover and that is improved hugely

And many other cool new features…

Have fun!

SQL Server essentials for SharePoint (1)

2009-11-16T11:46:48Z

SQL Server is an essential part of every SharePoint environment. Since the vast majority of SharePoint data is stored in a SQL Server database your SharePoint environment can never be faster than the speed at which SQL Server can handle the requests. Unfortunately most people installing and maintaining SharePoint databases aren’t trained SQL administrators. They usually are SharePoint administrators, or even SharePoint developers (like me). This means that on a lot of SharePoint environments performance isn’t what it could be, due to poor or nonexistent SQL Server management.
In this series I will list a few simple things that you can do to get more out of your SQL Server and SharePoint environment. I won’t explain the why of every tip, but I will focus on the how you can make your SharePoint environment faster and more stable by improving your SQL Server management. Some of the tips and tricks will only be relevant if you have multiple servers and a large budget, but other tips can be beneficial to even the smallest installations. The series is not intended to be the last word on SQL Server for SharePoint, but rather useful information, hints and tips for getting more out of your database platform.

This first post will talk about things you do before and while installing SQL Server.

Installing SQL Server

The first pieces of guidance is very straightforward and needs to be considered even before you start installing SQL Server.

For a production environment you should always run SQL Server on a separate physical server. Single server environments (where SQL Server and SharePoint are installed on the same server) can be used as development or test environments, but are not suitable for production and user acceptance environments.
Another tip for production and user acceptance environments; make sure you have at least 4GB of memory per processor in your SQL Server box.

Understand that SharePoint uses several different databases and that they all have different purposes and uses.

Configuration Database (Config DB).
Stores information about your SharePoint environment like server names and Web Application URLs and a site collection list with names and URLs. This database isn’t used very heavily during normal operations.
Content Databases (Content DB).
There is at least one Content Database per Web Application. Stores all data about site collections, sub sites, list, libraries and items. Data stored in the environment by end users ends up in (one of the) content database(s). In a collaboration environment content databases should be optimized for writing, in a web content management environment content databases should be optimized for reading.
Central Administration Content DB.
Stores content for the Central Administration Web Application. It is generally not recommended to store large amounts of data in here (e.g. TechNet documents).
Shared Services Provider Database (SSP DB).
Stores configuration data for the Shared Services Provider (there is one DB per SSP), and also all User Profiles, Audiences, Excel Trusted File Locations etc.
Shared Services Provider Search Database (SSP Search DB).
Stores crawled and managed properties identified and indexed by MOSS search. This database also stores information about which users have access to what content. Depending on your crawl schedule and the use of your environment the use of this database can be both read and write intensive. Note that the content index is *not* stored in this database but rather on the file system of the Index and Query servers.
SSP Admin Content Database (SSP Admin Content DB).
Stores content for the SSP Administration Web Application (there is one DB per SSP). It is generally not recommended to store large amounts of data in here, or to use the SSP Web Application and database for hosting My Sites.
Windows SharePoint Services Search Database (WSS Search DB).
This database stores the full text index of content in a WSS only deployment. In the case of MOSS only the full text index of the SharePoint help files are stored in the WSS Search DB. This database is not required in a MOSS deployment, unless help search is needed.
Single Sign On Credential Store DB (SSO DB).
Optional is using the MOSS SSO capability. Stores the credential cache for SSO applications.
TempDB.
TempDB is SQL Server System database that is heavily used by SharePoint as an intermediate database for writing information to any of the SharePoint databases. Because of this, TempDB is very read and write intensive. A slow TempDB will cause all write actions to all SharePoint databases to be slow as well.

RAID

Next you need to start thinking about the appropriate RAID configurations for the disks you’re going to store your SQL Server installation and databases on.
RAID (Redundant Array of Independent Disks) is a disk system that contains multiple disk drives, called an array, to provide greater performance, reliability, storage capacity, and lower cost. There are several different types of RAID arrays and each type is called a level. Each level uses a different algorithm to implement fault tolerance. The most common RAID levels are 0, 1, 5 and 1+0, which are also the levels that are used for SharePoint.

RAID 0 is also known as disk striping. Two or more disks are used for a RAID 0 configuration. All data and programs that are installed on a server using RAID 0 are divided in blocks and are equally distributed across all disks. This means that RAID 0 improves the read and write performance and uses all available disk capacity. A downside is that there is no fault tolerance. If one of the disks crashes you lose the data that was stored on it.

RAID 0

RAID 1 is called disk mirroring because all disks have an identical mirror disk. All data that is written to the primary disk is written to the mirror disk as well. This means that RAID 1 provides fault tolerance. It also means that effectively you can only use half of your disk capacity for storage and that write actions will be slower as data has to be written to two locations, instead of just one location.

RAID 1

RAID 5 is known as striping with parity. When using RAID 5 you will need at least three disks. All information is split in blocks, where half of each block is stored on a certain drive, and the other half of that block is stored on another drive. The third drive stores the parity information. If one of the first two drives fails the parity information can be used to reconstruct the missing data. This means that RAID 5 provides fault tolerance without storing all information twice. The downside is that you need at least three disks and that writing information is slower, because the parity has to be adjusted every time data is written to a disk.

RAID 5

RAID 1+0 or RAID 10 is also called mirroring with striping. RAID 10 uses a striped array of disks like the one used for RAID 0 and then mirrors that like in RAID 1 (hence the name, RAID 1+0). For example if you have a striped array of two disks than that set of disks is mirrored on two other striped disks. RAID 10 provides both good reading performance and fault tolerance. The downsides of RAID 10 are that you need at least four disks, that you can only use half of your disk space for storage and that writing will be a bit slower because all data gets stored twice.

The more physical volumes you have, the better it is. If you can separate anything out onto separate disks, you should do so in the following order:

The first disk can be RAID 1 and will be used to store the operating system and SQL installation files on. If you only have 1 disk, don’t let it be RAID 1, but try and make it RAID 10 or 5.
If you can separate one thing out from the rest make it TempDB, as SharePoint writes to and reads from TempDB all the time. Your SharePoint installation can’t be faster than your TempDB. The disk that you store TempDB on should be a RAID 10 disk
If you have another disk, use this one for the SQL transaction logs and make this disk also a RAID 10.
In the case you have a fourth disk use this one for the SQL data files. This one should also be RAID 10. If your environment is read intensive, like an Internet presence site, where not a lot data is written to SQL you should separate out the SQL data files before the transaction log files, as the transaction log is only used when data is written to SQL and not when data is read from SQL.

Should you be in the lucky situation where you have more than four disks separate out the log and data files for the separate databases. Start by separating out the search database and the content databases, go for the SSP database after that.

If you have a choice, use more smaller and faster drives, instead of having less larger drives.

If you have to prioritize among faster disks use the following ranking order:

SQL TempDB data and transaction log files
Database transaction log files
Search database
Content databases

While installing SQL

While you are running the SQL Server install wizard make sure you check the following things:

You need to install the SQL Agent
Select windows authentication (SQL authentication is evil)
When choosing collation settings select LATIN1_General_CI_AS_KS_WS
- Accent-sensitive
- Kana-sensitive
- Width-sensitive
Go to the Surface area configuration and click DB Engine. Make sure that remote connections are allowed
Select TPC/IP and Named Pipes
Configure SQL Server and SQL Server Agent using a regular Domain Service Account, do not use and Administrator or your SharePoint Farm Account as the SQL Server Service Account
Double check to make sure that the SQL Agent starts automatically
You don't need the browser, so don’t install it
Also don’t install Analysis Services or Reporting Services on the machine(s) hosting your SharePoint databases
Don’t install any other components that you don’t need
Restart SQL Server service

Database sizing

A database is build up out of several smaller components.

Databases consists of filegroups
Filegroups consist of files
Files consist of extents
Extents consist of pages
Pages consist of data

The size of a page is always 8kB and since an extent consists of 8 pages an extent is 64kB. Because of this you should format your RAID array using 64kB blocks.

To get an optimal performance and to minimize fragmentation you should always pre-grow your data and log files. If you are unsure of the total amount of space you need to allocate for a database or a log it’s ok to over-allocate. Even if you have pre-allocated space you need to leave auto-growth on, just to make sure that your SharePoint environment doesn’t grind to a hold should your database reach the size you pre-grew it to. Do set auto-growth to fixed amounts, not to percentages, to prevent your database from growing out of control. Keep monitoring your SQL environment to make sure that the size of your data and log files doesn’t get out of hand and that your hard drive doesn’t run out of space.

If you pre-allocate space for you log files the most ideal way to do this will in most cases be by allocating 8GB at the time. If your database is smaller than 8GB you probably will want to grow your log files by using smaller amounts.

Other advice

Your database server is the least ideal candidate of all SharePoint server types to be virtualized because SQL has the highest amount of disk I/O activity and can often have high memory and processor requirements.

The biggest database throughput killers are:

TempDB performance
Slow disk IO. The advice is to use at least 15,000 RPM disks
Inadequate memory. This will prevent SQL Server’s ability to cache data.

[Dutch] SDN Event in Houten op 14 december

2009-11-12T23:35:54Z

Op 14 december is er weer een SDN event in Houten en we hebben vier hele interessante presentaties over SharePoint 2010!

Spreker	Titel	Omschrijving
Ton Stegeman	Aan de slag met de SharePoint 2010 Client object modellen	De komst van SharePoint 2010 brengt voor ontwikkelaars een aantal interessante nieuwe ontwikkelingen. Een daarvan is het beschikbaar komen van drie nieuwe object modellen. Hiermee krijgen ontwikkelaars de beschikking over een SharePoint object model op de client. Dit maakt de ontwikkeling van SharePoint client applicaties een stuk eenvoudiger. Bovendien schept het nieuwe mogelijkheden voor software ontwikkeling voor hosted SharePoint omgevingen als SharePoint Online. Er is een .NET managed SharePoint API, om .NET applicaties te bouwen. Daarnaast is er een object model voor Silverlight en is er een javascript library. In deze sessie zal Ton Stegeman een aantal voorbeelden laten zien van ieder object model. Daarbij zal hij laten zien hoe het onwikkelen met een client API verschilt van het onwikkelen op basis van het server object model. De voorbeelden bevatten een aantal praktische tips, die je helpen na de sessie zelf snel aan de slag te gaan met deze interessante nieuwe ontwikkeling.
Robert Jaakke	Sandboxed Solutions	SharePoint 2010 heeft een nieuw deployment model genaamd Sandboxed Solutions. Sandboxed Solutions zorgen er voor dat de risico’s die custom code met zich mee brengen geminimaliseert worden. De Farm Administrator kan dit beheren door de beschikbare API’s te beperken en resources quota’s op te leggen. Door deze maatregelen kunnen Sandboxed Solutions ook goed in een shared hosting omgeving gebruikt worden. In deze sessie bespreek ik wat Sandboxed solutions zijn, hoe je ze maakt en wat de geavanceerde technieken zijn om het maximale er uit te halen.
Joran Markx	Het SharePoint 2010 Developer Platform	Deze sessie geeft een overzicht van de nieuwe mogelijkheden voor developers in SharePoint 2010. Er zal ingegaan worden op het bouwen van applicaties bovenop het SharePoint platform met behulp van de nieuw beschikbare tools in Visual Studio 2010. In deze sessie zal er met codevoorbeelden en demos worden gekeken naar de nieuwe features voor het bouwen van user interfaces, data access en andere programmeerfunctionaliteiten.
Andries den Haan	Social innovations for knowledge sharing in SharePoint 2010	Review van de social capabilities in het SharePoint 2010 platform in de context van het kennisdelingsprogramma bij Getronics Consulting. De sessie bestaat uit 2 delen: • Toelichting op het kennisdelingsprogramma (achtergrond en historie, visie en doelstellingen, status, lessons learned) • Innovaties in SharePoint 2010 tbv kennisdeling en networking: O.a. aandacht voor innovaties van de enterprise wiki’s, activity feeds (extensibility), profiles en social tagging, social search Als onderdeel van de sessie zal een demo worden gegeven van OOB functionaliteit in het nieuwe platform .

Schrijf je in op de SDN website, dan zie ik je de 14de in Houten!