Transaction replication is looks not working.
-
Hi, I Just joined to company and give me to bunch of SQL Servers to do admin.
I noticed there is a PRD DB has configured transactional replication Server A to Server B. Distribution also configured on Server A.
Now when look Server A replication - > local Subscribers -> view synchronization status -> Monitor -> Under the "Agent"--> "View Details" tag say following error.'\\XXXX\ABCD\\Subcriber\20170629155048\' is denied.
I am not sure This replication is working properly. I just create sample table in Server A and checked on Server B. Nothing has replicated.
1.What would be action plan to sole this
2. Can I reconfigure again? What steps to remove current configuration
Thanks
ashwan
-
Hello,
The first thing you need to do is understand if snapshot replication is in use. As you can see there are two methods of replication in the list.
From the screenshot you have provided you can see that the log reader agent is running fine located under the snapshot agent.
Typically when people install replication they leave the snapshot agent there without disabling the agent job, thus you get permission issues.
I wouldn't rush into making any changes and confirm with someone else in the office if you use snapshot agent at all.
-
Also replication works from articals, unless you tell replication how to replicate the data it's not going to do anything with a new table.
-
You have transactional replication set up since you have a log reader agent. The specific error you are posting is a permissions error on the snapshot folder. That folder is what is used to initialize publications and store the snapshots. And you can reinitialize using a new snapshot so the new snapshots goes to that folder. So you will always see the snapshot agent as well as the log reader agent with transactional replication. The log reader agent is what gets the data to be replicated to the distribution database.
But you have other issues in there as well. You'll likely need that to start figuring out the replication, what's published where, type, status, etc.
The server you took with the screenshot with is the subscriber. If you select that publication you point to and right click on it and select properties, you can get more information about the publisher, the subscriber and the snapshot folder location. In that same window of the properties for the publication, you can go down to the Replication Access list and that tells you which accounts are involved in your replication. And then if you go down one more to Agent Security, there is a screen where you can check each of the agents. It will tell you which accounts are used for the snapshot agent and the log reader agent.
Whatever account is used for the snapshot agent should be in the Replication access list. And then for the snapshot folder, that account needs full control to that folder.Sue
-
Hi Thank you for the valuable replay. Is there are way to alter articals for add new tables set to replicate.
- ashwan - Sunday, July 2, 2017 8:19 PM
You can add articles to a publication using sp_addarticle and then execute sp_refreshsubscriptions.
If you want to do this through SSMS, go to the publisher, to the Replication folder and expand local publications, right click on the publication you want to add articles to, select properties.
The publication properties window opens up and on the left, go down to articles. Remove the check mark for "Show only checked articles in the list". Then it will display all of the objects you can replicate from the database. Select the table or tables you want to add.
Open replication monitor, go to Agents, right click on the snapshot agent and select start. That should push out the updated snapshot of the table or tables you added as long as you didn't make any other changes to the existing articles or publication.Sue
-
My advice is spend a few hours understanding replication components, internals, and how it works. Folks on blogs will give you the answers your asking for, but without understanding it, you will never be able to support the product on your own.
Lots of things can go wrong with replication. Knowing the basics will make it a whole lot easier to troubleshoot.
-
Redgate has s free eBook on the fundamentals of SQL 2012 Replication:
MG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
-
for some reason the link didn't get inserted
http://www.red-gate.com/library/fundamentals-of-sql-server-2012-replication?utm_source=ssc&utm_medium=publink&utm_campaign=books&utm_content=replication_ebook&utm_term=FundamentalsofReplicationMG
"There are two ways of constructing a software design. One way is to make it so simple that there are obviously no deficiencies. And the other way is to make it so complicated that there are no obvious deficiencies."
Tony Hoare"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply