database server at remote location

Posted on 2006-04-16
Medium Priority
Last Modified: 2012-08-13
We are maiking a windows application using dotnet.this is based on a centralized database server.
We are implementing this application  in a state. The database server will be located at one central position.
Clients will be far apart from the database Server..
There may be a distance of 20-30 Kms between these locations and database server..
We are using Sql server2000 as database server.
we are using stored procedures and disconnected approach in ado.net

So my question is:
 What type of connectivity media should be used?
will database performance be depenedent on the connectivity media?
Question by:harpreetkhurana
LVL 16

Accepted Solution

RCorfman earned 500 total points
ID: 16466972
I would note that the database performance itself will not be affected, the database performs the same regardless of how close the client is... BUT, the transfer of data between the database and the client is definitely affected by the throughput between the client and the server. When you introduce latency between the database and the client, this has an effect. If you have a reasonably 'fat' pipe between the database and the client, 23-40 Kms really isn't an impact.  This is not far enough away to have any problem. hundreds of Kms may start to have an impact.

The speed of the connection does matter though if you are moving much data between the client and server. If you are on a dial-up type of connection, it becomes vitally important to try and minimize the data transfers and round-trips between the client and server... better to get 200 records at one swoop, rather than getting one-record, then another query for one record, then another query, etc.  On the otherhand, if the queries are retrieving 10's of thousands of records, you may want to put a cap on the total number of records that are transferred.

Bottom line is that distance between client and server don't affect the database's performance, but it does affect the transfer of data between the client and the database.

Assisted Solution

gabeso earned 500 total points
ID: 16468127
I assume you will be using tcp/ip networking over the internet and that your platform is Microsoft Windows.

If you want to see the latency (extra time due to network) added by the internet you could try to test the connection from the clients point of view.

The 'ping' command lets you check how long it takes for information to round-trip between the client and the server.

So from a client you will see this:

c:> ping MyServer

"Pinging MyServer [] with 32 bytes of data:

Reply from bytes=32 time<1ms TTL=150
  ... etc ....

Approximate round trip times in milli-seconds:
    Minimum = 0ms, Maximum = 0ms, Average = 0ms"

The summary at the end will give you the average round-trip times.

Now the ping command has extra options:

 -n T  => ping T times (once per second!)
 -l N => ping with size N bytes

So I suggest you try this:

experiment 1: ping -n 300 -l 1000 MyServer
experiment 2: ping -n 300 -l 2000 MyServer
experiment 3: ping -n 300 -l 4000 MyServer
experiment 4: ping -n 300 -l 8000 MyServer
experiment 5: ping -n 300 -l 16000 MyServer
experiment 6: ping -n 300 -l 32000 MyServer

And record the round-trip summary.

This will let you see how long different sizes of data take to round-trip the server purely in terms of the network.

From this you can decide how large a block of data needs to be for your client application.

Be sure to add the network latency to the connection timeout limit: Put very simply - if it takes 250ms to round-trip your data and your connection timeout is 300ms then your server will only have 50ms to process the data and send it back.

Remember that this is the round-trip time is the time it takes there and back again and the time to transmit may be far longer than the time to receive because more may be sent than received. Or vice versa.

To ensure that the connection is used efficiently always used stored procedures on the database server - never send raw sql to be executed. And always try to send the least amount of information back. After all, if you have a client application then it can always cache info it receives.

Hope this helps.


Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll look at how you can use a backup to start a secondary instance for MongoDB.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

809 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