There are many times when you want to move to Azure but don’t have the liberty of re-platforming the database due to the dependency of applications. Don’t despair, there are still ways to move your workload. Azure has support for Oracle! We previously showed a way to almost run an Oracle Database as a Service on Azure, but if you have a line of business (LOB) application or an application that requires support for high availability, it is also possible:  Presenting Azure support for Oracle disaster recovery including Oracle Data Guard.

Oracle Support on Azure

Oracle supports running Oracle DB 12.1 Standard and Enterprise editions in Azure on virtual machine (VM) images based on Oracle Linux.   Oracle has guaranteed license mobility from on-premises to Azure. “These images are considered “Bring Your Own License” and as such you will only be charged for compute, storage, and networking costs incurred by running a VM. It is assumed you are properly licensed to use Oracle software and that you have a current support agreement in place with Oracle.

Licensing Details

Microsoft Azure is an ‘Authorized Cloud Environment.’   Under this program, “count two vCPUs as equivalent to one Oracle Processor license if hyper-threading is enabled, and one vCPU as equivalent to one Oracle Processor license if hyper-threading is not enabled.” Please note that unlimited license agreements (ULAs) may also be used.

High Availability

Any conversation about this topic requires us to look at the recovery time objective (RTO) and recovery point objective (RPO) of an application. In our application, this is how long the database can be down as well as the maximum amount of data loss that can be tolerated. Oracle defines well-known reference architectures based on RTO & RPO objectives.

Assuming we require comprehensive high availability (HA) and disaster recovery (DR), real-time failover and zero/near-zero data loss, an implementation using Oracle Data Guard will be pursued.

Oracle Data Guard

Data Guard is one offering from Oracle that ensures high availability, data protection, and disaster recovery for enterprise data. It uses a standby database (exact replica) to survive outages of any kind and data corruptions. This technology is available in Oracle Enterprise Edition.   Oracle also provides, as a separately licensed product, Oracle Active Data Guard; this technology allows for real-time data protection and disaster recovery and expands capabilities.

A few key features from Oracle regarding Oracle Data Guard are:

  • Fast redo transport for best recovery point objective (RPO), fast apply performance for best recovery time objective (RTO)
  • Fast failover to a standby database to maintain availability should the primary database fail for any reason
  • Automatic or automated (depending upon configuration) re-synchronization of a failed primary database, quickly converting it to a synchronized standby database after a failover occurs.
  • Reduction of planned downtime by utilizing a standby database to perform maintenance in a rolling fashion

Oracle Real Application Clusters (RAC) was also considered, but it is not supported on any cloud environment other than Oracle’s.

Oracle Data Guard on Azure

Microsoft recommends — for the best performance of Oracle DB production workloads on Azure — to be sure to properly size the VM image and use Managed Disks that are backed by Premium Storage.

Azure provides the M-Series virtual machines that are ideal for extremely large databases or other applications that benefit from high vCPU counts and large amounts of memory.

Azure also provides a Managed Disk offering called Ultra SSD that can scale performance up to 160,000 IOPS. IOPS are “an input/output performance measurement used to characterize computer storage devices.” Database performance is often constrained by the performance of the underlying storage; therefore, utilizing the Ultra SSD offering, you can be sure to minimize this concern.

Get Your Oracle Resources into the Cloud, and Fast! Request an Oracle Migration Assessment from AIS Today.

Another key concept to enable this high availability scenario is to leverage Azure availability sets for the virtual machines. Availability sets are a fundamental technology that makes our scenario possible.

  • Each virtual machine in your availability set is assigned an update domain and a fault domain by the underlying Azure platform.
  • Update domains allow for a virtual machine to recover before maintenance on another virtual machine is initiated on a different update domain.
  • Fault domains define the group of virtual machines that share a common power source and network switch.
  • Managed disks provide better reliability for availability sets by ensuring that the disks of VMs in an availability set are sufficiently isolated from each other to avoid single points of failure.

Availability zones, an alternative to availability sets, expand the level of control you have to maintain the availability of the database VMs. With Availability Zones, Azure offers a 99.99% VM uptime Service Level Agreement (SLA).

Lastly, Azure Site Recovery (ASR) provides an additional level of disaster recovery via its ability to orchestrate replication, perform disaster recovery testing, and run failovers and failback; ASR is fully compatible with Oracle Data Guard on Azure.

A logical architecture using a virtual network (VNET) and subnets (isolation & projection), Oracle DB VMs, premium storage, and Oracle Data Guard is presented below.

Diagram of VM architecture

AIS recently participated in a joint proof of concept (POC) where this architecture was deployed with M128 VMs and premium storage and achieved the following goals:

  • ~35TB Database (Oracle Table Limit)
  • ~30-45 second failover under user load of 5000- 15,000 concurrent users with Zero Data Loss
  • Active Data Guard Running in Max Availability (SYNC, Successful Failover)
  • Can handle both planned and unplanned failovers
  • Achieve 120,000 IOPS
  • Can be further secured using Network Security Groups (NSG) and Application Security Groups (ASG)

Looking to Retire Your Oracle Hardware and Migrate to the Cloud? Contact AIS Today to Discuss Your Options.

Kubernetes logoI recently built a machine learning model, trained it, and explored the implications of deploying it using KubernetesMachine learning trains a program to recognize patterns in data so when new data is provided, it can make predictions based on what it’s learned. Kubernetes is a container orchestrator that automates the deploying and scaling of containers. Packaging a machine learning program and deploying it on Kubernetes has the potential to help our customers with their increasingly complex machine learning needs.

First, I determined what I wanted to accomplish with machine learning, the tools I needed, and how to put them together.  Teaching a machine how to recognize imagery is fascinating to me, so I focused on image recognition. I found the PlanesNet dataset on Kaggle — a dataset focused on recognizing planes in aerial imagery.  The dataset would be simple enough to implement but have good potential for further exploration.

To build a proof of concept I used TensorFlow, TFLearn, and Docker.  TensorFlow is an open-source machine learning library and TFLearn is a higher-level TensorFlow API which aids in writing less code to get started.  TFLearn is a Python library, so I wrote my proof of concept in Python.  Docker was used to package the program into a container to run on Kubernetes.

Machine Learning

planes data set

The PlanesNet dataset has 32,000 color images, each at 20px by 20px.  There are 8,000 images classified as “plane” and 24,000 images classified as “no-plane.” Reading in the dataset and preparing it for processing was straightforward using Python libraries like Pillow and NumPy.  Then I broke the dataset up into a training set and a testing set using the train_test_split function in the sklearn Python library.  The training set was used to build the model weights while the testing set validated how well the model was trained.

Neural Network

One of the most complex parts was how to design my simple neural network. A neural network is broken into layers starting with the input layer, then one or more hidden layers, ending with the output layer.

Creating the input layer, I defined its shape, preprocessing, and augmentation. The shape of the PlanesNet data is a 20px by 20px by 3 colors matrix. The preprocessing allowed me to tweak preparing the data for both training and testing while the augmentation allowed me to perform operations (like flipping or rotating images) on the data while training.

Because I was working with imagery, I chose to use two convolutional hidden layers before arriving at the output layer. Convolutional layers perform a set of calculations on each pixel and its surrounding pixels, attempting to understand features (i.e., lines, curves) of an image. After my hidden layers, I introduced a dropout percentage rate, which helps to reduce overfitting.

Next, I added a readout layer which brought my neural network down to the number of expected outputs.  Finally, I added a regression layer to specify a loss function, optimizer, and learning rate.  With this network, I could now train it over multiple iterations, called epochs, until I reached the desired level of prediction accuracy.

diagram of a simple neural network

Simple Neural Network

Using this model, I could now predict if an image was of an airplane. I loaded the image, called the predict function and viewed the output.  The output gave me a percentage of likelihood that the image was an airplane.

Exploring Scaling

Now that I had my simple neural network for training and predicting, I packaged it into a Docker container so it could be run on more than my single computer.  As I examined the details of deploying to Kubernetes, two things quickly became apparent.  First, my simple neural network would not train across multiple container instances.  Second, my prediction program would scale well if I wrapped it in a web API.

For my simple neural network, I had not implemented anything to split a dataset, train multiple containers, and bring the results back together.  Therefore running multiple instances of my container using Kubernetes would only provide the benefit of choosing the container with the highest accuracy model. (The dataset splitting process along with the input augmentation causes each container to have different accuracies.)  To have multiple containers which coordinate learning in my simple neural network would require further design.

diagram of the container model

My container’s prediction program executes via the command line but wrapping it in a web API endpoint it would make it easier to use.  Since each instance of the container has the trained model, Kubernetes could scale up or down the number of running instances of my container to meet the demand of the web API endpoint.  Kubernetes also provides a method for rolling out updates to my container if I further train my network model.

Conclusion

This was an excellent exercise in building a machine learning model, training it to predict an airplane from aerial imagery, and deploying it on Kubernetes. Additional applications could include expanding the dataset to include different angles of planes along with recognizing various specific types of planes. It would also be beneficial to re-design the neural network to benefit from Kubernetes scaling to the training needs. Using Kubernetes to deploy a prediction API-based on a trained model is both beneficial and practical today.

