SQL SERVER –Replication Q &A
What are different replication agents and what’s their purpose?
Snapshot Agent- The Snapshot Agent is used with all types of replication. It prepares the schema and the initial bulk copy files of published tables and other objects, stores the snapshot files, and records information about synchronization in the distribution database. The Snapshot Agent runs at the Distributor.
Log Reader Agent - The Log Reader Agent is used with transactional replication. It moves transactions marked for replication from the transaction log on the Publisher to the distribution database. Each database
published using transactional replication has its own Log Reader Agent that runs on the Distributor and connects to the Publisher (the Distributor can be on the same computer as the Publisher)Distribution Agent - The Distribution Agent is used with snapshot replication and transactional replication. It applies the initial snapshot to the Subscriber and moves transactions held in the distribution database to Subscribers. The Distribution Agent runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.
Merge Agent - The Merge Agent is used with merge replication. It applies the initial snapshot to the Subscriber and moves and reconciles incremental data changes that occur. Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both. The Merge Agent runs at either the Distributor for push subscriptions or the Subscriber for pull subscriptions.
Queue Reader Agent - The Queue Reader Agent is used with transactional replication with the queued updating option. The agent runs at the Distributor and moves changes made at the Subscriber back to the
Publisher. Unlike the Distribution Agent and the Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution database.
What is the difference between Push and Pull Subscription?
Push - As the name implies, a push subscription pushes data from publisher to the subscriber. Changes can be pushed to subscribers on demand, continuously, or on a scheduled basis.
Pull - As the name implies, a pull subscription requests changes from the Publisher. This allows the subscriber to pull data as needed. This is useful for disconnected machines such as notebook computers that are not always connected and when they connect they can pull the data.
Common Replication DMV's
sys.dm_repl_articles - Contains information about each article being published. It returns data from the database being published and returns a row for each object being published in each article.
sys.dm_repl_schemas – Contains information about each table and column being published. It returns data from the database being published and returns one row for each column in each object being published
sys.dm_repl_traninfo - Contains information about each transaction in a transactional replication
Queued Updating Conflict Detection and Resolution
Because queued updating subscriptions allow modifications to the same data at multiple locations, there may be conflicts when data is synchronized at the Publisher. Replication detects any conflicts when changes are synchronized with the Publisher and resolves those conflicts using the resolution policy you selected when creating the publication. The following conflicts can occur:
Update and insert conflicts. This conflict happens when the same data is changed at two locations. One change wins, and the other one loses.
Delete conflicts. This conflict occurs when the same row is deleted at one location and changed at the other.
Detecting Conflicts
When creating a publication and enabling queued updating, replication adds a uniqueidentifier column (msrepl_tran_version) with the default of newid() to the underlying table. When published data is changed at either the Publisher or the Subscriber, the row receives a new globally unique identifier (GUID) to indicate that a new row version exists. The Queue Reader Agent uses this column during synchronization to determine if a conflict exists.
Resolving Conflicts
When you create a publication using queued updating, you select a conflict resolver to be used if any conflicts are detected. The conflict resolver governs how the Queue Reader Agent handles different versions of the same row encountered during synchronization. You can change the conflict resolution policy after the publication is created as long as there are no subscriptions to the publication. The conflict resolver choices are the following:
Publisher wins (the default)
Publisher wins and the subscription is reinitialized
Subscriber wins
Conflicts are recorded and can be viewed using the Conflict Viewer.
The Conflict Viewer displays information from three system tables:
Replication creates a conflict table for each table in a merge article, with a name in the form MSmerge_conflict_<PublicationName>_<ArticleName>.
Conflict tables have the same structure as the tables on which they are based. A row in one of these tables consists of the losing version of a conflict row (the winning version of the row is in the actual user table).The MSmerge_conflicts_info table provides information about each conflict, including the conflict type.
The sysmergearticles table identifies which user tables have conflict tables and provides information about the conflict tables.
Use this query to find out the article that is out of sync:
USE [distribution]
select * from dbo.MSarticles where article_id IN ( SELECT Article_id from MSrepl_commands where xact_seqno = 0x0003BB0E000001DF000600000000)
You can also check the error on browsing MSrepl_errors table in distribution database.
USE [distribution]
select*From MSrepl_errors
To get the exact command using either SP_BROWSEREPLCMDS (If CLR is enabled) or you can cast the command column in msrepl_commands table.
EXEC SP_BROWSEREPLCMDS
@xact_seqno_start = '0x0000044100002D930001',
@xact_seqno_end = '0x0000044100002D930001',
@publisher_database_id = 1033,
@article_id = 12,
@command_id= 1
Add a Table in the Publisher and add that to Article
Question:Is there any other way to Replicate that single Table without ReInitializing the SnapShot Again
1) Run the following (with your database and publication name):
USE <Database>
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
2) Add newarticle to the publication using UI.
3) Right click on the publication. Select "View Snapshot Agent Status". Click on "Start" button.
This will generate snapshot for only the newly added articles
Add a Table in the Publisher and add that to Article
Question:Is there any other way to Replicate that single Table without ReInitializing the SnapShot Again
1) Run the following (with your database and publication name):
USE <Database>
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'allow_anonymous',
@value = 'false'
GO
EXEC sp_changepublication
@publication = 'MainPub',
@property = N'immediate_sync',
@value = 'false'
GO
2) Add newarticle to the publication using UI.
3) Right click on the publication. Select "View Snapshot Agent Status". Click on "Start" button.
This will generate snapshot for only the newly added articles.
Error:I am getting the following error:
Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated,or you do not have permission.
Solution:Check Database properties owner its should not blank .must value there like sa.
Better output use the Result to Text as output in SSMS
SELECT CAST(SUBSTRING(command, 7, 8000) AS NVARCHAR(MAX)) FROM msrepl_commands WHERE xact_seqno = 0x0000044100002D930001
AND command_id = 1
To disable SQL Server 2005 database replication
In the SQL Server 2005 SQL Server Management Studio console connected to the SQL Server hosting the site database, navigate to <SQL Server name> \ Replication.
Right-click Replication and click Disable Publishing and Distribution to launch the Disable Publishing and Distribution Wizard.
On the Disable Publishing and Distribution Wizard welcome page, click Next (if displayed).
On the Disable Publishing page, select Yes, disable publishing on this server. Disabling publishing on the server will:
Drop all publications on the server.
Drop all subscriptions to all publications on the server.
Disable the server as a distributor.
On the Confirm Dropping of Publications page, click Next.
On the Wizard Actions page, select Disable publishing and distribution and click Next.
On the Compete the Wizard page, click Finish.
On the Disabling Distribution page, review the progress as SQL Server disables publishing and distribution. Once publishing and distribution has successfully been disabled, click Close to exit the Disable Publishing and Distribution Wizard.
To delete local subscriptions to the replicated site database
In the SQL Server 2005 SQL Server Management Studio console connected to the SQL Server subscriber computer, navigate to <SQL Server name> \ Replication \ Local Subscriptions \ <site database replica subscription name>.
Right-click <site database replica subscription name> and then click Delete.
Click Yes to confirm subscription deletion when prompted.
Important Link:https://sites.google.com/site/saifsqlserverrecipes/sql-server-disable-replication