We have recently had the requirement to move the SQL databases to a new Microsoft SQL Server 2008 R2 server. This process is not complicated, but there does seem to be a lack of documentation available. At a high level we need to perform the following actions:
- Backup the RES Workspace Manager and Automation Manager databases on the source server;
- Restore the RES Workspace Manager and Automation Manager databases on the destination server;
- Fix the SQL permissions, i.e. recreate the users and redelegate access;
- Update the RES Automation Manager Dispatchers to point to the new database server;
- Update the RES Workspace Manager Agents to point to the new database server.
I’m not going to cover Steps 1 and 2 as these are well documented on Microsoft’s web site and many other various blogs. In this particular instance we’re moving from SQL 2008 to SQL 2008 R2 and I’ve restored copies of the Workspace Manager and Automation Manager databases on the new server. The SQL user account for the Workspace Manager database (RES-WM) is ‘RES-WM’ and the user account for the RES Automation Manager database is ‘RES-AM’ (note that naming the database and user accounts the same is not best practice but it helps in our lab environment!).
Migrating RES Automation Manager
We’ll start with the RES AM database as we’ll then use this to update the RES WM information! Firstly we need to check that the correct user permissions have be granted on the new database server. When creating the SQL user accounts you’ll need to ensure that the password policies are set correctly:
In short make sure that the user password policies are disabled (unless you want to be forever updating your Dispatchers!). If you forgot to uncheck this and you can’t seem to change it, you can run the following SQL script via the SQL Management Studio (remember to change the RES-AM reference to your SQL user account!):
USE Master
GO
ALTER LOGIN “RES-AM” WITH PASSWORD = ‘samepassword’
GO
ALTER LOGIN “RES-AM” WITH CHECK_POLICY = OFF, CHECK_EXPIRATION = OFF;
After this is complete you will need to ensure that the RES-AM user account has DB Owner (DBO) rights to the database via the “User Mapping” page of the user account:
Once we’re happy with this we can focus our attention on the RES Automation Manager console. As it’s only the RES AM Consoles and Dispatchers that talk directly to the SQL database, we do not need to worry about the RES AM Agents. From the RES AM management console select the Infrastructure > Setup > Database node and enter the new SQL database server name (if the username/password has changed you can update them here).
After you click the Connect button the management console will reload and ask if you want to use the connection information permanently.
The management console will reload. The final piece to this puzzle is to update all the Dispatchers and Consoles. From the Infrastructure > Engines node we need to repair each Dispatcher and update the SQL connection information.
After the Dispatchers have been updated don’t forget to update the consoles in the same manner. You could also run a registry job via AM to update the connection information (remember to do this from the old database as the Dispatchers will communicating with the old database until updated!) or push out a new MSI from the Components node.
Migrating RES Workspace Manager
Note: RES Workspace Manager 2011 has the built-in ability to migrate the exisiting database to a new SQL server and/or database; this can be found in Setup > Datastore > Connections > Click on the ‘…’ next to the Primary datastore to display the migrate wizard. At the end of the migration process after various other prompts you will also be prompted if you wish to create a handy building block that can be used in RES Automation Manager Module. You can use this Module to migrate RES Workspace Manager Agents running an older version of RES Workspace Manager, not yet containing the Datastore Migration Wizard. The only downside using the migrate method I’ve found is the fact you have to activate the licenses again; if this going to cause some issues follow the procedure set out below. [Nathan Sperry]
RES Workspace Manager is slightly different as all RES WM agents talk directly to the SQL server rather than via a Dispatcher. After migrating the RES Workspace Manager database (as above) and fixing the user permission we need to update the RES WM agents’ registry settings via RES Automation Manager! For this task I created a module that updates the required registry value and restarts the RES Workspace Manager agent service.
You will notice that there are two Reigstry Settings tasks with conditions; 1 is for 64-bit machines and the other for 32-bit. Note: if the authentication details have changed you’ll need to the relevant registry settings to both Registry tasks.
Note: if you have a mixture of freshly deployed RES Workspace Manager agents and agents upgraded from RES PowerFuse 2010 or earlier then the registry settings are in different locations and you may have more tasks/conditions!
Iain
