Main menu

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)

Image1

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”.

Images2

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”)

img3The 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”.

img4The 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”

 

Img5

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.

img6

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

img7

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.

img8

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

img10

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.

img11

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.

img12

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

img13

img14

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”

use Forest
go
sp_helppublication
go

img15

The above image shows that “immediate_sync” and “Allow_anaoymous” have been disabled now for current publication “ForestPub”

 

1 Comment

Leave a Reply

Your email address will not be published. Required fields are marked *