Choirul Amri
Head of Enterprise Division
Ebiz Cipta Solusi |
[email protected] https://groups.yahoo.com/neo/groups/sqlserver-indo/info
Session Objectives
http://micha19.deviantart.com/art/let-s-cooking-manga-girl212148508
AlwaysOn Landscape SQL Server 2014
Failover Cluster Instance
(FCI)
Instance level
Shared storage
Failover per instance
No active secondary
Support auto, transparent
failover
Availability Groups (AG)
Database level
No shared storage
Failover per DB group
Active secondary (read only)
Support auto, transparent
failover
4
Availability Groups Advantage
No shared storage, easier and faster to implement
Active secondary for better box utilization
Offload reporting to secondary
Backup from secondary
Support automatic failover
Transparent client failover
Whats the Groups mean?
Primary Server
Secondary Server
Jika Primary bermasalah:
Ticketing dan CustomerDB
dipindahkan bersamaan ke
secondary
Ticketing
CustomerDB
Ticketing
CustomerDB
Failover happens on group of databases rather than individual DBs
You can also create a group with only one database
Why: You want to make sure that related DBs are failed-over together
Availability Groups Availability Mode
Asynchronous
Better performance
Primary commits without waiting notification from secondary
Possible data loss in failover
Synchronous
Primary waits for notification from secondary before
committing transaction
Performance penalty
No data loss during failover
7
Availability Groups Common Implementation
Synchronous
KOMODOSQL1 - Primary Server
Ticketing
Primary Replica
CustomerDB
Primary Replica
KOMODOSQL2 - Secondary Server
KOMODOSQL3 - Secondary Server
Ticketing
Secondary Replica
Ticketing
Secondary Replica
CustomerDB
Secondary Replica
Readable Secondary
CustomerDB
Secondary Replica
Non-Readable (standby only)
Asynchronous
Availability Listener
SQLAG-LS
Demo
Planned Manual Failover
Failover Mode
Automatic
Require Synchronous mode
No data loss
Machine shutdown or service unavailable
Planned - Manual
Require Synchronous mode
No data loss
Use SSMS or T-SQL
Forced - Manual
Replicas are in asynchronous commit mode
Forced means: You can afford loosing data
Obviously, data loss is possible
10
Automatic Failover - Requirement
Requirement
Minimum 1 pair databases are in Synchronous-commit with Automatic
failover
Secondary databases are in synchronized state
Windows Cluster is in good condition and fulfill the quorum requirement
11
Automatic Failover Ready to failover
12
Demo
Automatic Failover
13
Failover Mode Real Life Scenario
Automatic
Servers in the same data center with low latency network
Unexpected machine crash or SQL service disruption
Planned - Manual
Patching OS or SQL Server
HA/DR exercise
Upgrading hardware
Forced - Manual
Real disaster happens failover to DR site
3 servers, but 2 machines in primary DC are down
There is no primary available in the configuration!
14
Forced Manual Failover Fixing Resolving condition
Server Instance
Availability Mode
Failover Mode
Role
KOMODOSQL1\SQL2014
Synchronous commit
Automatic
Primary
KOMODOSQL2\SQL2014
Synchronous commit
Automatic
Secondary
KOMODOSQL3\SQL2014
Asynchronous commit
Manual
Secondary
What happen if:
1)
KOMODOSQL2\SQL2014 is down; then
2)
Client still can access the primary as usual (but now there is no automatic pair)
3)
KOMODOSQL1\SQL2014 is down
4)
Automatic failover to KOMODOSQL3\SQL2014 will not happen!
5)
DBs in KOMODOSQL3\SQL2014 replica is in resolving mode
6)
Client application cannot connect
15
Recovering the last man standing node
Your situation:
Primary and secondary in production are lost
You only have 1 node in DR, but it is in resolving state, inaccessible from client
16
Bring Your resolving replica alive
--- YOU MUST EXECUTE THE FOLLOWING SCRIPT IN SQLCMD MODE
--- KOMODOSQL3 is the replica in resolving state
:Connect KOMODOSQL3\SQL2014
ALTER AVAILABILITY GROUP [KomodoSQLAG]
FORCE_FAILOVER_ALLOW_DATA_LOSS;
17
Demo
Recovering from Resolving state
18
Q/A
19
Download my e-book
https://leanpub.com/sqlagindo
Work in progress, If You find typos
please email me
Visit my blog
Thank You
22
