Tag Archives: blog
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.
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.
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.
Read This Next
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.
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.
Cross-posted from EPM On-Prem Pro. Read the original post here.
For more EPM wizardry, check out my on-demand webinar, “EPM 11.2: Lessons Learned and 2021 Preparedness.”
The post Adobe Flash Player EOL and On-Premises EPM 11.1.2.x appeared first on Datavail.
Making Your SQL Server to MySQL Migration as Smooth as Possible
Sometimes your first choice of a relational database management system ends up being a poor fit for a new project or your long-term goals. If you need to migrate from SQL Server to MySQL, you can improve your chances of a successful migration by going through a few preparation steps.
Analyze Your Current Database Performance
Establish a performance baseline for your SQL Server. You need that data to compare to the MySQL deployment, so you know if you’re achieving the expected performance goals and improvements.
Audit Your SQL Server Databases to Improve Data Quality
Deduplicate your databases, get rid of junk data, and generally clean up the SQL Server databases. You don’t want poor data quality to migrate to the MySQL servers, so address it long before the migration process.
Check-in on Project Schedules and Deadlines
If the migration results in significant downtime, it’s critical to coordinate with key stakeholders to avoid disruptions. Get feedback from departments and teams within your organization, as well as from larger client accounts. While you can’t make everyone happy if downtime occurs, you can at least minimize the impact on top priority and critical projects.
Decide Which Type of Deployment Makes Sense for Your SQL Server to MySQL Migration
Do you want to take an on-premise server into the cloud? Are you looking for a hybrid infrastructure instead? Establish the type of deployment and the associated resource requirements before you get too far into the planning process.
Create a Realistic Timeline with Extensive Testing
Best case scenario timelines may sound good when you’re getting buy-in from upper management, but delays come in many forms. Unrealistic timelines lead to more stress and hassle in the long-term. Account for enough testing time for any schedule that you develop.
Make Sure You Have Enough People on Hand for the Migration Process
Do you have the right mix of specialists to handle the SQL Server migration? Consider working with database managed services providers, such as Datavail, to staff this project without taking away from other critical IT tasks.
Put MySQL Training Materials In-place
Offer training workshops and resources to your team so they’re familiar with the differences between SQL Server and MySQL. While you likely have database professionals experienced in this technology, improving awareness of its features and benefits is worthwhile. A training program also helps SQL Server specialists upskill into MySQL if they need to make that change.
Back Up Your Data
Data loss is a risk for any migration, so check your backups before you proceed. A robust disaster recovery plan gives you the safety net you need in case unexpected problems delete or corrupt critical data.
SQL Server to MySQL migrations don’t need to be filled with stress, downtime, and adoption difficulties. Advance preparation and the right database team go a long way. Contact us to learn more.
Read This Next
The Advantages of Migrating from SQL Server to MySQL
This paper highlights the four steps to take to migrate from SQL Server to MySQL, plus the best features available in the latest version, MySQL 8.0. This paper also intends to help you determine whether it’s time for your organization to migrate from SQL Server to MySQL.
The post Making Your SQL Server to MySQL Migration as Smooth as Possible appeared first on Datavail.
