Implications of remote database 1

[Glasgow]

We have developed a couple of VB applications which traditionally have run stand-alone or on a LAN and have talked to SQL 2000 databases. We are moving these towards SQL 2005 in due course. We have never had to deal with any significant performance issues so far (touch wood).

In one case an application has been installed in a Terminal Services environment talking to a database on a remote server. The client themselves achieved this setup with input from us when required and I confess I don't know precisely how the comms are set up. As far as I know there are no performance problems.

We now have a new customer who wants our application to connect to a remote SQL server via the internet. In theory this should be achievable by (I assume) just changing the connection string supplied to our application. However, can anyone offer some guidelines as to what other issues we may need to consider in terms of performance and security?

These are sweeping topics I'm sure - even a pointer to an appropriate web site, book or other publication might help. I'd like to avoid being put in a position where a server hosting package is purchased, we set up the database then the application runs excruciatingly slowly because of problems we have not anticipated.

Thanks in advance.

 

[chigley]

Hi,

The first thing to consider is that the internet is that it may not always be there. ISPs go off line, sheer weight of traffic slows things so that timeouts start occuring. This is easy to test in house, by quite simply turning off the sql server service during processing.

The other things to consider is the code that handles database processing. If your apps are built in vb, then you are using ADO. ADO supports transactions, but are you using them? Each database activity should be wrapped in a transaction, so if there is a connection problem the transaction will fail (hopefully gracefully - if your error handling is in place) and just roll back.

Part b of the transactions argument is that the schema should enforce referential integrity. If there is not RI and no transaction support then orphan records are the risk here.

The final part to consider is what do you want to happen when the database is unavailable? Should the app stop taking orders, or cache locally and then upload the data when the database is now available? A lot of companies use DR sites, with SQL Server hosted off site and the databases replicated using log shipping or mirroring. Could you add a degree of fault tolerance by having a SQL Server running locally to cache to and then sync with the remote db server when available?

Just one more thought occurs. Pay heed to how you back up your database and transaction logs. With high demand systems in the case of taking orders it is vital to have the ability to do a point in time restore - ie being able to restore all of the data up to a given point in time.

If you are making your app available over the internet, then it is advisable to use ADO command objects with prepared statements for each database statement executed. This will help prevent SQL injection attacks.

So in short this will suffer a degradation in performance if you do this over the internet, but your end users need never know.

 

[Glasgow]

Thanks for the detailed response. All very valid points worth considering. Yes, ADO is being used. No, only a handful of updates are wrapped in transactions and yes, the schema enforce referential integrity.

I suspect the client's budget may not stretch to implementing some of the measures suggested.

Do you have any thoughts as to whether it would be a better solution if we increased the remote server spec to accommodate terminal services in addition to the database. Then we could install the application once on that server, it would talk to a database on the same machine and users would connect via Remote Desktop having created some kind of VPN over the internet?

 

[chigley]

I see your point about budget. Always the way. Increasing the spec of the remote server is all well and good, but these remote desktop sessions are quite chatty and eat bandwidth. What is the size of the pipe going into the building? What shares it? Do they host a website locally? How many remote desktop sessions? They will not thank you if you eat all there bandwidth and bring their site down.

While VOIP and IIS apps can be throttled to only consume so much bandwidth there is no way to throttle terminal services connections (that I know of), other than granting ip base access through the firewall. Are the clients using fixed IP addresses or leased via broadband?
If there are say 10 clients all needing remote desktop connectivity then you could poke a hole in the firewall for each ip address.
I think allowing terminal services to be accessible over the web is a bad idea, it certainly was last time I looked. It used to be the case that servers which left that service exposed could be exploited through buffer overrun attacks and so forth. I would really think hard before doing this.

 

[Glasgow]

Thanks again. It's all a bit depressing really!

Many of the questions you pose I simply have no answer to at this stage. The one thing we do know at the moment is that there will probably only be at most 2-3 desktop sessions into the application / database on the remote server.

I confess being tempted towards the terminal services solution because:
a) We install and maintain the application on only one machine.
b) The client can access from any machine that supports remote desktop.

However your own criticism of this option is, of course, a disincentive.

Perhaps the best way is to consider trying out a few tests - I just have to somehow gain access to a server or set up one of our own to try it out.

 

[Glasgow]

Initial tests connecting to a remote database have been encouraging - the application is not as sluggish as I was dreading. This could be the sensible starting point as it is reasonably cheap and data volumes and user counts will initially be quite low.

Thanks again - have a star for your efforts!