Data Architecture Blog articles

Automated Continuous integration and delivery – CICD in Azure Data Factory

MUA — Wed, 13 Nov 2024 13:45:44 GMT

In Azure Data Factory, continuous integration and delivery (CI/CD) involves transferring Data Factory pipelines across different environments such as development, test, UAT and production. This process leverages Azure Resource Manager templates to store the configurations of various ADF entities, including pipelines, datasets, and data flows. This article provides a detailed, step-by-step guide on how to automate deployments using the integration between Data Factory and Azure Pipelines.

Prerequisite

Azure database factory, Setup of multiple ADF environments for different stages of development and deployment.
Azure DevOps, the platform for managing code repositories, pipelines, and releases.
Git Integration, ADF connected to a Git repository (Azure Repos or GitHub).
The ADF contributor and Azure DevOps build administrator permission is required

Step 1

Establish a dedicated Azure DevOps Git repository specifically for Azure Data Factory within the designated Azure DevOps project.

Step 2

Integrate Azure Data Factory (ADF) with the Azure DevOps Git repositories that were created in the first step.

Step 3

Create developer feature branch with the Azure DevOps Git repositories that were created in the first step. Select the created developer feature branch from ADF to start the development.

Step 4

Begin the development process. For this example, I create a test pipeline “pl_adf_cicd_deployment_testing” and save all.

Step 5

Submit pull request from developer feature branch to main

Step 6

Once the pull requests are merged from the developer's feature branch into the main branch, proceed to publish the changes from the main branch to the ADF Publish branch. The ARM templates (JSON files) will get up-to date, they will be available in the adf-publish branch within the Azure DevOps ADF repository.

Step 7

ARM templates can be customized to accommodate various configurations for Development, Testing, and Production environments. This customization is typically achieved through the ARMTemplateParametersForFactory.json file, where you specify environment-specific values such as link service, environment variables, managed link and etc.

For example, in a Testing environment, the storage account might be named teststorageaccount, whereas in a Production environment, it could be prodstorageaccount.

To create environment specific parameters file Azure DevOps ADF Git repo > main branch > linkedTemplates folder > Copy “ARMTemplateParametersForFactory.json”
Create parameters_files folder under root path
Copy paste ARMTemplateParametersForFactory.json inside parameters_files folder and rename to specify environment for example, prod-adf-parameters.json
Update each environment specific parameter values

Step 8

To create an Azure DevOps CICD pipeline, use the following code and ensure you update the variables to match your environment before running it. This will allow you to deploy from one ADF environment to another, such as from Test to Production.

name: Release-$(rev:r) trigger: branches: include: - adf_publish variables: azureSubscription: <Your subscription> SourceDataFactoryName: <Test ADF> DeployDataFactoryName: <PROD ADF> DeploymentResourceGroupName: <PROD ADF RG> stages: - stage: Release displayName: Release Stage jobs: - job: Release displayName: Release Job pool: vmImage: 'windows-2019' steps: - checkout: self # Stop ADF Triggers - task: AzurePowerShell@5 displayName: Stop Triggers inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: | $triggersADF = Get-AzDataFactoryV2Trigger -DataFactoryName "$(DeployDataFactoryName)" -ResourceGroupName "$(DeploymentResourceGroupName)" if ($triggersADF.Count -gt 0) { $triggersADF | ForEach-Object { Stop-AzDataFactoryV2Trigger -ResourceGroupName "$(DeploymentResourceGroupName)" -DataFactoryName "$(DeployDataFactoryName)" -Name $_.name -Force } } azurePowerShellVersion: 'LatestVersion' # Deploy ADF using ARM Template and UAT JSON parameters - task: AzurePowerShell@5 displayName: Deploy ADF inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: | New-AzResourceGroupDeployment ` -ResourceGroupName "$(DeploymentResourceGroupName)" -TemplateFile "$(System.DefaultWorkingDirectory)/$(SourceDataFactoryName)/ARMTemplateForFactory.json" -TemplateParameterFile "$(System.DefaultWorkingDirectory)/parameters_files/prod-adf-parameters.json" -Mode "Incremental" azurePowerShellVersion: 'LatestVersion' # Restart ADF Triggers - task: AzurePowerShell@5 displayName: Restart Triggers inputs: azureSubscription: '$(azureSubscription)' ScriptType: 'InlineScript' Inline: | $triggersADF = Get-AzDataFactoryV2Trigger -DataFactoryName "$(DeployDataFactoryName)" -ResourceGroupName "$(DeploymentResourceGroupName)" if ($triggersADF.Count -gt 0) { $triggersADF | ForEach-Object { Start-AzDataFactoryV2Trigger -ResourceGroupName "$(DeploymentResourceGroupName)" -DataFactoryName "$(DeployDataFactoryName)" -Name $_.name -Force } } azurePowerShellVersion: 'LatestVersion'

Triggering the Pipeline

The Azure DevOps CI/CD pipeline is designed to automatically trigger whenever changes are merged into the main branch. Additionally, it can be initiated manually or set to run on a schedule for periodic deployments, providing flexibility and ensuring that updates are deployed efficiently and consistently.

Monitoring and Rollback

To monitor the pipeline execution, utilize the Azure DevOps pipeline dashboards. In case a rollback is necessary, you can revert to previous versions of the ARM templates or pipelines using Azure DevOps and redeploy the changes.

Create and Deploy Azure SQL Managed Instance Database Project integrated with Azure DevOps CICD

MUA — Wed, 13 Nov 2024 13:48:56 GMT

Integrating database development into continuous integration and continuous deployment (CI/CD) workflows is the best practice for Azure SQL managed instance database projects. Automating the process through a deployment pipeline is always recommended. This automation ensures that ongoing deployments seamlessly align with your continuous local development efforts, eliminating the need for additional manual intervention.

This article guides you through the step-by-step process of creating a new azure SQL managed instance database project, adding objects to it, and setting up a CICD deployment pipeline using GitHub actions.

Prerequisites

Visual Studio 2022 Community, Professional, or Enterprise
Azure DevOps environment
Contributor permission within Azure DevOps Con
Sysadmin server roles within Azure SQL managed instance

Step 01

Open Visual Studio, click Create a new project

Search for SQL Server, select SQL Server Database Project

Provide the project name, folder path to store .dacpac file, create

Step 2

Import the database schema from an existing database. Right-click on the project and select 'Import'. You will see three options: Data-Tier Application (.dacpac), Database, and Script (.sql). In this case, I am using the Database option and importing form Azure SQL managed instance

To proceed, you will encounter a screen that allows you to provide a connection string. You can choose to select a database from local, network, or Azure sources, depending on your needs. Alternatively, you can directly enter the server name, authentication type, and credentials to connect to the database server. Once connected, select the desired database to import and include in your project.

Step 3

Configure the import settings. There are several options available, each designed to optimize the process and ensure seamless integration.

Import application-scoped objects: will import tables, views, stored procedures likewise objects.

Imports reference logins: login related imports.
Import Permissions: will import related permissions.
Import database settings: will import database settings.
Folder Structure: option to choose folder structure in your project for database objects.
Maximum files per folder: limit number files per folder.

Click Start which will show the progress window as shown. Click “Finish” to complete the step.

Step 4

To ensure a smooth deployment process, start by incorporating any necessary post-deployment scripts into your project. These scripts are crucial for executing tasks that must be completed after the database has been deployed, such as performing data migrations or applying additional configurations.
To compile your database project in Visual Studio, simply right-click on the project and select 'Build'. This action will compile the project and generate a sqlproj file, ensuring that your database project is ready for deployment.
When building the project, you might face warnings and errors that need careful debugging and resolution to ensure the successful creation of the sqlproj file. Common issues include missing references, syntax errors, or configuration mismatches.
After addressing all warnings and errors, rebuild the project to create the sqlproj file. This file contains the database schema and is essential for deployment.
Ensure that any post-deployment scripts are seamlessly integrated into the project. These scripts will run after the database deployment, performing any additional necessary tasks.
To ensure all changes are tracked and can be deployed through your CI/CD pipeline, commit the entire codebase, including the sqlproj file and any post-deployment scripts, to your branch in Azure DevOps. This step guarantees that every modification is documented and ready for deployment.

Step 5

Create Azure DevOps pipeline to deploy database project

Step 6

To ensure the YAML file effectively builds the SQL project and publishes the DACPAC file to the artifact folder of the pipeline, include the following stages.

stages: - stage: Build jobs: - job: BuildJob displayName: 'Build Stage' steps: - task: VSBuild@1 displayName: 'Build SQL Server Database Project' inputs: solution: $(solution) platform: $(buildPlatform) configuration: $(buildConfiguration) - task: CopyFiles@2 inputs: SourceFolder: '$(Build.SourcesDirectory)' Contents: '**\*.dacpac' TargetFolder: '$(Build.ArtifactStagingDirectory)' flattenFolders: true - task: PublishPipelineArtifact@1 inputs: targetPath: '$(Build.ArtifactStagingDirectory)' artifact: 'dacpac' publishLocation: 'pipeline' - stage: Deploy jobs: - job: Deploy displayName: 'Deploy Stage' pool: name: 'Pool' steps: - task: DownloadPipelineArtifact@2 inputs: buildType: current artifact: 'dacpac' path: '$(Build.ArtifactStagingDirectory)' - task: PowerShell@2 displayName: 'upgrade sqlpackage' inputs: targetType: 'inline' script: | # use evergreen or specific dacfx msi link below wget -O DacFramework.msi "https://aka.ms/dacfx-msi" msiexec.exe /i "DacFramework.msi" /qn - task: SqlAzureDacpacDeployment@1 inputs: azureSubscription: '$(ServiceConnection)' AuthenticationType: 'servicePrincipal' ServerName: '$(ServerName)' DatabaseName: '$(DatabaseName)' deployType: 'DacpacTask' DeploymentAction: 'Publish' DacpacFile: '$(Build.ArtifactStagingDirectory)/*.dacpac' IpDetectionMethod: 'AutoDetect'

Step 7

To execute any Pre and Post SQL script during deployment, you need to update the SQL package, obtain an access token, and then run the scripts.

# install all necessary dependencies onto the build agent - task: PowerShell@2 name: install_dependencies inputs: targetType: inline script: | # Download and Install Azure CLI write-host "Installing AZ CLI..." Invoke-WebRequest -Uri https://aka.ms/installazurecliwindows -OutFile .\AzureCLI.msi Start-Process msiexec.exe -Wait -ArgumentList "/I AzureCLI.msi /quiet" Remove-Item .\AzureCLI.msi write-host "Done." # prepend the az cli path for future tasks in the pipeline write-host "Adding AZ CLI to PATH..." write-host "##vso[task.prependpath]C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin" $currentPath = (Get-Item -path "HKCU:\Environment" ).GetValue('Path', '', 'DoNotExpandEnvironmentNames') if (-not $currentPath.Contains("C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin")) { setx PATH ($currentPath + ";C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin") } if (-not $env:path.Contains("C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin")) { $env:path += ";C:\Program Files (x86)\Microsoft SDKs\Azure\CLI2\wbin" } write-host "Done." # install necessary PowerShell modules write-host "Installing necessary PowerShell modules..." Get-PackageProvider -Name nuget -force if ( -not (Get-Module -ListAvailable -Name Az.Resources) ) { install-module Az.Resources -force } if ( -not (Get-Module -ListAvailable -Name Az.Accounts) ) { install-module Az.Accounts -force } if ( -not (Get-Module -ListAvailable -Name SqlServer) ) { install-module SqlServer -force } write-host "Done." - task: AzureCLI@2 name: run_sql_scripts inputs: azureSubscription: '$(ServiceConnection)' scriptType: ps scriptLocation: inlineScript inlineScript: | # get access token for SQL $token = az account get-access-token --resource https://database.windows.net --query accessToken --output tsv # configure OELCore database Invoke-Sqlcmd -AccessToken $token -ServerInstance '$(ServerName)' -Database '$(DatabaseName)' -inputfile '.\pipelines\config-db.dev.sql'

Cross Subscription Database Restore for SQL Managed Instance Database with TDE enabled using ADF

MUA — Wed, 13 Nov 2024 14:12:13 GMT

Our customers require daily refreshes of their production database to the non-production environment. The database, approximately 600GB in size, has Transparent Data Encryption (TDE) enabled in production. Disabling TDE before performing a copy-only backup is not an option, as it would take hours to disable and re-enable. To meet customer needs, we use a customer-managed key stored in Key Vault. Azure Data Factory is then utilized to schedule and execute the end-to-end database restore process.

Permission requires

To perform backup and restore operations, the SQL Managed Instance Managed Identity needs to have the "Contributor, Storage Blob Data Contributor" permission for the blob storage.
To transfer backup files between two storage locations, ADF managed identity needs the "Storage Blob Data Contributor" permission for the blob storage.
To carry out backup and restore operations, ADF managed identity needs 'sysadmin' permissions on SQL Managed Instance.
To enable Transparent Data Encryption (TDE) with a customer-managed key, ensure you have Contributor access to the Resource Group (RG) where the SQL managed instance resides.
Establish an Azure Key vault access policy for the SQL managed instance's managed identity and the user configuring Transparent Data Encryption (TDE), granting them full key permissions.

Step 1

Create a TDE key in the non-production Azure Key Vault dev-kv-001 within the same subscription as the non-production SQL Managed Instance.

Name the key, select RSA with a 2048-bit key size, and leave the active and expiration dates unset for this demonstration. Ensure the key is enabled, and do not set a rotation policy. Finally, click Create.

Step 2

Establish an Azure Key vault access policy for the SQL managed instance's managed identity and the user configuring Transparent Data Encryption (TDE), granting them full key permissions.

Step 3

Backup the TDE key we just created with in non-prod key vault

Step 4

Create a new Key Vault dev-kv-002 and proceed to restore the key within this newly created vault. Ensure the name matches the backed-up key name and that the status is set to enabled.

Step 5

Move the new dev-kv-002 Azure Key Vault from the development (non-prod) subscription to the production subscription. This process may take a few minutes as it validates the ability to move the Key vault.

Step 6

Having successfully moved our Key Vault dev-kv-002 to the production subscription, we will now proceed to backup (Follow step 3) the Key for restoration in the actual production Key vault prod-kv-001

Step 7

We are now prepared to link the keys we created with their respective Azure SQL Managed Instances in both the development and production environments. The objective is to maintain the same key in the backup, enabling us to refresh our production environment into development seamlessly.

We will simultaneously execute these actions in both our Production and Development SQL Managed Instances. Begin by accessing your portal blades for the SQL Managed Instances. Once there, navigate to the SQL Managed Instance blade and select Transparent Data Encryption under the Security section.

To enable a successful production refresh of our development environment, we need to switch from Service-managed keys to Customer-managed keys.

Step: 8

Creates a server-level credential. A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server.

USE master CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] WITH IDENTITY='Managed Identity' GO

Step: 9

Create ADF link service connects for both SQL Managed Instances and storage accounts.
Create ADF dataset using both SQL Managed Instances and storage accounts link services

Step: 10

If you're utilizing a private endpoint, make sure to set up an ADF integration runtime and a managed link follow Create Azure Data Factory Managed Private Links

Step: 11

Create ADF pipeline to take database backup from source.

Split backup files into multiple files for faster backup
Use below scripts to take copy only database backup
Use Script activity to execute the backup scripts using source SQL MI link service

BACKUP DATABASE [@{pipeline().parameters.source_database_name}] TO URL = N'https://<storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_01.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_02.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_03.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_04.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_05.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_06.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_07.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_08.bak' WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION, STATS = 10

Allow a minute for the backup to transfer to blob storage, adjusting the duration to meet your specific needs.

Step: 12

Create ADF pipeline to copy database backup files from source storage account to target storage account.

Use copy activity to copy backup files from source storage account to target storage account.
Allow a minute for the backup to transfer to blob storage, adjusting the duration to meet your specific needs.

Step: 13

Create Azure Data Factory pipeline to restore database to a target SQL Managed Instance backup from the designated storage account.

Use below scripts to restore database from designated storage account
Use Script activity to execute the restore scripts using target SQL MI link service

USE [master] RESTORE DATABASE [@{pipeline().parameters.target_database_name}] FROM URL = N'https://<storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_01.bak', URL = N'https://<storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_02.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_03.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_04.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_05.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_06.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_07.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_08.bak'

Step: 14

Set up an additional pipeline to remove orphan databases users, provide user access, or carry out any extra tasks needed after a restore, using the suitable activity task.

Step: 15

Create ADF pipeline workstation to execute all Step4 > Step5>Step6>Step7 in sequence.

Set up parameters for both the source_database_name and target_database_name to enable dynamic operation of the pipeline across different databases.

Failed to Restore SQL Managed Instance Database Backup from Azure Blob Storage

MUA — Thu, 07 Nov 2024 05:58:29 GMT

If you encounter issues while restoring an Azure SQL Managed Instance database from Azure blob storage, ensure that your database backup files are correctly stored in Azure blob storage.

Error: 1

Cannot open backup device 'https://mystorageaccount.blob.core.windows.net/test_container/db_backup/mytest_db_01.bak'. Operating system error 86(The specified network password is not correct.).

RESTORE DATABASE is terminating abnormally.

Cause of failure

The blob storage credential is either incorrect or missing.

To validate credential

I am utilizing managed identity, but the same approach applies to all identity types. SQL managed instance managed identity Storage Contributor and Storage Blob Data Contributor is require.

select * from sys.credentials

To create credentials

USE master GO CREATE CREDENTIAL [https:// mystorageaccount.blob.core.windows.net/db_backup/] WITH IDENTITY='Managed Identity' GO

Error: 2

Cannot open backup device 'https://mystorageaccount.blob.core.windows.net/test_container/db_backup/mytest_db_01.bak'. Operating system error 12007(failed to retrieve text for this error. Reason: 317).

RESTORE DATABASE is terminating abnormally.

Cause of failure

The storage account's public network access is disabled, relying solely on a private endpoint. However, the private endpoint was either configured incorrectly or associated with the wrong DNS zone, leading to connectivity issues. The private endpoint for blob storage must be created with privatelink.blob.core.windows.net Private DNS zone.

To validate private endpoint DNS configuration

Navigate Storage account > Network > Private Endpoint > Click Private Endpoint > Setting > DNS configuration.

FQDN: mystorage.privatelink.blob.core.windows.net
Private DNS Zone: privatelink.blob.core.windows.net

Azure SQL Managed Instance Cross Subscription Database Restore using Azure Data Factory

MUA — Thu, 07 Nov 2024 15:27:22 GMT

Azure Data Factory set up automated, continuous, or on-demand restore of Azure SQL Managed Instance databases between two separate Azure subscriptions.

Before you start the database restore process, make sure to turn off TDE. Those who require Cross Subscription Database Restore for SQL Managed Instance Database with TDE enabled using ADF

Prerequisite

Azure SQL Managed Instances are located across two distinct subscriptions.
Azure Blob storage same subscriptions SQL Managed Instances are located
Azure Data Factory instance

Permission requires

To perform backup and restore operations, the SQL Managed Instance Managed Identity needs to have the "Contributor, Storage Blob Data Contributor" permission for the blob storage.
To transfer backup files between two storage locations, ADF managed identity needs the "Storage Blob Data Contributor" permission for the blob storage.
To carry out backup and restore operations, ADF managed identity needs 'sysadmin' permissions on SQL Managed Instance.

Note: We utilized Managed Identity for permission granting. Should you employ a different ID, ensure it has the same permissions assigned.

Step: 1

Creates a server-level credential. A credential is a record that contains the authentication information that is required to connect to a resource outside SQL Server.

USE master GO CREATE CREDENTIAL [https://<mystorageaccountname>.blob.core.windows.net/<mystorageaccountcontainername>] WITH IDENTITY='Managed Identity' GO

Validate the credential created successfully

Step: 2

Create ADF link service connects for both SQL Managed Instances and storage accounts.
Create ADF dataset using both SQL Managed Instances and storage accounts link services

Step: 3

If you're utilizing a private endpoint, make sure to set up an ADF integration runtime and a managed link follow Create Azure Data Factory Managed Private Links

Step: 4

Create ADF pipeline to take database backup from source.

Split backup files into multiple files for faster backup
Use below scripts to take copy only database backup
Use Script activity to execute the backup scripts using source SQL MI link service

Uas Master GO BACKUP DATABASE [@{pipeline().parameters.source_database_name}] TO URL = N'https://<storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_01.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_02.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_03.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_04.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_05.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_06.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_07.bak', URL = N'https:// <storageaccountname>.blob.core.windows.net/databaserefresh/@{pipeline().parameters.source_database_name}_08.bak' WITH COPY_ONLY, MAXTRANSFERSIZE = 4194304, COMPRESSION, STATS = 10

Allow a minute for the backup to transfer to blob storage, adjusting the duration to meet your specific needs.

Step: 5

Create ADF pipeline to copy database backup files from source storage account to target storage account.

Use copy activity to copy backup files from source storage account to target storage account.
Allow a minute for the backup to transfer to blob storage, adjusting the duration to meet your specific needs.

Step: 6

Create Azure Data Factory pipeline to restore database to a target SQL Managed Instance backup from the designated storage account.

Use below scripts to restore database from designated storage account
Use Script activity to execute the restore scripts using target SQL MI link service

Step: 7

Set up an additional pipeline to remove orphan databases users, provide user access, or carry out any extra tasks needed after a restore, using the suitable activity task.

Step: 8

Create ADF pipeline workstation to execute all Step4 > Step5>Step6>Step7 in sequence.

Set up parameters for both the source_database_name and target_database_name to enable dynamic operation of the pipeline across different databases.

Create Azure Data Factory Managed Private Links

MUA — Thu, 07 Nov 2024 06:05:22 GMT

Create a managed private link for an Azure SQL managed instance, which is a similar procedure to establishing a managed link for any Azure resources from the Azure data factory.

Create Azure integration runtime

Create managed link for Azure SQL Managed Instance

Click new

Search for managed instance and select Azure SQL Database Managed Instance

Provide name > select target subscription > select target SQL MI > Create

Azure portal target SQL managed instance > security > Private endpoint connection

Select the pending adf private endpoint > approve

Validate ADF managed private link to ensure approval state "Approved"

Creating a Kubernetes Application for Azure SQL Database

BuckWoodyMSFT — Tue, 27 Aug 2024 14:12:57 GMT

Creating a Kubernetes Application for Azure SQL Database

Buck Woody, Principal Applied Data Scientist, Microsoft

Modern application development has multiple challenges. From selecting a "stack" of front-end through data storage and processing from several competing standards, through ensuring the highest levels of security and performance, developers are required to ensure the application scales and performs well and is supportable on multiple platforms. For that last requirement, bundling up the application into Container technologies such as Docker and deploying multiple Containers onto the Kubernetes platform is now de rigueur in application development.

In this example, we'll explore using Python, Docker Containers, and Kubernetes - all running on the Microsoft Azure platform. Using Kubernetes means that you also have the flexibility of using local environments or even other clouds for a seamless and consistent deployment of your application and allows for multi-cloud deployments for even higher resiliency. We'll also use Microsoft Azure SQL Database for a service-based, scalable, highly resilient, and secure environment for the data storage and processing. In fact, in many cases, other applications are often using Microsoft Azure SQL Database already, and this sample application can be used to further leverage and enrich that data.

This example is comprehensive in scope, but uses the simplest application, database, and deployment to illustrate the process. You can adapt this sample to be far more robust, even leveraging the latest technologies for the returned data. It's a useful learning tool to create a pattern for other applications.

Using the AdventureWorksLT Sample Database in a Practical Example

The AdventureWorks (fictitious) company uses a database that stores data about Sales and Marketing, Products, Customers and Manufacturing. It also contains views and stored procedures that join information about the products, such as the product name, category, price, and a brief description.

The AdventureWorks Development team wants to create a Proof-of-Concept (PoC) that returns data from a View in the AdventureWorksLT database and make them available as a REST API. Using this PoC, the Development team will create a more scalable and multi-cloud ready application for the Sales team. They have selected the Microsoft Azure platform for all aspects of deployment. The PoC is using the following elements:

A Python application using the Flask package for headless web deployment.
Docker Containers for code and environment isolation, stored in a private registry so that the entire company can re-use the application Containers in future projects, saving time and money.
Kubernetes for ease of deployment and scale, and to avoid platform lock-in.
Microsoft Azure SQL Database for selection of size, performance, scale, auto-management and backup, in addition to Relational data storage and processing at the highest security level.

In this article I'll explain the process for creating the entire Proof-of-Concept project. The general steps for creating the application are:

Set up pre-requisites
Write the application
Create a Docker Container to deploy the application and test
Create an Azure Container Service (ACS) Registry and load the Container to the ACS Registry
Create the Azure Kubernetes Service (AKS) environment
Deploy the application Container from the ACS Registry to AKS and test the application
Clean up

Throughout this article, there are several values you should replace, as listed below. Ensure that you consistently replace these values for each step. You might want to open a text editor and drop these values in to set the correct values as you work though the Proof-of-Concept project:

ReplaceWith_AzureSubscriptionName: Replace this value with the name of the Azure subscription name you have.
ReplaceWith_PoCResourceGroupName: Replace this value with the name of the resource group you would like to create.
ReplaceWith_AzureSQLDBServerName: Replace this value with the name of the Azure SQL Database Server you create using the Azure Portal.
ReplaceWith_AzureSQLDBSQLServerLoginName: Replace this value with the vaue of the SQL Server User Name you create in the Azure Portal.
ReplaceWith_AzureSQLDBSQLServerLoginPassword: Replace this value with the vaue of the SQL Server User Password you create in the Azure Portal.
ReplaceWith_AzureSQLDBDatabaseName: Replace this value with the name of the Azure SQL Database you create using the Azure Portal.
ReplaceWith_AzureContainerRegistryName: Replace this value with the name of the Azure Container Registry you would like to create.
ReplaceWith_AzureKubernetesServiceName: Replace this value with the name of the Azure Kubernetes Service you would like to create.

Pre-Requisites

The developers at AdventureWorks use a mix of Windows, Linux, and Apple systems for development, so they are using Visual Studio Code as their environment and git for the source control, both of which run cross-platform.

For the PoC, The team requires the following pre-requisites:

Python, pip, and packages - The development team has chosen the Python programming language as the standard for this web-based application. Currently they are using version 3.9, but any version supporting the PoC required packages is acceptable. You can download the Python language version 3.9 here.
The team is using the pyodbc package for database access. You can find the pyodbc package here with the pip commands to install it.. You may also need the Microsoft ODBC Driver software if you do not have it installed.
The team is using the ConfigParser package for controlling and setting configuration variables. You can find the configparser package here with the pip commands to install it.
The team is using the Flask package for a web interface for the application. You can find the Flask library here.

The Microsoft Azure az CLI tool Next, the team installed the Azure AZ CLI tool. This cross-platform tool allows a command-line and scripted approach to the PoC, so that they can repeat the steps as they make changes and improvements. You can find the installation for the AZ CLI tool here.

With that tool set up, the team used it to log in to their Azure subscription, and set the subscription name they used for the PoC. They then ensured the Azure SQL Database server and database is accessible to the subscription:

az login
az account set --name "ReplaceWith_AzureSubscriptionName"
az sql server list
az sql db list ReplaceWith_AzureSQLDBDatabaseName

Create a Microsoft Azure Resource Group (RG) to hold the entire PoC A Microsoft Azure Resource Group is a logical container that holds related resources for an Azure solution. Generally, resources that share the same lifecycle are added to the same resource group so you can easily deploy, update, and delete them as a group. The resource group stores metadata about the resources, and you can specify a location for the resource group.

Resource groups can be created and managed using the Azure portal or the AZ CLI. They can also be used to group related resources for an application and divide them into groups for production and nonproduction, or any other organizational structure you prefer.

In the snippet below, you can see the AZ command used to create a resource group in the eastus region of Azure:

az group create --name ReplaceWith_PoCResourceGroupName --location eastus

Microsoft Azure SQL Database with the AdventureWorksLT sample database installed, using SQL Server Logins AdventureWorks has standardized on the Microsoft SQL Server Relational Database Management System platform, and the Development team wants to use a managed service for the database rather than installing locally. Using Azure SQL Database allows this managed service to be completely code-compatible wherever they run the SQL Server engine - on-premises, in a Container, in Linux or Windows, or even in an Internet of Things (IoT) environment.

The team used the sample AdventureWorksLT database for the PoC using the same PoC Resource Group, which you can learn to deploy here. They set a SQL Server account for login for testing but will revisit this decision in a security review.

During creation, they used the Azure Management Portal to set the Firewall for the application to the local development machine, and changed the default you see here to allow all Azure Services, and also retrieved the connection credentials. Note that with this approach, the database could be in another region or even a different subscription.

Create the Application

Next, the Development team created a simple Python application that opens a connection to Azure SQL Database and returns a list of products. This code will be replaced with much more complex functions and may also include more than one application deployed into the Kubernetes Pods in production for a robust, manifest-driven approach to application solutions.

The Team created a simple text file called .env to hold variables for the server connections and other information. Using the python-dotenv library they can then separate out the variables from the Python Code. This is a common approach to keeping secrets and other information out of the code itself.

SQL_SERVER_ENDPOINT = ReplaceWith_AzureSQLDBServerName
SQL_SERVER_USERNAME = ReplaceWith_AzureSQLDBSQLServerLoginName
SQL_SERVER_PASSWORD = ReplaceWith_AzureSQLDBSQLServerLoginPassword
SQL_SERVER_DATABASE = ReplaceWith_AzureSQLDBDatabaseName

Important Security Considerations: For clarity and simplicity, this application uses a configuration file that is read from Python. Since the code will deploy with the container, the connection information may be able to derive from the contents. You should carefully consider the various methods of working with security, connections, and secrets and determine the best level and mechanism you should use for your application. You have multiple options of working with secret information such as connection strings and the like, and the list below shows a few of those options. Always pick the highest level of security, and even multiple levels to ensure your application is secure.

The team next wrote the PoC application and called it app.py. You can see the self-documented code here:

# Set up the libraries for the configuration and base web interfaces
from dotenv import load_dotenv
from flask import Flask
from flask_restful import Resource, Api
import pyodbc

# Load the variables from the .env file
load_dotenv()

# Create the Flask-RESTful Application
app = Flask(__name__)
api = Api(app)

# Create connection to Azure SQL Database using the config.ini file values
server_name = os.getenv('SQL_SERVER_ENDPOINT')
database_name = os.getenv('SQL_SERVER_DATABASE')
user_name = os.getenv('SQL_SERVER_USERNAME')
password = os.getenv('SQL_SERVER_PASSWORD')

# Create connection to Azure SQL Database using the config.ini file values
ServerName = config.get('Connection', 'SQL_SERVER_ENDPOINT')
DatabaseName = config.get('Connection', 'SQL_SERVER_DATABASE')
UserName = config.get('Connection', 'SQL_SERVER_USERNAME')
PasswordValue = config.get('Connection', 'SQL_SERVER_PASSWORD')

# Connect to Azure SQL Database using the pyodbc package
# Note: You may need to install the ODBC driver if it is not already there. You can find that at:
# https://learn.microsoft.com/en-us/sql/connect/odbc/download-odbc-driver-for-sql-server?view=sql-server-ver16#version-17
connection = pyodbc.connect(f'Driver=ODBC Driver 17 for SQL Server;Server={ServerName};Database={DatabaseName};uid={UserName};pwd={PasswordValue}')

# Create the SQL query to run against the database
def query_db():
    cursor = connection.cursor()
    cursor.execute("SELECT TOP (10) [ProductID], [Name], [Description] FROM [SalesLT].[vProductAndDescription] WHERE Culture = 'EN' FOR JSON AUTO;")
    result = cursor.fetchone()
    cursor.close()
    return result

# Create the class that will be used to return the data from the API
class Products(Resource):
    def get(self):
        result = query_db()
        json_result = {} if (result == None) else json.loads(result[0])     
        return json_result, 200

# Set the API endpoint to the Products class
api.add_resource(Products, '/products')

# Start App on default Flask port 5000
if __name__ == "__main__":
    app.run(debug=True)

They checked that this application runs locally, and returns a page to http://localhost:5000/products:

Important Considerations: When building production applications, you should avoid using the administrator account to access the database. Read more here for details on how to set up an account for your application.. The code in this article is simplified so that you can quickly get started with applications using Python and Kubernetes in Microsoft Azure. Check this resource for a complete example on how to create API with Python and Azure SQL Database.

Deploy the Application to a Docker Container

A Container is a reserved, protected space in a computing system that provides isolation and encapsulation. To create one, you use a Manifest file, which is simply a text file describing the binaries and code you wish to contain. Using a Container Runtime (such as Docker), you can then create a binary Image that has all the files you want to run and reference. From there, you can "run" the binary image, and that is called a Container, which you can reference as if it were a full computing system. It's a smaller, simpler way to abstract your application runtimes and environment than using a full Virtual Machine. You can learn more about Containers and Docker here.

The team started with a DockerFile (the Manifest) that layers the elements of what the team wants to use. They start with a base Python image that already has the pyodbc libraries installed, and then they run all commands necessary to contain the program and config file in the previous step.

You can see the self-annotated Dockerfile here:

# syntax=docker/dockerfile:1

# Start with a Container binary that already has Python and pyodbc installed
FROM laudio/pyodbc

# Create a Working directory for the application
WORKDIR /flask2sql

# Copy all of the code from the current directory into the WORKDIR
COPY . .

# Install the  libraries that are required
RUN pip install -r ./requirements.txt

# Once the container starts, run the application, and open all TCP/IP ports 
CMD ["python3", "-m" , "flask", "run", "--host=0.0.0.0"]

With that file in place, the team dropped to a command-prompt in the coding directory and ran the following code to create the binary Image from the Manifest, and then another command to start the Container:

docker build -t flask2sql .
docker run -d -p 5000:5000 -t flask2sql

Once again, the team tests the http://localhost:5000/products link to ensure the Container can access the database, and they see the following return:

Deploy the Image to a Docker Registry

The Container is now working but is only available on the developer's machine. The Development team would like to make this application Image available to the rest of the company, and then on to Kubernetes for production deployment.

The storage area for Container Images is called a repository, and there can be both public and private repositories for Container Images. In fact, AdventureWorks used a public Image for the Python environment in their Dockerfile.

The team would like to control access to the Image, and rather than putting it on the web they decide they would like to host it themselves, but in Microsoft Azure where they have full control over security and access. You can read more about Microsoft Azure Container Registry here.

Returning to the command-line, the Development team uses the az CLI utility to add a Container registry service, enable an administration account, set it to anonymous "pulls" during the testing phase, and set a log in context to the registry:

az acr create --resource-group ReplaceWith_PoCResourceGroupName --name ReplaceWith_AzureContainerRegistryName --sku Standard
az acr update -n ReplaceWith_AzureContainerRegistryName --admin-enabled true
az acr update --name ReplaceWith_AzureContainerRegistryName --anonymous-pull-enabled
az acr login --name ReplaceWith_AzureContainerRegistryName

This context will be used in subsequent steps.

Tag the local Docker Image to prepare it for uploading

The next step is to send the local application Container Image to the Azure Container Registry (ACR) service so that it is available in the cloud. Returning to the command-line, the team uses the Docker commands to list the Images on the machine, then the az CLI utility to list the Images in the ACR service. They then use the Docker command to "tag" the Image with the destination name of the ACR they created in the previous step, and to set a version number for proper DevOps. They then list the local Image information again to ensure the tag applied correctly:

docker images
az acr list --resource-group ReplaceWith_PoCResourceGroupName --query "[].{acrLoginServer:loginServer}" --output table
docker tag flask2sql ReplaceWith_AzureContainerRegistryName.azurecr.io/azure-flask2sql:v1
docker images

With the code written and tested, the Dockerfile, Image and Container run and tested, the ACR service set up, and all tags applied, the team can upload the Image to the ACR service. They use the Docker "push" command to send the file, and then the az CLI utility to ensure the Image was loaded:

docker push ReplaceWith_AzureContainerRegistryName.azurecr.io/azure-flask2sql:v1
az acr repository list --name ReplaceWith_AzureContainerRegistryName --output table

Deploy to Kubernetes

The team could simply run Containers and deploy the application to on-premises and in-cloud environments. However, they would like to add multiple copies of the application for scale and availability, add other Containers performing different tasks, and add monitoring and instrumentation to the entire solution.

To group Containers together into a complete solution, the team decided to use Kubernetes. Kubernetes runs on-premises, and in all major cloud platforms. Microsoft Azure has a complete managed environment for Kubernetes, called the Azure Kubernetes Service (AKS). You can learn more about AKS here.

Using the az CLI utility, the team adds AKS to the Resource Group they created earlier. They add two "nodes" or computing environments for resiliency in the testing phase, they automatically generate SSH Keys for access to the environment, and then they attach the ACR service they created in the previous steps so that the AKS cluster can locate the images they want to use for the deployment:

az aks create --resource-group ReplaceWith_PoCResourceGroupName --name ReplaceWith_AzureKubernetesServiceName --node-count 2 --generate-ssh-keys --attach-acr ReplaceWith_AzureContainerRegistryName

Kubernetes uses a command-line tool to access and control a cluster, called kubectl. The team uses the az CLI utility to download the kubectl tool and install it:

az aks install-cli

Since they have a connection to AKS at the moment, they can ask it to send the SSH keys for connection to be used when they execute the kubectl utility:

az aks get-credentials --resource-group ReplaceWith_PoCResourceGroupName --name ReplaceWith_AzureKubernetesServiceName

These keys are stored in a file called .config in the user's directory. With that security context set, the team uses the "get nodes" command using the kubectl utility to show the nodes in the cluster:

kubectl get nodes

Now the team uses the az CLI tool to list the Images in the ACR service:

az acr list --resource-group ReplaceWith_PoCResourceGroupName --query "[].{acrLoginServer:loginServer}" --output table

Now they can build the manifest that Kubernetes uses to control the deployment. This is a text file stored in a yaml format. Here is the annotated text in the flask2sql.yaml file:

apiVersion: apps/v1
# The type of commands that will be sent, along with the name of the deployment
kind: Deployment
metadata:
  name: flask2sql
# This section sets the general specifications for the application
spec:
  replicas: 1
  selector:
    matchLabels:
      app: flask2sql
  strategy:
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 1
  minReadySeconds: 5 
  template:
    metadata:
      labels:
        app: flask2sql
    spec:
      nodeSelector:
        "kubernetes.io/os": linux
# This section sets the location of the Image(s) in the deployment, and where to find them 
      containers:
      - name: flask2sql
        image:  bwoodyflask2sqlacr.azurecr.io/azure-flask2sql:v1
# Recall that the Flask application uses (by default) TCIP/IP port 5000 for access. This line tells Kubernetes that this "pod" uses that address.
        ports:
        - containerPort: 5000
---
apiVersion: v1
# This is the front-end of the application access, called a "Load Balancer"
kind: Service
metadata:
  name: flask2sql
spec:
  type: LoadBalancer
# this final step then sets the outside exposed port of the service to TCP/IP port 80, but maps it internally to the app's port of 5000
  ports:
  - protocol: TCP
    port: 80
    targetPort: 5000
  selector:
    app: flask2sql

With that file defined, the team can deploy the application to the running AKS cluster. That's done with the apply command in the kubectl utility, which as you recall still has a security context to the cluster. Then the get service command is sent to watch the cluster as it is being built:

kubectl apply -f flask2sql.yaml
kubectl get service flask2sql --watch

After a few moments, the "watch" command will return an external IP address. At that point the team presses CTRL-C to break the watch command and records the external IP address of the load balancer.

Test the Application

Using the IP Address (Endpoint) they obtained in the last step, the team checks to ensure the same output as the local application and the Docker Container:

Coding Assets - You can find all of the code assets for this sample at this location. Here's what they do:

app.py - The Python application that performs a simple SELECT from an Azure SQL Database
.env - A text file with connection information to the Azure SQL Database
Dockerfile - The manifest for the Docker Image creation
flask2sql.yaml - The manifest for the Kubernetes deployment

In addition, the az CLI utility commands may make additional files such as the key for the application and other information in your application directory.

Clean Up

With the application created, edited, documented and tested, the team can now "tear down" the application. By keeping everything in a single resource group in Microsoft Azure, it's a simple matter of deleting the resource group using the az CLI utility:

az group delete -n ReplaceWith_PoCResourceGroupName -y

Note: If you created your Azure SQL Database in another resource group and you no longer need it, you can use the Microsoft Azure Portal to delete it.

The team member leading the PoC project uses Microsoft Windows as her workstation and wants to retain the secrets file from Kubernetes but wants to remove it from the system as the active location. She simply copies the file to a "config.old" text file and then deletes it:

copy c:\users\ReplaceWith_YourUserName\.kube\config c:\users\ReplaceWith_YourUserName\.kube\config.old
del c:\users\ReplaceWith_YourUserName\.kube\config

Learn More

The Well-Architected Framework for Oracle is NOW LIVE!

DBAKevlar — Fri, 20 Jan 2023 23:20:14 GMT

The Well-Architected Framework for Oracle has been published!

Oracle workloads - Microsoft Azure Well-Architected Framework | Microsoft Learn

This invaluable framework provides clear guidance on the recommended practices to assess, architect and migrate Oracle workloads to the Azure cloud. This should be the first place for answers to success for Oracle on Azure!

A special thanks to my teammate, Jessica Haessler for working so hard to help me get this to the finish line, as I would have never been able to get this done on my own!

IOPs is Overrated

DBAKevlar — Mon, 09 Jan 2023 16:41:11 GMT

IOPs is Overrated, yeah, I said it.

How many compute, storage area networks, hard drive vendors and storage services have posted their IOPs capabilities in marketing and didn’t include the throughput (MBPs)? Why when someone sends me IOPs for an Oracle database do I thank them kindly and ask for throughput?

Thank you for asking…

IO requests for Oracle can be exceptionally efficient depending on the type of workload. In this blog post, I’m going to take three, real examples of Oracle workloads and show how different the ratio is between IOPs and MBPs using the AWR report. Now there is a significant difference from what we produce for sizing and a raw AWR report, but I’m going to use the AWR data, as this is something anyone working with Oracle will recognize. The examples here are from different versions of Oracle, single instance vs. Exadata, but hopefully will explain why I am not a fan of IOPs for proving out a workload size.

Example #1

IOPS: 7736 per second

MBPs: 153 per second

Example #2

IOPS: 8327 per second

MBPs: 344 per second

Example #3

IOPS: 26215 per second

MBPs: 13008 per second

Interesting ratio of IO requests vs. throughput:

Source	IO Requests Reads	MBPs Reads	Ratio
Example #1	7736	153	51:1
Example #2	8327	344	24:1
Example #3	26215	12872	2:1

If you based the storage solution for these Oracle workloads based off the IOPS, you could make drastic mistakes on both compute as well as storage.

For Example #1, we see similar ratios set in compute for max limits on Azure compute for limits on IO:

SKU	vCPU	Memory: GiB	Temp storage (SSD) GiB	Max data disks	Max uncached disk throughput: IOPS/MBps	Max burst uncached disk throughput: IOPS/MBps¹	Max NICs	Expected network bandwidth (Mbps)
Standard_E2s_v4⁴	2	16	Remote Storage Only	4	3200/48	4000/200	2	5000
Standard_E4s_v4	4	32	Remote Storage Only	8	6400/96	8000/200	2	10000
Standard_E8s_v4	8	64	Remote Storage Only	16	12800/192	16000/400	4	12500
Standard_E16s_v4	16	128	Remote Storage Only	32	25600/384	32000/800	8	12500
Standard_E20s_v4	20	160	Remote Storage Only	32	32000/480	40000/1000	8	10000
Standard_E32s_v4	32	256	Remote Storage Only	32	51200/768	64000/1600	8	16000
Standard_E48s_v4	48	384	Remote Storage Only	32	76800/1152	80000/2000	8	24000

This is a smaller workload and we could easily go to the Standard E8s_v4 would be covering the average workload from this peak AWR that was submitted for sizing. If the vCPU and memory meets the requirements, then the IO peaks also do.

Using Example #2, where the ratio has more than halved, although the IO Requests on reads hasn’t changed that much, the MBPs (throughput) has more than doubled. We could meet the workload when bursting is available, but we really don’t want to count or pay for this and would need to size up. This is a clear case of why we lean on throughput vs. IOPs.

Example #3 is a very large Oracle workload coming from Exadata. There is considerable offloading, (smart scans) and along with flash cache scanning. With this, the IO requests are incredibly efficient, to the point that the requests vs. throughput is a ratio of 2:1. This is a workload that can only rely on network attached storage to meet its needs and would require some optimizing. There’s a reason the documentation shows both IOPs and throughput (MBPs). Make sure when you’re assessing workloads, especially Oracle, include the throughput. It may surprise you.

Migrating your workloads to Azure Database for MySQL

geo_walters — Tue, 22 Nov 2022 18:55:30 GMT

Migrating your workloads to Azure Database for MySQL

Microsoft has a long history of trying to make software development and administration of IT infrastructure easier overtime. Additionally, Microsoft in the last 10 years has embraced open source broadly.

Microsoft Azure supports virtual machines, platform services, containers, serverless, and various other infrastructure. In this article, I will share some options you can make to deploy your software on MySQL in Azure.

It may help you to look over the relative cost in terms of effort for administering your infrastructure versus physical, virtual on premises, Azure VM, or PaaS for your MySQL choices. The above diagram works for many other products, like SQL Server, or web servers, or other infrastructure as well.

MySQL on Azure VMs. This option falls into the industry category of IaaS. With this service, you can run MySQL Server inside a managed virtual machine on the Azure cloud platform. All recent versions and editions of MySQL can be installed in the virtual machine.

Azure database for MySQL is a Platform Service (PaaS), fully managed MySQL database engine based on the stable version of MySQL community edition. With a managed instance of MySQL on Azure, you can use built-in features viz automated patching, high availability, automated backups, elastic scaling, enterprise grade security, compliance and governance, monitoring and alerting that require extensive configuration when MySQL Server is either on-premises or in an Azure VM.

Azure Database for MySQL, powered by the MySQL community edition, available in two deployment modes:

Flexible Server – A fully managed production-ready database service designed for more granular control and flexibility over database management functions and configuration settings. The flexible server architecture allows users to opt for high availability within a single availability zone and across multiple availability zones. The service supports the community version of MySQL 5.7 and 8.0. The service is generally available today in various Azure regions. Flexible servers are best suited for all new developments and migration of production workloads to Azure Database for MySQL service.
Single Server is a fully managed database service designed for minimal customization. The single server platform is designed to handle most database management functions such as patching, backups, high availability, and security with minimal user configuration and control. The architecture is optimized for built-in high availability with 99.99% availability in a single availability zone. It supports the community version of MySQL 5.6 (retired), 5.7, and 8.0. The service is generally available today in various Azure regions. Single servers are best-suited only for existing applications already leveraging single server. A Flexible Server would be the recommended deployment option for all new developments or migrations.

I have two customers, who each had in-house developed applications with MySQL as the database engine, chose Azure Database for MySQL to migrate to as they moved from on-premises to Azure. Each customer has over 200 databases hosted on many MySQL servers.

Migration choices for Azure Database for MySQL

For detailed information and use cases about migrating databases to Azure Database for MySQL, refer to the Database Migration Guide. This document provides pointers to help you successfully plan and execute a MySQL migration to Azure.

Microsoft has a great set of learning on our website, with tutorials to get familiar with tools and processes. Here is the learning path for migrating MySQL from on-premises to Azure.

I hope this article helps clear up the choices of MySQL on Azure, as well as giving you information for how to migrate and learn more.

Addressing Oracle Redo Latency with Ultra Disk

DBAKevlar — Wed, 31 Aug 2022 23:17:20 GMT

An enterprise cloud, like Azure, handles an incredible variety of workloads and to be successful running Oracle in Azure means you need to know what you’re doing and where the sweet spot is for a relational workload.

I don’t want to get too deep here, as Azure Oracle SMEs are both data and infra, which is a hybrid area resulting in us splitting between the two focuses.

The Oracle Must-Knows

An Oracle database is a complex relational database, but there are terminology and physical architecture that is important to know before we proceed into this post:

Database instance, (background processes)
Memory, (SGA/PGA)
Control files
Datafiles
Redo log files
Archive logs

We’re going to discuss a common performance challenge when coming to the cloud and that’s around REDO LOGS. Oracle redo logs are the equivalent in Microsoft databases to the transaction log. Unlike SQL Server, Oracle has multiple logs that are part of the database that it switches between active, non-active and archiving. All operations that modify are written to the redo log buffer and then the log writer (LGWR) writes it to the active redo log. It’s not uncommon for the fastest storage IO to be required for the redo logs vs. datafiles because of this demand.

Redo logs are commonly mirrored to protect any changes not written to the datafiles and/or from a physical removal of one redo log “member” from a redo log member group.

Group	1^st Member	2^nd Member
Group 1	Redo01a.log	Redo01b.log
Group 2	Redo02a.log	Redo02b.log
Group 3	Redo03a.log	Redo03b.log
Group 4	Redo04a.rdo	Redo04b.rdo

Table 1

Notice that the extension can be anything you want- .log or. rdo is most common.

Oracle Wait Events

For Oracle, wait events are metrics that provide information about what is causing latency in performance of an Oracle workload. This information can be provided in multiple ways, but most often it is provided by the following:

An Automatic Workload Repository (AWR) Active Session History (ASH) or Statspack report
A query of top waits in Oracle
A trace file or trace file TKPROF report

There are two main wait events that we see for Oracle databases:

Log File Sync
Log File Parallel Write

A report showing this type of latency would look like this:

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) DBTime

----------------------------- ------------ ----------- ------ ------

CPU time 5,099 41.0

log file sync 621,615 3,520 6 28.3

log file parallel write 575,295 2,573 4 20.7

For the example above, we expect CPU time to be the top consumer- CPU is on or off and for a database, we expect the usage to be high and Oracle has included the “wait for CPU” in the total, too, so again, it’s not surprising that it’s #1. I commonly look for those wait events that have over a 10% of DB time. In the example, log file sync is 28% and log file parallel write is almost 21%. The two waits together are 49% of the database time consumption. Fix these two waits and the database performance will significantly improve.

What is Log File Sync

As changes happen in the Oracle database, changes are written to the redo log. Once changes are committed or rolledback, the LGWR will then write what is in the redo buffer to the redo log that is active. Before the process is considered complete, a confirmation has to be sent back and this latency is what is referred to as log file sync or the time required to flush the log buffer to disk and writes confirmed. To address this type of wait event, optimization of the code can be performed or to physically address it, faster storage for the redo can be recommended.

What is Log File Parallel Write

An Oracle database will have at least three redo logs and if the DBA wants to mirror those redo logs, that means each redo log has been mirrored and placed in a group. This results in all writes having to be done twice from the changes in the database. As shown in table 1, the database could have two or more, (older days we saw more than 2 redo log members per group) that are being written to each time a write is performed. This is another area where faster disk, (write faster) or optimized code, (write less and less often) can remove latency.

Ultra Disk to the rescue

Ultra disk is a managed disk with configurable IO that the customer pays for depending on the IO demand. For a large Oracle database, it rarely makes sense for datafiles due to the cost, but for redo logs, the size is small and the ability to configure the IO specifically for the high write needs can make a huge difference for the wait events discussed in this blog post.

Calculate the needs of the log files using the IO STAT FUNCTION SUMMARY in the AWR report, which will tell you the number of MBPs/IO Requests that are needed to meet demand. Pad a little over this to address averages in the report and this should give you the values to target for:

Using this information, along with the size of your redo logs, you can then price out ultra efficiently and pay for what you need:

If you only have a Log File Sync issue, then a single ultra disk and moving the redo logs to this new disk can address this problem. If you have both Log File Sync and Log File Parallel Write, then allocate TWO ultra disks, moving the 1^st members of each group to one ultra disk and the 2^nd members of each redo log group to the second ultra disk:

Redo logs can be moved online, so outside of enabling the use of ultra disk on a VM, there isn’t an outage required on the Oracle side to move the redo logs, which simply relies on new redo log groups created in the new ultra disk storage, checkpointing, switching logs and dropping the old groups on the old storage.

Once this has been performed, a full workload should be run and the waits should be eliminated on the log files.

Top 5 Timed Events Avg %Total

~~~~~~~~~~~~~~~~~~ wait Call

Event Waits Time (s) (ms) DBTime

----------------------------------------- ----------- --------------

CPU time 4,057 9.1

log file sync 988,793 1,382 1 3.1

log file parallel write 1,060,731 1,130 1 2.5

You should see the time waited and the percentage of DB Time consumed drop considerably using this method of optimizing with faster storage. Anything under 10% is often a good indication that you can focus on other priorities.

Caveats of using Ultra Disk with Oracle on Azure IaaS

Ultra Disk is still not an option for volume snapshot backups, so RMAN will be required for backups.
Ultra disk is only available on some VM series, so make sure it is available for the system you are hoping to use it on.
Azure Backup for Oracle is impacted when Ultra disk is introduced, but there is a disk exclusion policy that will address this in a future release.
Ultra disk can be expensive, so identify how much IO is required and optimize it to save, using the wait events and acceptable performance from the user experience.
Ultra disk, just like managed disk, is subject to IO throttling, based on the IO limit posed for the VM it resides on.

Recommendations for Oracle 19c Patches in Azure

DBAKevlar — Tue, 05 Jul 2022 18:00:47 GMT

Oracle 19c is the terminal release for Oracle 12c. If you aren’t familiar with that term, a terminal release is the last point release of the product. There were terminal releases for previous Oracle versions (10.2.0.4, 11.2.0.7.0) and after 19c, the next terminal release will be 23c. Therefore, you don’t see many 18c, 20c or 21c databases. We’ve gone to yearly release numbers, but the fact remains that 19c is going to receive all major updates and continue to be supported unlike the non-terminal releases.

Oracle will tell you for support, you should be upgrading to 19c. Premier Oracle Support ended for December 1^st, 2020 and as we discussed, not many are going to choose or stay on non-terminal releases, so 19c is it.

With that said, we must offer guidance on recommended practices for Oracle versioning and patching in Azure. Although I will list any bugs and document IDs that back up the recommendations I’m making, be aware that many of these will be behind Oracle Support’s paywall, so you’ll only be able to access them with an Oracle Support CSI. Let's talk about the things not to do first-

Don’t Upgrade DURING Your Migration

I know it sounds like an awesome idea to upgrade to the latest database version while you are migrating to the cloud, but please, don’t do these two things- migrating to the cloud and upgrading the database/app at the same time. It’s a common scenario that I’m brought in after the Azure specialists are left scratching their head or scrambling to explain what has changed and then I come in to tell them to stand down because it’s the DATABASE THAT’S CHANGED.

Do Patch to the LATEST Patchset for Oracle

I am part of the crowd that often did the latest patchset -1 approach. We would always be one patchset behind and let others figure out how many bugs might be introduced by the patch that had sneaked through testing.

Not anymore… I have a few customers on 19.14, which should be safe, considering the previous practice I mentioned, but the sheer number of bugs and serious bugs that were experienced has changed my thinking to recommend going to the latest patchset.

I think it’s easy to think, “Oh, it's just a small bug” but I’m in agreement with you, if it’s a small impact and it has an easy work around, that’s one thing, but these bugs I’m referring to are quite impactful and here’s how:

High CPU Usage

19.14 release, there were 11 bugs that caused high CPU usage for Oracle.
High CPU usage to the point of doubling the core count for the VM the database ran on in Azure.
Doubling the need for Oracle licenses for the database, even though it was a bug that was causing all the additional CPU usage.
At $47500 list price per processor license, this isn’t something I’d recommend letting go on.

For one customer that I was deeply involved in, the VM sizing required 20 vCPU to run the workload. I sized up to 32 vCPU for peak workloads and yet they were at 97.6% CPU busy with a 64-core machine. The workload hadn’t changed, and the CPU usage traced was out of control!

I would start here: After Upgrade to 19c, One or More of the Following Issues Occur on Non-Linux Platforms: High Paging/Swapping, High CPU, Poor Performance, ORA-27nnn Errors, ORA-00379 Errors, ORA-04036 Errors (Doc ID 2762216.1)

Bug examples for high CPU usage in 19.14:

NB	Prob	Bug	Fixed	Description
	II	31050103	19.15, 23.1.0.0.0	fbda: slow sql performance when running in pluggable database
	-	32869560	19.15, 21.6	HIGH CPU ON KXSGETRUNTIMELOCK AND SSKGSLCAS
	I	29446010	20.1	Query Using LIKE Predicate Spins Using NLS_SORT='japanese_m' NLS_COMP='linguistic'
	-	32431067	23.1.0.0.0	Data Pump Export is Slow When Exporting Scheduler Jobs Due to Query Against SYS.KU$_PROCOBJ_VIEW
	-	33380871	19.15, 21.6	High CPU on KSLWT_UPDATE_STATS_ELEM
	-	33921441	19.15	Slow performance in AQ dequeue processing
*	II	32075777		Performance degradation by Wnnn processes after applying july 2020 DBRU
	III	32164034		Database Hang Updating USER$ When LSLT (LAST SUCCESSFUL LOGIN TIME) Is Enabled
	III	30664385		High count of repetitive executions for sql_id 35c8afbgfm40c during incremental statistics gathering
	II	29559415		DMLs on FDA enabled tables are slow, or potential deadlocks on recursive DML on SYS_FBA_* tables
	II	29448426	20.1	Killing Sessions in PDB Eventually Results in Poor Buffer Cache Performance Due To Miscalculating Free Buffer Count

Time Slip

This issue will often display an ORA-00800 error and you will need to check the extended trace file for details. It will include the VKTM in the error arguments.

…/trace/xxxxx_vktm_xxxx.trc

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM], [Check traces and OS configuration], [Check Oracle document and MOS notes]