PowerApps logoI’m working on a project with a straightforward requirement that’s typically solved with out-of-the-box PowerApps platform features. The customer needs to capture employment application data as part of their online hiring process. In addition to entering standard employment data, applicants enter a typical month’s worth of budgeted expenses to paint a clear financial picture which serves as one factor in their suitability for employment.

The Requirements

The solution consists of an Application entity and a related Expense entity to track multiple expenses per Application. The Application entity needs to capture aggregate expense values based on expense categories. The Expense entity stores the institution or business receiving the payment, the Expense Type (lookup), the monthly payment in dollars, and the balance owed in dollars. Expense Types are associated with “Expense Categories.” An Expense Category is associated with one or more Expense Types. The Application entity tracks 15-20 aggregate expense values that need to be calculated from the individual expense entries.

For example, the expense types “Life Insurance” and “Auto Insurance” are both associated with the expense category “Insurance.” The Application entity has an aggregate field called “Total Insurance Expenses” to capture the sum of all monthly insurance-related expenses.

The basic entity model is shown below:

Diagram of entity model

To summarize all that detail (which is probably hard to follow in paragraph form), the essential requirements are:

  • Capture standard employment data for applicants
  • Capture monthly budgeted expenses associated with an expense type and category
  • Calculate 15-20 aggregate expenses based on expense category
  • The solution must accommodate the addition of new expense categories

The aggregate fields on the Application entity fall into one of four categories: 1) a monthly payment total by expense category, 2) an outstanding balance total by expense category, 3) a monthly payment total across all categories, and 4) a total outstanding balance across all categories.

The breakdown for each of the expense categories and their associated aggregations on the Application entity can be depicted as such:

Expense Category Category Payment Rollup Category Balance Rollup All Payments Rollup All Balances Rollup
Automobile Total Automobile Expenses Total Auto Balances Owed Total Monthly Expenses Total Outstanding Debt
Credit Card Total Credit Card Expenses Total Credit Card Balances Owed Total Monthly Expenses Total Outstanding Debt
Food/Clothing Total Food/Clothing Expenses Total Monthly Expenses
Housing Total Housing Expenses Total Housing Balances Owed Total Monthly Expenses Total Outstanding Debt
Insurance Total Insurance Expenses Total Monthly Expenses
Medical/Dental Total Medical Expenses Total Monthly Expenses
Other Debt Total Other Debt Expenses Total Other Debt Balances Owed Total Monthly Expenses Total Outstanding Debt
Utilities Total Utility Expenses Total Monthly Expenses

The table shows that some expense categories require a total of four aggregate calculations, whereas others only require two aggregate calculations. The calculations should occur when an Expense is created, the values for a monthly payment or balance owed change, an Expense is deactivated/activated (state change), or when an Expense is deleted. “Total Monthly Expenses” is calculated for all expense entries. Only four categories require the category balance and total outstanding balance calculations.

Platform Limitations

Maximum Number of Rollup Fields

Dynamics 365 only allows for a maximum of 10 rollup fields on any given entity — these are fields that take advantage of the “Rollup” field type customized in the solution, the values for which are automatically calculated by the platform on a predetermined interval, e.g., every 12 hours.

One option to overcome this limitation — only available in on-premises Dynamics 365 implementations — is to modify the maximum number of rollup fields per entity in the MSCRM_CONFIG database. There are rare circumstances wherein modifying table values in this database are beneficial. However, given the possibility of a disaster recovery situation in which Dynamics 365 needs to be reinstalled and/or recovered, any modifications made to the MSCRM_CONFIG database could be lost. Even if an organization has well-documented disaster recovery plans that account for these modifications, there’s always a chance the documented procedures will not be followed, or steps possibly skipped.

Another consideration is the potential to move to the cloud. If the customer intends to move their Dynamics 365 application to the cloud, they’ll want to ensure their solution remains on a supported path, and eliminate the need to re-engineer the solution if that day comes.

Rollup Calculation Filters

Rollup fields in Dynamics 365 are indeed a powerful feature, but they do come with limitations that prevent their use in complex circumstances. Rollup fields only support simple filters when defining the rollup field aggregation criteria.

To keep this in the context of our requirements, note above that the Expense Type and Expense Category are lookup values in our solution. If we need to calculate the sum of all credit card expenses entered by an applicant, this is not possible given our current design, because the Expense Type is a lookup value on the expense entry. You’ll notice that when I try to use the Expense Type field in the filter criteria for the rollup field, I’m only given the choices “Does not contain data” and “Contains data.” Not only can’t I use actual values of the Expense Type, but I can’t drill down to the related Expense Category to include it in my aggregation filter.

