Wednesday, January 16, 2013

Relocating a Database (Shelby)

If you've ever had to relocate your Shelby Systems database to another server then you might want to continue reading.  Shelby Systems is a Church Management database used by a lot of churches and non-profits.  I've had to move our Shelby database to a different server twice.  The first time I had to move it was in crisis mode because the hard drives in the server had failed.  Once we rebuilt the data we relocated it to a new server and the following article was very helpful.

The second time we moved the database to our new virtual sequel server cluster.  This move was a bit more challenging as the above article was written for Windows XP clients and not Windows 7 clients, which is what we run.

Ideally the above article on their Community is all you would need to know but for Windows 7 the location of some key files changed and that made the process a lot more challenging.  The biggest thing to note that is not in their article are the changes to various INI files in the DAT folder.

All Shelby installs have an ssv5.dat folder and an ssv5.prg folder.  Inside these folders are a few INI files that it would be good to check and confirm that you've changed the server paths and IP address to point to your new server.  Then all you would need to do to update the clients is change the shortcut on the client to point to the new database server.  This works well except on Windows 7 machines.  Depending on how you have Windows 7 configured for your users (local admins, not, redirected profiles, etc.) there is another set of INI files that need to be updated.

I did everything listed in the article but I couldn't get any reports to run.  This is the error I kept getting.

The client Event Viewer then showed an application event stating that PowerMerge could not find the report path.  It then listed the wrong report path.  It wasn't until I found this second copy of the INI file that I knew why the wrong path was being used.

In C:\Windows there is a formsetini file that I had manually updated.  There is a second copy of the file located in the user's profile at C:\Users\%usersname%\AppData\Local\VirtualStore\Windows.  This file is the one that was not being updated by the application and it wasn't until I manually updated this file that the clients were able to run reports again.

The C:\windows\formset.ini file can only be changed if you are running Shelby as the admin.  Otherwise the local profile version of the file is what the software looks for.  The C:\Windows version is used when you login to Shelby as a new user and is copied to that users profile on the local machine.

We have many clients running this program so to make it easier I used Group Policy Preferences to automatically update the files on all our clients.  This policy will then allow me to easily make this change again should we for whatever reason have to relocate to another SQL server.

It took me hours to figure this out and get our reports working again, if I was smarter it might not have taken so long.