SQL Server – v2017 – Install – “Oracle JRE 7 Update 51 (64-bit) is required for Polybase”

Background

Installing SQL Server v2017 and ran into an error that I have seen before.

Error

Error Image

OracleJRE7Update51_x64.2018109.1111AM.PNG

Error Textual

Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase

Tackled Before

History

Already tackled before here:

SQL Server (v2016) – Installation – Blocked – “Oracle JRE 7 Update 51 (64-bit) or higher is required for Polybase”
here

History

The fix applied was to install Java JRE downloaded from here:

https://www.oracle.com/technetwork/java/javase/downloads/index.html
Link

But, unfortunately even though install applied no help this time.

Trouble Shooting

Let us dig a bit deeper.

SysInternals

Process Monitor

Image

sysInternals.processMonitor.20181009.1010AM.PNG

Explanation

  1. Identified component used by SQL Server Setup
    • Component is ScenarioEngine
  2. ScenarioEngine
    • Log
      • C:\Program Files\Microsoft SQL Server\140\Setup Bootstrap\Log\20181009_092126\Detail.txt

Tail

Tail For Win32

Download

Downloaded Tail For Win32 from here:

Tail for Win32
Link

Usage

Launched “Tail For Win32” and loaded the identified SQL Server Log file ( Detail.txt )

Results
Image
TailforWin32_Usage_20181009_1151AM
Textual

