A week ago we completed the migration of SharePoint implementation to new infrastructure for a client of ours. The non-functional requirements included high-availability which we met with SQL Server 2008 Database Mirroring. Below are some of the architecture decisions from this project.

Fig 1: Content DB is running as principal on SQL2 and mirror on SQL1, while all the others are running as principal SQL1, and mirror on SQL2.

Asynchronous vs. Synchronous database mirroring

With asynchronous mirroring (high-performance mode), the transaction is completed on principal server before it is applied on mirrored server. This means that if fail down occurs, there is possibility of data loss.

With synchronous mirroring, transaction on the principal is committed after it has been completed on the mirror server, thus causing a performance delays. Synchronous mirroring requires a high-performance network infrastructure with low latency.

Note that asynchronous is available only in Enterprise version of SQL Server.

Using SQL Aliases

SharePoint is sensitive (meaning lots of stsadm work required) to changing the database server for Configuration database. Using SQL aliases, you can trick SharePoint to “think” that it is connecting to the same principal database, while in reality it is using the mirrored database (when principal is down).

You shall be setting SQL aliases on front end servers, where you don’t have SQL Server installed. Then, you shall use SQL Client Configuration utility cliconfig, located here: C:\Windows\System32\cliconfg.exe.

Here is the location to the key that this tool sets. This key can be directly modified in registry to change the alias.

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQLServer\Client\ConnectTo]
SQL -> DBMSSOCN, SQL1

“Active-active” Database mirroring

By definition, Database mirroring implies that one server is the principal, and the other is the mirror. With this configuration, each of the servers is running as principal for some of the databases, and mirror for the others, and vice versa.

The purpose is to distribute the load between the two servers and to utilize the memory of both of the servers. If you have read operation, than only the principal server of the database will register disk IO. Of course, if you have write operation, than both the principal and the mirror will carry out the operation.

Also, with SharePoint, you can put site collections in separate content databases. That means that you can have half of the site collections running on SQL 1 as principal, and half of them running on SQL 2 as principal.

Note that if you use “active-active” type of DB Mirroring, you will have to license the both of the SQL Servers. If one server is acting as only mirror instance, then you will have to have license only one SQL Server – the principal server.

Manual vs. Automatic Failover

With manual failover you will have to run the following scripts:

-       T-SQL Script on the active server to force manual failover on the mirror server (with data loss if you use asynchronous mirroring)

-       Registry update script on each of the front-end servers to update the SQL Aliases. Also, you can use remote registry in order to run the script remotely on each of the servers in the farm.

SharePoint with does not support truly automatic failover. However, what you can do is you can use a specific event that is triggered when DB mirroring failver has occured. The event will initiate a SQL server job, which in turn will update the SQL aliases.

Once the other server is up and running, you will have to revert the databases to their, and update the SQL aliases as well.

Our implementation:

We implemented asynchronous active-active mirroring, with fully-scripted manual failover without witness server.

Sincerely,
Rossen Zhivkov

The scenario:

One of our current projects required that we had to create a SharePoint list item (task) when specific condition occurs in CRM - "Responsible person" is selected on opportunity level.

The solution:

1. Create web service on SharePoint Server

Often, you will have you SharePoint infrastructure reside on different server than CRM. Then, you cannot access the object model directly so you need to create a web serivice. Decide also on service authentication.

In our case - the web service creates an list item (task) in SharePoint, and fills it with the necessary properties.

2. Create custom workflow activity

2.1. Create sharepoint workflow activity library. Add CRM assemblies (from CRM SDK). Annotate class with appropriate attributes.

[CrmWorkflowActivity("Crm Workflow Activity", "SharePoint Activity")]
public partial class SharePointActivity: SequenceActivity
{
   public SharePointActivity()
   {
      InitializeComponent();
   }
}

2.2. Register dependency properties, which will then be fed from CRM

public static DependencyProperty responsiblePersonProperty =
   DependencyProperty.Register("responsiblePerson", typeof(string), typeof(SharePointActivity));

[CrmInput("responsiblePerson")]
[CrmDefault(@"")]
public string responsiblePerson
{
   get
   {
      return (string)base.GetValue(responsiblePersonProperty);
   }
   set
   {
      base.SetValue(responsiblePersonProperty, value);
   }
}

2.3. Call the SharePoint web service. For simplicity - no error handling & no security calling the service.

protected override ActivityExecutionStatus Execute(ActivityExecutionContext executionContext)
{
   SamsService sams = new SamsService();
   sams.CreateTask(responsiblePerson);           
   return ActivityExecutionStatus.Closed;
}

3. Register the custom workflow activity in CRM and enable debugging

Registration is pretty straighforward with tools in CRM SDK (plugin registration). CRM workflows are run "assyncronously" by CRM Assyncronours Service - this means that in order to debug your custom workflow activites, you need to attach to this service, not to w3wp. Also,  your code is run with service's credentials  (network service by default), so you might experience security issues.

Remember also iisreset & restart assync service on every change

4. Create CRM workflow that uses the custom activity

The workflow is started automatically when opportunity is created. The workflow waits untill "Reponsible person" is not blank. Then the workllow executes our custom workflow activity for SharePoint integration, passing all the required properties.

You can pass data from opportunity level, or any related entites such as Primary Contact or Account to every custom activity's dependency property that has been marked with the CrmInput attribute.

Sincerely,
Rossen

We have recently completed a SharePoint Services 3.0 project for task management and business processes automation. It is a good habit to keep track of what issues appeared in the project, so that you can make better architecture decisions in the future.

Here is the quick “lessons learned” list:

1. Forms authentication
Create first a SharePoint web application with windows authentication, then extent the web application with forms authentication. Develop or purchase additional component for users’ registration with features like resetting passwords and emailing forgotten password.

Articles:
http://msdn.microsoft.com/en-us/library/bb975136.aspx
http://www.simple-talk.com/dotnet/windows-forms/configuring-forms-authentication-in-sharepoint-2007/
Free tool to manage users:
http://www.codeplex.com/fba/Wiki/View.aspx?title=basic%20FBA%20user%20and%20role%20management&referringTitle=Home

2. Office integration with SharePoint with Forms authentication
Not working by default as you might have guessed. These are the steps (in brief) how to configure it:

• Enable “client integration” In Central administration -> Application Management -> Authentication Providers for the selected zone
• When users sign in, they must check “remember me”. In that way, the authentication cookie is persisted and the rich clients can authenticate to SharePoint.
• SharePoint site must be in trusted sites, or in other internet explorer zone, which has Internet explorer Protected mode turned off. Protected mode being off enables other applications (such as Word, SharePoint designer), to use the authentication cookie
• The user must have appropriate access permissions to use rich client with SharePoint (check permission levels)

Article:
http://msdn.microsoft.com/en-us/library/bb977430.aspx

3. Alternate mappings for publishing SharePoint Services to Internet
Check your alternate access mappings or use load-balanced URL when extending the SharePoint site. Incorrect alternate access mappings cause login form to appear, but after logon, a 404 error is given. General rule of thumb - if functionality are working in intranet, but you have issues in internet, do check the alternate access mappings. Also, check windows firewall if you use custom ports. You need to open incoming requests on these ports.

Article:
http://blogs.msdn.com/sharepoint/archive/2007/03/06/what-every-sharepoint-administrator-needs-to-know-about-alternate-access-mappings-part-1.aspx (read part 1, 2 and 3)

4. Customizing Edit Pages
Customized edit pages enables you to rearrange fields, set some fields as read only, adding additional user controls on the web page, and more.
Abilitics free Edit Page redirector will be available soon. Edit page redirector “redirects” users to appropriate edit page, based on their group membership or based on field values. Stay tuned.

5. SharePoint designer workflows & Custom actions
Custom workflow activities enable you to add additional functionality to workflows that you develop with SharePoint designer.
Download free SharePoint designer activities: http://www.codeplex.com/SPDActivities. Giving permissions to users with forms authentication does not work. You have to modify the code of this tool.

Some SharePoint designer workflow issues & solutions:

Issue: Cannot select multiple users when selecting users (as in granting permissions activity)
Solution: The reason is because SharePoint designer user picker designer type does not allow you to select multiple users. The workaround is to save the multiple users in a dynamic string, and then feed the dynamic string to the activity.

