HiveBrain v1.2.0
Get Started
← Back to all entries
patternsqlMinor

SQL Server Multi-Instance IP Configuration - Service Doesn't Start

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
multisqlinstanceservicedoesnstartserverconfiguration

Problem

I have run into an issue whilst configuring a multi-instance SQL Server when assigning a specific IP address to the fist instance.
Dedicated IP Setup

The steps I perform to configure a single instance to a dedicated IP address are:

-
Open SQL Server Configuration Manager (SSCM)

-
Navigate down the tree to:

SQL Server Configuration Manager (local)
+-- SQL Server Network Configuration
    +-- Protocols for 


-
In the right pane I then doubl-click on TCP/IP which open the Properties of TCP/IP in the tab Protocol

-
I then ensure that Enabled is set to yes and the option for Listen All is set to No.

-
I switch tabs into the IP Addresses and ensure that

  • Enabled is set to No for all IPv4 and IPv6 addreses, except for the IP address that I want to assign to this instance. For this IP address I set the setting to Yes.



  • The TCP Dynamic Ports is empty for all IP addresses.



  • For the IP address that I want to assign to this instance I assign the TCP port 1433.



-
I then close all windows and SSCM displays the standard message:

---------------------------
Warning
---------------------------
Any changes made will be saved; however, they will not take 
effect until the service is stopped and restarted.
---------------------------
OK   
---------------------------


-
I restart the specific instance and it will generally allow connections to my instance via the dedicated IP address.

-
I test the instance is listening on the given IP address and port with:

c:\> netstat -abon


TCP    10.58.212.112:1433     0.0.0.0:0              LISTENING         2172
[sqlservr.exe]


Above results are from a working server

So far so good. I have done this for the umpteenth time and never had any issues.
Case

Two days ago the SQL Server service for a freshly configured instance on a test server wouldn't start after configuring the 10.58.194.5 address for the instance. I tried the following steps to fix the "broken" test instance:

  • Repair Shared

Solution

I am documenting the answer in order to provide others with ways of finding out why an IP address might be causing an issue when configuring an IP address for an individual SQL Server instance.

The following IP addresses were the IPs that were assigned to the NIC on the server and to the server itself:

IP address:        10.58.194.4       (server)
Subnet mask:       255.255.255.192
Standard gateway:  10.58.194.2
IP address:        10.58.194.5       (instance 1)
IP address:        10.58.194.6       (instance 2)
IP address:        10.58.194.7       (instance 3)
IP address:        10.58.194.28      (instance 4)
IP address:        10.58.194.29      (instance 5)


Following is a section of the IP range and what they were actually used for from a network perspective:

IP address:        10.58.194.0       (reserved; don't use)
IP address:        10.58.194.1       (reserved; don't use)
IP address:        10.58.194.2       (virtual Gateway)
IP address:        10.58.194.3       (reserved; don't use)
IP address:        10.58.194.4       (Actual Gateway Data Centre 1)
IP address:        10.58.194.5       (Actual Gateway Data Centre 2)
IP address:        10.58.194.6       (reserved; don't use)
IP address:        10.58.194.7       (reserved; don't use)


As you can see the IP address of the server matches the IP address of the Actual Gateway Data Centre 1. And the IP address that had been reserved for the first SQL Server instance was the IP address of the Actual Gateway Data Centre 2.

What had happened?

During the initial configuration of the server the IP addresses were partially in use be the network technicians. Because the current physical gateway for the server was running on 10.58.194.5 the server was able to be configured to run and start on 10.58.194.4.

The configuration of the additional IP addresses on the NIC wasn't an issue, because it is initially just an entry in the registry. The server was then handed over the DBA team.

Then along came the DBA guy (me) and tried to configure a new SQL Server instance on the IP address of the Actual Gateway Data Centre 2. Because this address was actually in use as the current gateway of the Windows Server, the SQL Server instance was unable to start.
Hint 1

Running an ipconfig showed the actual IP addresses that were active on the server:

Ethernet-Adapter Ethernet0:

   Verbindungsspezifisches DNS-Suffix: 
   Verbindungslokale IPv6-Adresse  . : 
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.4
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.7
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.28
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.29
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   Standardgateway . . . . . . . . . : 10.58.194.2


This showed us that ...194.5 and ...194.6 weren't available for the server/instance even though they had been configured for the NIC.
Hint 2

Running an tracert to a different server in a different IP range would have equally shown that the IP address 10.58.194.5 was in use as the current gateway for the server.
Solution

The solution to our problem was to move the server to a different IP range and to document that the first seven IP addresses of a range are reserved for the network technicians.

Code Snippets

IP address:        10.58.194.4       (server)
Subnet mask:       255.255.255.192
Standard gateway:  10.58.194.2
IP address:        10.58.194.5       (instance 1)
IP address:        10.58.194.6       (instance 2)
IP address:        10.58.194.7       (instance 3)
IP address:        10.58.194.28      (instance 4)
IP address:        10.58.194.29      (instance 5)
IP address:        10.58.194.0       (reserved; don't use)
IP address:        10.58.194.1       (reserved; don't use)
IP address:        10.58.194.2       (virtual Gateway)
IP address:        10.58.194.3       (reserved; don't use)
IP address:        10.58.194.4       (Actual Gateway Data Centre 1)
IP address:        10.58.194.5       (Actual Gateway Data Centre 2)
IP address:        10.58.194.6       (reserved; don't use)
IP address:        10.58.194.7       (reserved; don't use)
Ethernet-Adapter Ethernet0:

   Verbindungsspezifisches DNS-Suffix: 
   Verbindungslokale IPv6-Adresse  . : 
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.4
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.7
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.28
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   IPv4-Adresse  . . . . . . . . . . : 10.58.194.29
   Subnetzmaske  . . . . . . . . . . : 255.255.255.192
   Standardgateway . . . . . . . . . : 10.58.194.2

Context

StackExchange Database Administrators Q#305098, answer score: 2

Revisions (0)

No revisions yet.