Screenshot of test rollup screen

Alternatives

The limitations above could be overcome by redesigning our solution, for example, by choosing to configure both the Expense Type and Expense Category fields as Option Sets instead of lookups, along with some sophisticated Business Rules that appropriately set the Expense Category based on the selected Expense Type. That’s one option worth considering, depending on the business requirements with which you’re dealing. We could also choose to develop a code-heavy solution by writing plugin code to do all these calculations, thus side-stepping the limitation on rollup fields and accommodating the entity model I’ve described.

The Solution

Ultimately, however, the customer wants a solution that allows them to update their expense tracking requirements without needing developers to get the job done. For example, the organization may decide they no longer want to track a certain expense category or may want to add a new one. Choosing to create entities to store the necessary lookup values will afford them that kind of flexibility. However, that still leaves us the challenge of calculating the aggregate expense values on the Application entity.

The final solution may still require the involvement of their IT department for some of the configuration steps but ideally will not require code modifications.

Lookup Configuration

The first step toward our solution is to add four additional fields to the Expense Category entity. These four fields represent the four aggregation categories described above: 1) Category Payment Rollup, 2) Category Balance Rollup, 3) Payments Rollup (for all categories), and 4) Balances Rollup (for all categories).

These fields will allow users to define how aggregate values are calculated for each Expense Category, i.e., by identifying the target fields on the Application entity for each aggregation.

Screenshot of Expense Category in Dynmaics 365

Custom Workflow Activity

The next step is to write a custom workflow activity to perform the aggregate calculations described above. Custom workflow activities present several benefits to the customer, primarily centered on the ease of configuration within the Dynamics 365 UI itself  (run asynchronously, run on-demand, and/or when specific events occur on the target record type, e.g., create, update, delete, or state change). Custom workflow activities can accept user-defined parameters configured in the workflow definition.

This means that — as you might have guessed — the custom workflow activity can be written in such a way to allow users to add new Expense Categories so that the aggregate calculations “just work” without requiring code modifications or changes to the workflow configuration in the solution.

Here’s the custom workflow activity class that runs the calculations followed by the workflow definition. As you can see below, I’ve included the Application and Expense Category fields as required input parameters for the workflow activity. (I’ll likely refactor this solution to accept the four fields as inputs, but for now, this gets the job done. Thanks to my good friend, Matt Noel, for that suggestion.) Further down, you’ll notice that for each aggregate field, we run a custom fetch query configured appropriately to perform the required calculation.

using Microsoft.Xrm.Sdk;
using Microsoft.Xrm.Sdk.Client;
using Microsoft.Xrm.Sdk.Query;
using Microsoft.Xrm.Sdk.Workflow;
using System;
using System.Activities;

namespace Project.Workflow.Activities
{
    public class CalculateExpenses : CodeActivity
    {
        #region Input Parameters

        [RequiredArgument]
        [Input("Application")]
        [ReferenceTarget("new_application")]
        public InArgument<EntityReference> Application { get; set; }

        [RequiredArgument]
        [Input("Expense Category")]
        [ReferenceTarget("new_expensecategory")]
        public InArgument<EntityReference> ExpenseCategory { get; set; }

        private readonly string _categoryPaymentRollupField = "new_categorypaymentrollup";
        private readonly string _allPaymentsRollupField = "new_paymentsrollup";
        private readonly string _categoryBalanceRollupField = "new_categorybalancerollup";
        private readonly string _allBalancesRollupField = "new_balancesrollup";

        #endregion

        protected override void Execute(CodeActivityContext executionContext)
        {
            var tracer = executionContext.GetExtension<ITracingService>();
            var context = executionContext.GetExtension<IWorkflowContext>();
            var serviceFactory = executionContext.GetExtension<IOrganizationServiceFactory>();
            var orgService = serviceFactory.CreateOrganizationService(null);
            var orgContext = new OrganizationServiceContext(orgService);

            string[] _rollupFields =
            {
                _categoryPaymentRollupField,
                _allPaymentsRollupField,
                _categoryBalanceRollupField,
                _allBalancesRollupField
            };

            var expenseCategory = GetEntity(orgService, ExpenseCategory.Get(executionContext), _rollupFields);
            var applicationRef = Application.Get(executionContext);
            var application = new Entity("new_application")
            {
                Id = applicationRef.Id
            };

            // Set the Category Payment Rollup
            if (expenseCategory.GetAttributeValue<string>(_categoryPaymentRollupField) != null)
            {
                var paymentRollup = expenseCategory.GetAttributeValue<string>(_categoryPaymentRollupField);
                application[paymentRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, false, true));
            }

