March 1, 2019 at 7:07 am
hi,
In the process of changing IPs for one node of a clustered server with databases in a Basic Availability Group we have to drop and recreate the BAGs.
Working on the node where the BAG is primary we delete the BAG from the Availability Groups, this would result with the database on the secondary node to go into a Restoring state.
Dropping that db in the restored state and proceeding with recreating the BAG works fine, but is there a cleaner way for doing this?
Is there a way to delete a BAG that does not result in the secondary db going into a restoring state?
This is SQL Server 2016 Standard Edition.
Thank you
March 1, 2019 at 11:19 am
inund8ed - Friday, March 1, 2019 7:07 AMhi,In the process of changing IPs for one node of a clustered server with databases in a Basic Availability Group we have to drop and recreate the BAGs.
Working on the node where the BAG is primary we delete the BAG from the Availability Groups, this would result with the database on the secondary node to go into a Restoring state.
Dropping that db in the restored state and proceeding with recreating the BAG works fine, but is there a cleaner way for doing this?
Is there a way to delete a BAG that does not result in the secondary db going into a restoring state?This is SQL Server 2016 Standard Edition.
Thank you
Hmmm, I just did the same thing. We have one node at our primary data center, and the second node in our DR site.
The DR site's IP's needed to change.
I simply changed the IP, removed the second(no longer needed) IP address from the listener, performed an IPCONFIG /flushdns and an IPCONFIG /registerdns, and re-booted the server,
The AG picked up the synchronization, and all was well.
This worked the same way for 3 different AG's, as well as a SQL 2008R2 mirroring setup.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 1, 2019 at 2:25 pm
hi Michael,
Are those BAGs (Basic Availability Groups)?
Did you perform the IP changes in Failover Cluster Manager, or within SSMS?
I believe the IPs in a BAG can't be dynamically changed and the only option is to recreate it, if I'm wrong could you show me how that can be changed?
thank you
March 1, 2019 at 6:59 pm
inund8ed - Friday, March 1, 2019 2:25 PMhi Michael,
Are those BAGs (Basic Availability Groups)?
Did you perform the IP changes in Failover Cluster Manager, or within SSMS?
I believe the IPs in a BAG can't be dynamically changed and the only option is to recreate it, if I'm wrong could you show me how that can be changed?thank you
One is a BAG.
All of our servers are VM's We have the ability to log in to a server even if it is not connected to the network. If you are running physical machines, you may need to connect a monitor, keyboard and mouse to it.
Here are the steps we performed:
1. Added a new NIC in VMWare
2. Logged in with RDP
3. Added the IP to the new NIC, and disabled the old one. RDP was disconnected.
4. Connected via VMWare. Did a IPCONFIG /FLUSHDNS and IPCONFIG /REGISTERDNS
5. Connected via RDP using the new IP address.
6. Removed the second IP address from the listener. The servers were originally on 2 separate subnets.
a. Attempted to connect by name with SSMS from my PC, it took a few minutes for DNS to catch up
7. Checked the state of the AG. The primary and secondary are in synchronous mode, it was syncing properly.
8. Re-booted the server.
9. DNS must have synced, I was able to RDP anc connect with the name.
You may want to consider creating a hosts file with the new IP's on each server, which will bypass DNS until it syncs.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2019 at 12:31 pm
Thank you!
Could you please clarify on step #6. remove the second IP address from the listener -- I did not see the option to perform this step through SSMS gui, also how did you add in the new IP for the BAG?
All of our databases on the servers are configured as part of Basic Availability Groups so were planning on dropping the BAGs first before the server IP is changed, and then recreate the BAGs with its new IPs - does that make sense? We figured we would end up dropping them later on anyway if the original IPs (from one subnet) became unavailable and since I could not find any documentation online on how to change the listeners' IPs for a BAG.
And yes, both servers are VMs.
March 4, 2019 at 12:54 pm
Could you please clarify on step #6. remove the second IP address from the listener -- I did not see the option to perform this step through SSMS gui, also how did you add in the new IP for the BAG?
When you select "properties" of the listener by right clicking on it in SSMS, you can add or remove the IP addresses from there. In our case, the DR site was on a different subnet. We changed that, so I simply had to remove the ip address for the second subnet.
All of our databases on the servers are configured as part of Basic Availability Groups so were planning on dropping the BAGs first before the server IP is changed, and then recreate the BAGs with its new IPs - does that make sense? We figured we would end up dropping them later on anyway if the original IPs (from one subnet) became unavailable and since I could not find any documentation online on how to change the listeners' IPs for a BAG.
And yes, both servers are VMs.
No, it does not make sense. I described the steps we performed to change the IP addresses without going through the work of dropping and re-creating the availability groups.
This article from MS describes how to change a listener's settings:
https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/view-availability-group-listener-properties-sql-server?view=sql-server-2017
It appears that you are hung up on finding documentation that is specific to a basic availability group. There is really no difference, or at least none that I have found, that is different for administering a basic availability group.
The difference is the versions of SQL (standard vs enterprise), in a BAG< you can only have one database in the AG, and the secondary cannot be read-only. Everything else is essentially the same.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
March 4, 2019 at 9:33 pm
You could say that..
We've had to change the IP of just the BAGs itself a few times due to ip conflict, so the steps used there have been the starting point for this new activity of changing the IP for all other components as well. Microsoft support did not counter the steps we brought forward to them.
Thank you very much for your input!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply