March 19, 2009 at 8:21 am
We have a locally hosted SQL Server 2000 database, that we hit with a VB6 application. We are working on adding "remote" users, but running into problems. Our solution of having a terminal server set up isn't working - internet lag and latency is killing performance for some reason.
Anyway, the idea has come up of how we might have both a remote database, and our local database, and have them keep in sync with each other. This is all new to me and a bit outside my realm of experience....what is it that I'm even looking to do? I don't think it's "log shipping" (does SS2000 even do that?), as I was under the impression that that wouldn't necessarily be real time, nor allow changes to either database to affect the other.
Basically I'm thinking that I need some sort of syncing or clustering or replication or something? Something that allows changes made in either database to be replicated across to the other, and vice versa.
Where do I start? Thanks for any info!
March 19, 2009 at 8:36 am
Re-designing the database to overcome network and application issues is usually a very bad idea.
It would be better to re-write the application to work as a n-tier application instead of client server.
Or get someone to solve the network issues.
In other words, solve the problem where it is, not where it isn't.
March 19, 2009 at 9:07 am
Michael Valentine Jones (3/19/2009)
Re-designing the database to overcome network and application issues is usually a very bad idea.It would be better to re-write the application to work as a n-tier application instead of client server.
Or get someone to solve the network issues.
In other words, solve the problem where it is, not where it isn't.
Oh, man, I wish I could send your quote to our divisional manager and to the CEO, too. That's what we've been saying all along. Honestly, this "project" they've got us doing is an exercise in futility, but no one will see it. At least no one outside of the front lines. I was just wondering what I might look into if we are forced to do it. Sigh...
March 19, 2009 at 3:20 pm
I could mention using two-way merge replication, but that is really something that is just going to cause problems, not solve them. I imagine that anything you would do would have to be done in hurry up mode with little time to work out issues, or do a proper design. Also, it is unlikely that any database redesign for two way replication is not going to cause a major redesign of the application.
If you are going to have to redo the application, you might as well just create a proper n-tier application using the current database. And take the time to get the network in order.
March 19, 2009 at 5:52 pm
I agree with Mike there ... You can't use a solution to overcome bad design; it might work but it will come back to bite you later. In my experience usually the 2nd return is far worse then what you have.
In SQL 2000 options:
Merge Replication - Usually you'll have to look at database design and deal with conflict resolution. And if you use identity columns then you have whole new ball game of issues to deal with.
Log Shipping - One way, that is changes can't filter back.
I suppose multiple WAN applications usually for these kind of applications few things have to be done:
1) User & developer training, VERY IMPORTANT, user and developers tend to select more then they need. Train them to use filters, to limit results on SQL side so there is less traveling over the pipe.
2) No major reports are to be executed during work hours; schedule them at home base using a 3rd party reporting tool and save the reports to network directory which they can access at later time.
3) Performance tune the database to the nth degree; carefully watch Disk, Memory and Network usage to limit excessive delays from SQL.
Thanks...
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 19, 2009 at 7:52 pm
Hey Guys... I work with Pete, and you guys have basically summed up everything Pete and have already been thinking, to a T. I only wish we could convince corporate that you guys are all correct...
But I'm kind of the Network Admin at our office... (though I wouldn't be able to do it without Pete), and I'm curious what you guys think we can do about network speed? I'm no expert... but what we've got is a VERY "remote" office... not on this continent. Network latency between here and that office averages around 300ms with minimal jitter (fortunately). I don't even know where to begin tweaking to speed up latency. We've got a Sonicwall TZ170 behind a Cisco 2800 series router on a T1 on this end... and I really don't know what's on the end except that there is a Cisco firewall.
ANY help is appreciated... but I know this is not a networking forum 😉
March 19, 2009 at 11:15 pm
Colin McKenney (3/19/2009)
we've got is a VERY "remote" office... not on this continent.
Hmm I guess we should have asked how far was the remote office; in this case Replication would be the proper answer that is what it was designed for. But few other questions to ask before saying replication is the defacto answer is ...
1) What do they do the VB Application? Reporting? Add/Edit/Update?
2) The data the remote office works on is it a subset of the data that only that office edits?
3) How current does the data have to be at the remote office?
As for speed other then getting bigger pipe I don't know what else can be done; I know databases and programming. I have worked in almost every part of IT shop .. but networking heh. Because when I was upper student at a local IT shop; I couldn't get along with network manager LOL.
Thanks.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 20, 2009 at 8:01 am
Appreciate then response Mohit! We're talking about 7500 miles between the DB and the remote staff. Here's some answers to your querys:
1) What do they do the VB Application? Reporting? Add/Edit/Update?
Add/edit/update, this is a data entry and customer service application.
2) The data the remote office works on is it a subset of the data that only that office edits?
No, when this project is complete they will be doing the majority of the DE work.
3) How current does the data have to be at the remote office?
As current as possible, part of our client agreement is the ability to see that data "real-time".
So replication might be the solution? We'll have to look into that... what we're doing right now is attempting an install of the client application on the remote machine connecting to the DB over VPN in the main office. We'll see how this works!
March 20, 2009 at 8:28 am
We have literally thousands of people working in remote offices as far away as it is possible to be on Earth, so being far away is not a problem by itself. We have a mixture of n-tier web browser applications, terminal server client\server applications, and remote developers connecting to dedicated PCs. All are able to work OK, so distance by itself is not a show stopper.
I would just start investigating easy stuff first. Have you verified the problem is not with the terminal server setup? For example, if you setup a dedicated PC with your application, does the application perform any better when someone connects to it from the remote office? Are there other locations that work OK with your terminal servers?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply