Sage CRM 2019 R2: Some thoughts about moving database servers

A customer had a requirement to move database servers. This was a straight move from one server to another rather than upgrading database versions.

There have been some very good tips and advice shared on the forums and I thought it would be useful to bring this advice together in one space.

I have made the assumption that we are moving from a single webserver/database server to another single webserver/database server.

If that is the case then I would suggest on the new webserver you start by doing a clean CRM install. That will take care of your install files e.g. Program Files\Sage\CRM\.

Note: Some of this may not be relevant to your exact needs but read on to understand my logic.

You need to make sure that it is exactly the same version as your current 'Live' Web server.

You can then copy over any custom files from your 'Live' servers such as images/custom pages[wwwroot\custom pages and wwwroot\img]

If your document library & default document templates are on your current Web Server you need to copy this over too.

From the steps above your new Web Server should be the same version as your Live Web Server and custom files copied over.

Moving the Database Server

When you create the new install you can choose to install a clean CRM database on the new database server.

Then you can restore your "Live" database over it.

Checking the Registry

You will need to check the registry on the new web server. This is to ensure that the DefaultDatabase/DefaultDatabaseServer & DatabasePassword all are related to the new DB server. These are on the Web Server under:

HKEY_LOCAL_MACHINE\SOFTWARE\eWare\Config\

Do make sure that you keep backups of your Live database and current CRM install folder safe too.

Once you have done that you can start to check whether the new web server is up and running. Sage CRM should be installed to the same level, with all the custom images/files[asp development etc] and pointing to the new database server, which has a current database restored.

There are things you need to verify.

Check things like library document links, custom pages are still working correctly after the move. Check the Admin area to see what else is pointing at a drive e.g. the documents and reports Admin configuration.

Checking the Configuration Files

To ensure the aspects of Sage CRM that are delivered using the Apache Tomcat webapps you need to ensure the configuration file is correct:

C:\Program Files (x86)\Sage\CRM\CRM\tomcat\webapps\crmj\WEB-INF

db.properties

Here's a sample connection string for SQL Server 2014 Express (it will be similar in other versions):

  • #SQL Server settings
  • db.driver=net.sf.log4jdbc.DriverSpy
  • db.url=jdbc:log4jdbc:sqlserver://SAGE020111:61364;databaseName=CRM;language=English;instance=CRMEXPRESS2014
  • db.encrypt=true;
  • db.username=sa
  • db.password= &KKGDJKNEKCCFGDPBFJEKNBBFACNLJKAH

If you're using the default SQL Server instance (usually MSSQLSERVER), you don't need to specify it in the db.properties.

Exchange Integration

If you are using Exchange integration then please also check the file

C:\Program Files (x86)\Sage\CRM\CRM\tomcat\webapps\crmExchangeSyncEngine\WEB-INF

dp.properties

  • #SQL Server settings
  • db.driver=com.microsoft.sqlserver.jdbc.SQLServerDriver
  • db.url=jdbc:sqlserver://SAGE020331:61364;databaseName=CRM;instance=CRMEXPRESS2014
  • db.encrypt=true;
  • db.username=sa
  • db.password= &KKGDJKNEKCCFGDPBFJEKNBBFACNLJKAH

Other things to Check

Another possibility is that the SQL Server instance isn't set up to accept connections over TCP/IP. The CRM components driven by the eWare DLL use the SQL Server client tools to connect, so it's possible to have those working, and not the Tomcat webapps.

You should be able to enable TCP/IP in the SQL Server Configuration Manager. Under SQL Server Network Configuration, check out the Protocols for options. TCP/IP will need to be enabled and will need to be listening on a given IP/port combination. You might find it easiest to try enabling the Listen All option on the Protocol tab, then testing a TCP/IP connection using Management Studio.

This can be done from the connection screen in Management Studio. First set, your authentication method to SQL Server Authentication and make sure that you're using TCP/IP as your network protocol.

This should ensure your dashboards etc are working. You'll want to check out the logs generated by the Tomcat web apps - you'll find those in the CRM logs directory (/CRM/Logs/), and the Tomcat logs directory (/CRM/tomcat/logs/).

Only after you have completed the checks should you disable your old web server and send users the new Server URL.

Anonymous
  • Hi Jeff, thanks for the reminder. Another tip important to note: db properties is different in CRMj and crmExchnageSyncEngine... So don't copy the url too fast ;-)

    The db.properties could also be slightly different depending on the CRM version. So control it carefully and it will save you hours in tomcat issue resolving...