Resolving Database Mirroring Error 1418: Server Network Address Unreachable

Understanding Database Mirroring Connection Issues

Database mirroring is an essential strategy for ensuring high availability and redundancy in SQL Server environments. However, configuring mirroring can sometimes lead to frustrating errors, such as Error 1418, which states that the server network address cannot be reached or does not exist.

This particular error often occurs when attempting to set up a mirroring session between two SQL Server instances, even if both databases are accessible individually. The issue arises when the mirroring endpoints fail to communicate with each other.

In the case at hand, a local desktop (192.168.0.80) and a mini PC (192.168.0.85) are involved in the mirroring process. The mini PC is intended to serve as a read-only replica for a mobile application, using the "High Performance" mode of mirroring.

Despite correct port configuration and firewall adjustments, the user encounters Error 1418 when trying to initiate the mirroring session. This article will explore potential causes and solutions to address this issue.

Detailed Breakdown of the Mirroring Error Resolution Scripts

The first script provided in the earlier examples uses Transact-SQL (T-SQL) commands to configure and resolve the mirroring error in SQL Server. The most critical part of the script is the creation and configuration of mirroring endpoints. These endpoints are the network interfaces through which the SQL Server instances communicate during mirroring. The command ALTER ENDPOINT ensures that the endpoints on both servers are in the "STARTED" state, allowing communication to occur. The SET PARTNER command is then used to link the databases, specifying the network address of the partner server, which allows the two SQL instances to mirror data across the network.

The second script is a PowerShell solution designed to test the network connectivity between the two servers. PowerShell uses the New-Object System.Net.Sockets.TcpClient command to create a TCP client that attempts to connect to the specified IP address and port. This is an efficient way to verify that the required ports (1433 for SQL Server and 5022 for mirroring) are open and accessible. This script is particularly useful for diagnosing firewall or networking issues that may be preventing the two SQL instances from communicating, thus causing the Error 1418.

The third script leverages Windows Command Prompt commands to manage firewall settings. Specifically, the netsh advfirewall firewall add rule command is used to open the necessary ports for SQL Server and mirroring. This ensures that both the database traffic (port 1433) and the mirroring traffic (port 5022) can flow freely between the two servers. By temporarily disabling the firewall with the netsh advfirewall set allprofiles state off command, the script can verify whether the firewall is the root cause of the network access issue. This solution is particularly important when troubleshooting server communication problems in a secure environment.

Lastly, the Python script uses the socket.create_connection function to perform a network check between the two servers. This script provides a quick and effective way to validate whether the servers can reach each other over the required TCP ports. It attempts to establish a connection, and if successful, confirms that the network setup is correct. Python’s simplicity in handling network-related issues makes it a good choice for testing connectivity, especially in environments where other tools are unavailable or cumbersome to use. Together, these scripts offer a comprehensive approach to resolving the database mirroring error and ensuring smooth communication between SQL Server instances.

-- Enable server to listen on the specified ports
ALTER ENDPOINT [Mirroring] 
STATE = STARTED;
GO

-- Ensure both databases are in FULL recovery mode
ALTER DATABASE YourDatabaseName 
SET RECOVERY FULL;
GO

-- Create mirroring endpoints on both servers
CREATE ENDPOINT [Mirroring_Endpoint]
STATE = STARTED
AS TCP (LISTENER_PORT = 5022)
FOR DATABASE_MIRRORING (ROLE = PARTNER);
GO

-- Grant CONNECT permissions to the login account
GRANT CONNECT ON ENDPOINT::[Mirroring_Endpoint] 
TO [DOMAIN\UserAccount];
GO

-- Set up mirroring using T-SQL command
ALTER DATABASE YourDatabaseName 
SET PARTNER = 'TCP://192.168.0.85:5022';
GO

-- Verify the status of the mirroring configuration
SELECT * FROM sys.database_mirroring;
GO

# Define server IPs and ports
$server1 = "192.168.0.80"
$server2 = "192.168.0.85"
$port = 5022

# Function to test port connectivity
function Test-Port {
   param([string]$server, [int]$port)
   try {
       $tcpClient = New-Object System.Net.Sockets.TcpClient($server, $port)
        Write-Host "$server on port $port is reachable."
       $tcpClient.Close()
   } catch {
        Write-Host "$server on port $port is not reachable."
    }
}

# Test both servers
Test-Port -server $server1 -port $port
Test-Port -server $server2 -port $port

-- Check if SQL Server and mirroring ports are open
netsh advfirewall firewall add rule name="SQLPort" dir=in action=allow protocol=TCP localport=1433
netsh advfirewall firewall add rule name="MirrorPort" dir=in action=allow protocol=TCP localport=5022

-- Disable firewall temporarily for testing purposes
netsh advfirewall set allprofiles state off

-- Enable firewall again after testing
netsh advfirewall set allprofiles state on

import socket

# Define server IPs and port
server1 = '192.168.0.80'
server2 = '192.168.0.85'
port = 5022

# Function to check connectivity
def check_connection(server, port):
    try:
        sock = socket.create_connection((server, port), timeout=5)
       print(f'Connection successful to {server}:{port}')
        sock.close()
   except socket.error:
       print(f'Cannot connect to {server}:{port}')

# Check both servers
check_connection(server1, port)
check_connection(server2, port)

1. Open SQL Server Management Studio (SSMS).
2. Right-click your database -> Tasks -> Mirror...
3. Click Configure Security and follow the wizard.
4. Ensure both Principal and Mirror servers are correct.
5. Set the port for the mirroring endpoints to 5022.
6. Complete the configuration and click Start Mirroring.
7. Verify the mirroring status by checking the "Database Properties" window.

Exploring Network and Security Challenges in SQL Server Mirroring

When setting up SQL Server database mirroring, one aspect often overlooked is the role of network configuration and security settings. Error 1418, indicating that the server network address cannot be reached, is frequently caused by underlying network issues. Even when the correct ports (1433 and 5022) are opened and firewalls are disabled, other network elements such as routing and DNS configuration could cause communication failures. It’s important to ensure that both servers resolve each other's IP addresses properly, especially in multi-subnet environments.

Another challenge involves SQL Server authentication settings during the mirroring setup. Database mirroring requires that both the principal and mirror server authenticate each other via certificates or domain-based authentication (Kerberos). If this setup is not correctly configured, or if there is a mismatch in the security protocols between the two servers, Error 1418 can occur. Additionally, SQL Server service accounts must have the correct permissions on both machines, particularly access to the mirroring endpoints.

Finally, the choice of operating system can also affect how mirroring behaves. Different Windows versions may handle TCP connections differently, particularly in how they manage firewall rules and network traffic routing. If the operating system of either server has outdated or mismatched network drivers, communication between servers could fail. Ensuring the OS is up to date with the latest patches and that the appropriate services are running is crucial in resolving connectivity issues like Error 1418.