SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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 ??

SQL Undercover

David Fowler and Adrian Buckman, two database nerds who love nothing more than to spend their time, reading about, researching and sharing all things SQL Server. David is a DBA with over 15 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 3 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies.

Comments

Leave a comment on the original post [sqlundercover.com, opens in a new window]

Loading comments...