DISCUSS: The direction of AwareIM SaaS in 2019

Jaymer

Some of you are happy with Aware as a RAD tool inside your company, and others are happy with a few customers to do custom apps.
This discussion is for those who seek to "make millions" on one or several SaaS apps written in Aware.
I've had a discussion with 3 AIM peers in the past 10 days - all of whom have some degree/concern over just how to accomplish this feat in Aware.

First let me say that i'm very happy with the level of response I've seen from Vlad and the development team over the past few years of my involvement in Aware. I see things in the product (even in the latest build) that are wonky or immature or may even be a bug - but I attribute that to only being at that state because it hasn't been pushed/prodded or brought to the forefront enough for Vlad to fix it yet. So I'm not hung up over small stuff like that because we know stuff gets fixed when it rises to the top of the hitlist.

So, back to SaaS.

ISSUE # 1 - CO-MINGLING OF DATA

The two prevailing AwareIM strategies (from what I've seen) are using code to separate customer data (ie. a TenantID in data rows) to give a user only access to data that is for/from his tenant ID. The other being to use READ PROTECT to allow Aware to "filter" the data.
Nevertheless, the data IS CO-MINGLED in one database, which can be an issue in the medical world or just if your customer is hesitant to have his data out on the cloud mixed with other people's data. So, for the sake of discussion in this thread, lets say this strategy is not acceptable for the future, EITHER because we are in a medical situation (requiring non-mingled data) or the market/customers dictate it.

ISSUE # 2 - Separate BSV and Database per Tenant = NO CO-MINGLING OF DATA

Mark Bailey told me the other day he doesn't do his original model of SaaS anymore (which is the TenantID in the data) and now he has each customer (in this particular Saas app that we were discussing) with their OWN BSV and their own Database. He uses Aware's Upload New BSV function (under the standard menu) to upgrade and Aware handles all the table mods if needed. Very simple - but he's only at 4 customers. And not likely to go to 1000, so for now its a manageable deal. But what if you create a SaaS that gets traction, is cheaply priced and 100 sign up per month? So, likewise, lets discount this for those real go-getters who want to make a million and can't do it with 5-20 Tenants, and see a big future ahead! (Bruce, btw, wants you to know that if you're signing up 100 customers a month, you can hire him to copy your BSVs for you 🙂 )

ISSUE # 3 - How to automatically provision a new Tenant with SubDomain

A) Robin showed some of this at the recent conference. While I haven't seen it, one issue revolved around creation of a Tomcat subdomain. Lets say you come to my app and sign up for 30-day trial with code/company/domain/instance name "jaymer". The screen tells you "Access your new system at http://jaymer.coolSaasApp.com ". You can modify Tomcat's server.xml file to allow for a subdomain. Robin was saying that Tomcat has to restarted for this to be recognized - but more research needs to be done. See https://tomcat.apache.org/tomcat-8.0-doc/config/host.html#Automatic_Application_Deployment and https://tomcat.apache.org/tomcat-8.0-doc/config/context.html

B) Another issue is how to automatically get a new BSV into the system once a customer signs up. I'm not sure how Robin was doing this, but he also said something needed to be restarted in order for Aware to see the new BSV.
I think, that since the BSV is stored inside 1 ROW in the database (table BAS_DOMAINS and DOMAINVERSIONS) it should be possible to have a Stored Procedure CREATE A NEW BSV simply by creating a new row in these tables and VOILA! the new system is online. After copying, it needs to be modified to [at least] point to the new database - not sure how to do that. The user templates & documents are stored in other tables so those have to be copied too.
Maybe enough interest could prompt Vlad to make an API which could easily make this happen since he has code to do all this already. Like I said in the intro, things just have to have enough priority to make them get done.

In any case, this Provisioning is something that an Aware Developer would want to happen with minimal effort if not completely remove developer-involvement when setting up a new customer.

ISSUE # 4 - BASID_GEN

