DAT 412:Transactional DAT 412:Transactional Replication InternalsReplication Internals

Matt HollingsworthMatt Hollingsworth

Program ManagerProgram Manager

Microsoft CorporationMicrosoft Corporation

AgendaAgenda

• ReviewReview• General Agent OverviewGeneral Agent Overview• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

Transactional ReplicationTransactional Replication

• Snapshot Agent writes initial data to Snapshot ShareSnapshot Agent writes initial data to Snapshot Share• Publisher changes tracked and extracted by Log Reader Publisher changes tracked and extracted by Log Reader

AgentAgent• Distribution database (Distribution database (storestore) acts as reliable queue) acts as reliable queue• Distribution Agent Distribution Agent forwards forwards changes to Subscriberschanges to Subscribers

Publishingdatabase Subscribing

database

Log ReaderAgent

DistributionAgent

DBlog

DBlog

Distributiondatabase

Snapshot Agent

\\Snapshot Share

Transactional ReplicationTransactional ReplicationUpdating subscribersUpdating subscribers

Immediate Updating Immediate Updating SubscriberSubscriber

2PC, RPC2PC, RPC

PublisherPublisher

DistributorDistributor

Read-OnlyRead-OnlySubscriberSubscriber

Queued Updating Queued Updating SubscriberSubscriber

QueueQueue

NETWORKNETWORK

QueueQueue

Queue Reader AgentQueue Reader Agent

AgendaAgenda

• ReviewReview• General Agent OverviewGeneral Agent Overview• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

General Agent OverviewGeneral Agent Overview

• Agents implement the core functions of replicationAgents implement the core functions of replication• Initiated via SQLAgent or from command lineInitiated via SQLAgent or from command line• Maintain connections to repl server instancesMaintain connections to repl server instances

– Connection info supplied on agent command lineConnection info supplied on agent command line– Can use integrated or SQL Server securityCan use integrated or SQL Server security

• SQL Server security: SQL Server security: SecurityMode=0SecurityMode=0, login and password , login and password • Integrated security: Integrated security: Security Mode=1Security Mode=1, no login or password, no login or password

– LoginTimeOutLoginTimeOut and and QueryTimeOutQueryTimeOut configurable configurable

• Can be scheduled or run on demandCan be scheduled or run on demand– Continuous: sleeps for PollingInterval between loopsContinuous: sleeps for PollingInterval between loops

• Logging and HistoryLogging and History– HistoryVerboseLevel [1|2|3]HistoryVerboseLevel [1|2|3]– OutputVerboseLevel [0|1|2]OutputVerboseLevel [0|1|2]– Output Output – ErrorFileErrorFile

AgendaAgenda

• ReviewReview• General Agent PropertiesGeneral Agent Properties• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

Snapshot Agent AlgorithmSnapshot Agent Algorithm

• Connects to the Publisher and DistributorConnects to the Publisher and Distributor• For each article in the publicationFor each article in the publication

– Reads publisher schema metadataReads publisher schema metadata– Generates schema scripts into snapshot Generates schema scripts into snapshot

directory according to user article options directory according to user article options (.sch, .idx, .dri files)(.sch, .idx, .dri files)

– Bulk copies table data into snapshot directory Bulk copies table data into snapshot directory (.bcp files) according to user defined row and (.bcp files) according to user defined row and column filterscolumn filters

• Updates status for subscription Updates status for subscription to activeto active

Snapshot Agent ParametersSnapshot Agent Parameters

• MaxBcpThreadsMaxBcpThreads– Improves parallelism Improves parallelism

during BCP outduring BCP out

• Misc.Misc.– ReplicationTypeReplicationType =1 =1

(transactional)(transactional)– RowDelimiterRowDelimiter – FieldDelimiterFieldDelimiter

Snapshot Sync PropertiesSnapshot Sync Properties

• Controls how the snapshot is generated Controls how the snapshot is generated and appliedand applied

• sp_addpublicationsp_addpublication– @sync_method@sync_method • sets snapshot format (character or native)sets snapshot format (character or native)• sets snapshot concurrency behaviorsets snapshot concurrency behavior

– @immediate_sync@immediate_sync• true: subscriber can retrieve the initial data true: subscriber can retrieve the initial data

