How to change SQL Server, Instance or Port for SMA Web Service and Runbook Service
This is a quick post on how to change SQL connection parameters for SMA Web Service and SMA Runbook Worker Service.
If you ever have to migrate the database to another SQL backend, change the instance or just the port, here are the necessary steps.
Preparation
- Stop all Runbook Worker Services and SMA Web App Pools
- Change SQL Server, Instance, Port or migrate the database to the new target server
- If you have migrated to a new instance / server, make sure the SQL logins for SMA Web App Pool and Runbook Service exist in the instance
SMA Runbook Worker Service
This is the easy part. 🙂
- Open the configuration file C:\Program Files\Microsoft System Center 2012 R2\Service Management Automation\Orchestrator.Settings.config with elevated rights.
- Change the port / server or instance name accordingly. To use the default port 1433, just enter the server or server\instance value without “,
” - Change the installation information in the registry to match the new values: HKEY_LOCAL_MACHINE\Software\Microsoft\ServiceManagementAutomation\RunbookWorker
- Restart Runbook Service
SMA Web Service
Now a little bit trickier…
The db connection string section for the SMA Web Service config file is encrypted. So first we have to decrypt this section.
- Open an elevated PowerShell console or CMD
- Navigate to your .NET Framework home dir (e.g. C:\Windows\Microsoft.NET\Framework64\v4.0.30319 )
- Decrypt config file section
.\aspnet_regiis.exe -pdf "connectionStrings" 'C:\inetpub\Service Management Automation'
</li>
* Open elevated notepad and edit the **web.config** file in **C:\inetpub\Service Management Automation ** </ul>
* Encrypt the config file section again:
<pre>.\aspnet_regiis.exe -pef "connectionStrings" 'C:\inetpub\Service Management Automation'</pre>
</li>
* Change the installation information in the registry to match the new values: HKEY\_LOCAL\_MACHINE\Software\Microsoft\ServiceManagementAutomation\WebService 
* Restart the SMA App Pool</ul>
## Post-Checks
* Check if Web Service is working by talking via PowerShell to it, e.g. <pre class="crayon-selected">Get-SmaRunbookWorkerDeployment -WebServiceEndpoint <a href="https://sma001">https://sma001</a></pre>
* Check if Runbook service is accepting jobs, you should see EventID 2227 (Service is ready to accept jobs) after restarting the service 