Replication

REPLICATION

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Replication is intended to be a way of distributing data automatically from a source database to one or more recipient databases. As such, it can have obvious uses a distributed system. It has also been used to implement high availability system.

Advantages:

1. To allow sites work independently. So that each location can set up its own rules and procedures for working with its copy of the data.

2. To move data close to the user.

3. To reduce locking conflicts when multiple users are working.

4. Database replication can also supplement your disaster-recovery.

Note: Only committed transactions are transferred to the subscriber database.

          Support FILESTREAM.

Warm/ Hot Standby Solution:It provides a warm standby solution that has multiple copies of a database and require a manual failover.

There are three methods of Replication:

·         Snapshot Replication

·         Transactional Replication

·         Merge Replication

A publisher maintains the original copy of the data.  It holds the definition of the 'publication' which defines the 'articles' that are to be 'published'.

A 'Subscriber' receives the articles from a publisher.  It can subscribe to one or more publications.

Any database can take on either role or even both roles at once.

A Distributor is a specialist database that runs the 'Replication agents'

Replication Components (Entities):

·         Publisher

·         Publication

·         Article

·         Distributor

·         Subscriber

·         Subscription

ü Push Subscription

ü Pull Subscription

Publisher:

          It is the system that provides the data to be replicated and acts as a source database. It makes data available for replication. The publisher maintains information about which data is configured for replication.

Distributor:

          It is intermediary between publisher and subscriber. They store the distribution database, Meta data, historical data and transactions.

Publication:

          A group of articles is called publication.  It is made up of one or more articles. Publication is created in order to publish data to other systems.

Subscriber:

          They are the destination servers for replication.  They store the replicated data and receive updates.  Subscribers can also make changes to the data.  You can publish data to multiple subscribers.

Push Subscription:

          The Publisher is responsible for providing updates to the subscribers.  Updates are initiated without any request from the subscriber. It can be configured to keep the replication close to real time or to perform updates on a regular schedule.  It is created at the publisher server.

Pull Subscription:

          It allows synscroners to specify when the changes are propagated.  It is useful if there are large number of subscribers and when the subscribers are not connected to the network.  The Initiation of the replication is done on the subscriber side.

Articles Definition:

An Article is individual component of data that is to be replicated.  It could be an entire table or specific columns in a table or specific rows in a table or a stored procedure.

In short an article is a subset of a table that is being replicated.  Subsets are created by using filters.  A filter that is used to create subset of rows is called Horizontal Filter. 

A filter that is used to create a subset of columns is called Vertical filter.

Articles are basic building blocks of Replication.

Replication Agents:

·         Snapshot Agent

·         Distributor Agent

·         Merge Agent

·         Log Reader Agent

·         Queue Reader Agent

The following agents are associated with publications in Replication Monitor:

·         Snapshot Agent

·         Log Reader Agent

·         Queue Reader Agent

The following agents are associated with subscriptions in Replication Monitor:

·         Distribution Agent

·         Merge Agent

Replication Maintenance Jobs

• Replication uses the following jobs to perform scheduled and on-demand maintenance.

Clean up job	Description	Default schedule
Agent History Clean Up: Distribution	Removes replication agent history from the distribution database.	Runs every ten minutes
Distribution Clean Up: Distribution	Removes replicated transactions from the distribution database. Deactivates subscriptions that have not been synchronized within the maximum distribution retention period.	Runs every ten minutes
Expired Subscription Clean Up	Detects and removes expired subscriptions from publication databases.	Runs every day at 1:00 A.M.
Reinitialize Subscriptions Having Data Validation Failures	Detects all subscriptions that have data validation failures and marks them for reinitialization. The next time the Merge Agent or Distribution Agent runs, a new snapshot will be applied at the Subscribers.	No default schedule (not enabled by default).
Replication Agents Checkup	Detects replication agents that are not actively logging history. It writes to the Microsoft Windows event log if a job step fails.	Runs every ten minutes.
Replication monitoring refresher for distribution	Refreshes cached queries used by Replication Monitor..	Runs continuously.

  

SQL ServerReplication Latency

Replication latency is the amount of time it takes for a transaction that occurs in the primary database to be applied to the replicate database.

The time includes Replication Agent processing, Replication Server processing, and network usage. Replication latency appears on the path dashboard.

Latency is the time delay measure in the system.  

Sys.dm_io_pending_io_requests- View pending I/O request(s) in SQL Server.

