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.
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.
Comments
Post a Comment