Radi Atanassov

SharePoint MCM, MVP, MCT and owner of OneBit Software

Unit Testing SharePoint 2010 with MSTest

Most have figured out by now that Visual Studio 2010 and SharePoint 2010 have offered much better development stories compared to SP 2007 and VS 2008. Unit testing however, seemed to be worse off. MSTest projects only support .NET 4, and if a Visual Studio solution is set to .NET for, the SharePoint assemblies won’t work at all. You will get a System.IO.FileNotFoundException if you try to get reference to an SPSite. (There’s really good reasons why it is a FileNotFoundException, but thats for another post.)

This resulted in many loud cries in the industry…

With the release of Visual Studio 2010 SP1, along came a resolution to this problem. MSTest projects now support .NET 3.5, which lets us add reference to SharePoint assemblies and execute the test host in an x64 process.

Here’s how to do it:

1) Create a new Test project in your solution. Make sure you select the .NET 3.5 Framework option. Take note of the location the template is in, not sure what Test Documents is Smile


2) Go to the Properties of the newly created project and confirm that the Target framework is .NET 3.5


3) Set the Build - Platform target to “Any CPU”. This is important, it doesn’t work when explicitly set to x64, I haven’t got around to figure out why.


4) Go to the Test menu at the top, select Edit test settings, and choose local settings:


5) Click the Hosts option and configure the environment to run under x64 bit:


5) Add references to you SharePoint assemblies and write some unit tests. They should run fine:


Great stuff from the VS team. This still doesn’t solve the stubbing requirements though. Pex and Moles and TypeMock are still the way to go.

Another major functionality that is still missing is Load Tests in .NET 3.5 mode, still can't do them :(

Hope this helps!

SharePoint Forums - Why they matter

WARNING: "How cool am I?" post.

I've always wanted to spend some time on the SharePoint forums, just to see the fun and benefits of being a "regular".

I put some initiative into it and, I have to say, the forums are more than what I expected.

Here is why I think the forums are important for the community and every SharePoint expert out there:

  • When preparing an answer, you think about the issues and concepts around the asked question. This educates you. I answer to people, and when I do so I look over my notes on the topic. I have a huge arsenal of material on SharePoint concepts from which I pull out info and I always look up on a search engine to find anything recent. Going over knowledge and blogs is a learning experience. The end result - I am a better SharePoint expert.
  • You see trends of problems. This helps you understand common misconceptions so you can address them through blogs or open-source solutions. You see the biggest problems of the product, and the most often encountered headaches. You end up knowing more about your ecosystem.
  • You help people and they say thanks. And this makes you feel good. It also raises your profile, which is important for the self-development of everyone.
  • And of course, you get the opportunity to ask a question. If you are lucky, someone might even answer within minutes. No support ticket could do this for you.

A screenshot from my activity (taken today):


Which forums do I like? I'm currently a big fan of SharePoint 2010 General Questions and Answers as the questions cover everything and anything. People seem to ask all kinds of stuff here, configuring, development, architecture, search , PowerShell, you name it. I also poke around the others, but that one is my favorite.

How much time does it take? Not much - I answer questions while I wait for something else. Either a person, a task, a system job or an email. Answering a question takes a few minutes if you know the answer. What could cost you time is researching a problem, but hey, that is my job. I should know, so I don't mind looking into issues or concepts that I don't know the answer to. Either way, it is time which I am happy to give.

Last point - people ask me questions ALL the time. I always give them the answer, but ask them to post the question to the forums so everyone could benefit from my answer.

So don't think about it, post a question :)

See you on the forums!

I am a Microsoft Certified Master: SharePoint 2010

I have a big announcement to make. Since December 2010 I am officially a Microsoft Certified Master in SharePoint 2010. It took me more than a month of full-time pre-study, 3 weeks spent in Microsoft's Main Campus in Redmond doing training, 45 days post-rotation study, many hours of hard work, very little sleep, a few white hairs, plenty of SharePoint nightmares, countless coffees and a hell of a lot of energy and enthusiasm. I prepared myself for the challenge, I gave it my absolute best, got A LOT out of it and made it to the end.


There's lots to tell about the experience and the specifics of what a Master is, how one could help, and what responsibilities a Master has. I'll be speaking in the near future about it at the local SharePoint User Group (in Sofia, Bulgaria), but I'll also be sharing some experiences here in my blog.

A few links:





See you out there on the field!

