database server at remote location

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

So my question is:
 What type of connectivity media should be used?
will database performance be depenedent on the connectivity media?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gabesoSolution ArchitectCommented:
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.

It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.