Pro SQL Server 2005 Replication (Definitive Guide) (Hardcover)

by Sujoy Paul (Author)
Key Phrases: publication properties, replication agents, buffer manager, Cancel Figure, Merge Agent, Log Reader Agent (more...)

Editorial Reviews

Product Description

Replication is a way of copying and updating information in a database. This allows all users to see the same information and any changes made to it. Replication also duplicates the database across a number of computers so that, if one computer goes down, the information is not lost and can still be accessed. Pro SQL Server 2005 Replication, written by Sujoy Paul, a Microsoft Certified Professional for SQL Server, focuses on this most critical and complex of data management tasks.

This book is for Microsoft database developers and administrators who want to learn about the different types of replication and those best suited to SQL Server 2005. The book will also delve into advanced topics like replication of SQL Server with heterogeneous databases, replication of different database objects like stored procedures, backup and recovery of replicated databases, plus performance and tuning. This book is an ideal read whether youre a novice or advanced replication user.

About the Author
Sujoy Paul is a results-oriented database and software professional with extensive experience in different relational database management systems like MS SQL Server 2000, Sybase ASA, and Sybase ASE; data modeling; case-based tools like PowerDesigner; and coding and testing in demanding hi-tech environments. Sujoy’s background includes expertise in database replication, with special interest in two-way replication, backup and recovery, performance and tuning, and troubleshooting. Sujoy has a demonstrated ability to provide training for replication and Microsoft certifications on design and administration of SQL Server 2000. Sujoy is a Microsoft Certified Professional (installing, configuring, and administering Microsoft SQL Server 2000 Enterprise Edition).

He is the author or coauthor of dozens of technical papers, including "Replicating SQL Server 2000 across Heterogeneous Databases" (SQLTeam.com, August 2003).

Product Details

• Hardcover: 1016 pages
• Publisher: Apress (December 15, 2006)
• Language: English
• ISBN-10: 1590596501
• ISBN-13: 978-1590596500
• Product Dimensions: 9.4 x 7.2 x 2.2 inches
• Shipping Weight: 3.8 pounds
• Average Customer Review: 3.4 out of 5 stars See all reviews (10 customer reviews)
• Amazon.com Sales Rank: #624,697 in Books (See Bestsellers in Books)

Customer Reviews

Most Helpful Customer Reviews

31 of 33 people found the following review helpful:

2.0 out of 5 stars Should focus on what we don't know, April 3, 2007

By	J. Griffith (Portland, OR) - See all my reviews (REAL NAME)

I had a need... Bring up replication for a med-large Publishing DB (800GB).

I needed to configure, create test data, test several configurations, get a feel for performance and error handling, create the production scripts, prep the production environment, and turn it on.

The configuration we have setup involved "nosync" continuous Transactional Replication due to the Subscriber database starting out as an empty DB. The subscriber only receives new replicated transactions and does not have "snapshot" synchronization.

I had no real prior experience, nor guidance other than MSDN, MS Knowledgebase (KB), and various web sources.

Then, I came across this book, took a cursory glance, and saw what seemed to be a rich explication of replication. What caught my eye, in particular, were the chapters entitled: "Internals of Transactional Replication." There was such a chapter for each type of Replication (Snapshot, Transactional and Merge). I had hoped these would provide enough information to sort out problems I encountered and make my way through the task more efficiently.

Those were my expectations, and I was disappointed. Here's why:

1. The assumption is that Replication is going to use immediate Snapshot Synchronization. I got to believe there's a substantial number of DB's that would like to use it in a 'nosync' situation, particularly with a situation of Large DB replicating to Small DB. BOL, KB, nor this book really discusses this situation. There are specific and significant steps you must take in this situation. I was required to discover them through trial and error.

If you follow the directions given, you'll be forced to use Synchronization to initialize the Subscriber for Replication and this cannot happen in many situations.

Suggestion: provide a section for some types of "non-standard" database replication topologies and a summary of the differences. "Standard" topology, in this case, is where the Publisher and Subscriber are completely synchronized by an initial Snapshot Synchronization prior to replication transactions being distributed to the Subscriber.

2. This book is nearly 1000 pages. You'd expect a great deal of insight in that size of a book. I found little. What I did find was page after page of SQL Server Replication Wizard screenshots. I don't know about you, but I get pretty familiar with those wizards in the course of using them. They don't add very much value in a book like this.

Suggestion: Please don't provide out-of-the-box Wizard screenshots. If there is a situation that an admin will likely stumble over, then great! A snapshot of just that screen, pointing out what should be done is appreciated.

Suggestion: If you use diagrams (I love them!) provide more diagrams at a level like those in Chapter 3 that illustrate "how things work" by sequencing the replication types. Include some more non-standard ones perhaps in the Internals chapters.

3. The chapters entitled: "Internals of <Replication Type>" include ER-like figures. I say "ER-like" in that they are very inconsistent. In some cases there several Entity boxes (UML form) with the columns listed in a pattern in the figure. There are no relationship connectors, while it is clear there *are* relationships there. You're left to sort that out on your own. Effectively, what is added value is a listing of tables that are used for a specific form of replication. NOTE, *some* of the diagrams (Figure 10-29) have the relationships, but the Standard Transactional Publication figures (10-55 through 10-71) show almost no relationships. These were the ones I needed.

Suggestion: Create ER diagrams with the Entity names and their relationships (THESE ARE HELPFUL!). Don't try to list every column in the entity. If there are specific entities for which the column names are to be discussed, use separate figure(s).


4. Many of the non-wizard screenshots throughout chapter 10, the chapter I needed, were very blurry. I cannot even read some of them. I've looked at multiple copies of the book and they are the same. This is an issue with APress, not the author. This seems pretty unacceptable. Yuck.

Suggestion: proof *the entire book* before press...! :-)


5. Is it necessary (for even novices) to have 25% of the book showing Wizards and T-SQL for configuration? There are 6 chapters, 2 for each of the replication types entitled: "Configuring <Replication Type> Using the GUI" and "Configuring <Replication Type> Using T-SQL"

What's needed is:

- A conceptual understanding of what you're trying to achieve (terminology, roles, responsibilities, sequence of events) and I think you do this. This should provide enough information to identify the pattern of implementation you require.

- Next describe the procedures (tables, diagrams, enumerations) with the essence of how to implement the pattern. Look at the Table of Contents for "Configuring Transactional Replication using T-SQL" and you see the types of sequences that should be illustrated (not with Wizard GUI's). Then you have more room for non-standard configurations such as 'no-sync'. Specifically in this regard, nowhere is it discussed the correct procedure for generating the stored procedures and establishing them in the no-sync case.

6. Please focus on the "gotchas" what are the common mistakes you'll make and how do you rectify them. I believe the list is large and available, and it would be a great help to compile a succinct list/solutions.

7. More discussion is required regarding errors you may find in the Replication and what to do with them. There are a standard set of errors you may run into, and standard methods to deal with them, how to investigate MSrepl_errors, etc. You're not alone. There really is no great source for help in this regard.

10 of 12 people found the following review helpful:

3.0 out of 5 stars Lots of screenshots, lots of material from BOL, March 18, 2007

By	Andrey Smirnov (Moscow, Russia) - See all my reviews (REAL NAME)

The book contains lots of info from BOL + lots of screenshots which show step by steps details of some replication related actions. And BOL material + screenshots - its practically entire book. I think for some DBAs it will be good book, but I somewhat disappointed.

2 of 2 people found the following review helpful:

4.0 out of 5 stars Great for a Novice, May 18, 2008

By	Maxim Masiutin (Chisinau, Republic of Moldova) - See all my reviews (TOP 1000 REVIEWER)    (REAL NAME)

I have been involved in administering a database working on several geographically-separated servers. Initially, we've been using IPSec protocol for connections between the servers. To connect the clients, we've been using stunnel, which is easier to manage and to route. We were planning to stop using IPSec and moving to stunnel entirely. But I had no idea how to make Microsoft SQL Server 2005 to synchronize using stunnel. Microsoft had no ready-to-use solution for this. Thanks to this book, I have found the answer. Although this book does not explicitly state how to use stunnel, but it had enough clues to make me able to figure out the answer. For our company, this book was worth its price.

Anyway, this book is for an entry-level administrator of the SQL Server 2005. It contains the most basic information, with lots of screenshots and explanations. This overall information is quite easy to understand. If you want something specific, for example a solution for a rare or a complicated problem, this book is not for you, you would better to go to a specified forum. But if you are a novice in replication, I highly recommend this book.