immediately after subscribingimmediately after subscribing• false: subscriber must wait for next snapshot to false: subscriber must wait for next snapshot to

synchronizesynchronize– @pre/post_snapshot_script@pre/post_snapshot_script• executes user defined custom scriptsexecutes user defined custom scripts

Snapshot Scripting PropertiesSnapshot Scripting Properties

How to configureHow to configure • sp_addarticle @pre_creation_cmd sp_addarticle @pre_creation_cmd

– none, delete, drop or truncatenone, delete, drop or truncate• sp_addarticle @schema_optionsp_addarticle @schema_option– 0x000x00 User specified creation script (User specified creation script (@creation_script@creation_script))– 0x010x01 Object creation Object creation – 0x020x02 Custom SP’sCustom SP’s– 0x100x10 Clustered indexesClustered indexes– 0x200x20 Convert UDT’s to base data typesConvert UDT’s to base data types– 0x400x40 Nonclustered indexesNonclustered indexes– 0x800x80 PK IndexesPK Indexes– 0x1000x100 TriggersTriggers– 0x2000x200 FK ConstraintsFK Constraints– 0x8000x800 DefaultsDefaults– 0x10000x1000 Column-level collationColumn-level collation– 0x20000x2000 Extended propertiesExtended properties– 0x40000x4000 UK ConstraintsUK Constraints– 0x80000x8000 PK ConstraintsPK Constraints

• UI: configure via article properties pageUI: configure via article properties page

AgendaAgenda

• ReviewReview• General Agent OverviewGeneral Agent Overview• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

Log Reader Agent AlgorithmLog Reader Agent Algorithm

• Connects to the publisher and distributorConnects to the publisher and distributor• Scans the publisher log, building a list of committed tx Scans the publisher log, building a list of committed tx

which are marked for replication = which are marked for replication = sp_replcmds.sp_replcmds.• For each command in the committed transactionsFor each command in the committed transactions

– For each publication receiving the commandFor each publication receiving the command• inserts data into the distributorinserts data into the distributor using the user specified article format using the user specified article format

and column filter = and column filter = sp_Msadd_repl_cmds27sp_Msadd_repl_cmds27 if the row matches the if the row matches the specified user row filterspecified user row filter

• Moves replication truncation mark in log = Moves replication truncation mark in log = sp_repldonesp_repldone• Commits transaction batch at distributorCommits transaction batch at distributor• Logs history messages into the distribution databaseLogs history messages into the distribution databaseNote: Log Reader must run before a concurrent snapshot is available for subscribersNote: Log Reader must run before a concurrent snapshot is available for subscribers

Log Reader Agent Log Reader Agent Profiler TraceProfiler Trace

demodemo

Log Reader Agent ParametersLog Reader Agent Parameters

• BatchingBatching– ReadBatchSizeReadBatchSize – max number of transactions read from – max number of transactions read from

publisher log per processing looppublisher log per processing loop• always honors a tx boundaryalways honors a tx boundary

– ReadBatchThresholdReadBatchThreshold – max number of commands read from the – max number of commands read from the publisher log per processing looppublisher log per processing loop• always honors a tx boundaryalways honors a tx boundary

– MaxCmdsInTranMaxCmdsInTran – max number of commands written to – max number of commands written to distributor as a transaction distributor as a transaction • Warning:Warning: forces a commit when the limit is reached. This may result forces a commit when the limit is reached. This may result

in transactional inconsistency at the subscriber in certain cases.in transactional inconsistency at the subscriber in certain cases.

• Other ParametersOther Parameters– PacketSizePacketSize– MessageIntervalMessageInterval

Command FormatsCommand Formats

• The Log Reader writes statements as The Log Reader writes statements as “commands” into the distributor “commands” into the distributor MSrepl_commandsMSrepl_commands table table

• The command format is defined by the user The command format is defined by the user as an article propertyas an article property

• These commands are applied directly to the These commands are applied directly to the subscriber by the distribution agentsubscriber by the distribution agent

• This flexibility is valuable for application This flexibility is valuable for application specific processing and performance tuningspecific processing and performance tuning

““None” Command FormatNone” Command Format

• Log Reader skips the specified command Log Reader skips the specified command type instead of inserting it into the type instead of inserting it into the distribution dbdistribution db

• When to useWhen to use– Great for auditing, use “None” for the delete Great for auditing, use “None” for the delete

commandcommand

• How to configureHow to configure – sp_addarticle @ins/upd/del_cmd = ‘None’sp_addarticle @ins/upd/del_cmd = ‘None’– UI: configure via article properties pageUI: configure via article properties page

““SQL” Command FormatSQL” Command Format

• Commands stored in distribution DB as SQL Commands stored in distribution DB as SQL inserts, updates, and deletesinserts, updates, and deletes

• When to useWhen to use– Heterogeneous subscribers are presentHeterogeneous subscribers are present– Lower distribution performance is acceptableLower distribution performance is acceptable

• Less SQL plan sharing occurs at the subscriberLess SQL plan sharing occurs at the subscriber

• How to configureHow to configure– sp_addarticle @ins/upd/del_cmdsp_addarticle @ins/upd/del_cmd = ‘SQL’= ‘SQL’– To include column names in insert statements, set To include column names in insert statements, set

@status = 8@status = 8– ODBC parameterization improves performance. Set ODBC parameterization improves performance. Set

@status = 16 or 24@status = 16 or 24– UI: configure via article properties pageUI: configure via article properties page

Custom Command FormatsCustom Command Formats

• Commands stored in distribution db as stored procedure Commands stored in distribution db as stored procedure calls = calls = sp_Msins/upd/del<article>sp_Msins/upd/del<article>

• When to useWhen to use– Highest performance distributionHighest performance distribution– Provides application flexibility since the stored procedure can Provides application flexibility since the stored procedure can

be customizedbe customized– No heterogeneous subscribers are presentNo heterogeneous subscribers are present

• How to configureHow to configure – Specify Specify @schema_option = 0x02@schema_option = 0x02 to generate to generate

subscriber side stored proceduressubscriber side stored procedures– Configure Configure @status = 16@status = 16 to improve performance via to improve performance via

parameterizationparameterization– UI: configure via article properties pageUI: configure via article properties page

‘‘CALL’ Command FormatCALL’ Command Format

• How SP parameters are specifiedHow SP parameters are specified– InsertsInserts

• all column valuesall column values– UpdatesUpdates

• initial pk column valuesinitial pk column values• all final column values for the updated rowall final column values for the updated row

– Delete parameters generatedDelete parameters generated• pk column valuespk column values

• When to useWhen to use– for highest insert and delete performancefor highest insert and delete performance– when custom subscriber logic doesn’t require the when custom subscriber logic doesn’t require the

values for columns in deleted rowsvalues for columns in deleted rows• How to configureHow to configure– sp_addarticle @ins/upd/del_cmdsp_addarticle @ins/upd/del_cmd = ‘CALL’= ‘CALL’– UI: configure via article properties pageUI: configure via article properties page

‘‘MCALL’ Command FormatMCALL’ Command Format

• How SP parameters are specifiedHow SP parameters are specified– InsertsInserts

• not supportednot supported

– UpdatesUpdates• initial pk column valuesinitial pk column values• final column values for the specific columns updated final column values for the specific columns updated • bitmask indicating which columns were updatedbitmask indicating which columns were updated

– DeletesDeletes• not supportednot supported

• When to useWhen to use– for highest update performancefor highest update performance– when custom subscriber logic doesn’t require the before values for when custom subscriber logic doesn’t require the before values for

columns columns

• How to configureHow to configure– sp_addarticle @ins/upd/del_cmd = ‘MCALL’sp_addarticle @ins/upd/del_cmd = ‘MCALL’– UI: configure via article properties pageUI: configure via article properties page

‘‘XCALL’ Command FormatXCALL’ Command Format

• How SP parameters are specifiedHow SP parameters are specified– InsertsInserts

• not supportednot supported

– UpdatesUpdates• all initial and final column valuesall initial and final column values

– DeletesDeletes• all initial column valuesall initial column values

• When to useWhen to use– For the most flexible subscriber side custom logic For the most flexible subscriber side custom logic

and transformsand transforms

• How to configureHow to configure– sp_addarticle @ins/upd/del_cmdsp_addarticle @ins/upd/del_cmd = ‘XCALL’= ‘XCALL’– UI: article properties pageUI: article properties page

Browsing Distributor Browsing Distributor Commands withCommands withsp_browsereplcmdssp_browsereplcmds

demodemo

User Trigger ConsiderationsUser Trigger Considerations

• Some publisher statements are applied as different sets Some publisher statements are applied as different sets of statements at the subscriberof statements at the subscriber

• Concurrent snapshot processing applies compensating Concurrent snapshot processing applies compensating commands after snapshot is delivered to subscribercommands after snapshot is delivered to subscriber

• By default, SQL Server logs updates to unique By default, SQL Server logs updates to unique constraints in replicated tables as as deletes followed by constraints in replicated tables as as deletes followed by insertsinserts– SQL Server 2000 SP1 added new dbcc option that converts SQL Server 2000 SP1 added new dbcc option that converts

single row unique constraint updates from delete/insert pairs single row unique constraint updates from delete/insert pairs back into single update statements for the subscriberback into single update statements for the subscriber

– This trace flag is not available for queued and immediate This trace flag is not available for queued and immediate updating subscribersupdating subscribers

– How to configureHow to configure• dbcc traceon(8207,-1)dbcc traceon(8207,-1)• Note:Note: updates to columns in unique keys that affect updates to columns in unique keys that affect multiplemultiple rows rows

are always applied to the subscriber as deletes followed by inserts. are always applied to the subscriber as deletes followed by inserts.

Stored Proc ConsiderationsStored Proc Considerations

• Two SP publishing methods exist:Two SP publishing methods exist:1.1. Replicate the rows affected by the SPReplicate the rows affected by the SP2.2. Replicate the execution of the SPReplicate the execution of the SP

• When to use When to use – Publish execution for SP’s that affect large numbers of rows. Publish execution for SP’s that affect large numbers of rows.

Provides significant performance improvement (10x).Provides significant performance improvement (10x).

• How to configureHow to configure – sp_addarticle @typesp_addarticle @type– value of value of ’proc exec’’proc exec’ replicates via RPC replicates via RPC– value of value of ’serializable proc exec’’serializable proc exec’ replicates via RPC if publisher replicates via RPC if publisher

call is within a serializable transactioncall is within a serializable transaction– other values will replicate the individual rows affected by the other values will replicate the individual rows affected by the

stored procedurestored procedure– UI: article properties pageUI: article properties page

View ConsiderationsView Considerations

• Standard Views Standard Views – Replicated as scripted schema object Replicated as scripted schema object

definitionsdefinitions• Requires publishing the underlying table(s)Requires publishing the underlying table(s)

• Indexed Views Indexed Views – Replicated as scripted schema object Replicated as scripted schema object

definitionsdefinitions– Replicated as a base object like a tableReplicated as a base object like a table• only the changes to the indexed view data are only the changes to the indexed view data are

replicatedreplicated• underlying table(s) do not need to be publishedunderlying table(s) do not need to be published

Text/Image Considerations Text/Image Considerations

• SizeSize– Server option Server option ‘max text repl size’‘max text repl size’ sets the sets the

largest text/image value on the replicated table largest text/image value on the replicated table – Data beyond the max size is not allowedData beyond the max size is not allowed• Error: Length of text data 1000 to be replicated Error: Length of text data 1000 to be replicated

exceeds configured maximum: 500." exceeds configured maximum: 500." • Text/Image Update Limitations with ReplText/Image Update Limitations with Repl– No updates during concurrent snapshotNo updates during concurrent snapshot– Repeatable read tx isolation level or greater Repeatable read tx isolation level or greater

required when using text pointersrequired when using text pointers– XCALL before image not supportedXCALL before image not supported– No updates at subscriber for text/imageNo updates at subscriber for text/image

Loopback ConsiderationsLoopback Considerations

• Includes originating server instance and database Includes originating server instance and database with each distributor command to prevent looping with each distributor command to prevent looping command distribution scenarioscommand distribution scenarios

• When to use When to use – Necessary for Bi-directional (reciprocal) topologies Necessary for Bi-directional (reciprocal) topologies

