Transcript Tips and Tricks for Successful Database Mirroring

Paul Filmalter
PFE
Microsoft
DAT313
Agenda
Setting the Scene
What’s New In Database Mirroring in SQL Server 2008
Tips and Tricks
Summary
Setting the Scene
Witness
Principle
Mirror
Setting it up
Agenda
Setting the Scene
What’s New In Database Mirroring in SQL Server 2008
Tips and Tricks
Summary
Improvements in 2008
Log Stream Compression
Works with both async and sync
Good network, local = minimal/no improvement
Will see more improvements with “bad”
networks, distance
Reduces send queue
Function of the transactions, not the network
Tradeoff: CPU hit on principal and mirror
On by default (Trace Flag 1462 to turn off)
Log Stream Compression
Log Stream Compression
Measuring
Database Mirroring: Log Bytes Sent/sec
(uncompressed)
Database Mirroring: Log Compressed Bytes
Sent/sec
Databases: Transactions/sec
SQL Statistics: Batch Requests/sec
Processor: % Processor
Log stream compression
Improvements in 2008
Data Page Corruption Recovery
823, 824, 829 error encountered
SQL Server will read partner
Good copy of page will replace bad copy
Recovery from corruption with database
mirroring
Improvements in 2008
Backup Compression
Enterprise Edition only, but can restore
compressed backups on all editions
Reduces initialization time
File sizes smaller
Quicker copy time
Watch the CPU
Agenda
Setting the Scene
What’s New In Database Mirroring in SQL Server 2008
Tips and Tricks
Summary
Number of Databases …
10 DBs is a recommendation, not a limitation
Customers ARE doing more than that
Will depend on workloads
I/O
Threads
CPU
Memory
More headroom with 64-bit
Failover Clustering and Database
Mirroring
Compliment each other
Instance vs. database protection
Age old question: asynchronous or
synchronous?
Considerations
Most want clustering to be the primary HA feature
Challenge: database mirroring failover is generally
faster than a cluster failover (Timeout)
Using a Mirror for Reporting
Done via snapshots
Must be refreshed
Can make the mirror more useful – not just a
standby
Downside: licensing
General tip: watch I/O use – do not want to
impact the mirror if your main goal is availability
Creating snapshots with database
mirroring
Using DBM to Upgrade 2005->2008
Minimal downtime
Unconfigure Witness
Must be synchronous (no automatic failover)
Use SQL Server 2005 SP2 CU5 (or later)
Upgrading from SQL Server 2005 to
2008 using database mirroring
Gotchas/Tips/Tricks 1
Application
Watch transaction size
Failover Partner in connection string
Make sure you have good networks
Use aliases in connection string if using failover
partner
Restoring the principal database
Cannot happen while in a mirroring session either
from a backup or a snapshot
Gotchas/Tips/Tricks 2
Initial configuration
Possibly configure log shipping, then convert to
DBM
Transactional replication stops if DBM paused
Prevents Subscriber from getting ahead of mirror
Trace flag 1448 (2008) – overrides behavior
2005 – KB937041 (hotfix for SP2, then TF)
T-log growth and backup affected
Gotchas/Tips/Tricks 3
Disk configuration
Need good I/O performance; can be a bottleneck
I/O on mirror may be higher than principal (by
design)
Trace Flag 3499 to alter I/O behavior (DO NOT USE
UNLESS TEST THOROUGHLY) – will delay failover
Logged operations and maintenance – effect on
principal’s log
Agenda
Setting the Scene
What’s New In Database Mirroring in SQL Server 2008
Tips and Tricks
Summary
Summary
Database mirroring can improve a database’s
availability with minimal to no loss of data
Database mirroring has improved with SQL Server 2008
Log stream compression can be helpful
Do proper monitoring
Need good configurations and hardware (esp. network
and disk)
South African SQL Server Usergroup
Meet monthly, 3rd Tuesday evening at
Microsoft’s Offices in Bryanston
Current leads: Paul Filmalter and Gail Shaw
Resources
Tech·Ed Africa 2009 sessions will be
made available for download the week
after the event from: www.tech-ed.co.za
www.microsoft.com/teched
www.microsoft.com/learning
International Content & Community
Microsoft Certification & Training Resources
http://microsoft.com/technet
http://microsoft.com/msdn
Resources for IT Professionals
Resources for Developers
Additional Resources
External Resources
SQL Server Customer Advisory Team blog
2005 Database Mirroring Best Practices and Performance Considerations
whitepaper
Configuring Database Mirroring with SQL Server 2008 PowerShell
Configuring SharePoint and Database Mirroring whitepaper
Database Mirroring and Log shipping Working Together whitepaper
Implementing Application Failover with Database Mirroring whitepaper
SQL Server Replication: Providing High Availability using Database
Mirroring whitepaper
SQL Server 2008 Business Value Calculator:
www.moresqlserver.com
SQL Server Community Resources
The Professional Association for SQL Server (PASS) is an independent,
not-for-profit association, dedicated to supporting, educating, and
promoting the Microsoft SQL Server community.
• Connect: Local Chapters, Special Interest Groups, Online Community
• Share: PASSPort Social Networking, Community Connection Event
• Learn: PASS Summit Annual Conference, Technical Articles, Webcasts
Become
a FREE
PASSorganization
Member: www.sqlpass.org/RegisterforSQLPASS.aspx
• More
about
the PASS
www.sqlpass.org/
Learn more about the PASS organization www.sqlpass.org/
Additional Community Resources
SQL Server Community Center
www.microsoft.com/sqlserver/2008/en/us/community-center.aspx
TechNet Community for IT Professionals
http://technet.microsoft.com/en-us/sqlserver/bb671048.aspx
Developer Center
http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspx
SQL Server 2008 Learning Portal
http://www.microsoft.com/learning/sql/2008/default.mspx
10 pairs of MP3
sunglasses to be won
Complete a session
evaluation and
enter to win!
© 2009 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.
The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should
not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS,
IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.