Issue: After you edit workflow with SharePoint designer, SharePoint still runs the old workflow
Solution: it appears to be a bug of some kind. Our workaround is to copy the workflow and associate it with another library, delete it from the original location, and then return it back. A friend of mine with the same problem suggested to save the workflow locally, delete it from SharePoint (the action deletes all previous versions), and then upload it again.

Issue: SharePoint designer does not show latest version of a custom developed activity
Solution: Delete SharePoint cache from this location C:\Users\rossen.ABILITICS\AppData\Local\Microsoft\WebsiteCache (on Vista)

Issue: Access denied when custom SharePoint activity is run
Solution: By default, the workflow runs with the security account of the user who initiated the workflow. You need to run with elevated privileges with SPSecurity.RunWithElevatedPrivileges. Do remember to recreate new SPSite & SPWeb.

6. Modifying item when you have only read-only access
Imagine a vacation request business process. Once approved, you will have read-only access, because you should not be able to modify it. But you must be able to cancel your vacation request.
The solution: Create custom web part with single button (Cancel Vacation Request), put it on the DispForm. The web part will use elevation of privilege (SPSecurity.RunWithElevatedPrivilege) to modify the current item. DispForm is accessible when you have read-only access, but edit form is not.

7. Script Everything
If you repeat some action twice – script it!

• Script web part deployment, SharePoint designer custom activity deployment, features activation and deactivation
• Script application pool recycling and web site warming up (http://blogs.msdn.com/joelo/archive/2006/08/13/697044.aspx)
• Script backup and restore operations(and run backup as scheduled task, or backup the DB regularily)

Script user configuration:
Incorrectly set user accounts and group membership can ruin a perfectly fine SharePoint implementation. User configuration shall be automated and validated for errors. 

• The client provides user accounts, temporary password, emails, user profiles, group membership in excel file
• Custom application creates the users in the SQL Server AspNetSqlMembership provider database
• Custom application fills in and validates user profiles in SharePoint (missing properties, missing managers, incorrect email addresses)
• Stsadm script adds the users to the required security groups in SharePoint

Sincerely,
Rossen

Dear friend,

Welcome to my blog. I am the one in the right corner, couple of years younger … and probably wiser :). Brave visionary, determined to go on a hard, but fulfilling journey for creating success.
Here is a little information where I came from, where I am now and where I want to go!

The past
I have spent 3-4 years in one of the leading IT services company in Bulgaria, working on various projects for .NET, SharePoint and SQL Server. It has been a wild journey between project work, presales activities, consulting, trainings, presentations, team building events and everything.

Despite the fact that I enjoyed my work there and the strong friendships I built, I decided that it is time to move on. It is time for new beginning, it is time to make things the way I want them to be and the way I believe they should be.

The present
I found Abilitics, with lots of passion and belief that it will grow as one beautiful and healthy company.  So far things are going better than expected. Our clients believe and trust us, we respond with a quality work, dedication and great results.

The team is great, motivated and happy.  We have lots of fun and laugher, which is the way how we will make our way through the difficulties.  

I am happy that I managed to find the right people for all of the key roles necessary to deliver our services – we have the PM  & Business analysis girl, the developer guys, the infrastructure consultant and the QA.  Soon some of them will be blogging too. The dev guys are great professionals, though I am little jealous that they are getting better development experts than me. However, that is something that I expected and ready to give up for the sake of the company success.

The future
Unknown! Fortunately.

Stay with us, I am sure it will be a wild and very interesting ride.

I will share you our experience about various topics around architecture and development from our SharePoint and .NET projects, as well as Business Intelligence solutions and Data Mining.

I will give you some insights of our successful practices and the mistakes we made in Abilitics business, sales and the management.
Abilitics is launching lots of SharePoint-related products right now, some of them commercial, but some of them are free.  Stay tuned to see how we can make your life easier with delivering sharepoint projects. 

The end
Wish you all lots of good luck, interesting projects and a quality leisure time. Enjoy!

Sincerely,
Rossen Zhivkov
Founder and Solution Architect,
Abilitics Ltd.