Considerations while provisioning the service application using pre-created databases”there is already a database exists with the same name”

* 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 inthis 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.

Categories: Service Application

Tags: , ,

%d bloggers like this: