• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 658
  • Last Modified:

database server at remote location

Hi
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?
0
harpreetkhurana
Asked:
harpreetkhurana
2 Solutions
 
RCorfmanCommented:
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.
0
 
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 [192.168.0.1] with 32 bytes of data:

Reply from 192.168.0.1: 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.



0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now