Pro SQL Server 2008 Replication

Prelims
......Page 1
Contents at a Glance......Page 7
Contents......Page 9
About the Author......Page 19
About the Technical Reviewer......Page 21
Acknowledgments......Page 23
Prerequisites......Page 25
Contacting the Author......Page 26
Distributed Data......Page 27
Distributed Data Transactions......Page 31
Eager Replication......Page 35
Lazy Replication......Page 37
Replication in SQL Server......Page 38
Benefits of Replication......Page 39
SQL Server 2008 Tools......Page 40
Replication Monitor......Page 41
Quick Tips......Page 42
Publisher-Subscriber Model......Page 43
Components of Replication......Page 44
Distributor......Page 45
Publication......Page 46
Subscriptions......Page 47
Agents......Page 49
Log Reader Agent......Page 50
Queue Reader Agent......Page 51
Central Publisher–Multiple Subscribers Model......Page 52
Central Subscriber–Multiple Publishers Model......Page 54
Multiple Publishers–Multiple Subscribers Model......Page 55
Installing SQL Server Replication......Page 56
Configuring with the GUI......Page 57
Distribution Database Properties......Page 63
Distributor Server Properties......Page 65
Distribution Agent Profiles......Page 66
Publisher Server Properties......Page 68
Replication Agents......Page 70
Configuring with T-SQL......Page 72
Summary......Page 80
Quick Tips......Page 81
Snapshot Replication......Page 83
The Snapshot Agent Profile......Page 85
How Snapshot Replication Works......Page 87
Transactional Replication......Page 88
How Transactional Replication Works......Page 90
Immediate Updating Subscriptions......Page 91
Queued Updating Subscriptions......Page 93
The Log and Queue Reader Agent Profiles......Page 94
Peer-to-Peer Transactional Replication......Page 98
Merge Replication......Page 101
The Merge Agent Profile......Page 104
Summary......Page 107
Quick Tips......Page 109
Configuring Snapshot Replication Using the GUI......Page 111
Configuring Publication......Page 112
Configuring Push Subscriptions......Page 121
Configuring Pull Subscriptions......Page 130
Starting Synchronization for Pull Subscriptions......Page 138
Starting Pull Subscriptions Using the WSM......Page 139
Adding and Deleting an Article......Page 146
Summary......Page 147
Quick Tips......Page 148
Dropping Subscriptions and Publications......Page 149
Dropping a Pull Subscription......Page 150
Dropping a Push Subscription......Page 152
Dropping a Publication......Page 154
Creating a Publication......Page 155
Creating a Snapshot Agent......Page 158
Creating Articles for Publication......Page 160
Starting the Snapshot Agent......Page 163
Adding a Subscription to the Publication Database......Page 166
Adding a Scheduled Agent Job on the Publisher......Page 168
Creating a Pull Subscription and Adding a Scheduled Agent Job on the Subscriber......Page 169
Synchronizing a Pull Subscription......Page 171
Configuring Anonymous Subscriptions......Page 172
Snapshot Replication Stored Procedures......Page 174
Summary......Page 175
Quick Tips......Page 176
Locating the Snapshot Folder......Page 177
Securing Snapshot Data......Page 178
Transmitting and Storing Snapshots......Page 179
Transferring Compressed Files......Page 180
Locating the Snapshot Files......Page 182
Determining the Types and Size of Snapshot Files......Page 183
Setting Up the Snapshot Folder......Page 187
Setting Up the Subscriber Server’s Alias......Page 190
Configuring the Pull Subscription......Page 191
Summary......Page 193
Quick Tips......Page 194
The Publication Access List......Page 195
The Publication......Page 199
Enabling the Database for Replication......Page 201
Adding the Publication......Page 203
Adding the Articles......Page 204
The Snapshot Agent......Page 206
The Distribution Agent......Page 216
The Maintenance Cleanup Jobs......Page 223
The Distribution Database......Page 225
The MSDB Database......Page 236
On the Subscriber Server......Page 242
Summary......Page 246
Quick Tips......Page 247
Configuring Transactional Replication Using the GUI......Page 249
Configuring Publications......Page 250
Configuring Standard Transactional Publications......Page 251
Configuring Transactional Publications with Updatable Subscriptions......Page 259
Configuring Updatable Subscriptions for Transactional Publication......Page 266
Switching Update Modes......Page 275
Setting Up the Publication......Page 276
Enabling the Publication for Peer-to-Peer Replication......Page 277
Initializing the Database Schema......Page 278
Configuring Peer-to-Peer Topology......Page 280
Summary......Page 285
Quick Tips......Page 286
Configuring Transactional Replication Using T-SQL......Page 287
Configuring Publication......Page 288
Enabling Publication for Transactional Replication......Page 289
Adding Articles to the Publication......Page 292
Creating the Snapshot Agent and Generating the Snapshot......Page 297
Configuring a Push Subscription for Standard Publication......Page 298
Configuring a Pull Subscription for Standard Publication......Page 299
Configuring Transactional Publication with Updatable Subscriptions......Page 301
Verifying That the Publication Supports Immediate Updating Subscriptions......Page 307
Adding the Pull Subscription on the Subscriber......Page 308
Adding the Pull Subscription Agent......Page 309
Configuring Security for the Synchronization Triggers......Page 310
Adding the Subscription on the Publisher Server......Page 311
Configuring a Push Subscription for Immediate Updating......Page 312
Configuring a Push Subscription for Queued Updating......Page 313
Configuring a Pull Subscription for Queued Updating......Page 315
Switching Between Immediate and Queued Updating Subscriptions......Page 318
Adding the Publication on Node A......Page 320
Adding the Articles to the Publication on Node A......Page 321
Adding the Push Subscription Agent on Node A......Page 322
Adding the Publication on Node B......Page 323
Adding the Articles to the Publication on Node B......Page 324
Adding the Subscription on Node B......Page 325
Checking the Configuration for Peer-to-Peer Replication......Page 326
Configuring Bidirectional Transactional Replication......Page 328
Transactional Replication Stored Procedures......Page 336
Summary......Page 339
Quick Tips......Page 340
Concurrent Snapshot Processing......Page 341
The Transaction Log......Page 342
The Log Reader Agent......Page 347
The Queue Reader Agent......Page 355
The Publication......Page 361
The Subscriptions......Page 367
Conflict Detection and Resolution......Page 372
The Publication......Page 377
The Subscriptions......Page 378
Peer-to-Peer Replication......Page 381
The Publication......Page 394
The Distribution Database......Page 407
Summary......Page 422
Quick Tips......Page 424
Configuring Merge Replication Using the GUI......Page 425
Configuring a Publication for Download-Only Articles......Page 426
Configuring a Publication for Standard Articles......Page 435
Adding Filters......Page 439
Configuring Subscriptions......Page 451
Configuring Pull Subscriptions......Page 452
Synchronizing with the Windows Synchronization Manager......Page 459
Configuring Push Subscriptions......Page 460
Summary......Page 466
Quick Tips......Page 467
Web Synchronization Basics......Page 469
Configuring a Publication for Web Synchronization......Page 470
Configuring SSL for Web Synchronization......Page 472
Configuring the IIS Server for Web Synchronization......Page 480
Setting Permissions for the SQL Server Replication Listener......Page 485
Running IIS in Diagnostic Mode......Page 486
Configuring Subscriptions for Web Synchronization......Page 488
Summary......Page 495
Quick Tips......Page 496
Configuring Publications......Page 497
Configuring Publication with Download-Only Articles......Page 498
Configuring Publication with Standard Articles......Page 505
Configuring Client-Type Push Subscriptions for Download-Only Articles......Page 537
Server-Type Subscriptions......Page 541
Client-Type Subscription......Page 543
Identity Range Management......Page 544
Configuring Automatic Range Management......Page 545
Merge Replication with Filestream......Page 550
Merge Replication Stored Procedures......Page 558
Summary......Page 560
Quick Tips......Page 562
The Merge Agent......Page 563
Subsystem Objects......Page 571
The Roles of Subsystems and Proxies......Page 574
Publication with Download-Only Articles......Page 586
Initializing Subscriptions for Dynamic Partitioning of Data......Page 602
The Merge Agent’s Evaluation of Partitions......Page 615
The Merge Agent’s Enumeration Process......Page 618
Conflict Detection and Resolution......Page 619
Summary......Page 623
Quick Tips......Page 624
Snapshot Replication......Page 625
Backing Up the Publication Database......Page 627
Restoring the Publication Database......Page 637
Considerations for Other Databases......Page 641
Validating Subscriptions......Page 642
Checking for Nonconvergence......Page 643
Logging Validation Errors......Page 646
Standard Read-Only Transactional Replication......Page 650
Backing Up the Publication Database......Page 651
Restoring the Publication Database......Page 652
Transactional Publication with Updatable Subscriptions......Page 658
Backing Up the Publication Database......Page 659
Restoring the Publication Database......Page 662
Peer-to-Peer Replication......Page 665
Log Shipping with Transactional Replication......Page 670
Summary......Page 677
Quick Tips......Page 678
Publication with Download-Only Articles......Page 679
Restoring the Publication Database for Push Subscription......Page 680
Restoring the Publication Database for Pull Subscription......Page 685
Synchronizing Subscriptions with No-Sync Initialization Using T-SQL and sqlcmd......Page 702
Backing Up and Restoring Subscription Databases......Page 705
Validating Subscriptions for Merge Replication......Page 708
Configuring Log Shipping on the Primary Server......Page 713
Configuring Log Shipping on the Secondary Server......Page 715
Adding the Secondary Server Information to the Primary Server......Page 718
Summary......Page 719
Quick Tips......Page 721
Optimizing Performance......Page 723
Using the System Monitor......Page 725
Using SQL Server Profiler......Page 743
Management Data Warehouse......Page 752
Creating Custom Data Collection Sets......Page 753
Viewing Reports and Query Statistics......Page 754
Generating a Custom SQL Trace Collection Set......Page 755
Best Practices for Snapshot Replication......Page 758
Summary......Page 760
Quick Tips......Page 762
Performance Considerations for the Transaction Log......Page 763
Using Tracer Tokens......Page 766
Using the System Monitor......Page 772
Using SQL Trace......Page 777
Using the Database Engine Tuning Advisor......Page 811
Best Practices for Transactional Replication......Page 821
Summary......Page 825
Quick Tips......Page 826
Using the System Monitor......Page 827
Using the SQL Server Profiler......Page 830
Using the Management Data Warehouse......Page 849
Adjusting Publication Retention......Page 853
Changing Merge Agent Parameters......Page 854
Snapshot Considerations......Page 856
Best Practices for Merge Replication......Page 857
Summary......Page 860
Quick Tips......Page 861
Configuring an Oracle Publication for Snapshot Replication......Page 863
Configuring a SQL Server Subscription for Snapshot Publication......Page 875
Monitoring Replication with Replication Monitor......Page 882
Transactional Replication from an Oracle Publisher to a SQL Server Subscriber......Page 884
Configuring an Oracle Publication for Transactional Replication......Page 885
Configuring a SQL Server Subscription for Transactional Publication......Page 892
Quick Tips......Page 900
E-R Diagram of the mysales Database......Page 901
Index......Page 905