Ok, so you’re deploying PowerPivot for SharePoint 2013 and using the configuration tool? It does a great job, but I do have one niggle with it. During the Service Application creation step, you are not given the opportunity to change the database name. While this may not be a concern for some, many organisations (rightly so) have a naming convention for their SharePoint databases. Additionally, if the database backup files are located in the default path, combined with the default database name, it is entirely possible that the directory length is too long, causing your backups to fail! Consider this:
- [drive]:Program FilesMicrosoft SQL ServerMSSQL11.[InstanceName]MSSQLBackupDefaultPowerPivotServiceApplicationDB-c6324718-66bb-4344-9ed3-4f0496feff4e
What a mess!
So what can we do about this? Well the answer is rather nicely handed to us within the configuration tool. If you click the “Script” tab on the “Create Service Application” step, you will see the PowerShell cmdlet for precisely what we want. Obviously, you just need untick the “include this action in the task list” box and swap out the variables to suit your environment, but it couldn’t be easier. First, create the Service Application:
New-PowerPivotServiceApplication -ServiceApplicationName 'PowerPivot' -DatabaseServerName 'SharePointSQL' -DatabaseName 'SP2013_ServiceApplication_PowerPivot' -AddToDefaultProxyGroup:$true
Once you receive the “A PowerPivot service application was created successfully” confirmation, execute the next cmdlet:
Set-PowerPivotSystemService -Confirm:$false
Finally, you will receive the “PowerPivot system service instance was successfully updated” confirmation. Easy!
