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
Best practices for getting help on SQLServerCentral -- Jeff Moden
How to Post Performance Problems -- Gail Shaw
Nasty fast set-based string manipulation functions:
For splitting strings try DelimitedSplit8K or DelimitedSplit8K_LEAD (SQL Server 2012+)
To split strings based on patterns try PatternSplitCM
Need to clean or transform a string? try NGrams, PatExclude8K, PatReplace8K, DigitsOnlyEE, or Translate8K
I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code. -- Itzik Ben-Gan 2001