where each database publishes and subscribes to the where each database publishes and subscribes to the otherother

• How to ConfigureHow to Configure– sp_addsubscription @loopback_detectionsp_addsubscription @loopback_detection = ‘true’= ‘true’ for for

both subscriptionsboth subscriptions

Backup ConsiderationsBackup Considerations

• Publisher restoredPublisher restored to previous point-in-time to previous point-in-time– Problem: distributor and subscriber may contain rows later than Problem: distributor and subscriber may contain rows later than

restored database!restored database!– Solution: set “sync with backup” option on publisher to prevent Solution: set “sync with backup” option on publisher to prevent

commands from inserting into distributor until the log entries are commands from inserting into distributor until the log entries are backed upbacked up

– Side Effect: increases command distribution latencySide Effect: increases command distribution latency

• Distributor restoredDistributor restored to previous point-in-time to previous point-in-time– Problem: Distributor loses commands that have not been delivered to Problem: Distributor loses commands that have not been delivered to

subscribers when they are already truncated from publisher log!subscribers when they are already truncated from publisher log!– Solution: set “sync with backup” option on distributor to prevent Solution: set “sync with backup” option on distributor to prevent

publisher log truncation until the distributor has backed up entriespublisher log truncation until the distributor has backed up entries– Side Effect: may increase size of publisher logSide Effect: may increase size of publisher log

• Subscriber restoredSubscriber restored to previous point-in-time to previous point-in-time– No issues if within the distributor retention interval since transaction No issues if within the distributor retention interval since transaction

timestamp on subscriber is also restoredtimestamp on subscriber is also restored

AgendaAgenda

• ReviewReview• General Agent OverviewGeneral Agent Overview• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

Distribution Agent AlgorithmDistribution Agent Algorithm

• Connects to the distributor, subscriber, and Connects to the distributor, subscriber, and snapshot sharesnapshot share

• Applies scripts and bcp’s snapshot data into Applies scripts and bcp’s snapshot data into subscriber. Depends on status and subscriber. Depends on status and sp_addsubscription @sync_typesp_addsubscription @sync_type

• Gets next batch of commands for the subscription Gets next batch of commands for the subscription from the distributor MSrepl_commands table = from the distributor MSrepl_commands table = sp_msget_repl_commandssp_msget_repl_commands– Determines last applied xact id for subscription by Determines last applied xact id for subscription by

querying querying MSreplication_subscriptions MSreplication_subscriptions transaction_timestamptransaction_timestamp

• Applies the literal commands directly to the Applies the literal commands directly to the subscribersubscriber

• Commits the batchCommits the batch

Distribution Agent Distribution Agent Profiler TraceProfiler Trace

demodemo

Distribution Agent ParametersDistribution Agent Parameters

• Performance related parametersPerformance related parameters– BcpBatchSize BcpBatchSize – CommitBatchSize CommitBatchSize – CommitBatchThresholdCommitBatchThreshold– MaxBcpThreadsMaxBcpThreads– MaxDeliveredTransactionsMaxDeliveredTransactions– UseInprocLoaderUseInprocLoader

• Misc parametersMisc parameters– MessageIntervalMessageInterval– TransactionsPerHistoryTransactionsPerHistory– NoTextInitOnSyncNoTextInitOnSync– ProfileNameProfileName– DefinitionFileDefinitionFile– PublicationPublication– QuotedIdentifier [0|1|2]QuotedIdentifier [0|1|2]– Note: FTP parameters are obsoleteNote: FTP parameters are obsolete

Distribution Tuning TipsDistribution Tuning Tips

• Offload distribution agentOffload distribution agent load load– Configure pull subscriptionsConfigure pull subscriptions

• Tune databasesTune databases– Minimize indexes on subscriberMinimize indexes on subscriber– Tune I/O for subscriber and distributor: RAID 0+1 for data, Tune I/O for subscriber and distributor: RAID 0+1 for data,

separate logseparate log

• Increase BatchingIncrease Batching– Increase CommitBatchSize, CommitBatchThreshold, and Increase CommitBatchSize, CommitBatchThreshold, and

MaxBcpBatchSize parametersMaxBcpBatchSize parameters

