Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

No data showing up in a Database replication Expand / Collapse
Author
Message
Posted Thursday, December 13, 2012 10:06 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:40 PM
Points: 18, Visits: 141
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?

Post #1396281
Posted Thursday, December 13, 2012 10:57 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Yesterday @ 7:34 PM
Points: 555, Visits: 2,580
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.


-- Alan Burstein



Read this article for best practices on asking questions.
Need to split a string? Try this (Jeff Moden)
Need a pattern-based string spitter? Try this (Dwain Camps)
My blog
Post #1396308
Posted Tuesday, December 18, 2012 9:45 AM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 22, 2014 2:40 PM
Points: 18, Visits: 141
[b]
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 :)
Post #1397888
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse