In this four part series I will describe the process of extending the SharePoint 2013 user profile from Active Directory with additional data from a SQL Server table.  The additional data will then be displayed in users’ My Sites and SharePoint people search.

Part 1 will show the process to create the SQL Server table.  Part 2 will show the process to make the data in the SQL Server table accessible to SharePoint.  Part 3 will show the process to extend user profiles and My Sites to include the additional user data.  Part 4 will show the process to make the new user profile properties accessible in SharePoint Search.

The SharePoint environment is SharePoint 2013 Standard, with SQL Server 2008 R2 and SharePoint Designer 2013.  The steps are based on an environment where the User Profile Service has already been setup, users have been imported from Active Directory and My Sites have been configured.  It is assumed that SharePoint Search has been configured and a search site using the Enterprise Search Center template has been created.

Firstly, create a SQL table to hold the user data.  Below is some example code that creates the table and adds an index on the email address column.

CREATE TABLE [dbo].[SPPeopleData](
[NameID] [int] NULL,
[FirmInitials] [varchar](6) NULL,
[Jurisdiction] [nvarchar](15) NULL,
[Email] [varchar](40) NULL
) ON [PRIMARY]

GO

CREATE NONCLUSTERED INDEX [_TW_Email] ON [dbo].[SPPeopleData]
(
[Email] ASC
) ON [PRIMARY]
GO

Next, fill the table with some test data.

Now create a stored procedure to return user data for a given email address.  I’m using email address as it’s a property that’s imported for each user from Active Directory.

CREATE PROCEDURE [dbo].[twsp_GetSPPeopleDataForEmail] @Email VARCHAR(40) AS

SELECT [NameID]
,[FirmInitials]
,[Jurisdiction]
,[Email]
FROM [SPPeopleData]
WHERE [Email] = @Email

In part two I’ll show how to connect SharePoint to the data in the SQL Server table.