Tuesday, 16 December 2014

Provisioning a new Nintex Workflow Content Database using PowerShell

Scenario:

I need to create a new Nintex Workflow Content database and associate it with a new SharePoint Site Collection as part of a PowerShell based solution provisioning process.

Problem:

There is no obvious way to do this; there are no methods in the web api, and nwadmin doesn't have any operations that remotely resemble adding a new content database.

Approach:

There are two ASPX pages in SharePoint Central Admin that allow administrators to create new Nintex Workflow Content databases and associate those databases to Site Collections. Since these pages must have code behind them, I thought I'd open up the Nintex dll's (using ILSpy) and see if I could find the code responsible for the functionality on these pages.

This approach worked perfectly, and it turned out that I only needed a few lines of PowerShell to create my new database and associated it with a Site Collection.

The caveat is, the PowerShell needs to be run from a PowerShell command prompt on the Server, so it won't work if you're solution is being built for Office 365, or if you need to use all client side (PowerShell) code.

Solution / Code:

#Load all the assemblies that we need to use            
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SharePoint.Administration') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Administration') | Out-Null            
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.Common') | Out-Null
[System.Reflection.Assembly]::LoadWithPartialName('Nintex.Workflow.ContentDbMappingCollection') | Out-Null            
            
#Add the SharePoint PowerShell snapin (in case it's not already loaded)            
if(-not(Get-PSSnapin | Where-Object {$_.Name -eq "Microsoft.SharePoint.PowerShell"}))
{
 Add-PSSnapin Microsoft.SharePoint.PowerShell;            
}            
            
$NintextDatabaseName = "Nintex_Flintstones"            
$Url = "http://portaldev.bi.local/sites/flintstones";            
#Get the SharePoint Site that you want to create a separate Nintex Content database for.            
$site = Get-SPSite $Url            
#Get the content database for the SharePoint Site            
$siteContentDb = Get-SPContentDatabase -Site $site            
#Get the top level farm object. We'll use this to get access to the farms config database server             
#Note: The Microsoft.SharePoint.Administration.SPGlobalAdmin class is deprecated. 
#I'm using it here, only becuase I'm trying to keep my PowerShell code as close 
#as possible to the code used in the Nintex admin pages            
$globalAdmin = New-Object Microsoft.SharePoint.Administration.SPGlobalAdmin            
#Get the Nintext Configuration Database            
$configDatabase = [Nintex.Workflow.Administration.ConfigurationDatabase]::GetConfigurationDatabase();            
#Check if there is an existing Nintex Content database with the name we want to use            
$contentDatabase = $configDatabase.ContentDatabases.FindByDatabaseAndServerName($globalAdmin.ConfigDatabaseServer,$NintextDatabaseName);            
#If the an existing database with the same name we want to use wasn't found, then we'll add it.             
if($contentDatabase -eq $null)            
{            
    Write-Host "The Nintex Content Database $NintextDatabaseName does not exist." -f Yellow;            
    Write-Host "Creating a new Nintex Content Database with the following name: $NintextDatabaseName" -f Yellow;            
    #Create a SQL connections string            
    $connectionString = ([String]::Format("Data Source={0};Initial Catalog={1};Integrated Security=SSPI;", $globalAdmin.ConfigDatabaseServer,$NintextDatabaseName))            
    #Initialise a new DatabaseAttacher object using the connection string            
    $dbAttacher = New-Object Nintex.Workflow.Administration.DatabaseAttacher($connectionString, 0)            
    #Set properties on the DatabaseAttacher object.             
    $dbAttacher.AttachOptions.CreateNewDatabase = $true;            
    $dbAttacher.AttachOptions.ProvideAllWebApplicationsAccess = $true;            
    $dbAttacher.AttachOptions.IncludeStorageRecordStep = $false;            
    #Finally, call the Attach() method to create and attach the 
    #database to the SharePoint farm.             
    $attachResult = $dbAttacher.Attach();            
    #Handle the success and failure scenarios            
    if($attachResult.CanContinue)            
    {            
        Write-Host "Successfully created a new Nintex Content Database with the following name: $NintextDatabaseName" -f Green;            
        if($attachResult.Warnings)            
        {            
            Write-Host "The following warnings were logged via creating the Nintex Content Database:" -f DarkYellow            
            Write-Host $($attachResult.Warnings) -f DarkYellow            
        }            
        #Get the new database we just created             
        $contentDatabase = $configDatabase.ContentDatabases.FindByDatabaseAndServerName($globalAdmin.ConfigDatabaseServer,$NintextDatabaseName);                    
    }            
    else            
    {            
        Write-Host "Error creating the Nintex Content Database." -f Red            
        Write-Host $($attachResult.Errors) -f Red            
        return;            
    }            
}            
else            
{            
    Write-Host "The Nintex Content Database $NintextDatabaseName already exists." -f Green;            
}            
            
#If the database was successfully created (or already existed), 
#update the mappings to associate the Nintex Content database 
#with the SharePoint Site Collection.            
if($contentDatabase -ne $null)            
{            
    Write-Host "Updating the Nintex Content Database mappings for site: $($site.Url)" -f Yellow;            
    #Create a new ContentDbMapping object, and get the current content 
    #database mappings for the Site Collection            
    [Nintex.Workflow.ContentDbMapping]$contentDbMapping;            
    $contentDbMapping = [Nintex.Workflow.ContentDbMappingCollection]::ContentDbMappings.GetContentDbMappingForSPContentDb($siteContentDb.Id)            
    #if there are no content database mappings found, initialise a new 
    #ContentDatabaseMapping object            
    if($contentDbMapping -eq $null)            
    {            
        $contentDbMapping = New-Object Nintex.Workflow.ContentDbMapping            
    }            
    #Set the properties of the ContentDatabaseMapping object to associate
    #the Nintex Content Database with the Site Collection            
    $contentDbMapping.SPContentDbId = $siteContentDb.Id;            
    $contentDbMapping.NWContentDbId = $contentDatabase.DatabaseId;            
    #Call the CreateOrUpdate() method to save the changes            
    $contentDbMapping.CreateOrUpdate();                
    Write-Host "Successfully added a Nintex Content Database mapping for site: $($site.Url)" -f Green;            
}