Solved

MS SQL replication

Posted on 2011-03-03
20
545 Views
Last Modified: 2012-06-27
We have a server A running SQL 2000 and we replicate data from Server A to server B (running SQL 2000 too) by transaction mode of MS SQL replication. We have application C that using data from A and application D that using data from B.
When the connection between A and B drops, the replication is stopped, C and D still work. However, when connection between A and B up again. The replication is reinitialized and it locks the data at B, so the D gets error.
Is there any settings for the replication that can help D to still use data at B while the replication is reinitializing? (We accept that data between A and B can be different at this time). Is there any third-party software can do the replication that can support this if the MS SQL is not able to?
0
Comment
Question by:tamthapluc
  • 9
  • 7
  • 2
20 Comments
 
LVL 19

Expert Comment

by:Rimvis
ID: 35053802
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?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35055385
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.

0
 

Author Comment

by:tamthapluc
ID: 35057835
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
0
 
LVL 19

Expert Comment

by:Rimvis
ID: 35058061
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
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35058168
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.
0
 

Author Comment

by:tamthapluc
ID: 35059400
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?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35059711
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35059781
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.
0
 

Author Comment

by:tamthapluc
ID: 35060831
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?
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 26

Expert Comment

by:Zberteoc
ID: 35061356
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35061371
Correction:

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

Author Comment

by:tamthapluc
ID: 35062387
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 ?
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35062465
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.
0
 

Author Comment

by:tamthapluc
ID: 35062721
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.
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35068625
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.
0
 

Accepted Solution

by:
tamthapluc earned 0 total points
ID: 35073122
To Zberteoc : Thank for your advises. I will test in some cases then feedback.
0
 

Author Closing Comment

by:tamthapluc
ID: 35174588
My problem is solved
0
 
LVL 26

Expert Comment

by:Zberteoc
ID: 35174984
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.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

The System Center Operations Manager 2012, known as SCOM, is a part of the Microsoft system center product that provides the user with infrastructure monitoring and application performance monitoring. SCOM monitors:   Windows or UNIX/LinuxNetwo…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now