This is a continuation of the articles on multitenancy with the most recent one linked https://1drv.ms/w/s!Ashlm-Nw-wnWhLZnYUBoDUNcjAHNwQ?e=NAo7vM. This article focuses on performance.

 

Reports can be efficiently written. They can pertain to a single instance of an entity like an invoice or they can be more analytical in nature by performing joins across multiple instances.  Faster reports can be enabled by performing read scale-out to read data from a read-only copy of the database, by using partial records to reduce the data loaded from the database, by using AL queries to optimize the way data is read from the database, and using word layouts and RDL layouts which can result in slower performance with document reports, especially for actions related to the user interface.

 

If a data lake or a data warehouse is involved, there are typically two types of data extractions: 1. a historical load with all the data from a given point-of-time and 2. delta loads on what changed since the historical load. The fastest and least disruptive way to get  ahistorical load is usually a SQL BACPAC file that can be restored on a private database server.  The fastest or the least disruptive way to get delta loads is to setup API queries configured with read scale-out and use of data audit field LastModifiedOn.

 

Efficient data access is at the heart of performance improvements. There are a lot of parallels that can be established with data access in a database using SQL, even if the multitenant application makes differences.

 

Many performance issues can be tracked down to missing indexes but index design does not always fall on the tenant or the multitenant solution developer. The indexes must be designed to conform the way data is accessed.

 

Keys are used to identify rows in table and can combine one or more columns in a table. Indexes speed up data retrieval. They serve different purposes. Records are stored sequentially in ascending order and sorted by the primary keys in clustered indexes involving primary keys. Records are sorted dynamically so the data is always structurally correct. Sorting allows faster data access via binary search.

 

Non-clustered column store indexes are also stored in tables. This index type can also achieve significant performance gains over traditional row-oriented storage. It also achieves data compression over the uncompressed data size on normal tables.

 

If the number of secondary keys that are marked as active  are increased, performance can improve when the data is retrieved in several different sorting sequences because the data is already sorted. But this slows down insertion and deletion because indexes for each secondary key must be maintained.

IF the number of keys are small, performance increases because a minimal number of indexes are maintained. It slows when the data is retrieved because of missing secondary keys to get the appropriate sorting.

 

Schema synchronization errors can be minimized when primary keys are not deleted, the order of primary keys is not changed, more unique keys are not added and more clustered keys are not added.

SumIndexField Technology or SIFT for short, helps to quickly calculate the sum of numeric data type columns in tables even with thousands of records. Any field that has a numeric data type can be associated with a key as a SumIndexField. With a materialized view per SIFT key, this speeds up retrieval of data.

 

Databases are big about locking and logging.  Performance issues that are not due to resource starvation can often be attributed to workers waiting on other workers to release locks on shared objects. Some tips to avoid locking include reading information before starting write operations, limiting the time locks are held, limiting the transaction size by dividing it into smaller number of operations and making sure indexes are present on ranges that are updated. Multitenant databases generally have less locking contention due to isolation.

 

Testing and validating the multitenant solution for performance is necessary prior to its planned deployment to production environments. Performance unit testing comes helpful in this regard and can include unit-tests that track the number of SQL statements or rows read. This can be used before and after the code to be tested. Also, assert statements can check for normal behavior. Performance toolkits are available to simulate the number of resources that tenants use in realistic scenarios to compare performance between builds of their solution. This toolkit might not measure throughput but given the time to execute key scenarios and the guidance provided by operational limits, it is possible to determine say how many orders are processed per hour. If there are queues involved, advanced analysis can be enabled. Performance telemetry includes database locks, long running operations, long running queries, page views, reports, sessions started, and web service requests

 

The art and science of software code reviews:

The practice of reviewing software code written by colleagues in an organization is called software code reviews. Code is one of the most important digital assets for any software company and the bearer for all innovations and value offerings that reach the end customer. As software projects vary widely in size and complexity from organization to organization, the practice of reviewing code also varies. Some teams can be lenient with this practice while others are more exacting. This article provides a peek into the world of software professionals as they realize their contributions.

A code review is done just before submitting the code to a pipeline that delivers the improvements to the end users via a series of staged progression through quality gates and environments. It provides an opportunity for colleagues to inspect each-other’s work for potential concerns that can be cheaply and effectively addressed before putting into a pipeline. If bad code gets checked in, software development managers will find their quality measuring dashboards tank which effectively brings a freeze to simultaneous contributions from many teams. A code freeze impedes organizational productivity and increases costs due to lost time and increased overhead among engineers. In addition, backing out a bad code change requires detection and remediation investigations and reports that might be more involved than reverting a single commit which is the unit of code changes made to the master version of the source code in the repository.

It is typical to see dozens of commits a day from an organization that contributes to a source code which exceeds several hundred thousand lines of code. Efficiency is increased when the commits are as independent of one another as possible. For these reasons, managers insist on ‘baking’ the feature completely in a branch and with a feature flag before it is merged as a single commit into the master. A single commit is easier to revert as well than a series of commits spread across folders and their history. The feature flag is an option to disable the feature at runtime in the production environment so that the code becomes inactive and reduces surface areas that would otherwise have required investigations, troubleshooting and workaround education for end-users when potential issues crop up. Often the operations engineering team works independent of the software development team, so defects introduced into the code that make their way to the customers is almost like throwing it over a wall for someone else to take care.

Seasoned engineers in a team that is well-oiled will usually get a code review done with little change to the initial proposal made by its author. But most teams usually draw fresh blood to keep their source healthy, the widely varying practice of code reviews can evoke fear, fatigue from iterations and even friction for the uninitiated regardless of their experience and background. When there is a lot of back and forth, it can get overwhelming and hair raising to anyone. Some manage to not make the same mistake twice while others try not to contact the same individual for another code review request!

The process of code reviewing is highly structured and invested heavily by organizations. Most provide one or another productivity tool for individuals to review the code while also providing a DevOps pipeline to vet the code via running multiple code analysis tools before even a code review request is made. Engineers can view the exact changes to each line before and after an iteration and provide comments during a code review. Experienced ones will be able to spot defects and improve the code style and organization that the tools might fail to provide. Some of the traits they look for include readability, re-usability, testability and refactoring.  Unit-tests are a particular favorite for both bots and humans to call out during a code review. The most skilled reviewers will often point at writing tests that will discourage incorrect usage of the proposed code changes in the future in addition to testing the code for both success and failure. Style is another big contention between engineers and often code reviews iterations can go from a couple of iterations up to double digits just for style and conformance.

The savvy author of a code review would have done a lot of home-work prior to turning it in for review such as informing about the proposed changes via a design document and getting general acceptance to the intent behind the form of implementation. Establishing relationships with peers that can smoothen and expedite the code review process is another popular technique. Managers tend to take note of the number and quality of code reviews by their direct reports, so individuals help themselves by working one or two code reviews at a time.

Finally, the less the better for any code changes and reviews. Engineers will vouch for this universally.

Performance for multitenant application (continued)

This is a continuation of the articles on multitenancy with the most recent one linked https://1drv.ms/w/s!Ashlm-Nw-wnWhLZnYUBoDUNcjAHNwQ?e=NAo7vM. This article continues to focus on performance

Large volumes of web service calls can cause stability and performance issues. It is important to understand the operational limits and to scale such that the load always falls under the limit. External applications can handle the HTTP Status codes 429 for too many requests and 504 for gateway timeout.

Handling status code 429 requires the client to adopt a retry logic while providing a cool off period. Retries can be regular interval, incremental interval, exponential backoff, and randomization. Status code 504 requires the client to refactor the long running request to execute within the time limit by splitting the request into multiple requests. Then the potential 429 codes can be handled by a backoff strategy. A common pattern is to implement a queue in the external application to flatten the spikes in the traffic. If the request gets a 429, it is put back in the queue and one of the retry strategies is applied.

 

Reports can be efficiently written. They can pertain to a single instance of an entity like an invoice or they can be more analytical in nature by performing joins across multiple instances.  Faster reports can be enabled by performing read scale-out to read data from a read-only copy of the database, by using partial records to reduce the data loaded from the database, by using AL queries to optimize the way data is read from the database, and using word layouts and RDL layouts which can result in slower performance with document reports, especially for actions related to the user interface.

 

If a data lake or a data warehouse is involved, there are typically two types of data extractions: 1. a historical load with all the data from a given point-of-time and 2. delta loads on what changed since the historical load. The fastest and least disruptive way to get  ahistorical load is usually a SQL BACPAC file that can be restored on a private database server.  The fastest or the least disruptive way to get delta loads is to setup API queries configured with read scale-out and use of data audit field LastModifiedOn.

 

Performance for multitenant application

This is a continuation of the articles on multitenancy with the most recent one linked https://1drv.ms/w/s!Ashlm-Nw-wnWhLZnYUBoDUNcjAHNwQ?e=NAo7vM. This article focuses on performance.

 

The multitenant application discussed so far has an application server and a database. Performance is improved by 1. Writing efficient pages, 2. Efficient web services, 3. Efficient reports, 4. AL performance patterns, 5. Efficient data access, 6. Testing and validating performance, 7. Tuning the development environment 8. And using the AL profiler to analyze performance.

 

Efficient pages are written by using patterns that get a page to load faster. These include: avoiding unnecessary recalculation, 2. Doing less work, 3. And offloading the UI thread. Caching the data and refreshing the cache regularly avoids recalculation.  This saves time each time the page is loaded. Querying objects are notorious for recalculation since they reach the database each time. Caching the results from an API works significantly better.

 

Reducing the amount of work also speeds things up.  A simple page with few UI elements can also be ease of use and navigation.  Removing calculated fields from lists if they aren’t needed  and removing the field definition  or page extension definition improves loading of pages that list data.

 

Creating dedicated lookup pages instead of the normal pages when dropdown like logic is involved, and removing triggers and fact boxes will help because a default page will render all controls

Offloading the UI thread with say page background tasks can get a more responsive and faster UI. Custom controls that require heavy duty logic can also be avoided.

 

Avoiding expose of calculated fields, avoiding heavy duty logic in pre and post handlers of getting records, refactoring the page and its code so that values are persisted can reduce performance hits. It is not recommended to use temp tables if there are many records. Fetching and inserting each record in a temp table without caching data can be detrimental to performance. If the number of  records exceeds a hundred, this antipattern is easy to detect.

 

Parent and child records need not be inserted in parallel. This condition causes locks on parent and integration record tables because parallel calls try to update the same parent record. It is best to do it incrementally by allowing one to finish before another or by putting them in a transaction batch.

 

A deprecated protocol can be avoided. OData version 4 and APIs have best performance. API queries and pages are faster with newer technology stacks.

 

API pages and API queries are better than exposing ui pages as web service endpoints. If the latter must be implemented, then triggers need to run for all the records returned from the server. If we want OData endpoints that work as data readers, we can use API queries. OData has a few performance callouts such as limiting the set with $filter and $top if there’s an expensive $expand, using a transaction batch and read-only data access intent.

 

 

Synchronization and Data security for multitenant databases

This is a continuation of the articles on multitenancy with the most recent one linked https://1drv.ms/w/s!Ashlm-Nw-wnWhLZnYUBoDUNcjAHNwQ?e=NAo7vM. This article focuses on data synchronization and security. 

The customer’s data is stored in a separate business database, each of which is a tenant in the deployment. By separating application from data, the same stack can be deployed to many customers with centralized maintenance of the application and the isolation of each tenant.  The application database contains the tables that define an application.

Data and applications are partitioned and isolated from one tenant to another in a multitenant application. The application data is in a dedicated application database, separate from the business data in the tenant database. The revert to a single-tenant deployment mode requires the revert of the separation of application data and business data and the merging of the two databases. A single tenant can be folded from the multitenant mode and leaving the remaining tenants in the existing deployment by adding the application tables to the relevant tenant database and leaving the original application database unchanged. If the application data is separated into a dedicated database, but it is not used in a multitenant deployment mode, the databases can still be merged.

Tenant database must be synchronized with the application database in both modes of deployment – single tenancy as well as multitenancy.

Synchronization can occur in the following ways: 1) continuously, which is typical for transactional replication, 2) On demand, which is typical for merge replication. And 3) On a schedule, which is typical for snapshot replication. Replication happens by way of state or operations transfer which is like the well-known log shipping and mirroring options. State gets overwritten on the destinations whereas operations are re-executed on the destination. 

 

If there are ten customers to the current solution, each of them can get a tenant database dedicated to store the company’s business data. The knowledge about the shared application is then stored in a dedicated application database. When this solution is upgraded but the deployment mode is not changed, we can still have a single database that has one or more companies in it. We can also choose to extract the application tables to an application database but still have one business data database that has one or more companies in it. 

 

A security system must be in place to control which objects or tables a user can access within each database. We can specify the type of access each user has to these objects and tables and whether they are able to read, modify or enter data. Permissions can be granted at both the table level and the record level. It should be easy to query the permissions assigned to a user and to find out who can access a particular database. Typically, there are four different levels of security – Database, Company, Object, and Record. The first layer of security is database security when the credentials are checked even before the database is opened. Logins can be native to the database server or integrated with the operating system. The user is granted access only after the login is authenticated. Authorization is determined from role-based access control. A database can have several companies. Each company can use its own tables or share the tables with other companies. User’s access to companies is controlled by permissions sets. Object level security is determined by the permissions within the permission set. Permissions can be defined for all types of objects such as table data, table, pages, report, code units, data import and export units, query, and system. The lowest level of security is the record level security which limits the access that a user has to the data in a table. It is implemented by creating security filters on table data.

 

 

Switching modes:

This is a continuation of the articles on multitenancy with the most recent one linked https://1drv.ms/w/s!Ashlm-Nw-wnWhLZlxLBXSWQZmcirVA?e=ugcdca . This article focuses on migrating between deployment modes. 

It’s possible to convert a solution from a multitenant deployment mode to single-tenancy. This article describes some of the steps.

Data and applications are partitioned and isolated from one tenant to another in a multitenant application. The application data is in a dedicated application database, separate from the business data in the tenant database. The revert to a single-tenant deployment mode requires the revert of the separation of application data and business data and the merging of the two databases. A single-tenant can be folded from the multitenant mode and leaving the remaining tenants in the existing deployment deployment by adding the application tables to the relevant tenant database and leaving the original application database unchanged. If the application data is separated into a dedicated database, but it is not used in a multitenant deployment mode, the databases can still be merged.

Application code instances and configurations per tenant are still easily honored because as the applications instances are decreased the multitenant mode ensured that the infrastructure was shared. This made it easier to scale in or out and continues to work for migrating back into the single tenant mode.

Data, on the other hand, requires immense protection, isolation and compliance standards. It is not easy for the shared infrastructure to interpret the data belonging to a tenant. The multitenant solution provider can move or migrate the data one by one but it cannot make changes to syntax, semantics or format.

Even when the data is allowed to be read for operational purposes, the size of the data can vary from a few GB to TeraBytes. In such cases, even a migration of data from one table to another poses a long running operation that must be carefully planned and executed. It’s best to move a single record at a time in an idempotent manner and with failure detection and corrections so that when the operation succeeds for one single record, it can be repeated for all the records in the table.

When we refer to a tenant, we refer to it by the tenant ID but it is also possible to refer to them by the hostnames for the tenants in the deployment. A tenant specific sub-domain is setup in this case. The tenant host name, mytenant.myservice.com must be specified as an alternative in the tenant configuration.  The URL can specify the tenant ID and the tenant host name if we specify the hostnames as an alternative IDs for tenants. 

 

 

Multitenant Application example:

Let us take an example of an application that can be deployed in a single tenant mode and a multitenant mode. In a multitenant deployment, the information about the application is stored in a separate database. The customer’s data is stored in a separate business database, each of which is a tenant in the deployment. By separating application from data, the same stack can be deployed to many customers with centralized maintenance of the application and the isolation of each tenant.  The application database contains the tables that define an application.

If there are ten customers to the current solution, each of them can get a tenant database dedicated to store the company’s business data. The knowledge about the shared application is then stored in a dedicated application database. When this solution is upgraded but the deployment mode is not changed, we can still have a single database that has one or more companies in it. We can also choose to extract the application tables to an application database but still have one business data database that has one or more companies in it. In both scenarios, we have not migrated to multitenancy, but in the second scenario, we have prepared our solution so that we can move to multitenancy at a later point.

When a multitenant solution is deployed, a relationship is activated between the server instance by mounting the tenant to the instance. Similarly, to disconnect a tenant, the dismount needs to be performed. When the tenants are mounted, the tenant configuration is stored in the tenants table of the application database that is connected to the instance. If we connect additional server instances to the same application database, then the server instances will automatically inherit the tenant configurations from the application database. This means that the existing tenants will be automatically mounted to the new server instance.  If we mount or dismount an instance, then we have to perform the operation on one of the instances. The other instances will automatically detect and update the changes.

When we refer to a tenant, we refer to it by the tenant ID but it is also possible to refer to them by the hostnames for the tenants in the deployment. A tenant specific sub-domain is setup in this case. The tenant host name, mytenant.myservice.com must be specified as an alternative in the tenant configuration.  The URL can specify the tenant ID and the tenant host name if we specify the hostnames as an alternative IDs for tenants.