this print for content only—size & color not accurate spine = 1.773" 936 page count
Books for professionals By professionals
®
Pro SQL Server 2008 Replication
Dear Reader:
Replication facilitates the transfer of data and objects from one database to
another across different platforms and geographic locales. The physical separa-
tion of databases and the inherent latency associated with replication makes it an
attractive technology in various business implementations. However, configuring,
administering, and optimizing a harmonious replication environment can be
a daunting task even for the seasoned DBA due to the complexities of not only
maintaining data integrity, but also knowing how the innards of replication work.
This book explains the different types of replication available in SQL Server
2008 and provides realistic examples of how those types can be deployed.
In this book I discuss the configuration and administration of snapshot, trans-
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.
Besides discussing the internals of different types of replication, I also dem-
onstrate the new features, such as the filestream feature in merge replication.
You’ll learn about bidirectional, transactional replication. And you’ll learn to
configure replication through SQLCMD, using it, for example, to configure log
shipping with replication.
Other tools such as the tablediff utility, the Database Engine Tuning Advisor,
and Replication Monitor are covered, with emphasis upon their use in backing
up, recovering, and optimizing replication environments. Management Data
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.
I hope you find this book rewarding and as pleasurable to read as it was to
write. I am grateful for the opportunity to teach you what I know on a topic I am
passionate about.

Replication
Accelerated
SQL Server 2008
SQL Server 2008
Query Performance
Tuning Distilled
www.apress.com
SOURCE CODE ONLINE
Companion eBook

See last page for details
on $10 eBook version
An essential and comprehensive guide to
developing, administering, and mastering the
complexities of SQL Server 2008 replication
ISBN 978-1-4302-1807-4
9 781430 218074
9 0 0 0 0
Pro

Pro SQL Server 2008
Replication
■■■
Sujoy Paul
Paul_18074FRONT.fm Page i 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.

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 .
Paul_18074FRONT.fm Page ii Friday, May 22, 2009 9:11 AM
Dedicated to my parents, the late Subhendu P. Paul and Kavita Paul
Paul_18074FRONT.fm Page iii Friday, May 22, 2009 9:11 AM
Paul_18074FRONT.fm Page iv Friday, May 22, 2009 9:11 AM
v
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 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
Paul_18074FRONT.fm Page v Friday, May 22, 2009 9:11 AM
Paul_18074FRONT.fm Page vi Friday, May 22, 2009 9:11 AM
vii
Contents
About the Author

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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

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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 173
The Snapshot Agent
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 180
The Distribution Agent
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 190
The Maintenance Cleanup Jobs
. . . . . . . . . . . . . . . . . . . . . . . . . . . . 197
Paul_18074FRONT.fm Page ix Friday, May 22, 2009 9:11 AM
x
■
CONTENTS
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 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
Paul_18074FRONT.fm Page x Friday, May 22, 2009 9:11 AM
■
CONTENTS
xi
Configuring Peer-to-Peer Transactional Replication
. . . . . . . . . . . . . . . . 294
Adding the Publication on Node A
. . . . . . . . . . . . . . . . . . . . . . . . . . . 294

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

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

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
Subsystem Objects
. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 545
The Roles of Subsystems and Proxies
. . . . . . . . . . . . . . . . . . . . . . . 548
Paul_18074FRONT.fm Page xii Friday, May 22, 2009 9:11 AM
■
CONTENTS
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 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
Paul_18074FRONT.fm Page xiii Friday, May 22, 2009 9:11 AM
xiv
■
CONTENTS
■
CHAPTER 17
Optimizing Snapshot Replication
. . . . . . . . . . . . . . . . . . . . . . . . 697

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