Listener ip not to be removed while decommissioning secondary server

[Sujay1185]

Hi,

We have an activity to remove Always on and decommissioning secondary db server but keeping the listener name alone since listener name is pointed to application.

Is it possible? Please let me know 

[#2]

@Sujay1185 

Hi, Sujay.

In your title, you ask about preserving the IP address, however, in the question, you neglect the IP address and ask about keeping the cluster name instead. Which is it that you're interested in keeping?

Regardless of which it is, it is possible to retain both under the right circumstances.

Retaining the IP address

Looking at the current AlwaysOn state, if the currently-primary replica is being retained, then you can retain the virtual IP address by assigning it to the network interface of the remaining server as an additional address once the decommissioning has been fully completed.

It's advisable that you check using SQL Configuration Manager that the SQL instance is then configured to listen additionally on that address (i.e. on both addresses, not just one or the other).

Lastly, you should also have an Active Directory administrator either reset the permissions on the DNS records (A, AAAA and PTR) that used to belong to the cluster resource so that the SQL host now has the ability to update those. Or, if they don't know how to do that, get them to simply delete those DNS records and then run an "ipconfig /registerdns" on the SQL host around five minutes later to let the SQL host re-register them.

Cluster resource name (virtual network name - VNN)

There's two key action items here:

1. Ensure that DNS contains/retains an A/AAAA record for the VNN pointing to the remaining SQL host;
2. Ensure that the servicePrincipalName entries matching the VNN exist under the database service account.

DNS A/AAAA record

If you do not need to retain the cluster resource's virtual IP address, then simply create (or retain, if it still exists) an A and/or AAAA DNS record where the value for the name is the value of the VNN, and the host address is the "real" IP address of the remaining SQL host (i.e. not the cluster resource's virtual IP).

If you do need to retain the virtual IP address then use it for the A/AAAA record's host address value.

Service principal names

If you currently use a domain user or computer (which typically shows as "Network Service") account as the service account, the additional service principal names should already exist under that account, and from memory, I don't think removing failover cluster services removes those registrations.

Regardless, the best thing to do is check the current values before you decommission anything at all, and then ensure that the same values derived from the VNN are put back (if necessary) once decommissioning has been completed.

• Register a Service Principal Name for Kerberos connections - SQL Server | Microsoft Learn

Cheers,

Lain

[#3]

Thanks @LainRobertson for the explanation, but this is the first time doing AG removal by keeping the Listener name and IP. So as far I understand the below steps are to be done first before removing the AG.

1. Check the app config is pointing to Listener name or IP address.
2. If app is pointing to Listener name need to add the name as CNAME pointing to DB server.
3. If app is pointing to IP address then we need to add this IP adress to the Network interface of the same IP were the Primary DB server is. Can you please clarify this part alone.

Once any 1 of the steps completed we can proceed with AG removal.

Please help on this

[#4]

Yes, it's definitely possible to decommission the secondary DB server while retaining the listener IP. You'll need to reconfigure the listener to point to the Fsginspections [link removed by admin] primary server instead. Ensure proper testing and coordination to avoid any disruptions to your application.

[#5]

@Sujay1185 

Hi, Sujay.

1. This depends on your application - I can't answer this;
2. If you want to use a CNAME, you can, but since the A record already exists from the cluster configuration, I've taken the easier option of continuing to use it;
3. Just add another IP address to the one that already exists on the primary server's network interface - meaning it will then have two (or more) addresses.

The guidance I provided in my previous reply is more comprehensive than you might need so you have something to refer to should you want to understand not just what to do but why. However, it's quite possible you won't have to do anything (in the scenario where the AlwaysOn group DNS name is being used).

For the first point, you're focusing on one application. Think bigger. You should be looking to ensure that the primary server properly responds to all scenarios. What happens if half the users connect via name and the remainder connect via IP? I've absolutely seen that a lot, where Helpdesk staff fall back to using IP addresses when the customer has had a DNS issue. What happens when there's more than just that one application to consider, or even things like linked servers?

The application is just one small part of what could be a larger puzzle.

Focus on getting that primary server to respond to all the name and address combinations. Again, there's very little you need to do (perhaps even nothing at all) to achieve this, and once you have, you don't need to know anything whatsoever about application config files.

There's plenty of articles out there on how to add a second IP address to an interface. Because I run Server Core, I use PowerShell (below), but if you need GUI step-by-step screens, just search for them.

• New-NetIPAddress (NetTCPIP) | Microsoft Learn

Cheers,

Lain

[#6]

Thanks @lain we have this activity tomorrow let me get these points and will perform the AG removal