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));

 

2018-02-08 20_17_41-DC01 on CATACLYSM - Virtual Machine Connection

 


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));

 

2018-02-08 20_16_41-DC01 on CATACLYSM - Virtual Machine Connection

 


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));

 

2018-02-08 20_17_07-DC01 on CATACLYSM - Virtual Machine Connection

 

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));

 

2018-02-08 20_42_33-DC01 on CATACLYSM - Virtual Machine Connection

 

Thanks for reading 🙂

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s

Create a website or blog at WordPress.com

Up ↑

%d bloggers like this: