Considerations while provisioning the service application using pre-created databases

Recently I had worked with my customer to setup a new SharePoint farm with pre-created (DBA created) databases. Concept and prerequisites are very well documented in this TechNet article. While testing the deployment what really matters were the creation of couple of service application. Bad guys were Usage Logging & State Service Application. All other service applications were easy to provision (we were using auto AutoSPInstaller to automate the deployment using Power Shell).

Except Usage Logging & State Service application all other service applications were able to use the pre-created database while provisioning the new service application using New-SP* commandlets. Usage Logging & State Service applications were complaining that the there is already a database exists with the same name. To work-around this, we have to approach the provision in a different way. I’m giving the provisioning script samples for both Usage Logging & State Service Applications.

Usage Logging Service Application :

Steps are explaining below.

1. Create new Usage Service Application with a temporary ( or default) database

2. After that, modify the Usage Service Application to use the pre-created database name, this will dismount the usage logging database created in step #1

3. Delete the database created in step #1 manually from the SQL server

Automating the Steps 1-2 given below.

 add-pssnapin microsoft.sharepoint.powershell
 Write-Host -ForegroundColor White "Provisioning WSS Usage Application..."
 $SPUsageApplicationName = "WSS Usage Service Application"
 $precreatedUsageDB = "WSS_Usage_Database"
 $sqlserver = "spsql"
 $tempUsageDB = "WSS_Usage_ToDelete"
 New-SPUsageApplication -Name $SPUsageApplicationName -DatabaseName $tempUsageDB               
 $UsageServiceApp = Get-SPUsageApplication $SPUsageApplicationName
 Set-SPUsageApplication -Identity $UsageServiceApp.ID -DatabaseName $precreatedUsageDB -DatabaseServer $sqlserver
 Write-Host -ForegroundColor White "Re-provisioning Health Data Collection Proxy as by default it will be in stopped state"
 $SPUsageApplicationProxy = Get-SPServiceApplicationProxy | where {$_.DisplayName -eq $SPUsageApplicationName }
 Write-Host -ForegroundColor Blue "Done provisioning SP Usage Application."

State Service Application :

This is little different than Usage Logging Service Application provisioning.

Steps are explaining below.

1. First we have to mount the pre-created database to SharePoint and have to initialize it.

2. Use the same method as other service application provisioning, using New-SPStateServiceApplication and provide the pre-created database name.

3. Create state service application proxy.

 add-pssnapin microsoft.sharepoint.powershell
 $StateServiceDB = "State_Service_DB"
 $StateServiceName = "State Service Application"
 $StateServiceProxyName = "State Service Application Proxy"
 Write-Host -ForegroundColor White "Provisioning State Service Application..."
 Mount-SPStateServiceDatabase -Name $StateServiceDB | Initialize-SPStateServiceDatabase
 New-SPStateServiceApplication -Name $StateServiceName -Database $StateServiceDB | Out-Null
 Write-Host -ForegroundColor White "Creating State Service Application Proxy..."
 $StateServiceApplication = Get-SPStateServiceApplication -identity  $StateServiceName
 $StateServiceApplication | New-SPStateServiceApplicationProxy -Name $StateServiceProxyName -DefaultProxyGroup 
 Write-Host -ForegroundColor Blue " - Done creating State Service Application."


Other service applications can be pointed in this scenario are

1. ASP.NET Session state service (Enable-SPSessionStateService). With that there is no way to specify a pre-created database , 

    ASP.NET session state database schema is provisioned by ASP.NET by calling SQLServices class. once you use Enable-SPSessionStateService.

From my deployment experience this was the main issue and there was no any other deployment issues faced while going with the DBA pre-created deployment method. Hope this hint will help someone.