Archive for the ‘Replication’ Category

How to drop subscriptions in Transactional Replication?

Scenario: You are working on refreshing the staging environment by using the database backups from the Production environment. Prior to the database restores, you had removed the subscriptions and publications and then performed the database restores. You had then re-created the publications and tried to subscribe to them. You are greeted by the following error message:

SQL Server could not create a subscription for Subscriber “Subscription_InstanceName”
Cannot create the subscription because the subscription already exists in the subscription database. Only one subscription to the same publication is allowed in each subscription database. Drop the subscription and add it again if necessary. If the problem persists, replication metadata might be incorrect; see Books Online for troubleshooting information. Changed database context to ‘MCFIData’. (Microsoft SQL Server, Error: 14058)”

Replication Error

How can you resolve this issue? First of all, you already know that prior to the refresh, replication worked fine. Installing new patches or .NET Framework 4.5 or new or any other software will not solve your problem. Don’t mislead by others. Trust yourself. You will just need to find a way to remove the subscription that you thought you had removed prior to the refresh. Here’s the code that I had used to resolve this issue:

-- Run this on the Publisher
-- Replace the Publishing_DB, Publication_Name, Subscriber_InstanceName, Subscriber_DB accordingly
use Publishing_DB 
exec sp_dropsubscription @publication =N'Publication_Name', @subscriber = N'Subscriber_InstanceName', @article = N'all', @destination_db = N'Subscriber_DB'

Make sure you monitor your publishing instance for blocking while this code to remove the subscription is running and make assessments and kill the blockers if needed or permitted.

How to query the replication information in a minute or less?

What if you have just been assigned as the new DBA in company XYZ and you need to find out the transactional replication setup in a New York minute. What system procedures and query can you utilize to help you?
In the past, I have used the following two system procedures and query to help me find the replication setup information under a minute. I hope they can help you do the same:
This system store procedure can be executed on the publication database or any other databases in the publisher and it will return the information about the distributor, the distribution database, working directory, and the SQL Server Agent user account in addition to other useful distribution related information.

-- run it on the publisher

This system store procedure has to be executed on the publication database in the publisher and it will list the subscription information associated with the publications, articles, subscribers, and subscriptions.

-- run it against the publisher database

3. Querying the distribution database

USE Distribution -- replace this with your distribution database

SRV.srvname publication_Server  
, Articles.publisher_db 
, Publications.publication publication_name 
, SS.srvname subscription_server 
, Subscriptions.subscriber_db 
FROM MSArticles Articles  
JOIN MSpublications Publications ON Articles.publication_id = Publications.publication_id 
JOIN MSsubscriptions Subscriptions ON Publications.publication_id = Subscriptions.publication_id 
JOIN master..sysservers SS ON Subscriptions.subscriber_id = SS.srvid 
JOIN master..sysservers SRV ON SRV.srvid = Publications.publisher_id 
JOIN MSdistribution_agents DistricutionAgent ON DistricutionAgent.publisher_id = Publications.publisher_id  
     AND DistricutionAgent.subscriber_id = Subscriptions.subscriber_id 
ORDER BY publication_Server, Articles.publisher_db, publication_name 

You can then save these information in an Excel file for further analysis or recording keeping purposes.



How to enable replication agents to log to output files?

Yesterday, while troubleshooting a replication issue for one of the clients that we support, we have encountered a very annoying issue. In the Replication Monitor, the Log Reader Agent shows error. When you right-click on the Log Reader Agent and then select “View Details” with the expectation to see the error details, you are then shown with the error messages in the form of “Agent ‘Name of the Agent Job’ is retrying after an error. ### retries attempted. See agent job history in the Jobs folder for more details.” You had probably thinking to yourself: “Great! Now we are getting somewhere.” You then browse to the Job Activity Monitor then sort the jobs by Category and then right-click on the Log Reader Agent job that’s experiencing a replication error and then select “View History” to see the error. To your surprise, nothing shows. This is more annoying those DIRECTV commercials “What Cable Worse Than?”, where a man is bitten by a turtle in the park. To see the details of the replication errors, you will need to add an output file to the replication agent jobs. It is well documented in