There have been grumblings about this (probably forever) but certainly recently about the way Aware generates its ID #s for table rows. And some improvement is coming in 8.3 - I'm not quite sure what that is... perhaps someone can comment below.
OK, your BAS_IDGEN table is used for your entire installation of Aware on a licensed server. One license. One IDGEN. One batch of ID #s system wide on that server of 1 thru 2 Billion. NO matter how many Tenants or BSVs or databases you have on that server. Whichever above SaaS strategy you might use, you have this limit.

THIS issue is the fact that Aware's ID #s are (in MS SQL) an "int" datatype, which puts them at a maximum value of approx 2,000,000,000 (2 billion).
But, not only is the limit an issue (maybe a minor one), the other is that all data rows in the system have their ID #s scattered all over the place with gaps in them. Those ID #s ARE IN FACT the Primary Key of tables. And some IT Managers may have issues that the values are not consecutive and have large gaps (caused by other used on that server creating data which use the IDGEN numbers). Dunno. (Neither of these are particularly troubling to me.)

Robin has an issue with this because of a known real-world case where this affected a SaaS company and the integer datatype bothers him.

ISSUE # 5 - SINGLE BSV with Connection String

One item that has hit the forum in the past year is that of dynamically connecting to different databases. Aware doesn't use a traditional CONNECTION STRING that goes back to early Windows/Access & ODBC days. I'm not sure how this architecture would fit into Aware's world or if it even could.
Since Aware already manages server-wide tables (BAS_IDGEN for one), why not manage a BAS_CONNECTIONS table. Could you log into a BSV (which validates the default REGULARUSER table) and THEN determine a Connection String to point to data? A BSV could "own" many CSs so on the fly you could switch to other data. Yes, talking too much on this will make your head explode. Would Aware's unique and very-valuable table/data/scheme Synchronization still be able to function? One CS endpoint has data in sync with the current BSV and what about the other CSs that are now out of sync. How to keep all that straight? It boggles my mind every time many changes have been done in Testing that Vlad 99.995% of the time perfectly converts the Production database when you Publish.

IN SUMMARY
If we discount Issue # 1 with traditional TenantID in Data, then is Issue # 2 an issue? I think it is for those with many many potential Tenants.
Will Issue 3 become easily doable with no intervention? Is this the ideal solution?
Does the IDGEN Integer limit bother anyone? and/or does support already have a plan?
Is a connection string too much of a pipe dream?

OK, well enough for now. Please post your SaaS concerns and ideas and lets see what replies we can get from SUPPORT.
thx
jaymer...

NOTES:
N1) Splitting off to another server incurs another $100/mo (lets say) PLUS another Aware license (approx $100/mo amortized across 3 years). Assume you made that new server because you needed to put 10 customers on there and you're only at $20/customer/month to pay for the server and license. If you're charging $250 per month for SaaS then you're fine, if its $49 then you're gonna have to hope you can put more than 10 customers on that server.

ACDC

ISSUE # 4 - BASID_GEN

Please, please, can we get a separate ID_GEN table for each specified DB outside BASDB, it just makes sense. I am segmenting my ID nos to facilitate remote BSV synchronising with a central database. It would be more controllable, more versatile and elegant to deal with independent IDGen Tables

I believe the changes to BASID_GEN table in 8.3 refers to MSSQl only, where the database is incrementing the ID number as opposed to AwareIM doing it. If this change was possible then surely it should be simple enough to give each DB its own ID-GEN table

ACDC

I would like to add to Jaymer list :

Another big requirement I have and I am probably pushing my luck here is to have multi-level BSV’s. by introducing a higher Level BSV in the tree, call it BSP (Business Space Platform) for want of a name

This has to do with scaling and efficiency when an application starts to take traction and gets more customers each having their own BSV setup

Here’s the problem:

