Apress - Pro SQL Server 2008 Replication (2009)01

Chia sẻ: Hoang Nhan | Ngày: | Loại File: PDF | Số trang:40

Thêm vào BST

Báo xấu

306
lượt xem 108
download

Download Vui lòng tải xuống để xem tài liệu đầy đủ

Apress - Pro SQL Server 2008 Replication (2009)01

Chủ đề:

Bình luận(0) Đăng nhập để gửi bình luận!

Lưu

Nội dung Text: Apress - Pro SQL Server 2008 Replication (2009)01

The eXperT’s Voice ® in sQl serVer Pro SQL Server 2008 Replication An essential and comprehensive guide to developing, administering, and mastering the complexities of SQL Server 2008 replication Sujoy P. Paul
Pro SQL Server 2008 Replication ■■■ Sujoy Paul
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 201-348-4505, e-mail orders-ny@springer-sbm.com, or visit http://www.springeronline.com. For information on translations, please contact Apress directly at 2855 Telegraph Avenue, Suite 600, Berkeley, CA 94705. Phone 510-549-5930, fax 510-549-5939, e-mail info@apress.com, 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.
Dedicated to my parents, the late Subhendu P. Paul and Kavita Paul
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
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
viii ■C O N T E N T S 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
■C O N T E N T S ix ■CHAPTER 5 Configuring Snapshot Replication Using T-SQL . . . . . . . . . 123 Dropping Subscriptions and Publications . . . . . . . . . . . . . . . . . . . . . . . . 123 Dropping a Pull Subscription . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 124 Dropping a Push Subscription . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 126 Dropping a Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 128 Configuring a Publication Using T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Creating a Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 129 Creating a Snapshot Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 132 Granting Publication Access . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Creating Articles for Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . 134 Starting the Snapshot Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 137 Configuring a Subscription Using T-SQL . . . . . . . . . . . . . . . . . . . . . . . . . 140 Configuring Push Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 140 Configuring Pull Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143 Configuring Anonymous Subscriptions. . . . . . . . . . . . . . . . . . . . . . . 146 Snapshot Replication Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . 148 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 150 ■CHAPTER 6 Snapshot Generation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Locating the Snapshot Folder . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 151 Securing Snapshot Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 152 Transmitting and Storing Snapshots . . . . . . . . . . . . . . . . . . . . . . . . . . . . 153 Transferring Compressed Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 154 Transferring a Snapshot with Other Methods . . . . . . . . . . . . . . . . . 156 Locating the Snapshot Files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 156 Determining the Types and Size of Snapshot Files . . . . . . . . . . . . . 157 Transmitting Snapshots Using FTP . . . . . . . . . . . . . . . . . . . . . . . . . . 161 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168 ■CHAPTER 7 Internals of Snapshot Replication . . . . . . . . . . . . . . . . . . . . . . . 169 On the Publisher Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 The Publication Access List . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169 The Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173 The Snapshot Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180 The Distribution Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190 The Maintenance Cleanup Jobs . . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
x ■C O N T E N T S On the Distributor Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 The Distribution Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 199 The MSDB Database. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210 On the Subscriber Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 220 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221 ■CHAPTER 8 Configuring Transactional Replication Using the GUI . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 223 Configuring Publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 224 Configuring Standard Transactional Publications . . . . . . . . . . . . . . 225 Configuring Transactional Publications with Updatable Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 233 Configuring Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Configuring Updatable Subscriptions for Transactional Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 240 Switching Update Modes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 249 Configuring Peer-to-Peer Transactional Replication . . . . . . . . . . . . . . . . 250 Setting Up the Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 250 Enabling the Publication for Peer-to-Peer Replication . . . . . . . . . . 251 Initializing the Database Schema . . . . . . . . . . . . . . . . . . . . . . . . . . . 252 Configuring Peer-to-Peer Topology . . . . . . . . . . . . . . . . . . . . . . . . . 254 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 259 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 260 ■CHAPTER 9 Configuring Transactional Replication Using T-SQL . . . . 261 Configuring Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 262 Configuring Standard Transactional Publication . . . . . . . . . . . . . . . 263 Configuring a Push Subscription for Standard Publication . . . . . . 272 Configuring a Pull Subscription for Standard Publication . . . . . . . . 273 Configuring Transactional Publication with Updatable Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 275 Configuring a Pull Subscription for Immediate Updating . . . . . . . . 281 Configuring a Push Subscription for Immediate Updating . . . . . . . 286 Configuring a Push Subscription for Queued Updating . . . . . . . . . 287 Configuring a Pull Subscription for Queued Updating. . . . . . . . . . . 289 Switching Between Immediate and Queued Updating Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 292
■C O N T E N T S xi Configuring Peer-to-Peer Transactional Replication . . . . . . . . . . . . . . . . 294 Adding the Publication on Node A . . . . . . . . . . . . . . . . . . . . . . . . . . . 294 Adding the Articles to the Publication on Node A . . . . . . . . . . . . . . 295 Adding the Subscription on Node A . . . . . . . . . . . . . . . . . . . . . . . . . 296 Adding the Push Subscription Agent on Node A . . . . . . . . . . . . . . . 296 Adding the Publication on Node B. . . . . . . . . . . . . . . . . . . . . . . . . . . 297 Adding the Articles to the Publication on Node B . . . . . . . . . . . . . . 298 Adding the Subscription on Node B . . . . . . . . . . . . . . . . . . . . . . . . . 299 Adding the Push Subscription Agent on Node B . . . . . . . . . . . . . . . 300 Checking the Configuration for Peer-to-Peer Replication . . . . . . . 300 Configuring Bidirectional Transactional Replication . . . . . . . . . . . . . . . . 302 Transactional Replication Stored Procedures . . . . . . . . . . . . . . . . . . . . . 310 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 313 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 314 ■CHAPTER 10 Internals of Transactional Replication . . . . . . . . . . . . . . . . . . 315 Concurrent Snapshot Processing . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 315 The Transaction Log . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 316 The Log Reader Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 321 The Queue Reader Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 329 Transactional Replication with Queued Updating . . . . . . . . . . . . . . . . . . 335 The Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 335 The Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 341 Conflict Detection and Resolution . . . . . . . . . . . . . . . . . . . . . . . . . . . 346 Transactional Replication with Immediate Updating . . . . . . . . . . . . . . . . 351 The Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 351 The Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 352 Peer-to-Peer Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 355 Standard Transactional Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 The Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 368 The Distribution Database . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 381 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 396 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398 ■CHAPTER 11 Configuring Merge Replication Using the GUI . . . . . . . . . . . 399 Configuring Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 400 Configuring a Publication for Download-Only Articles . . . . . . . . . . 400 Configuring a Publication for Standard Articles . . . . . . . . . . . . . . . . 409 Adding Filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 413
xii ■C O N T E N T S Configuring Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 425 Configuring Pull Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 426 Configuring Push Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . 434 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 440 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 441 ■CHAPTER 12 Web Synchronization with Merge Replication . . . . . . . . . . 443 Web Synchronization Basics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 443 Configuring a Publication for Web Synchronization . . . . . . . . . . . . . . . . 444 Configuring IIS for Web Synchronization . . . . . . . . . . . . . . . . . . . . . . . . . 446 Configuring SSL for Web Synchronization . . . . . . . . . . . . . . . . . . . . 446 Configuring the IIS Server for Web Synchronization . . . . . . . . . . . . 454 Setting Permissions for the SQL Server Replication Listener . . . . 459 Running IIS in Diagnostic Mode . . . . . . . . . . . . . . . . . . . . . . . . . . . . 460 Configuring Subscriptions for Web Synchronization . . . . . . . . . . . . . . . . 462 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 469 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 470 ■CHAPTER 13 Configuring Merge Replication Using T-SQL . . . . . . . . . . . . 471 Configuring Publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 471 Configuring Publication with Download-Only Articles . . . . . . . . . . 472 Configuring Publication with Standard Articles . . . . . . . . . . . . . . . . 479 Configuring Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511 Configuring Client-Type Push Subscriptions for Download-Only Articles . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 511 Configuring Pull Subscriptions for Standard Articles . . . . . . . . . . . 515 Identity Range Management . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 518 Understanding the Problem . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 519 Configuring Automatic Range Management . . . . . . . . . . . . . . . . . . 519 Merge Replication with Filestream . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 524 Merge Replication Stored Procedures . . . . . . . . . . . . . . . . . . . . . . . . . . . 532 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 534 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 536 ■CHAPTER 14 The Internals of Merge Replication . . . . . . . . . . . . . . . . . . . . . 537 The Merge Agent . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 537 Subsystems and Proxies . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545 Subsystem Objects . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545 The Roles of Subsystems and Proxies . . . . . . . . . . . . . . . . . . . . . . . 548
■C O N T E N T S xiii Publication and Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 560 Publication with Download-Only Articles . . . . . . . . . . . . . . . . . . . . . 560 Publication with Standard Articles and Parameterized Filters . . . . 576 Conflict Detection and Resolution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 593 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 597 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 598 ■CHAPTER 15 Backup and Recovery of Snapshot and Transactional Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599 Snapshot Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 599 Backing Up the Publication Database. . . . . . . . . . . . . . . . . . . . . . . . 601 Restoring the Publication Database . . . . . . . . . . . . . . . . . . . . . . . . . 611 Considerations for Other Databases . . . . . . . . . . . . . . . . . . . . . . . . . 615 Transactional Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616 Validating Subscriptions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 616 Standard Read-Only Transactional Replication . . . . . . . . . . . . . . . . 624 Transactional Publication with Updatable Subscriptions . . . . . . . . 632 Peer-to-Peer Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 639 Log Shipping with Transactional Replication . . . . . . . . . . . . . . . . . . 644 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 651 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 652 ■CHAPTER 16 Backup and Recovery of Merge Replication . . . . . . . . . . . . 653 Publication with Download-Only Articles . . . . . . . . . . . . . . . . . . . . . . . . . 653 Backing Up the Publication Database. . . . . . . . . . . . . . . . . . . . . . . . 654 Restoring the Publication Database for Push Subscription . . . . . . 654 Restoring the Publication Database for Pull Subscription . . . . . . . 659 Synchronizing Subscriptions with No-Sync Initialization Using T-SQL and sqlcmd . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 676 Backing Up and Restoring Subscription Databases . . . . . . . . . . . . . . . . 679 Validating Subscriptions for Merge Replication . . . . . . . . . . . . . . . . . . . . 682 Log Shipping with Download-Only Articles . . . . . . . . . . . . . . . . . . . . . . . 687 Configuring Log Shipping on the Primary Server . . . . . . . . . . . . . . 687 Configuring Log Shipping on the Secondary Server . . . . . . . . . . . . 689 Adding the Secondary Server Information to the Primary Server . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 692 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 693 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 695
xiv ■C O N T E N T S ■CHAPTER 17 Optimizing Snapshot Replication . . . . . . . . . . . . . . . . . . . . . . . . 697 Optimizing Performance . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 697 Using the System Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 699 Using SQL Server Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 717 Management Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 726 Configuring the Management Data Warehouse . . . . . . . . . . . . . . . 727 Creating Custom Data Collection Sets . . . . . . . . . . . . . . . . . . . . . . . 727 Viewing Reports and Query Statistics . . . . . . . . . . . . . . . . . . . . . . . . 728 Generating a Custom SQL Trace Collection Set . . . . . . . . . . . . . . . 729 Best Practices for Snapshot Replication . . . . . . . . . . . . . . . . . . . . . . . . . 732 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 734 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 736 ■CHAPTER 18 Optimizing Transactional Replication . . . . . . . . . . . . . . . . . . . 737 Performance Considerations for the Transaction Log . . . . . . . . . . . . . . 737 Using Tracer Tokens . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 740 Using the System Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 746 Using SQL Trace . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 751 Using the Database Engine Tuning Advisor . . . . . . . . . . . . . . . . . . . . . . . 785 Best Practices for Transactional Replication . . . . . . . . . . . . . . . . . . . . . . 795 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 799 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 800 ■CHAPTER 19 Optimizing Merge Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . 801 Using the System Monitor . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 801 Using the SQL Server Profiler . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 804 Using the Management Data Warehouse . . . . . . . . . . . . . . . . . . . . . . . . . 823 Adjusting Publication Retention . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 827 Changing Merge Agent Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 828 Snapshot Considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 830 Best Practices for Merge Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . 831 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 834 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 835
■C O N T E N T S xv ■CHAPTER 20 Heterogeneous Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 837 Snapshot Replication from an Oracle Publisher to a SQL Server Subscriber . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 837 Configuring an Oracle Publication for Snapshot Replication . . . . . 837 Configuring a SQL Server Subscription for Snapshot Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 849 Monitoring Replication with Replication Monitor . . . . . . . . . . . . . . . 856 Transactional Replication from an Oracle Publisher to a SQL Server Subscriber . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 858 Configuring an Oracle Publication for Transactional Replication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 859 Configuring a SQL Server Subscription for Transactional Publication . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 866 Summary . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 874 Quick Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 874 ■APPENDIX E-R Diagram of the mysales Database . . . . . . . . . . . . . . . . . . 875 ■INDEX . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 879
About the Author ■SUJOY PAUL has a bachelor’s degree in chemical engineering from the University of Toronto, Canada, and a postgraduate degree in the same discipline from the University of Strathclyde, Glasgow, UK. Since then, he has moved on to the software industry and has gained extensive experience in database management systems, such as Microsoft SQL Server, Sybase ASA, and Sybase ASE; data modeling; and case-based tools like PowerDesigner and ERWIN. His background includes expertise in database replication, with a special interest in two-way replication, backup and recovery, performance and tuning, and troubleshooting. He is a Microsoft Certified Professional on SQL Server 2000. His other professional interests include knowledge management, semantic web, and bioinformatics, in which he also has a postgraduate diploma. He has also written Pro SQL Server 2005 Replication. Currently, he is working as a Senior SQL Server DBA in Ontario, Canada. Sujoy is an avid fan of soccer and tennis. In his spare time, he plays squash and enjoys reading literature, and he has also published poetry. He can be reached at sujoyp@hotmail.com. xvii