Have you ever wondered whether or not the problem you are facing with SQL Server was caused by replication? Our organization uses TFS Build to manage our databases. Just recently we had a situation where we attempted to restore a database and apply a “.dacpac” file to it. We discovered the database was set up for replication. The SQL Agent jobs were disabled, but the fact the database had been set up for replication was the issue.

That being said we had to write some scripts to basically check to see if a given server had been set up for replication and then remove the replication components from the database prior to applying the “.dacpac.”

In order to understand where replication is set up you need to know there are three databases impacted by replication. They are: the publisher database (the source of the data); the subscriber database (the receiver of the data); and the Distribution database (the manager of the data). Each has components associated with the process of replication. There are also SQL Agents associated with a given replication process. These agents are managed through the MSDB database.

You can actually use SQL Server Management Studio to see if replication is running on your server or you can use T-SQL.

Using Sql Server Management Studio

1. Open SSMS and connect to your instance.

2. Expand the Replication folder in the Object Explorer. If you see this folder structure you have replication running.

3. If you do not see anything under Local Publications, expand the Local Subscription folder to see if your server is a subscriber.

4. If neither of these folders show anything, look at the Sql Server Agent\Jobs folder. Click on the Job Activity Monitor and look for jobs with categories starting with “REPL.”

If none of these steps show anything you will need to use T-SQL to further investigate. The reason for this is that a remote publisher many be in control of replication and may have set up replication without using the Subscriber’s server directly.

Using T-SQL

In trying to identify if replication has been set up on your server the first place I would look is the Distribution database table. A query of the Publications table will tell you where to begin looking.

SELECT publisher_db, Publication, Description
 FROM distribution.dbo.mspublications

If you have an entry, you need to determine where the subscriber is located. Point to note: The publication name is the key between a publisher and a subscriber.

SELECT TOP 1 p.publication, s.publisher_db, s.subscriber_db
 FROM distribution.dbo.mssubscriptions s
  JOIN distribution.dbo.mspublications p ON p.publisher_id=s.publisher_id
 WHERE p.publication = 'AdventureWorks_Trans'

Servers may be found through this query:

SELECT Publisher, Subscriber
 FROM Distribution.dbo.MSsubscriber_info

Although I have shown only one publication and subscription in these examples, a given server and database may have several publications and any given publication may have multiple subscribers. These examples should help you through the investigation process to determine where your data is moving.