Document #571, Moving Sales and Marketing to a New Server

As your company grows, it may become necessary to migrate your GoldMine FrontOffice 2000 installation from one server to another. This operation is not as simple as copying files from one place to another because of intricately intertwined elements that make up FrontOffice and their relationship with your current server. Care must be taken to adjust the properties, paths and pointers of various areas of concern including, but not limited to:

1. The Windows Registry
2. The GM.INI
3. The MSSQL Server
4. The Borland Database Engine
5. Linked Documents
6. Synchronization settings

Failure to properly adjust the settings of any of the above may lead to a situation of dependency on the old server for some functions and a decentralization and fragmentation of your data and installation and possibly a loss of data.

It is highly recommended that a knowledgeable FrontOffice Certified Solutions Partner should be involved with both the planning and implementation phases of your server move. The FrontRange Solutions Inc Sales department can help you to locate a FrontOffice Certified Solutions Partner in your area. FrontRange Sales can be reached at (800)654-3526.

FrontRange Solutions Inc cannot be held responsible for loss of data, damages or other adverse results encountered as a result of following the directions in this document. These steps are provided as is and cannot be elaborated on or supported by FrontRange Technical Support personnel.

NOTE: It is not recommended that you synchronize the data from your current FrontOffice installation to the new one, unless you plan on continuing to use the old installation in the production environment. Please also note that synchronization was not designed as a server migration tool.

This document assumes that a number of things are true:

1. The new server will have the same name as the old one
2. Your original FrontOffice installation was not a custom install
3. Your original FrontOffice SQL databases were the default databases created by the FrontOffice installation procedure
4. Your original BDE aliases were the default aliases created by the FrontOffice installation procedure
5. Your FrontOffice installation is running the current build of Sales and Marketing (v5.00.909 when this document was created
6. Your original FrontOffice installation was installed to the default path, c:\Program Files\GoldMine\

If any of these assumptions are incorrect, the procedures outlined below must be adjusted accordingly.

The New Server

Your new Windows NT 4 server should be named the same as the old server and should not currently have any network contact with the older server. It should not be configured to run any part of FrontOffice on the old server. If FrontOffice, BDE or MSSQL have previously been installed on the new server, they should be uninstalled before continuing. For best results, the old server should be offline or not connected to the network while MSSQL and FrontOffice are installed on the new server. To insure that the installation does not see your old installation's databases, follow the below procedure to remove existing BDE entries that may point to the old server's databases. If the new server does not have these registry entries, move on to Step 2.

Note: If it is not possible to name the new server the same thing as the old server, the steps on this document will fail and data will be lost or rendered inaccessible. If you must migrate to a differently named server, the information on pages 5 and 6 should be reviewed before going any further.

Step 1: Remove Any Current BDE Registry Entries

WARNING: Improper use of the Registry Editor can render your system unusable to the extent that you may have to reinstall Windows, and/or restore from backup. It is advised that you backup or export the registry by going to Registry>>Export Registry in the Registry Editor. FrontRange Solutions Inc cannot guarantee that problems resulting from the improper use of the Registry Editor can be fixed. Use the Registry Editor with caution and at your own risk

a)      Click on the Start button and choose Run

b)      Type in Regedit and click on OK to start the Registry Editor

c)       In the left pane of the Registry Editor, double-click on HKEY_LOCAL_MACHINE

d)      Double-click on Software

e)      Double-click on Borland

f)        Click on the Database Engine key and hit the Delete key on your keyboard

g)      In the Confirm Key Delete dialog box that appears, click on the Yes button

h)      Click on the BLW32 key and hit the Delete key on your keyboard

i)        In the Confirm Key Delete dialog box that appears, click on the Yes button

Step 2: Install MS SQL and FrontOffice 2000

Install GMFO and MS SQL 7 on your new server as outlined in the FrontOffice 2000 Installation Guide: ftp://www.goldmine.com/downloads/public/manuals/gmfo-ins.pdf

When choosing options in the installer, choose the GoldMine FrontOffice Server installation option and the same location on the new server. Meaning that if FrontOffice was installed to c:\program file\goldmine\ on the old server, it is recommended that you install it to the same path on the new server and share it in the same manner. Refer the FrontOffice Installation

Guide for details on sharing your FrontOffice server's files.

