SQL to Cloud porting

Guys, has anyone ported a CW desktop app from local MS SQL to some Cloud solution? I reckon this means not connecting to SQL the usual way (TCP 1433) but moving to some communication via RESTful APi or something?

Ah, by cloud I mean like AWS, Azure etc, not to some remote windows server where I could have IP driver or Nettalk server running

Has anyone done it? If yes, how and what’s the experience?

TIA

Bostjan

3 Likes

When the IP driver came out, I had the IP driver server running on a windows box at some data centre in the US and then the UK when I got forced to move it to the UK. General experience was the further away you are the worse the lag was, so those in Australia saw pretty noticeable lag, those close to the UK didnt notice much lag, but that was mid 00’s internet infrastructure. There’s more fibre cables now, Google run their own fibre cables from the UK to the US as one of many companies laying their internet cables in different parts of the world.

You cant synchronise multiple instances around the world of the IP driver server out of the box, you’d have to write your own functions to do this unless a 3rd party addon exists and I think capesoft might do one called Replicate and I dont know if it works with the IP Driver server. This approach would reduce the lag by directing users to the nearest instance using Anycast domain name system - How can I send visitors to the closest server using DNS? - Server Fault , but your backend ip driver server synchronisation is more complicated than just record locking one database, its record locking across databases.

There isnt any reason why you couldnt have an sql server in a datacentre somewhere where the the MS Sql port(s) are open to the public, but you’d want to have an IDS/IPS running on the port and the sql server logging everything so you can trap new threats. If your users are coming from fixed ip’s you can put address blocks on your server firewall to further restrict who can connect.

This is handy tool to see whats connected to the web Facet Analysis (shodan.io)
Port 1434 Shodan Search Engine
nettalk - Shodan Search

Most people would hide their sql server behind a vpn though, some even tunnel a vpn inside another vpn. UDP is way faster than TCP connections which is why UDP VPN are the norm. The networking hardware today drops very few packets to something a decade or two ago, other than the design flaws built into the way switches handle packets to individual ports and network topology, ie bottlenecks in the internet connections between places and regions like undersea continental cabling.

The SQL server can synchronise with other instances of your databases around the world, and like the IP driver, you can use Anycast to direct users to their nearest instance, but your have to set up the Replicate rules yourself.
Replication Security Best Practices - SQL Server | Microsoft Learn
Securing Replication Over the Internet - SQL Server | Microsoft Learn

I dont know if you have seen this post, its mainly focused on Postrgres and using it online, Databases in 2022: A Year in Review - ClarionHub

