Split databases

This topic describes how to split existing SharePoint and Nintex content databases.

If Nintex Workflow was installed using its default configuration of one all-inclusive database, this data can be divided and distributed to one or more new databases. If SharePoint content needs to be similarly subdivided, there is a SharePoint method to achieve this. This document describes a process using both methods in conjunction to achieve a 1-to-1 mapping of SharePoint and Nintex data.

Note: If you do not want a 1-to-1 mapping of SharePoint and Nintex content databases, the SharePoint content can be left as-is and only a dedicated Nintex database created and the Nintex data moved. With this mapping, whenever a backup and restore is done in the future, the entire SharePoint content database and all the Nintex Workflow content databases will need to be backed up at the same time and restored at the same time. For more information, see Backup and restore databases.

For guidance on planning database mapping and maintenance, see Design databases.

Process Overview

Please read this entire article to familiarize yourself with the process and its requirements. As always, Nintex recommends testing this process on a non-production environment first.

The SharePoint method for moving database content: The PowerShell Move-SPSite command is used to move the content of a SharePoint site collection from one database to another on the same SQL Server. This process can be done independently of Nintex – if a SharePoint site collection is given its own SharePoint content database, it will use its existing Nintex content database until directed otherwise.

The Nintex method for moving database content: The NWAdmin movedata command is used to move the Nintex workflow progress data of a site collection into its own Nintex content database. The new database can be created using the Central Administration interface, but the data itself must be remapped using NWAdmin.

High Level Steps

To split a SharePoint content database and a Nintex content database to enable one-to-one content for each

1. Create a new SharePoint content database (use the Microsoft recommended methods).
2. Create a new Nintex Workflow content database.
   1. Access the Database management page: On the Central Administration Home page, click Nintex Administration and then click Database management under Licensing and setup.
   2. Click Add and then specify the details.
3. Log in to the SQL Server and ensure that all necessary service accounts are added to the newly created Nintex database. To add a service account:
   1. Open SQL Server Management Studio.
   2. Expand the new Nintex content database.
   3. Expand the Security folder.
   4. Right-click the Users folder and select New user.
   5. Enter a display name for the user then the login name.
   6. From the Role Member section select WSS_Content_Application_Pool.
   7. Save the changes and perform an IISRESET.
4. Stop the web application that contains the content database being split by stopping the IIS site mapped to the web application.
5. Stop all instances of the SharePoint Timer Service wherever they exist for the entire farm.

6. To move SharePoint content from an existing content database to another database, use Windows PowerShell Move-SPSite.

   This move will keep all GUIDs including SiteID. For more information on using this command, see Using Windows PowerShell Move-SPSite.

7. Run nwadmin -o movedata, the detail for which is below.
8. Map the new SharePoint content database to the new Nintex Workflow content database using the Manage database mappings page accessed from Central Administration.
9. Restart the web application, the IIS site, and all instances of the SharePoint Timer Service in the farm.

To test the process

1. Open your new site.
2. Confirm display of visual and detailed workflow history for a workflow that is either “In progress” or “Completed.”
3. Start a new instance of an existing workflow and confirm that it starts, that it enters an "In progress" status, and that visual and detailed workflow history are displayed.

Using Windows PowerShell Move-SPSite

To split out site collections to maintain a 1-to-1 mapping between SharePoint and Nintex Workflow content databases, use Move-SPSite in Windows PowerShell. This operation provides the ability to move a site collection to an another content database while preserving all SharePoint GUIDs (except the Content Database ID, of course).

For these procedures to work, the following conditions must be true.

• The destination content database must already exist.
• The source content database and destination content database must be located on the same instance of SQL Server.
• The source content database and destination content database must be attached to the same Web application.

For more information on Move-SPSite, see the following Microsoft articles.

• "Move site collections between databases (SharePoint Server 2010)" at http://technet.microsoft.com/en-us/library/cc825328(v=office.14).aspx
• "Move-SPSite" at https://technet.microsoft.com/en-us/library/ff607915(v=office.14).aspx

Before performing the below steps, disable access to the farm using Microsoft’s standard methods.

Using NWAdmin -movedata

This section describes the use of -movedata for the above example. For more information about this command, see NWAdmin Guide.

This operation is used to move all workflow progress data for a site collection to a selected workflow content database. This command should be run as the Farm Account, as this identity will have access to each workflow database.

The utility will begin by warning the user to pause all services that can run or interact with workflow. You will need to stop the web application that is to be restored. This includes stopping the IIS site mapped to the web application and stopping the SharePoint Timer Service and all servers in the farm. This is important: if the workflow data changes during the migration process it may become corrupted. Once the migration is complete, the tool will attempt to detect any changes to the data and handle this by warning the user and moving the changed data, but we recommend avoiding this situation.

In a command prompt, type the following.

nwadmin.exe -o moveData –Url http://YourFullyQualifiedDomanName/sites/sitename

The tool will list the workflow content databases that are configured with numeric identifiers and prompt the user to choose which of these databases the site collection workflow data should be moved in to.

The data for each workflow instance is moved one by one. If any fails to move, the tool will display error details and prompts the user either to retry moving the instance, skip the instance or abort the process, rolling back any changes.

Once the operation is complete, restart all services to continue workflow operation. From this point on, workflows in the site collection will use the new database. If any services were not stopped during the migration process, they will contain cached references to use the old database so it is critical they are restarted at this point (which will cause them to clear the cached database pointer and use the new database).

Usage

NWAdmin.exe -o MoveData [-Url <siteCollectionUrl>] [-SourceDatabase <connection string>] [-TargetDatabase <connection string>] [-RetainSourceData]