sys.dm_io_virtual_file_stats      - Returns I/O statistics for Database data and log files. Measure IO transaction rates and sys.

Latency is the time it takes for the data to replicate at the subscriber once itupdates the primary.

Reason for latency 

1. Huge batch jobs. 
2. Blocking at replicate 
3. Replicate DB used intensively and it there is P/F impact to entire server. 
4. Network 
5. Secondary DB is full 
6. Stats are not dated frequently in replicate and causing the query to take different query plan than what was executed at primary

These columns display different values, depending on replication latency threshold, and can report one of the following:

·        Excellent: latency is 0–34 percent of the threshold. For example, if the latency threshold is 30 seconds, and transactions are delivered within 3 seconds, performance is excellent.

·        Good: Latency is 35–59 percent% of the threshold.

·        Fair: Latency is 60–84 percent of the threshold.

·        Poor: Latency is 85–99 percent of the threshold.

·        Critical: Latency exceeds the threshold.

There are multiple built-in system procedures that you can use to manage tracer tokens, some of which include:
- sp_posttracertoken (used to push a token into a publication)
- sp_helptracertokens (returns a row for each tracer token that has been inserted at a publisher for a given publication)
- sp_helptracertokenhistory (returns latency information for a given token id value retrieved from either of the procedures above)
- sp_deletetracertokenhistory (removes token records from the system meta tables)
we can directly accessing the LogReader and Distribution delivery latency by querying dynamic management view sys.dm_os_performance_counters.

1. Snapshot Agent:

         

The name of the Snapshot Agent executable is snapshot.exe. This agent usually resides in the Distributor server. The Snapshot Agent is used in all replications, particularly at the time of initial synchronization. It makes a copy of the schema and the data of the tables that are to be published, stores them in the snapshot file, and records information about synchronization in the distribution database

Each published database has its own snapshot agentthat runs on the distributor and connects to the publisher and takes a snapshot of the objects. 

For merge replication, a snapshot is generated every time the Snapshot Agent runs. For transactional replication, snapshot generation depends on the setting of the publication property immediate_sync. If the property is set to TRUE (the default when using the New Publication Wizard), a snapshot is generated every time the Snapshot Agent runs, and it can be applied to a Subscriber at any time. If the property is set to FALSE (the default when using sp_addpublication), the snapshot is generated only if a new subscription has been added since the last Snapshot Agent run; Subscribers must wait for the Snapshot Agent to complete before they can synchronize.

The Snapshot Agent performs the following steps:

1.    Establishes a connection from the Distributor to the Publisher, and then takes locks on published tables if necessary:

o    For merge publications, the Snapshot Agent does not take any locks.

o    For transactional publications, by default the Snapshot Agent take locks only during the initial phase of snapshot generation.

o    For snapshot publications, locks are held during the entire snapshot generation process.

2.    Writes a copy of the table schema for each article to a .sch file. If other database objects are published, such as indexes, constraints, stored procedures, views, user-defined functions, and so on, additional script files are generated.

3.    Copies the data from the published table at the Publisher and writes the data to the snapshot folder. The snapshot is generated as a set of bulk copy program (BCP) files.

4.    For snapshot and transactional publications, the Snapshot Agent appends rows to the MSrepl_commands and MSrepl_transactions tables in the distribution database. The entries in the MSrepl_commands table are commands indicating the location of .sch and .bcp files, any other snapshot files, and references to any pre- or post-snapshot scripts. The entries in the MSrepl_transactions table are commands relevant to synchronizing the Subscriber.

For merge publications, the Snapshot Agent performs additional steps. For more information, see How Merge Replication Initializes Publications and Subscriptions.

5.    Releases any locks on published tables.

During snapshot generation, you cannot make schema changes on published tables. After the snapshot files are generated, you can view them in the snapshot folder using Windows Explorer.

2. Distribution Agent:

         

The name of the Distribution Agent executable is distrib.exe. Both snapshot and transactional replication use this agent. The Distribution Agent is responsible for moving the snapshot and the transactions held in the distribution database to the subscribing servers. In the case of push subscriptions, the Distribution Agent resides on the Distributor server, In the case of pull Subscriptions; it resides on the Subscriber server.

For snapshot publications, each time the Distribution Agent runs for the publication, it moves a new snapshot to each Subscriber that has not yet been synchronized, has been marked for reinitialization, or includes new articles.

For snapshot and transactional replication, the Distribution Agent performs the following steps:

1.      Establishes a connection to the Distributor.

2.      Examines the MSrepl_commands and MSrepl_transactions tables in the distribution database on the Distributor. The agent reads the location of the snapshot files from the first table and Subscriber synchronization commands from both tables.

3.      Applies the schema and commands to the subscription database.

For an unfiltered merge replication publication, the Merge Agent performs the following steps:

1.      Establishes a connection to the Publisher.

2.      Examines the sysmergeschemachange table on the Publisher and determines whether there is a new snapshot that should be applied at the Subscriber.

3.      If a new snapshot is available, the Merge Agent applies to the subscription database the snapshot files from the location specified in sysmergeschemachange.

3. Merge Agent:

         

The name of the Merge Agent executable is replmerg.exe. This agent is used with merge replication. The Merge Agent applies the initial snapshot to the Subscriber servers. Incremental changes subsequent to the initial synchronization are monitored and merged to the Subscriber servers by the Merge Agent. The agent also resolves the update conflicts. Each of the databases taking part in the process has one Merge Agent. Like the Distribution Agent, the Merge Agent runs on the Distributor server in push subscriptions, and on the Subscriber server in the case of pull subscriptions.

In merge replication there is no distribution of snapshot agent involved.  Instead the merge agent communicates with both the publisher to the distributor.

There is one merge agent for each merge subscription.

4. Log Reader Agent:

The name of the Log Reader Agent executable is logread.exe. This agent is used in transactional replication. The Log Reader Agent monitors the transaction logs of all databases that are involved in transactional replication. The agent copies any changes in the data that are marked for replication in the transaction log of the publication database and sends them to the Distributor server where they are stored in the distribution database. The transactions are held there until they are ready to be sent to the Subscriber servers.

Each database that is using transaction replication has its own log reader agent on the publisher.

5. Queue Reader Agent:

The name of the Queue Reader Agent executable is qrdsvc.exe. In transactional replication, there is an option to either immediately update the messages or store them in a queue, using either the SQL Server queue or the Microsoft Messaging queue. If the updated messages need to be sent immediately, there needs to be a constant connection between the Publisher and the Subscriber servers. However, if you are going to store the messages in the queue, you do not need a constant connection; you can send the messages whenever the connection is available. In such cases, the Queue Reader Agent takes the messages from the queue and applies them to the publishing server. The Queue Reader Agent is multithreaded and runs on the Distributor server. There is only one instance of this agent for a given distribution database, and it services all the publications and Publisher servers.

Purpose of Replication

The purpose of this document is to show most skilled ways to replicate your database from one server to other and also to show the benefits of replication of database. The following advantages users can avail by using replication process.

Users working in different geographic locations can work with their local copy of data thus allowing greater independence.

Database replication can also supplement your disaster-recovery plans by duplicating the data from a local database server to a remote database server. If the primary server fails, your applications can switch to the replicated copy of the data and continue operations.

You can automatically back up a database by keeping a replica on a different computer. Unlike traditional backup methods that prevent users from getting access to a database during backup, replication allows you to continue making changes online.

You can replicate a database on additional network servers and reassign users to balance the loads across those servers. You can also give users who need constant access to a database their own replica, thereby reducing the total network traffic.

Database-replication logs the selected database transactions to a set of internal replication-management tables, which can then be synchronized to the source database. Database replication is different from file replication, which essentially copies files.

Replication Topology.

Central publisher/distributor, multiple subscribers.

Central Distributor, multiple publishers, multiple subscribers.

Central Distributer, multiple publishers, single subscriber.

Mixed Topology.

Replication Types:

1.      Snapshot Replication:

2.      Transactional Replication

3.      Merge Replication

1. Snapshot Replication

Snapshot replication makes a copy of the data and propagates changes for the whole set of data rather than individual transactions, thereby making it a discontinuous process and entailing a higher degree of latency. For example, suppose a bookstore chain offers discounts once or twice a year. The regional bookstores only need to be aware of the price changes occasionally, so you could use snapshot replication to transfer the changes from the head office to the regional bookstores. Using

Snapshot replication by itself is most appropriate when one or more of the following is true:

Data changes infrequently.

It is acceptable to have copies of data that are out of date with respect to the Publisher for a period of time.

You are replicating small volumes of data.

Many changes occur over a short period of time.

Snapshot replication is most appropriate when data changes are substantial but infrequent. For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended. Given certain types of data, more frequent snapshots might also be appropriate. For example, if a relatively small table is updated at the Publisher during the day, but some latency is acceptable, changes can be delivered nightly as a snapshot. Snapshot replication has a lower continuous overhead on the Publisher than transactional replication because incremental changes are not tracked. However, if the dataset set being replicated is very large, it will require substantial resources to generate and apply the snapshot. Consider the size of the entire data set and the frequency of changes to the data when evaluating whether to use snapshot replication. The below figure 1 shows how snapshot replication will works.

Work Model Steps:-

1. Publication Database Contains articles in publisher server.

2. Snapshot agent transfers articles from publication database and stores in snapshot folder.

3. Snapshot agent writes history data and adds rows in MSrepl_commands table in the Distribution database.

4. Distribution database transfers articles from Distribution database to Subscription database.

Snapshot replication takes the entire set of data and sends it during each cycle of the replication engine. By default, all three types of replication use a snapshot to initialize Subscribers.

It is also known as static replication

It copies and distributes data and database objects exactly as they appear at the current moment of time.

Scenarios:

·          When the data is not changing frequently.

·          If we want to replicate small amount of data.

·          To replicate look-up tables which are not changing frequently.

·          It is acceptable to have copies of data that are out of date with respect to the publisher for a period of time.

Snapshot replication and transactional replication use the Distribution Agent to deliver the files, whereas merge replication uses the SQL Server Merge Agent. The Snapshot Agent runs at the Distributor. The Distribution Agent and Merge Agent run at the Distributor for push subscriptions, or at Subscribers for pull subscriptions.

It uses Snapshot Agent and Distribution Agent

HOW IT WORKS:

Snapshot Agent

-Establishes a connection from the Distributor to the Publisher, and places locks on the tables during the entire snapshot generation process.

-Writes a copy of the table schema for each article to a .sch file. If other database objects are published, such as indexes, constraints, stored procedures, views, user-defined functions, and so on, additional script files are generated.

-Copies the data from the published table at the Publisher and writes the data to the snapshot folder. The snapshot is generated as a set of bulk copy program (BCP) files.

-For snapshot and transactional publications, the Snapshot Agent appends rows to the MSrepl_commands and MSrepl_transactions tables in the distribution database. The entries in the MSrepl_commands table are commands indicating the location of .sch and .bcp files, any other snapshot files, and references to any pre- or post-snapshot scripts. The entries in the MSrepl_transactions table are commands relevant to synchronizing the Subscriber.

-Releases any locks on published tables.

To configure publication:

sp_replictiondboption

sp_addpublication

sp_addpublication_snapshot

sp_addarticle

To configure Subscriptions:

sp_addsubscription

sp_addpullsubscription

2. Transaction Replication:

Transactional replication allows incremental changes to data to be transferred either continuously or at specific time intervals. Transactional replication is normally used where there is a high volume of inserts, updates, and deletes. This type of replication is usually used in a Server-to-server environment. For example, auto repair shops need to have real-time data about inventory in their warehouses and other shops. By using transactional replication across all stores, it is possible for each of the shops to know the current inventory, and stock shortages can be anticipated ahead of time. Transactional replication is typically used in server-to-server environments and is appropriate in each of the following cases:

• You want incremental changes to be propagated to Subscribers as they occur.
• The application requires low state between the time changes are made at the Publisher and the changes arrive at the Subscriber.
• The application requires access to intermediate data states. For example, if a row changes five times, transactional replication permits an application to respond to each change, not only the net data change to the row.
• The Publisher has a very high volume of insert, update, and delete activity.
• The Publisher or Subscriber is a non-SQL Server database, such as Oracle.

By default, Subscribers to transactional publication should be treated as read-only, because changes are not propagated back to the Publisher. However, transactional replication does offer options that permit updates at the Subscriber. The below figure 2 shows how snapshot replication will works

Work Model Steps:-

1. Transactions are written in the transaction log of publication database.

2. Log reader agent reads only committed transactions from publication database.

3. Log reader agent writes committed transactions in the distribution database.

4. Distribution agent reads from MSrepl_transactions.

5. Distribution agent transfers data to the subscription database.

It is also known as dynamic replication.

It is typically used in server-to-server environment.

Transactional replication begins with an initial snapshot being applied to the subscriber, to ensure that two databases are synchronized.

We can configure transaction replication with two optional modes:

          * Immediate updating subscription

          * Queued updating subscription

Truncate command will not work here.

Scenarios:

For database which changes regularly.

Subscribers always need the latest data for processing.

The application requires low latency between the time changes are made at the publisher and the changes arrive at the subscriber.

Types of publication for T Replication:

T Publication with updatable subscription

(It uses Snapshot Agent & Distribution Agent and Log Reader Agent)

T publication in a peer-to-peer topology.

(It uses Snapshot Agent & Distribution Agent and Log Reader Agent)

How it works:

The snapshot agent prepares files containing schema and data of published tables and database objects, stored the files in the snapshot folder, and records synchronization jobs in the distribution database on the distributor.

Log reader agent monitors transaction log of each database configured for T R and copies the transactions marked for replication from T L into MSrepl_transactions of distribution DB.

Distribution agent moves the initial snapshot and the transactions held in the distribution database tables to subscribers.

Note:

You can't implement transactional replication in either a bidirectional or peer to peer architecture due to data conflict.

Note: Do not replicate delete statement.

If we delete any records in the publisher it will not affect to subscriber server then we have to use above option in publisher properties->articles properties.

3. Merge Replication

Merge replication permits a higher degree of autonomy. It allows the subscribing servers to make changes and then it propagates those changes to the publishing servers, which in turn transfer the changes to other subscriber servers. For example, Sales people working in the field can enter their orders or changes once the transactions are complete. The updated data from different sales people can lead to conflicts, which can be resolved by setting up a conflict policy in merge replication. The Conflict Policy Viewer in SQL Server 2005 helps you track the conflicts. Point of sales applications, like sales force automation, are situations where you can use merge replication. Merge replication is typically used in server-to-client environments. Merge replication is appropriate in any of the following situations:

·         Multiple Subscribers might update the same data at various times and propagate those changes to the Publisher and to other Subscribers.

·         Subscribers need to receive data, make changes offline, and later synchronize changes with the Publisher and other Subscribers.

·         Each Subscriber requires a different partition of data.

·         Conflicts might occur. When they do, you need the ability to detect and resolve them.

·         The application requires net data change instead of access to intermediate data states. For example, if a row changes five times at a Subscriber before it synchronizes with a Publisher, the row will change only once at the Publisher, to the fifth value, to reflect the net data change.

Merge replication enables various sites to work autonomously and later merge updates into a single, uniform result. Because updates are made at more than one server, the same data might have been updated by the Publisher and by more than one Subscriber. Therefore, conflicts can occur when updates are merged. Merge replication provides several ways to handle conflicts.

Work Model Steps:-

1. Publication database contains articles in the publisher server.

2. Conflict tables, change- tracking tables, and triggers present in publication and subscription databases.

3. Merge agent transfers data.

4. Merge agent writes history in MSmerge_history table in the distribution database.

It provides the advantages of both snapshot and transactional replication.

Both publisher and subscriber can make changes.

Both publisher and subscriber can work without any active connection.

When they are connected, the merge replication agent checks for changes on both sets of data and modifies each database accordingly.

Truncate command will not work here.

Here both sides insert, update, delete triggers will be created

Note:

Replication can be configured to run in either a continuous or a schedule mode.

It uses Snapshot Agent Merge Agent

Scenarios:

Site autonomy is very critical

Multiple subscribers needs to change data either at the same time or at a different time and propagates changes to publisher.

Question: When setting Replication, is it possible to have a Publisher as 64 Bit SQL Server and Distributor or Subscribers as a 32 Bit SQL Server.

Yes it is possible to have various configurations in a Replication environment

Question: What the different Topologies in which Replication can be configured?

§  Replication can be configured in any topology depending keeping in view of the complexity and the workload of the entire Replication. It can be any of the following:

§  Publisher, Distributor and Subscriber on the same SQL Instance.

§  Publisher and Distributor on the same SQL Instance and Subscriber on a separate Instance.

§  Publisher, Distributor and Subscriber on individual SQL Instances.


Important Replication Tables:

