SQL Server – Installation on Linux/CentOS

Background

Let us install SQL Server v2017 on our CentOS System.

Installation

Outline

  1. Repositories
    • Review registered Repositories
    • Add missing Repositories
  2. MS SQL Server Engine
    • Install
    • Configure
  3. Services
    • Service Review
  4. Adjust Network Firewall Rules
  5. Validate

Repositories

Review Registered Repositories

yum repolist

Syntax

yum repolist

Output

yum.repolist.20181126.0831PM.PNG

Explanation

We do not see the repositories that we need :-

  1. Repositories
    • packages-microsoft-com-mssql-server-2017

 

yum repository – add

Sample
Sample – Add SQL Server RTM

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017-gdr.repo

 

Sample – Add Cumulative Patch

sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/7/mssql-server-2017.repo

 

Review Registered Repositories

yum repolist

Syntax

yum repolist

Output

yum.repolist.post.20181126.1034PM

Explanation
  1. Repositories
    • packages-microsoft-com-mssql-server-2017

 

Install

Install SQL Server Engine

Syntax

yum install -y mssql-server

Output

install.20181126.0859PM.PNG

 

Configuration

License

Syntax

sudo /opt/mssql/bin/mssql-conf setup
Output

sudo-ms-config-20181126-0914PM.PNG

 

Explanation
  1. Licensing
    • Developer
  2. sa password
    • Enter and Confirm sa password

Services

Review Services :- mssql-server

Syntax

systemctl status [service]
Sample

systemctl status mssql-server

Output

systemctl.20181126.0917PM.PNG

Explanation
  1. Service is running

Firewall

iptables

Our Firewall Software is iptables.

Port TCP/1433 ( mssql-server )

/etc/sysconfig
Outline
  1. Using an editor load /etc/sysconfig/iptables
  2. Add Entry for TCP/port 1433
    • -A INPUT -p tcp -m state –state NEW -m tcp –dport 1433 -j ACCEPT
Image – Pre

firewall.etc.sysconfig.pre.20181126.0942PM.PNG

Image – Post

firewall.etc.sysconfig.post.20181126.1052PM

Reload
Syntax

sudo systemctl reload iptables

Validation

SQL Query

@@version

Syntax

select @@version

Output

@@version.20181126.1110PM

 

References

  1. CentOS.Org
  2.  DigitalOcean
    • Mitchell Anicas
      • How To List and Delete Iptables Firewall Rules
        Link
  3. Sharad Chhetri
    • How to start / stop / restart / reload iptables on CentOS 7 / RHEL 7
      Link

 

 

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.

SQL Server / Minimum OS Software Requirement

Background

Wanted to tabulate the minimum MS Windows OS Requirement for the various versions of MS SQL Server.

 

Minimum OS Support Matrix

SQL Server Version OS Version – Server OS Version – Desktop Bitness Link
v2017  Windows Server 2012  Windows 8 Minimum: x64 Processor: 1.4 GHz
Recommended: 2.0 GHz or fasterDoes not support 32 bit OS
 Link
v2016  Windows Server 2012  Windows 8 Minimum: x64 Processor: 1.4 GHz
Recommended: 2.0 GHz or fasterDoes not support 32 bit OS
 Link
v2014  Windows Server 2008  Windows 7  x64 Processor
x86 Processor
 Link
v2012  Windows Server 2008  Windows Vista x86 Processor: 1.0 GHz
x64 Processor: 1.4 GHz
 Link
v2008-R2  Windows Server 2003 Windows Vista
Windows XP
x64 Processor
x86 Processor
 Link
v2008  Windows Server 2003 Windows Vista
Windows XP
x64 Processor
x86 Processor
 Link
 v2005  Windows Server 2000  Windows Vista
Windows XP
600-megahertz (MHz) Pentium III-compatible or faster processor
1-gigahertz (GHz) or faster processor recommended
 Link

 Link

 v2000  Windows NT Server 4.0  Windows 98
Link
 

 

 

SQL Server 2017 – Developer Edition – ISO

Background

Currently, I have SQL Server 2014 Developer Edition on my laptop.

Recently found out that since my machine is now Windows 10, I can have SQL Server 2017 Developer Edition.

 

Download ISO

Let us go download v2017 Developer Edition.

v2017 is available here.

Available

Image

Textual

  1. Free Trial Evaluation
  2. Developer edition
  3. Express edition

Developer Edition

If we click on the “Download now” button underneath the “Developer edition“, we will notice that the name of the file that will be availed is SQLServer2017-SSEI-Dev.exe.

Already have that file and it is a small file.

I don’t really want a small file, I want the full ISO.

 

Self-Prepare ISO

Download Bootstrap

To get a nice ISO, please process with downloading SQLServer2017-SSEI-Dev.exe.

 

Prepare ISO

Outline

  1. Select an installation type
  2. Specify SQL Server Installer download
    • Which package will you like to download
      • ISO
      • CAB
    • Click on the Browser folder button
      • Choose an existing folder or create a new folder
      • Click the OK button
    • Click the OK button
  3. Download Media

Screen shot

Select an Installation Type

 

Specify SQL Server Installer download

Browse Folder

Download Media

Download Successful

 

Installed

Once the files was packaged into a nice ISO, was able to install it.

 

Conclusion

So unfortunately, it appears that one cannot download v2017 as a nice offline ISO.

But, please do not let that overly deter you.

Go ahead and download SQLServer2017-SSEI-Dev.exe and run it.

You will be able to prepare an ISO through running that package.

SQL Server – Upgrade – “It is not possible to change the SQL Server features to be upgraded in this release”

Background

Trying to upgrade the SQL Server Instance, but kept shooting blanks.

 

Blanks

SQL Server v2005 to v2014

Upgrade to SQL Server 2014

Shared Features

Textual

It is not possible to change the SQL Server features to be upgrade in this release.

Image

Select Features

Textual

There are validation errors on this page.  Click OK to close this dialog box. Review errors at the bottom of the error page, then provide valid parameters or click Help for information.

There are no features selected for upgrade.

TroubleShooting

Looked everywhere on this one.

But, then thought back to a few months past.

 

SQL Server Instance

Version Info

SQL


select @@version

Output

Image

Textual

Microsoft SQL Server 2005 – 9.00.5000.00 ( Intel X86 )
Dec 10 2010 10:56:29
Express Edition on Windows NT 6.1 ( Build 7601: Service Pack 1)

 

Tabulate

  1. Version :-
    • We are running Microsoft SQL Server v2005
  2. Platform :- Intel x86
  3. Edition :- Express Edition
  4. OS :- Windows NT 6.1 ( Build 7601 : Service Pack 1 )
    • MS Windows 2008/R2
    • Service Pack 1

 

32 Bit?

It appears that we are running 32-bit SQL Server

Control Panel

Services
Image

Textual
  1. path to executable :- C:\Program Files (x86) \XDM-SQLExpress\MSSQL.1\MSSQL\Binn\sqlservr

 

Explorer

Folder :- C:\Program files (x86) \ XDM-SQLExpress – MSSQL.1 \ MSSQL \ Data
Image

Explanation

Confirmed that our files are in the “C:\Program Files x86” folder

Remediation

  1. Prepare to re-Install
    • Preserve SQL Server data and log files
      • Take SQL Server Services offline and copy files over to safe location
      • Backup databases
    • Capture SQL Server Logins
  2. Uninstall Microsoft SQL Server v2005
  3. Install Microsoft SQL Server v2014
    • Our OS, MS Windows 2008-R2, only let us go up v2014
    • Apply MS SQL Server v2014 – SP2 and Cumulative Updates
  4. Restore
    • Re-create SQL Server Logins
    • Re-attach data & log files

Summary

Sometimes simple SQL Server Upgrades take on a life of their own.

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

Background

In the process of installing “SQL Server 2016” for a new client.

As it is a new client and not really sure exact features they will be using, chose to install “All features“.

Install Blocked

Installation is blocked with the error pasted below:

ScreenShot

Feature Rules

Image

Text

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

Rule Check Result

Image

Text

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 software.

To continue, download the Oracle SE Java Runtime Environment from http://go.microsoft.com/fwlink/?LinkId=526030.

 

 

Remediation

Visited Microsoft URL

Visited the listed Microsoft page and directed right away to Oracle’s Java download page.

 

Java Standard Edition Downloads

Here are the downloads available for java Standard Edition ( Java SE)

 

Explanation

  1. JDK
    • We know we need JRE ( Java Runtime Engine), and not JDK ( Java Development Kit )
  2. Server JRE
    • Thought we could get away with the the lighter footprint Server JRE
  3. JRE
    • JRE

 

Server JRE

Server JRE – Downloads

Here is the link to the current version ( 10 ).

 

Chose Windows x64.

Install Package Contents

The installer is bundled as a tar.gz.

Using 7-Zip extracted the file, but no installer.

Just app executable and class files.

Need more than that to install JRE and have SQL recognize that Java JRE is installed.

 

JRE

Java SE Runtime Environment 8 Downloads

Needed an executable offline installer and our bitness is 64 bit and so chose jre-8u131-windows-x64.exe.

 

Install

Actual Installation is straightforward.

ScreenShot

Welcome

 

Destination Folder

Create destination Folder beforehand.

Here is a sample script.


@echo off
rem _folder=C:\Program Files\java\jre1.8.0_131
set "_folder=E:\ProgramFiles\Java\JRE1.8.0_131"

if not exist %_folder% mkdir %_folder%

 

 

 

Destination Folder ( Changed )

 

Java Setup – Progress

 

Java Setup – Complete

Summary

If you will be evaluating \ using Polybase as part of SQL Server v2016, please download and install Oracle JRE ( Java Runtime engine) beforehand.

For JRE, there are two packages available for download.  One is marked Server and the generic one.

Please choose the generic, full version.