Слайдове и код от SharePoint ITPRO семинара в New Horizons

Много съм доволен, че се събрахме толкова много хроа за ITPRO сесията, залата беше добре препълнена и онлайн имаше 15-20 човека.

Ето и презентацията, както и разни скриптове, които обсъждах в материла:  SharePointITPRO.zip

В скоро време ще разгледам и възможностите да кача записите. Трябва да намеря начин да превърна LiveMeeting формат в AVI или WMV.

До скоро!

SharePoint 2010 Search: Some Managed Properties cannot be included in queries

A colleague today showed me a problem he was having with full text search queries. All he was doing is including the ContentType managed property in the search query, which returned a Microsoft.Office.Server.Search.Query.InvalidPropertyException.

The reason for this is that the Content Type field is now marked as non-retrievable. The ManagedProperty class has a “Retrievable” property, which according to this MSDN article, specifies whether it can be used in queries: microsoft.office.server.search.administration.managedproperty.retrievable.aspx

According to this blog post, it used to be retrievable in SharePoint 2007. Definitely a gotcha - most people would think this is possible, and most may also sell it in a client meeting.

The problem is that you cant set it to true… I currently have no solution for this, but I thought I might post a list of ALL managed properties (in a default installation with very few content) that have Retrievable set as False.

PowerShell script to check properties:

Get-SPEnterpriseSearchMetadataManagedProperty -SearchApplication "Search Service Application" | where {$_.Retrievable -eq $false}



OK so I looked into it further, I tried setting it in the Search SA to be included in all results, but that didn’t work:



And adding it in the advanced search web part also doesn’t work:


The query will return no results. Trying to set the property to True in PowerShell or the OM still gives the same error.

So, the only workable solution that is flying around is the one posted on the MSDN forums. It still, however, is a workaround that can’t be used in ALL situations out there. This is not great, as one would think the ContentType field is something you would want to include in your search results.

Please let me know if you find any other solutions.

Developing custom Health Rules for SharePoint 2010

What are health rules?

SharePoint 2010 has a new feature called the Health Analyzer and it has Health Rules. Each rule checks a particular metric within the SharePoint farm, and Central Administration provides an interface to see problems, warnings, and rule definitions. Administrators can subscribe to rules so they get alerts when the rule fails. Administrators cannot create custom rules, but developers can.

This is what the interface looks like:


Why should architects and developers consider them?

Administrators might ask you to create rules to monitor specific things on a server.

Further to that, developers can create rules to monitor their own solutions. Certain aspects of a solution might be problematic and require attention after time - for example a growing list. A custom rule can monitor this list, and notify certain people if a threshold is being passed.

High-Level steps to create a custom Health Rule:

  1. Create a class that inherits from Microsoft.SharePoint.Administration.Health.SPHealthAnalysisRule
  2. Override the required methods in SPHealthAnalysisRule. Check() is where the action happens, but also pay attention to SPHealthAnalysisRuleAutomaticExecutionParameters
  3. Create a feature to register the rule definition with SharePoint
  4. (Optional) use a Console Application to test your rule code while developing it. This is much more convenient when you need to debug with Visual Studio 2010.

Example Solution:

I have put together a template you could reuse to build custom health rules. My example pings each database server from each WFE and fails if the ping is greater than 1ms. Download the code from the bottom of the post.

Here is my schedule and scope configuration (see notes below for more details):

  1. public override SPHealthAnalysisRuleAutomaticExecutionParameters AutomaticExecutionParameters
  2. {
  3.     get
  4.     {
  5.         SPHealthAnalysisRuleAutomaticExecutionParameters execParams =
  6.             new SPHealthAnalysisRuleAutomaticExecutionParameters();
  8.         execParams.Schedule = SPHealthCheckSchedule.Hourly;
  9.         execParams.RepairAutomatically = false;
  10.         execParams.Scope = SPHealthCheckScope.All;
  11.         execParams.ServiceType = typeof(SPWebService);
  13.         return execParams;
  14.     }
  15. }

I use the following code to get all Database servers:

  1. //Get the Database Service
  2. SPDatabaseService spdbservice =
  3.     SPFarm.Local.Services.GetValue<SPDatabaseService>();
  5. //Get all instances of the Database Service
  6. SPServiceInstanceDependencyCollection dbServices = spdbservice.Instances;
  8. //fail the test if no DB servers are found (network is dead)
  9. if (dbServices.Count == 0) return SPHealthCheckStatus.Failed;
  11. //The following will enumerate all instances so we can get the servers hostname
  12. foreach (SPDatabaseServiceInstance instance in dbServices)
  13. {
  14.     SPServer dbServer = instance.Server; //each of these is a DB server
  16.     if (!PingServer(dbServer.Name)) //ping it
  17.     {
  18.         failedPings++;
  19.     }
  20. }

And here is my ping method:

  1. private static bool PingServer(string serverHostName)
  2. {
  3.     bool returnValue = false;
  5.     Ping dbPing = new Ping();
  6.     PingReply reply = dbPing.Send(serverHostName);
  8.     if (reply.Status == IPStatus.Success)
  9.     {
  10.         long latency = reply.RoundtripTime;
  12.         if (latency <= 1) { returnValue = true; }
  13.     }
  15.     return returnValue;
  16. }

Here is the Feature Receiver code to register the rule:

  1. public override void FeatureActivated(SPFeatureReceiverProperties properties)
  2. {
  3.     Assembly currectAssembly = Assembly.GetExecutingAssembly();
  5.     try
  6.     {
  7.         SPHealthAnalyzer.RegisterRules(currectAssembly);
  8.         //SPHealthAnalyzer.UnregisterRules(currectAssembly);
  9.     }
  10.     catch (Exception ex)
  11.     {
  12.         throw new Exception("Registering Health Rules from "
  13.             + currectAssembly.FullName + " failed. " + ex.Message);
  14.     }
  15. }

The commented out line lets you unregister the rule. You should do this in the Deactivating method.

Important Points in configuring the scope and schedule of you rule:

  • The ServiceType property - this lets you specify a particular SharePoint service that is required on servers that are to run this rule. For example, you can use this property to specify that your rule should only run on machines running Excel Services.
  • The Scope property - this defines whether the rule will run on ALL servers, or ANY servers. If set to ANY, it will run on the first server that is running the SharePoint Service specified in the ServiceType property. If set to ALL, it will run on all servers running that service.
  • In my example I am pinging the DB servers from ALL servers running the SharePoint Web Service (web front ends). Any application server not running this service will not fire this rule. You might want to change this to SPTimerService or whatever suits your needs.

Other things of interest:

  • Central Administration has a “HealthRules” list, which has it’s own List Template type (SPHealthRulesList).
  • The SPHealthAnalyzer object maintains this feature.
  • The AddItem method of the SPHealthRulesList does two things: Creates an Item in the List, then registers an SPHealthAnalyzerJobDefinition Timer Job. There is one of these for each schedule, service and scope. This can be seen in the Timer Job definitions page:


  • The implementation of this can be seen in the AddItem method with Reflector”


Further links:

Download source code: Community.SharePoint.zip (you must click the download button on the Live page...)

Hope this helps !

Troubleshooting Permissions with Excel Services

I was setting up Excel Services with Kerberos following the steps of this great whitepaper:

Configuring Kerberos authentication for SharePoint 2010 Products

I did everything fine, except I missed a step a while back when initially configuring the farm with least privileged accounts. This led to an interesting permission troubleshooting exercise, so I thought it might be interesting to share.

It started of with a frequent error when opening an Excel document with a connection to a database on an SQL server. The error: The workbook cannot be opened.



First place to look: the grand ULS logs:


The error was obvious, the Excel Service Application service account didn't have access to the Content Database of the web application. But hang on, I was confident I allowed it access via the PowerShell script described in the document:

$webApp = Get-SPWebApplication -Identity https://claims.mcm.com


So I did it again:


Naturally, the next step would be to check the DB's, and to my surprise there was no "SharePoint_Shell_Access" assigned to the svc_excel user. In fact there was nothing in there for him...

PowerShell didn't indicate any errors, but ULS clearly did:


The user running the PowerShell script (mcm\sharepointowner), which is also the SP Farm Account, didn't have access to the DB. But more specific, I must have forgot to give that user PowerShell access in this particular SharePoint farm. I'm sure I did it, but it must have been on another farm (my lab is running 3 farms).

Most of you will have guessed the solution by now - run the PS Add-SPShellAccess command as I should have during the install of the farm:

Add-SPShellAdmin -Username "mcm\sharepointowner" -Database (Get-SPContentDatabase -Identity "WSS_Content_ClaimsDB")

Here's a PS screenshot:


Running the GrantAccessToProcessIdentity() goes ahead again, but this time the ULS logs appropriately show success:


It is worthwhile to point out how the command adds "mcm\sharepointowner" to WSS_ADMIN_WPG and gives "SharePoint_Shell_Access" to both the Config and Admin Content DB's on top of the Content DB.

Hope this helps!

Step-by-Step Guide to Configuring Database Mirroring in SharePoint 2010

I was looking up some material on database mirroring, and I could find a good step-by-step post on setting up such functionality. So here it is!

Read these posts before you begin - you should really understand the concepts if you are to undertake this, especially in a production environment.




My SQL instances are based on the SQL Server 2008 R2 Standard SKU, but they we're upgraded from SQL Express 2008 R2, hence the sqlexpress names:

Mcm-server-1\sqlexpress (Primary) & Mcm-server-2\sqlexpress (Mirror)

SQL Service Account: mcm\svc_sql

Database to sync: WSS_Content_ProductionDB

For mirroring to work, the versions of SQL must be the same on both servers.

1. Configure permissions on the mirror server according to the details from the technet article:

  • The Central Administration application pool account should be a member of the dbcreator and securityadmin fixed server roles.
  • All application pool accounts, the default content access accounts, and any accounts required for service applications should have SQL Server logins, although they should not be assigned to SQL Server fixed server or fixed database roles.
  • Members of the Farm Administrators SharePoint group should also have SQL Server logins and should be members of the same SQL Server roles as the Central Administration application pool account.

2. Ensure that no firewall is blocking connectivity between the servers. In particular, you will create a TCP connection over a port, by default 5022.

3. Make sure that the database is in the Full recovery model


4. Perform a FULL backup of the database you are going to mirror:


5. Place the backup to a place where the mirror server can access it. I dropped it on a network share:


6. Restore the database on the mirror SQL Server. Notice how I have referenced the UNC path - SQL's interface here is not the best, you have to type in the location and file name for it to find the backup. See the next point before you proceed.



7. On the restore page you MUST select a RESTORE WITH NORECOVERY under the Options menu. This is required for DB mirroring to work:


8. Assuming your restore was successful, the database will always show as "Restoring…":


At this stage you could proceed with setting up mirroring.

9. Go to the Mirroring settings page on the Primary server's database in the Database Properties section. Go ahead and click Configure Security…


10. Run through the wizard. Select to set up a witness server if the situation requires one. The wizard will ask you to configure the endpoints for communication on all instances. Make sure the port is open over TCP and that you select to encrypt the data.



11. The next screen lets you specify database mirroring service accounts. In my case I left them blank as all SQL instances are running under the same service account. Read this article for more info on service accounts: http://msdn.microsoft.com/en-us/library/ms189434.aspx


12. Continue the wizard and your endpoints will get created:


