Transcript Mission Critical - AlwaysON

MODIFY THIS SLIDE FOR ACTUAL PRESENTER, DELETE THIS BAR AFTER
MODIFICATION
1
1The
percentage reduction in patching varies & can be less based on the server roles that are enabled & the type of patches that are applied.
•
•
•
•
Shared Storage solution
Instance Level HA
Instance Level DR
Doesn’t require database to be in FULL recovery
model
Availability Group
for HA & DR
•
•
•
•
•
Non-Shared Storage solution
(Group of) Database Level HA
(Group of) Database Level DR
DR replica can be Active Secondary
Requires database to be in FULL recovery model
Failover Cluster Instance for
local HA & Availability
Group for DR
• Combined Shared Storage and Non-Shared
Storage
• Instance Level HA
• (Group of) Database Level DR
• DR replica can be Active Secondary
• Requires database to be in FULL recovery model
Multi-site Failover Cluster
Instance (FCI)
for HA & DR
AlwaysOn Availability Groups is a new feature that enhances
and combines database mirroring and log shipping capabilities
• Multi-database failover
• Multiple secondaries
• Application failover using
virtual name
• Active Secondary
• Configuration Wizard
• Dashboard
• Synchronous and
asynchronous data
movement
• Built in compression and
encryption
• Auto-page repair
• Automatic and manual
failover (new design)
• Flexible failover policy
• System Center
Integration
• Rich diagnostic
infrastructure
• File-stream replication
• Replication publisher
failover
• Monitoring and
Troubleshooting
enhanced
• Automation using
PowerShell
Availability Groups Listener allow applications to failover seamlessly
to any secondary; reconnecting through Virtual Network Name
ServerA
ServerC
ServerB
2
D
B
2
DB
2
D
B
TechAG1
TechListener1
Primary
Secondary
Primary
Secondary
Application retry during failover
Parameter Sample: server
TechListener1;catalog HRDB
Connect to new primary once
failover is complete
and the listener is online
Database
Active Log
Synchronization
Availability Group uses WSFC
for
Database
Active Log
Synchronization
WSFC is a Common Microsoft Availability Platform
Multiple no data loss secondaries
Faster failover to DR
Rich diagnostics
Multi-DB failover
New Management Dashboard
AlwaysOn Active Secondary enables efficient utilization of
high availability hardware resources to improve overall IT efficiency
IT EFFICIENCY AND COSTEFFECTIVENESS ARE CRITICAL FOR
BUSINESSES
Idle hardware is no longer an option.
ACTIVE SECONDARY USES
Read-only workloads
Offloading Backups
SQLservr.exe
SQLservr.exe
Secondary
Primary
Secondary
Primary
InstanceA
InstanceB
Database Log
Synchronization
DB1
DB2
Reports
DB1
DB2
Reports
Readable secondary allow offloading read queries to secondary
Low data latency
After failover, the read applications can be automatically redirected to the new Secondary
(require explicit connection request)
Not a replacement for replication scenarios
Primary
Secondary
Log
Log
Capture
Capture
Commit
Log
Receive
Log Pool
Redo
Thread
Log Cache
Log Cache
Log Flush
DB1 Log
Redo
Pages
Log Hardened
DB1 Data
Acknowledge
Commit
DB1 Log
DB1 Data
Secondary read is always behind primary during transaction activity
CONCURRENCY AND BLOCKING
REDO can get blocked by reporting workload
REDO thread and read workload can deadlock
Read/Write
Read
SOLUTION
Internally map read workload to non blocking
isolation levels (no application changes
required)
•
•
•
•
•
Read Uncommitted  Snapshot Isolation
Read Committed  Snapshot Isolation
Repeatable Read  Snapshot Isolation
Serializable  Snapshot Isolation
Ignore all locking hints
PRIMARY
SECONDARIES
Never choose REDO as deadlock victim
RESULT
Blocking and deadlock between Reporting workload (i.e. Query) and REDO thread is
eliminated
No issues with DML (INSERT/DELETE/UPDATE) as it is not allowed
Will incur additional cost of row versioning.
READ / WRITE WORKLOAD
•
•
Connecting using AG Listener
Connection using FAILOVER_PARTNER (if
connection string of existing applications can’t be
changed)
CLIENT
READ ONLY WORKLOAD
•
•
•
Connection using VNN and
ApplicationIntent=ReadOnly
Connection to the secondary instance directly
ReadOnly Routing
MULTI SUBNET FAILOVER SCENARIO:
•
•
New client libraries =>
MultiSubnetFailover=True
Old client libraries configure appropriate client
connection timeout
PRIMARY
SECONDARIES
Backups from
any replica
Synchronous or
asynchronous secondaries
Primary backups still work
Adds capacity to
primary server by offloading backups to a
replica
Log backups done
on all replicas form
a single log chain
Recovery Advisor makes
restores simple
All SQL servers (including the secondary in the DR
site) in the same Windows domain
•
One Windows Server Failover Cluster spreads over
the primary and DR sites
All the databases must be in FULL recovery model
The unit of failover (for local HA, as well as DR) is
at the AG level, i.e., group of databases – not the
instance
•
•
Consider using Contained Database for containing
logins for failover
For jobs and other objects outside the database,
simple customization needed
No delayed apply on the secondary like log
shipping
Removing log shipping means the regular log
backup job is removed
•
Need to re-establish periodic log backup (essential
for truncating the log)
AlwaysOn Dashboard
System Center
Operations Manager
HA & DR Solution
Provide High Availability at the Instance Level
• Unit of failover = SQL server instance
• Maintain same virtual network name after failover. Clients re-connect to same name
• Instance restart requires database to go through recovery
Provide Disaster Recovery at the Instance Level
• Provide Disaster Recovery protection from site failure: be it network, power, infrastructure
or other site disasters.
• Require storage based replication technology and networking considerations
• Multi-subnet support:
SQL Server 2008
R2
SQL Server 2012
NO
• Create stretch Virtual-LAN (VLAN) to act as a single
subnet
YES
• IP address OR dependency support within SQL
Server setup
• SQL Engine skips binding to IP’s not online on start-up
Corpnet
Network Name: SqlClust
OR
IP1: 10.168.0.10
subnet 1
Local Site
SAN Replication
IP2: 192.168.0.10
subnet 2
Remote Site
WHY WE ENABLE THIS?
• tempdb access occupies large % of
SAN I/O
• Fast local HDD/SSD becomes
standard Server configuration
LOCAL TEMP
DB
LOCAL TEMP
DB
(Fast disk, SSD)
(Fast disk, SSD)
PRIMARY
SECONDARIES
BENEFITS
• Better overall performance
• Cost saving
IMPORTANT NOTE!
• Ensure that tempdb local paths are
available to SQL Service on all the
nodes
Diagnos
tics
Configurable options eliminate false failover
Improved logging for better diagnostics
SQL Server Hands-on-Labs
SQLSERV
ERLAUNC
H
.COM