MS SQL replication

Hi tamthapluc,

When replication is being reinitialized, tables at subscriber are rewritten in bulk insert mode. So there is no way you could access data at this time.

For what time connection is lost between publisher and subscriber? Is replication reinitialized automatically or manually?

The question actually is why do you need to reinitialize or why the "connection drops and replication is stopped"? Something is wrong here, you set up a replication for good with no need to reinitialize, this way your apps will work just fine.

If for some reason you need to reinitialize the replication frequently then something is wrong with the design or with the system.

Maybe you can explain what is going on so that we can help.

Hi all

Thanks for your comments

Just let you know that I’m new guy in this area so maybe what I configured is not perfect. So I need your helps

To Rimvis : The replication is reinitialized manually because I do not know how to set it automatically. Another reason for the manually is that I can reinitialize manually at night so that it does not interrupt my application D which provides services for my customers at day. Do you have any advice for my case?

To Zberteoc : The only reason that I need to reinitialize is that A and B is replicated through the WAN connection (it is the leased line between Houston and Austin) and this connection sometimes drop in some seconds. I  just setup the replication by following the wizard of MS SQL 2000 then when connection drops , the replication is stopped.  It is sure that I do not want to reinitialize the replication for any reason except the connection drops and this is out of my control. Please tell me if you need more information

Khoa

You don't have to reinitialize replication, if connection drops. You just jave to restart failed agent at distributor. By the way, what error do you get? Check agent history

To access replication agent (BTW, I'n not sure which agent is failing, I don't have SQL2K5 to check this):
In Enterprise Manager, go to you server, then "Replication Monitor"->Agents

First as Rimvis said there is no need for reinitialization if the connection drops. Second, if the connection resumes then you should be fine as the replication takes over from where it was left. Third, if there is no need of immediate sync between th e2 servers you could set up the frequency of the distribution agent to a schedula of every hour or every 2 hours or depending how often needed.

The idea is that you set up the replication with a sync interval that you need and then you don't have to worry about connection if it comes back. The connection is needed only while the distributor tries to access the publisher to read and the subscription to write. If the distribution database is on the publisher(push) then the only connection will be to the subscriber. There is no need to reinitialize unless you do changes to the replicated objects.

To Rimvis : How can I restart the failed agent ? Below is what I do:
I go to Enterprise Manager -> Replication Monitor -> Agents then I see the error on Distribution Agents. When I go to Distribution Agents, I see the fail on the Publication that we are talking about. I check the agent history then it says: The synchronize is stopped because of the network failure. I right click on the agent then I see the option “Start Agent” besides other options like: Error detail, Agent history, …
When I click to start Agent then nothing happen except the error changes to: “ The subscription have been marked inactive and must be reinitialized. NoSync subscriptions need to be dropped and recreated”.
Can you give me some advice?

To Zberteoc :
I need to setup the replication so that they sync immediately between A and B whenever A has some changes. It is required for my business. How can I setup so that the replication automatically takes over when the connection drops and resumes again? Maybe I is not clear about your ideas so can you clarify again for me?
At my case, I use the Publisher and the Distributor by two different servers and they are at the same LAN, so I do not worry about connection between them.  Only the connection between the Distributor and the Subcriber drops. Can you show me how to setup the replication with a sync interval? It is still a better option and I think I need it. However, B cannot get updates from A in real time in this case, right?

What you need is to set up a schedule for the replication. It is simply a step in the wizard. In the adding a subscription wizard at the step "Set Distribution Agent Schedule" instead of choosing "Continuously" chose the second option "Use the following schedule" and then click on Change button next to it. There you will set the schedule exactly like in the case for a job. You will choose Daily and then every 5min, 10min, or 6hr or whatever interval works for you(by default is 1hr i think). At the end you will generate the snapshot by starting the snapshot agent then you can start the distribution agent manually if you want or just wait for the interval to kick in.

This way the distribution agent will only run at the pre-established interval and if the connection is down will report an error but you will not have to reinitialize. Next time when executed will just work fine if the connection restored.

The problem with this setting is that the subscriber will be behind the publisher in terms of up to date data but normally this should not be a big problem. We use this setup for all our replications with intervals varying from every 5min to every 6hr or even once a day.

If you already have a replication in place you can also go to the distribution agent job on the distributor server for this publication and change the schedule there from continuously to the desired interval. This way you don't have to reset the subscriber.

You just find the distribution job on the distributor server. In the ER under the Server name > Management > SQL Server Agent right click on the job > Properties > Schedule and change it.

To Zberteoc : After I setup a schedule for the replication (every 5 minutes for example), if the connection drops and resumes again, the distributor server will automatically resumes the replication. It does not do the reinitialize process, right? However, when it does the resume replication process, does it lock the database at B as it does in the reinitialize process?

So, you don't need reinitialization, that is for sure. Replication after initialization is simply a matter of running some jobs. The most important one is the distribution agent job. It will read the source log file and connect to the source and apply the changes as they happened on the source. One thing though, you have to make sure the identity property and the foreign key constraints are not transmitted to the subscriber because they are controlled at the publisher. But these are all publisher properties and they can be configured at the creation time. But normally these these settings are defaulted like that.

About locking, it will be done at row level only for the short  period when it does the update on a particular table. I asume that your applications are both read only because if not you can't keep the databases in sync only with transactional replication.

Correction:

"It will read the source log file and connect to the target and apply the changes as they happened on the source."

To Zberteoc :Finally, is there any way for the Distibutor Server to resume the replication ( without reinitialize ) when the connection drops and resumes in "Continuously" mode ?

I am not sure but you said that the replication broke when that happened in this case. The only way to make sure is to try again and see what happens with the replication, does it resume or not.

There is no specific setting for this case that is for sure and for any kind of schedule for that matter. It is more about how that "permanent" schedule works for a job. If the job can resume after a network glitch then it will work, if not then it won't.

It is possible that in "permanent" mode the job will fail and it will never restart. For periodic schedule the job is fired at every interval so if one fire fails the next one could work if everything is back in place.

To Zberteoc :
1. I will try what your suggest about "Distribution Agent Schedule" to see if Distibutor Server resumes the replication ( without reinitialize ) when the connection drops and resume then let you know.
2. About "Continuously" mode, it happened many times and the replication does not resume when the connection droped and resumed until I reinitialized manually as I mentioned. The errors are what I showed before (“ The subscription have been marked inactive and must be reinitialized. NoSync subscriptions need to be dropped and recreated”). That is reason why I created this article.

Make a schedule every 5 min or even less. If the interval is smaller the sync will be more actual and the amount of data to be replicated is smaller so faster to apply. Also the probability to hit a connection glitch is bigger but if it resumes it will not be to big of an issue.

Do you know why the connection drips? Maybe you can address that problem.

My problem is solved

So you accepted your own answer here, which is what?!!

Was our time wasted in this case? At least some information about what the solution was.