Contributed by: KD on November 28, 2015.

Replication Some Basic Troubleshooting

How many jobs get created when we create a new publication?

I am going to create a new publication “ForestPub”but before creating the publication, let’s verify what all the jobs are running on the Publisher server. (Please keep in mind that Publisher SQL server is also working as distributor SQL server)

The above snapshot proves that publisher server doesn’t have any publication and any publication related job too. So, I am going to create my new publication “ForestPub”.

The above snapshot proves that once we create a new publication, two jobs (Log Reader and Snapshot) get created on the publisher SQL server which refers to that publication.

How many jobs get created when we add new subscription?

I am going to add a new subscription to publication “ForestPub” but before adding the new subscription, let’s verify what all the jobs are running on the Publisher server as well as Subscriber SQL Server instances. (Please keep in mind I am going to select “Push Subscription”)

The above snapshot proves that publishers as well as subscriber SQL server instance don’t have any subscription and any subscription related job too. So, I am going to add my new subscription to the publication “ForestPub”.

The above snapshot shows that after adding new subscriptions to publication “ForestPub”, only one job got created but it was created on Publisher SQL Server instance because we have selected “Push Subscription” and Publisher SQL Server instance also works as distributor SQL server instance.

How to a new article in transnational Replication

Let’s create a new table under the “Forest” database on the Publisher server

use Forest
go
create table Foresttbl2
(
ProductID Int NOT NULL PRIMARY KEY,
ProductName nvarchar(50),
ProductDate Datetime
)
go

Let’s add the table as an article under Publication “ForestPub”

Let’s verify that does the table “Foresttbl2” exists on the Subscriber SQL Server instance or not? Below snapshot says, the table doesn’t exist on the Subscriber SQL Server instance.

Let’s run the “Snapshot agent” and see what snapshot agent monitor says

As per above snapshot, the snapshot agent should generate script for 1 (newly created article) article script instead of script for both the articles (Foresttbl1 & Foresttbl2). Let’s verify the files physical location too. We will see ahead how to generate script for only new added articles.

During initialization by snapshot agent how many physical files get created a UNC folder?

Let’s verify the “immediate_sync” and “Allow_anaoymous” of our current publication “ForestPub”, is it enabled or disabled?

use Forest
go
sp_helppublication
go

The above snapshot shows that “immediate_sync” and “Allow_anaoymous” are enabled for current publication “ForestPub”.

Let’s create one more table (Foresttbl3) on the publisher database

use Forest
go
create table Foresttbl3
(
ProductID Int NOT NULL PRIMARY KEY,
ProductName nvarchar(50),
ProductDate Datetime
)go

Let’s add the table (“Foresttbl3”) as an article.

Let’s make sure that newly created table is subscribed or not (means the article(s) has been initialized or not). As per below snapshot, the table is not subscribed because “Subscription status” shows 1.

Now, let’s run the “snapshot agent” to initialize the new article. But it actually initialized all 3 articles under “ForestPub” Let’s verify physical files too

Now, let’s disable the options “immediate_sync” and “Allow_anaoymous” for the “ForestPub” Please run the below commands;

--Run on your publisher database
EXEC sp_changepublication 
@publication = 'ForestPub', 
@property = 'allow_anonymous' , 
@value = 'false' 
GO 
EXEC sp_changepublication 
@publication = 'ForestPub', 
@property = 'immediate_sync' , 
@value = 'false' 
GO

Now, Let’s again verify the “immediate_sync” and “Allow_anaoymous” of our current publication “ForestPub”