Cloud MSSQL Database for VB6 App

[Tobyy]

Hello !!!!!

Can someone please explain to me how to get about my sanity -threatening issues i have with a client request, and which i have been trying to research about but not found anything directly tangible for me to kick off with.

I have a VB6 app which have existed for years and which is still been updated till date. It uses a MSSQL 2008 database back-end and i connect my VB6 app to the database using the SQL Server Native Client 10.0 ODBC Driver connection string, everything works perfectly fine on LAN but the client has requested to have the same app work on a cloud server, whereby he does not need to run any local server whatsoever and whereby he can also login to the app anywhere he is as long as he has internet, without having to be limited to the availability of Local server beyond closing time.

Is this possible ?

Is it possible to have a VB6 app on a system and which accesses the MSSQL database on a cloud anywhere and anytime ?

I need help, insights and advice on this, please.

Thank you.

Regards

[PlausiblyDamp]

Quick answer is yes, it would be a matter of building a server in the cloud, installing SQL 2008 on it and configuring the firewall to allow access. The exact process for this would vary from provider to provider, if you aren't locked into 2008 then there are other options open to you rather than a virtual machine based approach.

However I would personally be very concerned with the security implications of being able to directly access a database over the internet, a more typical approach would to build an API layer in the cloud that talks to the database and have the application talk to this API instead of dealing with the database directly. This is not going to be a trivial change to your existing application either.

The other thing to consider is the running costs, cloud based systems can run a lot more expensive than a simple on premise server deployment - this isn't always the case but a good understanding of your requirements and the various cloud options might be needed to really understand the cost implications.

[dilettante]

SQL Server on a rented off-premise Windows host isn't the same thing as "the cloud" except perhaps in layman's terms.

Isn't SQL Server (or a sort of clone) one of the click-bait features of Azure Services? I'm not sure it has acceptable performance without some re-architecting to avoid round tripping a lot of data across the Internet, and it probably isn't cheap. Supposedly it offers better security than something like an exposed instance of MySQL, but I don't have any experience with it. Even then "Proactive Monitoring" seems to be your responsibility.

An overview of Azure SQL Database security capabilities

[DEXWERX]

Exposing a SQL Server instance hosted on Amazon RDS requires the user to be added to a security group by IP. Not very practical, in your case.
Best bet is to re-architect your app to use a web service - or go full on web app.

[Tobyy]

Thank you so so much. The best news for me is that its possible as you have said. That pushes me on to continue until i get it done. I have created a Microsoft Azure account and i am trying to upload the MSSQL 2008 installation file. I will install, restore the backup and i would try pointing my connection string to it, hoping it would work that easily.

[VB6 Programming]

Hello !!!!!
I have a VB6 app which have existed for years and which is still been updated till date. It uses a MSSQL 2008 database back-end and i connect my VB6 app to the database using the SQL Server Native Client 10.0 ODBC Driver connection string, everything works perfectly fine on LAN but the client has requested to have the same app work on a cloud server, whereby he does not need to run any local server whatsoever and whereby he can also login to the app anywhere he is as long as he has internet, without having to be limited to the availability of Local server beyond closing time.

There is never an easy answer to this, though it is becoming a commonly requested requirement.

One thing you don't make clear is whether the existing VB6 app needs to remain in use as is (for local/office based use).
And does the requirement to 'login to the app anywhere" mean the VB6 app has to be used? Or could a (possible cut-down "Lite") web app be used for remote access?

It's possible to have a Windows (VB6) app running on desktops with your database hosted remotely.
It's possible to have a database "in the cloud" to access via a browser app.
It isn't easy to offer both of these on the same database.

[techgnome]

It isn't easy to offer both of these on the same database.

It isn't? Why not? I've seen that done quite successfully before with no issues...


-tg

[Zvoni]

Not exactly a Cloud-Topic, but VPN might be an approach.

I'm working in a company, where we access our ERP locally on the LAN (Desktop-Computers).
However, we have traveling employees, who have to be able to connect to it remotely.
Solution: They fire up a VPN, and then have access to it as if they are on the LAN

