Pro SQL Server 2008 Mirroring

258 Pages·2009·6.18 MB·English

by  Robert Davis

cyan yeLLOW
MaGenTa BLack
BOOks fOR PROfessIOnaLs By PROfessIOnaLs® The eXPeRT’s VOIce® In sQL seRVeR
Companion
Ken Simmons
Author of Pro SQL Server 2008 Mirroring AveaBiolaobkl e
Pro SQL Server 2008
Administration
Dear Reader, Pro
(Apress, 2009)
High availability and disaster recovery have become increasingly popular sub- S
jects in SQL Server. Many organizations now want to achieve uptime of 99.99% Q
and even 99.999% of the year. Since achieving “four nines” allows for less than
one hour of downtime per year and achieving “five nines” allows for less than six L
minutes per year, living up to this type of expectation is not an easy task.
Microsoft introduced database mirroring in SQL Server 2005 in order to help S Pro
you meet these stringent availability requirements. Database mirroring provides e
the automatic failover capabilities offered by failover clustering along with the
r
added disaster-recovery benefits of having your database on a second server. You
v
can also integrate database mirroring with other high-availability techniques to
e
provide an even higher level of availability.
Database mirroring is becoming a major factor in high-availability environ- r SQL Server
ments, and we want to provide you with a single point of reference you can use
2
both before and after implementing database mirroring. We aim to provide you
with the guidance you need to make the right decisions when deploying data- 0
base mirroring. We also aim to provide you with a quick place to turn to in the 0
event of a disaster. Maintaining a highly available SQL Server can be a stressful
8
job at times. However, having the right options in place, such as database mir-
roring, and having the knowledge to support the options you have in place will 2008 Mirroring
M
make your job much easier, not to mention a lot less stressful.
i
r
Sincerely,
r
Rob Davis and Ken Simmons o
r
i
n
THE APRESS ROADMAP g
Beginning SQL Server Pro SQL Server Pro SQL Server
2008 Administration 2008 Administration 2008 Failover Clustering A complete guide to planning, deploying,
Companion eBook
and maintaining database mirroring in
Accelerated SQL Pro T-SQL 2008 Pro SQL Server SQL Server 2008
Server 2008 Programmer’s Guide 2008 Mirroring

See last page for details
on $10 eBook version
S
SOURCE CODE ONLINE im Robert L. Davis and Ken Simmons
www.apress.com mD
(cid:41)(cid:51)(cid:34)(cid:46)(cid:0)(cid:25)(cid:23)(cid:24)(cid:13)(cid:17)(cid:13)(cid:20)(cid:19)(cid:16)(cid:18)(cid:13)(cid:18)(cid:20)(cid:18)(cid:19)(cid:13)(cid:21) oa
(cid:21)(cid:20)(cid:25)(cid:25)(cid:25) nv
i
ss
US $49.99
Shelve in
Databases / SQL Server
User level:
Intermediate–Advanced (cid:25) (cid:23)(cid:24)(cid:17)(cid:20)(cid:19)(cid:16) (cid:18)(cid:18)(cid:20)(cid:18)(cid:19)(cid:21)
this print for content only—size & color not accurate trim = 7.5" x 9.25" spine = 0.5625" 256 page count
Download at WoweBook.Com
Pro SQL Server 2008
Mirroring

■ ■ ■

Robert L. Davis, Ken Simmons

Download at WoweBook.Com
Pro SQL Server 2008 Mirroring
Copyright © 2009 by Robert L. Davis, Ken Simmons
All rights reserved. No part of this work may be reproduced or transmitted in any form or by any
means, electronic or mechanical, including photocopying, recording, or by any information storage
or retrieval system, without the prior written permission of the copyright owner and the publisher.
ISBN-13 (pbk): 978-1-4302-2423-5
ISBN-13 (electronic): 978-1-4302-2424-2
Printed and bound in the United States of America 9 8 7 6 5 4 3 2 1
Trademarked names may appear in this book. Rather than use a trademark symbol with every
occurrence of a trademarked name, we use the names only in an editorial fashion and to the benefit
of the trademark owner, with no intention of infringement of the trademark.
Lead Editor: Jonathan Gennick
Technical Reviewer: Glenn Berry
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Tony
Campbell, Gary Cornell, Jonathan Gennick, Jonathan Hassell, Michelle Lowman,
Matthew Moodie, Jeffrey Pepper, Frank Pohlmann, Douglas Pundick, Ben Renow-
Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh
Project Manager: Kylie Johnston
Copy Editor: Hastings Hart
Compositor: Mary Sudul
Indexer: Julie Grady
Artist: April Milne
Distributed to the book trade worldwide by Springer-Verlag New York, Inc., 233 Spring Street, 6th
Floor, New York, NY 10013. Phone 1-800-SPRINGER, fax [phone], e-mail orders-
[e-mail], or visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 233 Spring Street, New York, NY
10013. E-mail [e-mail], or visit http://www.apress.com.
Apress and friends of ED books may be purchased in bulk for academic, corporate, or promotional
use. eBook versions and licenses are also available for most titles. For more information, reference
our Special Bulk Sales–eBook Licensing web page at http://www.apress.com/info/bulksales.
The information in this book is distributed on an “as is” basis, without warranty. Although every
precaution has been taken in the preparation of this work, neither the author(s) nor Apress shall have
any liability to any person or entity with respect to any loss or damage caused or alleged to be
caused directly or indirectly by the information contained in this work.
The source code for this book is available to readers at http://www.apress.com.