Steps 3 and 4 must be completed for each of the FrontOffice-related databases. In a default installation, these include:

GoldMine_Metric_Log_Combined

GoldMine_Metric_Log_Sales

GoldMine_Metric_Log_Support

GoldMine_Sales_And_Marketing

GoldMine_Service_And_Support

To perform a simple backup of an existing MSSQL Database:

a)      Launch the MSSQL 7 Enterprise Manager on the OLD server

b)      Double-click the server’s name to connect to the database server

c)       Expand the Databases folder

d)      Locate the GoldMine database

e)      Right-click on the database and select All Tasks>>Backup Database…

f)        Select Add

g)      Specify a name and location for your backup file and select OK

h)      Select OK to start the backup process

Copy the SQL backup file(s) created in Step 3 over from the \MSSQL7\Backup\ folder on the OLD server to the \MSSQL7\Backup\ folder on the NEW Server by locating the file(s) using windows explorer by right-clicking on them and selecting Copy and then pasting them to a network location from which you can then copy the file(s) to the \MSSQL7\Backup\ folder on the new server. Restore each of the backed up SQL database(s) from the OLD server into the corresponding database(s) on the NEW server with MS SQL’s Enterprise Manager using the steps below:

a)      a)   Launch the MS SQL 7 Enterprise Manager on the OLD server

b)      Double-click the server’s name to connect to the database server

If your databases were named anything other than the default databases listed at the top of the page, you’ll need to create them using steps c through f. If your old server used only the default FrontOffice databases, move on to step g

c)       Create a new MSSQL database on the new MSSQL server by right clicking on the databases folder and selecting New Database…

d)      d)   Give the database the same name as it had on your old MS SQL server

e)      Adjust the size of the database file and transaction log to be near that of the database on the old server and be sure to leave the automatic file growth settings to the default settings

f)        Click OK to create your database

g)      Right click on the database and select All tasks>>Restore Database…

h)      Highlight the From device… radio button

i)        Select Select Devices…

j)        Select Add

k)       Use the browse button to browse to the Backup folder and select your backup file and select OK

l)        Select OK to accept your backup file selection

m)    Go to the Options tab and select Force Restore over Existing Database

n)      Select OK to begin the restoration of your database

Step 5: Copy Your ODBC Settings

The FrontOffice setup will take care of creating the default ODBC entries for the default databases on the new server. In order for any custom Service and support databases to be usable on the new server, however, you will need to copy the ODBC data from your old server and duplicate it on your new server. If you have any custom Service and Support databases, follow the steps below to copy your ODBC data sources:

a)      Click on the Start button and select Run

b)      Type in Regedit and click on OK to start the the Registry Editor

c)       In the left pane of the Registry Editor, double-click on HKEY_LOCAL_MACHINE

d)      Double-click on Software

e)      Double-click on ODBC

f)        Single-click on ODBC.INI

g)      Go to Registry>>Export Registry File…

h)      Set the Export Range to Selected branch, and the box should contain “HKEY_LOCAL_MACHINE\Software\ODBC\ODBC.INI"

i)        Specify a name and location for the registry file and click Save

j)        Close the Registry Editor

k)       Copy the registry snippet to a temporary location in your network by right clicking on it in the windows explorer and selecting Copy and then pasting it to a location in the network

To import the ODBC data source registry entries on your new server, open windows explorer on your new server and navigate to the network location where you’re saved registry file exists. Double-click on the registry file. You will be prompted whether you want to add the information to the registry. Select Yes.

To properly move all of your system files from your old server to the new one, you will need to make sure that all users are out of both installations and that no files in either location are in use by any running services or applications, including GoldSync.

a)      Rename the c:\Program Files\Goldmine\ and c:\Program Files\Borland\ folders on the NEW server to GoldTEMP and BorlandTEMP by right clicking on each and selecting Rename. Overtype the old names with the new names. If an error message is received or the system reports that it could not rename either folder, double check that all users are out of GoldMine and/or reboot the machine and all applications that use the Borland Database Engine are closed. If file locks persist, the NT/2000 administration tools should prove helpful in locating and releasing them properly

b)      Copy the c:\Program Files\Goldmine\ and c:\Program Files\Borland\ folders from the OLD server to the NEW server’s Program Files folder by right clicking on each in the windows explorer and selecting Copy and then pasting them each to a location in the network

