Book Review: Pro SQL Server 2008 Replication By Sujoy P. Paul

At its base, replication enables data and objects to be transferred from one database to another across different platforms and geographic locales. While configuring, administering, and optimizing a replication environment can be a daunting task, the goal of Pro SQL Server 2008 Replication is to break down the information needed accomplish these goals and fill in the gaps with real-world examples.

The primary audience for Pro SQL Server 2008 Replication is the database administrator who must plan and implement a replication environment. It is also for the developer who must deal with replication issues in their code. The book is 936 pages divided into 20 chapters. I am going to break it out into six functional sections and an add-on chapter. The book has no such sections, but that is how the book seems like it could be broken down.

Intro: Chapters 1-3 begins by explaining that there are two methods of distributing data in SQL Server. The focus of this book is on replication, but the author covers the other method – that of distributed transactions, and compares the differences between the two.

Next he goes on to talk about the details the Publisher-Subscriber model of replication, identifies the components of replication, and will give you a grasp of the fundamentals of the physical models of replication. Finally you will explore the three different types of replication and which situations in which you might consider using them.

Snapshot Replication: Chapters 4-7 involves the bulk copying of database objects from the Publisher to the Distributor server through the Snapshot Agent. This means that the data replicated is not done in real-time. Here you will learn to configure Snapshot Replication using the GUI method as well as using the Transact-SQL method.

Then you will examine the snapshots themselves. You will look at a lot of issues including where to put the snapshots, what kind of network you have, when the agent should be scheduled, and security considerations. Finally, you will look under the hood at Snapshot replication so as to better understand and interpret errors when they occur.

Transact Replication: Chapters 8-10 only replicates committed transactions from the Publisher server to the Subscriber server. Here the data is transmitted in real-time. While the Snapshot Agent is used for the initial synchronization, it is the transaction log that is used to transmit messages.

Again you begin with the use of the GUI to set things up and then learn how to work with the Transact-SQL method. Just like with the Snapshot method, you need to know how each of the components work in synch to make the transactional replication function smoothly.

Merge Replication: Chapters 11-14 involves the modifications of data at both the Publisher server and at multiple Subscriber servers. Using Merge Replication, there are two types of publications, download-only (client subscriptions) and standard articles (server subscriptions).

Again, the initial synchronization done by the Snapshot Agent and subsequent updates are pulled to download data via the internet. You will use both the GUI and T-SQL to configure your Merge replication, as well as examining the internals to get a better feel for what goes on behind the scenes.

Backup: Chapters 15-16 now looks at administering replicated databases first by backing up and then restoring them. Here you will see how to backup for all three types of replication, how to validate the replications, how to restore the various types of replications. To accomplish this you need to have a good understanding of the Simple, Full, and Bulk Logged recovery models that SQL Server supports as well as an understanding of how the transaction log works.

Optimizing: Chapters 17-19 now looks at optimization techniques for all three forms of replication. For Snapshot replication you will work with the System Monitor, SQL Server Profiler, and the Management Data Warehouse. For Transactional Replication you will trace without the SQL Server profiler, learn about the Database Engine Tuning Advisor, and learn about the use of tracer token, as well as other best practices.

For Merge Replication, you will set up the trace for merge replication, capture the events using T-SQL, and load the trace in a table on a separate database. You will examine the use of the Database Engine Tuning Advisor command line utility to perform tuning and also to make an exploratory analysis. You will also see how you can adjust the publication retention, and the different merge agent profiles you can use as well as best practices.

Heterogeneous Replication: Chapter 20 now looks at another form of replication; that of replication between two completely different SQL platforms. In this case, it is between SQL Server and Oracle Publisher server. Here you will see how to set up permissions and how to configure for both snapshot and transactional replication between Oracle and SQL Server.

Pro SQL Server 2008 Replication is well thought out and very logically presented. It takes each topic and walks you through in detail. It nicely highlights the steps that you will take and then explains them in detail. Throughout there are notes that highlight things to pay attention to as well as caution’s that could cause you problems.

Pro SQL Server 2008 Replication is a comprehensive guide for experienced users of SQL Server who want to learn about the different types of replication. It also is for those who use SQL Server 2005 and want to get up to speed with the new features of replication in SQL Server 2008. This book will benefit primarily novice and intermediate users of replication by introducing replication and the different processes and then building on the basics to develop and address the advanced features of replication. If you want to get a better understanding of the principles of replication then I can highly recommend this book.