• Increase agent parallelismIncrease agent parallelism– Use independent agent: Use independent agent: sp_addpublicationsp_addpublication

@independent_agent =’true’@independent_agent =’true’– Configure multiple publications for each subscriber according to Configure multiple publications for each subscriber according to

transactional requirementstransactional requirements– Increase MaxBcpThreads parametersIncrease MaxBcpThreads parameters

Scripting Replication Stored ProcsScripting Replication Stored Procs

• To manually generate subscriber replication procs To manually generate subscriber replication procs – exec sp_helparticle (to get article id)exec sp_helparticle (to get article id)– exec sp_scriptinsprocexec sp_scriptinsproc– exec sp_scriptupdprocexec sp_scriptupdproc– exec sp_scriptdelprocexec sp_scriptdelproc

• To manually generate queued conflict tables and To manually generate queued conflict tables and triggerstriggers– sp_makeconflicttablesp_makeconflicttable– sp_addsynctriggerssp_addsynctriggers

Scripting Replication Stored Procs(2)Scripting Replication Stored Procs(2)

• Useful for manually Useful for manually generating an update proc generating an update proc that is optimized for tables that is optimized for tables with large numbers of with large numbers of indexesindexes– Exec Sp_scriptdynupdprocExec Sp_scriptdynupdproc

Subscriber Procedure

Indexes on subscriber table Throughput, cmds/sec

Default MCALL 1 clustered index 1,570

1 clustered index, 10 nonclustered indexes

110

Dynamic MCALL 1 clustered index, 10 nonclustered indexes

1,180

AgendaAgenda

• ReviewReview• General Agent OverviewGeneral Agent Overview• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

Queued Subscriber Trigger LogicQueued Subscriber Trigger Logic

• SQL DML modification to subscription table SQL DML modification to subscription table occursoccurs

• Replication trigger fires and inserts data Replication trigger fires and inserts data into local subscriber queueinto local subscriber queue– Queue can be a SQL Server table Queue can be a SQL Server table

((msreplication_queuemsreplication_queue) or MSMQ) or MSMQ

• Queue Reader Agent will later collect these Queue Reader Agent will later collect these changes from the queue and apply to the changes from the queue and apply to the publisherpublisher

Queue Reader Agent AlgorithmQueue Reader Agent Algorithm

• Connects to the distributor, subscriber, and publisherConnects to the distributor, subscriber, and publisher• For each subscription serviced by the distributorFor each subscription serviced by the distributor

– Gets next batch of commands from the subscriber queue = Gets next batch of commands from the subscriber queue = MSreplication_queue MSreplication_queue via stored procedure = via stored procedure = sp_replsqlqgetrowssp_replsqlqgetrows

– Applies commands to the publisher table Applies commands to the publisher table sp_MSsync_ins/upd/del_<table>_#sp_MSsync_ins/upd/del_<table>_#

– Note: manages an internal thread pool to keep CPU’s busy and Note: manages an internal thread pool to keep CPU’s busy and increase parallelization. Groups approximately 1000 commands per increase parallelization. Groups approximately 1000 commands per batch.batch.

• Commits the publisher rowsCommits the publisher rows• Conflicts detected via msrepl_tran_version uniqueidentifier Conflicts detected via msrepl_tran_version uniqueidentifier

column differencescolumn differences• Note:Note: rangedranged updates (update foo set x=x+1) to unique key updates (update foo set x=x+1) to unique key

columns will fail at the publisher since they are applied as columns will fail at the publisher since they are applied as individual update statements.individual update statements.

Queue Reader Agent Queue Reader Agent Profiler TraceProfiler Trace

demodemo

Queue Reader Agent ParmsQueue Reader Agent Parms

• You can configure who wins in case of You can configure who wins in case of conflicting updatesconflicting updates

• ResolverState [1|2|3]ResolverState [1|2|3]1.1. Publisher wins conflictPublisher wins conflict2.2. Publisher wins conflict with reinitialization of Publisher wins conflict with reinitialization of

subscribersubscriber3.3. Subscriber overwrites existing Publisher Subscriber overwrites existing Publisher

valuesvalues

Administering the QueuesAdministering the Queues

• sp_getqueuedrowssp_getqueuedrows– Returns the Returns the transactionstransactions that are stored in the that are stored in the

queuequeue

• sp_replqueuemonitorsp_replqueuemonitor– Returns the Returns the commandscommands that are stored in the that are stored in the

queuequeue

• sp_setreplfailovermodesp_setreplfailovermode– Switches between immediate updating and Switches between immediate updating and

queued updating modesqueued updating modes

AgendaAgenda

• ReviewReview• General Agent OverviewGeneral Agent Overview• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

Distributor Cleanup AgentsDistributor Cleanup Agents

• Distribution CleanupDistribution Cleanup– Deletes rows from the distribution Msrepl_cmds and Deletes rows from the distribution Msrepl_cmds and

Msrepl_transactions table which are outside the distribution Msrepl_transactions table which are outside the distribution database retention perioddatabase retention period

– sp_adddistributiondb @max_distretention, sp_adddistributiondb @max_distretention, sp_MSdistribution_cleanupsp_MSdistribution_cleanup

• History and Logging CleanupHistory and Logging Cleanup– Deletes rows from each distributor agent history table which are Deletes rows from each distributor agent history table which are

outside the history retention periodoutside the history retention period– sp_adddistributiondb @max_historycleanup, sp_adddistributiondb @max_historycleanup,

sp_MShistory_cleanup @history_retentionsp_MShistory_cleanup @history_retention

• Expired Subscription CleanupExpired Subscription Cleanup– Removes subscriptions which have not synchronized within the Removes subscriptions which have not synchronized within the

publication retention periodpublication retention period– sp_addpublication @retention sp_addpublication @retention – sp_expired_subscription_cleanupsp_expired_subscription_cleanup

Other Replication AgentsOther Replication Agents

• Replication CheckupReplication Checkup– Raises error 14151 for all agents that have not logged Raises error 14151 for all agents that have not logged

messages within the specified intervalmessages within the specified interval – sp_replication_agent_checkup @heartbeat_intervalsp_replication_agent_checkup @heartbeat_interval

• Re-init Subs with Data Validation FailuresRe-init Subs with Data Validation Failures– Marks all subscriptions for re-initialization that have Marks all subscriptions for re-initialization that have

failed a validation checkfailed a validation check– sp_MSreinit_failed_subscriptionssp_MSreinit_failed_subscriptions

AgendaAgenda

• ReviewReview• General Agent OverviewGeneral Agent Overview• Snapshot AgentSnapshot Agent• Log Reader AgentLog Reader Agent• Distribution AgentDistribution Agent• Queue reader AgentQueue reader Agent• Other Replication AgentsOther Replication Agents• SummarySummary

Replication SummaryReplication Summary

• FastFast– Can deliver 2500 commands per secondCan deliver 2500 commands per second– Latency is often a few secondsLatency is often a few seconds

• FlexibleFlexible– Modular agent approach maximizes application Modular agent approach maximizes application

flexibilityflexibility– Stored procedures and DMO permit fine-Stored procedures and DMO permit fine-

grained controlgrained control

• ConsistentConsistent– Data is kept transactionally consistentData is kept transactionally consistent

Other ResourcesOther Resources

• DAT411DAT411 Programming and Deploying Programming and Deploying Microsoft SQL Server 2000 Microsoft SQL Server 2000 Replication: Lessons LearnedReplication: Lessons Learned  – Thursday 15:00 - 16:15, Room 3Thursday 15:00 - 16:15, Room 3

• Transactional Replication Performance Transactional Replication Performance Tuning whitepaperTuning whitepaper– http://www.microsoft.com/SQL/techinfo/adminishttp://www.microsoft.com/SQL/techinfo/adminis

tration/2000/ReplPerf.asptration/2000/ReplPerf.asp

Don’t forget to complete the Don’t forget to complete the on-line Session Feedback form on-line Session Feedback form on the Attendee Web siteon the Attendee Web site

https://web.mseventseurope.com/teched/https://web.mseventseurope.com/teched/

© 2002 Microsoft Corporation. All rights reserved.© 2002 Microsoft Corporation. All rights reserved.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.This presentation is for informational purposes only. Microsoft makes no warranties, express or implied, in this summary.