Follow this procedure to change the database server:
- Stop the CranSoft/DSP Service(s) and IIS on the application server.
- Back up all the databases on the original database server.
- Restore those databases to the new database server.
- Create a login on the new database server for the CranSoft/DSP app to use. Ideally this login will have the same name and password as the one already in use - if so this will make things easier.
- Assign that login to every database and give it dbowner privileges. (you might have to remove the original database user before doing this).
- On the application server, open the CranBerryConfigurator tool (in the \Web\Bin directory).
- For DSP, the details only need to be changed once on the main Configurator page. On the right hand side, change the Server to the new server name, and the username and password to match the new login. Ensure that Connection Timeout is set to 60 and CommandTimeout is set to at least 300. Click Validate, and then Save. Skip steps 8 and 9 below and continue with step 10.
- For CranSoft: Expand Services and select the CranSoft Service. On the right hand side, change the Server to the new server name, and the username and password to match the new login. Ensure that Connection Timeout is set to 60 and CommandTimeout is set to at least 300. Click Validate, and then Save.
- Expand Sites, and Default Web Site and select the CranSoft virtual directory. Update the Server, username and password fields just as you did for the service and confirm that timeouts are set to appropriate values. Click Validate and then Save.
- Open SQL Server Management Studio, connect to the new database server and display the contents of the following tables (not all exist in CranSoft):
- DataGarage.dbo.dgTarget (specifically, the ConnectionString column)
- DataGarage.dbo.dgTargetSource (again, the ConnectionString column)
- Update values in these tables that refer to the old server name to the new server name. Also update username and password as appropriate.
- Start the CranSoft/DSP Service and IIS. Log into CranSoft/DSP. Navigate to System Administration / Configuration / DataSources and validate each datasource in turn, responding to any errors as appropriate.
The process overall is pretty straightforward and local client DBAs should be able to assist you with most of the initial steps.