Migrating Workflow Manager Databases to new server

Question

Our Workflow Manager, setup on a standalone server (win 2012), has its databases configured on a shared database server (win 2008). The WFM is healthy and connected to SharePoint 2013 farm. Because win 2008 is going to be unsupported, and as part of the infrastructure upgrade, we would like to migrate just WFM databases (6 databases) to SharePoint DB server (win 2012) without re-installing WFM farm.

Is it possible to just migrate WFM databases from one server to another, and just re-point the connectionstrings without doing the reinstalling/restoring WFM farm? Does WFM allow updating connectionstrings?

Thanks

Answer 1

Hi,
if you don't use alias names try following procedure:

“Run as Administrator” PowerShell on first Workflow farm server

Add Workflow Manager module to PowerShell

Import-Module WorkflowManager

Save PrimarySymmetricKey in Clipboard

Get-SBNamespace | clip

Disconnect WorkFlow Farm (Workflow Manager Configuration -> Leave Workflow Manager Farm)

It is sufficient to back up only the following databases on old SQL Server

1- Service Bus Gateway database
2- Service Bus Message Container database
3- Workflow Manager Instance database
4- Workflow Manager Resource Manager database

You do not need a backup of management databases for Service Bus and Workflow Manager, because you will make this configuration from scratch in the new farm. 

Restore databases on new database server

Assign CertificateAutoGenerationKey 

$myKey = ...

Restore the Service Bus farm

Restore-SBFarm -RunAsAccount ‘DOMAIN\SPFarm’ -GatewayDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=SbGatewayDatabase;Integrated Security=True;Encrypt=False’ -SBFarmDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=SbManagementDBNEW;Integrated Security=True;Encrypt=False’ -AdminGroup ‘BUILTIN\Administrators’ -CertificateAutoGenerationKey $myKey

Restore-SBGateway -GatewayDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=SbGatewayDatabase;Integrated Security=True;Encrypt=False’ -SBFarmDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=SbManagementDBNEW;Integrated Security=True;Encrypt=False’

Restore-SBMessageContainer -ContainerDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=SBMessageContainer01;Integrated Security=True;Encrypt=False’ -SBFarmDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=SbManagementDBNEW;Integrated Security=True;Encrypt=False’ -Id 1

Add the existing server to the new Service Bus farm

$myPassword=convertto-securestring ‘[Password]’ -asplaintext -force

Add-SBHost -EnableFirewallRules $TRUE -RunAsPassword $myPassword -CertificateAutoGenerationKey $myPassword -SBFarmDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=SbManagementDBNEW;Integrated Security=True;Encrypt=False’

Restore Workflow Manager farm

Restore-WFFarm -RunAsAccount ‘DOMAIN\SPFarm’ -InstanceDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=WFInstanceManagementDB;Integrated Security=True;Asynchronous Processing=True;Encrypt=False’ -ResourceDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=WFResourceManagementDB;Integrated Security=True;Asynchronous Processing=True;Encrypt=False’ -WFFarmDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=WFManagementDBNEW;Integrated Security=True;Encrypt=False’ -InstanceStateSyncTime ‘Friday, Jan 24, 2020 06:30:00 PM’ -ConsistencyVerifierLogPath ‘c:\temp\log.txt’ -CertificateAutoGenerationKey $myKey -Verbose –SkipConsistencyVerification

Restore the “PrimarySymmetricKey” and set up authorized users for WorkflowDefaultNamespace

Set-SBNamespace -Name “WorkflowDefaultNamespace” -PrimarySymmetricKey “[PrimarySymmetricKey]”

$SBClientConfiguration = Get-SBClientConfiguration -Namespaces ‘WorkflowDefaultNamespace’ -Verbose;

Set-SBNamespace -Name WorkflowDefaultNamespace -ManageUsers @(‘spfarm@domain’, ‘spservice@domain’)

Add Workflow Manager farm to the Workflow Manager farm

Add-WFHost -WFFarmDBConnectionString ‘Data Source=[DatabaseServer];Initial Catalog=WFManagementDBNEW;Integrated Security=True;Encrypt=False’ -RunAsPassword $myPassword -EnableFirewallRules $TRUE -CertificateAutoGenerationKey $myKey -SBClientConfiguration $SBClientConfiguration –Verbose

Check Workflow farm

Get-WFFarm

Get-WFFarmStatus

Get-SBFarm

Get-SBFarmStatus

Connect SharePoint site collection and the new Workflow Manager

Register-SPWorkflowService -SPSite “https://portal.domain.com" -WorkflowHostUri https://wfm.domain.com:12290

Answer 2

Hi,
yes, it's possible to just migrate WFM databases from one server to another:

1. prepare keys for 5th step!
2. disconnect WorkFlow Farm (Workflow Manager Configuration -> Leave Workflow Manager Farm)
3. backup databases on old SQL Server
4. restore databases on new SQL Server
5. connect Workflow Farm (Workflow Manager Configuration -> Join an Existing Workflow Manager Farm)

Wictor Wilen has written a series of articles about disaster recovery procedure for workflow. You should check it out once:

http://www.wictorwilen.se/workflow-manager-disaster-recovery-and-restore-options-series

And you can also check out this video from SP conference:

http://channel9.msdn.com/Events/SharePoint-Conference/2014/SPC356

Answer 3

Hi @Raj Bairi ,

Try to use the method provided by PeterFleischer-3316 to check whether the restored databases in the new SQL Server can be used to join the existing WFM farm.(Keep the old databases exist)

As far as I am concerned, I recommend you uninstall and re-install WFM farm since it can keep the completeness of the farm.

========================================UPDATE=============================================

Sorry to confuse you. Per my first reply, I suggested you keep the old databases exists after migrating to another SQL server, since I am worried that when we retired old related databases in previous SQL server, the WFM farm may be affected.

You will fail to join the farm with restored databases in new SQL server, so I suggest you re-install the WFM farm.

Currently, I backup and restore six databases in a new SQL server, keep the old databases exists, then I successfully joined the farm with the restored databases.

However, if we retired the old SQL server, the current workflow manager farm is damaged.

Finally, re-configure the workflow manager farm rather than joining the farm with restored databases.

---------------------------------------------------------------------------------------------------------------------------------------------------------------

If an Answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.