Se pare că activitatea mea on-line a intrat într-un mare con de umbră, explicaţia fiind dată de implicarea mea într-un proiect pentru un Client respectabil. Cu tot respectul în schimb, nu mi-a mai fost dat să văd ochilor o atitudine atât de contrară unui proiect ca la acest Client. Simt că sunt oameni care îşi doresc cu adevărat să nu reuşească acest proiect iar asta este o problemă deosebit de gravă. Se pare că anii de management care i-am făcut în tinereţe la Iaşi nu se potrivesc cu managementul de prin alte locuri aşa că, nu pot să închei această introducere tenebră decât cu banala expresie românească: asta este şi un Slash P din adâncul inimii.

Prezentul articol este despre SharePoint 2007 şi despre modul de rezolvare, mai altfel a unei probleme de prioritate -2 dar peste care nu se poate trece. O să îl scriu în engleză (aia a mea de baltă) că poate or avea şi alţii nevoie de aşa ceva şi să nu se mai chinuie. Nu vă gândiţi că mi-aş propune un titlu de MVP ceva pentru că acest subiect este închis complet pentru mine, ci o fac doar din altruism şi pentru alţi oameni. Ştiu că eu am impus regula ca toate blogurile să fie scrie numai în Engleză şi accept votul de blam al comunităţii.

 These days I’m involved into a huge SharePoint implementation and migration. There are many problems but one of them is really tricky for my team.

Context:

We have a stand-alone farm (Pilot – P) that must be migrated into a new farm (Test – T). There are many differences:

–      P is MOSS 2007, 32b, NTLM ; T is MOSS 2007 SP1 Kerberos

–      P contains database server, index and search services on a single server

–      P haves a single Shared Services provider all logical components are deployed into a single web application.

–      Database index on P was broken and recovered in time and the index is stored directly into the db.

–      MySites were deployed at the beginning with username and in time this model was changed to include domain name.

Requirements:

Migrate all content into T farm that contains web applications for each logical component: SharedServices, MySites and business units (BU).

The Microsoft’s recommendations for this problem: http://technet.microsoft.com/en-us/library/cc262325.aspx. This reference could be usefully for beginners [:)] but if you have a complex scenario that’s not enough to solve your deployment.

Also Microsoft recommends a Object model in SharePoint deployments. Some good guys from Codeplex are giving us few free tools for migrating profiles from a farm to another. The greatest moment for me was the moment when I read this blog post: Profile Properties Import/Export. Easy enough …isn’t it? Hey, I’m not programmer. I do not know how to build an application or how to import some libraries into a application. I’m usually with Click, Next, Next, Finish….

So, this Easy import / export didn’t works for me.

In fact what are the problem and the steps?

First at all I cannot use backup/restore classical procedures from SharePoint… because there is a broken Index and when I’m trying to restore content into a new farm I cannot specify another address location for that index.

I have an SQL Backup of database content and SSP content. That’s all that I need. I have done restoration and mounting database (A_MigrareT_Content) into a new web application (migraret.client.org) and if I’m trying to restore user’s MySites with stsadm export/import procedures I cannot import also specific user’s details like: picture, about me, responsibilities and another business critical data like Interests, Schools, skills etc. [:)]. I restored old SSP database (OLD_SSP_Content) in SQL Server and I was trying to restore old SSP into SharePoint Central Administration.  Guess what? Didn’t works for me.

In that case the only way to solve that was to ignore object model and I have done a T-SQL script to automate whole process of migration profiles from old content to new content.

The keypoint of SharePoint is that all users’ details are stored into a table called UserProfileValue but there is another important table called UserProfile_Full. I’m not so good in T-SQL but I want to thanks especially to Tibbs that helps me to create a cursor.

1.   Creating scripts for Export/Delete/Create/Import

Easiest way when you have a complex problem is to discompose them into small parts… you know old latin words: Divide et impera.

create view wOldMySite as (

select up_full.recordid, up_full.ntname, up_full.preferredname, up_full.email, up_full.datasource, up_val.propertyval

  FROM [Old_SSP_Content].[dbo].[UserProfile_Full] as up_full,

          [Old_SSP_Content].[dbo].[UserProfileValue] as up_val

where up_full.recordid=up_val.recordid and up_val.propertyid=22)

 

create view wOldMySite2 as (

select wOld.*, up_val.propertyval as username

  FROM [Old_SSP_Content].[dbo].[wOldMySite] as wOld,

          [Old_SSP_Content].[dbo].[UserProfileValue] as up_val

where wOld.recordid=up_val.recordid and up_val.propertyid=17)

 

–      propertyid=22 is a correspondent of old MySite address, something like: /personal/jsmith/ or /personal/domain_msmith/ 

–      propertyid=17 is the username without domain.

–      You can replace in this script the database name Old_SSP_Content with your old database name.

 

select 1 as [level], ‘stsadm -o export -url http://migraret.client.org‘+cast(propertyval as nvarchar (200))+

   ‘ -filename D:\MySite-Export\‘+cast(recordid as nvarchar (20))+‘A.bak -includeusersecurity -versions 4 -quiet’

from wOldMySite2

 

union

 

select 2 as [level], ‘stsadm -o deletesite -url http://migraret.client.org‘+cast(propertyval as nvarchar (200))

from wOldMySite2

 

union

 

select 3 as [level], ‘stsadm -o createsite -url http://mysitet.client.org/personal/’+

substring(ntname,1,len(cast(ntname as nvarchar (200)))-len(cast(username as nvarchar (200)))-1)+

‘_’+cast(username as nvarchar (200))+‘ -owneremail ‘+email+‘ -ownerlogin ‘+ cast(ntname as nvarchar (200))

from wOldMySite2

 

union

 

select 4 as [level], ‘stsadm -o import -url http://mysitet.client.org‘+cast(propertyval as nvarchar (200))+

   ‘ -filename D:\MySite-Export\‘+cast(recordid as nvarchar (20))+‘A.bak -includeusersecurity -quiet’

from wOldMySite2

 

Level is the priority of operations:

–      1 – Exporting actual mysites files.

–      2 – Deleting mysites after export because if you are executing creation (level 3) without deleting these sites you can receive an Error with event id 7888 in event viewer.

–      3 – Create users’ mysites into a new web application called in my case mysitet.client.org. In this case the method of creating sites is to use only user name as identifier without domain.

–      4. Importing data into new profiles

Of course, you can do that in Excel…

Next step … add specific details into a user profile.

2.   Migrating users’ details into new farm

Users where imported into farm T with a new SSP. In that case there is a difference between old farm and new farm at recorded level. In that case we must do a mapping between old and new id of user.

A.   Creating a view into old database

create view OldUsers as (

select * from UserProfileValue where propertyid=3 and recordid in (

select distinct recordid from [Old_SSP_Content].[dbo].[UserProfileValue]

where propertyid=22 )

)

 

B.    Creating a view into new database

Create view wUserMaping as (

select NEW.[Propertyval] As NTName,

         NEW.[RecordID] as IDNou,

         OLD.[RecordID] as IDVechi

FROM

         [Old_SSP_Content].[dbo].[OldUsers] AS OLD,

         [A_SharedServices_DB].[dbo].[UserProfileValue] AS NEW

where

         NEW.[propertyid]=3 AND

  NEW.[propertyval] = OLD.[propertyval] )

 

My new database that contains SSP is called A_SharedServices_DB.

 

C.   Copy old users’ details from old db into new db.

–begin transaction

declare @idNou int, @idVechi int, @nume sql_variant

DECLARE MySite_Cursor CURSOR FOR

SELECT IDNou, IDVechi, NTName FROM wUserMaping

OPEN MySite_Cursor

 

FETCH NEXT FROM MySite_Cursor into @idNou, @idVechi, @nume

WHILE @@FETCH_STATUS = 0

   BEGIN

 

insert into [A_SharedServices_DB].[dbo].[UserProfileValue]

select @idNou as recordid,[PropertyID]

      ,[PropertyVal]

      ,[Image]

      ,[Text]

      ,[VocValID]

      ,[OrderRank]

      ,[Privacy] from [Old_SSP_Content].[dbo].[UserProfileValue] where recordid=@idVechi and propertyID not in

(select PropertyID from [A_SharedServices_DB].[dbo].[UserProfileValue] where [recordid]=@idNou)

 

      FETCH NEXT FROM MySite_Cursor into @idNou, @idVechi, @nume

   END;

CLOSE MySite_Cursor

DEALLOCATE MySite_Cursor

 

Easy enough [:)]…

We still have a problem to solve… absolute path… specified for pictures or other details.

3.   Clean up new database and replace old paths with new ones.

–begin transaction

declare @RecordID int, @PropertyID int, @PropertyVal sql_variant

DECLARE Update_Cursor CURSOR FOR

 

SELECT     RecordID, PropertyID, ‘http://mysitet.client.org’ + substring(CAST(PropertyVal AS varchar(2000)),17,len(rtrim(CAST(PropertyVal AS varchar(2000))))-16)

FROM       [A_SharedServices_DB].[dbo].[UserProfileValue]

WHERE      (CAST(PropertyVal AS varchar(2000)) LIKE ‘%oldappsrv%’)

OPEN Update_Cursor

 

FETCH NEXT FROM Update_Cursor into @RecordID, @PropertyID, @PropertyVal

WHILE @@FETCH_STATUS = 0

   BEGIN

   UPDATE [A_SharedServices_DB].[dbo].[UserProfileValue] SET PropertyVal = @PropertyVal

   Where RecordID = @RecordID and PropertyID = @PropertyID

    FETCH NEXT FROM Update_Cursor into @RecordID, @PropertyID, @PropertyVal

   END;

CLOSE Update_Cursor

DEALLOCATE Update_Cursor

 

Oldappsrv is the name of my pilot server …

 

Huh. That’s all folks. If you have any comments or suggestions I will be honored to read them.

Good luck and… didn’t call me … [:)] Microsoft didn’t recommend that. Verify these scripts into a test environment first.

[UPDATE 11/04/08]

There is a problem with usernames that contains dot (.) character like lastname.firstname@domain.com

In that case you must replace (when you are creating sites or importing MySites) dot (.) with undescore (_)