Pages

Tuesday, October 17, 2017

Modifying Endpoint URLs on Availability Group Replicas

I recently had to modify the Endpoint URLs on our SQL Server Availability Group replicas. 

The reason for this blog post is that I could not answer the following questions:

Do I need to suspend data movement prior to making this change? 

Would this change require a restart of the database instance?

I spent enough time searching on my own to no avail that I tossed the question to the #sqlhelp hashtag on Twitter and Slack but didn't get an answer prior to executing the change request.

After reading the relevant documentation, I think it's probably a good idea to suspend data movement for this change.

The T-SQL is straightforward. 

USE MASTER
GO

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON 'SQL2012-1' WITH (ENDPOINT_URL = 'TCP://10.10.10.1:5022');

ALTER AVAILABILITY GROUP [AG1] 
MODIFY REPLICA ON 'SQL2012-2' WITH (ENDPOINT_URL = 'TCP://10.10.10.2:5022');

ALTER AVAILABILITY GROUP [AG2] 
MODIFY REPLICA ON 'SQL2012-1' WITH (ENDPOINT_URL = 'TCP://10.10.10.1:5022');

ALTER AVAILABILITY GROUP [AG2] 
MODIFY REPLICA ON 'SQL2012-2' WITH (ENDPOINT_URL = 'TCP://10.10.10.2:5022');

ALTER AVAILABILITY GROUP [AG3] 
MODIFY REPLICA ON 'SQL2012-1' WITH (ENDPOINT_URL = 'TCP://10.10.10.1:5022');

ALTER AVAILABILITY GROUP [AG3] 
MODIFY REPLICA ON 'SQL2012-2' WITH (ENDPOINT_URL = 'TCP://10.10.10.2:5022');

I completed this change successfully last night. I suspended data movement for each replica prior to executing their respective ALTER. I'm not sure suspending data movement was required but it didn't hurt. 
This change did not require a restart of the SQL instance.