Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
MuadDBA
628 Posts |
 Posted - 2002-01-04 : 16:52:37
|
| Howdy everyone,I have an application that was not designed with large volume WAN operations in mind. It's chatty, it doesnt use connection pooling, it uses client-side cursors as well as server-side cursors. In short, it sucks for our deployment. However, it sucks the least of all options, and we didn't develop it so I don't get to tell anyone they have to rewrite it.The performance falls off rapidly as you scale out across a WAN envorinment (what with all that chatting really soaking up bandwidth), so what I am thinking is that I need to have 2 servers, one in each location, that do merge replication with each other. The thing is, it would have to be pretty close to real-time, like perhaps no more than a 5 minute delay. If I were doing transactional replication, I would say, ok, no problem. I do this now in the form of log-shipping to our standby server, and don't notice too much of a negative impact on our performance. However, I need to make sure that changes in either location get applied to the other location, so I need to do merge replication (I think).I have very little experience with replication in general, and NONE with merge replication, so my boss wants me to see external consultation, both frmo groups like SQLTEAM, and possibly from a consulting organization. Hopefully you can help me on both fronts. I need a good, reliable source for consulting services, and I need your advice as well.Does what I am thinking about sound feasible? The one major flaw most people will see is that you can't have that level of synchronization with critical data existing in two locations. I can work around that because I know that the data in one location will seldom be modified by people in the other location, as it is location tracking software, so people in location A are unlikely to be moving and modifying stuff that resides in location B.What do you think? Anyone have any advice? |
|
|
chadmat
The Chadinator
1974 Posts |
Posted - 2002-01-04 : 19:42:39
|
I may be a bit biased :) but you will get the most SQL knowledgable consultants from MCS (Microsoft Consulting Services).We have an office that covers pretty much every region in the US, and nobody knows our products as well as we do.Enough of the shameless plugs :)It is hard to make a good design recommendation based on the info given in your post, but merge replication is fairly simple to set up especially on SQL 2000. Play around with it a bit, and see BOL, and I'm sure you will pick it up without a problem.You can have transactioal replication with updatable subscribers, but probabaly Merge would be better:Use Merge when:- Replicated data is read and updated at the Subscriber.
- Subscriber and Publisher are only occasionally connected.
- Conflicts caused by multiple updates to the same data are handled and resolved.
- You need updates to be propagated on a row-by-row basis, and conflicts to be evaluated and resolved at the row level.
Use transactional replication with immediate updating when:- Replicated data is mostly read-only at the Subscriber.
- Subscriber, Distributor, and Publisher are connected most of the time, but this is not necessary for queued updating subscriptions.
- Conflicts caused by multiple updates to the same data are infrequent.
- You need updates to be propagated on a transaction basis, and conflicts to be evaluated and resolved on a transaction basis (the entire transaction is either committed or rolled back).
HTH-Chad |
 |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-04 : 19:50:51
|
quote:
Enough of the shameless plugs :)
Nah, bring 'em on! We can't get enough of them!  BTW, have you readThe Guru's Guide To Transact-SQL by Ken Henderson? It has absolutely NOTHING in it about replication, but it makes a GREAT shameless plug, and is a superb book full of incredible T-SQL tricks! Plus, Ken is extremely generous with his kickbacks  |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-04 : 20:03:25
|
| Although I've been known to promote The Guru's Guide to Transact-SQL by Ken Henderson myself a few times, it is now clear why Rob is known as The Pimp.--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2002-01-04 : 20:15:51
|
| ...which is not entirely fair, if you consider the costume that graz wore this past Halloween!I've got pictures if you've got enough money Don't be surprised if graz kicks me off SQLTeam though... |
|
|
|
Garth
SQLTeam Author
119 Posts |
Posted - 2002-01-04 : 21:57:33
|
| You should contact Mike Hotek at mhotek@mssqlserver.com. He is generally considered to be *the* expert when it comes to replication. And I know he will do remote consulting if he has the time.I have done a bit of work with merge replication, but not this scenario. The one thing you need to keep in mind is that merge replication uses tables inside the source databases to keep track of changes. If these merge metadata tables are not managed properly bad things (e.g., system degradation) happen. MS claims that managing these tables gets much easier with SP1 of SQL 2000. Make sure you fully understand how to manage these tables *before* you put any merge design in place.And one more thing...if the databases use the IDENTITY property to implement counter columns you need to make sure you understand how this affects merge replication.Garthwww.SQLBook.com |
|
|
|
mhotek
Starting Member
1 Post |
Posted - 2002-01-05 : 04:05:59
|
| I'm here and thank you. I know a few people in MCS that are very good, but they are far from the only place to find very knowledgable people on SQL Server. There are many of us who have seen SQL Server do things or made it do things that even Microsoft doesn't know about or specifically designed it to do. Yes, some of us know this product or areas of this product better than even the people writing the code when it comes to deploying it in business environments. (toot! toot!)And for the record, yes, I do consulting both onsite and remotely. I also specialize in replication and have been doing it for going into my 9th year now. Been there, done that, and have seen nearly everything. I also don't know of anyone or any company for that matter who has done more merge installations and I definitely know that no one else has deployed merge systems even a small fraction of the size that I've done.You have a LOT of work ahead before even thinking about clicking buttons and configuring replication. I've had to clean up WAY too many replication installations where they just configured replication and then promptly watched their production environments melt to the ground.Your requirements point towards merge. Your latency requirements completely rule out replication in any form. Doesn't mean it can't meet those requirements. It simply means you can't guarantee a transaction will replicate within x amount of time.You're also talking about a 3rd party app. It may simply be impossible to deploy anything but strict transactional because trying to do anything else will render the application unusable. (Yes, I've seen that done WAY too many times as well.) If you are stuck with purely transactional, you have a huge data architecture issue to get past there as well. (I VERY STRONGLY discourage EVER trying to do bi-directional replication.)The evaluation, planning, and testing phases are extremely critical. Get them right and you have a very smooth implementation with a very smooth system to manage. Get them wrong and you literally have the worst nightmare you could possibly imagine. I'm doing 3 merge implementations right now. 2 are being done correctly from the beginning (1 is with a 3rd party app vendor). The 3rd is a ground zero clean up of a failed implementation where we've spent several weeks scrubbing, extracting, and validating production data that got completely hosed. We also have about 9 months worth of development work that essentially re-architects an entire set of applications so they are friendly to replicated and in particular merge replicated databases.I can be found very easily as Garth has pointed out.SQL Server MVP |
|
|
|
AjarnMark
SQL Slashing Gunting Master
3246 Posts |
Posted - 2002-01-05 : 22:09:05
|
COOL! Looks like another great resource, mhotek. And a little free publicity to boot. As for Rob...quote:
I've got pictures if you've got enough money Don't be surprised if graz kicks me off SQLTeam though...
I'd hate to be the cause of losing such a great resource as yourself from here, so I'll just have to leave this to my imagination... On second thought, I don't want to think about it too much, so I'll just be movin' on...--------------------------------------------------------------1000 Posts, Here I come! I wonder what my new title will be... |
|
|
|
|
|
|
|
|
Merge replication over WAN fairly regularly