March 1, 2016 at 3:51 pm
I have a SQL Server mirror with no witness with TCP. Connections are configured to use certificates. The IP address of the Principal Partner is changing; the IP of the Mirror Partner is staying the same (and using the IP's is working fine.) Safety mode is off. Due to the size of the database I want to avoid removing the mirror and then re-configuring it.
What is the process for changing the Principal Partner IP without removing and re-configuring the mirror? This is not covered in Books Online that I saw. Also, I haven't found the issue in forums.
Do I stop the end point, change it, and restart it; or do I suspend, change the IP, then resume?
It will take days to reestablish the mirror if I don’t have a workaround, or a test. Thanks in advance.
What I was thinking of trying:
From the Principal Server:
ALTER DATABASE [SomeDB] SET PARTNER SAFETY FULL;
From the Mirror:
ALTER DATABASE [SomeDB] SET PARTNER SUSPEND;
ALTER DATABASE [SomeDB] SET PARTNER = 'TCP://123.456.78.910:12345'
ALTER DATABASE [SomeDB] SET PARTNER RESUME;
From the Principal:
ALTER DATABASE [SomeDB] SET PARTNER SAFETY OFF;
March 4, 2016 at 8:26 am
First things, I wouldn't change the IP here. I'd actually change to an FQDN, and use that in the future. There have been reports of issues here, so if you are altering things, do it once. This removes dependencies on particular hardware.
Second, I haven't done this, though I should try it. however I did find this at MSDN: https://blogs.msdn.microsoft.com/benjones/2009/09/25/changing-the-listener_ip-address-in-a-database-mirroring-configuration/
It appears you pause mirroing, remove the partners, change things, then re-enable. This should be endpoint dependent, so your mirroring shouldpick back up.
March 6, 2016 at 6:30 am
WHug (3/1/2016)
I have a SQL Server mirror with no witness with TCP. Connections are configured to use certificates. The IP address of the Principal Partner is changing; the IP of the Mirror Partner is staying the same (and using the IP's is working fine.) Safety mode is off. Due to the size of the database I want to avoid removing the mirror and then re-configuring it.What is the process for changing the Principal Partner IP without removing and re-configuring the mirror? This is not covered in Books Online that I saw. Also, I haven't found the issue in forums.
Do I stop the end point, change it, and restart it; or do I suspend, change the IP, then resume?
It will take days to reestablish the mirror if I don’t have a workaround, or a test. Thanks in advance.
What I was thinking of trying:
From the Principal Server:
ALTER DATABASE [SomeDB] SET PARTNER SAFETY FULL;
From the Mirror:
ALTER DATABASE [SomeDB] SET PARTNER SUSPEND;
ALTER DATABASE [SomeDB] SET PARTNER = 'TCP://123.456.78.910:12345'
ALTER DATABASE [SomeDB] SET PARTNER RESUME;
From the Principal:
ALTER DATABASE [SomeDB] SET PARTNER SAFETY OFF;
How did you create the endpoints when you configured mirroring originally?
If you used the security configuration wizard or the defaults in TSQL then the endpoints will be set to use IP_ALL.
eEnsure the databases are synchronised and then pause the session, change the IP address and then resume the session
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply