Pro SQL Server 2008 Replication

938 Pages·2009·49.64 MB·English

by  Sujoy Paul

cyan yelloW
MaGenTa Black
panTone 123 c
Books for professionals By professionals® The eXperT’s Voice® in sQl serVer
Companion eBook Available
Pro SQL Server 2008 Replication
Pro
Dear Reader:
Replication facilitates the transfer of data and objects from one database to
another across different platforms and geographic locales. The physical separa-
RS
tion of databases and the inherent latency associated with replication makes it an
Pro
attractive technology in various business implementations. However, configuring,
e
administering, and optimizing a harmonious replication environment can be
Q
Author of a daunting task even for the seasoned DBA due to the complexities of not only
p
maintaining data integrity, but also knowing how the innards of replication work.
Pro SQL Server 2005
Replication This book explains the different types of replication available in SQL Server lL SQL Server 2008
2008 and provides realistic examples of how those types can be deployed.
i
In this book I discuss the configuration and administration of snapshot, trans-
c
actional, peer-to-peer, merge, and heterogeneous replication. You’ll see how
to work with configuration from both a GUI and a command-line perspective. a
S
Besides discussing the internals of different types of replication, I also dem-
onstrate the new features, such as the filestream feature in merge replication. t Replication
You’ll learn about bidirectional, transactional replication. And you’ll learn to i
e
configure replication through SQLCMD, using it, for example, to configure log o
shipping with replication.
Other tools such as the tablediff utility, the Database Engine Tuning Advisor, n
r
and Replication Monitor are covered, with emphasis upon their use in backing
up, recovering, and optimizing replication environments. Management Data
v
Warehouse, which is new in SQL Server 2008, is an exciting technology that can
help you monitor replication performance. You’ll find examples of how to use
the Management Data Warehouse in this book. e
I hope you find this book rewarding and as pleasurable to read as it was to
An essential and comprehensive guide to
write. I am grateful for the opportunity to teach you what I know on a topic I am
r
passionate about. developing, administering, and mastering the
Sujoy P. Paul, MCP complexities of SQL Server 2008 replication
2
Companion eBook
THE APRESS ROADMAP
0
Pro SQL Server Pro SQL Server 2008
Disaster Recovery Replication
Accelerated
0
SQL Server 2008 Pro T-SQL 2008 SQL Server 2008
See last page for details Programmer’s Guide Query Performance
on $10 eBook version Tuning Distilled
8
Sujoy P. Paul
ISBN 978-1-4302-1807-4
SOURCE CODE ONLINE
90000
www.apress.com Paul
Shelve in
Programming
User level: 9 781430 218074
Intermediate–Advanced
this print for content only—size & color not accurate spine = 1.773" 936 page count