13. Click close and you will get the next message with some information. Click the Start Mirroring button and hopefully it works (:


14. You should get no errors and your Mirroring page should look similar to mine. (I postponed the configuration of my witness):


15. Your databases will indicate that mirroring is configured:



(ignore the ArchiveDB in the screenshot, I had to set it up twice...)

16. To check if it is all working you could use the "Launch Database Mirroring Monitor" tool:


This will provide you with information, which is near real time. I uploaded a large document on the Primary server in SharePoint and refreshed the tool a few times:


Now it is time to tell SharePoint that we have a failover database set up. SharePoint must know this to change its connection string when the mirrors swap. To do this we must use PowerShell:

$db = get-spdatabase | where {$_.Name -eq "WSS_Content_ProductionDB"}



In Central Administration you will see the setting populated. Read Bill Baer's post on how exactly this setting is used.


As a final test, go back in SQL, go on the mirroring page and click the Failover button. This will ultimately swap the roles of your two databases.


Click it and make sure SharePoint is still working for you! Hope this helps!

SharePoint 2010: Backup and Restore Farm Configurations

I often get called out to do SharePoint health checks - both environment and customisations. The people I've worked with know I'm an absolute nazi - if it's not in the books then it should be pointed out. Although some tasks are individual to the case, I have a really long checklist that I always go through. One of the first items on that list is to backup everything I could. A responsible consultant should do so. I also like to be sure that there is a restore process in place, and that the client has tested it. I hate the feeling of going into a company knowing that their restore process is not good enough.

SharePoint 2010 has new feature that makes backing up SharePoint configuration settings much easier than what it was in 2007. Through Central Administration or Powershell you could "backup" the configuration of the farm without any content. In 2007 backing up the configuration database was supported, but restoring it was not. Only certain situations allowed it.

Central Administration

To backup your farm configuration, go to "Backup and Restore" in Central Administration, then click "Perform a backup".


You will be taken to a screen which allows you to select the components to back up. Since we want the configuration, we must select the whole farm. Click "Next".


The next screen is where it all gets interesting. In SP 2010 you get a new option, "Back up only configuration settings". Select it, and specify a UNC file path where the timer service has rights to write to disk. Notice the required disk space and make sure you have enough. Don't be worried if the backup is less than that, mine ended up 1MB.


Click "Start backup" and off you go. You can monitor the progress on the Timer Job Status page, or the Backup and Restore page:



Given that it is configuration only, it didn't take long. Under 5 minutes.

Once complete, you could be enthusiastic and check out how SP backs things up. The folder will contain .BAK files, which are XML, and represent the objects and their properties as they lived within the configuration database.


The XML will be very familiar to developers.

Now, restoring is also interesting. You get the option to choose to restore to the "Same" server, or a new environment. If you select new, you get the option to input new names - databases, database servers, etc.

Go to "Backup and Restore" in Central Administration, then click "Restore from a backup". Enter the folder where you placed your backup, and choose the latest one (there could be more than 1 backup in that folder).


Select the whole farm:


Notice the Restore Options on the same screen. "Same configuration" is for restoring to the same farm, "New configuration" is if you have different computer names, databases and other settings.


You can fire it off and monitor the progress:


And that's pretty much it.


To achieve the above with Powershell:

PS C:\Users\Administrator> Backup-SPConfigurationDatabase -Directory c:\spbackup -DatabaseServer devbox -DatabaseName SharePoint_Config -Verbose

Note, this will not get executed by the Timer service, so you will not find a timer job. You will, however, see the backup job in the Backup and Restore job status page. Also, Verbose is pretty nasty on the Powershell screen.

To restore from Powershell:

PS C:\Users\Administrator> Restore-SPFarm -Directory c:\spbackup -RestoreMethod Overwrite -ConfigurationOnly

The command will ask you to confirm.

Hope this helps!

Recreating the SiteMap in your configuration database

When having a disaster recovery farm facilitated by Log Shipping, you would set up content databases in your secondary farm to process logs from your primary farm. For that to work successfully, you need to configure and set up the secondary farm with it's own configuration database, and some services also can't be configured as targets for log shipping or asynchronous mirroring. This KB article has more info on that: http://technet.microsoft.com/en-us/library/ff628971.aspx

As you create sites in your primary environment, the Configuration Database in the secondary environment(s) don't get updated - they end up not knowing about these sites. The configuration database stores a "SiteMap" of all sites, and that is used in various places, such as Search, the Central Administration UI, and I also believe the constructor of an SPSite object uses that to identify the exact site you are instantiating.

In SharePoint 2007, the only way to refresh the SiteMap in the configuration DB is to detach and re-attach the content database. Apart from being inconvenient and not well known this was also adding to precious minutes in downtime.

In SharePoint 2010 you can also detach and re-attach the database, but we now have a managed method that does the job for us. The SPContentDatabase object has a RefreshSitesInConfigurationDatabase() method that we could call from code or PowerShell:

To list all databases in the farm:


To get a specific database in a variable:

$contentDB = Get-SPDatabase | where {$_.Name -eq "WSS_Content_Contoso"}

To execute the method on the database:


You will have to do this on each disaster recovery farm that you have.

Opening up this method with Reflector, we could see its implementation.


It first calls the CleanUpSiteMap() method, which deletes all entries that do not exist anymore. It runs the following SQL query:

"SELECT SiteMap.Id, SiteMap.Path FROM SiteMap WHERE SiteMap.ApplicationId=@ApplicationId AND SiteMap.DatabaseId=@DatabaseId"

And then runs a check for each SiteMap.Id (the GUID of the site), deleting entries where the site is not found.

After that it calls RestoreSiteMap() , which internally gets all sites and runs the SPConfigurationDatabase.CreateSite() method. And that’s how the configuration database gets updated.

The key thing here to point out is that having a HOT standby environment requires planning and testing. If you haven’t tested your recovery strategy you might as well say you don’t really have one.

Other resources: