How to move K2 [blackpearl] data from one farm to another

Sometimes it can be useful to recreate the same situation that you have in a K2 farm elsewhere:

• if you got some troubles in a production environment and you want to investigate on them without corrupting the environment further
  
• if you want to test an hotfix or upgrade and you want to do that having exactly the same situation that you will find in your final environment
  
• if you want to stress test a clone of your production environment without interrupting the service
  

K2 [blackpearl] comes with 14 databases. As far as it concerns processes data, however, only two databases have the main impact:

• K2Server: it is the transactional database used for workflow execution and tasklist retrieval
  
• K2ServerLog: it is the logging database used for reporting data, smart object and viewflow
  

While the server is running some dedicated threads move data that's no more needed for execution from the K2Server to the K2ServerLog database.

In order to move data from one farm to another it is sufficient to replace these two databases in the destination farm with the ones from the source farm (either via detach/attach or backup/restore).

At that point it is enough to run the following T-SQL script in the K2Server database of the destination farm before restarting the K2 server service:

USE [K2Server]

-- Setting K2 Server and removing extra entries

DECLARE @HOSTNAME NVARCHAR(20)

DECLARE @LICENSE NVARCHAR(20)

SELECT @LICENSE = [LicenseKey], @HOSTNAME=[HostName] FROM [HostServer].[dbo].[LicenseKeys] WHERE [HostName] = 'dest farm hostname'

UPDATE [_Server] set [Name]=@HOSTNAME, [LicenseKey]=@LICENSE WHERE [ID] = 1

DELETE FROM [_Server] WHERE ID > 1

-- Update Server Users

DELETE FROM [_ServerUser]

INSERT INTO [_ServerUser] VALUES ('K2:DENALLIX\Administrator', 1,1,1)

INSERT INTO [_ServerUser] VALUES (''K2:DENALLIX\K2Admin', 1,1,1)

INSERT INTO [_ServerUser] VALUES (''K2:DENALLIX\K2App', 1,1,1)

-- Update Settings…

UPDATE [_Setting] SET [Value] = 'Integrated=True;IsPrimaryLogin=True;Authenticate=True;EncryptedPassword=False;Host=' + @HOSTNAME + ';Port=5555' WHERE [Name] = 'UMData'

--Update String Tables

UPDATE [_StringTable] SET [Value] = @HOSTNAME

WHERE [Root] = 'Production' AND [Name] = 'Mail Server'

UPDATE [_StringTable] SET [Value] = 'Integrated=True;IsPrimaryLogin=True;Authenticate=True;EncryptedPassword=False;Host=' + @HOSTNAME + ';Port=5555'

WHERE [Root] = 'Production' AND [Name] = 'SmartObject Server'

UPDATE [_StringTable] SET [Value] = 'Integrated=True;IsPrimaryLogin=True;Authenticate=True;EncryptedPassword=False;Host=' + @HOSTNAME + ';Port=5555'

WHERE [Root] = 'Production' AND [Name] = 'ServiceObject Server'

UPDATE [_StringTable] SET [Value] = 'Integrated=True;IsPrimaryLogin=True;Authenticate=True;EncryptedPassword=False;Host=' + @HOSTNAME + ';Port=5555'

WHERE [Root] = 'Production' AND [Name] = 'Category Server'

UPDATE [_StringTable] SET [Value] = 'http://' + @HOSTNAME + ':8081'

WHERE [Root] = 'Production' AND [Name] = 'Web Service URL'

UPDATE [_StringTable] SET [Value] = 'Integrated=True;IsPrimaryLogin=True;Authenticate=True;EncryptedPassword=False;Host=' + @HOSTNAME + ';Port=5252'

WHERE [Root] = 'Production' AND [Name] = 'Workflow Server'

UPDATE [_StringTable] SET [Value] = 'Integrated=True;IsPrimaryLogin=True;Authenticate=True;EncryptedPassword=False;Host=' + @HOSTNAME + ';Port=5555'

WHERE [Root] = 'Production' AND [Name] = 'Workflow Management Server'

-- ** NOTE - The Actioners might need to be updated with new labels??

--select * from _Actioners

The script updates all the relevant tables… it is only needed to change the hostname and the server users (highlighted) accordingly.