c)       If possible, temporarily shut down the OLD server at this point

d)      On the new server, copy the GoldMine and Borland folders from the network location and paste them into the Program Files folder on the NEW server

e)      Run Regedit on the NEW server and delete the BORLAND registry entries. See Step 1 for details on this procedure

Run the BDE admin from the BDEshare folder under \GoldMine\Setup\BDEShare\ on the NEW server. If you leave the OLD server running, when you launch the BDE admin do not assume that you have the right BDE config file open.

Verify that the path to the BDE configuration file listed in the title bar of the BDE Admin tool is to the correct location on the NEW server before modifying or checking any settings. Double-check that the aliases can connect to your NEW MSSQL server by clicking on the plus symbol next to the alias names themselves and entering a valid MSSQL username and password when prompted. If the entry turns green, the MSSQL server has been reached and the database specified in your alias has been connected to. Continue on to Step 8.

a)      Verify that your shortcut to gmw5.exe on the new server is correct and points to the executable file on the NEW server by right-clicking on the shortcut and viewing it’s Properties

b)      Open the GM.INI file located in your new server’s c:\Program Files\GoldMine\ folder (or your custom location) and verify that the SYSDIR, GOLDDIR, and COMMONDIR settings reflect the new server's proper paths

Note: Not all GM.INI files will contain all three settings. In most cases it is not necessary to add them.

Double-click on the GoldMine desktop icon. You should be presented with the login screen, which should accept your Sales and Marketing username and password and log you into your databases without error. If this step is successful and the OLD Server is either off or not connected to the network, your server is successfully migrated to the NEW server.

It is recommended that a pervasive check of your data be performed to verify that all data is available in the calendar and contact files before the temporary files and directories created throughout this process can safely be removed. For reference, these include:

a)      The temporarily installed GoldMine and Borland Files renamed to GoldMineTemp and BorlandTEMP in Step 6a

b)      Your old server’s GoldMine and MS SQL installations

c)       The copies of your GoldMine and Borland installations sitting in your temporary network location

d)      The MSSQL backup file located in your network location and in your new server’s \MSSQL7\Backup\ folder

Using a Different Server Name

It is highly recommended that you name your new server the same as the old server. If this is impractical or impossible in your situation, the following information details important data-related incongruities that can wreak havoc with your installation if they are not corrected. It is highly recommended that a knowledgeable FrontOffice Certified Solutions Partner should be involved in evaluating the scope of your data issues when moving to a differently named server. The FrontRange Solutions Inc Sales department can help you to locate a FrontOffice Certified Solutions Partner in your area. FrontRange Solutions GoldMine Sales team can be reached at (800)654-3526.

Data Updates

UNC paths are constructed by using the server’s name plus the shared folder(s) and subfolder(s) down to specific files, such as the path below:

\\GMServer\GoldMine\gmw5.exe

You’re Linked Documents, e-mail attachments, Crystal reports, signature files, files liked to InfoCenter entries and merge forms may contain pointers that point to UNC paths on the OLD server. If this is the case, there are two main options available to update them:

GoldBox

A popular third party utility distributed by Redstone Softbase Company, GoldBox, is capable of performing mass-updates on your data in a synchronization-friendly manner. This is crucial if the above-mentioned items are to synchronize properly in the future. A Solutions Partner familiar with GoldBox should be consulted to gauge the scope and difficulty of your data update needs and making the decision as to whether or not GoldBox is the proper tool for the job.

SQL Stored Procedures

GoldMine's SQL Stored Procedures were created to allow Database administrators the update their GoldMine data in a synchronization friendly manner based on criteria they supply. The Stored Procedures can be utilized to update your data to reflect the new Server's name for records still pointing to the old server. The SQL Stored Procedures can be downloaded at ftp://ftp.goldmine.com/public/developer/gmsqlapi/GMSProcs.zip

The following example script can be executed in the MSSQL query analyzer to utilize the SQL stored procedures to modify the server name portion of all linked documents containing paths like \\serverold\docs\goldminestuff\somedocument.doc to \\serverNEW\docs\goldminestuff\somedocument.doc.

NOTE: This script is provided as-is. FrontRange Solutions Technical Support cannot provide assistance with troubleshooting, modifying or using the script.

