RES ONE SQL migration
Recently I had to move all my RES ONE datastores to a new SQL server. Here are the migration processes for the following RES ONE products:
- RES ONE Workspace
- RES ONE Workspace Relay Server
- RES ONE Service Store/ Identity Director
- RES ONE Automation
RES ONE Workspace Datastore
RES ONE Workspace is the easiest product to migrate because the migration feature is built-in.
Go to Setup and click on Datastore.
First thing to do is to look if the datastore is split. You can see the Secondary data store is checked.
In the example above there is a split datastore. Before we can migrate the datastore we first need to join the datastore again by making it one datastore (database).
RES ONE Workspace (ROW) comes with a wizard to do this. Next to the secondary datastore click on the … symbol.
At the bottom you will find the Split/Join button, click on the button.
Now the wizard will open and with a few clicks you can join the datastores together again.
When that’s done you will go back to the datastores screen again. You will now see one datastore (primary) click on the .. symbol next to it.
Then click on the Migrate… button. Now the migration wizard will start and with a few clicks you will have migrated to your new databases server. At the end of the wizard you will be asked if you permanently want to switch to the new datastore.
RES ONE Workspace Relay Server
After the datastore is migrated it is necessary to update your Relay servers with the new datastore. Open the relay server configuration tool, select the old connection and click on Remove.. After that, click on Add.. this will start the RES ONE Workspace Relay Server connection Wizard. With this wizard you can connect to the new datastore.
RES ONE Automation
Unfortunately RES ONE Automation (ROA) doesn’t have a migration Wizard. To migrate ROA datastore we need to start SQL studio on the old database server. From there we can create a database back-up by clicking right mouse on the ROA database, select task and then Back Up…
Now the Back Up Database screen will appear. Select backup type Full and back up to Disk. Now click on Add.. and select a location to place the backup and give the backup a name, for example RESAutomation.bak
Click on OK at the bottom of the screen. The back-up will start. Once finished copy the .BAK file to the new SQL server.
On the new SQL server start SQL Studio and right click on Databases and click on Restore Database…
Select Device and browse to the .BAK file in the restore window. When the .BAK file is selected the restore windows will read the database information and fill in the Database name. Click in the bottom on OK. The database will now be restored.
Create a new SQL account for Automation or add a Windows User or Group (with rights in Automation) on the new SQL server. Give the new user or group the db_Owner right on the ROA database.
In the example above you can see that there is a new user created, RESONEAutomation2, and that the user has DB_OWNER rights on the ROA database.
Shut down the old database and start RES ONE Automation Console. This will result in an error because the datastore could not be reached. After this you can edit the datastore connection to use the new database server \ instance and username. Click on Connect. If the migration was successful you will now connect to the new datastore. It’s important to repair your dispatchers and agents. This can be done from the console under administration -> Agents and Dispatchers. When this is done the migration is finished.
RES ONE Service Store /Identity Director
For RES ONE Service Store / RES ONE Identity Director there also is no wizard. So follow the same steps with SQL studio as RES ONE Automation. Create a back-up from the old SQL server and restore it on the new SQL server. Don’t forget to recreate the SQL credentials/
Startup the Setup and Sync Tool after the database migration, click on Setup and click on Datastore…
Edit the connection settings with the new SQL server and Instance and click on connect.
Now we need to edit the management portal, browse to the following folder:
Here you will find the WebConsole.Config file edit this file at server=”FILL IN NEW SERVER NAME”
Now you can start the management portal. Login and go to Setup -> Datastore. Make sure that the connection is correct with Test Connection and than save the settings.
Now restart the catalogue and Transaction services.
If you use the mobile gateway for the iOS and Android app you will need to change the following. Browse to this folder:
Edit the service.config file add server= fill in the new SQL Server \ Instance and than restart the webserver.
Now the RES ONE Service Store / Identity Broker is successfully migrated.
I hope this was informative. For questions or comments you can always give a reaction in the comment section or contact me:
2 thoughts on “RES ONE SQL migration”
Thanks for the post. However, I had to do two more steps to make Service Store Catalog service and Transaction service connect to the new database instead of (still) the old one:
In an admin cmd box:
“C:\Program Files\RES Software\IT Store\Transaction Engine\resote.exe” /configdb
“C:\Program Files\RES Software\IT Store\Catalog Services\resocs.exe” /configdb
Both commands open an additional database connection window where the server needs to be changed…
Thank you for the extra info!