The trace file will include additional information about the error, including:

Kstmmainvktm: failed in setting elevated priority

Verify: SETUID is set on ORADISM and restart the instance highres_enabled

This refers to a bug and has two documents around time drift and how to address it-

ORA-00800: soft external error, arguments: [Set Priority Failed], [VKTM] (Doc ID 2718971.1)

I’d also refer to this doc, even though you aren’t running AIX:

Bug 28831618 : FAILED TO ELEVATE VKTM'S PRIORITY IN AIX WITH EVENT 10795 SET

Network Connection Timeouts

Incident alerting will occur in the alert log, and it will require viewing the corresponding trace file for the incident.

ORA-03137: malformed TTC packet from client rejected.

ORA-03137: Malformed TTC Packet From Client Rejected: [12569] (Doc ID 2498924.1)

Potential Tracing to gather more data:

Getting ORA-12569: TNS:Packet Checksum Failure While Trying To Connect Through Client (Doc ID 257793.1)

Block Corruption

Thanks to Jeff Steiner from the NetApp team who advised on this one.

Bug 32931941 - Fractured block Corruption Found while Using DirectNFS (Doc ID 32931941.8)

This can result in 100’s to 1000’s of corrupted blocks in an Oracle database.
All customers using dNFS with 19c should run 19.14 or higher to avoid being vulnerable to this bug.

Also follow the Recommended Patches for Direct NFS Client (Doc ID 1495104.1)

Summary

If you’re considering an upgrade to Oracle 19c, please review the following Oracle Doc:

Things to Consider to Avoid Database Performance Problems on 19c (Doc ID 2773012.1)

It really is worth your time and can save you a lot of time and headache.

Cross Subscription Prod Refresh on SQL Managed Instance using TDE and Azure Key Vault

Bradley_Ball — Thu, 16 Jun 2022 15:05:21 GMT

Hello Dear Reader! I was working with some friends lately and we needed to set up a process to refresh their Development Environment databases from Production. Additionally, the databases are encrypted using Transparent Data Encryption, The SQL MI instances are in different regions, and the SQL MI Instances are in different subscriptions. To duplicate the environment, in order to match our friends, we did the following setup.

We created a virtual machine and a database named TDE encrypted with a self signed certificate
We exported the Certificate & Private Key and used pvk2pfx to create a pfx file and uploaded this to our SQL MI Instances, see Microsoft Doc’s article Migrate a certificate of a TDE-protected database to Azure SQL Managed Instance
Backup and Restore the TDE database from our VM to the SQL MI Instances
Create an Azure Key Vault in our development subscription and our production subscription

This gives us the following environment. We have our SQL Managed Instances in two different regions with our TDE databases restored using the self-signed certificate.

Remember our goal is to be able to perform cross subscription restores and still keep our databases encrypted. To do that we will perform the next 10 steps.

Create a TDE Key in the Development Azure Key Vault
Backup the Key
Create a new “2-b-moved” Azure Key Vault
Restore the Key to the new “2-b-moved” Azure Key Vault
Migrate the new “2-b-moved” Azure Key Vault from the development subscription to the production subscription
Backup the key from the new “2-b-moved” Azure Key Vault now in the production subscription
Restore the Key to the production Azure Key Vault
Associate both SQL Managed Instances, Production & Development, with the Key’s in their respective Azure Key Vaults
Create a T-SQL Credential on each Azure SQL Managed Instances to an Azure Blob Storage account
Backup the TDE database from the production SQL Managed Instance and restore it to the development SQL Managed Instance

Here is a flow chart of these activities.

Azure Key Vault Steps 1 – 7

One of the things we have to do is make sure that Production & Development can access the same Keys for TDE. This is because TDE encrypts the database and places a thumbprint of the Key in the header file of the database. Without access to the key the database will not come online. If we are looking to do a “prod refresh”, refreshing the development environment with a current copy of the production database, then we will need the keys to be accessible to both instances.

“But Brad”, you say, “Why can’t I just backup the keys from the Development Key Vault and restore them to the Production Key Vault?”.

Alas Dear Reader, there is a dependency on the subscription for backing up and restoring a Key Vault Key. If you attempt to restore a Key from one subscription to another you will get this error.

So we start at step 1. Create the TDE Key

We start by opening our Azure Portal and opening our Dev Azure Key Vault.

Under Settings click Keys.

Click + Generate Import. We are not going to go in-depth on all the options for creating a Key. There is one essential piece of advice I will give you. Each Key should be unique, and if you plan on switching out the Keys every year, BUT you have a regulatory compliance that you must keep X number of years of backups on hand then you need to ensure the life of the Key is X number of years.

For Example: Company A wants to switch Keys every year. Company A has a requirement to restore backups that are 10 years old if needed. Therefore every Key that is created should have a minimum of 10 years before they expire.

Because in 5 years if Company A must restore a backup, they will need the active non-expired Key from 5 years ago to be associated with the Azure SQL Managed Instance so they can restore the backup.

*Remember what I said above, thumbprint of the Key is in the header of the database file and the backup, you cannot restore or bring online a database if you do not have the Key with the thumbprint associated with the SQL Managed Instance. ….also Company A needs Long Term Backup Retention.

By default Keys do not have expiration dates or activation dates. You need to select them.

Enter a name for the Key, go with the default of RSA, RSA Key 2048, no active or expiration date for this demo, Enabled Yes, no rotation policy set, click Create.

Now click on the Key we have created. Now onto Step 2.

Backup the Key

Click on the Download Backup button.

A copy of the key will automatically be downloaded. Now onto Step 3.

Create New Key Vault

There are multiple ways to create a new Key vault, we will do this by clicking on the Azure Portal Menu and selecting + Create a resource.

Type Key vaults in the text box and click enter.

Click Key Vault.

Click Create.

For the Resource Group name we’ll make a new one named bball-keyvault-test-move. The name will be bball-Keyvault-2-b-moved, the region South Central US, and click Next.

We are going to add an Access Policy for the managed identity of our Azure SQL Managed instance, bball-tde-test. To do this we will click on +Add Access Policy.

Select Key, Secret, & Certificate Management in the drop down menu.

Click None selected and type in the name of the service, select the managed identity, and click add, then Add again.

There may be some additional Networking or Tag addition that you need to make. For the purposes of this walk through we can move straight to review + create.

Then click Create. After the Key vault is created click on Go to resource.

Click on Keys.

We are ready for step 4.

Restore Key Backup

Click Restore Backup.

Browse to where you saved your Key backup from step 2 and select the Key backup. Click Open. Now you should see the Key restored. If you click on the key and look at the CURRENT VERSION they should match.

The Name should match the backed up Key name, and the status should be enabled. Now click on Overview because it is time for step 5.

Move Key Vault bball-Keyvault-2-b-moved to Prod Subscription

Click the -> Move drop down and select Move to another subscription.

Select the subscription and the Resource group to which you would like to move the Key Vault, then click Next.

At this point you will see a screen that says, “checking whether these resources can be moved. This might take a few minutes.”. And it will take a few minutes, validating that it can move the Key vault.

Once this finishes you will get a green check and the Next button will become blue. Click Next.

Check the check box next to the statement, “I understand that tools and scripts associated with this moved resources will not work until I update them to use new resource IDs”. Click Move.

This will take you back to the overview page. Wait until the notifications alerts you that the Key vault has successfully moved subscriptions. This takes us to Step 6.

Backup Key

Now that we have moved our Key vault to our production subscription we will backup the Key so we can restore it to our production Key vault. Go to your production subscription, and to the Key vault we’ve just moved. Just like step 2 go to Keys and click on bball-demo-tde-key.

Click Download Backup.

This takes us to step 7.

Restore Key Backup

Like step 4 we are restoring the Key Backup, but this time we are doing it to our Production Key vault bball-keyvault-useast. Navigate to our Production Key vault. Click on Keys and Click Restore Backup.

Browse to the backup of the key and click Open. You should see your key restored inside of our Production Key vault.

Azure SQL Managed Instance Steps 8-10

Now we are ready for Step 8, where we will associate both keys that we have created with their respective Azure SQL Managed Instances in Dev and Prod. Remember the goal is to have the same key in the backup, so we can perform our prod refresh into our development environment.

Associate Key with SQL MI Prod & SQL MI Dev

We will perform these actions in both our Production and Development SQL Managed Instances at the same time. Open your portal blades to the SQL Managed Instances. On the SQL Managed Instance blade click on Transparent Data Encryption under Security.

We need to change the setting from Service-managed key to Customer-managed key. A quick note on these settings. If you use Service-managed keys and your database is encrypted, you cannot create a Copy Only Backup. It will fail. So we need to change this so we can do our prod refresh of our dev environment.

Click on the link that says Change key.

Select Key vault, the Key vault for the environment, the Key we created bball-demo-tde-key, and click Select.

Back on the Transparent Data Encryption screen click Save for both instances.

Once the operation completes we are ready to move on to Step 9.

Create Credential with SAS token to Azure blob storage

In Each SQL Managed Instance we will execute the following script.

