Safecom Multiserver Is Relying on Replication Between Master SQL Database and Slave SQL

SafeCom multiserver is relying on replication between Master SQL database and Slave SQL databases to be working and that the Master SQL database is able to connect to and login to Slave servers SQL databases.
Especially the SQL databases needs to know on what TCP port to connect on.

Let’s first see how the Master server establishes a connection to slave server databases.
If e.g. the Slave database is running using a dynamic assigned TCP port for connection, then one week Safe Com slave server sql instance (slave1\safecomexpress) will run on TCP port 52345 and next week run on TCP port 51678.
Assuming that these ports are open across the network, the Master SQL server will still need to know on what port to connect. For that a well working DNS server is needed.

From the Master SQL servers perspective: A change in SafeCom has happened, and the SafeCom Master server needs to update all slave servers with this update.
The Master SQL server asks the DNS sever to resolve the name for the slave instance: slave1\safecomexpress
The DNS server hands back the IP. Adr. for slave1, and the Master SQL server than sends a SQL broadcast request using UDP port 1434 to slave1 asking: What port is the safecomexpress instance currently running on. The response could be TCP port 52345.
The Master SQL server then connects to slave1\safecomexpress instance using TCP port 52345 and the connection is made.

If this does not work, or if the slave server never responds back with what port it is on, or if the response is lost, then the Master SQL server will revert to try using the fail-back port: TCP port 1433. And if the slave database instance happens to be using this TCP port, then the connection is made.

In a perfect network, the DNS server will fully be able to resolve a SQL request when e.g. the Master SQL server needs to connect to a slave SQL database.

In a not so perfect network, it is possible to force SafeCom SQL instances on the Master SQL server and on the slave SQL servers to run on TCP port 1433.

But what if the customer for security reasons is using non-standard fixed TCP ports for all their SafeCom sql databases, and either the DNS server is not helping, or the network is not allowing the SQL broadcast on UDP 1434 ?

Then it is time to set up SQL alias helpers to help the Master SQL server know what port to connect on.
And not least important: To let the slave servers know what TCP port to answer back on when responding to the Master SQL server.

Lest assume that the SafeCom Master SQL instance is running on fixed TCP port 12050 and all of the Slave SQL instances are running on fixed TCP port 13090.
Then we need to set up an alias pointer on the Master SQL server for all of the known Slave servers listing the correct TCP port to connect on (TCP port 13090).

Likewise on all of the SafeCom Slave servers we need to setup an alias pointer to the Master SQL database listing the correct TCP port to connect on (TCP port 12090).

In the below example we have the following SQL servers:
Mastersql (this needs not be the same windows server as the SafeCom Master server)
Mastersql\master instance running on TCP port 12090

Slave1\safecomexpress instance running on TCP port 13090

Slave2\safecomexpress instance running on TCP port 13090

On the Master SQL server do the following:
Start, All programs, Accessories,Command Prompt
(right click Command Prompt and choose “run as administrator”)

In the command prompt type c:\cliconfg <Enter>

This will bring up the SQL Server client Network Utility

Click the Alias tab and press the Add button.

In the left hand side, choose TCP/IP, and untick “Dynamically determine port”

In the server alias type in the full instance name for the slave server, in this example SLAVE1\SQLEXPRESS
And type in the TCP port used by the slave instance.

Hint: type in the instance name using uppercase letters.
Important. Do not use DNS like naming convention like SLAVE1.training.local\SQLEXPRESS

Press the OK button:

Now make another alias entry on the same server, this time for the other slaver server:

Now you have alias pointers on the Master sql server for all the slave instances.

Now on the slave servers, likewise make alias pointers this time to the master sql instance:

Now your servers should be able to correctly resolve name and TCP port number in order to connect to each other during replication updates.

But before the change is fully applied, you need to restart the Windows Server for both the Master SQL database server as well as the Slave servers.

When you intend to add a new slave server, then remember to also update your sql alias entries accordingly.