sp_AGReconfigure 1.1 is now available

sp_AGReconfigure 1.1 is now available HERE and on GitHub

V1.1 includes a new Parameter and some minor bug fixes:

@Readable

NULL – DEFAULT:  Ignore Readable secondary check (acts as though you are using V1)

0 – Produce a statement to switch readable secondary off if switched on

1 – Produce a statement to switch readable on if set to off or Read intent

2 – Produce a statement to switch read intent on if set to off or Readable

Examples:

EXEC sp_AGreconfigure
@ServerName = NULL,
@AGName = NULL,
@SyncCommit = 1,
@AutoFailover = 1,
@Readable = 0,
@CheckOnly = 0

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

 

 

EXEC sp_AGreconfigure
@ServerName = NULL,
@AGName = NULL,
@SyncCommit = 1,
@AutoFailover = 1,
@Readable = 1,
@CheckOnly = 0

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));
ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = ALL));

 

 

EXEC sp_AGreconfigure
@ServerName = NULL,
@AGName = NULL,
@SyncCommit = 1,
@AutoFailover = 1,
@Readable = 2,
@CheckOnly = 0

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));
ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = READ_ONLY));

 

 

These are just some simple examples to show the new commands available, these will be appended as appropriate to the existing statements produced from V1 of the Procedure.

 

For Example:

EXEC sp_AGreconfigure
@ServerName = NULL,
@AGName = NULL,
@SyncCommit = 0,
@AutoFailover = 0,
@Readable = 0,
@CheckOnly = 0

ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (FAILOVER_MODE = MANUAL); ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT); ALTER AVAILABILITY GROUP [AG1] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));
ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (FAILOVER_MODE = MANUAL); ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT); ALTER AVAILABILITY GROUP [AG2] MODIFY REPLICA ON N'SQL02' WITH (SECONDARY_ROLE(ALLOW_CONNECTIONS = NO));

 

 

Thanks for reading 🙂