Transcript Primary Secondary - Shanghai SQL Server User Group > Home

AlwaysOn Introduction in Denali
November 27th , 2011
Seventh Activity Activities Brief
• Introduce the AlwaysOn features of SQL2011 –
Denali
• Deep dive of AlwaysOn characters
Attendees
•
•
•
•
Tony Wu
Eric Xia (Microsoft)
Hua Zhu (Microsoft)
Simon Liao
Introducing AlwaysOn Active Secondary
IT efficiency and cost-effectiveness is critical for
businesses
– Idle hardware is not an option anymore
AlwaysOn Active Secondary enables efficient utilization of high
availability hardware resources thereby improving overall IT efficiency
Active Secondary can be utilized for read-only queries, or for taking
backups without impacting the primary workload.
Active Secondary:
Enabling Backup On Secondary
Kevin Farlee
Agenda
•
•
•
•
Database Mirroring Backups
Backup On Secondary
Features and Limitations
Backup Automation
–
–
–
–
Current Solutions
Backup On Secondary Solution
Configuring Backup Preferences
Example Configurations
• Demo
Database Mirroring Backups
• Backups are only allowed on Primary
• Secondary cannot be leveraged for backups
• Backup Workload impacts production Server
Active Secondary: Offloading Backup
• Backups are allowed on any replica
– Primary and secondary can be leveraged for backups
• Backup workload does not impact production
server
• Highly requested feature
Database Mirroring Backups
R/W workload
Failed
Backups
Backups
Primary
Secondary
SQL 2008 Database Mirroring
Active Secondary: Enabling Backup On
Secondary
R/W workload
Backups
Secondary
Backups
Backups
Primary
Secondary
Capabilities
• Backups can be done on any replica of a
database
• Secondary replica may be synchronous or
asynchronous
• Backups on primary replica still works
• Log backups done on all replicas form a single
log chain
• Recovery Advisor makes restores simple
Log backups done on all replicas form a single log chain
Replica 2
Replica 1
Log
back
up
Log
Bac
kup
LSN 1 - 10
LSN 21 - 30
Log
Bac
kup
Log
Bac
kup
LSN 11 - 20
LSN 31 - 40
Restrictions, cautions, and gotchas
• Differential backups are not supported on
secondary
• Only Copy-only full backups are supported on
secondary
• The only distinction is the differential bitmap clearing
• Advisable for backups to be stored centrally
Automated Backups
•
•
•
•
How to choose which replica to use for a backup?
DBM: Only the primary would work
Now: Backups succeed on all replicas
Solution: Declarative policy
Declarative Policy
• Preference for which role to use:
– Primary Only
– Secondary Only
– Secondary (but use Primary as fallback)
– Don’t care
• Relative Priority for each replica
Declarative Policy
• Logic:
– Filter out replicas which are not up & online
– Filter out replicas which don’t meet the policy for
role
– Select the highest priority replica among the
remaining set
– Use instance name as tie-breaker
Declarative Policy
• Policy is advisory only (NOT enforced)
• Automatically used by Maintenance Plans and
Log Shipping
• Implemented as a system function (returns
boolean)
DEMO
• Backup On Secondary
Session Code | Session Title
21
Active Secondary: Off-loading
Reporting Workload
Sunil Agarwal
Agenda
• Readable Secondary
– Reporting Workloads Today
– Value Proposition
– Architecture
– Impact and Performance
– Demo
– Application Connectivity
Reporting Workloads Today
Database Mirroring
•
•
Run all workloads including reporting on Primary
• Impacts primary workload
Resource/Blocking
Run reporting on mirror with snapshots.
•
Data Latency
•
Additional management overhead
•
No application failover
•
Performance Impact during snapshot
Transactional Replication
Most widely used for offloading reporting
scenarios today
Pros:
• Large number of subscribers,
Filtering of data
• Reporting work load specific indexes
Cons:
• Need replication expertise to manage
and tune environment
ReadSec: Reporting Workload
SQLservr.exe
Secondary
Primary
Secondary
Primary
SQLservr.exe
InstanceA
CRASH
DB1
DB2
Reports
•
•
•
•
InstanceB
Database Log
Synchronization
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
ReadSec – Data Latency
Log
LogCapture
Capture
Primary
Network
Secondary
Log Recieve
DB1
DB1
Log Pool
Commit
Redo
Thread
Log Cache
Redo
Pages
Log Cache
Log Flush
Log Hardened
DB1 Log
DB1 Data
Acknowledge
Commit
DB1 Log
DB1 Data
Secondary read is always behind primary during transaction activity
ReadSec:
Impact
of
Read
Workload
Secondary
Impact on RTO
Log Apply
DB1
•Reporting workload can take
resources away from REDO
thread
Redo
Thread
Log Cache
Redo
Pages
DB1 Log
DB1 Data
Reports
Resource
Intensive
(IO, Memory, CPU)
– Can impact the recovery
time(RTO)
• Mitigation
– User resource-governor to
control resources taken by
reporting workload
– If using combination of
sync/Async secondary, offload
reports to Async Secondary.
ReadSec: Impact of read workload
Impact on RTO (cont.)
•Concurrency and Blocking
– REDO can get blocked by reporting workload
– REDO thread and read workload can deadlock
•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
– 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.
SI Mapping and Row Versioning
• Support Snapshot Isolation Mapping
– Row versions must be created on the secondary replica
• Rule:
Data Row
XSN
RID
– Primary and Secondary replica (i.e. database(s) must
have the same physical structure
6 bytes
8 bytes
• Implications:
– 14 bytes must be added to data/index row on the
primary replica
– Not an issue if SI or RCSI is already enabled on the
primary replica
Row Versioning Optimizations
DDL and Reporting Workload
• On standalone SQL Server
– Query can block (even with NOLOCK) a concurrent
DDL
• Primary
SameReplica
can happen on Readable
Secondary
Secondary Replica
– REDO can block Reporting
Workload
Start
Reporting workload on table T1
SCH-S
lock on table T1
– Read workload can block•Takes
REDO
thread
DDL operation on table T1
• Execute
Mitigation
– DDL not common in production
environment
REDO processes
the DDL log record
SCH-M lock
on table T1
– You may have flexibility in•Requests
scheduling
maintenance
jobs with concurrent Reporting Workload
– Tools to identify blockingReporting Workload completes
• An XEvent is generated when REDO is blocked. You can
choose to kill Reporting workload
Blocked
Query Performance on Secondary
Optimized Query Plans
•Goal: Comparable query plan on Readable Secondary
•Query Optimization and Statistics
– SQL Server uses cost based optimizer that heavily relies on object statistics
– If statistics are missing, SQL Server automatically creates them and persists
•Auto-stat on readable secondary will require physical Changes
– Example: Table T1 (C1, C2, C3)
• Query on primary with predicate (C3 > 10).
• SQL Server auto-create the statistics, if needed, on column C3 on primary replica
• Won’t work on Readable Secondary as it will cause physical changes to the
database.
– Similar issue if the statistical information is stale on the secondary replica.
•Solution
– Auto-create statistics on the secondary replica but persist them in TempDB
Auto Stats: Permanent vs. Temp
SQL Memory On RS
stats T1/C1
Stats blob
blob T1/C1
T1/C1
Stats
Stats blob T1/C1
RS: Ghost Cleanup
• Issue
– Cannot remove the ghost row if needed on RS by active
query
• Solution
– Expose low_water_mark_for_ghosts for each database
– Ghost can only be removed when it is safe to.
DEMO
• Readable Secondary
Session Code | Session Title
35
Setting up Readable Secondary
Application Connectivity
ApplicationIntent – A New Connection Property
– Used to gate access to secondary
– Applicable when Secondary Replica set with
ALLOW_CONNECTIONS =READ_ONLY
Connection String
– Connect directly to a secondary instance
• Server=N2; Database=Db1; ApplicationIntent = ReadOnly
Read-Only Routing
– Connection behavior optimized for automatic routing of
read only applications to secondary
Supportability
• Catalog Views
– Sys.availability_replicas (used to see if
secondary allows READ)
– Sys.stats
• New column to indicate if the statistics is
temporary
• DMVs
– Sys.dm_hadr_database_replica_states
• Xevents
– REDO Blocked
– REDO Unblocked
Summary
• Better return on hardware investment
– Primary workload can better use available resources
– Reporting workload can use secondary replicas
• Comparable query plans on secondary replica
• Easy to manage and setup
Complete the Evaluation Form
to Win!
• Win a Dell Mini Netbook – every day – just for submitting your
completed form. Each session evaluation form represents a
chance to win.
• Pick up your evaluation form:
– In each presentation room
– Online on the PASS Summit website
Sponsored by Dell
• Drop off your completed form:
– Near the exit of each presentation room
– At the Registration desk
– Online on the PASS Summit website
Session Code | Session Title
40
Thank you
for attending this session and the
2011 PASS Summit in Seattle
Microsoft SQL
Server Clinic
Microsoft
Product Pavilion
Ask The Experts
@ Dev Pods
Hands-on Labs
Work through your
technical issues with SQL
Server CSS & get
architectural guidance from
SQLCAT
Talk with Microsoft SQL
Server & BI experts to
learn about the next
version of SQL Server and
check out the new
Database Consolidation
Appliance
Meet Microsoft SQL
Server Engineering team
members &
SQL MVPs
Get experienced through
self-paced & instructor-led
labs on our cloud based lab
platform - bring your laptop
or use HP provided
hardware
Room 611
Expo Hall
6th Floor Lobby
Room 618-620
Session Code | Session Title
42