NOTE:
1. Change the @userid variable to reflect the user name that you wish to use as the updating user. In this example 'ANGEL' is used as the value of the @userid variable.
2. "Attach" refers to the pattern we are looking for, i.e. C:\attach\LinkedFile.DOC, and "Detach" refers to the text that will be used to replace that value, i.e. change to C:\Detach\LinkedFile.DOC.
3. GoldMine_Sales_And_Marketing is the name of the test GMSM database that was used.

USE GoldMine_Sales_And_Marketing
DECLARE @test varchar(255), @tablename varchar(20), @recid varchar(15), @userid varchar(8), @action varchar(1), @fieldname varchar(20)
DECLARE docs_cursor CURSOR FOR
SELECT linkeddoc, recid FROM contsupp WHERE rectype = 'L'
SET @tablename = 'CONTSUPP'
SET @userid = 'ANGEL' --Change this value to reflect the user name you wish to use as the updating user.
SET @action = 'U'
SET @fieldname = 'LINKEDDOC'
OPEN docs_cursor
FETCH NEXT FROM docs_cursor INTO @test, @recid
WHILE @@FETCH_STATUS = 0
BEGIN

PRINT @test
SET @test = (SELECT REPLACE(@test,'Attach','Detach')) --"Attach" refers to the pattern we are looking for, i.e. C:\Attach\LinkedFile.DOC, and "Detach" refers to the text that will be used to replace that value, i.e. change to C:\Detach\LinkedFile.DOC.
PRINT @test
UPDATE contsupp SET linkeddoc = @test WHERE CURRENT OF docs_cursor
EXEC GMW_UpdateSyncLog @tablename, @recid, @userid, @action, @fieldname
FETCH NEXT FROM docs_cursor INTO @test, @recid

END
CLOSE docs_cursor
DEALLOCATE docs_cursor

GoldSync Settings

GoldSync sites on your server may point to paths located on the old server. Any paths should be corrected manually to insure correct updates. GoldSync processes on your new server will need to be created to match those defined for your old server or GoldSync will not properly listen for incoming connections on the new server.

BDE's Aliases

If your server name is changed, you will need to run the BDE admin from the BDEshare folder under \GoldMine\Setup\BDEShare\ on the NEW server and change the details of all GoldMine related aliases to reflect the new server name and database names (if required). If you do leave the OLD server running, when you launch the BDE admin do not assume that you have the right BDE config file open. Verify that the path to the BDE configuration file listed in the title bar of the BDE Admin tool is to the correct location on the NEW server before modifying any settings. Double-check that the aliases can connect to your NEW MSSQL server by clicking on the plus symbol next to the alias names themselves and entering a valid MSSQL username and password when prompted. If the entry turns green, the MSSQL server has been reached and the database specified in your alias has been connected to.

Workstation Settings

Unless the new server has the exact same name as the old server AND the GoldMine folder is shared on the NEW server EXACTLY as it was on the OLD server, all the BORLAND registry entries and GoldMine shortcuts on every workstation will be wrong. The easiest thing to do is to delete the old registry entry for BORLAND and delete the GoldMine shortcut, then create a new shortcut and launch it to re-install the BORLAND registry entries on each of the workstations. Savvy Network administrators may also wish to make use of NT's network login script to copy a shortcut to the new GoldMine executable and import a .REG (registry key) containing only the \HKEY_LOCAL_MACHINE\Software\Borland\ key on each workstation upon login to the network.

Copyright (c) 2002 FrontRange Solutions Inc.

All rights reserved. You may use this document for personal and informational (non-commercial) purposes, provided that the copyright notice and all other notices and permissions appear in all copies, the document is not copied or posted on any network computer or broadcast in any media and modifications are not made to the document. Use for any other purpose is expressly prohibited by law, and may result in civil or criminal penalties.

The information contained in this document is provided “as is” without warranty of any kind. To the maximum extent permitted by applicable law, FrontRange Solutions disclaims all warranties, either express or implied, including warranties for quality, accuracy, merchantability, fitness for a particular purpose, title and non-infringement; and in no event shall FrontRange Solutions or its suppliers be liable for any damages whatsoever including direct, indirect, incidental, consequential, loss of profits or data or special damages, even if FrontRange Solutions or its suppliers have been advised of the possibility of such damages.