Reconfiguring ServiceStore to a new datastore

By Max Ranzau


This article will help you change an environment from an existing RES Service Store database to a fresh new one. In my case I needed to spin up a fresh database to weed out an error which I did not want to import into my production environment, thus I needed a temporary disposable working servicestore without having to build one from scratch in my development environment. In other words, this operation allows you to switch your servicestore back and forth between several databases. There’s however a couple of things you’ll need before you start:

  • Current SA password for the SQL server
  • Current Catalog Services password as we don’t want to bother changing that everywhere
  1. First, start the Setup & Sync Tool, and go to setup |database in the menu.
  2. Do not touch the settings there, just hit the create button in the lower left.

  3. Fill everything out in the next wizard Note the password will not be your SA password pre-filled, it’s just a bunch of dots, you will have to know the SA pwd to continue here. Note to someone who would bother putting it in Uservoice: Password field should be blank.

  4. After successful authentication, name your new datastore:

  5. Size your database correctly. If this is just a dummy/scratch database, you can just leave it at the defaults. Otherwise if you’re importing a massive amount of stuff from buildingblocks, you’ll do well to size the DB accordingly.

  6. Enter the new SQL credentials. Note that even though you might already have an SQL user you’d like to use, the installer insists on creating a new one. This can be fixed later, so just enter something sensible and continue.

  7. If circumstances allow, re-use the current Catalog Services password as that will save you a boatload of configuration

  8. Hit, next, verify everything looks kosher then hit the Creat button. The wizard will now generate a new datastore with fresh new tables from scratch.

  9. Once you hit Finish the Setup&Sync thingy will relaunch, pointing to the new, empty servicestore DB.
  10. If you’re just spinning up a temporary blank store to test something, you may want to re-use the SQL user from your previous database. This will save you even more reconfiguration hassle. First, you’ll want to hop into your SQL Management studio and give the old SQL user DBO permissions on the newly created database. Rightclick on the old SQL user, chose properties (1), go to the User Mapping section and checkmark the new database (2), then checkmark db_owner at the bottom (3)

  11. Go to your catalog server(s) and start regedit. Navigate to HKEY_LOCAL_MACHINE\SOFTWARE\RES\ITStore\Catalog Services and change the database name in the REG_SZ value DBName to the new database name
  12. Start the services snapin and bounce the service “RES ONE Service Store Catalog Services” aka RESOCS
  13. Go to your transaction engine(s) and change the REG_SZ DBname to the same name in HKEY_LOCAL_MACHINE\SOFTWARE\RES\ITStore\Transaction Engine
  14. While there, bounce the service “RES ONE Service Store Transaction Engine” aka RESOTE
  15. Fire up your browser and go to the store /management site. Once there you go to the burger menu, setup, datastore. Since we kept the SQL username and password the same, the only thing you will need to change is the database name as shown. The console will offer a dropdown showing all the databases available, including your new one.

  16. Once you’ve saved the changes, use the Test Connection to verify you’re good to go, then hit save. Note that you’ll be kicked out of the console for re-login, but since there are no security roles defined in the blank database, you’ll be able to log right back in, using your normal administrative account.

You are now running on a new fresh database with the 25 built-in 45 day eval licenses. The same process can be reversed to hop back to your original database if you should be so inclined. Do however remember in addition to run the Setup & Sync Tool, go to the Setup|Database, pointing it the correct database. Just because you change the web-interface to one datastore, the S&ST can still be pointing to another.

Overall, the advantage of this approach is that you do not have to change any additional servicestore components such as the mobile clients, website or windows clients as they’re all pointing to either the website or catalog servers, which haven’t changed.

A couple of closing notes: If you are using this approach to debug a ServiceStore database, where you need to restore the database ever so often to a previous backup, you are likely to run into the problem that db-restore takes forever. I’ll just sit there and wait for hell to freeze over, eventually failing because the datastore is in use. There is a easy way to get around this. WARNING: Use this only on a garbage ServiceStore datastore which you’re going to discard anyway.

  1. On the SQLserver housing the ServiceStore database you want to unlock, start a command prompt
  2. Start SQLCMD (should be in the path on a SQL box)
  3. Paste this in at the 1> prompt: ALTER DATABASE yourSSDBname SET OFFLINE WITH ROLLBACK IMMEDIATE
  4. Hit enter
  5. Type GO at the 2> prompt and hit enter again.

This will take a few moments, but is lightning fast compared to the alternative. It will produce output something like this: Nonqualified transactions are being rolled back. Estimated rollback completion: 0%. Nonqualified transactions are being rolled back. Estimated rollback completion: 100%.  Once complete the locked servicestore database is offline and you can restore it immediately.