There is also the Clarion LINQ which is tied to this Language-Integrated Query (LINQ) (C#) | Microsoft Learn
technology and the Clarion Amazon S3 technology.

I dont know what version S3 was introduced in, but in the C11 help page, if you search for S3 there is a bit of info in there which you might find useful.

I havent used Linq or S3 in the cloud, but I would hope what they bring to the table is the replication handled for me.
Querying data without servers or databases using Amazon S3 Select | AWS Storage Blog

Billing and security has been my main concerns, but if 2FA is setup, then it should be harder to hack your cloud instance, but be mindful of airgapping and mobile phones, the array of sensors on a mobile phone make them ideal data capture units for innovative forms of hacking especially when placed on a desk next to the desktop, like sensing the desk vibrations from the keyboard keys being pressed in order to work out passwords.

Did you know you can load malware on to a simcard which has an ARM processor that will work independently of your mobile phone OS? An over view of the technology can been seen here media.ccc.de - SIM card technology from A-Z Thats why I say dont under estimate mobile phones as attack vectors.

Back in the mid 00’s 2FA was not common place for servers in datacentres and the security products werent as robust as they are today and I found it too much effort to maintain and keep an eye on, but I dont see why it shouldnt be possible and fairly hassle free now a days but I havent used Linq or S3 yet.

Edit
Azure SQL which is MS’s online SQL server engine might handle the replicate automatically for you as well, I’ve not used Azure, but imo the replication and security took up a lot of time, which might be handled for you now. I’d certainly be keen to know.

2 Likes

You can also use TSPlus as this type of solution. That also means zero changes.

Given that AWS and Azure are mostly virtual servers where you can run IP Driver or NetTalk you might want to define what you mean by cloud more specifically.

AWS for example hosts SQL instances, and that’s been used by several folk.

AWS Lambda is not in play though (so called “serverless”) because Clarion is not one of their supported languages.

TSPlus, AnyScreen, even H5 are also easy ways to take an existing desktop app and serve it from a server. There’s plenty of those around.

But your question is too vague to know if I’m giving you a useful answer.

Ok. My clients, for one of my specific apps, wish to move from having their own SQL servers in LAN to me taking care of their data in the cloud. Strictly speaking, I could setup a server of my own, install MS SQL, push it into DMZ and connect all the clients from all companies to this server.
But there are many rules with this, GDPR rules, two servers have to be in mirror, on two different physical locations, responsibility for the data falls on me, etc. And on top of that (VPNs are not possible), I don’t believe direct connections (TCP 1433) to server is the best idea in case of WAN connections.

So, I’d hire a cloud, which already has two locations, backups etc. but it has to be in Slovenia due to some new law here, which prohibits using out-of-country servers for personal data (which my app does process). But the offer here is limited. Clouds that fulfill the requirements regarding personal data, are offered mostly as some servers that do offer MS SQL as a backend but with no direct access, only thru http at frontend (web apis). Web apis can be developed in node.js. So no Windows server available.

So, I was thinking if there was ever some solution which would “convert” ms sql driver “calls” to let’s say restful api calls. You know, the app would “think” it’s talking to ms sql, while in reality it would do a bunch of http calls and replies (and someone should still build api services on the server of course).
It’s probably a sci-fi idea anyway.

But in short: clients wanna experience deskop app as they do now, ditch their own ms sql servers and data backups, have great performance still, while clouds here tend to be linux based. If I manage to find a windows based with ms sql on it (double location, backups, 1gb link, etc), then I’d have more options.

Maybe this chap can help? He appears to be a fellow countryman with the skills and knowledge you might be looking for. He might even know of a suitable data centre in .si

Creating REST API for reading data from Microsoft SQL Server in web browser | TomazTsql (wordpress.com)
About | TomazTsql (wordpress.com)
Blogger: User Profile: i love spss
tomaztk (Tomaz Kastrun) · GitHub

Edit @Bostjan_Laba dont thumbs up me, thumbs up the search engines, they are the ultimate manipulation and control mechanism for users of tech.

1 Like

Yes. I use Microsoft SQL Server as my applications’ databases on my cloud server.
My applications and my SQL Server databases are on the same cloud server.
I have used a cloud server since 2011 for my Clarion applications.
I am not using the IP driver or NetTalk server.
My experience is it works great and it requires no changes to my applications from running it as a standalone desktop app.
I’m using TSplus on my cloud server and my users connect with RemoteApp or RD Client (on a Mac).

What is the condition of your internet infrastructure between you and your cloud sql server and your clients and their cloud sql server like?

ie, what type of connection, how many hops, how many users share the connection to the cloud server (neighbours/office colleagues), what sort of infrastructure are you going through?

For example I can tell you what the firmware version of my local cabinet is which is the thing that converts the copper cables from the property onto a fibre connection, and then I can trace route back to the data centre and then look up information on the individuals nodes to get an idea of the infrastructure I’m passing over.

Ok Having a desktop app that talks to a cloud DB and has great performance are almost mutually exclusive.
You can do it if you use TSPlus and put the whole lot in the cloud, because that way you’re only sending desktop info up and down the wire, not all the data as well. This sounds like the better solution for you. All the others will cause way more problems and be way more expensive, either in money, time, performance or all of them at once.

You might be able to do a Rest API, but I doubt you’d get great performance because you now have a conversion layer to cope with.

Look into a cloud server with TSPlus

It sounds like its screen data plus printer and network shares but I’m not sure which product it is.
Plenty of TSPlus posts on here but no specific links to a website and search engines throw up a few different links.

Is TSPlus this link? Tsplus Download - Free trial #1 Best Remote Desktop Software

So if its using some sort of Terminal Server, a trick I’ve always used is to use the black and white grey scale mode to get a more responsive remote access desktop experience as I found using colour rendition on remote access software like teamviewer, vnc, pcanywhere (remember that) and Terminal Server too slow over slow internet connections.

Having it in black and white mode reduced the amount of data sent down the wire so it was like going from a 15hz television where you patiently wait for every screen redraw to something akin to todays 240hz monitors, in other words you dont see the flicker or lag, its very fast in black and white mode.

But this is where the ipdriver can still compete if you are careful with the amount of data sent down the wire, and dont constantly keep fetching data over and over again which I have seen in the ABC classes.

Yep https://tsplus.net
I think it’s all the same site.
Somewhere there’s also a link to get a discount for clarion people.
Yes it’s a terminal server, but cheaper than the MS equivalent. Works really well actually.

Is this the chap from Malta whose behind it?

no, that’s ClarioNet which is a different thing. TS Plus is (literally the first thing in google search if you search on TS Plus) https://tsplus.net/

Agreed. Moving data in and out of a server, two things come into play;
a) the physical distance from the program to the server - which in your case sounds quite short and
b) the latency between them. This can be significant.

I’d likely recommend something like TS Plus as well here. Rent a Windows Virtual Private Server (VPS) - put it all on there, and be done with it. The server could even be at one of the branches. Replicating data between two SQL instances

is possible, but a lot more work to setup.

Well, yes Replicate would work with IP Driver, but replicate is no longer the preferred approach to replication. It was invented for a time when Dial-Up was common, and we didn’t have “always on” servers. A better approach for today’s world is described here;
https://www.capesoft.com/docs/NetTalk12/NetTalkApps.Htm#DataSyncIntroduction

This is a more resilient approach, and takes a lot less (read “no”) maintenance once it’s set up. And setting it up (per deployment) is easier than Replicate. And the data replicates in “real time” as distinct to “every now and then”.

But this is likely overkill for this situation.

Cool. Thanks. I will look up tsplus and see how it works. Then show it to some clients and see how they feel about it.

** to cut a long story short :slight_smile: ** you always have several solutions for the same thing, you just need to work a little harder and find the optimal solution. I hope my comment doesn’t bother anyone. greetings to all benevolent foxes. Prof. Dean
just try.
https://member5.smarterasp.net/account/loginform?go=1&w=1280&cplang=

I got the connection string, pulled the mssql database through clarion dct and it works in my application for a huge hospital clinical center. It is an integral hospital information system written in Clarion 10. I was surprised when I pulled in a database of 137 tables from the cloud without any problems, with just a username, password and port, and when it took off. Yes in desktop applications. I no longer used net talk, etc., and I also created interfaces over the same base in the radzen(c# rad tool) community version, just so that doctors have everything they need for hospital patients on tablets or phones…I have never advertised anyone on the forums, but these guys are worthy of praise, and I got the most out of Clarion and Cloud.

1 Like

In 25 years working with clarion teams from America to Australia, I have seen many solutions. I also saw this solution that the professor is talking about. It is about millions of records and more than 3000 users, without errors, without the possibility of hacking, everything works extremely fast. I have seen similar Oracle solutions, but I have never seen such a simple, operational and secure one in Clarion. Thanks to the professor for altruistically donating the solution to the Clarion community.

The link seems to be a hosting company SmarterASP.net - Unlimited ASP.NET Web Hosting using Cloudfare Content Delivery Network. This would be worth bearing in mind when thinking about Cloudfare.
Chris’s Wiki :: blog/web/AggressiveStealthyWebSpider (utoronto.ca)
In this blog we are made to think this is coming from MS ip addresses, so I wonder has someone hacked some of the Candian internet infrastructure, to the extent they can inject packets from a switch purporting to be from a MS ip address, knowing said packets will return to that switch? Has someone hacked the bloggers data centre and is in the next server in their rack cabinet? Some things are not always obvious.

One of my milder complaints with hosting companies, is they dont state where the data centre is located so they get excluded straight away because I’m not sure I could be GDPR compliant, ie having to host some data in the country of origin. Their US pricing would also suggest the are US based. Nothing against that but GDPR forces some data to not leave the country. @Bostjan_Laba requirements means the data cant leave .is ie Slovenia.

The level of trust is so great, that I cant even tell you that the US and UK computer systems are separated at these locations Home page of RAF Lakenheath and Royal Air Force Mildenhall > Home (af.mil) and at other bases, but I can tell you about some of the airforces latest technology.

Photo of the Day: A400-MCD (Mobile Charging Device) recharges a B2 Stealth Fighter (Left) and an F22 (Right) using its USB2 charging cables, the cables can also be used to tow the airplanes, saving up to £800.85 per hour in fuel costs

Photographed from a Canberra #aviationlovers pic.twitter.com/3RPFpKiN9A

— RAF_Luton (@RAF_Luton) January 16, 2023