December 13, 2012 at 10:06 am
I have a server 2008 R2 server were Database A is replicating to Database B. A full snapshot was done without any issues. I have the publication setup as Transactional Log with updates with the subscription pulling. This is running at night at 1am.
After the replication I go to a table in Database B (That I know has data in Database A) - I select the top 1000 rows to see any available data and the results show up blank. Database B;s size is 8GB ...............so is there anything I'm missing or doing wrong here?
Do I need to be just doing snapshot replication instead?
December 13, 2012 at 10:57 am
forceman29 (12/13/2012)
I have a server 2008 R2 server were Database A is replicating to Database B. A full snapshot was done without any issues. I have the publication setup as Transactional Log with updates with the subscription pulling. This is running at night at 1am.After the replication I go to a table in Database B (That I know has data in Database A) - I select the top 1000 rows to see any available data and the results show up blank. Database B;s size is 8GB ...............so is there anything I'm missing or doing wrong here?
Do I need to be just doing snapshot replication instead?
What you have made clear is: Transactional Replication existis between a table in Database A (which I will refer to as Table A) database and a table in databaser B (Table B). You have transactional replication pulling from Table A to Table B.
I lose you at, "After Replication".
If by, "after replication", you are saying "after the snapshot agent runs" you are getting blank records on Table B... If that's wha t you are saying then your snapshot agent is not completing successfully. You should stop your log reader agent, re-run the snapshot, then run your TOP 1000 row query to ensure that you get the correct results. If the snapshot agent is failing then so will the log reader agent.
If your snapshot is verified to be running successfully but, after the log reader comits a new record, you suddenly have a table full of blanks then that would suggest some sort of corruption. I had an issue once in 2008 where I was replicating a from one table to another and the subscriber data was showing up as Chinese-looking characters. To fix I:
1) Blew away table B
2) Recreated the publication and subscription with the CREATE TABLE IF IT DOES NOT EXIST option (Something like that - forgive me - I don't know the exact verbage, I don't have a server in front of me).
3) Re-ran the snapshot agent
4) Re-started the log reader agent.
Lastly, Re: "Do I need to be just doing snapshot replication instead? "
That all depends on your requirements. If you need up-to-the-minute data then Transactional Replication is for you. If the data does not need to be up-to-the-second/minute then you only need a periodic snapshot of Table A replicated to table B.
-- Itzik Ben-Gan 2001
December 18, 2012 at 9:45 am
What you have made clear is: Transactional Replication existis between a table in Database A (which I will refer to as Table A) database and a table in databaser B (Table B). You have transactional replication pulling from Table A to Table B.
I lose you at, "After Replication".
If by, "after replication", you are saying "after the snapshot agent runs" you are getting blank records on Table B... If that's wha t you are saying then your snapshot agent is not completing successfully. You should stop your log reader agent, re-run the snapshot, then run your TOP 1000 row query to ensure that you get the correct results. If the snapshot agent is failing then so will the log reader agent.
If your snapshot is verified to be running successfully but, after the log reader comits a new record, you suddenly have a table full of blanks then that would suggest some sort of corruption. I had an issue once in 2008 where I was replicating a from one table to another and the subscriber data was showing up as Chinese-looking characters. To fix I:
1) Blew away table B
2) Recreated the publication and subscription with the CREATE TABLE IF IT DOES NOT EXIST option (Something like that - forgive me - I don't know the exact verbage, I don't have a server in front of me).
3) Re-ran the snapshot agent
4) Re-started the log reader agent.
Lastly, Re: "Do I need to be just doing snapshot replication instead? "
That all depends on your requirements. If you need up-to-the-minute data then Transactional Replication is for you. If the data does not need to be up-to-the-second/minute then you only need a periodic snapshot of Table A replicated to table B.
Hey Alan,
Sorry for a long response. Things are starting to calm down at work. We just finished deploying a new cisco phone system across our 6 sites.
After some more reading on replications, watching video's and seeing your question for when I said "after "replication" it very well could be with the agents not running. Previously there was a replication setup (that broke) and I've been tasked to make a new one. Since who previously made this replication service is no longer here and no documentation was done i'm figuring it out on the fly.
When I made the new publication there was no agents created which leads to me it could be one of the agents that was previously created.
I've unfortunately been set back by a possible table restriction issue with the replication/snapshot process. Which is for a different thread.
Thank you for your help 🙂
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply