Art of BI | OBIEE.nl

Category Archives: Art of BI

October 13, 2020 Art of BI

Availability Groups Supporting Distributed Transactions

By default, SQL Server Availability Groups (AG) do not fully support Distributed Transactions – even if the AG’s underlying Windows Servers are configured for Distributed Transaction support.

Typically, if a system is required to support Distributed Transactions, the only requirement is to enable and configure the Windows Servers’ local MS-DTC to support XA Transactions. “XA” is a two-phase commit protocol used to ensure data integrity for a single transaction shared between multiple relational databases. However, with SQL Server AG, there are additional requirements to enable Distributed Transaction support and high availability.

Prior to SQL Server 2016 SP2, it was not recommended or supported to implement AGs on systems which supported Distributed Transactions within databases on the same Instance. Since SQL Server 2016 SP2, distributed transactions are fully supported in all databases – including those involved in AG and within databases on the same Instance.

If an AG is not configured to support Distributed Transactions (this includes unsupported SQL Server versions) – potential side effects can go unnoticed…. until the AG is moved to a different Replica. Here is a real-life example that happened during a “planned” SQL Server 2017 AG failover in which Local DTCs were used and the AG was not configured for Distributed Transactions. The database names and server names have been genericized:

All AG databases, including the Shipping database, were reporting “Synchronized” just prior to a planned failover. However, a DTC-enlisted transaction was made on ReplicaA_Instance immediately prior to the failover. When the AG failed over to ReplicaB_Instance, all standard transactions were either rolled forward or back successfully in each AG database. Then the recovery of in-doubt Distributed Transactions were invoked. At that point, ReplicaB_Instance went to check in its local MS DTC for the Distributed Transaction result….but since MS DTC on ReplicaB_Instance doesn’t have any knowledge of the Distributed Transactions which were initiated from ReplicaA_Instance – ReplicaB_Instance generated this error:

Message: SQL Server detected a DTC/KTM in-doubt transaction with UOW {F37CAD38-899F-4469-9FAF-45BF1C67FD9D}.Please resolve it following the guideline for Troubleshooting DTC Transactions.

Then automatically (and immediately) the Shipping AG database was put in Suspect mode.

It is surprising we do not see this issue more often! Luckily there are a handful of easy actions which will help prevent this issue from happening going forward:

Ask the Application Owner if Distributed Transactions are required to be supported when configuring an AG.

There is already a myriad of questions a DBA must ask when architecting a reliant SQL Server environment for an application. Make sure to add this one to your list of questions!
If transactions can span multiple databases, even on the same Instance, they are Distributed Transactions!

Configure a Clustered MS-DTC as a Resource in the WSFC.

To make this highly available, configure this Resource onto Clustered Shared Storage

Disable the Local MS-DTC on all Replica Windows Servers.

Enable the AG for Distributed Transactions with the “DTC_SUPPORT = PER_DB” option.

In SQL Server 2016 SP2, this option must be set during the CREATE AVAILABILITY GROUP sequence. This option is not enable-able after the AG is created.
In SQL Server 2017 or beyond, this option can be enabled during and after the AG is created

Configure the in-doubt xact resolution option if you are using a Local DTC.

To presume commit any in-doubt MS DTC transactions, set to 1
To presume abort any in-doubt MS DTC transactions, set to 2
By default, this is set to 0, which requires the DBA to manually resolve in-doubt transactions as the affected database will go into Suspect Mode.
It is recommended to have this configuration option consistently set across all Instances which support Distributed Transactions

Here are the effects of configuring a Clustered MS DTC and configuring the DTC_SUPPORT = PER_DB AG option:

In order to participate in distributed transactions, an instance of SQL Server enlists with a DTC. Normally the instance of SQL Server enlists with DTC on the local server, but in the case of an AG, the SQL Server Instance should enlist with a Clustered MS DTC. Each instance of SQL Server creates a resource manager with a unique resource manager identifier (RMID) and registers it with the Clustered DTC. In the default configuration, all databases on an instance of SQL Server use the same RMID. Also in the default configuration, during AG failover – the new Primary Replica will have a different RMID (because it is running on a new Instance), therefore the new Primary Replica’s Instance does not have any knowledge of prior in-doubt distributed transactions initiated on the old Primary Replica’s Instance.

To support distributed transactions during AG failover movement, each database should act as a separate resource manager and must have a unique RMID. When an availability group has DTC_SUPPORT = PER_DB, SQL Server creates a resource manager for each database and registers with the Clustered DTC using a unique RMID. In this configuration, the database is a resource manager for DTC transactions. It is very important to know that the RMID follows the database during an AG failover! This is the secret sauce which allows the resolution of in-doubt transactions!

Other helpful tips to ensure trouble-free AG failovers:

Replicas should be in Synchronous Mode
Validate the AG is Healthy and Synchronized between the current Primary and Secondary Replica(s) immediately prior to moving the AG to a different Replica
Validate the AG is Healthy and Synchronized after the failover (Databases incurring a large number of transactions may take several minutes to become synchronized.)
If any issues are detected, consult the SQL Errorlog immediately

In short review, if your SQL Server 2016 SP2 (or higher) AG environment is required to support Distributed Transactions, ensure the following configurations are enabled:

Enable Clustered MS DTC

Put this Resource on Clustered Shared Storage

Disable Local MS DTC

If this is not done, at least set the in-doubt xact resolution option

Enable the “DTC_SUPPORT = PER_DB” option in the AG

In SQL 2016 SP2, you must set this option while the AG is being created
In SQL 2017 and beyond, this AG option can be set at any time

If you’re looking for support with SQL Server availability groups, or other initiatives around SQL Server please contact us.

The post Availability Groups Supporting Distributed Transactions appeared first on Datavail.

October 8, 2020 Art of BI

Oracle EBS 12.2.10 Hits the Stands: What You Need to Know

The latest version of Oracle E-Business Suite, 12.2.10, is hot off the presses—it was just released on September 25. So what’s new with Oracle EBS 12.2.10, and what do you need to know about this latest version?

What is Oracle EBS 12.2.10?

EBS 12.2.10 is Oracle’s most recent iteration of the E-Business Suite collection of software applications for enterprise resource planning, customer relationship management, and supply chain management. The previous version, EBS 12.2.9, was released last year in August 2019.

This release schedule is in line with Oracle’s EBS roadmap, which aims to deliver a new EBS patch on an annual basis. Oracle also plans to issue an upcoming “12.X” version of EBS which will be supported through at least 2030, although details at this point are hazy.

What’s New in Oracle EBS 12.2.10?

With that said, what are the new features in EBS 12.2.10?

EBS 12.2.10 is a cumulative patch, which means that it includes both new features and updates that were part of previous 12.2 patches. According to Oracle product management director Elke Phelps, many of the new EBS 12.2.10 features were requested and voted on by EBS customers, while others have been in the pipeline as part of Oracle’s overarching goals of improving operational efficiency and modernizing the user interface.

Just a few of the most anticipated new features of EBS 12.2.10 are:

Order management: Oracle iStore uses Enterprise Command Center technology to “provide a modern user experience in B2B shopping.” The Performance Evaluation Dashboard in the Oracle Incentive Compensation Command Center lets administrators view employees’ sales attainment and performance across different roles, plans, and periods.
Logistics:2.10 has made significant updates to the Receiving Dashboard, Reservations Dashboard, and Reservations HTML UI. For example, the Receiving Dashboard allows users to track material by “Expected Date,” track pending inspections, manage pending putaways, and much more. Oracle Warehouse Management (WMS) has added various enhancements, e.g. creating optimized travel paths for pickers to finish their work with a single pass through the warehouse.
Procurement: As with Order Management, Oracle iStore now has “an enhanced consumer-like shopping experience”. The Employee Shopping Tracker in the Oracle Procurement Command Center helps improve catalog content by tracking employees’ shopping searches.
Projects:2.10 includes more accurate financial management for U.S. federal trading partners, with support for G-Invoicing (government invoicing).

The 12.2.10 features listed above really are just the tip of the iceberg—there are also various improvements to asset lifecycle management, human capital management, financial management, and more. For the full list of new features in EBS 12.2.10, check out Oracle’s document “Announcing Oracle E-Business Suite: Innovations in 2020.”

Upgrading to Oracle EBS 12.2.10

Keeping your EBS deployment up-to-date is crucial—especially if you’re using Oracle EBS 12.1, which is scheduled to end Premier Support next year. (Still using EBS 12.1? Check out our white paper “Time is Running Out for Oracle EBS 12.1 – Here’s What You Can Do.”)

If you’re already on EBS 12.2, you can install 12.2.10 through My Oracle Support as Patch 30399999. The good news is that 12.2.10 is an online patch, i.e. you can keep your EBS deployment running while it installs. You can find detailed instructions for applying the 12.2.10 patch here.

If you’re not yet on EBS 12.2, however, you won’t yet be able to install 12.2.10 by itself (and it’s high time that you start planning for an upgrade). You’ll first have to upgrade to a EBS 12.2.x version, and then install the 12.2.10 patch.

Looking for some help? As an Oracle Platinum Partner with 17 different specializations in Oracle products, Datavail has helped countless clients enjoy the latest features and bug fixes by upgrading their Oracle deployments. Get in touch with Datavail’s team of Oracle experts today to chat about your business needs and objectives.

The post Oracle EBS 12.2.10 Hits the Stands: What You Need to Know appeared first on Datavail.

October 6, 2020 Art of BI

ZeroLogon Vulnerability and EPM On-Premises and Cloud

I don’t normally write about Microsoft vulnerabilities and related patches, but this one is important for all Oracle EPM/Hyperion instances…whether on-premises or in Oracle’s EPM SaaS Cloud.

A little background: Vulnerabilities are ranked on a score from 0.1 to 10.0. What I’m about to discuss here is a 10.0, which is the most dangerous score.

The official designation of this particular critter is “CVE-2020-1472”. Independent security research firms, such as Secura, refer to it as ZeroLogon. Microsoft issued a patch for it in August 2020’s “Patch Tuesday”, but the extent of the problem wasn’t fully known at the time. If you want to read the gory details, you can check out Secura’s white paper on the subject. I’ll summarize, in brief:

The vulnerability allows anyone having access to the network to become a Windows Domain Administrator. You don’t even need network credentials if you stroll into the office and plug a device into an Ethernet port. Remote workers, of course, often have the access required. The point being that once the attacker runs the exploit and elevates himself to a domain admin or creates a new domain admin account with a known password, he can cause all sorts of mischief with far-reaching consequences throughout the organization.

Now let’s talk about EPM, starting with on-premises and then moving on to Oracle’s EPM SaaS Cloud (PBC, FCC, etc).

Microsoft Active Directory (“MSAD”) is ubiquitous within the on-premises EPM space. The vast majority of EPM implementations I’ve supported, installed, or health-checked use MSAD for end-user authentication. Hyperion Shared Services and the various EPM components connect to a Windows Domain Controller in order to authenticate end-user login attempts.

Disclaimer: the following paragraph contains theoretical conjecture. We won’t know the effects for sure until an non-patched system is attacked.

Our fictional attacker, who exploits ZeroLogon, can completely break this. Worse, the attacker could kick the EPM servers out of the domain, making it hard to hop on the EPM servers and troubleshoot why nobody can login.

I have worked with a few customers who use alternatives to Microsoft for end-user authentication, such as Novell eDirectory or other LDAP solutions. By and large, though, there can be a Microsoft Windows Domain lurking somewhere within the network.

They key takeaway here is that EPM system stakeholders should inquire with the IT department and confirm the Domain Controllers have had the August 2020 Microsoft patches applied. I’ve noticed it is a mixed bag “out in the wild”; some organizations patch immediately, while others lag behind…especially during financial Quarter-End or Year-End change freezes.

Now let’s talk Cloud briefly.

Oracle’s EPM SaaS Cloud products for Consolidation, Planning, Account Rec, etc. all share one thing in common: EPMAutomate.

EPMAutomate is the Cloud’s command-line utility used for a variety of tasks: upload data to the Cloud, run it through Data Management, fire off Calculation Rules, download reports and audit logs, and more. EPMAutomate resides on a server under the customer’s control, either on-premises or in a hosted cloud such as AWS, Azure, OCI, etc. The vast majority of EPMAutomate implementations I’ve seen happen to sit on MS Windows servers. (It can be hosted on Linux, and sometimes I witness that variation.)

If EPMAutomate is hosted on MS Windows, and that machine happens to be joined to the MS Windows Domain…well, there’s a possibility your EPM Cloud automation might stop working someday if an intruder bricks your network account or kicks the EPMAutomate host server out of the domain. (Again, I use the word possibility until we see the fallout when it eventually happens.)

2020 has been an awful year thus far, so please do your part not to make it…awful-er. Insist your network domain controllers get patched for “CVE-2020-1472”, included in August 2020 Microsoft Patch Tuesday.

That’s it for this post, but if you’re looking for more reading on EPM 11.2, be sure to check out my white paper, “It’s the Eleventh Hour for Hyperion 11.1.2.4 — Here’s What to Do.”

Cross-posted from EPM On-Prem Pro. Read the original post here.

The post ZeroLogon Vulnerability and EPM On-Premises and Cloud appeared first on Datavail.

October 1, 2020 Art of BI

Which Containerization Option Should You Choose for MongoDB?

Containers have a lot going for them in software development. They make it easy to scale application components, fix and update code, and manage complex programs. Kubernetes and Docker are two containerization options that are available for MongoDB. Both offer excellent feature sets and benefits for application development, so choosing between the two can be difficult. Here’s a rundown of the advantages of Docker and Kubernetes, as well as the use cases they’re best suited for.

Kubernetes on MongoDB

Kubernetes is an open-source container orchestration technology that is focused on container management in clustered database environments. You group containers together in a Kubernetes Pod, and can easily manage their resources, updates, and other functionality.

Kubernetes excels at simplifying container management for complex applications that span multiple servers. It offers high availability, automated scaling, and efficient load-balancing on MongoDB. You have tight control over networking and security functions, and your development team can easily work within complex environments. Migration to and from the public cloud, private cloud, hybrid cloud, and on-premises infrastructure is much easier for these applications.

Docker on MongoDB

Docker is an open-source container file format focused on portable and self-sufficient containers. Unlike Kubernetes, Docker is used on a single node rather than a cluster. Many developers turn to Docker as their container option of choice. It uses the Docker Engine runtime environment, which offers significant flexibility in working with many types of development machines. The Docker Hub is a container registry that simplifies the process of storing images. It works best for simple applications that do not require the resources of multiple MongoDB servers. While Docker Swarm offers some features for working on a cluster, this technology falls short in this area.

Good News – You Can Use Both Container Options for MongoDB Applications

Since the focus of Kubernetes and Docker on MongoDB is different, you’re not forced into deciding between the two of them. Leveraging the advantages of both is possible. They work well together and apart, giving your software development team the container flexibility they need for modern applications.

For example, you can use Docker to package the containerized application and use it with individual MongoDB servers. When the software begins to grow in complexity and resource requirements, you can bring Kubernetes in to orchestrate the containers and manage scaling across a MongoDB cluster.

When Kubernetes and Docker work in tandem, they deliver the following benefits:

Improve application availability
Implement load balancing and application scaling
Use an open standard for application packaging
Quickly develop and manage containers and images

No matter which containerization option for MongoDB you choose, Datavail is here to help. Our MongoDB specialists have extensive experience with implementing and managing containerized applications, and we’ll assist you with making the right decision for your organization.

MongoDB on Kubernetes: How to Add Containerization Tech

Containers make it possible to achieve identical application performance no matter where you deploy them. This feature is attractive in complex development and production environments involving a wide array of infrastructure.

The post Which Containerization Option Should You Choose for MongoDB? appeared first on Datavail.

September 30, 2020 Art of BI

Adobe Flash Player EOL and On-Premises EPM 11.1.2.x

If you are already live on Oracle EPM/Hyperion 11.2.x (you brave soul!) or in the Oracle EPM SaaS cloud, this post isn’t for you.

EPM 11.1.2.3.500 through 11.1.2.4.x both have dependencies upon Adobe Flash Player on the end-user side for Hyperion Calculation Manager and Hyperion Planning. Hyperion Financial Close/Account Rec in 11.1.2.3 also uses Flash.

Adobe announced earlier that End of Life for Flash Player is December 31, 2020. Not far away now!

So, let’s review the scenarios for on-premises EPM:

For Hyperion Planning 11.1.2.4, patch 31365862 – 11.1.2.4.010 takes care of this.
For Hyperion Calculation Manager 11.1.2.4, patch 28557058 – 11.1.2.4.014 takes care of this.
For 11.1.2.3 and older, there is no solution other than upgrading or moving to the cloud.
For 11.2.0.0 and higher, the solution is already baked into the base release and there is no need to patch.

What does it mean if you don’t patch before the deadline, or you’re on an older release and can’t patch at all?

Adobe has stated very clearly that the ability to download Flash Player will be removed once the support deadline of Dec 31, 2020 has passed. Neither the latest version nor older versions will be available to download. Furthermore, no new security patches will be issued.

I haven’t seen this in writing but expect Firefox to quickly flag the Flash Player extension as vulnerable in January 2021. I wouldn’t be surprised at all if the extension gets disabled without the option to re-enable it.

Adobe has further stated that a Flash Player installer downloaded from any 3rd party site will be considered “Unauthorized”.

If you’re on 11.1.2.4 and haven’t applied the patches I mentioned above, my recommendation would be to patch and regression test now before you enter a fiscal 2020 4th Quarter change freeze.

There’s an added benefit if you’re working directly on a server for testing purposes: Flash Player often isn’t installed on MS Windows Server 2012 and is hard to get. The latest download page on Adobe’s website flags your browser as coming from Windows 8 and shows a Knowledge Base article instead of letting you download the installer. If you need to get into the Calculation Manager Rules or Variable designers, the browser wants to invoke Flash Player and the page hangs. This exact issue hit me yesterday, which led me down the road of investigating these patches.

A quick reminder to carefully read the patch READMEs for the two patches I listed. Planning 11.1.2.4.010 contains a new optional application property, some files need to be copied over to the Financial Reporting server, and the CalcMgr patch needs to be installed on multiple machines in a typical distributed environment.