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

SQL Server – Linked Server – TroubleShooting – SysInternals / Process Monitor

Background

Though better minds would disagree, I am lured into running OLE/DB providers out of process when configuring Linked Servers.

 

SysInternals/Process Monitor

For one, it is a bit easier to debug and troubleshoot via SysInternals/Process Monitor.

 

Filter

Here is what we will be filtering on…

Image

Explanation

  1. Process Name
    • is
      • dllhost.exe

 

Capture

Image

Explanation

  1. sqlservr.exe
    • TCP*
      • TCP Accept
      • TCP Receive
      • TCP Send
  2. DLLhost.exe
    • Process Create
    • Thread Create
  3. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • combase.dll
      • sechost.dll
      • rpcss.dll
  4. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( OLE-DB )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32.dll
      • C:\Windows\System32\MSDART.dll
      • C:\Windows\System32\ole32.dll
      • C:\Program Files\Common Files\System\Ole DB\oledb32r.dll
  5. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows )
    • dll
      • C:\Windows\System32\comsvcs.dll
      • C:\Windows\System32\authz.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
  6. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • C:\Program Files\Common Files\System\Ole DB\msdaps.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDB12.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\VERSION.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\OraOLEDBgmr12.dll
      • OCI
        • E:\app\oracle\client\product\12.2.0\client_1\bin\OCI.dll
        • C:\Windows\System32\OCI.dll
        • E:\app\oracle\client\product\12.2.0\client_1\oci.dll
      • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCP120.dll
  7. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Windows – MSVCP* )
    • dll
      • Visual C++ Redistributable Packages for Visual Studio 2013
        • C:\Windows\System32\msvcp120.dll
        • E:\app\oracle\client\product\12.2.0\client_1\bin\MSVCR120.dll
  8. Oracle.key
    • E:\app\oracle\client\product\12.2.0\client_1\bin\Oracle.Key
  9. Load Image / CreateFile / QuerySecurityFile/CreateFileMapping/CloseFile ( Oracle / Client )
    • dll
      • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
        • The oraociei12.dll file is the main binary for Instant Client
      • E:\app\oracle\client\product\12.2.0\client_1\oraons.dll
        • Oracle Basic Instant Client & Oracle Basic Light Instant Client
  10. Oracle Key & OLEDB DLLS
    • E:\app\oracle\client\product\12.2.0\client_1\Oracle.Key
    • E:\app\oracle\client\product\12.2.0\client_1\Oraoledbic12.Dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDB12us.dll
    • E:\app\oracle\client\product\12.2.0\client_1\OraOLEDBpus12.dll
  11. Oracle Network
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\oraaccess.xml
    • C:\Windows\System32\mswsock.dll
      • MS Windows WinSock
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\sqlnet.ora
    • E:\app\oracle\client\product\12.2.0\client_1\log\diag\clients
    • C:\Windows\System32\SHCore.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\intchg.ora
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnav.ora
    • E:\app\oracle\client\product\12.2.0\client_1\oraociei12.dll
    • E:\app\oracle\client\product\12.2.0\client_1\Network\Admin\tnsnames.ora
    • E:\app\oracle\client\product\12.2.0\client_1\NETWORK\mesg\tnsus.msb
  12.  Network
    • TCP Reconnect
      • :

        -> [destination/hostname]:[destination/port-number (1521) ]

  13. Oracle Client Logs
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user\host_[####_###]\trace\sqlnet.log
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\incident
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\metadata
    • C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_[####_###]\alert\log.xml
  14. Network
    • TCP Send/Receive/Disconnect
      • [destination/hostname]:[destination/port-number] ->

        :

  15. Thread Exit/Process Exit/Close File

 

 

Oracle Log files

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_####_###\trace

Image

Textual


***********************************************************************

Fatal NI connect error 12170.

VERSION INFORMATION:
TNS for 64-bit Windows: Version 12.2.0.1.0 - Production
Windows NT TCP/IP NT Protocol Adapter for 64-bit Windows: Version 12.2.0.1.0 - Production
Time: 21-FEB-2018 10:46:59
Tracing not turned on.
Tns error struct:
ns main err code: 12535

TNS-12535: Message 12535 not found; No message file for product=NETWORK, facility=TNS
ns secondary err code: 12560
nt main err code: 505

TNS-00505: Message 505 not found; No message file for product=NETWORK, facility=TNS
nt secondary err code: 60
nt OS err code: 0
Client address: 

Tabulated

  1. Errors Recorded
    • Main
      • Fatal NI connect error 12170
    • Detail
      • TNS-12535
        • ns secondary err code :- 12560
        • nt main err code :- 505
      • TNS-00505
        • ns secondary err code :- 60
        • nt OS err code :- 0

User_host_trace

C:\Users\[user]\Oracle\oradiag_[user]\diag\clients\user_[user]\host_##\alert\log.xml

Image

Explanation

XML file which contains error messages from the Oracle Connectivity Client.

 

Summary

Our error is “Fatal NI connect error 12170” and that translates to “ORA-12170: TNS:Connect timeout occurred“.

Basic firewall issues.

Though DCOM itself as a container does not expose a lot of errors, client applications themselves are free to do so.

Using SysInternals’ Process Monitor, we are able to quickly familiarize ourselves with our OLE-DB Provider.

 

References

  1. Native Instruments
    • Native Instruments > General > OS and Computer Tuning
      • Windows Error Message: Missing MSVCP120.dll File
        Link
  2. Oracle Technology Network / Database / Database Features / Oracle Call Interface
    • Oracle Instant Client ODBC Release Notes
      Link
  3. Burleson Consulting
    • Donald Burleson
      • fatal ni connect error 12170
        Link

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