Always On availability groups — Part 3

How to change Failover Mode in GUI
1. Open SSMS, Connect to primary replica (DB01)
2. Expand “Always On High Availability” > Expand “Availability Groups” > Expand “AG1(Primary)” > Expand “Availability Replicas”
3. Right-Click “DB03(Secondary)” > “Properties”
4. In “Failover mode:”, change “Automatic” to “Manual” > Click “OK”

How to change Failover Mode in TSQL

-- Change DB03's replica from Automatic to Manaual
ALTER AVAILABILITY GROUP "AG1" MODIFY REPLICA ON 'DB03'
WITH(
FAILOVER_MODE = MANUAL
)

-- Change DB03's replica from Syncharonous to Asynchronous
ALTER AVAILABILITY GROUP "AG1" MODIFY REPLICA ON 'DB03'
WITH(
AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT
)

Automatic Failover
1. It only work under Synchronous with automatic-failover mode

How to manual Failover in GUI
1. Open SSMS, Connect to primary replica (DB01)
2. Expand “Always On High Availability” > Expand “Availability Groups” > Right-Click “AG1(Primary)” > Expand “Availability Replicas”
3. Right-Click “DB03(Secondary)” > “Failover…”
4. In “Select New Primary Replica”, select “DB03” > Click “Next >”

How to manual Failover in TSQL

-- Run the following TSQL in Secondary replicas
USE master;
GO
ALTER AVAILABILITY GROUP [AG1] FAILOVER
GO

Force Failover with Possible Data Loss
1. Under asynchronous mode, all secondary databases are suspended after we failover
2. We need to resume in each secondary databases

How to force Failover in TSQL

-- Run the following TSQL in Secondary replicas
USE master;
GO
ALTER AVAILABILITY GROUP [AG1] FORCE_FAILOVER_ALLOW_DATA_LOSS
GO

How to resume suspended Database

ALTER DATABASE [AlwaysOn1] SET HADR RESUME;

How to connect to the availability group listener
1. Open SSMS, enter DNS name of availability group listener (AG1L.joeschoice.com) into “Server name:”
2. Verify the connection

-- It show which server you are currently connected
SELECT @@SERVERNAME

How to connect to the availability group listener in non-default port
1. Open SSMS, enter DNS name of availability group listener and Port (AG1L.joeschoice.com,1433) into “Server name:”

Read-Only Routing
1. It is used to route incoming connections to read-only secondary replica
2. It is used to management incoming connections

How to configure Read-Only Routing
1. Setup READ_ONLY_ROUTING_URL in each read-only replica

ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB01' WITH
(SECONDARY_ROLE(
READ_ONLY_ROUTING_URL = N'TCP://DB01.joeschoice.com:1433'
));

ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB02' WITH
(SECONDARY_ROLE(
READ_ONLY_ROUTING_URL = N'TCP://DB02.joeschoice.com:1433'
));

ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB03' WITH
(SECONDARY_ROLE(
READ_ONLY_ROUTING_URL = N'TCP://DB03.joeschoice.com:1433'
));

ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB04' WITH
(SECONDARY_ROLE(
READ_ONLY_ROUTING_URL = N'TCP://DB04.joeschoice.com:1433'
));

2. Setup the Read Only Routing List on Primary Replica

-- When DB01 is primary, please route connection to DB02
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB01' WITH
(PRIMARY_ROLE(
READ_ONLY_ROUTING_LIST=('DB02')
));

-- When DB02 is primary, please route connection to DB03
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB02' WITH
(PRIMARY_ROLE(
READ_ONLY_ROUTING_LIST=('DB03')
));

-- When DB03 is primary, please route connection to DB04
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB03' WITH
(PRIMARY_ROLE(
READ_ONLY_ROUTING_LIST=('DB04')
));

-- When DB04 is primary, please route connection to DB01
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB04' WITH
(PRIMARY_ROLE(
READ_ONLY_ROUTING_LIST=('DB01')
));

How to configure load balancing Read-Only Routing
1. Setup READ_ONLY_ROUTING_URL in each read-only replica

2. Setup the Read Only Routing List on Primary Replica

-- When DB01 is primary, please route connection to DB02, DB03, DB04
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB01' WITH
(PRIMARY_ROLE(
READ_ONLY_ROUTING_LIST=('DB02','DB03','DB04')
));

How to configure of nested load balancing Read-Only Routing
1. Setup READ_ONLY_ROUTING_URL in each read-only replica

2. Setup the Read Only Routing List on Primary Replica

-- When DB01 is primary, please route connection to DB02, DB03, DB04
-- DB04 only be connected when both of DB02 and DB03 are unavailable
ALTER AVAILABILITY GROUP AG1
MODIFY REPLICA ON N'DB01' WITH
(PRIMARY_ROLE(
READ_ONLY_ROUTING_LIST=(('DB02','DB03'),'DB04')
));

How to connect to Read-Only Routing
1. Open SSMS, Click “Options >>”, Select “Additional Connection Parameters”
2. Enter “ApplicationIntent=ReadOnly;MultiSubnetFailover=True;”
3. Clicl “Options <<“, enter DNS name of availability group listener (AG1L.joeschoice.com) into “Server name:”

Joe's choice

Always On availability groups — Part 3

Author: Joe Chan

Search Me

Recent Posts

Author: Joe Chan

Search Me

Recent Posts

Tag Cloud