High Level Transactional Replication Process Flow

I’ve posted this flow before on my BLOG but I wanted to include the stored procedure calls used by Transactional Replication.

http://blogs.msdn.com/chrissk/archive/2010/01/28/troubleshooting-transactional-replication-part-1.aspx
http://blogs.msdn.com/chrissk/archive/2010/01/29/slides-from-talk-i-gave-on-troubleshooting-transactional-replication.aspx

While troubleshooting Replication performance/latency we’ll try and identify which stored procedure executing from which Replication Agent has the highest IO or highest CPU usage. These usually are a great place to start the investigation.  To analyze the processes flow we use SQL Profiler and/or SQLDIAG and capture RPC:COMPLETED Events. We also add –OUTPUT tracking parameter to the Agent which writes a text file of each agent execution step along with a time stamp.

http://blogs.msdn.com/chrissk/archive/2010/01/05/walk-through-of-kb-article-312292-how-to-enable-replication-agents-for-logging-to-output-files-in-sql-server.aspx

Log Reader Agent (logread.exe) – Sequence of StepsLogReaderFlow
  1. Calls sp_MSadd_LogReader_History to write to MSLogReader_History – “Starting Agent”
  2. sp_MShelp_logreader_agentid – Obtain log reader agent specific information for that publication
  3. sp_MShelp_profile – Obtains profile information for the Log Reader
  4. MSadd_logreader_history to write MSlogreader_history – “Initializing”
  5. sp_MSget_last_transaction – determine where the log reader agent left off reading the log.
  6. Read the transaction log – sp_replcmds
  7. Processes the returned commands from the sp_replcmds in batches by calling sp_MSadd_repl_commands
  8. Marks this transaction as committed in distribution database by using sp_repldone procedure
  9. Adjusts the identity range if necessary and if you are using Automatic Identity Range Management y calling sp_MSpub_adjust_identity
  10. Calls sp_MSget_last_transaction to check the last transaction read and stored in MSReplication_transactions table
  11. When all transactions are read, LogRead.exe calls sp_MSAdd_logreader_history and writes message to MSLogReader_history “1 transactions with 9 commands were delivered”
  • Distribution Agent (distrib.exe) - Sequence of Steps
    1. master.db.sp_msget_jobstate – get the status of the job (if it is already started)
    2. sp_Msadd_distribution_history – MSDistribution_history – Starting agent
    3. sp_MSSubscription_Status – whether subscription has expired or the snapshot is ready
    4. sp_server_info- determines the collation
    5. sp_mshelp_subscriber_info – retrieve subscriber information
    6. sp_mshelp_subscription_agentid – determine the name of the distribution agent
    7. sp_Msadd_distribution_history – Initializing message – Msrepl_distribution_history
    8. sp_Msadd_distribution_history – Connecting to Subscriber - Msrepl_distribution_history
    9. so_datatype_info – to determine the data type mapping necessary to create the tracking table necessary for the Distribution agent
    10. sp_MScheck_subscribe on subscription database – verifies that SQL Server Agent account is in sysadmin and db_owner role in subscription database
    11. sp_mscreate_sub_tables on subscriber in subscription database – creates MSSusbcription_agents and MSreplication_subscriptions tables
    12. Sp_MSinit_Subscription_agent – updates the Subscription agent information on subscription database
    13. Retrieves transaction_timestamp and subscription_guid to determine what Distribution agent has already replicated to the Subscriber. Transaction_timestamp correlates to xact_seqno column in MSReplication_transactions table in distribution database. All values large than the xact_seqno will be replicated
    14. If we are doing initial sync, Distribution Agent calls sp_MSupdatelastsyncinfo which updates MSreplication_susbcriptions and MSSusbcription_agents table
    15. Starts to retrieve all transactions and their corresponding commands from MSReplication_transactions and MSreplication_commands table where transaction_timestamp column in subscription database < xact_seqno column in MSreplication_transactions table. Applies the transaction using sp_MS_get_repl_commands procedure
    16. Issues dynamic SQL to update the MSreplication_subscriptions table with the last delivered transaction ID
    17. sp_MSDistribution_history to write the MSrepl_distribution_history table with status message “nn transaction(S) with nn command(s) were delivered”

    To learn more about troubleshooting LogRead.exe and Distrib.exe check out:
    http://blogs.msdn.com/chrissk/archive/2009/05/25/transactional-replication-conversations.aspx

    Chris Skorlinski
    Microsoft SQL Server Escalation Services