database server at remote location

Posted on 2006-04-16
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

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

    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.
    LVL 9

    Assisted Solution

    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

    Highfive Gives IT Their Time Back

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    Introduction: Often, when running a query with joins, the results show up "duplicates", and often, those duplicates can be "eliminated" in the results using DISTINCT, for example. Using DISTINCT is simple: just add it after the SELECT keyword, an…
    In today’s complex data management environments, it is not unusual for UNIX servers to be dedicated to a particular department, purpose, or database.  As a result, a SAS® data analyst often works with multiple servers, each with its own data storage…
    Video by: Steve
    Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now