December 28, 2011 at 10:57 am
Would someone please tell me which one of these statements is correct:
1. The Snapshot Agent is only necessary in Transactional Replication to do the initial setup (copying) of the data between Publisher and Subscriber and "turns itself off" after this inital setup is complete.
2. The Snapshot Agent in Transactional Replication needs to remain on at all times as periodic snapshots need to be taken in order to "compare the data" between Publisher and Subscriber.
I ask this because our Snapshot takes around 5 hours to complete because of the size of 1 specific table (63M rows), and I am a noob when it comes to replication.
December 28, 2011 at 11:28 am
How about neither, and both?
For the answer neither, you do not need to initialize a subscription with a snapshot. Do a search on this site for initialize replication wihtout a snapshot.
For the answer both, #1 is the "traditional" way of setting up transactional replication. Create the publication, create the subscription, initialize it with a snapshot. It's possible that the snapshot does not need to run again.
With #2, When setting up a publication, one of the options is to set a schedule to run the snapshot. It simply adds a schedule to the snapshot job.
You can certainly run this constantly, but typically this is used to re-sync the subscriptions on a regular basis.
In any case, the agent runs, and when the snapshot has been generated, it shuts down.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 28, 2011 at 12:55 pm
Thanks, Michael. I wasn't sure if the Snapshot needed to be run in order for the Publisher to be able to "compare" values in tables to values in the Subscriber tables.
I'll search this forum for "initialize replication wihtout a snapshot".
Edit: What I'm trying to do is push changes from 23 tables on server A to 23 tables on server B and vice versa. I was wondering if I need to keep constantly updating the Snapshot.
December 28, 2011 at 1:18 pm
Edit: What I'm trying to do is push changes from 23 tables on server A to 23 tables on server B and vice versa. I was wondering if I need to keep constantly updating the Snapshot.
Huh? it sounds as if you are not doing transactional replication, but merge replication. Which is a very different beast.
Can inserts/updates/and deletes be applied at both the subscriber and publisher?
Also, are you trying to compare rowcounts (or something? to determie if the replication is in sync?
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 29, 2011 at 8:03 am
Maybe I should explain that statement a little better. I would like to keep the 2 sets of data (23 tables, 1 set on server A and 1 set on server B) in sync with each other. Whenever a change (insert, update, delete) is made on one server, the same change is made on the other. If this is Merge Replication, please let me know.
Someone before me had set up Replication using "Transactional publication with updatable subscriptions" and it was working fine for a time, so that almost instantly the change was recorded on the other server. He has left and somehow the Replication has stopped working (and it isn't due to his login credentials).
I am doing something slightly different as our process has changed. Like I said, I am a noob at this and I thought I understood how Replication works. The description for Transactional Replication seemed to fit exactly what I wanted to do.
My predicament now seems like nothing is happening. All my Agents look fine, but the Queue Reader (in the Replication Monitor) is constantly showing the Last Action to be "No queued transaction available", even after I make a change.
Am I headed down the correct path or do I need to setup Merge Replication instead?
December 29, 2011 at 11:06 am
To re-state what I think you need, or want:
1. Server A, Table A, 10 rows get inserted. These rows then get replicated to Server B.
2. Server B, same Table A, 5 rows get inserted. These rows then get replicated back to Server A.
If that's correct, then "transactional publication with updatable subscriptions" is probably what you want. That being said, without a detailed set of requirements, I may not be correct. But, if it worked fine before, then it is probaly the correct way to go.
it also sounds as if you need to get a lot more learning under your belt, especially from how to administer replication.
Here is the MSDN link on the replication agents:
http://msdn.microsoft.com/en-us/library/ms152762.aspx
Technet:
http://technet.microsoft.com/en-us/library/ms151198.aspx
And another link:
http://www.replicationanswers.com/default.asp
When you say nothing is happening, the queue reader is not the place to start. The LOG reader agent will give you a better set of data to determine if replication is working.
The first question I have is did you try the lowest level test of replication by inserting or updating a row in a table on the publisher, and then checking the subscriber to see if your change has been made?
Merge replication is not a path to travel. It's likely that it will not work becasue of the database schema.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 29, 2011 at 12:08 pm
Your assumption is correct, for both 1 and 2, that's exactly what I want to do.
Thanks for the links, although I feel like I've read everything under the sun about the subject, I will definitely check them out.
Yes, I changed the value of a field in a table on the Publisher and nothing is being updated on the Subscriber.
The "All Subscriptions" tab in the Replication Monitor shows the Subscription "Running".
The Log Reader shows the Last Action as "Starting Agent" and it was started yesterday around 2pm.
I'm going to try to do a Replication test on a smaller scale just to get a bearing on how exactly this works.
Edit: btw, I also look at the 'distribution' database and it "appears" that my subscription, articles, publications, etc. are all there.
Michael, thanks for your help.
December 29, 2011 at 1:37 pm
It sounds af if the subscription was never initialized.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
December 29, 2011 at 2:48 pm
That's kinda what it sounds like. Almost as if the 'Start' button was never pressed. And I do realize there isn't really a 'Start' button.
Does the Log Reader Agent ever display another "Last Action" besides "Starting Agent"?
And I'm getting a Last Refresh every 4-5 seconds in the Repl Monitor that read "Retreiving data from Distributor" which in my simple brain tells me that the Repl Monitor is reading info from the Distributor.
Edit: Ah, this might be the issue here. I'm getting this error when I look at the tab "Distibutor to Subscriber History" : The concurrent snapshot for publication 'Replication_PROD' is not available because it has not been fully generated or the Log Reader Agent is not running to activate it. If generation of the concurrent snapshot was interrupted, the Snapshot Agent for the
January 3, 2012 at 7:56 am
SQLWannabe (12/29/2011)
That's kinda what it sounds like. Almost as if the 'Start' button was never pressed. And I do realize there isn't really a 'Start' button.
There sort of is a Start button. If, in SSMS, you go to Replication>Local Publications, right-click on your Publication and choose View Snapshot Agent Status, there will be a Start button that will generate your snap shot.
If everything else is set up correctly, that will create your Snapshot and push it out to Subscribers. If this is your initial Snapshot (can't tell from the post whether you're starting over from scratch or trying to revive the original Publication), you have a lot of data, and the Subscribers are on the WAN, this process can take a loooong time and you may want to look into Initializing by Backup (as opposed to Snapshot). If you need a new Snapshot b/c of a schema change to an existing Publication (e.g. you added an article) than the Snapshot generated should be differential and not take as long to propagate.
January 3, 2012 at 8:58 am
Steve and Michael, thanks for your replies and taking your valuable time to help a noob like me. I finally figured out what the problem was. I essentially deleted everything that was done before me, including the 'distribution' database. Once I re-created the whole Replication process from beginning to end, it appears everything is working as planned.
Steve, yes, I saw the supposed 'Start' button you are referring to. And have used it several times.
January 3, 2012 at 3:22 pm
Glad you got it running. I know how troubleshooting Replication issues can make for some long days.
January 4, 2012 at 8:38 am
No doubt. I will say that fighting thru all these issues has taught me quite a bit about how everything works and what to look for. Thanks again.
July 3, 2023 at 2:07 pm
I have a problem where I intend the following on a Transactional Replication with a Push subscription;
1 - 10 rows were inserted into Table 1 on Publisher. I want these 10 rows to be replicated in Table 1 in the Subscriber. However, I want these data to reach the distributor but not the subscriber immediately. Only when I need them every 15 minutes.
2 - How do I stop the distributor from sending the data immediately to the subscriber?
Where do I schedule a job if necessary in order to stop the distributor from sending the?
July 3, 2023 at 2:08 pm
I have a problem where I intend the following on a Transactional Replication with a Push subscription;
1 - 10 rows were inserted into Table 1 on Publisher. I want these 10 rows to be replicated in Table 1 in the Subscriber. However, I want these data to reach the distributor but not the subscriber immediately. Only when I need them every 15 minutes.
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply