Sql server - performance

Databases
1

I am writing a Desktop App which will use SQL Server as its database.
I want to deploy the Database to be on the WEB so it is nationally accessible.
Can I simply connect my Desktop App to the WEB SQL Database.
Or should I also deploy my App to run from the WEB.
Which will run faster ?

ALSO - Do I need to do any major re-engineering of my APP which is written for the Desktop, and I want to run it from a Website as a WEB APP ?

2

You don’t want to have your database available to the Internet.

I would rewrite my desktop application to access the database via the web application which would be set up as an API server.

3

You could create a middleware (WebApp with no user interfase at all) that on one side interacts with the SQL Server database inside the web server, and on the other side your desktop application sends requirements via APIs to the middleware, some to retrieve information from the database and others to insert, update or delete records of the database.
So you dont have to make big changes in your desktop application, but you do have to write the middelware code.
The good news of doing this is that you could use the same middleware for future movile apps you could write for iOS and Android.

4

I am not sure what you mean by middleware. Do you mean I might send -

  1. DatabaseName
  2. TableName
  3. SQL Statement
    What language am I writing the middleware in ?
5

Not sure what you mean - my experience is limited to VB6 and ASP.
An API server is foreign to me

Why can’t I just connect to a SQL SERVER Database being hosted on a website, and interact with it with my Desktop APP

6

I have a desktop app that uses a MySQL database, and the database can be on a remote server. It works, but there are several potential problems you’ll need to address:

  1. The database server needs to be open to the internet - you’ll need to get the server/network administrator to set this up for you. They may or may not allow this. (I manage my own servers, so I can make the rules.)
  2. You’ll need to change the network port that SQL Server uses. A public facing server on the default port will be discovered by hackers and attacked constantly.
  3. You probably want to set up a secured connection to the server, otherwise data passes in plain text and can be intercepted.
  4. Make sure your code handles exceptions from database calls (SelectSQL, ExecuteSQL, etc.) A common issue is the network connection will drop - you just need to reconnect it any try again. But Xojo doesn’t do this automatically.
  5. If your application has a wide audience, you need to assume there may be hostile users. Protect yourself from SQL Injection. And configure database access with the least needed permissions.
  6. Performance - the remote connection will be noticeably slower than a local (LAN) server. Write your code to minimize the number of queries executed, and your SELECT statements should return only data that you actually need.

With all this in mind, my app works reasonably well for some tasks. A local server works much better. Wayne’s suggestion is a good one: write a separate web app to do the communication between the desktop and the database. Or maybe you can build a web app instead of a desktop app.

1 Like
7

Thanks Eric. I am still not understanding the difference in writing a Desktop App or a WEB APP.
I keep hearing there are minor changes required.
I need to see some sample code to fully understand the differences.
Will hunt through the tutorials I guess

Thanks for your valuable interest

8

When you create projects in Xojo, you have to designate what type of project it will be such as desktop, console, web, or iOS.

In a nutshell, a web app is designed to respond to requests on the web while a desktop app is designed to respond to user interactions. But the ability to create a web app also depends on your Xojo license(s). I don’t see a “Pro” designation after you forum name, so you currently only may have a license to compile desktop projects.

You still need your desktop app for the users. But the recommendations here are that it does NOT directly talk to the SQL server, but instead you put a program (the “middleware web app”) on the same server as the SQL database, and your desktop app channels all database access through the middle man layer. The main advantage here is security and NOT directly exposing the database to the outside world.

@Eric Bloom gave a very good summary above of what to consider. I’ll just add that IF this is for a very limited number of users that will ALWAYS be accessing from known static IPs, then you can greatly reduce the risk of database exposure by using firewall or connection rules on the DB server to reject all access other than what you configure. In reality, it is rare that you only need access from a finite number of static IPs.

You may also want to look at a nifty project called Aloe XWS as it can greatly simply the effort to create one of these middleware layers. The current incarnation only works with web app projects, and I’m not sure your Xojo license supports compiling those. The prior version was called Aloe Express and can be used in console or even desktop project types.

I have several things using Aloe Express which let me create an API layer desktop and mobile apps can use to get data without the need to directly access the database server.

9

@George_Sideris — What they are trying to say is that you cannot make your database public (i.e. accessible for everyone, even through your own software). That is just begging to be hacked!

What you need to do is to create a software (a “middleware”) which has an exclusive access to the database and which acts as a moderator between your software and the real database. That “middleware” should ensure that no dangerous request is passed to the database.

1 Like
10

Yes… that is right… You create a webservice with parameters and that webservice will return some information (commonly in JSON format).
the programming language you will use to write the middleware is XOJO

11

You could do it… it will be slow and insecured. Use of webservices (APIs) is the right way.

12

Well as workaround for a limited number of well-known people he could tunnel everything through openVPN.

George, if your experience is limited to VB6 and ASP then I would consider to get trained or updated how networks and the internet work and how to create secure and privacy-friendly apps by design and default today.

13

" I would consider to get trained or updated how networks and the internet work ". I’ve only created a few web based eCommerce Stores, where my hosting website takes care of these matters (I hope). eg, Prestashop based store, and others I have created myself based on ASP with a Microsoft Access Database on-line.

Any ideas on good websites to visit on these subjects ?

14

Is XOJO Cloud a good, fast, secure and affordable option, do you think ?

15

Xojo is ideal if you have desktop apps and a Server JSON API working together. The main advantage: You can reuse your Code and develop and check things very fast if you are familiar with the basic concepts of the Web, Servers and Protocols.

I wouldn’t recommend Xojo as Web Front-End or as WebUI in its current state. Big hopes are on announced Web 2.0.

16

Thanks Tomas - I am hearing that WEB 2.0 is worth waiting for - which I will do.

I have just joined XOJO so I am coming in at a most exciting time - WEB 2.0, Android
In the meantime I will try to get as familiar as I can by writing some Desktop Apps
Cheers