2 Sites, 1 Database Server, site to site vpn = slow...branchcache or something else???

Scenario is for a dental office with 2 locations, less than 5 users.  Split schedule for entire staff during week, at office A mon,wed,thurs @ office B tues, fri, sat.  Database server running Practiceworks (office mgmt db server) on top of Pervasive SQL 10 at office A.  Sites connected via Cisco site to site VPN, tunnel speed tested at about 3 to 4 mbps.  Accessing files\shares is not a problem, problem is strictly with Practice Mgmt Software and Imaging software (gendex-vixwin application).  Very slow from office B.  Patient comes in, open up their Chart, it's very very slow.  To View, Save Xrays, make appointments, update patient info, etc.  

-Term services not an option, Imaging software and hardware will not work, confirmed with support.
-Adding local server to site b and replicating to site A nightly, much to0 expensive to get a 2nd license of Practiceworks and file sync not supported.
-Physical connection between locations (about 30 miles apart) much to expensive.
-WAN Acceleration, an option, a bit pricey but an option at the moment.
-Branchcache?  Server is running W2008r2 enterprise at site A and workstations are Win 7 at site B, so it can be installed\configured but would it help in this scenario with the Office Management and Imaging programs?
-Any other recommended solutions\options?  Any insight at all would be greatly appreciated, have been banging my head against a wall here for a few months now researching\testing solutions!!!

Who is Participating?
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.

My prediction is WAN Acceleration will be the ultimate solution.

What is the latency between the 2 offices when you ping across the VPN?

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
kevheinAuthor Commented:
At last test, bytes 32\time 22ms\ttl=126   - did contact Cisco support to see if any tweaks could be made to the tunnel and firewall, but no changes of impact were made.  

I know WAN acceleration is not cheap, I've been shopping all around looking near the lower end of the money scale I guess, I did find an interesting VPN solution from xroads networks and a software based solution from Replify software, but price tag is in the $4,000 to 5,000 range.  Was hoping to find something cheaper, but that may be a pipe dream?

Recently started researching 'Branchcache', couldn't hurt to test  (I have the necessary components) but wasn't sure if it would help in a database type environment?
22ms actually isn't bad but if the application is very "chatty" all that roundtrip time can add up.  I've tested Riverbed WAN Accelerators over a VPN with 75-100 ms latency and it made a HUGE difference.  The difference might not be as huge with 22ms.  But I'm sure it would still help.

Can't speak to Branchcache, sorry.
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Bill BachPresident and Btrieve GuruCommented:
The issue is with your network latency.  Practiceworks is an older application that uses the Btrieve database interface to the Pervasive PSQL v10 database engine.  The Btrieve interface offers tremendous flexibility and performance for developers, but it works by accessing records one at a time.

Let's look at an example:  You want to read 1000 records to load a patient chart.  At the server level, the database typically responds in under 0.1ms per record, and even if you add a bit of network latency on the local LAN, you have response times of 0.3ms or better, and it takes 0.3s to read 1000 records.  

When you run across the WAN, though, the network latency times are now more like 5ms, or perhaps 10ms.  If you PING across the VPN with a 150-byte packet, you'll get a good idea of your "real" WAN latency.  Let's assume 5ms.  Each request still takes 0.1ms to process on the server, so this number hasn't changed.  But now, it takes the network 5ms to send the request to the server and get the reply back, so it takes 5.1ms total.  Multiple this by 1000 records, and you see a response time of 5.1s, to do the same thing that takes 0.3s on the LAN.

You really have limited options in a case like this:
1) Reduce Latency.  Sometimes, you can request a low-latency link from your ISP, though these are likely to be VERY expensive.  Eliminating routers, firewalls, and other such components can also help, but usually cannot overcome the limit caused by the distances involved.  
2) Redesign the application: Unless you switch apps, or unless Practiceworks creates a new version which uses GetNextExtended database calls or SQL calls, then this isn't an option.
3) Use a Terminal Services/Citrix environment.  By allowing users at the remote location to access a desktop on the main location, they will have what appears to be local access speeds.

What will NOT help is concentrating on bandwidth.  Remember that each Btrieve request is about 150 bytes, and each reply will usually be a few hundred to a few thousand bytes.  The bandwidth of your connection is a measure of how much capacity the link can handle, not on how fast it will transfer it.  Latency will always be affected by the speed of the electrons in the cable (about 2/3 the speed of light) or the speed of the light waves in the fiber (about 2/3 the speed of light also).  Add in additional latency for every switch that looks at each packet, for every router that has to route each packet, for every firewall that has to examine and pass each packet and for the VPN devices that have to encrypt/decrypt each packet, and you'll quickly see that your latency is your biggest enemy.  

There is ONE suggestion you can think about, though it has limited benefits in practice.  PSQL v10 supports what they call a Client Cache Engine -- a block of RAM reserved on the local workstations's memory to cache data from across the network.  When reading data, the system will detect when batch reads are occurring and attempt to read entire pages (blocks of records) at a time.  For SOME applications, this has been known to reduce the number of network round-trips, which (as you expect) will improve performance.  However, the cache engine can be finicky, and can cause problems for some applications.  Check with Practiceworks tech support and see if they support their app with the CCE enabled.  If they do, then enable it on the server (Allow Cache Engine Connections) AND on the remote workstations (Use Cache Engine) and see if that helps.  If you start to get weird errors, like Status 5 (duplicate record), though, then the app may NOT work with the CCE, and you'll have to disable it again.

Going back to your list of options:
1) TermServices: Hardware access makes this a lot harder, but this is still the best solution.  I have never played with VDI (virtual desktops), but perhaps they offer a lot better hardware integration?  Would recommend getting on the phone with VMWare techs to see if they can build you a test environment.
2) Phys Connection: Without line-of-sight communications link like radio or laser, I agree that this will be WAY too expensive.
3) WAN Accel: Won't help.  The idea here is that data is compressed & sent using fewer overall bytes.  This is good on slow links, but if you already have 3-4Mbps data rates, then I think this would only add more latency, and shrinking the 150-byte packets to 100-byte packets won't help much.
4) BranchCache: Won't help.  Incompatible with the database engine, because you are accessing records, not the entire files.

TermServices/VDI will remain the best option, though it may take someone to hack through the hardware issues.  Are you only interfacing to an xray imaging machine, or are there other hardware components, too?

The only last thing I can think of is to move the server every day.  Move the database to a laptop (you can easily run up to 10-20 users on a workstation-class OS, or install a Windows Server if you need more) and take the laptop with you from site to site.  Far from ideal, but it works.  Similarly, you could set up two completely independent PW environments and then just copy the data to a USB drive and carry it back and forth every day.  I didn't say it was a GOOD idea, but it is an idea...
On a 20/20 mbps link, 75-100ms latency end to end, Riverbed improved our maximum throughput by a factor of 10: from 1.5mbps to 15mbps.  We ultimately worked around the problem by spawning multiple parallel data transfers.  The Riverbed was too expensive for us and the parallel transfers worked fine.  Of course that won't work in every situation.
Bill has it.

Every time I've investigated a "Works in the local network, but not on the WAN" issue, it's always been poor programming for the data requests from the client app to the database.

If you can't fix the client software, then the virtual desktop model is your best option. You say "Term services not an option, Imaging software and hardware will not work" What kind of imaging devices? Can they be replaced with network versions?

The third option is to move the server between offices, but I would recommend using a VMWare replication across the VPN at night. Install Practiceworks in a small VM image and move it between two VM servers at each site each night.
kevheinAuthor Commented:
-Term services option is out, practice mgmt. software will work but the image acquisition software will not, checked with their support and tested for myself.
-Checked with PW support re: Client Cache Engine, it is not supported.
-Adding a 2nd DB server at site B is on the table, doing nightly replication\sync, but the price tag for a 2nd pw license is hefty.
-WAN acceleration, though how much of an impact it would make remains to be seen, if I can find something not too pricey and do a 'try before you buy', it would interesting to see, I know it would not be like having a server down the hall from you but any uptick in speed\response time would be a plus.

Little bit more homework to be done, thanks for the info\responses it's been very helpful.
Bill BachPresident and Btrieve GuruCommented:
You can try a WAN accelerator, but I believe it will be a waste of time.  Remember the 22ms round trip times?  comparing with a 0.1ms RTT on the local network, the WAN will be 220 times slower than the local access.  The ONLY option is to reduce latency, and the WAN accelerator will reduce bandwidth, but may actually increase latency.

If you want to tell for sure, fire up a network analyzer like Wireshark on the LAN client and do something in PW.  Then do the same thing over the WAN and compare the traces.  You'll see there exactly how big each packet is, along with the response times, and you can compare the two directly.  

I really like the suggestion from dcj21 (and wish I'd thought of it) about moving the server to a VM and then pushing the VM across the link each night when you lock up, so that it is ready to go at the other location in the AM.  I don't think this would require a second PW license, but it might require a second Pervasive license.
WAN Accelerators like Riverbed do more than just data compression.  It attacks the latency issue by reducing the "chatty" behavior of everything moving across the link.

kevheinAuthor Commented:
This one is on hold for the moment, not sure what the next step is but I appreciate the feedback from everyone.
Bill BachPresident and Btrieve GuruCommented:
Why not close it and award partial points to each person who added value?
kevheinAuthor Commented:
Sorry, my bad - your'e right.  As there are viable options on the table, it basically comes down to the person controlling the purse strings to inve$t in a solution and pull the trigger.
Have you considered any of the free WAN optimizers? Silver Peak's VX-X (http://bit.ly/IdzqN3)  practically eliminates packet loss in real time and works on all IP-based apps. It's the full commercial  Silver Peak  software with no hidden charges or time limits. WANs are limited to  4M of optimized WAN capacity.  You might also look into TrafficSqueezer (http://bit.ly/JYgvBY),  the open source project. I don’t think there are any restrictions, but I hear it’s a bit rough around the edges.  
PS I work for Silver Peak, but how invested can I be in  a product that doesn’t make any money? :-)
kevheinAuthor Commented:
Interesting, I'll take a look.  I have the hardware to run it on, I've got nothing to lose.  Thanks for the info.
kevheinAuthor Commented:
Dave, just to confirm, I would need vx-x running on a server at each site (there are only 2 sites)?
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.