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

Today we launched the new site of the Bulgarian SQL & BI user group, available at address http://bgsqlgroup.com/.  Great thanks to Lubo, who has driven the development efforts and Teddy, the one responsible for design and information infrastructure.

Here I will give some insight in how we have built it.

SharePoint Services

The site is built in SharePoint services, users are authenticated using Forms Authentication, also with the Bulgarian language pack

Business and Technical sections

The technical section contains technical documents, useful SQL tools, technical news and links to sites.

The business section has document libraries for business documents and presentations, business news and links. Additionally, members can upload case studies, which are published after approval.

Protected documents upload

Workflow is run in the background to give contribute permissions to the person who uploads the document, and read-only permissions to everyone else.

Users are registered in aspnet membership database – storing the basic user registration information - username, email and password.

User profile is stored in SharePoint list.  User profile pictures are stored in separate picture library. The user profile edit form is changed to custom list form to incorporate additional user control for uploading user profile pictures.

The Forum

The forum is built using the open-source Yet Another .NET Forum. Authentication of both forum and sharepoint are done against the same ASP.NET Membership database, thus achieving single-sign on between the two applications.

Sincerely,
Rossen Zhivkov