Book Review: A Guide to SQL Server 2000 Transactional, Snapshot Replication
Last updated: March 25th '05 | Best viewed with: All popular browsers | Best viewed at: 1024x768 | Links to external sites will open in a new window

About myself
My technical skills
My favorites
My picture album

Shortcut keys
My code library

VB resources
SQLServer resources
SQLServer books
Replication FAQ
Scripting resources
ASP resources

Search my site
Sign my guestbook
Contact information

SQL Server Articles New

This month's 2 click survey:
Is .NET important for a database professional?

Click here to find out the top 15 SQL Server books purchased by this site's visitors! NEW

NEW!!! Subscribe to my newsletter:
Want to keep in touch with the latest in SQL Server world? Email with 'subscribe' in the subject line

Book review: A guide to SQL Server 2000 transactional and snapshot replication

Click here to buy: A guide to SQL Server 2000 Transactional & Snapshot Replication

A Guide to SQL Server 2000 Transactional & Snapshot Replication


Hilary Cotter, Microsoft SQL Server MVP


Not While the Surf's Up Press





Click here to buy this book from

As the title suggests, this is a book about SQL Server replication, and just about SQL Server replication. Yes, at last, there is a dedicated, comprehensive book, that covers all aspects of SQL Server 2000 transactional and snapshot replication. This book comes from Hilary Cotter, who is an SQL Server MVP. That is a very familiar name, if you frequently hang out in the Microsoft SQL Server Full-Text search and Replication newsgroups. Hilary has been a prolific answerer in these newsgroups for years, helping thousands of users from the SQL Server community. Hilary has a lot of hands on experience with implementing and administering SQL Server replication. Coming from an author like him, the book has to be a good one. So I decided to have a read to find out for myself. After reading all the important and interesting chapters from this book, I can happily say that, this is the definitive book on SQL Server 2000 replication, a must have and must read for any SQL Server DBA, interested in learning the ins and outs of SQL Server replication. This is not only a book for beginners, but also for DBAs with replication experience. I myself, have been involved with SQL Server 2000 replication since year 2000, but still managed to learn a lot of tips and tricks from this book. Simply put, this is a replication book, that I highly recommend. At 1000+ pages, this book is packed with tons of SQL scripts and code samples related to replication programming.

Now let me quickly walk you through the chapters of this book:

Chapter 1: An Introduction to Replication

This is a comprehensive chapter on, what replication is all about. Introduces you to all the terminology used in the replication world. Discusses the pros and cons of, and when to use various other methods like backup/restore, log shipping, linked servers, triggers, DTS, clustering, hardware mirroring etc. Concludes with a discussion of various replication types supported by SQL Server 2000, like: snapshot replication, transactional replication, bi-directional transactional replication, immediate updating, and queued updating subscribers and merge replication.

Chapter 2: Installing Replication

In this chapter, Hilary walks you through various wizards and screens one needs to understand to setup replication in SQL Server 2000. He also explains how to setup replication with heterogeneous subscribers like MS Access, MSDE, ODBC and OLE DB data sources, Oracle, MySQL, DB2 AS/400, Sybase. Then he also explains how to use the under-the-hood SQL Server system replication stored procedures. This is especially useful for those DBAs, that like to script their replication setup.

Chapter 3: Snapshot Replication Wizard

All about the wizards provided by Enterprise Manager for setting up snapshot replication.

Chapter 4: The Transactional Replication Wizards

A detailed chapter, that uses ample screen shots to explain all the transactional replication wizards and the advanced options provided by those wizards.

Chapter 5: Creating Subscriptions to Transactional and Snapshot Publications

Explains how push and pull subscriptions work, and various methods for setting up pull subscriptions, like FTP, Windows Synchronization Manager (WSM) etc.

Chapter 6: Replication Stored Procedures for Snapshot and Transactional Publications

This is a lengthy chapter that covers all the replication system stored procedures related to snapshot and transactional publications. These stored procedures are divided into four main categories: Configuration stored procedures, Modification stored procedures, Dropping/Deleting stored procedures and Informational stored procedures. Hillary did a great job at explaining all the input/output parameters for these stored procedures, and how to use various parameters of these stored procedures. A much detailed description, compared to SQL Server Books Online.

Chapter 7: Transactional and Snapshot Programming Using SQL-DMO

Yet another detailed chapter that deals with programming replication using SQL-DMO (Distributed Management Objects). This chapter is full of SQL-DMO code snippets, in VBScript and covers all the methods, properties, and events supported by various replication DMO objects.

Chapter 8: Transactional and Snapshot Programming using ActiveX Controls

SQL Server ships with a set of ActiveX controls, that expose all the functionality required to setup and administer replication. This chapter shows you how to use these ActiveX controls, along with code samples, and examples.

Chapter 9: Snapshot and Transactional Replication Internals

As the chapter name indicates, this is all about the internals and the nitty-gritty of how SQL Server replication works behind the scenes. Hilary explains how replication uses various internal tables to manage itself. Also provided are the various SQL queries that examine these system tables to monitor and check the health of the replication setup. Also discusses the various replication agents like the snapshot, logreader, distribution in detail. This chapter is a must read for anyone in charge of setting up, maintaining, performance tuning, and troubleshooting replication. For example, have you ever wondered how to convert the transaction id/LSN returned by DBCC OPENTRAN to the same format, as stored in the replication system tables like msrepl_commands and msrepl_transactions? This chapter has the answer for you!

Chapter 10: Troubleshooting Replication

Provides you with information on how to understand and troubleshoot replication problems. Shows you where to and how to look for clues, when troubleshooting a replication problem. Discusses the common problems encountered by various replication agents. Chapter concludes with a Question & Answer style discussion of various commonly encountered replication problems and their solutions.

Appendix A: Snapshot & Transactional Publications

Walks you through the basic replication wizards for snapshot and transactional replication.

Appendix B: Advanced Transactional and Snapshot Options

Looks at some of the advanced options available with snapshot and transactional replication.

Appendix C: @frequency_value values

Shows you values in both hex (hexadecimal) and decimal for all possible combinations of days of the week which are used in the @frequency_value for replication scheduling stored procedures.

My conclusion? A great book on SQL Server replication and a must read for everyone interested in database replication, let them be beginners or advanced DBAs.

Click here to buy this book from

Disclaimer, terms of use and privacy policy
Copyright 1997 - 2005 Narayana Vyas Kondreddi. All rights reserved.