            // Set the rollup for all Monthly Payments
            if (expenseCategory.GetAttributeValue<string>(_allPaymentsRollupField) != null)
            {
                var allPaymentsRollup = expenseCategory.GetAttributeValue<string>(_allPaymentsRollupField);
                application[allPaymentsRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, false, false));
            }

            // Set the rollup for Category Balances
            if (expenseCategory.GetAttributeValue<string>(_categoryBalanceRollupField) != null)
            {
                var categoryBalanceRollup = expenseCategory.GetAttributeValue<string>(_categoryBalanceRollupField);
                application[categoryBalanceRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, true, true));
            }

            // Set the rollup for all Category Balances 
            if (expenseCategory.GetAttributeValue<string>(_allBalancesRollupField) != null)
            {
                var allBalancesRollup = expenseCategory.GetAttributeValue<string>(_allBalancesRollupField);
                application[allBalancesRollup] = new Money(GetExpenseAggregate(orgService, application.Id, expenseCategory.Id, true, false));
            }

            // Execute the update on the Application rollup fields 
            try
            {
                //trace 
                orgService.Update(application);
            }
            catch (Exception e)
            {
                //trace
                throw new InvalidPluginExecutionException("Error updating Application: " + e.Message);
            }
        }

        private static Entity GetEntity(IOrganizationService service, EntityReference e, params String[] fields)
        {
            return service.Retrieve(e.LogicalName, e.Id, new ColumnSet(fields));
        }

        private decimal GetExpenseAggregate(IOrganizationService service, Guid applicationId, Guid expenseCategoryId, bool balanceRollup, bool includeCategory)
        {
            var sum = 0m;

            var aggregateField = balanceRollup ? "new_balanceowed" : "new_monthlypayment";

            var fetchXML = @"<fetch distinct='false' mapping='logical' aggregate='true' >" +
                  "<entity name='new_expense' >" +
                    "<attribute name='" + aggregateField + "' aggregate='sum' alias='sum' />" +
                    "<filter type='and' >" +
                      "<condition attribute='statecode' operator='eq' value='0' />" +
                      "<condition attribute='new_applicationid' operator='eq' value='" + applicationId + "' />" +
                    "</filter>";

            var endFetch = "</entity></fetch>";

            if (includeCategory)
            {
                var categoryFetch = @"<link-entity name='new_expensetype' from='new_expensetypeid' to='new_expensetypeid' link-type='inner' alias='ExpenseType' >" +
                      "<link-entity name='new_expensecategory' from='new_expensecategoryid' to='new_categoryid' link-type='inner' alias='ExpenseCategory' >" +
                        "<filter type='and' >" +
                          "<condition attribute='new_expensecategoryid' operator='eq' value='" + expenseCategoryId + "' />" +
                        "</filter>" +
                      "</link-entity>" +
                    "</link-entity>";

                fetchXML += categoryFetch + endFetch;
            }
            else
            {
                fetchXML += endFetch;
            }

            FetchExpression fetch = new FetchExpression(fetchXML);

            try
            {
                EntityCollection aggregate = service.RetrieveMultiple(fetch);

                foreach (var c in aggregate.Entities)
                {
                    if (((AliasedValue)c["sum"]).Value is Money)
                    {
                        sum = ((Money)((AliasedValue)c["sum"]).Value).Value;
                        //tracer.Trace("Sum of payments is: {0}", sum);
                    }
                }
            }
            catch (Exception e)
            {
                //tracer.Trace(e.Message);
                throw new InvalidPluginExecutionException("Error returning aggregate value for " + aggregateField + ": " + e.Message);
            }

            return sum;
        }
    }
}

Workflow definition:

Powerapps workflow screenshot

Configuration of custom inputs:

Configuring custom inputs

Testing

After building and registering my workflow assembly, I created expense entries for all expense types ensuring that all expense categories were represented. The following images depict the successful aggregation of payments and balances:

Screenshot of expense output

Screenshot of debt output

Conclusion

Custom workflow activities are a powerful tool that balances the need for a highly maintainable solution after deployment, with complex requirements that need a coded solution. The design gives end users the flexibility to adapt their data collection needs over time as their requirements change, and they can do so with little or no involvement from IT.

As I mentioned above, an alternative approach to this requirement could involve writing a plugin to perform the calculations. This approach would still require some entity-based configurations for flexibility but would suffer from limited end-user configuration needed if or when requirements change. I can also update the custom workflow activity to accept the four aggregate fields as optional arguments to the workflow. Doing so would enable users to run separate workflow processes for each expense type/category, giving them additional configuration control over these automated calculations.