Table	Discription
MSrepl_commands	The MSrepl_commands table contains rows of replicated commands. This table is stored in the distribution database.
MSrepl_errors	The MSrepl_errors table contains rows with extended Distribution Agent and Merge Agent failure information. This table is stored in the distribution database.
MSrepl_backup_lsns	The MSrepl_backup_lsns table contains transaction log sequence numbers (LSN) for supporting the 'sync with backup' option of the Distribution database. This table is stored in the distribution database.
MSrepl_identity_range	The MSrepl_identity_range table provides identity range management support. This table is stored in the publication, distribution and subscription databases
MSrepl_originators	The MSrepl_originators table contains one row for each updatable Subscriber from which the transaction originated. This table is stored in the distribution database.
MSreplication_queuedtraninfo	The MSreplication_queuedtraninfo table is used by the replication process to store information about the queued commands issued by all the queued updating subscriptions that are using SQL-based queued updating. This table is stored in the Subscription database.
MSrepl_transactions	The MSrepl_transactions table contains one row for each replicated transaction. This table is stored in the distribution database.
MSrepl_version	The MSrepl_version table contains one row with the current version of replication installed. This table is stored in the distribution database.
MSreplication_monitordata	The MSreplication_monitordata table contains cached data used by Replication Monitor, with one row for each monitored subscription. This table is stored in the distribution database.
MSreplication_objects	The MSreplication_objects table contains one row for each object that is associated with replication in the Subscriber database. This table is stored in the subscription database.
MSreplication_options	The MSreplication_options table stores metadata that is used internally by replication. This table is stored in the master database.
MSreplication_queue	The MSreplication_queue table is used by the replication process to store the queued commands issued by all the queued updating subscriptions that are using SQL-based queued. This table is stored in the subscription database.
MSreplication_subscriptions	The MSreplication_subscriptions table contains one row of replication information for each Distribution Agent servicing the local Subscriber database. This table is stored in the subscription database.
MSreplmonthresholdmetrics	The MSreplmonthresholdmetrics table defines the metrics provided for monitoring replication. This table is stored in the msdb database.
MSsnapshot_agents	The MSsnapshot_agents table contains one row for each Snapshot Agent associated with the local Distributor. This table is stored in the distribution database.
MSsnapshot_history	The MSsnapshot_history table contains history rows for the Snapshot Agents associated with the local Distributor. This table is stored in the distribution database.
MSsnapshotdeliveryprogress	The MSsnapshotdeliveryprogress table is used to track files that have been successfully delivered to the Subscriber when a snapshot is being applied. This data is used to resume the delivery of files in case the Merge Agent fails to deliver all of the files during the session so that the same files are not delivered again the next time that the Merge Agent is run. This table is stored at the Subscriber in the subscription database.
MSsub_identity_range	The MSsub_identity_range table provides identity range management support for subscriptions. This table is stored in the subscription databases.
MSsubscriber_info	The MSsubscriber_info table contains one row for each Publisher/Subscriber pair that is being pushed subscriptions from the local Distributor. This table is stored in the distribution database.
MSsubscriber_schedule	The MSsubscriber_schedule table contains default merge and transactional synchronization schedules for each Publisher/Subscriber pair. This table is stored in the distribution database.
MSsubscription_agents	The MSsubscription_agents table is used by Distribution Agent and triggers of updateable subscriptions to track subscription properties. This table is stored in the subscription database.
MSsubscription_articles	The MSsubscription_articles table contains information regarding the articles in a queued subscription. This table is populated only for the replication types of queued updating and immediate updating with queued updating as a failover
MSsubscription_properties	The MSsubscription_properties table contains rows for the parameter information required to run replication agents at the Subscriber. This table is stored in the subscription database at the Subscriber for a pull subscription or in the distribution database at the Distributor for a push subscription.
MSsubscriptions	The MSsubscriptions table contains one row for each published article in a subscription serviced by the local Distributor. This table is stored in the distribution database
MSsync_states	The MSsync_states table tracks which publication is still in concurrent snapshot mode. This table is stored in the distribution database.
MStracer_history	The MStracer_history table maintains a record of all tracer tokens that have been received at the Subscriber. This table is stored in the distribution database and is used by replication for performance monitoring
MStracer_tokens	The MStracer_tokens table maintains a record of tracer token records inserted into a publication. This table is stored in the distribution database and is used by replication for performance monitoring.
sysarticlecolumns	The sysarticlecolumns table contains one row for each table column that is published in a snapshot or transactional publication, and maps each column to its article. This table is stored in the publication database.
sysarticles	Contains a row for each article defined in the local database. This table is stored in the published database
sysarticleupdates	Contains one row for each article that supports immediate-updating subscriptions. This table is stored in the replicated database.