CREATE CREDENTIAL [https://bballstorage.blob.core.windows.net/sqlmitcpbackups] WITH IDENTITY='SHARED ACCESS SIGNATURE' ,SECRET='SAS TOKEN without the ?';

In order to get the SAS Token you will go to the storage account that you would like to use, user Security + networking select Shared access signature.

I’m not going into setting the expiration date of the SAS token. You will want to discuss this with your team, follow any polices your organization has, in order to determine what expiration date should be. Mine will literally be for a few minutes, because that’s all I need for this blog.

Copy the SAS token. Remember to remove the question mark after pasting it into the script from above. Now run this script on both SQL Managed Instances in order to create a connection to the storage account.

This takes us to our final step, Step 10!

Backup and Restore TDE.bak to bball-tde-test

This is the moment we’ve been waiting for, time to complete our prod refresh of dev. We will start by running a backup script on bball-sqlmi-useast to our Azure blob storage account.

Using Azure Storage Explorer I can validate the backup is in the storage account.

Now we perform the Restore to dev.

And we have completed our prod refresh!

Clean Up

Earlier we spoke about the need to rotate Keys. At this point we can delete the Azure Key Vault 'bball-keyvault-2-b-moved'. In a year, quarter, or whatever your timeframe is to rotate keys you can repeate all the steps to switch over to new keys and make sure you can still perform prod refreshes to your dev environment.

Alright Dear Reader, I hope this is helpful. As always Thank You for stopping by!

Thanks,

Brad

The Importance of Proximity Placement Groups for Oracle multi-tier systems in Azure

DBAKevlar — Thu, 16 Jun 2022 00:59:46 GMT

Oracle is a unique, high IO, workload beast, but it's important to recognize it's more often an ecosystem made up of an Oracle database, applications and often other databases that all must connect, feed and push data to users and each other.

When migrating to the cloud, the architecture discussion about what apps will be placed on what VMs, in what region, availability zones and even availability sets occur, but many forget that Azure is an enterprise cloud and as such, is massive in scale, let alone that we have over 200 datacenters. With this scale comes a unique learning curve for technical specialists to pivot their view of how this differs from their on-premises datacenter view. We talk about regions and availability zones (AZ) which may not provide as much transparency that we really need to understand the complexity that goes into HA, redundancy, scaling and distance.

Same Bat Problem, Same Bat Channel

I was brought into two scenarios this week with Severity 1 tickets and I was asked repeatedly how I was able to quickly identify the issue with diagnostic data provided by Oracle and Azure that appeared hidden to so many. I also have to admit that Oracle focuses on what is the responsibility of the database in their wait events captured in the Automatic Workload Repository (AWR) or its predecessor, Statspack, and as Oracle considers the network outside of the database’s responsibility, the waits may be missed if you don’t know what you’re looking for. Oracle lists some valuable info around the waits, but sets a clear boundary that the network is beyond the database.

For both customers in question, irony would have it, the complaints were about IO performance. First on the scene tried to scale the VM and storage and as nothing changed, became frantic and located me through the internal Microsoft network. I quickly asked for both Oracle diagnostic data and Azure internal diagnostic telemetry from Microsoft Support. These tools provide an incredible amount of valuable diagnostic data, marry well together, and provide a data-driven picture of what is really happening that we can then address at a time-consumed level vs. just implementing products or resources at the problem hoping it might offer a resolution.

For the first customer, they didn’t have any AWR or Oracle’s free version, Statspack data to show us what the system performed like before the changes made. Yes, I wasn't going to have an easy time of this one. I quickly guided them to install Oracle Statspack on the database and then proceeded to identify where time was being consumed and optimize, as understanding what happened before this was going to be difficult at best.

As the database is always guilty until proven innocent, Oracle was quick to point to latency around the redo logs which resided on the same storage as the datafiles. After separating these from the data, there was only a 35% increase in performance vs. 100% which told me that the VM and the database weren't the real issue. I’d just fixed a problem that had been present the whole time and needed to dig deeper into the data.

As discussed earlier, Oracle doesn’t consider the network “it’s problem”. The diagnostic tools still track high-level information on the network, which is displayed in data around SQL*Net Message to/from client:

As this is Statspack, the number of Total Wait Time in seconds isn't formatted to present such large numbers, so all you get is ############. This is a large red flag, even if SQL*Net isn’t shown as a top 5 wait event. This was quite far down the list, but SQL*Net notice the WAITS and WAITS/TXN. The values demonstrated there were waits happening between the database VM and those VMs interacting with it. Upon investigation, I could see there were both applications, SQL Server and an Oracle app server all in the top connections to this database.

I was also able to view the total waits in MB and the amount of time spent on those waits, which quickly showed, it was the largest ones of all:

Our Last Bottleneck

I think we can easily forget the network is our last bottleneck, especially in the cloud and for multi-tier systems, where all targets, (VMs, servers, containers, etc.) should be treated as one unit. What needed to be understood, was when maintenance or changes that require a VM cycle occur, a restart runs the risk of one or more VMs to redeploy in a different location in a data center or region, depending on availability and configuration.

As I didn’t have diagnostic data from before the performance change, I could only go off of what Oracle statspack was telling me and then confirm with Azure diagnostic data that each of the VMs involved in this “ecosystem” wasn’t connected as part of a Proximity Placement Group. To understand where PPG’s lie in the hierarchy of Azure, think of it this way:

Notice that the PPG “groups” the application VMs and database VMs inside the AZ, letting Azure know that these resources are connected, even if they are in different resource groups or using different virtual networks. If you’re looking for resiliency and using Oracle, consider Data Guard and have replication to a standby in a second AZ. If using SQL Server in IaaS, then you’d do the same with Always-on AG, replicating the application tier to the second AZ with Azure Site Recovery (ASR) or similar.

For the first customer, as I’d also addressed the redo log IO latency issue, the performance improved 200% after the PPG was added for the involved VMs and blew the customer away. For the second customer, I expect similar increases in performance based off the AWR data but will have to wait until they send me the AWR report after the changes that are still waiting for a window to deploy. If you look at the high level AWR diagnostic data from the second customer, you’ll notice a trend in waits similar to the first customer:

Foreground Wait Events

Event	Waits	%Time -outs	Total Wait Time (s)	Avg wait	Waits /txn	% DB time
log file sync	2,418,416		126,918	52.48ms	1.05	55.33
enq: TX - contention	130,455		35,409	271.43ms	0.06	15.44
SQL*Net message from client	93,868,885		4,846,357	51.63ms	40.72
SQL*Net message to client	93,722,245		125	1.34us	40.66	0.05

Although still not considered Oracle’s problem, the AWR at least identifies that the SQL*Net waits should be looked into.

With the PPG in place, both customers can comfortably deploy changes knowing the VMs involved will be closely located in a single data center to decrease network latency and new VMs that need to be connected can be added to the PPG afterwards.

Incorrect compute SKU family, storage type, redo log latency and connectivity between database and app server are the most frequent issues we see for customers in Oracle on Azure IaaS. I discuss the first two in posts often, but we’re now going to start to discuss the next tier of performance and how easy it is to identify and resolve with the diagnostic data provided by Oracle and Azure.

Performance Improvements

Once a PPG is created for involved VMs for an Oracle “ecosystem” the wait times clearly decreases and performance, although outside of Oracle’s control is quickly resolved with PPG from Azure, but the resolution wait times is demonstrated in AWR or Statspack reports, (or in queries directly to the Oracle v$ and historical wait event objects.)

For the first customer, the %Total Call Time dropped from 70% to less than 6% and you’ll notice the decrease in waits on the log file wait events then pushes the overall percentage up for what is now viewed as the new bottleneck: db file sequential and scattered reads:

As we corrected the SQL*Net waits, those have also decreased drastically, allowing for much better performance, not just experienced by the customer, but seen in our diagnostic data, too.

Summary

Recommended practice for any Oracle multi-tier system, such as E-business suite, (EBS) Peoplesoft, Hyperion, Essbase, etc. or Oracle database/applications deployed a multiple VMs in Azure is to use a Proximity Placement Group to deter from network latency across multiple datacenters in a region or Availability Group. High Availability and DR should be architected based on Recovery Point Objective (RPO), Recovery Time Objective, (RTO) and any SLAs the business is held to if there is an outage or impact to user access.

Bring Vision to Life with Three Horizons, Data Mesh, Data Lakehouse, and Azure Cloud Scale Analytics

DarwinSchweitzer — Thu, 19 May 2022 11:14:39 GMT

Introduction:

Many of you may have been focused on analytics for years and have shown audacity, acumen, and determination in improving your decision-making and the decisions of those around you. You have personified Teddy Roosevelt’s "the person in the arena" as opposed to sitting on the sidelines and watching. Whether you have been in the arena, are new to the space, or can’t wait to get on the field, I hope this will inspire thought, discussion, debate, and action.

There’s always been organizational, architectural, and technical blockers to successfully building, deploying, maintaining, and using analytical systems. I have experienced many of these firsthand as an implementer and user since I wrote my first SQL statement back in 1993. So much has changed in the last 29 years, but so much has also stayed the same. We are still getting, preparing, modeling, analyzing, and visualizing data regardless of role (Data Analyst, Data Scientist, Data Engineer, Data Consumer) and tools (BI, AI/ML, Pipelines/Notebooks, Report/Dashboards) used. It’s now just with more diverse personnel using more affordable, intuitive, and scalable tools than in past decades. I am going to consider analytical system architecture from a strategic, organizational, architectural, and technical point of view.

Modeling an organization’s strategic vision for the future requires changing the organization in incremental, evolutionary, and revolutionary ways. Analytics is an important catalyst for change, innovation, and transformation. People using analytical tools can help the organization make better data-driven decisions that support the transition from where an organization sits today to somewhere on and beyond the horizon. Getting started involves tactical assessment of the current state (As-Is) architecture, and discussion and prioritization to define the future state (To-Be) architecture. These first steps can lead to prioritizing which systems should receive operational enhancements initially, and what data, tools, and processes should be used to drive innovation. Essentially transforming complex systems is part of strategy and you need a strategy to vision cast to hook others to move towards the destination. I think that the Data Mesh and Data Lakehouse approaches could help tactically drive change and realize vision.

The methodology I propose to explore and start down the path towards realization of this analytic strategic vision is through the composition of these concepts:

Three Horizon Model/Framework – strategic
Data Mesh Sociotechnical Paradigm – organizational
Data Lakehouse Architecture - architectural
Azure Cloud Scale Analytics Platform – technological

Exploration:

Like exploratory data analysis (EDA) performed on a dataset, I decided to spend some time exploring these concepts during a recent 7-day camping trip to Salt Creek Recreation Area near Port Angeles, WA.

I recommend staying in this beautiful setting with small islands, tidal pools, trees, bald eagles, and snowcapped mountains.

By the time I reached the midpoint of camping I felt great about Data Mesh and Data Lakehouse. I had been coming to the conclusion about the better together synergies for a while, and the more I read Data Mesh by Zhamak Dehghani (Data Mesh founder – Thoughtworks) the more it appeared that Data Mesh and Data Lakehouse need each other. Watching Matei Zaharia’s (Chief Technologist & Cofounder – Databricks) YouTube video Data Mesh and Lakehouse solidified this point further.

Three Horizons Model

I have been reading about the McKinsey Three Horizons model introduced in the book The Alchemy of Growth - Practical Insights for Building the Enduring Enterprise by Authors Mehrdad Baghai, Stephen Coley and David White. Three Horizons, like analytics, has been seasoned over multiple decades. This strategy-focused methodology appears promising to help align stakeholders from the business (management and leadership outside of Information Technology). Helping them envision, embrace, and invest in innovation and transformation.

Looking at the diagram below from McKinsey’s Enduring Ideas: The three horizons of growth you can get an idea of how an organization can drive business value creation (growth and profit) over time by reaching: Horizon 1 - improving performance of existing core businesses, Horizon 2 - building new emerging businesses, and Horizon 3 - creating viable new options through research projects or pilot programs.

Here is a similar diagram in the context of operational and analytical systems related to cloud maturity and accumulated total returns.

This diagram is another view of 3 Horizons that illustrates H1 operational enhancements to existing business systems and its importance to setting up H2 innovation and H3 transformation possibilities.

The reason I like the 3 Horizons model is that strategy is important to the leadership in companies and the support of leadership is essential for architecting analytical systems. Every successful project or program needs some sustained BANTR – Budget, Authority, Need, Timing, and Resources (not the Ted Lasso fictional dating app). As we move to looking at Data Mesh you can see that some strategic vision is going to be applied because it will require a new approach to how teams are organized, and what the team’s priorities are. A similar shift has already taken place with the distribution and scaling of operational systems that use microservices, domain-based teams (2 pizza teams – small enough that they can be fed with two extra-large pizzas), and distributed architectures enabled by containers and orchestrators like Kubernetes.

Data Mesh

As defined in the book Data Mesh by Zhamak Dehghani, Data Mesh is a socio-technical paradigm based on four principles:

Principle of Domain Ownership - decentralization and distribution of data responsibility to people who are closest to the data
Principle of Data as a Product - the analytical data provided by the domains is treated as a product, and the consumers of that data should be treated as customer
Principal of the Self-serve Data Platform - self-serve data platform services that empower domains’ cross-functional teams to share data
Principal of Federated Computational Governance - a decision-making model led by the federation of domain data product owners and data platform product owners, with autonomy and domain-local decision-making power, while creating and adhering to a set of global rules

The Domains make data products out of domain data that can be Source-aligned domain data (the facts and reality of the business), Aggregate domain data (long-standing aggregate data composed from multiple source-aligned domains), or Consumer-aligned domain data (the applications that consume the data, or data users such as data scientists or analysts).

With Data Mesh you organize teams in a distributed way by Domain (focus on Business or Activity being supported) vs centralized teams of Data Warehouse specialists for analytics. In my way of thinking if you are going to build Data Lakehouse’s we need more skilled resources. The construction business scales to meet the demand for building and maintaining structures, so why shouldn’t analytics scale to do the same with data structures. The Data Mesh approach can help with the bottlenecks of centralized organizational structures and centralized technical architectures. Data Mesh could also limit the power struggles where the Data Warehouse team is the only standard that bullies everyone else around, blocks entry into the Data Warehouse, or throttles query access when you want to get data out. If a group wants to share data let them get some cloud-based storage and compute, and not be blocked by another group. Proprietary storage and table formats along with limited tools for query access (just SQL) are the legacy of on-premises MPP Data Warehousing appliances. Cloud-based Data Warehouses have changed this dynamic but the organizational structure and technological consolidation into one place approach still may be far from distributed. Not because it could not be but because the team and/or organization wants it to be that way.

Data Mesh Domains have the same team responsible for the operational (OLTP) system and the analytical (OLAP) system. Synapse Link HTAP (Hybrid Transaction/Analytical Processing) has features for Cosmos DB and Azure SQL Database. For teams that are responsible for Source-aligned domain data in the Data Mesh world, Synapse Link HTAP could be an approach.

When it comes to self-serve data platforms, I think that tools like Power BI have done a great job of providing self-service for Data consumers and Data Analysts. Databricks and Synapse Notebooks, and Data Factory and Synapse Pipelines or Databricks Delta Live Tables are great tools for Data Engineers. Also Databricks Notebooks, Azure Machine Learning Notebooks, and AutoML have Data Scientists covered.

When it comes to Data Governance, Data Officers must go on both offense to increase access to data, and on defense to protect data. Tools like M365 Information Protection, Power BI Sensitivity Labels, and Azure Purview access policy seem to help with the Governance principle.

I really see strong synergies between the Three Horizons Model and the Data Mesh Paradigm

Data Lakehouse Architecture

What is a Lakehouse? - The Databricks Blog

A Data Lakehouse is a new, open architecture that combines the best elements of data lakes and data warehouses.

Some of the characteristics of a Lakehouse include the use of:

Low-cost Object Storage (like ADLS) decoupled from compute
Batch and Stream processing
Diverse Workloads
- Data science
- AI and Machine learning
- BI, SQL and analytics
Diverse and open data formats and diverse data typed
- File formats: Avro, CSV, Json, ORC, Parquet
- Table formats: Delta, Hudi, Iceberg, Hive Acid
- Tables, images, video, audio, semi-structured data, and text.
Metadata Layers
- ACID Transactions
- Versioning and Time travel
- Governance
Diverse Engines, Tools, and Languages
- Presto, Dremel, RDBMS External tables
- Great SQL performance with Photon engine
- PySpark, Scala, Java language support
- Jupyter, Tensorflow tool support
Sharing and governance
- Delta Sharing
- Unity Catalog
- Schema enforcement

Data Mesh and Data Lakehouse better together

IMO Data Mesh using a Data Lakehouse Architecture, built on Azure Data & AI and Data Governance Services can really help organizations. I see domain-driven design foundation of Data Mesh and the data-driven decisions enabled by Data Lakehouse as a great one-two punch.

Azure Cloud Scale Analytics

Microsoft Azure has accelerated analytics at a faster rate than I experienced in the first 20 years of my career. Cloud competition has become a space race that accelerates transformation. When building a Cloud Scale Analytics architecture to define, model, and reach an organization’s strategic horizons, architects must consider everything from how teams are organized (see Data Mesh) to how to simplify and unify operational and analytical data systems (see Data Lakehouse, Synapse Link HTAP).

Azure is a great Cloud Scale Platform for architecting Analytical Systems. It includes Azure Data Lake Storage (ADLS) object storage, Azure Data Factory batch and Event Hub, Azure Databricks and Azure Synapse Spark pool streaming pipelines for data engineering. Data Science AI and Machine Learning tools in Azure Machine Learning, Azure Databricks, and Azure Synapse. Data Management capabilities in ADLS, Azure Databricks, and Azure Synapse SQL and Spark pools. Data Analyst tools in Power BI and Excel.

The following image shows some examples of various cloud services that could be used by teams representing Consumer-aligned, Aggregate, and Consumer-aligned data domains. These domains could be governed by Azure Purview and Azure Active Directory in support of access policy and discovery and classification of data existing in the organization.

Balancing Current Realities - But just a minute:

The week after camping I was back to the realities of work and the challenges of modernizing an existing house. It is not a Lakehouse or a Warehouse, but a residential house. We were getting new windows and exterior doors which needed replacing on the 30-year-old structure. I am thinking pragmatically here because most organizations might be working with legacy systems for operational and analytical data that could be more than decades old. To balance my fondness for Data Mesh and Data Lakehouse (two new shiny objects) I need to measure them against the analytics concepts that have come before. Data Mesh and Data Lakehouse are still relatively new. Therefore, I am going to balance my hope for the To-Be future of analytical system architectures with the reality of keeping the lights on with the current As-Is analytical system architectures. As a result, I will discuss another concept, Data Fabric, which is also a new emerging trend. Data mesh is going to play nice with new operational systems based on microservices and distributed event streaming, but how will it do with Legacy monolithic on premises apps? Tactically what projects are candidates for Data Mesh and Data Lakehouse, versus what should left alone and use alternative ways to integrate?

Keep the lights on and leverage what an organization already has – Modernize the House

Bonus concepts:

What about Legacy Analytics and keeping the lights on – Use Data Fabric

IMO Data Fabric is an umbrella term that covers some emerging technology, as well as some existing seasoned technologies that has been around decades before Data Mesh and Data Lakehouse. Initially some of the data virtualization technologies previously known as Enterprise Information Integration (EII) have been marketed as logical analytics systems that don’t move or copy data from sources but get it from sources without using ETL and Data Warehousing - an alternative approach.

According to Gartner data fabric is a design concept which requires multiple data management technologies to work with the aim of supporting “frictionless access and sharing of data in a distributed network environment”. I have also seen Data Fabric described as modernizing existing investments like databases, data warehouses, data lakes, data catalogs, and data integration platforms.

Data Fabric helps make data the fabric of our lives. For me Data Fabric is at least three things:

New naming of data management technologies that have been available in some cases for 2 or more decades: BI, ETL, OLAP/In-memory, Data Virtualization, Replication, Change Data Capture (CDC)
Newer emerging data management technologies like Data Sharing, Distributed event streaming, Distributed query engines like Spark, Presto and Dremel; and External table access to files using RDBMS systems
Some newer AI enabled features and catalogs that are showing up as new vendor features

The first Decision Support Systems (DSS) or BI tools I used had pipelines (Metaphor capsules), and sematic layers (Metaphor, Brio, Business Objects) and OLAP databases (Essbase, Analysis Services). These tools (as Power BI and Tableau are today) were sold as solutions that could work with or instead of data warehouses. Business Objects semantic layers (universes) could be created on top of OLTP database schema, an Operational Data Store (ODS), or star (ROLAP) schemas from Data Marts or Enterprise Data Warehouses (EDW). Sales, marketing, and finance are examples of departments that were not going to wait for the EDW to be built.

In the case of Metaphor it was marketed as an Executive Information System (EIS), but it looked like an ETL tool that could run pipelines on demand. Data preparation/ETL tools could bring data into Lotus or Excel, like the way Power Query does today in Excel.

CDC and Replication tools were used to create an ODS that was used as data hubs for integration and sources for Reporting. Early EII tools like Nimble Technology were acquired by reporting tools like Actuate to become it’s semantic layer.

Emerging examples of Data Fabric include Data Sharing like Databricks Delta Sharing, Snowflake Secure Data Sharing, and Azure Data Share. Distributed query engines like Spark, Presto and Dremel are examples of Data Fabric that can make Data Lakehouse and Data Mesh work.

External tables on RDBMS systems like Azure Synapse Analytics Serverless or Dedicated pools can be used for Lakehouse and Data Warehouse queries or for loading DW tables, and Azure SQL Managed Instance can query open file formats like Parquet.

Examples of Data Fabric/Data Virtualization Types (Not exhaustive list and my Opinion):

Except for Data Replication (which maintains the source schema) and Distributed Event Processing (which has a schema registry) most of the items in the table above create a data layer on top of data sources that allows for the integration of data. The reason I have included Data Replication and Distributed Event Processing in the table is because these are practical ways of getting data out of the operational systems into changing copies that data layers can be created on top of. I am not sure that practical coping of data can be avoided when it comes to integration, security, and retrieval of data. There is always going to be a copy whether it be a replicated transactional database, events set to multiple places by a distributed event processing system like Kafka, a landing zone for files that are processed and rewritten, or a BI tool with data cached in an in-memory database that is refreshed on a BI service like Power BI.

Improving access to data by consumer-aligned custom applications, data scientists, data analysts, and data consumers justifies the copies. The data product needs consumers.

IMO Data Mesh and Data Lakehouse make better To-Be data sources for Data Fabric technologies because much of the logic is built into the outputs vs just pointing to the original sources. Find the right use case, business process, or domain and build your own Data Mesh, Data Lakehouse, and Data Fabric with today’s As-Is tools as the To-Be market evolves. Prioritize business critical operational systems and modernize those operational systems with unification to analytical systems as part of the design.

An aside about Data Marts vs Data Warehouses

What is appealing to me about Data Mesh and Data Lakehouse is that it is distributed. I have always been a distributed fan. I was a Ralph Kimball follower, and data mart practitioner who started building data marts in 1996. The tooling has always been the same for data marts and data warehouses (Data Integration - ETL/ELT, Relational Database, BI Tool), but the philosophy was different – data marts where bottom-up and had some department autonomy vs enterprise data warehouses were top down and centralized. Data marts were on a central RDBMS to combine the data needed for the business process, but they were still distributed. Microsoft SQL Server 7.0 and future versions were great places to build data marts. The challenge with data marts was that you could not fit all the fact tables on a single RDBMS and not every group would use the same database vendor for their data mart. BI and data integration tools could glue it all together, but it was siloed.

Initially the goal was to deliver data marts in 90 days, but this did not scale because:

Data Marts never scaled the teams up or out to additional teams
After delivering a couple of Data Marts the team could fail under the technical debt of maintaining the existing Data Marts so they couldn’t also create new ones
The more that were built the higher the complexity got
Data Mart teams had no authority over the source applications, so they continually needed to fix data quality that was never fixed upstream in the source system

Did data marts fail because they were distributed? Or did they fail because the architecture was monolithic? Did they fail, or did BI tools consume the approach and just do more of what the data mart did like data integration, data modeling, data storage, and data access?

I think the centralized attitudes in Data Warehousing was one of the reasons that Data teams stayed small and specialized as controlled communities. If you believe in one version of the truth you keep the community small. If you accept the ambiguity that exists in life you let more people participate - it can’t be centralized. All the spread-marts, Microsoft Access databases, Budgeting and Planning OLAP Cubes, SQL Server Data Marts, Analysis Services Cubes, and Reporting Services, Brio, Business Objects, Cognos, Microstrategy, Nquire (OBIEE), Tableau, Power Pivot, and Power BI assets have happened regardless of whether a Data Warehouse existed or not. Will the concepts of Data Mesh and Data Lakehouse fully staffed become the domain data sources that Data Marts and Data Warehouses had mixed success achieving.

Customer Scenarios

Couple customers scenarios I have recently seen that are reasons to think about distributed vs centralized approaches:

Not treating Data as a Product: The IT Pros responsible for the analytical source-aligned domain data are apprehensive to improve the integration into operational data stores (ODS) and don’t see the aggregate domain data team as a customer.

Not defining Domain Ownership: IT Leadership are eager to expand to an enterprise data warehouse focus despite the Institutional Research (IR) team’s wants and needs to get going on a project that is more data mart worthy. The team must take on the burden and technical debt of a centralized approach.

The first scenario needs the Data Mesh and Data Lakehouse approach to restore the trust between the IT team and the analytics team. IT needs to create some SADD teams that are more responsive to changes to the source systems, so the analytics team can be an ADD team.

The second scenario needs the Data Mesh and Data Lakehouse approach because the centralized approach is not going to make anyone happy, may take too long, and be too expensive (can’t distribute the costs). IT Leadership needs to build SADD Data Mart, Data Warehouse, or Data Lakehouse so the IR team can become an ADD Data Mart or Data Lakehouse. IR does not need to be a Data Warehouse.

The following tables show some of the options a Source-aligned Domain Data (SADD) team can use to provide Data Products, and what an Aggregate Domain Data (ADD) team would need to do based on what the SADD team provided. A Consumer-aligned Domain Data (CADD) team could get data from either the SADD or ADD. I would say that the replication and Operational Data Store (ODS) or Data Lake are the most common ways that data sources are provided for analytics currently. It would be the exception currently that a SADD team would provide Data Mart, Data Warehouse, or Data Lakehouse functionality to potential ADD teams.

Source-aligned Domain Data (SADD) Product Options

Aggregate Domain Data (ADD) Sources Options

Consumer-aligned Domain Data (CADD)

Apps that consume the data or data users such as Data Scientists, Data Analysts, or Data Consumers. Using queries, notebooks, custom apps, portals, reports, dashboards.

Get data from the most mature ADD or SADD product. The least mature product would be SADD Replication, and the most mature would be ADD Data Lakehouse. I am categorizing Data Lakehouse as more mature than Data Mart or Data Warehouse because the user could query across multiple SADD or ADD Data Lakehouse tables using a query spanning multiple Delta Tables (Colocation important to make this practical).

The drawing above only depicts the Data Lakehouse as storage leaving out the fact that compute would be needed to move data out of the data sources and do the data processing required to transform the data (by perhaps a data engineer in the aggregate domain). The data consumers in the consumer-aligned domain would also need compute to get data out of the Data Lakehouse but they could do this in a serverless way vs needing to pay to persist the data.

This really seems like a decent way of sharing the costs of usage to the teams that are doing the work creating their data product. If the economics of cost to value don’t workout then that group can adjust their usage appropriately.

Roles involved, and tools and processes

Here I would like to look at the Data Analyst, Data Scientist, Data Engineer, and Data Consumer roles and the tools they might use and the process they might follow. I general the Data Consumer uses artifacts created by the 3 other roles. The other 3 roles all follow similar processes and could use similar tools. The Data Analysts and Data Scientists can leverage work done by the Data Engineer or go do their own data processing. This is where the friction exists between distributed autonomy and distributed teams. The better the SADD and ADD team the less likely the CADD team or individual is to go do data processing themselves.

The diagram from the O’Reilly report “Implementing a Smart Data Platform” shows the closed loop steps of data processing. My premise is that a data analyst can perform the steps in Power BI, the data scientist can do much of this in a Jupyter notebook, and the data engineer can use pipelines and Spark notebooks that augment the efforts of the other two roles.

Closed loop of data processing (figure courtesy of Wenfeng Xiao) from the O’Reilly report “Implementing a Smart Data Platform”

Concluding Thoughts:

This brings me to my back issue. I was not sure whether to ice it, use a heating pad, take anti-inflammatories, stretch, lie flat on my back. While looking for ideas I found out that the advice for treating soft-tissue injuries had changed. Rice had moved to Police, and then on to Peace and Love.

The P.O.L.I.C.E. Principle Emergency Treatment for Acute Injuries

Soft-tissue injuries simply need PEACE and LOVE

The basic metaphor here is that things change.

When to use DW (RICE) vs DF (POLICE) vs DM and DL (PEACE and LOVE)?

Below is a recent slide I used in a recent presentation. I see some definite advantages for Data Mesh and Data Lakehouse related to diverse data types, AI/ML and BI Support, the use of low-cost object storage, diverse tools and languages, and options for both serverless and persistent compute. For Data Warehouse you many have the query engine of the DW service itself, the data may be in a proprietary data format (but it could map to open formats as external tables), might use proprietary storage (although may use low-cost object storage), and the compute may be persistent (although it could be paused or scaled as needed). For my broad definition of Data Fabric it can leverage existing systems which could lead to performance issues on the source system requiring at least replication of the database, I am not convinced that don’t move or copy the data is realistic, or the use of the existing systems storage would work and that the storage and caching of data would need persistent compute. I am much more sure of data layers for BI semantic layers than I am of data layers for integration that are not persisted by a copy in OLAP/In-memory, Data Mart, Data Warehouse, or Data Lakehouse. This is definitely getting more blurred with Query engines on top or Parquet or Delta tables, but someone did a bunch of work to create those file and table formats ahead of time and the data was copied.

Guidance

Do you put new windows in the old house or move to a Lakehouse?

Data Warehouse, Data Fabric, Data Mesh, and Data Lakehouse can be complementary but also compete based on ideology/belief
If you need RDBMS familiar features like Row-level security, use Data Warehouse or OLAP/In-memory Data Fabric
For existing Analytics architectures, use Data Fabric to connect, compose, and consume what you already have and selectively migrate priority Source-aligned domain data to Data Mesh and Data Lakehouse, because rip and replace is expensive
For new (green field) Cloud Scale Analytics projects, use Data Lakehouse and Data Mesh (DL and DM work well together) To Be your new doors and windows; then see if the neighbors with the 30-year-old houses follow suit.
Remember technical debt requires scaling workers (code development, modification, and maintenance) and is a bigger lock-in than vendor lock-in
More people and more talent development when capital is decentralized. Less bureaucracy, more staffing, less expert bias, and a little friendly competition (new windows)
Building organizational and technical maturity takes time and participation
Recognize the existing politics between OLTP and Analytics (OLAP) software developers on which is priority
Milk Carton Rule: “Instead of dwelling on what you can’t change, focus on what you can.” This includes others views of centralized and distributed.
Like Waterfall to Agile, centralized Data Warehouse to distributed Data Mesh with Data Lakehouse is going to take some time for adoption and transformation

IMO Azure Synapse Guidance for DW, DM, and DL

When using Synapse Dedicated MPP pool use the RDBMS as a High-Performance Query endpoint not an ELT tool
For Data Integration leverage Pipelines, Spark pools, and Spark Notebooks for ETL/ELT on ADLS using Parquet/Delta
For Power BI in import mode refresh datasets using Lakehouse and Serverless SQL and for direct query mode use Dedicated pool
For telemetry captured in Azure use Data Explorer (Kusto), for example data collected by Azure Application Insights or Azure Monitor
For teams that are responsible for Source-aligned domain data, consider Synapse Link HTAP (Hybrid Transaction/Analytical Processing) for Apps backed by Cosmos DB and Azure SQL Database
For streaming applications delivering data using Kafka, Event Hub, or IoT Hub consider using Spark Structured Streaming in Azure Databricks, or Azure Synapse Spark pools. Captured data in ADLS container can be queried in Spark, Synapse Serverless SQL, or Azure Databricks SQL endpoints.
For AI/ML needs use Lakehouse and Spark (Databricks or Synapse Spark pools and Notebooks) and/or Azure ML and Cognitive Services

Last week as I sat in the Building 34 Café on the Redmond campus between my Chiropractic and Acupuncture appointments I feel like my back issue is actually improving. Sometimes it takes multiple disciplines (Chiropractic, Acupuncture, Physical Therapy, Pharmacology) and modalities to diagnose, treat, rehabilitate, and prevent an injury. I think medical professionals make decisions just like decision makers in other domains.

Hopefully this post can help your organization justify investment in analytics and exploration of some of these concepts discussed to break the pain-spasm cycle that exists between the spread-mart wild wild west and the locked down Enterprise Data Warehouse! Please let me know how you feel about my depiction of Three Horizons, Data Mesh, Data Lakehouse, Cloud Scale Analytics, Data Fabric, Data Marts, and Data Warehouse; the customer scenarios, roles involved, and tools and processes used to architect them.

If you have not categorized me yet, I am a distributed data marts, data mesh, and data lakehouse fan that would surround these concepts with distributed AI/ML, BI, and Custom Apps.

Gazing over the horizon, the improved view will “Bring Vision to Life”!

Links to Data Mesh, Data Lakehouse, Data Fabric, and Azure Resources

Using Oracle AWR and Infra Info to Give Customers Complete Solutions to Performance Problems

DBAKevlar — Wed, 11 May 2022 23:41:31 GMT

One of the best allocations of an Oracle SME specialist at Microsoft is when there is a complex data/infra issue for one of our customers. We have a unique set of skills, understanding relational workloads along with deep infrastructure knowledge combined to identify issues that may be missed without these skills.

For one customer this last week, they were receiving poor IO performance on one of their Oracle databases running on a VM in Azure. It’s quite easy to just blame storage or scale up the VM, but that not only costs money, it doesn’t get to the real culprit, which so often has nothing to do with the Azure cloud, but lack of understanding of the inter-dependency between database and infrastructure.

Collect the Information

For this type of troubleshooting, there are two things required:

Information about the specific Azure VM SKU and the storage/storage configuration the database is running on.
And Automatic Workload Repository, (AWR) report from the window the performance problem was experienced. As AWR reports are by default, 1-hour snapshots, a manual snapshot can be issued for the beginning and ending snapshot to isolate the performance window.

Armed with this information, we can then start to gather a picture of what has occurred-

The VM SKU the database is running on is important for us to attain detailed information about what resources are available and what limits are set:

Each of the VM series have their own category links and spend the time to understand the differences between storage that’s been configured to take advantage of host level caching vs. uncached and network attached storage. For our customers, the limits highlighted in yellow are what they are held to.

This VM is a good fit for the customer. It has 64vCPU and if they use host level caching, they can get upwards of 4000MBPs. This was part of the problem though. I was sent the storage used for the Linux VM:

No host level read-only caching turned on for any of the P30 disks that could take advantage of it.

There was more than just demonstrated here, but the important thing to know is that the OS was on a premium SSD P10 disk, (good) and then the rest of the data was residing on P30 disks and one P70 disk.

Optimize the Storage

I know that most would say, “Go Ultra SSD” but just ignore Ultra disk unless you have redo latency and then Ultra SSD has a use case for redo logs, but for most, large Oracle databases, Ultra is an expensive choice that is still limited to the max uncached IO for the VM chosen. In this case, that means no matter if you use Premium SSD or Ultra SSD, you can only get a max of 1735 MBPs WITHOUT caching. For our customer, they had the P30s and a P70… If we view this data in a different way, I think it might help understand the reason I want a slightly different choice in disk:

We talk a lot about bursting, but relational workloads need a consistent, high performant storage solution. With premium SSD, we have something called read-only host-level caching. In the last column for the P30-P50 disks, you may realize that this caching option offers us a free boost in performance for high read workloads, which is something we see often with Oracle. Yes, customers could pay for bursting, and although we’re satisfied with it when testing, bursting has to be available and we find in reality that most workloads in the cloud need bursting at the same time, so it results in inconsistent performance for Oracle. Cached read-only workloads do fantastic and if you are deploying multiple P30, (1TB) disks and having IO problems, why wouldn’t you go to P40s for half the number of disks and get 250MBPs more throughput with a free feature you can turn on?

The reason the customer was using the P70 was to get the throughput, but actually didn’t need all the storage, so when shown they could get 750MBPs with the smaller, 2TB, P40 disks, this made great economic sense, too.

First step:

Replace the P30 and P70 disks with P40 Premium SSD.
Turn on read-only host-level caching on each P40 disk.

Optimize the Database to Work with the Infrastructure

Now that we’ve identified what is needed for the storage, we need to identify anything in the database that may be contributing to the issues and the AWR quickly shows we do have a problem:

It might be easy to say, “We fixed the storage, so those User IO problems will go away.” But we really need to understand what is causing it and although the undersized System Global Area, (SGA) is also something we may want to investigate; the undersized Process Global Area, (PGA) is more important. The SGA and PGA are the two memory areas used by Oracle databases. For this situation, we have to ask, “What happens when we don’t have enough PGA to allocate to a given process in Oracle?”

The PGA varied drastically during the window when performance degraded, yet was it enough? The Foreground wait events quickly lets us know what happens when there isn’t enough PGA to perform sorts, hashes, etc. in memory:

That sorting and hashing must run in temp and the temp tablespace is IO. We can see how many times the PGA had to “swap” to temp, along with how much disk had to be used to perform the task in the AWR report:

The AWR also separates the single passes to temp vs. multiple passes to temp tablespace during the small window of performance degradation. This is also driving a lot of extra IO to datafiles, too, so it’s kind of tripping up on this, requiring more SGA, as well as more direct reads. Even though this is farther down in the list, it’s the driving issue.

The last issue that we want to take on with this customer degradation is that the initialization parameters show the customer isn’t using Oracle Automatic Storage Management (ASM) which would provide diskgroups that could be balanced and provide better performance across the storage. In the top objects section, it’s apparent that the same objects are concurrently required for the processing and could benefit from ASM.

At the database level, the recommendations are:

Raise the PGA to 55G to contain all but 2% of PGA processing, (which fits in the current VM memory available.)
Implement ASM as part of the migration to the new storage on the P40 disks.

Follow All of the Data

For relational database workloads running on Infrastructure as a Service, (IaaS) a data/infra specialist can offer a complete picture of performance issues or beneficial migrations strategies vs. a single side of the coin, data or infra.

Oracle Licensing in Azure for Virtual Machines- Sales vs. Reality

DBAKevlar — Mon, 11 Apr 2022 18:03:06 GMT

This post will address an ongoing licensing myth that has just enough complexity around it, that it really trips up customers and account teams alike. As part of this post we’ll reference my vCPU licensing post, which I’d hoped would help answer this challenge, but for some reason, we still have folks confounded by Oracle sales people who, as sales will do, try to create friction that may cause people to hesitate from doing what they want with their workloads vs. doing what the Oracle sales people want them to do.

Constrained vCPU machines, as I’ve stated in the past, are in my opinion, mis-named, as it appears that someone could have access to more vCPU than what is available, (which the answer is NO, they don’t have access to anything other than what is listed and that was the purpose of my previous blog post demonstrated.)

Myths vs Reality

There shouldn’t be an issue here, but we should accept naming the offering “Constrained vCPU” will continue to drive this debate and challenge for customers. If you’re curious where this myth or confusion is coming from, please see the following Oracle documentation for licensing in a third-party cloud for reference as we go forward into the discussion.

To close the debate about COULD Oracle SOMEHOW license the customer for more vCPU than what is available on a constrained vCPU VM, the key is in the documentation from Oracle in which they used the term available instead of the term allocated. We should always focus on the licensing documentation and not what “someone said” or “sales claimed” or anything else verbally stated.

Contracts are Contracts

The only vCPUs that are available on the constrained VM skus are the ones which are active. vCPUs that are inactive on a constrained VM are not available in any sense of the word. The vCPU may have been allocated to the virtual machine, but they are not available to the virtual machine, which is common for any virtual compute in a public cloud.

The wording by which Oracle sales might have achieved their licensing objective is customers are required to count the maximum ~~available~~ allocated vCPUs of an instance type, because the inactive vCPUs have only been allocated, but they are certainly not available in any way, shape, or form makes this a myth and shouldn’t be entertained. I doubt they’d win many customers choosing this avenue, as they’re already losing significant customers as can be seen in percentage of new relational database projects by platform.

We Said What We Said

For us Oracle SMEs for Azure, this avenue of addressing the constrained vCPU issue closes the discussion and makes it a moot point. In legal matters – and Oracle licensing is indeed a contract – we shouldn’t admit imprecise or incorrect verbiage to the conversation. There’s no reason for us to debate it.

When any account team or customer is faced with this myth or misunderstanding by the Oracle sales person, we should advise them of Oracle’s mistake and move on to migrating them to Azure. All contractual issues are not liable to the whims of the Oracle sales team, but the global License Management Services (LMS) organization and must rule upon this in writing. Engaging with LMS would result in them performing a similar assessment as I did in my referenced post in the beginning of this one and list the number of available vCPUs in writing to the customer that are liable for licensing, which are, the available vCPU on a constrained vCPU VM.

Licensing Specialists

If your customer is looking for a partner to assist them with a licensing assessment for Oracle, especially an overall one since their Oracle licensing contract is between the customer and Oracle and not between them and Microsoft, I can’t recommend the following partners enough:

New White Paper- Recommended Practices for Oracle on Azure IaaS

DBAKevlar — Mon, 11 Apr 2022 16:29:37 GMT

I know this is a long-awaited white paper and I would like to get this out into the hands of the public to help those who simply want to know the recommended practices for successfully assessing, sizing, architecting and migrating Oracle onto Azure Infrastructure as a Service (IaaS).

This white paper provides guidance from beginning to end that can be used by any organization to assess an Oracle workload and begin its journey to successfully migrating it to Oracle on Azure IaaS. The goal is to centralize all the recommended practices into one document and yes, it will become a Microsoft document eventually, (we're still in the early stages or redesigning all the Oracle documentation for Microsoft) but the Data Architecture Blog provides me a quick way to get content to customers in a very easy and consumable manner.

This will also be linked in the upcoming SQLBits and MVP Summit presentations around Oracle to Azure Migration Strategy presentations, but this paper is customer available for all those ready to bring Oracle workloads to Azure.

Understanding AWR Data for Exadata to Azure IaaS Migrations, Part I

DBAKevlar — Thu, 20 Jan 2022 23:23:35 GMT

High IO workloads in Azure are a topic of common interest and of those workloads, Oracle Exadata tops the list. I’m going to begin to post about how the Oracle on Azure SMEs in the Cloud Architecture and Engineering team handle those.

Exadata is a unique beast- it’s not an appliance or a single database, but an engineered system. It is a collection of hardware and software intelligence designed to run Oracle workloads, especially those with high IO, efficiently. I’ve built out a high level Exadata architecture and added the main features that we need to address most when going to Azure.

There is no way to architect an Exadata in the Azure cloud and since there are most often NUMEROUS Oracle databases running on an Exadata, it’s important to identify the workload needs for each database and then migrate the workload to the appropriately architected IaaS in Azure. Rarely do I recommend refactoring these out of the gate, as each database is a collection of schemas, that serve an individual application/function and to successfully migrate them directly off of Oracle is riddled with pitfalls. Migrating them to Azure IaaS with Oracle, then later identifying what schemas can be refactored to another platform for those customers hoping to eliminate some of their Oracle footprint is the best course of action, (in my experience.)

What Oracle Says Exadata Can Provide

In the above diagram, we see the following features available on an Exadata:

Multitenant
In-Memory DB
Real Application Clusters
Active Data Guard
Partitioning
Advanced Compression
Advanced Security, DB Vault
Real Application Testing
Advanced Analytics
Management Packs for Oracle
Offload SQL to Storage
InfiniBand Fabric
Smart Flash cache/log
Storage Indexes
Columnar Flash Cache
Hybrid Columnar Compression
IO Resource Management
Network Resource Management
In-memory fault tolerance
Exafusion direct-to-wire Protocol, (This is a benefit of RAC, so put this in the RAC bucket)

With the list above, the items highlighted in Dark Red are Exadata specific. All others are Oracle specific and can be done in Azure, not just Exadata. Cross the ones that aren’t highlighted off your list as a concern. If you license for it, you can build it out and license it in Azure, too.

Oracle Real Application Clusters, (RAC) is a moot point. You can’t build it in a third-party cloud and honestly, it’s rarely something we even need. We are now architecting for the Azure cloud, (please see the blog post on HA for Oracle on Azure at the bottom of this article for more information).

That leaves us with just this to address:

Offload SQL to Storage
InfiniBand Fabric
Smart Flash cache/log
Storage Indexes
Columnar Flash Cache
Hybrid Columnar Compression

So, let’s step back and discuss how we identify the workloads and decide the solution on the Azure side.

Go for the Gold- AWR

Some folks like to boil the ocean- the problem is, the fish will die and there’s just no reason to do it when just trying to identify and size the workload. It’s all about what is the size of bucket it’s going to fit in. If your estimate is off by 100 IOPS/100MBPs, it doesn’t mean you’re going to mis-size the workload when you decide what sizing it falls into. It’s all about small, medium, large and extra-large architectures to begin with.

The Automatic Workload Repository, (AWR) fits this purpose and grants you valuable information about the Exadata feature use per database.

For Oracle 12.2 and above, there are significant enhancements to the AWR report that can be used to help us determine the amount of work that it will be to “decouple” from the Exadata.

For our example, we’re going to use a 4-node RAC database running on an Exadata.

At the very top of the report, we can see the database information, the number of nodes, but even more importantly, how busy it is, comparing the elapsed time to the DB Time. Of course, we expect an OLTP is going to have higher numbers for average active sessions, where this is Exadata, and its primary bread and butter are OLAP workloads with fewer beneficial features for OLTP.

I’m not going to focus on the sizing of a database workload. You can learn how to do that in another link at the bottom of this post, but we’re going to identify the parts of the AWR report that can tell you what you’re up against to move it off of Exadata.

An AWR report is quite large and after 12.2, you will notice that Oracle [often] included the ASH, (Active Session History) and the ADDM, (Automatic Database Diagnostic Monitor) report in with the AWR. Past these two report additions to the AWR data, you will then see a section for Exadata:

In the Top IO Reasons by MB for Cell Usage, we can quickly see how each of cell nodes are being used by the database hosts for offloading:

The majority of the work by the cell nodes is smart scans, i.e., offloading, and pretty consistently across the cell nodes at around 85%.

Which Database Are You Targeting?

We can then go to the next section of interest and see, broken down by database, (top ten databases, after that, the rest are just aggregated and put in under “other”) what percentage of throughput is used by each:

This table lists each database, and we can see, the database identified by DBID 696978140, which we’ll refer to as DB01 from this point forward, is using 70% of the total IO throughput and how much MB per second it uses on average. We don’t size by the value shown in this section, but we can easily see which database is our “whale in the pond” and most likely the reason for the Exadata investment. The next database down is less than 11%, so you can easily see the resource demand difference that the DB01 will require vs. the others.

In the section following this one, we will even see the breakdown of percentage and IOPS per database to each cell node. It often falls very close to the total you see above, with minor differences.

What Don't You Need to Worry About?

One of the nice things about the AWR, it will also tell you when they’re NOT using a feature, such as Columnar Flash Cache:

In this post, I’ve demonstrated how to help identify the ease or challenge to migrate an Oracle database from Exadata to Azure. If you are migrating DB01, then you are going to spend more time on the IO demands for this database. If you’re moving one of the other 8 databases, (the line that appears blank is actually the ASM instance managing the storage layer) then it’s going to be a much easier time and I’d recommend aiming at one of those for a POC, not the "whale in the pond"!

For each one of the additional features in Exadata:

Smart Flash cache/log
Storage Indexes
Flash Cache
Hybrid Columnar Compression (HCC)

There is a separate section in the AWR report that corresponds to it and the databases/Exadata workloads use of it. For Hybrid Columnar Compression, it will tell you if the feature is in use and if so, then you can query the space savings and prepare for the amount of storage and the IO throughput additionally required when leaving Exadata. This commonly is the reason for us to bring in a high IO storage solution. Depending on settings for Query low, Query high, etc., the savings and performance can vary, if they need thin clones and deep compression, then I might suggest Silk to make up for the loss of those features on the Exadata.

The Kitchen Sink

Other data that comes in handy and to think about:

Data is provided for flash caching, and single block reads is very helpful. When you see high single block reads, it’s important to have an Oracle SME identify the database level usage of this. It often offers us insight into Oracle workloads that will BENEFIT from not being on Exadata. This is a longer discussion than what I will go in here, but it’s a less optimal use of an Exadata and worth this discussion, (another day, I promise!)

Last, but not least, is TEMP tablespace usage. Exadata can’t fix your temp problem- I’ve talked about that for years now and it’s still an issue. Temp usage is due to numerous things, but often it’s because the database is not using PGA allocation correctly from inefficient code or databases design and temp is simply disk. The faster the disk, the faster the performance, but needless to say, you’re “swapping” (best description) and performance would benefit by fitting in Process Global Area, (PGA). Slow storage will only worsen this challenge, so it’s important to know how much “swapping” to temp the database in question is doing.

In my next series in these posts, I’ll explain what options there are to deal with:

Offloading calculations
IO Explosion after loss of HCC
Flash logging
High IO Storage solutions for Exadata
InfiniBand for network latency

If you want to learn more than what was provided in this blog about Oracle on Azure IaaS, please see the following posts:

Estimate Tool for Sizing Oracle Workloads to Azure IaaS VMs - Microsoft Tech Community

How to Save on Oracle Core Licensing on Azure Cloud - Microsoft Tech Community

Oracle HA in Azure- Options - Microsoft Tech Community

Constrained vCPU and Oracle Licensing in Azure

DBAKevlar — Fri, 17 Dec 2021 23:47:40 GMT

Numerous times I’ve experienced misunderstanding on licensing around constrained vCPU VMs. Sometimes the confusion is on the term, “constrained”, (I would have named this VM type, “Same CPU, bigger chassis”.) We’ll go over how we can confirm how many vCPU is on the VM, including comparing a standard VM with 16 vCPU and a constrained 8-vCPU with the 16-vCPU chassis, demonstrating the vCPU count validation between both of them.

We’ll also discuss the continual confusion around the terms of hyperthreading and multithreading. Oracle recently updated their documentation around third party cloud support and licensing, replacing hyperthreading to go to the more generic term for any CPU that can produce multiple threads per CPU.

The third topic stems around the need to distinguish between enabled and disabled in the licensing documentation. Microsoft Support can disable hyperthreading, (or multithreading) on our vCPUs, which some customers choose to do to save on licensing at the cost of performance and paying more on monthly infrastructure. This again, is not to be confused with constrained VM series, as it only covers the vCPU available and that can be counted on the VM.

The following statement from Oracle licensing has nothing to do with constrained vCPUs and everything to do with hyper, now referred to as multithreading vCPUs:

"Microsoft Azure – count two vCPUs as equivalent to one Oracle Processor license if multi-threading of processor cores is enabled, and one vCPU as equivalent to one Oracle Processor license if multi-threading of processor cores is not enabled."

If there is a question about the validity of the core counts for licensing for any customer, that’s easy to resolve- Ask for an audit! Oracle is going to use the correct procedure to audit and count the vCPUs, just as I will demonstrate below.

Oracle licensing is based off the CPU or vCPU count that is available to the host. Below I have chosen the most common ways you can gather Core/CPU count from a Linux host. Oracle pulls the data from the host, and I’ve used these commands to demonstrate that although poorly named, constrained vCPU VMs have a bigger chassis, but only the number of vCPU that are listed and those are the ONLY vCPUs that Oracle can charge licensing on.

One or more of these commands would most likely be used by any audit team. To prove the results, I just built two Oracle VMs, using Oracle Linux in my own subscription, one with 16-vCPU and then it’s same chassis, but with a constrained, 8-vCPU VM sku.

What do the numbers show?

E16ds v4 16-vCPU

Command	Total CPU	LSCPU INFO
1. lscpu command	16	CPU(s): 16
2. cat /proc/cpuinfo \| grep processor \| wc -l	16	On-line CPU(s) list: 0-15
3. top or htop with "1" option	16	Thread(s) per core: 2
4. nproc	16	Core(s) per socket: 8
5. hwinfo command**	16	Socket(s): 1
6. dmidecode -t processor \| grep "Status: Populated, Enabled" > cpu.lst	16
7. getconf _NPROCESSORS_ONLN	16
8. grep -c processor /proc/cpuinfo	16

E16-8ds v4 8-vCPU Constrained

Command	Total CPU	LSCPU INFO
1. lscpu command	8	CPU(s): 8
2. cat /proc/cpuinfo \| grep processor \| wc -l	8	On-line CPU(s) list: 0-7
3. top or htop with "1" option	8	Thread(s) per core: 2
4. nproc	8	Core(s) per socket: 4
5. hwinfo command**	8	Socket(s): 1
6. dmidecode -t processor \| grep "Status: Populated, Enabled" > cpu.lst	8
7. getconf _NPROCESSORS_ONLN	8
8. grep -c processor /proc/cpuinfo	8

**Had to install outside of the yum repo and install both epel_release and hwinfo, so unavailable normally.

As you can clearly see, the E16ds v4 has 16-vCPU, (8 cores with hyperthreading to total 16) to be licensed by Oracle. The E16-8ds v4 shows 8-vCPU, (4 cores with hyperthreading to total 😎 allowed to be licensed by Oracle. If for some reason, someone was to attempt to license vCPU that can’t be counted with any tool, legal would have a party with them. It’s essential, when having this type of conversation, where either the customer or the salesperson is making Oracle sound more restricting with its licensing than it really is. To combat this, it’s important to deeply understand what terms are being used in the documentation, (it’s not always easy to translate.)

Hopefully this is helpful and happy holidays!