Remote database access vs Terminal Services

[Glasgow]

We have a VB application which talks to a SQL 2005 database. Typically the database is installed on a single stand-alone PC or, occasionally, on a server on a LAN. Performance has nevere really been an issue.

One customer who needs a multi-user setup wants to out-source the database server to an ISP. In theory, therefore, our application would connect to the database over the internet.

What are the likely performance implications? Would we be better thinking about suggesting a higher spec server on which we could use a Terminal Services environment and install our application on the same machine as the database? Then users could just connect via Remote Desktop and take the performance hit on screen-repainting. The alternative would be installing the application on individual PCs which then connect to the remote database and take a hit on data retrieval/update performance.

Any input would be appreciated.

 

[ousoonerjoe]

We are currently using both setups. Terminal Services is nice if you have email accounts and/or other applications you want to serve out. If you have just one application that connects to the database, I would setup a VPN and let the client pull across the internet. Plus, it would lower your bandwidth usage. Which in turns lowers the overhead costs.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[JoeAtWork]

VPN connection is way too slow, in my opinion, even on a high-speed connection.

 

[ousoonerjoe]

True, but for the scenario stated above, I would use a VPN (for security). If security is not an issue and the data is not sensitive to your business or violates HIPPA or Privacy Act, then by all means, let it fly with out a VPN. I agree VPN can slow things down, but the advantages and security out weigh the speed loss in my opinion.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Glasgow]

Thanks for the comments.

I think our first port of call will be to experiment with hosting only the database remotely - primarily because the prices for such packages are so much lower than hosting a dedicated server that the Terminal Services / VPN route will force. Obviously, if that route is a wipe-out we'll need to think again but if performance is tolerable we can think about a more sophisticated solution later.

 

[gmmastros]

If you plan on doing this, I'd like to make a couple suggestions.

First, use a non-standard port for SQL Server. By default, sql server uses port 1433. Many, many people know this, and will try to hack your database. They will pick random IP's and attempt to connect using port 1433. By picking a non-standard port, you make it much more difficult for a random hacker to find your database. You can easily accommodate a non-standard port in your connection string.

Second, make sure you use a VERY strong password for the sa account. Finding a database is one thing, hacking it is something else entirely.

Third, since you will be using an internet connection, you need to realize that you will likely be connecting at a much lower data rate than you are usually accustomed to. As such, it is very important that you only return the data you need. Returning extra columns that are not needed at 100 mbps (typical LAN speeds) is very different than returning unnecessary columns at internet speeds.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Glasgow]

I have started testing with a remote database and results are quite encouraging. The application is slower, yes, but not impractically so as yet and, as data volumes and user counts will be low initially, this could be the way to go for the short term at least.

George - points taken thank you. The port and sa password will be the ISP's choice I guess. They have allocated me a username and password to connect to the database. Yes, reducing the columns returned on queries makes perfect sense.

Excuse my ignorance of VPN's but, in this scenario where we are connecting to an ISP's shared server, is it even possible? Any suggestions as to where can I read up on VPN's?

Thanks again.

 

[ousoonerjoe]

That might be a question better asked in the Virtual Private Networks(VPN) Forum

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.

 

[Glasgow]

I didn't even think of looking for such a thing! Thanks.

 

[DonQuichote]

If you have ever heard of an SSH tunnel, you could forward the local database port to the ISP's database port. SSH tunnels are by definition encrypted and are optionally compressed. The impact on the machine and the software used is then minimal. Off course, the ISP should be running an SSH server, but any ISP that takes security serious does (or should do).

I regular do this for MySQL databases. Some database frontend programs even have SSH tunnel facilities built-in.

Note that "security by obscurity" (picking a non-standard port for example), does not make your system any more secure. Ports are easily scanned along with their services.

+++ Despite being wrong in every important aspect, that is a very good analogy +++
Hex (in Darwin's Watch)

 

[BobRodes]

>does not make your system any more secure
I think that's a bit strong. If picking a non-standard port eliminates a percentage of the less sophisticated hackers, the system is more secure. Whether it's secure enough depends on a given set of security requirements. I just don't feel that it's inherently useless to do this as you seem to be implying.

 

[ousoonerjoe]

I agree Bob. In the fight against hackers, every little thing helps.

"If I were to wake up with my head sewn to the carpet, I wouldn't be more surprised than I am right now.