-Output C:\Temp\OUTPUTFILE.txt -Outputverboselevel [0|1|2]
C:\Temp\OUTPUTFILE.txt is the output text file you wish to output to.
-Outputverboselevel specifies the output verbose level. For 0, SQL Server will only print the error messages. For 1, SQL Server prints all the progress report messages. For 2, which is the default, SQL Server will print all the error messages and progress report message. Usually, you will want to select 2 for your best interest in troubleshooting the replication error.

Implementation steps:
1. Identify the replication failure or possible failure from the Replication Monitor or from the alerts from your monitoring system such as Nagios, GFI, Logic Monitor, your proprietary monitoring system, and etc…
2. In SSMS, browse to the Job Activity Monitor. Sort the jobs by Category.
3. Right-click on the Replication Agent job(s) that had experienced an error. Then, select “Properties.” This applies to all Replication Agents.
4. Under “Select a page” section, click on “Steps.”
5. Under the “Job step list:” section, click on step 2 “Run agent.” Then, click the “Edit” button.
6. On the “Command:” textbox, scroll to the end of the command, then add the line “-Output C:\Temp\OUTPUTFILE.txt”

After you had captured the error details in the output file, you need to consider modifying the job(s) to remove the parameter added to output the text file for the replication agent job(s) as the output file can grow very quickly. If you wish to continue to log the error details in the output file, you will need to make sure you have enough disk space.

How to enable replication agents for logging to output files in SQL Server

What is Replication?

Replication is a form of high availability technology that’s similar to the publishing industry. You will hear and see terminologies such as Publishers, Subscribers, Distributors, Articles, Agents, and etc. Similar to Log Shipping and Database Mirroring, Replication is a per database rather than a per instance high availability technology. But, that’s where the similarities end. In Replication, you have more granular control over the specific database objects that will get copied or transferred over to the secondary database that’s located in a separate server across the street, across the river, or even across continents.

The common typical uses of replication can be:

  • Offload the reporting to the subscriber(s) to reduce the resource contentions on the OLTP servers.
  • Integrating and combining data from multiple sites or various geographically distant branch offices.
  • Integrating data from various database systems such as Oracle, MySQL, Sybase, PostgreSQL, and etc. or anything database system that can be communicated via OLE DB.
  • Increasing availability by replicating all the data on a server to another server so that in case the primary server fails, the applications and users can switch to the secondary server.

Let’s say your company is headquartered in New York City with branch offices throughout the countries in Europe, you wish to reduce the contention and reporting needs for the European offices from constantly querying the OLTP server that’s located in a top secret Data Center that’s located in New Jersey somewhat 30 minutes from Manhattan. What can you do?

Before we get too excited about traveling to London again and visiting all the branch offices throughout the European cities to setup all the required servers because you wish to work more closely with your colleagues in Europe, if the company approves, we need to first understand the terminologies, concepts, pre-requisites, setup requirements, and monitoring of replication before you book your flights and hotels for the long overdue road trips. Yes. You can complete the setup remotely. But, why not take this opportunity to have face time with your coworkers and work together as a team, check out the construction progress of the Shard, revisit the Buckingham Palace, and get your taste bud reacquainted with fish and chips and some of the great beer?

As we had stated from the beginning, Replication is similar to the publishing industry. It is essentially a process of copying and synchronizing data from the source database to the subscribing database(s) that are located in separate servers. It contains terminologies that you may or already very well familiar with:

  • Publisher: contains the source database(s).
  • Publication: the database objects to be published; it can be tables, views, and stored procedures.
  • Subscriber: the database that located in a separate server that will receive the publication changes.
  • Subscription: the process or method of receiving the publication.
  • Distributor: the server that delivers the publication to the subscriber(s). It can be on the same server as the Publisher. But, it is usually best for it to be located on a different remote server to reduce contentions.
  • Article: the tables, views, and stored procedures to be published.

Replication starts with the first or initial synchronization of the objects to be published from the Publisher to the Subscriber(s). Yes. You can have multiple Subscribers. The first or initial synchronization is called a snapshot. You would first define the articles to be published in the Publisher, then the snapshot (a point in time copy of the Publication) is created on the Publisher, and then the Distributor will deliver the snapshot to the Subscriber(s). When data is copied or transferred over to the Subscriber(s) from the Publisher, it is considered as synchronized. The process of synchronization can happen instantly or on a scheduled basis depending on the replication scenarios, type of replication, and the configurations.

The initial snapshot is stored in the snapshot folder of the Distribution server. The Distribution server can be located in the same server as the Publisher server or it can be located on a separate remote server. You must make sure you have sufficient disk space for the initial snapshot as it is the main reason for snapshot failures. In addition, you must make sure the snapshot agent account has permission to write to the snapshot folder.

There are 3 common replication scenarios:

  • Central Publisher: It is the most common form of replication. The setup involves with one Publisher, one Distributor that can be on the same server as the Publisher but preferably should be located in a different server, and one or more Subscriber(s).
  • Central Subscriber: The setup involves with multiple Publishers and one Subscriber. This setup is usually reserved for centralized reporting databases where you have multiple branch offices servers replicating to the centralized reporting database back in the headquarter office or at a specific location.
  • Publishing Subscriber: This is usually reserved for situations where there’s low-bandwidth between the Publisher and Subscriber. You can first publish to a subscriber, then the subscriber in turn distributes to others within its region where there’s higher bandwidth for network.

There are 4 types of replications that I will be exploring in the future blogs and articles. Here’s a brief overview of each:

  • Snapshot: provides the initial synchronization for all other types of publications (transactional, peer-to-peer, and merge). It involves copying the articles of the publication over to the subscriber(s) and the existing subscriber(s) will be overwritten. Usually reserved for small databases and for initial synchronizations.
  • Transactional: is the most common type of replication. Changes that occur on the Publisher can also applied on the subscriber(s) immediately.
  • Peer-to-Peer: is a type of transactional replication where the each of the servers involved in replication is both a Publisher and Subscriber usually for load-balancing proposes.
  • Merge: allows the Subscribers that need update to the same sets of data at various times then the changes will get sent back to the Publisher where the changes will get merged and then sent back to all other Subscribers.

When the Distribution server is created, a new database called the Distribution database will be automatically created. This database will be created on the Distribution server. It serves as the store-and forward database. It holds all transactions from the Publisher waiting to be distributed to the Subscribers. The transactions are held in this Distribution database until they are distributed to the subscribers successfully. At a specific time interval, the transactions are purged from the distribution database.

When you create a publication to be subscribed, you have the option of either having the data pushed to the subscribers or have the subscribers pull the data when they are needed. For the push subscriptions, the Distribution Agent, the agent that moves the transactions to the subscribers will be located on the Distribution server. For the pull subscriptions, the Distribution Agent, the agent that moves the transactions to the subscribers will be located on the Subscribers.

The Replication processes are maintained by the Replication agents. Each one has its core responsibilities in making Replication work and continues to function:

  • SQL Server Agent: all the agents perform the replication tasks via SQL Server Agent jobs. The SQL Server Agent manages these jobs.
  • Snapshot Agent: responsible for synchronization to make sure the Subscriber(s) have the same selected database schema and data as in the Publication(s) of the Publisher. After the process of synchronization, all updates are made through replication.
  • Log Reader Agent: moves transactions marked for replication from the published database to the distribution database.
  • Distribution Agent: moves transactions held in the distribution database out to the subscribers in push subscriptions. In pull subscriptions, the Distribution Agent runs on the Subscriber. Merge replication does not have a Distribution Agent. It has a Merge Agent instead.
  • Merge Agent: is utilized only for Merge replications. It moves and reconciles the data changes that occurred after the initial snapshot. It runs on the publication server for push subscriptions and on subscriber servers for pull subscriptions.
  • History Cleanup Agent: clears the agent history from the distribution database every 10 minutes by default.
  • Distribution Clean Agent: clears the replicated transactions from the distribution database every 72 hours by default.
  • Expired Subscription Cleanup Agent: removes the expired subscriptions from the published databases. Runs once per day.
  • Reinitialize Subscriptions Having Data Validation Failures Agents: detects the subscriptions that failed data validation and marks them for re-initialization. It is not scheduled by default and it has to be run manually. Yes. This can mean that you will likely need a new snapshot applied to the subscribers.
  • Replication Monitoring Refresher for Distribution Agent: refreshes the SQL Server Replication Monitor on a scheduled interval.
  • Replication Agents Checkup Agent: make sure the replication agents are logging history as it is critical to have the history for troubleshooting.

In the subsequent or future blogs and articles, I will explore and document the major replication types, how to monitor them, and other critical information that I need to know and use on a daily basis as a SQL Server DBA supporting replication.