This past weekend was an interesting one. Not only was it my birthday, we had an Enterprise 6.3 client's SBS 2008 R2 server have 3 of 6 drives in a RAID 5 array in a pending error status. There was serious talk of a punctured array. It's a small business so it not only was it the sole active directory provider, it also managed DNS, DHCP, and SQL servers not to forget File and Print service. The IT staff was busy rebuilding arrays, drives, and restoring backups, time estimates for data transfers were given in half days. About a day and a half and this job should be complete. Then it failed, another day long attempt and failure had the calls out to put Option "D" in place.
Option "D" was a hastily thrown together thought experiment, one of those if all else fails, this might keep us going solution. It was to provide a temporary ProForm environment complete with lookup tables and templates, along with this years data files and this years soft file folders. Since it was a Plan 4, no budget was allocated for trial or testing. The direction was you're smart, copy what you think you'll need. And by the way gather quickly, the server is going to be rebooted in a little over an hour. It helped that we knew the network well.
The preparation process was pretty simple, grab the shared SoftPro folder, SQL data and file data, then copy it somewhere else. The first interesting thing was, the SoftPro folder has grown over time with various folder being used as temporary folders. With the time constraint in place, we didn't want to copy all that data. We didn’t need reports just the critical folders, Data, Merge, RdyDocs, Templates, Tools and the contents of the root of the SoftPro shared folder. (A note on those folders, even though it's big, grab Setup, it comes in handy, we also had another unique folder Overlays that we copied.)
There are things to consider when attempting a maneuver such as this, we invested mere minutes into those considerations. Here we take a look at some of the things that come up when slapping a temporary ProForm solution in place. Somewhat in the order in which we saw them.
ProForm validates the correct version between the client and server based on 2 files, the first lives in the %WinDir%, usually C:\Windows and is called SoftPro.ini. It looks like this
[Setup] ServerDestDir=\\Granite\SoftPro\ ServerInstallerType=Wix Version=6.3.61120.0 SrcDir=\\Granite\SoftPro\Setup\1 ClientDestDir=C:\Program Files (x86)\SoftPro\Enterprise\Client\ ClientInstallerType=Wix
The Version in the SrcDir= Folder needs to match the executing ProForm Version, the number after Version= in the SoftPro.ini file is for technician reference, the app as of 6.3.6 doesn't require it. Which brings us to our second file, version.ini which typically lives in the SoftPro Shared Folder\Setup\1 folder. It looks like this:
That is the number that must match the running version of ProForm. If the SrcDir in SoftPro.ini is incorrect or the CurrentVersion in version.ini is incorrect the system will throw a SPFUNCS-350 Error. The link has a bit more info on this error.
I should mention to edit files in the Windows Folder you must change the security to allow Users to Modify files. Do that on SoftPro.ini, FormSet.ini and you might as well do pfwin.ini to get your recently used files list back.
A bit of preparation work is necessary if you hope to run on more than one machine. SQL server needs security granted to access the information. Once the domain is down, that security can't be added. So a user needs created that mirrors a user on the production server. The group Domain\Domain Users works if it's there. The restoration of the SQL tables was easy if a bit click happy. The SQL tables that are needed to run ProForm are ProForm, ProFormLookups, ProTrust and SoftPro. Restore the others at your pleasure or need. (See http://danvanfleet.com/index.php/automated-softpro-sql-database-backup/ for a method to backup the SQL database)
ProForm Database Links
If started, ProForm will now complain it can't find it's databases. SoftPro has a great configuration utility, SpSqlReg.exe which typically is installed into the SoftPro Shared Folder. (\\Server\SoftPro) It has a configuration file, SpSqlReg.ini which were going to edit. (no need to mess with security on this one) Open it up in your favorite text editor,
Profile = Default DSN Type = System SQL Server Name = DeadServer\softpro PfPx Database = ProForm PTWin Database = ProTrust P1Win Database = Pro1099 PiWin Database = ProIndx SoftPro Database = SoftPro PSWin Database = ProScheduler WAN Lookups = ProFormLookups Path to LAN Lookups = \\DeadServer\softpro
Copy including the Profile line, to either the next Profile line or the end of the file. Paste that to a new section of the file, Edit the Profile name and Server Names to make something like this.
Profile = Backup DSN Type = System SQL Server Name = TempServer\softpro PfPx Database = ProForm PTWin Database = ProTrust P1Win Database = Pro1099 PiWin Database = ProIndx SoftPro Database = SoftPro PSWin Database = ProScheduler WAN Lookups = ProFormLookups Path to LAN Lookups = \\TempServer\softpro Profile = Default DSN Type = System SQL Server Name = DeadServer\softpro PfPx Database = ProForm PTWin Database = ProTrust P1Win Database = Pro1099 PiWin Database = ProIndx SoftPro Database = SoftPro PSWin Database = ProScheduler WAN Lookups = ProFormLookups Path to LAN Lookups = \\DeadServer\softpro
Run SpSqlReg.exe as Administrator, select the newly named template, Press Go, the program will note that it updated the ODBC connections for the machine.
Standard SQL users might have to run it as both Administrator and user. MDB or Access users very few concerns, put the file somewhere, point to those files with %WinDir%\SysWow64\OdbcAd32.exe
SpAdmin will need run at some point, it gets a little goofy and locked up a few times. If that happens, kill the task with Windows TaskManager. SpAdmin.exe lives in the SoftPro\Tools\SpAdmin Folder. Click the Edit Shared Directory and enter the new temporary path. Exit and restart SpAdmin. Next change the User Preferences for the <default> user. If you use File Numbering Manager’s unique directory option Edit the Save Directory for Data Files for each File Numbering Code.
The next stopping point will be licensing, at this point ProForm had no clue where to find the license files. Standard and Enterprise handle licensing differently, Standard should license correctly after editing the registry entries, we will have to wait and see what ProForm 14 does to these thoughts. At one time, still with Standard, the HKCU\Software\SoftPro\PFPXWin folder holds keys that point to the various configuration items, like Files Folder, Licensing folder, or the other paths to configuration files. With ProForm Standard, edit those paths and skip the rest, don’t mess up the trailing backslash if it’s there. That all changed with Enterprise a couple versions ago, which now populates the Registry on program launch from a couple SQL tables. In the ProForm Database the Preferences Table is used to handle most of the folders, but not Licensing. That is in the SoftPro Database in the PreferencesByUser table.
Both tables are similar in nature with a default entry as the first entry of the table and user names under that. If you’re creating new users, simply change the default entry’s paths to point to the temporary location. This information is read in an interesting way, since it’s related to licensing I won’t delve into how it all works. If your temporary user’s name is in the database, I recommend either deleting that entry or changing the username on it so it doesn’t match the temporary user name.
An SPFUNCS-134 can mean there is a problem in the Preferences table for the user or default entry.
From a technician point of view the flow of this configuration information is confusing. The Registry isn't the boss, it exists for compatibility purposes with the new database driven preferences. We'll probably see more of this with ProForm 14.
There are a few things that can get in the way when moving SoftPro ProForm from production to emergency use when little planning occurs. This story turned out with fairly good news, 40% of the staff could access ProForm not real long into the first day. The second day the server was back and working superbly. Merge the changed ProForm files back into the production system and close the next loan was the day. Since we've gone this far with this effort I think it's time to do that preparatory work. The question at hand is how few resources can be used to setup a backup system for SoftPro ProForm which can be brought on-line in about a half hour. I'll let you know how it comes out.
Thanks for stopping by.