SQL Server 2005 Replication Learnings

Transaction replication notes from by Jose Barreto

#1.
Configuring Replication for Partitioned Tables Using T-SQL Here
MSDN link for Scripting Replication here
One more Replication Setup Script here

Which objects in the database are published?
<<Publish DB>>
SELECT * FROM sysarticles
SELECT * FROM syspublications

<<Distribution DB>>
Use Distribution
GO
SELECT * FROM distribution..mspublications

--Cleanup uncleaned subscriptions
delete from mspublications where publication_id=7
Note: You might have to delete from MSsubscriber_schedule as well. For me deleteting from mspublications fixed the issue.

<<SubscriberDB>>
USE <<SubscriptionDB>>
SELECT * FROM MSsubscriptions
SELECT * FROM MSsubscriber_info

#2.
While setting up transaction replication on a table that has millions of records. Initial snapshot would take time for the records to be delivered to subscriber. In SQL 2005 we have an option to create the tables on both transaction and publish server, populate dataset and setup replication on top of it. When you add subscription with command EXEC sp_addsubscription set The @sync_type = 'replication support only'. Example here

#3.
Replication - Difference between push and pull subscription
Distribution agent for push subscriptions typically runs on the Distributor. For pull subscriptions, the distribution agent typically runs on the Subscriber.

In a Pull Subscription
1. Distribution agent resides on subscriber instead of distributor because subscriber initiates replication request
2. Distributor sends data to distributor agent on subscriber
3. Replication by pull subscription has control at subscriber side instead of publisher

In a Push Subscription

1. Replication in push subscription has control in publisher, subscribers do not need to initiate replication request
2. Changes in publisher could be sent to distributor by demand, by schedule or continuously.

I found this link useful for highlighting above differences Thanks to the author.

Best Practice as suggested by msdn link here says pull subscriptions perform much better than push subscriptions in a WAN scenario.

Transaction Replication Deep Dive

Got the below error today "The row was not found at the Subscriber when applying the replicated command. (Source: MSSQLServer, Error number: 20598)".

Found workaround for this to be to skip this error at Distributor Agent. You can add the parameter to the distribution agent -SkipErrors 20598. Steps mentioned in Microsoft KB here

Replication Performance Tuning Guidelines

1. Performance counters to check for replication - SQLServer:Replication Dist.-Dist:Delivery Latency
2. Inserting Trace Tokens and check time taken to replicate data between publisher-distributor and subscriber

SQL 2008 R2 MSDN Replication Documentation

Merge Replication Step by Step
Troubleshooting SQL Server Transactional Replication
Log Reader Agent Fails with the Error “The Log Reader Agent failed to construct a replicated command from log sequence number (LSN)”
Customized Alerts for Transactional Replication
How to resolve when Distribution Database is growing huge (+25gig)
Using sp_repldone to mark all pending transactions as having been Replicated
Checking Replication Latency with T-SQL
Determine Transactional Replication workload to help resolve data latency
Troubleshooting LogReader Timeout executing sp_replcmds
How to cleanup Replication Bits
Distribution latency in transactional replication: Is a volume surge the culprit?
Troubleshooting Transactional Replication - PART 1
Troubleshooting Transactional Replication - PART 2
Troubleshooting Transactional Replication - PART 3
How to add an article to an existing Transactional Subscription initialized through backup
How to replicate some fields of two different tables?
Adding a column to Destination table
SQL 2005 Transaction Replication – Adding new article to an existing publication
Replication Agent has not logged a message in 10 minutes
SQL Server Replication Explorer
Divide and Conquer Transactional Replication using Tracer Tokens
All About Automatically Monitoring Replication Agent Failures
Replication features in various editions of SQL Server 2005/2008
Undocumented Gotchas of Transactional Replication

Happy Reading!!