Download at WoweBook.Com
■ CONTENTS

Contents at a Glance
Contents ............................................................................................................................... iv
About the Authors ............................................................................................................. xii
About the Technical Reviewer ........................................................................................ xiii
Introduction ....................................................................................................................... xiv
■ Chapter 1: High Availability Options ............................................................................. 1
■ Chapter 2: Database Mirroring Overview ...................................................................11
■ Chapter 3: Planning and Best Practices .....................................................................23
■ Chapter 4: Database Mirroring Setup .........................................................................31
■ Chapter 5: Automating Setup .......................................................................................53
■ Chapter 6: Maintenance Simplified .............................................................................95
■ Chapter 7: Monitoring and Alerting ......................................................................... 143
■ Chapter 8: Upgrading to SQL Server 2008 ............................................................... 171
■ Chapter 9: Reporting Considerations ....................................................................... 189
■ Chapter 10: Planning for Failure .............................................................................. 209
Index ................................................................................................................................ 227

iii
Download at WoweBook.Com
■ CONTENTS

Contents
Contents at a Glance .................................................................................................. iii
Contents ..................................................................................................................... iv
About the Authors ..................................................................................................... xii
About the Technical Reviewer ................................................................................. xiii
Introduction ............................................................................................................. xiv
■ Chapter 1: High Availability Options ....................................................................... 1
High Availability vs. Disaster Recovery .............................................................................. 1
Database Mirroring ............................................................................................................ 2
Failover Clustering ............................................................................................................. 2
Log Shipping ...................................................................................................................... 4
Replication ......................................................................................................................... 6
Comparing High-Availability Features ............................................................................... 7
Designing a Complete High-Availability Solution ............................................................... 8
Summary ......................................................................................................................... 10
■ Chapter 2: Database Mirroring Overview ............................................................. 11
Defining Key Terms .......................................................................................................... 11
Choosing an Operating Mode ........................................................................................... 12
High-Safety Mode ................................................................................................................................... 14
High-Performance Mode ........................................................................................................................ 15
Understanding Sessions .................................................................................................. 17
Pausing and Resuming Sessions ............................................................................................................ 17
iv
Download at WoweBook.Com
■ CONTENTS

Running Multiple Sessions ..................................................................................................................... 17
Understanding Mirroring States ....................................................................................... 18
Switching Roles ............................................................................................................... 20
Transparent Client Redirection ........................................................................................ 20
Enhanced Features .......................................................................................................... 20
Log Stream Compression ....................................................................................................................... 20
Automatic Page Recovery ....................................................................................................................... 21
Availability Enhancements ..................................................................................................................... 21
Features by Edition .......................................................................................................... 22
Summary ......................................................................................................................... 22
■ Chapter 3: Planning and Best Practices ............................................................... 23
Step One: Know Your Environment .................................................................................. 23
Are Your Servers Ready? ........................................................................................................................ 23
Is Your Database Ready? ........................................................................................................................ 24
Do You Know Your Service Accounts? .................................................................................................... 24
Do You Know Your Database Size? ......................................................................................................... 25
Is Your Mirror Comparable to Your Principal? ........................................................................................ 25
Are You Combining with Other Techniques? .......................................................................................... 25
Step Two: Know Your Applications .................................................................................. 25
How Are Your Applications Connecting to the Servers? ......................................................................... 26
Do Any Components Not Support Automatic Failover?........................................................................... 26
Do Your Applications Have Dependencies on Other Databases? ............................................................ 26
Do Your Applications Rely on External Resources? ................................................................................ 27
Step Three: Know Your Plan ............................................................................................ 27
Sample Plan ..................................................................................................................... 27
Best Practices .................................................................................................................. 28
Summary ......................................................................................................................... 30

v
Download at WoweBook.Com
■ CONTENTS