13) 2018-10-09 10:13:06 Slp: Init rule target object: Microsoft.SqlServer.Configuration.Polybase.Polybase_IsMinJavaVersionInstalledFacet
(13) 2018-10-09 10:13:06 SQLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
(13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.
(13) 2018-10-09 10:13:06 SQLPolyBase: Rule 'Polybase_IsMinJavaVersionInstalled' detection result: IsMinJavaVersionInstalled= False
(13) 2018-10-09 10:13:06 Slp: Evaluating rule        : Polybase_IsMinJavaVersionInstalled
(13) 2018-10-09 10:13:06 Slp: Rule running on machine:
(13) 2018-10-09 10:13:06 Slp: Rule evaluation done   : Failed
(13) 2018-10-09 10:13:06 Slp: Rule evaluation message: This computer does not have the Oracle Java SE Runtime Environment Version 7 Update 51 (64-bit) or higher installed. The Oracle Java SE Runtime Environment is software provided by a third party. Microsoft grants you no rights for such third-party software. You are responsible for and must separately locate, read and accept applicable third-party license terms. To continue, download the Oracle SE Java Runtime Environment from https://go.microsoft.com/fwlink/?LinkId=526030.
(13) 2018-10-09 10:13:06 Slp: Send result to channel : RulesEngineNotificationChannel

Textual
  1. QLPolyBase: Could not find registry setting HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    (13) 2018-10-09 10:13:06 SQLPolyBase: Minimum version expected: 1.7.51. Java not found.

Registry

regedit

Images

Regedit – Image – Before
Image

registry_20181009_1038AM.PNG

Textual
  1. Computer\HKEY_LOCAL_MACHINE\Software\JavaSoft
    • Java Plug-in
    • Java Update
    • Java Web Start
    • Java Web Start Caps
    • JRE
Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.
    • Java Runtime Environment
      • Missing

Remediation

Outline

Please download Java JRE 8 and install it.

Download

Please download artifacts from here :-

Java SE Runtime Environment 8 Downloads
https://www.oracle.com/technetwork/java/javase/downloads/jre8-downloads-2133155.html
Link

Artifacts

Image

Java SE Runtime Environment 8u181

download_JavaSERuntimeEnvironment_20181009.1221PM.PNG

download_20181009.1026AM.PNG
Explanation
  1. Chose to download Windows X64

 

Install

Images

Destination Folder

download_DestinationFolder_20181009.1031AM.PNG

Installing
Installing -01

install_20181009.1032AM.PNG

java Setup – Complete

install_JavaSetupComplete_20181009.1033AM.PNG

Change in License Terms

download_JavaSERuntimeEnvironment_Roadmap_20181009.1030AM.PNG

Registry

Using regedit, please review the Java’s registry structure.

regedit

Images
Regedit – Image – After
Image

registry_java_jre_1Dot8_20181009_1243PM.PNG

Explanation
  1. HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion is now present

 

Summary

It appears that Oracle has changed the folder structure for Java in the Windows Registry.

Specifically what used to be

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\Java Runtime Environment\CurrentVersion.

is now

HKEY_LOCAL_MACHINE\SOFTWARE\JavaSoft\JRE\CurrentVersion.

Please be sure to have Java JRE v1.8 ( Java 8) installed for SQL Server v2016\v2017 Polybase.

On MS Windows, Profiling Java Apps Memory Utilization Using VMMap

Background

In our last post, we covered the usage of a few Oracle standard tools to profile Java Applications.

BTW the referenced post is available here.

 

SysInternals

VMMap

In this post, we will briefly cover using SysInternal’s VMMap GUI to track down memory utilization.

 

Artifact

VMMap is available from here.

The current version is v3.21 and it was released a couple of years ago; July 20, 2015 to be exact.

Download & Extract

Download and Extract it; no need for installation.

Usage

ElasticSearch

Select or Launch Process

 

Metrics

 

WebSphere

Select or Launch Process

 

Metric

DataStore.edb

Background

An alarm was raised by our monitoring software.

An alarm is raised whenever a drive free space falls below 10%.

Combed the drive using SpaceSniffer and found out that the DataStore.edb file on C:\Windows\SoftwareDistribution\DataStore is larger than usual.

 

Image

Here it is clocking in at 1.3 GB

 

TroubleShooting

SysInternals

Process Monitor

Overview

Let us see if we can use SysInternal’s Process Monitor and determine which processes are accessing the DataStore.edb file.

Filter

Clause
  1. Path
    • Begins with C:\Windows\SoftwareDistribution\DataStore
Image

 

Capture

Image
 
Event Properties
Event Properties – Create File – Event

Event Properties – Create File – Event – Property
  1. Desired Access :- Read Attributes, Synchronize
  2. ShareMode :- Read, Write
Event Properties – Create File – Event

Image

Details

  1. Path :- C:\Windows\System32\svchost.exe
  2. Command Line :- C:\Windows\System32\svchost.exe -k netsvcs
  3. User :- NT AUTHORITY\SYSTEM

 

Services

Knowing that svchost.exe is a host for many services, which one is netsvcs?

Services Applet

Image

Explanation

We see it is the “Windows Update” service.

 

Process Explorer

Overview

Which program has datastore.edb opened?

Process Explorer Search

Menu Find

Using the menu item”Find Handle or DLL…”, sought for datastore.edb

Handle or DLL substring

 

Process Explorer Results

Here is the result from searching for DataStore.edb

What process is is using the marked PID

Our marked PID is 1012

WIthin Process Explorer ordered by Process ID, PID, and looked for our identified process ID, 1012.

What process is is using the marked PID

Right clicked on that Process and from the drop down menu chose the Properties item.

Here are the services that are using that running within the identified process.

 

Summary

Though DataStore.edb is principally used by the Windows Update Service, because svchost.exe is a shared process, it is going to take more than stopping Windows Update Service to prune / clean out the DataStore.edb file.

SQL Server – Files In use – Day 1

Background

One of the many areas that that one needs to keep an eye one when monitoring database engines  is which files are opened, how they are opened ( exclusively, read only), what other processes are competing for them, etc.

Day 1

This is Day 1 and so we will start off with the basic tools.

 

Microsoft

Lineage

Microsoft really did a very job with Resource Monitor.

Prior to Resource Monitor, Task manager was the go to quick tool.

Task Manager

Here is what Task Manager exposes:

unfortunately, it only exposes information at process level.

 

Resource Monitor

Tab – Disk

Here we see the active Disk Activities.

Explanation

  1. We are able to filter by Process
  2. And, we can order by
    • Process Name
    • File name
    • Reads/Writes/Totals

Tab – CPU

Tab – CPU – Original

When we switch over to the CPU Tab, here is what we see

Explanation

We are forced to choose a process.

Tab – CPU – Process = sqlservr.exe

Once we chose sqlservr.exe from the list of processes:

Tab – CPU – Process = sqlservr.exe = Drive C:

Explanation:
  1. Unfortunately, we were getting really bad storage utilization a few months ago, and had to move to System Drive C: till more storage was allocated
  2. Will come back and move the rollover data and log files
Tab – CPU – Process = sqlservr.exe = Drive D & E:

Explanation:
  1. Most of our SQL Server Data and log files are on Drive D & E:
  2. Will come back and segregate them

SysInternals

I real like the straightforwardness of SysInternals tools.

Handle

Here is how to use handle.

Scripts

Look for file handles

Here we ask for a specific process:

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | more

Output

Look for file handles – File  – Extension [ mdf, ndf, ldf]

Here we ask for files that have have df in their names.

  1. -p = sqlservr
  2. type = file
Code

handle -nobanner -p sqlservr | find "File" | find "df" | more

Output

 

SQL Server

Sql Server Management Studio ( SSMS )

You can also use SSMS Activity Monitor.

But, to me it is a big hammer to what one really needs.

 

Dedicated

Can’t go anywhere else but to SysInternals, Mark Russinovich & Bryce Cogswell.

To me they made it cool to want to look into things, Windows Internals anyone.

SQL Server – HyperThread Ratio

Background

Trained Aireforge Studio against a set of SQL Server Instances that serve one of our one Applications.

 

AireForge Studio

Compare

Image

 

Explanation

The Numbers looked OK

  1. Max Server Memory
    • In Dev, we have 8 GB of RAM
      • We set our Max Memory to 6.2 ( max  server memory )
      • BTW, the number of actual memory is available via total_physical_memory
    • In QA/Test/Prod, we have 64 GB of RAM
      • We have set Max Memory to 58 GB
      • Again, the number of memory installed in box is displayed in the total_physical_memory
      • We have an average of 48 GB available in all three environments
  2. Total Page File in GB & Available Page File in GB
    • Will skip in depth discussion for now
    • Except to note that it is much smaller in Dev, where we have only 8 GB
  3. CPU Count
    • We have 4 CPUs in Dev
    • And, 8 in QA, Test, and Production
  4. HyperThread Ratio
    • In Dev, QA, and Production the Hyper Thread Ratio is 1
    • And, Test is 2

 

HyperThread Ratio

HyperThread Ratio?

Where is HyperThread coming from?

 

SysInternals

Process Explorer

Again, we go back on the SysInternals line, and get Process Explorer.

To get System Information, we click on the menu Item “View” \ “System Information”

TEST

Image

Explanation
  1. Raw
    • Cores is at 8
    • Sockets is at 4
  2. Interpretation
    • Hyperthread ratio is Cores / Sockets
      • 8 /4 = 2

 

 

Prod

Image

Explanation
  1. Raw
    • Cores is at 8
    • Sockets is at 8
  2. Interpretation
    • Hyperthread ratio is Cores / Sockets
      • 8 /8 = 1

SysInternals – AccessCheck – Querying Folder\File Permissions

Background

Wanted to document how to use SysInternal’s accesschk to review NTFS permissions.

 

Premise

This effort was brought to bear by a question asked by desmando on the Windows SysInternals forum.

Question

The question is here

Image

accesschk64-desmando

Textual

I’m trying to look for files and folders and I don’t have access to. To test, I created a folder on my desktop and removed all rights to it. I then ran the following command:

accesschk64.exe -nsd "domain\username" c:\Users\username\Desktop\

It came back saying “No matching objects found.”

Is this not the right tool? Am I not using it right?

Environment Provisioning

File Security Assignment

We have three files.  And, they are in E:\BAK folder.

Our file names are File1.txt, File2.txt, and File3.txt

  1. For files 1 and 2, we are the owner
  2. On File 3
    1. Unchained it from the Folder permission set
    2. And, assigned ownerships to someone else

File List

File2.txt

File2.txt – Properties

securitysettings-security-file2

 

File2.txt – Advanced Setting Properties

advancedsecuritysettings-file2

 

File3.txt

File3.txt – Properties

securitysettings-security-file3

 

File3.txt – Advanced Security Settings

securitysettings-file3-advancedsecuritysettings

 

Script

Logged On user

Code


rem **********************************************************
rem -q Omit Banner
rem -nobanner No banner
rem -v Verbose
rem **********************************************************


set _folder=E:\BAK

set _principalSelf=%USERDOMAIN%\%USERNAME%

rem List all permissions on this folder
AccessChk -nobanner -d "%_folder%"

REM User me has access
AccessChk -nobanner "%_principalSelf%" "%_folder%"

REM User me has no access ( -n )
AccessChk -nobanner -n "%_principalSelf%" "%_folder%"



Output

 

filepermissionforself-20170126-0109pm

Explanation

  1. Used %USERDOMAIN%\%USERNAME% to get current logon’s Domain and User
  2. Retrieve permissions at folder level using -d
  3. Retrieve permission for user against all files
    • Listed 3 files
    • file1.txt and file2.txt we have permissions ( RW :- Read and Write )
    • file3.txt we do not have permission ( File name still listed but without permission set )
  4. Listed files that we do not have permission ( E:\BAK\file3.txt )

 

Another User

This other user does not have access

Script


rem **********************************************************
rem -q Omit Banner
rem -nobanner No banner
rem -v Verbose
rem **********************************************************


set _folder=E:\BAK

set _principal=AD\bpolakam

rem List all permissions on this folder
AccessChk -nobanner -d "%_folder%"

REM User me has access
AccessChk -nobanner "%_principal%" "%_folder%"

REM User me has no access ( -n )
AccessChk -nobanner -n "%_principal%" "%_folder%"



Output

filepermissionforanother-20170126-0119pm

Explanation

  1. Specified full domain name using Domain\Principal syntax
  2. Retrieve permissions at folder level using -d
  3. Retrieve permission for user against all files
    • Listed 3 files
    • file1.txt, file2.txt, and file3.txt  are all listed
      • File names still listed but without permission set
  4. Listed all 3 files as user does not have permission to any of the files

 

References

  1. By Aaron Margosis and Mark E. Russinovich – Windows Sysinternals Administrator’s Reference: Security Utilities
    Link