Imagine having 50 clients using the CRM sample, but each client has their own unique requirement related to form layout, business rules, Queries, and Visual perspectives etc. Currently, the only way to do this is have 50 forks of the original system which becomes a nightmare to manage.

A suggested solution (I know Vlad likes to hear problems and not solutions, but here goes anyway) to this would be to have the main BSV (now called BSP core) and then when changes are made to the existing system, its either a universal ( BSP only)change, or it’s a change related to a subcategory BSV. (If there are new attributes that have to be created in the database as part of a custom BSV change they would just exist in core BSP )

So carry on as normal with development of the BSV ( now called BSP core) and then if there is a special requirement for a particular BSV in the tree then assign the specific change (be it any one of the items mentioned above) to the related subcategory BSV. So it becomes exclusive to that BSV only

Then at the time “Putting into Test” or Publishing or Exporting, the developer is prompted to select the related BSV for whatever ever action is about to take place, resulting in the system exporting/publishing the related components associated to that BSV in the BSP tree

So the BSV in the tree that has no custom changes or additions will show no entries, it would take on the default BSP core version

Does this make sense, or am I overlooking something? Please let me know if there would be any support for this kind of feature. Maybe we can start lobbying support to consider a solution

Markfre1

I feel # 5 is the best option. When you first login, AIm could have an option to assign a tenant. A schema gets created for that tenant and subsequent tenants. All data works with the tenant's schema selected. If the bsv table is modified, instead of modifying one tenant as the system works now, it should modify all tenants. I am not sure how difficult that would be to do. I would like to avoid multiple BSV, because I could have a thousand + tenants, and since the app is the same across all tenants, who wants to update 1000+ bsv's.

My market in C/S was/is CPA's/Accountants and we had over 2000 CPA's. Each CPA would have average 30-50 separate companies (tenants) which they would process monthly. About 75,000 tenants overall but basically 30+ per CPA. So one company had nothing to do with another and 1 bad company data was a simple restore. ( which is another issue with commingled data).

Our procedure was simple and is still in effect and has worked fine for 35 years. The only note here is that it is Pervasive Btrieve which puts physical files on the disk.

When a CPA would create a company, we would create a sub folder \ax\abc \ax\abc, etc. Any files that got created got created in the subfolder, and we also added the file extension customer.abc, invoice.abc. When we backed up a client, we just backed up the sub folder. Deleted a company, just deleted the sub folder. So I am suggesting the same type of method, but instead of folders, just create schema's for each tenant, and then work within that schema of the logged in tenant.

I have no idea the structure of AIM, how difficult it would be to do, but this I believe would be the best method. Modifying tomcat, manual manipulation, restarting the server, etc is to complex and doesn't lend itself to self signups. Logging into a tenant from one bsv is the simplest method from the developer's point of view.

Many apps now are self service, where you go in, sign up and begin processing for 30 days. The worst thing to do is send an email, someone will contact you in 24 hours, and then you set up the bsv, etc. but the customer is long gone by then onto next product.

Because I have no choice, currently I am designing my AIM accounting using one BSV with multiple tenant Id's. However, due to the US Hippa laws, I have to eliminate any medically based clients as commingle of data is not allowed.

Last comment. FreshBooks has 5 million users and I believe they use MySQL. If it's one database, the size of the tables has to be huge. I think they must have a schema per tenant. Xero and FreshBooks have over a million users. I haven't been able to find out how they are handling their tenancy yet.

Vladimir is a genius, and with the various posts to know our thoughts, he listens and always comes up with what makes the most sense. Hopefully, this can be moved to the top of the priority list.

Mark F.

Markfre1

Looked up what Xero is doing. They provide a history. https://www.xero.com/blog/2016/07/xeros-platform-structure-history

From One database
Then clustering 40,000 per cluster - outgrew this
Now implementing Cell method.

Not sure this is what they want but they are trying to accommodate millions of users. Check out the article.

Mark F.

Markfre1

In researching this further, there are pro's and con's for different methods, including performance, costs. etc. Because with our C/S app, the client was always responsible for their data, and with our method of having each tenant with their own set of independent data, it was easy to manage and never had to be concerned of one failure resulting in all companies affected. So very little exposure.

In the Saas environment, we the developer are responsible for the data. When dealing with thousands of customers the thought of having a failure of the database, and affecting every customer is quite scary. The idea of having separate databased per customer is more palatable. However, because of my lack of knowledge regarding scaling, redundancy, performance, how failures are handled, etc, I'm now confused as the best approach.

I heard at the conference, separate BSV's are recommended for each tenant. If that is the thought, then I prefer one bsv creating separate schemas per tenant. Much easier to maintain.

At this point, I'm on overload and would like to hear other developers feedback.

Mark F.

Markfre1

When I say schema's I meant databases. Mf

Markfre1

Whereas I was a strong advocate of single tenant databases, the more I read up the more it seems that multi-tenancy in one database seems to be the method used by most large companies. Salesforce, Xero, etc. See link below for excellent article.

My biggest concern data reliability and recovery. My lack of understanding of how reliable relational databases are, does data get corrupted, is it isolated, is there automatic recovery. You can't restore a whole database when you have hundreds or thousands of customers. My plan is to host with say a company like Rackspace and let them handle the entire infrastructure, security, backups, etc. My skill is writing apps, and the rest is way beyond my knowledge base. But I need to understand if you have table with hundreds of different tenants, how failure on a table affects things. Just a block, screws up the whole table, ect???

Back to research but if anyone has experience in this, I appreciate any feedback. Also, do read this article as its pro-multi-tenant, one database and makes a good argument.

Article link: https://www.netsolutions.com/insights/5-reasons-why-you-should-choose-multi-tenant-architecture-for-your-saas-application/

Mark F

Markfre1

Some more pro's and cons

Single Database for Single Tenant
The main advantage of “one database–one tenant” design is that it ensures the highest level of data safety. Each database instance is located on a separate server, so instances are physically separated. This means that one tenant simply can’t access another tenant’s data.

The complete isolation of databases also helps us to easily restore data. Whenever a tenant’s data gets corrupted, we can retrieve it from a backup database without implementing complex logic.

However, when you provide a dedicated database for each tenant, you have to use a separate server instance to store each database. Hence, you’ll have to pay for additional servers, driving up the cost of using the application. Besides, the ‘separate database’ approach complicates the server infrastructure, making it more difficult to support.

Separate Schema for Each Tenant
To lower the operating costs of the database layer, you can opt for a ‘separate schema’ design.

With this design, the app connects to a single database instance. Each tenant has their own schema (a set of tables) within the database, but not an entire database. Using separate schemas lets you reduce the complexity of server infrastructure, and thereby the cost. The separate schema design provides another benefit as well: it’s very flexible. Although initially, each new schema has standard tables, tenants are able to customize their schema however they want.

There are some disadvantages of the separate schema design, however; for one, we have to pay attention to how we back up and restore data. Let’s say that one tenant’s data gets corrupted, while the data of all other tenants is intact. To avoid overwriting the entire database, developers will have to implement special techniques.

One approach would be to copy tenant data from a backup server to a separate server. After that, we could retrieve the data from the necessary schema. Finally, we could use this data to replace the corrupted information in the main database. As you can imagine, this process is time consuming.

Shared Schema for Tenants
Using a shared schema for all tenants is an easy-to-implement approach, at least at the initial stages of development. Let’s sort through how a shared schema works. In the simplest sense, the data of all tenants will be stored in the same tables. To find and retrieve data from each row, which is assigned to a specific tenant, your SaaS application will use a tenant ID.

The shared schema design provides a few benefits. There’s no need to create and adjust schemas for tenants, unlike with the separate schema approach. There’s also no need to run additional servers for databases.

But what about the disadvantages of using a shared schema? Hopefully, your app will gradually add more and more tenants over time. Assuming it does, then eventually it will become increasingly difficult to query, index, and update data. Besides, it’s also difficult to retrieve data for a given tenant. The multi-tenant SaaS app first has to verify the tenant access level; then the app must also verify the user access level, and only after that can we build a request to the database. With this design, each request will be fairly complex.

Given the advantages and disadvantages of each approach, we recommend using separate schemas, as they provide the best balance between difficulty of development and cost of running the database layer

ACDC

Single Database for Single Tenant
The main advantage of “one database–one tenant” design is that it ensures the highest level of data safety. Each database instance is located on a separate server, so instances are physically separated. This means that one tenant simply can’t access another tenant’s data.

The complete isolation of databases also helps us to easily restore data. Whenever a tenant’s data gets corrupted, we can retrieve it from a backup database without implementing complex logic.

Why separate servers and costs, AwareIM lets you assign a different database on the same server. So multiple databases on one instance is possible as opposed to having all the tables under BASDB. Also you can add instances of the database on one server. Backing up and restoring multiple DB's is simple logic in this kind of setup

Markfre1

Agreed. Was researching posts and posted one. But our system is a low cost 20-30 a month subscription, self signup, self-service besides lots of tutorials and some phone support staff.

So we cannot setup BSV's for each customer. They come and hopefully, they don't go. Which means we have to use multi-tenancy with a tenant id which is what we designed. No separate bsv's.

I'm just concerned, due to lack of knowledge of how reliable sql data is, are there whole system crashes, or just isolated, how recovery, redundancy works, etc. We would hire the expertise to handle this.

When you work separate database successfully in a C/S app for 35 years, going to a new method where our knowledge is limited, you want to use the separate database or schema method, but then it supposedly takes away self signup and features. One thing I am thinking now is our system allows cross tenancy. Meaning an accountant can have access to the accounting of various businesses running our system. It's unique but offered by qb, Freshbooks, etc. , where the accoutant has 1 signin but can see a list of multiple tenants and log into that tenant.

If its separate databases, I can't see how to do that. What we do , is have a accountant object, with one login. There is an accountant detail record, which logs each tenant. The accountant selects the tenant and the loggedincurrent tenant is used as the tenantid. We did that for staff, customers, contractors, etc. It basically allows one login to access multiple tenants. The way AIM is now, it looks like we have to go with the tenantid.

With our current base we know we can get 50+ users. But I'm thinking optimistically, so we're designing for thousands of users. Don't want to pick the wrong approach if we hit on this.

Thanks for the feedback, and seem to be coming to some conclusions.

Mark f

BenchmarkDan

If your head hurts - maybe this will make you feel better.

The largest SAAS provider - Salesforce - agrees scaling a saas solution is hard. So hard that they do nothing like normal databases etc. Everything is metadata.

See this interesting page: https://developer.salesforce.com/page/Multi_Tenant_Architecture

I have done two fairly simple applications on the force.com platform. I like it - but generally way too expensive for clients I deal with.

Markfre1

OK. Got 1/2 through it and was all I could handle. However, Salesforce is managing a unique schema's per tenant with custom code per tenant. It's beyond anything we could do or need.

In my case, it's one app, with multiple tenants. I came up with a scenario which I think might work, and I believe it may not be too difficult for AIM to implement.

Currently, you have a testDB and a production DB. IE: Accounting and AccountingTest. These are two separate schemas with there own set of data. So aware is, in essence, doing it already. Why can AIM have an option for a bsv to create tenants. Maybe up front, when creating a company (new company logs in) auto creates that tenant.

You don't even need to create multiple accountingTest. When you publish, just publish the same BSV to all tenants of that bsv. My thought is if AIM can create a schema from the test, why can't it create schemas from an upfront tenant table. For those who maintain custom information on bsvs, they would have to create a separate bsv as they do now but since its custom, it's what they should be doing.

Now that the data is separate, backups, failures, etc. are all separate. However, I believe they would still be in one database. I think another approach is to have separate databases per tenant but that I believe is expensive, and doesn't fit within I think a doable method in AIM. I'm not familiar if there is such thing as a database failing and all the schema's fail, but since you can backup schema's they can be restored if 1 or more is affected.

I believe this would also have the best performance and easiest scalability but this is beyond my expertise. However, having a separate schema per tenant is a comfort in that I've messed up a bsv in the past, and it had no effect on the other schema's.

So far after all the reading I've done this makes the most sense. I think............

Mark F

aware_support

Thanks, everyone, for your input. However, all I can say is this:

There are two methods in Aware IM and they are unlikely to change - multiple tenants in a single database and a tenant per database (Issue 1 and 2 above). Which one you choose is up to you. Most people do a tenant per database and if it becomes too much for one server, hire another one and then more.
If you use one tenant per database method, then when a new tenant signs up you have to create a new business space, import the BSV and publish it. There is an API that automates this process (not documented yet) and the plugin that uses this API. So if you use this plugin everything is automatic and when the client signs up you can can respond immediately that everything is ready and show him the URL for login.
In the version post 8.3 we intend to replace the plugin with native support, so you won't need to worry about the plugin.
Separate sub-domains will not be supported automatically in the foreseeable future - if you want them you will need to create everything manually.
BAS_IDGEN. 8.3 will use database sequences for this for SQL Server, Oracle and Maria DB. This means that id's will be per database. When MySQL supports sequences (I bet it should be soon), then this support will be added to MySQL as well

Jaymer

Thanks vlad
That’s good news.
Thank you for the reply
I don’t think many of us knew a plug in existed.
And that will be great to automate the process.
The management tool was all that was needed and then one tenant with its own set of data works great.
Is this what Codrin is using now (the plugin)?

aware_support

Is this what Codrin is using now (the plugin)?

Yes

Markfre1

Thank you, Vlad, for lending clarity on the issue. Now that I see the roadmap, I think we have to opt for one BSV / Tenantid method. I'm sure MYSQL or MSSQL have scalability capabilities. I read about sharding which spreads the data across multiple database servers, etc. But there are complexities there. I sure there are ways to scale with one database and get really large before maxing out.

I'm using something I call cross tenancy. A CPA might be invited to work on many companies data. The CPA would have one login and be attached to many tenants with a CPAdetai object (specific information for that CPA for that tenant). When the CPA logs in, he sees the list of tenants he is associated with, and it uses his loggedinuser.currenttenant to work in that set of data. So this works in one database. I'm not sure I could log the CPA into multiple different databases. I'm not sure I can have a bsv just for logins, which redirects to another bsv and use that schema. I'm getting to complicated and I have a method which works now.

The concern has always been data integrity, recovery, scaling and I assume this is all available under one bsv if Xero did it for their system. As previously mentioned, they did one database, then sharded it to 40,000 tenants, and now switching to cells which can handle millions. I figure if I get to 40,000, I'll be sipping a Mai Tai on an island and let someone else worry about it.

Case settled for me for now. One BSV / TenantID segregation.

Mark F.

numberz

Most of this is over my head but what did stand out in all of this thread was "The Hammer" stating that there were two options, pick one, period.
My understanding is that the REASONS for this entire thread was two-fold, price and security.

1) Price:
Is it feasible to have a SAAS subscription for, say, $39 to $59/month?

2) Security:
I think that I can rule out the need for "medical or government grade" type security...but ultimately, if stuff does happen...would it be rather painless to get a customer up and running quickly with this method?

Much appreciated.

codrin

1) Price:
Is it feasible to have a SAAS subscription for, say, $39 to $59/month?
Yes, we have a number of applications on SAAS deployment for a number of years some within the price range above.

I can vouch for the ease of getting up and running quickly after restoring a server that got hit by a crypto attack 😃 .

numberz

Thank you.