■ Chapter 4: Database Mirroring Setup ................................................................... 31
Before You Begin ............................................................................................................. 31
Part One: Advanced Preparation ............................................................................................................. 31
Part Two: Pre-setup Configuration ......................................................................................................... 32
Following the Wizard ....................................................................................................... 33
Setting Up Mirroring Manually ......................................................................................... 43
Step 1: Create Endpoints ........................................................................................................................ 44
Step 2: Create Logins ............................................................................................................................. 45
Step 3: Grant Connect on Endpoint to Logins ......................................................................................... 45
Step 4: Set the Principal Partner on Mirror ............................................................................................ 45
Step 5: Set the Mirror Partner on Principal............................................................................................. 46
Step 6: Set the Witness Partner on Principal .......................................................................................... 46
Step 7: Change the Operating Mode ....................................................................................................... 47
Special Case: Mirroring with Certificates ........................................................................ 47
Step 1: Create Database Master Key ...................................................................................................... 47
Step 2: Create a Certificate .................................................................................................................... 48
Step 3: Create an Endpoint ..................................................................................................................... 48
Step 4: Back Up the Certificate ............................................................................................................... 48
Step 5: Create Logins ............................................................................................................................. 49
Step 6: Create Users ............................................................................................................................... 49
Step 7: Assign the Certificate ................................................................................................................. 49
Step 8: Grant Connect on Endpoint to Logins ......................................................................................... 49
Step 9: Configure Database Mirroring .................................................................................................... 49
Test, Monitor, and Wrap Up ............................................................................................. 49
Troubleshooting Common Setup Issues .......................................................................... 50
Restore Issues ........................................................................................................................................ 50
Transaction Log Issues ........................................................................................................................... 51
Communication Issues ........................................................................................................................... 51
vi
Download at WoweBook.Com
■ CONTENTS

Summary ......................................................................................................................... 52
■ Chapter 5: Automating Setup ................................................................................ 53
Prepare the Database for Mirroring ................................................................................. 53
Back Up the Principal Database ...................................................................................... 55
Restore Full Backup on the Mirror Database with No Recovery ...................................... 60
Back Up the Log of the Principal Database...................................................................... 64
Restore the Log on the Mirror Database with No Recovery ............................................. 64
Create Endpoints on Instances ........................................................................................ 65
Create Login for the Service Accounts and Grant Connect .............................................. 67
Set the Mirroring and Witness Partners........................................................................... 69
Change the Operating Mode If Not Using a Witness ........................................................ 72
Helper Procedure to Manage Linked Servers ......................................................................................... 73
The Master Procedure ..................................................................................................... 74
Parameters ............................................................................................................................................. 74
Sample Execution ................................................................................................................................... 75
The Code ................................................................................................................................................. 75
Alternative Setup Using PowerShell ................................................................................ 82
The PowerShell Setup Script .................................................................................................................. 83
Summary ......................................................................................................................... 94
■ Chapter 6: Maintenance Simplified ....................................................................... 95
Routine Maintenance Considerations .............................................................................. 95
Transaction Log Backups ................................................................................................ 95
Backing Up the Log File .......................................................................................................................... 96
Write the Script .................................................................................................................................. 96
Make the Script Usable ..................................................................................................................... 97
Automating Log Backups ........................................................................................................................ 99
Managing Log Backup Retention .......................................................................................................... 106
vii
Download at WoweBook.Com
■ CONTENTS

Deleting Log Backups ........................................................................................................................... 107
Log File Size .................................................................................................................. 110
Identifying Log Files to Shrink .............................................................................................................. 111
Shrinking a Log File .............................................................................................................................. 111
Routine Patching and SQL Server Upgrades .................................................................. 115
Installing General Patches .................................................................................................................... 116
Upgrading or Patching SQL Server ....................................................................................................... 116
Fail Back to the Original Principal .................................................................................. 117
Reasons to Fail Back to the Original ..................................................................................................... 117
Procedure to Fail Back to the Original Principal ................................................................................... 118
Setting Database Owner and TRUSTWORTHY Property ................................................. 119
Controlled Failover of Mirrored Databases .................................................................... 121
Problems You Might Encounter ............................................................................................................ 121
Procedure to Perform a Controlled Failover ......................................................................................... 121
Automatic Failover of Mirrored Databases .................................................................... 129
Creating a SQL Alert to Detect Automatic Failovers ............................................................................. 130
Using Service Broker to Detect Automatic Failovers ............................................................................ 133
Stored Procedure to Respond to a Failover ..................................................................................... 135
Service Broker Queue ...................................................................................................................... 138
Service Broker Service and Route ................................................................................................... 139
Summary ....................................................................................................................... 141
■ Chapter 7: Monitoring and Alerting .................................................................... 143
Monitoring Components................................................................................................. 143
Database Mirroring Status Table .......................................................................................................... 143
Database Mirroring Monitor Job ........................................................................................................... 144
Understanding Warning Thresholds ............................................................................... 145
Using the Database Mirroring Monitor ........................................................................... 146
Registering Mirrored Databases ........................................................................................................... 146
viii
Download at WoweBook.Com