Paul_18074FRONT.fm Page i Friday, May 22, 2009 9:11 AM
Pro SQL Server 2008
Replication
■ ■ ■
Sujoy Paul
Paul_18074FRONT.fm Page ii Friday, May 22, 2009 9:11 AM
Pro SQL Server 2008 Replication
Copyright © 2009 by Sujoy Paul
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-1807-4
ISBN-13 (electronic): 978-1-4302-1808-1
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: Don Watters
Editorial Board: Clay Andres, Steve Anglin, Mark Beckner, Ewan Buckingham, Tony Campbell,
Gary Cornell, Jonathan Gennick, Michelle Lowman, Matthew Moodie, Jeffrey Pepper, Frank Pohlmann,
Ben Renow-Clarke, Dominic Shakeshaft, Matt Wade, Tom Welsh
Project Manager: Kylie Johnston
Copy Editor: Ami Knox
Associate Production Director: Kari Brooks-Copony
Production Editor: Ellie Fountain
Compositor: Susan Glinert
Proofreader: April Eddy
Indexer: BIM Indexing & Proofreading Services
Artist: Kinetic Publishing Services, LLC
Cover Designer: Kurt Krames
Manufacturing Director: Tom Debolski
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 [email protected], or
visit http://www.springeronline.com.
For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600,
Berkeley, CA 94705. Phone [phone], fax [phone], e-mail [email protected], 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.
Paul_18074FRONT.fm Page iii Friday, May 22, 2009 9:11 AM
Dedicated to my parents, the late Subhendu P. Paul and Kavita Paul
Paul_18074FRONT.fm Page iv Friday, May 22, 2009 9:11 AM
Paul_18074FRONT.fm Page v Friday, May 22, 2009 9:11 AM
Contents at a Glance
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii
■ CHAPTER 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
■ CHAPTER 2 Replication Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
■ CHAPTER 3 Types of Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
■ CHAPTER 4 Configuring Snapshot Replication Using the GUI . . . . . . . . . . . . . . . 85
■ CHAPTER 5 Configuring Snapshot Replication Using T-SQL . . . . . . . . . . . . . . . 123
■ CHAPTER 6 Snapshot Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151
■ CHAPTER 7 Internals of Snapshot Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
■ CHAPTER 8 Configuring Transactional Replication Using the GUI . . . . . . . . . . 223
■ CHAPTER 9 Configuring Transactional Replication Using T-SQL . . . . . . . . . . . 261
■ CHAPTER 10 Internals of Transactional Replication . . . . . . . . . . . . . . . . . . . . . . . . 315
■ CHAPTER 11 Configuring Merge Replication Using the GUI . . . . . . . . . . . . . . . . . 399
■ CHAPTER 12 Web Synchronization with Merge Replication . . . . . . . . . . . . . . . . . 443
■ CHAPTER 13 Configuring Merge Replication Using T-SQL . . . . . . . . . . . . . . . . . . 471
■ CHAPTER 14 The Internals of Merge Replication . . . . . . . . . . . . . . . . . . . . . . . . . . 537
■ CHAPTER 15 Backup and Recovery of Snapshot and
Transactional Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599
■ CHAPTER 16 Backup and Recovery of Merge Replication . . . . . . . . . . . . . . . . . . 653
■ CHAPTER 17 Optimizing Snapshot Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697
■ CHAPTER 18 Optimizing Transactional Replication . . . . . . . . . . . . . . . . . . . . . . . . 737
■ CHAPTER 19 Optimizing Merge Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 801
■ CHAPTER 20 Heterogeneous Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 837
■ APPENDIX E-R Diagram of the mysales Database . . . . . . . . . . . . . . . . . . . . . . . 875
■ INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 879
v
Paul_18074FRONT.fm Page vi Friday, May 22, 2009 9:11 AM
Paul_18074FRONT.fm Page vii Friday, May 22, 2009 9:11 AM
Contents
About the Author . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xvii
About the Technical Reviewer . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xix
Acknowledgments . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxi
Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xxiii
■ CHAPTER 1 Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Distributed Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1
Distributed Data Transactions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Eager Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Lazy Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11
Replication in SQL Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 12
Benefits of Replication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 13
SQL Server 2008 Tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 14
SQL Server Configuration Manager . . . . . . . . . . . . . . . . . . . . . . . . . . 15
SQL Server Management Studio. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Database Engine Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Replication Monitor. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
■ CHAPTER 2 Replication Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Publisher-Subscriber Model . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 17
Components of Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 18
Distributor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 19
Publisher . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Subscriber. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Publication. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 20
Article. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Subscriptions. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 21
Agents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 23
vii
Paul_18074FRONT.fm Page viii Friday, May 22, 2009 9:11 AM
viii ■ CONTENTS
Physical Replication Models . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
Publisher/Distributor–Subscriber Model . . . . . . . . . . . . . . . . . . . . . . 26
Central Publisher–Multiple Subscribers Model. . . . . . . . . . . . . . . . . 26
Central Subscriber–Multiple Publishers Model. . . . . . . . . . . . . . . . . 28
Multiple Publishers–Multiple Subscribers Model . . . . . . . . . . . . . . . 29
Installing and Configuring Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Installing SQL Server Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . 30
Configuring with the GUI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 31
Configuring Database Properties . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Configuring with T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
■ CHAPTER 3 Types of Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
Snapshot Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 57
The Snapshot Agent Profile. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 59
How Snapshot Replication Works. . . . . . . . . . . . . . . . . . . . . . . . . . . . 61
Transactional Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 62
How Transactional Replication Works . . . . . . . . . . . . . . . . . . . . . . . . 64
Immediate Updating and Queued Updating Subscriptions. . . . . . . . 65
The Log and Queue Reader Agent Profiles . . . . . . . . . . . . . . . . . . . . 68
Peer-to-Peer Transactional Replication. . . . . . . . . . . . . . . . . . . . . . . 72
Merge Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 75
The Merge Agent Profile . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 78
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 81
Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 83
■ CHAPTER 4 Configuring Snapshot Replication Using the GUI . . . . . . . . 85
Configuring Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 86
Configuring a Subscription . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Configuring Push Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 95
Configuring Pull Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
Adding and Deleting an Article . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 120
Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 121
Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 122