Configuring a Separate MS SQL Server for CloudM Migrate

If you are using a standalone SQL environment and intend to introduce a new primary server, it is essential to create a new SQL instance to establish a connection. You can export the configurations from your current primary server and import them to the new primary server. However, please note that the migration history cannot be preserved.

SQL Express is usually installed on the Primary migration server as part of the default install of CloudM Migrate. However, full SQL Server can be used in its place and hosted on an independent server.

This guide is in addition to the standard installation articles and assumes that the Migration Server (Primary) and SQL Server are hosted as Virtual Machines in Google Cloud Platform (GCP).

Usually, it is simpler to do an in-place upgrade of SQL Express

Primary Migration Server

Setup and configure Windows Server based on the standard CloudM Migrate recommendations, but do not install Migrate yet.

DTC properties page

SQL Database Server

Install Windows Server and then download and follow the defaults to install Microsoft SQL Server and SQL Management Studio

Once installed, open SQL Management Studio and follow the instructions here to create a SQL user with dbcreator and public role

Disable Windows Firewall and configure MSDTC as with the Primary Migration Server.

Firewall

GCP Firewall can be accessed here: https://console.cloud.google.com/networking/firewalls

An Ingress rule/rules should be applied to allow data to flow through the following ports:

  1. SQL Server = 1433/1434;
  2. Microsoft Distributed Transaction Coordinator = 5000-5020
  3. MSMQ = 1801
  4. Redis Server = 6379

Firewall rules example

Installing CloudM Migrate

Download and install the latest version of CloudM Migrate. Choose the Advanced option to specify an existing SQL Server.

Select 'Use Existing SQL Server' and populate the following fields.

  • Server Name - This will be the server/server sql instance where your database resides. SQL SERVER may not require an instance name if it was installed without one, in which case just specify the server name
  • Database - Database name, usually PoweredBy - this and NServiceBus databases will be created by the install process.
  • User Name - SQL user that has access to the database entered in the previous step.
  • Password - SQL user password

The installer will connect to the SQL Server and create two databases (under the Databases folder in Management Studio) - PoweredBy and NServiceBus. Additional databases will be created as projects and configurations are added.

PowerdBy Database structure

Once CloudM Migrate has installed on the Primary server, open the CloudM Service Manager and change the Redis Server Location.

An older Windows version of Redis is installed by default to the Primary migration server, but the latest version of Redis can also be installed to its own Linux server.

By default the Redis Server Location is blank which means that Migrate is configured as if Redis is installed alongside the SQL Server. If a different version of Redis is being used, this empty field should be changed to the primary server hostname or the Redis Linux server hostname/IP address.

Redis Server location example

Troubleshooting

Check that the Ports are open and connect

Install Telnet to test if the ports are open

Run the following command: >cmd > telnet <servername> <port>

  • If it connects - Good
  • If it fails - Recheck GCP and the Windows firewalls.

Secondary Servers are not connecting

  1. Go to SQL Server Configuration Manager
  2. Open SQL Server Network Configuration
  3. Click on your Named Instance
  4. Ensure TCP/IP Enabled
  5. Open TCP/IP
  6. Click on IP addresses
  7. Add Port 1433 to all IP's
  8. Restart MSSQL service
Was this article helpful?
0 out of 0 found this helpful