|
|
You're in the
sql server replication
group:Can merge replication be used to keep two database in sync
sql server replication:
be able to keep both databases in sync? We have two databases (a primary and a secondary) Regular users use the primary database. To keep the secondary data in sync with the primary, I have a job running everyday to copy and replace the tables and data in the secondary database. Assuming the primary database is offline and users use the secondary. And users update/add to the secondary database. Now, the primary database is back on line. How should I plan to sync the secondary database data with the primary? Can it be done by merge replication? Can merge replication be setup so if there is update in the primary, the secondary will be update. And when there is update in the secondary, the primary will be updated? What to setup so both copies of the database have the same data if I want them to have the same data all the time? Thanks,
Q,
there was a thread on this last week answered by me, Hilary and Mike Hotek called 'add a field'. This is a big topic and there are many angles. You might consider: transactional replication with queued updating subscribers, database mirroring and merge replication. Each has its pros and cons - largely covered in the thread mentioned above. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
You can add transactional with immediate updating, bi-directional
transactional, and peer-to-peer transactional since this question is a more general case and doesn't specify versions or editions. 200 - 300 pages later, you would still wind up with the answer of it depends. All of them will work, but it depends upon lots of things in your environment to be able to decide one way or the other. -- Mike http://www.solidqualitylearning.com Disclaimer: This communication is an original work and represents my sole views on the subject. It does not represent the views of any other person or entity either by inference or direct reference. [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:%23uWY0KUGGHA.3984@TK2MSFTNGP14.phx.gbl... > Q, > there was a thread on this last week answered by me, Hilary and Mike Hotek > called 'add a field'. This is a big topic and there are many angles. You > might consider: > transactional replication with queued updating subscribers, > database mirroring and > merge replication. > Each has its pros and cons - largely covered in the thread mentioned > above. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) >
Mike,
I would not include transactional with immediate updating in this mix. If the primary server is down, the poster wants to be able to use the secondary one, and the inability to do a 2PC will prevent any changes being made on the subscriber in this case. Cheers, Paul Ibison SQL Server MVP, www.replicationanswers.com (recommended sql server 2000 replication book: http://www.nwsu.com/0974973602p.html)
Further note that bi directional replication is not really scalable due to
no conflict mechanism; peer-to-peer is not scalable beyond 12-15 nodes. Updateable subscription types are best when the majority of DML occurs on the publisher. Updateable Subscriptions allow conflicts to be logged but not rolled back. Merge replication is scalable to 1,000 or subscribers, it has a rich conflict detection and resolution mechanism, and there is no restriction with where the majority of DML occurs. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message news:u%23sNLR0GGHA.1192@TK2MSFTNGP11.phx.gbl... > Mike, > I would not include transactional with immediate updating in this mix. If > the primary server is down, the poster wants to be able to use the > secondary one, and the inability to do a 2PC will prevent any changes > being made on the subscriber in this case. > Cheers, > Paul Ibison SQL Server MVP, www.replicationanswers.com > (recommended sql server 2000 replication book: > http://www.nwsu.com/0974973602p.html) > > > >
That's really baffling. Those are some pretty absolute numbers being thrown
around. How does conflict detection have anything at all to do with scalability? If I want to, I can build a bi-directional transactional configuration in a linear chain that has 1000 servers in it and make it work perfectly fine, so that blows your "can't scale" theory out of the water, because it can in fact be done. (Although I don't have an explicit business application for that.) How do you figure that peer-to-peer is not scalable beyond 12 - 15 nodes? Just what exactly was your test platform that gave you those explicit numbers. I'd really like to know, because my testing has taken it out to 30 nodes and everything was still working. I've done updating subscribers in configurations where not only where there dozens of subscribers, but over 90% of the changes occured on the subscribers. There is no such thing as rolling back from a conflict. A rollback occurs within a transaction space and causes changes to be undone before they are committed to the database. A conflict is thrown against committed and completely valid data in the database. The only thing that conflict resolution can do is produce a compensating transaction which is then applied, but it is still a completely separate transaction that modifies data. (It most certainly doesn't rollback.) Merge is scalable to 1000 subscribers? Really. How do you figure? I've had a merge architecture in place and running perfectly fine since SQL Server 7.0 that had over 10,000 subscribers in the architecture. I've also had merge architectures which couldn't scale beyond 5 subscribers. I really like these nice blanket statements being thrown out. Particularly since they have zero basis in reality. The reality of replication is that scalability has a direct correspondence to the volume of data per unit time that is being sent through the engine. It has ZERO correspondence to the number of subscribers or the pattern of modifications. When you say "does not scale beyond x", that means it plain and simply does not work if you try it. That is obviously a completely false statement, because you can ALWAYS get something to scale beyond x. Keep in mind there are a LOT of people out here reading this. When you say that a technology can't scale beyond X, then people are going to start looking for different tecnologies to apply, because according to you, the replication engine can't meet their business requirements. It also makes it really difficult for SQL Server DBAs to architect systems, because their managers point to your posts which say that SQL Server can't do what they are proposing to do and no amount of testing is going to change their minds once they've decided. So, how about giving the people who wrote the code for the replication engine a break and if you are going to post a scalability number, back it up with enough information to explicitly define the entire environment that drew that conclusion. -- Mike http://www.solidqualitylearning.com Disclaimer: This communication is an original work and represents my sole views on the subject. It does not represent the views of any other person or entity either by inference or direct reference. [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:eqHw513GGHA.1388@TK2MSFTNGP11.phx.gbl... > Further note that bi directional replication is not really scalable due to > no conflict mechanism; peer-to-peer is not scalable beyond 12-15 nodes. > Updateable subscription types are best when the majority of DML occurs on > the publisher. Updateable Subscriptions allow conflicts to be logged but > not rolled back. > > Merge replication is scalable to 1,000 or subscribers, it has a rich > conflict detection and resolution mechanism, and there is no restriction > with where the majority of DML occurs. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message > news:u%23sNLR0GGHA.1192@TK2MSFTNGP11.phx.gbl... >> Mike, >> I would not include transactional with immediate updating in this mix. If >> the primary server is down, the poster wants to be able to use the >> secondary one, and the inability to do a 2PC will prevent any changes >> being made on the subscriber in this case. >> Cheers, >> Paul Ibison SQL Server MVP, www.replicationanswers.com >> (recommended sql server 2000 replication book: >> http://www.nwsu.com/0974973602p.html) >> >> >> >> > >
Most of my figures come from a recent presenation that Phil Vaughn did at
Pass this year on replication. I'll listen to it again and verify these numbers. If I am incorrect I will post back here with the corrections. I'll also ping him to verify these quotes. Paul Ibison has a copy of the same presentation. While I have no doubt that you have built such systems let me quote from BOL In a section entitled Queued Updating - Queued updating is most appropriate for applications where users mostly read data and only occasionally update data. In a section entitled Immediate Updating - . Immediate updating benefits applications in which snapshot or transactional publications are preferred but occasional updates need to be made at the Subscriber. While BOL has occasionally being inaccurate, it is my belief and experience that it is completely correct here. When I say something is rolled back, I mean it in the same sense a transaction is rolled back and the system is left in the state is was in before. You can use the conflict viewer to "rollback" replication changes, or as they put it "keep the Wining Change", resubmit delete, insert, update. Note that in SQL 2000 you have an option to compensate for errors which had a default of false. In SQL 2005 it has a default of true. In other words conflicts will be logged but the changes will not win on the subscriber with this setting as false. As I have stated previously in this newsgroup Paul and I have a committment to accuracy and helping people with correct information. I trust you have the same committment. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Michael Hotek" <mike@solidqualitylearning.com> wrote in message news:OFKLzQ6GGHA.2696@TK2MSFTNGP14.phx.gbl... > That's really baffling. Those are some pretty absolute numbers being > thrown around. How does conflict detection have anything at all to do > with scalability? > > If I want to, I can build a bi-directional transactional configuration in > a linear chain that has 1000 servers in it and make it work perfectly > fine, so that blows your "can't scale" theory out of the water, because it > can in fact be done. (Although I don't have an explicit business > application for that.) > > How do you figure that peer-to-peer is not scalable beyond 12 - 15 nodes? > Just what exactly was your test platform that gave you those explicit > numbers. I'd really like to know, because my testing has taken it out to > 30 nodes and everything was still working. > > I've done updating subscribers in configurations where not only where > there dozens of subscribers, but over 90% of the changes occured on the > subscribers. > > There is no such thing as rolling back from a conflict. A rollback occurs > within a transaction space and causes changes to be undone before they are > committed to the database. A conflict is thrown against committed and > completely valid data in the database. The only thing that conflict > resolution can do is produce a compensating transaction which is then > applied, but it is still a completely separate transaction that modifies > data. (It most certainly doesn't rollback.) > > Merge is scalable to 1000 subscribers? Really. How do you figure? I've > had a merge architecture in place and running perfectly fine since SQL > Server 7.0 that had over 10,000 subscribers in the architecture. I've > also had merge architectures which couldn't scale beyond 5 subscribers. > > I really like these nice blanket statements being thrown out. > Particularly since they have zero basis in reality. The reality of > replication is that scalability has a direct correspondence to the volume > of data per unit time that is being sent through the engine. It has ZERO > correspondence to the number of subscribers or the pattern of > modifications. When you say "does not scale beyond x", that means it > plain and simply does not work if you try it. That is obviously a > completely false statement, because you can ALWAYS get something to scale > beyond x. > > Keep in mind there are a LOT of people out here reading this. When you > say that a technology can't scale beyond X, then people are going to start > looking for different tecnologies to apply, because according to you, the > replication engine can't meet their business requirements. It also makes > it really difficult for SQL Server DBAs to architect systems, because > their managers point to your posts which say that SQL Server can't do what > they are proposing to do and no amount of testing is going to change their > minds once they've decided. So, how about giving the people who wrote the > code for the replication engine a break and if you are going to post a > scalability number, back it up with enough information to explicitly > define the entire environment that drew that conclusion. > > -- > Mike > http://www.solidqualitylearning.com > Disclaimer: This communication is an original work and represents my sole > views on the subject. It does not represent the views of any other person > or entity either by inference or direct reference. > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:eqHw513GGHA.1388@TK2MSFTNGP11.phx.gbl... >> Further note that bi directional replication is not really scalable due >> to no conflict mechanism; peer-to-peer is not scalable beyond 12-15 >> nodes. Updateable subscription types are best when the majority of DML >> occurs on the publisher. Updateable Subscriptions allow conflicts to be >> logged but not rolled back. >> >> Merge replication is scalable to 1,000 or subscribers, it has a rich >> conflict detection and resolution mechanism, and there is no restriction >> with where the majority of DML occurs. >> >> -- >> Hilary Cotter >> Looking for a SQL Server replication book? >> http://www.nwsu.com/0974973602.html >> >> Looking for a FAQ on Indexing Services/SQL FTS >> http://www.indexserverfaq.com >> >> "Paul Ibison" <Paul.Ibison@Pygmalion.Com> wrote in message >> news:u%23sNLR0GGHA.1192@TK2MSFTNGP11.phx.gbl... >>> Mike, >>> I would not include transactional with immediate updating in this mix. >>> If the primary server is down, the poster wants to be able to use the >>> secondary one, and the inability to do a 2PC will prevent any changes >>> being made on the subscriber in this case. >>> Cheers, >>> Paul Ibison SQL Server MVP, www.replicationanswers.com >>> (recommended sql server 2000 replication book: >>> http://www.nwsu.com/0974973602p.html) >>> >>> >>> >>> >> >> > >
The presentation is #336 - SQL Server 2005 Replication: Lesson's learned
from Early Adopters, in a slide entitled Peer to Peer Topology, in response to an inaudible question, Phil has this to say "Realistically speaking when once you get to about 10-12, you start sending around so many changes you get to a point of diminishing returns, but about 10-12 nodes is where it peaks out, cause all changes flow everywhere." The transcription is mine. You can order this cd from the pass website. I suggest you follow up with Phil if you have more questions about his remarks or figures. If you are able to make this scale out to 30 servers I am sure Microsoft would be very interested in speaking with you. Phil also says (another quote from the same slide) in reference to bi-directional transactional replication - "it supported one node, and two nodes, but you couldn't extend it beyond 2." If you want to contact me I can play these sound clips for you. I can contact Kevin Kline president of Pass and ask him for permission to publish the audio's for these slides if you require it, but I urge you to contact Phil or to follow up with your Microsoft contacts. -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:uIq8356GGHA.3100@tk2msftngp13.phx.gbl... > Most of my figures come from a recent presenation that Phil Vaughn did at > Pass this year on replication. I'll listen to it again and verify these > numbers. If I am incorrect I will post back here with the corrections. > I'll also ping him to verify these quotes. Paul Ibison has a copy of the > same presentation. > > While I have no doubt that you have built such systems let me quote from > BOL > > In a section entitled Queued Updating - Queued updating is most > appropriate for applications where users mostly read data and only > occasionally update data. > In a section entitled Immediate Updating - . Immediate updating benefits > applications in which snapshot or transactional publications are preferred > but occasional updates need to be made at the Subscriber. > > While BOL has occasionally being inaccurate, it is my belief and > experience that it is completely correct here. > > When I say something is rolled back, I mean it in the same sense a > transaction is rolled back and the system is left in the state is was in > before. You can use the conflict viewer to "rollback" replication changes, > or as they put it "keep the Wining Change", resubmit delete, insert, > update. > > Note that in SQL 2000 you have an option to compensate for errors which > had a default of false. In SQL 2005 it has a default of true. In other > words conflicts will be logged but the changes will not win on the > subscriber with this setting as false. > > As I have stated previously in this newsgroup Paul and I have a > committment to accuracy and helping people with correct information. I > trust you have the same committment. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > "Michael Hotek" <mike@solidqualitylearning.com> wrote in message > news:OFKLzQ6GGHA.2696@TK2MSFTNGP14.phx.gbl... >> That's really baffling. Those are some pretty absolute numbers being >> thrown around. How does conflict detection have anything at all to do >> with scalability? >> >> If I want to, I can build a bi-directional transactional configuration in >> a linear chain that has 1000 servers in it and make it work perfectly >> fine, so that blows your "can't scale" theory out of the water, because >> it can in fact be done. (Although I don't have an explicit business >> application for that.) >> >> How do you figure that peer-to-peer is not scalable beyond 12 - 15 nodes? >> Just what exactly was your test platform that gave you those explicit >> numbers. I'd really like to know, because my testing has taken it out to >> 30 nodes and everything was still working. >> >> I've done updating subscribers in configurations where not only where >> there dozens of subscribers, but over 90% of the changes occured on the >> subscribers. >> >> There is no such thing as rolling back from a conflict. A rollback >> occurs within a transaction space and causes changes to be undone before >> they are committed to the database. A conflict is thrown against >> committed and completely valid data in the database. The only thing that >> conflict resolution can do is produce a compensating transaction which is >> then applied, but it is still a completely separate transaction that >> modifies data. (It most certainly doesn't rollback.) >> >> Merge is scalable to 1000 subscribers? Really. How do you figure? I've >> had a merge architecture in place and running perfectly fine since SQL >> Server 7.0 that had over 10,000 subscribers in the architecture. I've >> also had merge architectures which couldn't scale beyond 5 subscribers. >> >> I really like these nice blanket statements being thrown out. >> Particularly since they have zero basis in reality. The reality of >> replication is that scalability has a direct correspondence to the volume >> of data per unit time that is being sent through the engine. It has ZERO >> correspondence to the number of subscribers or the pattern of >> modifications. When you say "does not scale beyond x", that means it >> plain and simply does not work if you try it. That is obviously a >> completely false statement, because you can ALWAYS get something to scale >> beyond x. >> >> Keep in mind there are a LOT of people out here reading this. When you >> say that a technology can't scale beyond X, then people are going to >> start looking for different tecnologies to apply, because according to >> you, the replication engine can't meet their business requirements. It >> also makes it really difficult for SQL Server DBAs to architect systems, >> because their managers point to your posts which say that SQL Server >> can't do what they are proposing to do and no amount of testing is going >> to change their minds once they've decided. So, how about giving the >> people who wrote the code for the replication engine a break and if you >> are going to post a scalability number, back it up with enough >> information to explicitly define the entire environment that drew that >> conclusion. >> >> -- >> Mike >> http://www.solidqualitylearning.com >> Disclaimer: This communication is an original work and represents my sole >> views on the subject. It does not represent the views of any other
Thanks, you don't have to. I don't need the slides. I know exactly what is
being said. It also isn't an absolute statement. When you say "doesn't scale" or something else to that effect, to me it means it physically can NOT be done as in the engine blows up, throws error messages, prevents you from doing it etc. While BOL may say that it was designed for a particular application, that doesn't mean it can't be used for something else. The merge engine certainly wasn't designed for 500GB+ databases when it released in 7.0, but it was certainly done. The queued updating option had an original design spec for scenarios where most of the updates happened at the subscriber, but more than 90% of the implementations I've put in (numbering in the over 100 implementations category) had nearly 100% of the changes occuring at the subscriber. So, there are hundreds of cases that I've personally done which disagree significantly with BOL. I also have several queued updating architectures with more than 50 subscribers which again disagrees with your absolute numbers. As far as peer-to-peer goes, it really depends upon what you are doing and what you are running on. On a quad processor Xeon, I had a hard time getting 6 of them running where I had 100 or so changes per minute going in the system. If I chopped that down to 50 changes per minute, I could double the number of subscribers before it started slowing down. If I changed from Windows 2000 to Windows 2003, I could add a couple more. If I moved it to a quad, dual core, Opteron, I shoved it for 30 in a peer-to-peer architecture with about 50 changes per minute going on before it started to bog down. If I increased it to 200 per minute, I had to chop out ~1/4 of the subscribers. If I moved from issuing the transactions against a 30 column table to doing it against a 5 column table, I could shove it up to about 400 changes per minute before it started to bog down. So, the number are VERY HIGHLY DEPENDENT upon precisely what you are doing. If you are going to post numbers, particularly with the replication engine, I am ALWAYS going to dispute them. (Plain and simply because since way back in SQL Server 6.5, I've had implementations in production that have ALWAYS exceeded any type of numbers Microsoft has posted and have ALWAYS had implementations doing things that a feature wasn't originally designed to do.) You had better be prepared to explicitly specify: 1. OS version 2. OS configuration 3. Hardware config 4. SQL Server version 5. SQL Server config 6. Network infrastructure 7. Network bandwidth statistics 8. Database structure 9. Write activity a. Volume broken down by inserts, updates, and deletes b. Broken down by transaction per minute c. Broken down by transaction pattern 10. Replication method 11. Replication config If you aren't meeting at least those set of requirements, any numbers that are posted are VERY BASIC rules of thumb at the very least and most definitely do not impose limitations or prevent you from surpassing them. They most definitely are not meant to be thrown around as absolute barriers to doing something. If the interest is in being accurate, then any time numbers are posted, they certainly should not be posted in these 1 and 2 sentence blurbs that convey the meaning that if you are looking to exceed those numbers, you had better look at some other technology because the replication engine can't do X. -- Mike http://www.solidqualitylearning.com Disclaimer: This communication is an original work and represents my sole views on the subject. It does not represent the views of any other person or entity either by inference or direct reference. [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message
news:eZe%23ZE%23GGHA.3752@TK2MSFTNGP11.phx.gbl... > The presentation is #336 - SQL Server 2005 Replication: Lesson's learned > from Early Adopters, in a slide entitled Peer to Peer Topology, in > response to an inaudible question, Phil has this to say "Realistically > speaking when once you get to about 10-12, you start sending around so > many changes you get to a point of diminishing returns, but about 10-12 > nodes is where it peaks out, cause all changes flow everywhere." > > The transcription is mine. You can order this cd from the pass website. I > suggest you follow up with Phil if you have more questions about his > remarks or figures. If you are able to make this scale out to 30 servers I > am sure Microsoft would be very interested in speaking with you. > > Phil also says (another quote from the same slide) in reference to > bi-directional transactional replication - "it supported one node, and two > nodes, but you couldn't extend it beyond 2." > > If you want to contact me I can play these sound clips for you. I can > contact Kevin Kline president of Pass and ask him for permission to > publish the audio's for these slides if you require it, but I urge you to > contact Phil or to follow up with your Microsoft contacts. > > -- > Hilary Cotter > Looking for a SQL Server replication book? > http://www.nwsu.com/0974973602.html > > Looking for a FAQ on Indexing Services/SQL FTS > http://www.indexserverfaq.com > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:uIq8356GGHA.3100@tk2msftngp13.phx.gbl... >> Most of my figures come from a recent presenation that Phil Vaughn did at >> Pass this year on replication. I'll listen to it again and verify these >> numbers. If I am incorrect I will post back here with the corrections. >> I'll also ping him to verify these quotes. Paul Ibison has a copy of the >> same presentation. >> >> While I have no doubt that you have built such systems let me quote from >> BOL >> >> In a section entitled Queued Updating - Queued updating is most >> appropriate for applications where users mostly read data and only >> occasionally update data. >> In a section entitled Immediate Updating - . Immediate updating benefits >> applications in which snapshot or transactional publications are >> preferred but occasional updates need to be made at the Subscriber. >> >> While BOL has occasionally being inaccurate, it is my belief and >> experience that it is completely correct here. >> >> When I say something is rolled back, I mean it in the same sense a >> transaction is rolled back and the system is left in the state is was in >> before. You can use the conflict viewer to "rollback" replication >> changes, or as they put it "keep the Wining Change", resubmit delete, >> insert, update. >> >> Note that in SQL 2000 you have an option to compensate for errors which >> had a default of false. In SQL 2005 it has a default of true. In other >> words conflicts will be logged but the changes will not win on the
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |