Transcript Mirroring - Dynamics User Group

STRYK System Improvement
Introduction Jörg Stryk
MS Dynamics NAV (Navision) since 1997
MS SQL Server since 2003
100% Focus on „NAV/SQL Performance Optimization“
STRYK System Improvement (since 2006)
Worldwide support of MS Dynamics Partners & Customers
Microsoft Most Valuable Professional
(MVP MS Dynamics NAV)
Book:
Software:
Blog:
“NAV/SQL Performance Field Guide”
“NAV/SQL Performance Toolbox”
“NAV/SQL Performance – My Two Cents”
Contact:
[email protected]
http://www.stryk.info
(ISBN 978-3-8370-1442-6)
http://www.stryk.info/toolbox.html
http://dynamicsuser.net/blogs/stryk/
Agenda
NAV/SQL High Availability:
High Availability with NAV and SQL Server
Some “Inspirations”
Abstract:
With increasing transaction volumes processed by NAV customers the need for HA solutions is also growing. This
session will explain some available HA solutions with NAV and SQL as there are e.g. “Transaction Log-Shipping” and
“Mirroring”, but also in addition with using “Database Snapshots”. The advantages and disadvantages of the
several solutions are discussed to inspire NAV partners to implement the appropriate scenarios at their customer’s
sites.
Two Big Questions
What is the maximum acceptable Data-Loss?
What is the maximum acceptable Server-Down-Time?
Off Topic
Backup Strategies (“Cold Stand-By”)
Full
Full
Log
18:00
…
08:00
Log
09:00
Log
Diff
10:00
Log
Diff
Log
Log
11:00
12:00
Diff
Diff
Log
13:00
14:00
Log
Log
15:00
16:00
Log
17:00
Log
18:00
Off Topic
Window Failover Cluster (“Hot Stand-By”)
„Heartbeat“
Active Node
Active Node
Passive Node
Failover
Storage = „Single Point of Failure“
Passive Node
Warm-Standby Solutions
• Transaction Log Shipping
• Database Mirroring
• Database Snapshots
Warm-Standby Solutions
Transaction Log Shipping
Main Server
Monitor
Standby Server
NAV
(STANDBY)
NAV
TRN
Standard NAV
Failover NAV
Client PC
Warm-Standby Solutions
Database Mirroring
LSN
Witness
Principal Server
Mirror Server
NAV
(NO RECOVERY)
NAV
Standard NAV
Failover NAV
Client PC
Warm-Standby Solutions
Synchronous vs. Asynchronous Mirroring
LSN
LSN
Principal Server
Client PC
COMMIT
Witness
COMMIT
aka „High Safety Mirroring“
With Witness: „High Availability Mode“
No Witness: „High Protection Mode“
Mirror Server
Warm-Standby Solutions
Synchronous vs. Asynchronous Mirroring
LSN
LSN
Principal Server
Client PC
COMMIT
Mirror Server
COMMIT
aka „High Performance Mirroring“
Additional (?)
Database Snapshots
Read/Write
SQL Server (Enterprise)
NAV
Snapshot
Client PC
Read Only
NAV_SNAPSHOT
(read only)
Data „Freeze“
Demo
Overview
Option
Edition
Failover
Witness
Data-Loss
Down-Time
Performance
Multiple
Mirrors
Connect to
Mirror
Costs
Failover Cluster
OS: EE
Auto
n/a
minimal
minimal
high
(Yes)
n/a
$$$
Mirroring (Async)
SQL: EE
Manual
No
small
medium
high
No
No
$$
Mirroring (Sync)
All
(Auto)
Yes
minimal
medium
medium
No
No
$
Log Shipping
All
(Auto)
Yes
medium
medium
medium
Yes
(Read Only)
$
Backups
All
Manual
No
medium+
maximum
medium
Yes
n/a
$
DB Snapshots
SQL: EE
n/a
n/a
n/a
n/a
n/a
n/a
Read Only
$$
HA & Failover Scenario
Mirroring
Principal Server
Mirror Server
NAV
(NO RECOVERY)
NAV
Backup
BAK/TRN
NAV
(STANDBY)
Snapshot
Standard NAV
SQL Backup
Standby Server
NAV_SNAP
(read only)
Reporting NAV
Log Shipping
Failover NAV #1
Failover NAV #2
Client PC
Challenges
What is the maximum acceptable Data-Loss?
What is the maximum acceptable Server-Down-Time?
Time to get Standby-System online
Transferring Logins
Notifications and Reactions
Re-Routing Clients/Service Tier(s)
Licensing
Who/What/When
Time to recover Backup
Weekend/Holiday
Time to get Primary-System online
Interfaces
The Future
SQL Server 2012 “Denali”
HADRON = High Availability & Disaster Recovery - Always ON
•
•
•
•
•
•
Querying the Mirror (= Replica)
Multiple Replica
Database Availability Groups
Mirroring more than 10 databases
Faster Recovery
Automatic Failover
http://www.microsoft.com/sqlserver/en/us/product-info/future-editions.aspx
Q&A
ThanksQuestions?
for your attention!