July 24, 2011 at 1:01 pm
Hello,
I am a relative newbie at replication although I've worked in SQL Server for several years. I am setting up replication for a client and am having a problem(s).
I am using Transactional replication between 2 machines A and B. They are running SQL Server 2008 R2 Standard Edition. They are not members of any domain. I have successfully (I think) configured everything and all accounts have the appropriate priveleges (I think).
The snapshot agent and log reader agent show "green" in replication monitor on the agents tab. The snapshot agent shows a status of Completed and a last action of "[100%] A snapshot of 1278 articles was generated". I look in the folder specified for the snapshot files and it is populated with files resembling table names, etc.
The log reader agent shows a status of Running and a last action of "No replicated transactions are available". So nothing ever gets replicated to the subscriber. I have Googled my brains out but can't find a solution.
Can someone help me out? The client is getting antsy and I need to get this resolved.
I probably left out some critical information so please let me know what else you need and I'll supply the missing information ASAP.
Thanks,
Brett
July 24, 2011 at 7:58 pm
Hi
You should read a bit about the architecture of replication.
I always think of it in 3's
Log reader agent, distribution, snapshot.
If there is nothing in the log for your specfic article then you won't get any new commands to distribute.
Try insert \ update into the table you published and you should see something happen.
Cheers
Jannie
July 24, 2011 at 8:02 pm
Hi Jannie,
Thanks for your response. The problem is that none of the objects (tables, stored procedures, etc.) are being created at the subscriber. It creates the new database but doesn't replicate any articles.
Brett
July 24, 2011 at 10:17 pm
Hi
When you setup the subscription did you click the initialize checkbox ?
You can do it by going on the menu options in replication and goto reinitialize subscription.
Note this will deleted your subscriber data depending on how replication was setup.
You basically need to initialize you subscriber once you setup the distributor. Then run the snapshot.
Then replication delivers your snapshot and then if there is transactions it will replicate them.
You can test the replication end to end by inserting tracer tokens, this can be done through replication monitor GUID.
Or some procs but I suggest using the GUI for now.
Cheers
Jannie
July 25, 2011 at 8:02 am
Hi Jannie,
I did check initialize. I'll try the tracer token. Will it work even though there are no tables, etc. replicated?
Brett
July 25, 2011 at 8:11 am
Hi brett
On the subscriber there will be subscriptions listed under replication in SSMS - check the view syncronisation status? what you are describing makes me suspect the snapshot isn't being downloaded (i'm assuming you select use ftp in the publication setup?)
HTH
July 25, 2011 at 9:48 am
Thanks HTH,
I'm not seeing anything on the subscriber side.
I don't understand why it's creating the database as part of the initialization of replication but not replicating anything.
Brett
July 25, 2011 at 10:08 am
Hi Brett
HTH = Hope that helps lol
if you are not seeing an subscriptions that is why the database is empty...normally you need to run a script on the publisher for the client subscriptions, then on the subscriber (usually in a database you have just created)
Add a subscription manually on the subscriber (SSMS -> replication ->right-click new subscription), connect to the publisher and step through the wizard, that will create the jobs needed and hopefully the missing tables once the snapshot has run through
Ellie
July 25, 2011 at 4:17 pm
Hi
No it will not work if no tables are replicated.
Think of replication as a small book publishing company.
In your example the publishers have set up the supply chain to be able to publish books but you haven't written any 🙂
You have to have articles in order for anything to replicate.
In bol there is a great outlay of replication architecture. Have a look and see if it answers more questions.
You have to replicate tables with PKs .
Cheers
Jannie
July 25, 2011 at 6:11 pm
Hi Jannie,
I know that if there are no tables, replication can't occur. That is my question -- why are the tables, etc. not replicating. The snapshot is getting generated but never making it to the subscriber.
Brett
July 25, 2011 at 7:10 pm
if replication is setup and you add a table to replication by going into the GUI and checking the box next to the article or running sp_addarticle. Once this is done you will need to run a snapshot which will apply the schema to the subscriber.
Once the schema is applied new transactions to the publisher will replicate to the subscriber.
You can also verify in the SQL agent jobs that all your jobs are in fact running.
Cheers
Jannie
July 25, 2011 at 7:10 pm
remove double post
July 25, 2011 at 7:11 pm
remove double post
November 16, 2012 at 4:58 pm
Hi All,
I am also facing same issue.
View Snapshot Agent Status-
[100%] A snapshot of 128 article(s) was generated.
View Log Reader Agent Status-
No replicated transactions are available.
Data is not replicated in the subscriber database from actual database. Though I am not able to see any errors.
Please help me out.
thanks all in advance.
April 9, 2014 at 12:00 am
The message "No replicated transactions available" is showing when you are checking the synchronization status and the sync job is always running once u start. This is happening because the distributor is running continuously. To avoid this just select the option distributor should run on demand only while creating the subscriber.
Hope this helps!!!!:-)
Thanks
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply