Paul Filmalter PFE Microsoft DAT313 Agenda Setting the Scene Whats New In Database Mirroring in SQL Server 2008 Tips and Tricks Summary Setting the Scene Witness Setting it up demo ID: 343577
Download Presentation The PPT/PDF document "Tips and Tricks for Successful Database ..." is the property of its rightful owner. Permission is granted to download and print the materials on this web site for personal, non-commercial use only, and to display it on your personal computer provided you do not modify the materials and that you retain all copyright notices contained in the materials. By downloading content from our website, you accept the terms of this agreement.
Slide1Slide2
Tips and Tricks for Successful Database Mirroring Deployments with Microsoft SQL Server
Paul Filmalter
PFE
Microsoft
DAT313Slide3
Agenda
Setting the Scene
What’s
New In Database Mirroring in SQL Server
2008
Tips and Tricks
SummarySlide4
Setting the Scene
WitnessSlide5
Setting it up
demoSlide6
Agenda
Setting the Scene
What’s
New In Database Mirroring in SQL Server
2008
Tips and Tricks
SummarySlide7
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, distanceReduces send queueFunction of the transactions, not the networkTradeoff: CPU hit on principal and mirrorOn by default (Trace Flag 1462 to turn off)Slide8
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/secProcessor: % ProcessorSlide9
Log stream compression
demoSlide10
Improvements in 2008
Data Page Corruption Recovery
823, 824, 829 error encountered
SQL Server will read partner
Good copy of page will replace bad copySlide11
Recovery from corruption with database mirroring
demoSlide12
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 CPUSlide13
Agenda
Setting the Scene
What’s
New In Database Mirroring in SQL Server
2008
Tips and Tricks
SummarySlide14
Number of Databases …
10 DBs is a recommendation, not a limitation
Customers ARE doing more than that
Will depend on workloads
I/O
Threads
CPUMemoryMore headroom with 64-bitSlide15
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)Slide16
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 availabilitySlide17
Creating snapshots with database mirroring
demoSlide18
Using DBM to Upgrade 2005->2008
Minimal downtime
Unconfigure
Witness
Must be synchronous (no automatic failover)
Use SQL Server 2005 SP2 CU5 (or later)Slide19
Upgrading from SQL Server 2005 to 2008 using database mirroring
demoSlide20
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 databaseCannot happen while in a mirroring session either from a backup or a snapshotSlide21
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 affectedSlide22
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 logSlide23
Agenda
Setting the Scene
What’s
New In Database Mirroring in SQL Server
2008
Tips and Tricks
SummarySlide24
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)Slide25
question & answerSlide26
South African SQL Server Usergroup
Meet monthly, 3
rd
Tuesday evening at Microsoft’s Offices in
Bryanston
Current leads: Paul Filmalter and Gail ShawSlide27
www.microsoft.com/teched
International Content & Community
http://microsoft.com/technet
Resources for IT Professionals
http://microsoft.com/msdn
Resources for Developers
www.microsoft.com/learning
Microsoft Certification & Training Resources
Resources
Required Slide
Speakers,
TechEd 2009 is not producing
a DVD. Please announce that
attendees can
access session
recordings from Tech-Ed website. These will only be available after the event.
Tech
·Ed
Africa 2009 sessions will be made available for download the week after the event from:
www.tech-ed.co.zaSlide28
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
PowerShellConfiguring SharePoint and Database Mirroring whitepaperDatabase Mirroring and Log shipping Working Together whitepaperImplementing Application Failover with Database Mirroring
whitepaperSQL Server Replication: Providing High Availability using Database Mirroring whitepaperSQL Server 2008 Business Value Calculator: www.moresqlserver.comSlide29
SQL Server Community Resources
Become a FREE PASS Member:
www.sqlpass.org/RegisterforSQLPASS.aspx
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.aspxTechNet Community for IT Professionalshttp://technet.microsoft.com/en-us/sqlserver/bb671048.aspxDeveloper Center
http://msdn.microsoft.com/en-us/sqlserver/bb671064.aspxSQL Server 2008 Learning Portalhttp://www.microsoft.com/learning/sql/2008/default.mspx
Connect: Local Chapters, Special Interest Groups, Online CommunityShare: PASSPort Social Networking, Community Connection EventLearn: PASS Summit Annual Conference, Technical Articles, WebcastsMore about the PASS organization
www.sqlpass.org/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. Slide30
Required Slide
Complete a session evaluation and enter to win!
10 pairs of MP3
sunglasses
to be
wonSlide31
©
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.
Required Slide