No idea about costs though, but i'm pretty sure it's way cheaper than any cloud-solution.

[wqweto]

Such layman "cloud-solution" would need the SQL Server to be licensed per core -- about $3000 per double cores or $12k per octa-core CPU. (Btw, Windows Server core licenses are sold w/ a minimum of 8-cores per CPU.)

cheers,
</wqw>

[VB6 Programming]

It isn't? Why not? I've seen that done quite successfully before with no issues...

tg,

With ODBC connection from a desktop VB6 app and Web Services/Web App connection to the same database? In the cloud?
That's why I asked if the existing VB6 app had to remain in use for local (non-remote) access.

Often hosting companies won't allow ODBC connection, for security and performance reasons. At least typical website hosting companies don't. Maybe AWS/RDS or Azure may allow this?

Other approaches include using VPN for remote access to the database, or having a shadow-copy of the database for access from a web app.

But if you have suggestions for keeping an ODBC connected database (using an existing VB6 app, but moving the database offsite) and allowing access to that from a web app I'd be very pleased to hear about them.

In fact you've just inspired me to go and look at AWS again

[techgnome]

I'll admit, I don't know the specifics, but at my last company, that's how we had one of our products setup ... the main app was a VB6-built app that talked to a database that could be hosted either on their servers on ours ... we also offered a web-version upgrade that we hosted on our webservers that could then communicate with that database... granted it's easier if we hosted the database, and I don't know the specifics because that wasn't something I was involved in, but I do know that that was the setup, there was a VB6-based app on the desktop that communicated with a database on a server, and a web-based app that also communicated with same database, and each of those pieces were - or could be - on three different servers.
In re-reading what you wrote - yeah, it may not be trivial... but it's not impossible. The best best is to build a set of web-based services or micro-services that both the web-app and the desktop app can consume, and build both off that... and not worry about trying to do direct connections to the database.

-tg

[PlausiblyDamp]

tg,

With ODBC connection from a desktop VB6 app and Web Services/Web App connection to the same database? In the cloud?
That's why I asked if the existing VB6 app had to remain in use for local (non-remote) access.

Often hosting companies won't allow ODBC connection, for security and performance reasons. At least typical website hosting companies don't. Maybe AWS/RDS or Azure may allow this?

Other approaches include using VPN for remote access to the database, or having a shadow-copy of the database for access from a web app.

But if you have suggestions for keeping an ODBC connected database (using an existing VB6 app, but moving the database offsite) and allowing access to that from a web app I'd be very pleased to hear about them.

In fact you've just inspired me to go and look at AWS again

Both Azure and AWS will allow external access to a database it will just require you to configure the relevant provider's firewall accordingly.

[VB6 Programming]

Both Azure and AWS will allow external access to a database it will just require you to configure the relevant provider's firewall accordingly.

That's good news

I'm trying to understand the AWS pricing now
It's always worrying when they provide a "simple calculator" to help you estimate the monthly costs.

[Tobyy]

tg,

With ODBC connection from a desktop VB6 app and Web Services/Web App connection to the same database? In the cloud?
That's why I asked if the existing VB6 app had to remain in use for local (non-remote) access.

Often hosting companies won't allow ODBC connection, for security and performance reasons. At least typical website hosting companies don't. Maybe AWS/RDS or Azure may allow this?

Other approaches include using VPN for remote access to the database, or having a shadow-copy of the database for access from a web app.

But if you have suggestions for keeping an ODBC connected database (using an existing VB6 app, but moving the database offsite) and allowing access to that from a web app I'd be very pleased to hear about them.

In fact you've just inspired me to go and look at AWS again

I did successfully setup a Virtual Machine on Azure, i installed the MSSQL Server 2008, i restored the database, allowed the ports and i tried connecting the VB6 app on my local system to the database on the Virtual machine on Azure. It connected fine but the software was so damnnnnnnnnnnn slow. It was so slow i had to wait a long time even when i wanted to Login.

I guess all my codes in textbox_change events, timer events, etc would need to be removed. Each of these processes dragged for a long time. One thing i can see here is VB6 with fancy features would not work well at all on a cloud server. I have to make it work and would appreciate suggestions of how to make this work.

Thanks everybody !!!

[Tobyy]

I am determined to have this work.......

[PlausiblyDamp]

I did successfully setup a Virtual Machine on Azure, i installed the MSSQL Server 2008, i restored the database, allowed the ports and i tried connecting the VB6 app on my local system to the database on the Virtual machine on Azure. It connected fine but the software was so damnnnnnnnnnnn slow. It was so slow i had to wait a long time even when i wanted to Login.

The size of the Azure VM can make a big difference, too few cores or not enough RAM will certainly cause problems, also the different sizes can have different network performance as well which is another thing to look out for. Out of interest what size VM did you go for?

I guess all my codes in textbox_change events, timer events, etc would need to be removed. Each of these processes dragged for a long time. One thing i can see here is VB6 with fancy features would not work well at all on a cloud server. I have to make it work and would appreciate suggestions of how to make this work.

Thanks everybody !!!

If you are accessing a server via the internet then you really need to avoid "chatty" database access, try to avoid frequent small queries as the overheads and roundtrip times will be a killer performance wise. Wherever possible cache data client side to avoid unnecessary database calls.

[PlausiblyDamp]

That's good news

I'm trying to understand the AWS pricing now
It's always worrying when they provide a "simple calculator" to help you estimate the monthly costs.

AWS pricing is a rather complicated thing, if you are going EC2 then choosing between On Demand, Spot, Reserved and scheduled reserved is a challenge in itself. Reserved instance normalisation factors make this even more fun! If you are going RDS then you still get to choose from On-Demand or Reserved Instance pricing models (which also include the concept of Reserved Instance Size Flexibility) as ways of managing your spend....

As a rule go with On Demand until you understand your usage patterns and can asses if a reserved commitment will be worthwhile (or if you think you could sell off the unused portions when it is no longer needed). RDS has the benefit that you can scale up or down your capacity without disruption to the users, therefore you can manage your spend based on usage patterns; this could be automated via a combination Lambdas, SNS and Cloudwatch to adapt based on current usage.

[Tobyy]

The size of the Azure VM can make a big difference, too few cores or not enough RAM will certainly cause problems, also the different sizes can have different network performance as well which is another thing to look out for. Out of interest what size VM did you go for?

The VM is 150GB HDD, 4GB Ram and Intel Xeon C5 1 core, 1 Logical processor.

If you are accessing a server via the internet then you really need to avoid "chatty" database access, try to avoid frequent small queries as the overheads and roundtrip times will be a killer performance wise. Wherever possible cache data client side to avoid unnecessary database calls.

This might be my major challenge. I am connecting to the database directly using connection string lines like Conn.execute ("Select AcctNo from tblSchool1") and in some long procedures, i have that connection string up to 25 times in one procedure and between them is calculations between fields of recordsets, etc. On my main form, i have about 15 timer controls, all summing up from different tables and for different scenarios, all those plus my several database hits is making it so so soooooooo slow. On my local database, a long procedure took about 3 seconds to complete the several things and with the Azure VM, it took more than 4 minutes and was dragging the whole software all through before it finishes. Its just unusable and i need suggestions on how to make the codes of my several controls work perfectly with the cloud VM as just as it work on Local machine.

[dilettante]

Just think of the problems when you bring transactions and locks into things by allowing multi-client access. With your delays I'd worry about lock timeouts leading to problems... even data corruption.

Sounds like you need a major re-architecting around disconnected ADO Recordsets or a full move to business logic running on the server with a web service talking to clients for presentation.

I suspect unless there is a good deal of processing that can still be handled at each client (or needs to be)... most people would end up chucking it all and writing a web application instead. "Needs to be local" usually comes down to things like local hardware access or intensive interactive graphical operations like drawing or scanning/OCRing that gets posted to the database afterward.

[wqweto]

For an app of ours we did some tests with Azure Database (not a VM with standard SQL Server) and we found out that DB requests have an atrocious latency -- something like 100-200 ms per request.

The app was a classical N-tier architecture, so there was a middle tier DLL that communicated with the DB and fetched disconnected recordsets to the UI layer. We took this DLL and hosted it in a 32-bit app pool on an IIS instance in Azure. We had to implement web requests proxy to accessed object/methods of the middle tier DLL but this paid off as web requests in Azure are really low on latency. I have no idea how they do this but http traffic is inhumanly optimized for latency and traffic compared to raw DB access going through public interwebs.

Just my $.02 worth.

cheers,
</wqw>

[Tobyy]

Just think of the problems when you bring transactions and locks into things by allowing multi-client access. With your delays I'd worry about lock timeouts leading to problems... even data corruption.

Sounds like you need a major re-architecting around disconnected ADO Recordsets or a full move to business logic running on the server with a web service talking to clients for presentation.

I suspect unless there is a good deal of processing that can still be handled at each client (or needs to be)... most people would end up chucking it all and writing a web application instead. "Needs to be local" usually comes down to things like local hardware access or intensive interactive graphical operations like drawing or scanning/OCRing that gets posted to the database afterward.

Its a tough one but thanks so much for the tips. The way it is, it might be practically impossible to have my VB6 app works perfectly, fast as if database is local, swift, etc, if i am to point it to a database hosted on the cloud. There are so many important queries that goes back and forth the database from the app, which has made it veryyyyyyyy slow when i tested it with a cloud database.

Another way is creating a web service to talk to database directly but i really dont have an idea of that.

[Tobyy]

For an app of ours we did some tests with Azure Database (not a VM with standard SQL Server) and we found out that DB requests have an atrocious latency -- something like 100-200 ms per request.

The app was a classical N-tier architecture, so there was a middle tier DLL that communicated with the DB and fetched disconnected recordsets to the UI layer. We took this DLL and hosted it in a 32-bit app pool on an IIS instance in Azure. We had to implement web requests proxy to accessed object/methods of the middle tier DLL but this paid off as web requests in Azure are really low on latency. I have no idea how they do this but http traffic is inhumanly optimized for latency and traffic compared to raw DB access going through public interwebs.

Just my $.02 worth.

cheers,
</wqw>

This is so good for me to hear. I am currently making my research on your tips. I just really wanna make this work. The VB6 app has been growing for more than 11 years, has about 300 forms and a huge app that i really wish it work without having to start writing a web based app version from the scratch.

[Davor Geci]

I would give a try with Application Virtualization.
You install and run your vb6 app on the server and stream the display and the keyboard and mouse to the user.
For Azure there is a company that can help you with that, it is called MyCloudID, and for Amazon take alook at AppStream 2.0.

[Davor Geci]

I would give a try with Application Virtualization.
You install and run your vb6 app on the server and stream the display and the keyboard and mouse to the user.
For Azure there is a lot of work to put the infrastructure to work, but there is a company that created a simple workaround, it is called MyCloudID, and for Amazon take a look at AppStream 2.0.
I think that with this setup you could also run your vb6 apps on windows, mac, mobile, tv and tablets.
Didn't investigate this for some time now, but wayback it was interesting.

[Schmidt]

... so many important queries that goes back and forth the database from the app,
which has made it veryyyyyyyy slow when i tested it with a cloud database.

In your Tests - could you at least try to set the Cnn-Object (in the Opening-phase) to:
Cnn.CursorLocation = adUseClient 'or adUseClientBatch

The default with new instantiated ADO-Connection-Objects is (sadly) adUseServer
(which is not ideal for remote-access).

So, if you have a "global" Cnn-Object (declared in a *.bas-Module), which is opened only once
(at App-startup) - you could try to set the CursorLocation explicitely to "ClientMode" -
and then make a second attempt in accessing your "Cloud-DB".

Another way is creating a web service to talk to database directly but i really dont have an idea of that.

Did you take a look here already?
http://www.vbforums.com/showthread.p...g-of-http-RPCs

Setting up the Service-Layer behind a Dll is not really difficult.

Also the Read-Direction (from inside the Client-App) should be changeable to a...
Set Rs = oWebConn.GetRecordset ("Select ...")
(instead of an Rs.Open or a Set Rs = Cnn.Execute) without taking too much of your time.

What takes more time to adapt (client-coding-wise), is usually the write-direction (making changes in the DB)..
Here it depends on:
- how many different ways "to insert/update/delete data" you've engaged in (Rs-based ... or Cnn.Execute-based)
- what type of locking-scheme you used (an "optimistic one" would be the easiest to adapt to a WebService)
- and also, if you were a heavy user of "explicit transactions" (Cnn.BeginTrans/CommitTrans/RollbackTrans)

Olaf

[Schmidt]

... there was a middle tier DLL that communicated with the DB and fetched disconnected recordsets to the UI layer.
We took this DLL and hosted it in a 32-bit app pool on an IIS instance in Azure.

We had to implement web requests proxy to accessed object/methods of the middle tier DLL
but this paid off as web requests in Azure are really low on latency.

What timings do you get from this (e.g. when querying a NorthWind-DB)?
For example the timing until you got an ADO-Rs (back at the clientside) for:
- "Select * From Shippers"
- "Select * From Invoices"
would be interesting to know (and compare) ...
(as would be the "performance-class" of the Azure-VM - and what you have to pay "per month" on average).

Olaf

[Tobyy]

I would give a try with Application Virtualization.
You install and run your vb6 app on the server and stream the display and the keyboard and mouse to the user.
For Azure there is a lot of work to put the infrastructure to work, but there is a company that created a simple workaround, it is called MyCloudID, and for Amazon take a look at AppStream 2.0.
I think that with this setup you could also run your vb6 apps on windows, mac, mobile, tv and tablets.
Didn't investigate this for some time now, but wayback it was interesting.

Davor, thanks so much. While i was busy on what best direction to take, i saw your post and i am currently researching about it. I will give more info here on my findings and test results soonest.

[Tobyy]

In your Tests - could you at least try to set the Cnn-Object (in the Opening-phase) to:
Cnn.CursorLocation = adUseClient 'or adUseClientBatch

The default with new instantiated ADO-Connection-Objects is (sadly) adUseServer
(which is not ideal for remote-access).

So, if you have a "global" Cnn-Object (declared in a *.bas-Module), which is opened only once
(at App-startup) - you could try to set the CursorLocation explicitely to "ClientMode" -
and then make a second attempt in accessing your "Cloud-DB".


Did you take a look here already?
http://www.vbforums.com/showthread.p...g-of-http-RPCs

Setting up the Service-Layer behind a Dll is not really difficult.

Also the Read-Direction (from inside the Client-App) should be changeable to a...
Set Rs = oWebConn.GetRecordset ("Select ...")
(instead of an Rs.Open or a Set Rs = Cnn.Execute) without taking too much of your time.

What takes more time to adapt (client-coding-wise), is usually the write-direction (making changes in the DB)..
Here it depends on:
- how many different ways "to insert/update/delete data" you've engaged in (Rs-based ... or Cnn.Execute-based)
- what type of locking-scheme you used (an "optimistic one" would be the easiest to adapt to a WebService)
- and also, if you were a heavy user of "explicit transactions" (Cnn.BeginTrans/CommitTrans/RollbackTrans)

Olaf

Thanks !!!!. I am going to test this and get back as soon as i do.
I want to make some changes to the code according to your advice and then try again to see what difference it makes. This would take sometime but i have copied the source code for a test project for this and i would try to see what that gives me in terms of efficiency and speed with the azure cloud database.

[Davor Geci]

Tobby,
do share your findings with us, it could be interesting.