SQL Server – Installation – v2019

Background

Needing to install MS SQL Server v2019 so that I can start evaluating it.

Artifacts

Please go here to download SQL Server 2019 :-

  1. Community Technology Preview ( CTP )
    • SQL Server 2019 – CTP
      • SQL Server 2019 CTP
        CTP

 

Installation

Outline

Install Steps

  1. Product Key
    • Choices are
      • Specify a Free Edition
      • Enter the Product Key
    • Accept default of Free Edition ( Evaluation )
  2. License Terms
    • Accept the License Terms
  3. Install Setup Files
    • The files that will be used for the setup are themselves installed
  4. Feature Selection
    • Please review features
    • And, select the features you want
      • Database Engine
        • We want the Database Engine
          • Please specify the drive and folder where the database engines files will be installed
          • By default the system drive ( C: ) will be targeted
  5. Instance Configuration
    • Choices
      • Choose Instance
        • Default
        • Named Instance
      • If a default instance is not already installed, consider accepting the default choice
      • Else, if another instance already exists, please specify a name to assign the instance you are installing
    • Our choice
      • We have a default instance installed on same box
      • We entered a new name
  6. Server Configuration
    • Choices
      • Review Services
        • Service Account Name
        • Startup Type
    • Our choice
      • We chose go with the default Service Account Names
  7. Database Configuration
    • TempDB
      • Data files
        • Drive and Folder to locate TempDB Data Files
        • Initial Size
        • File Growth
        • Based on number of CPU Cores
          • Choose # of TempDB files
      • Log Files
        • Drive and Folder to locate TempDB Log Files
  8. Analysis Services Configuration
    • Tab :- Server Configuration
      • Please add the accounts ( Active Directory and Local Accounts that should have access )
  9. Ready To Install
  10. Installation Progress
  11. Complete
    • Restart Computer
      • As there are pending file operations, please advance towards completing the install, gracefully exiting the Install application,  and restart your computer

Server Network Configuration

Post Installation and Computer Reboot Steps:

  1. Restarted Computer
  2. Launch Server Network Configuration
  3. Adjust Instance Network Configuration

Screenshots

Install Steps

Product Key

productKey.01.20190411.1126AM.PNG

License Terms

licenseTerms.01.20190411.1127AM.PNG

Install Setup Files

installSetupFiles.01.20190411.1127AM.PNG

Feature Selection

Initial

FeatureSelection.01.20190411.1128AM.PNG

Customize

FeatureSelection.02.20190411.1130AM.PNG

Instance Configuration

Initial

instanceConfiguration.01.20190411.1131AM.PNG

Revise

instanceConfiguration.02.20190411.1132AM.PNG

 

Server Configuration

Initial

serverConfiguration.01.20190411.1134AM.PNG

Revise

serverConfiguration.02.20190411.1136AM.PNG

Database Configuration

Initial

DatabaseEngineConfiguration.01.20190411.1137AM.PNG

Revise
Revise – Tab – TempDB

DatabaseEngineConfiguration.Tab.TempDBFiles.01.20190411.1138AM.PNG

Analysis Services Configuration

Revise

AnalysisServicesConfiguration.01.20190411.1140AM.PNG

Ready to Install

Installation Progress

installProgress.01.20190411.1146AM.PNG

Complete

Restart Computer

ComputerRestartRequired.01.20190411.1205PM.PNG

Complete
Complete.01.20190411.1206PM.PNG

Sql Server Configuration Manager

SQL Server Network Configuration

Protocols
Protocol – TCP/IP Properties

SQLServerConfigurationManager.01.20190412.0117PM.PNG

 

Summary

Considerations

  1. Upgrade Path
    • Edition – Evaluation
      • You can advance a previous version to v2019 only if the current version is Evaluation
  2. Post Upgrade
    • Review Services
      • Please stop and disable previous services
    • Amend Network Ports
      • Only one service can use a network port at a time
      • Review each serview and amend the Network Port it is using

 

SQL Server :- Error – “Cannot open database version [version #]. Upgrade the database to the latest version.”

Background

Upgrading our SQL Server Instances and noticed that our database backup job is failing.

Error Message

The error message read :-

Cannot open database ” version [nnn]. Upgrade the database to the latest version.

TroubleShooting

When we upgrade our SQL Server Instance, each individual database is upgraded to the new version.

Read-Only databases are skipped.  The reason is that they are read-only not just to user processes, but also to system processes, as well.

Remediation

Please change database from read only to read-write

SQL

Syntax


alter database [database]
	set read_write

Sample


alter database [hrdb]
	set read_write

Output


Converting database 'hrdb' from version 782 to the current version 869.
Database 'hrdb' running the upgrade step from version 782 to version 801.
Database 'hrdb' running the upgrade step from version 801 to version 802.
Database 'hrdb' running the upgrade step from version 802 to version 803.
Database 'hrdb' running the upgrade step from version 803 to version 804.
Database 'hrdb' running the upgrade step from version 804 to version 805.
Database 'hrdb' running the upgrade step from version 805 to version 806.
Database 'hrdb' running the upgrade step from version 806 to version 807.
Database 'hrdb' running the upgrade step from version 807 to version 808.
Database 'hrdb' running the upgrade step from version 808 to version 809.
Database 'hrdb' running the upgrade step from version 809 to version 810.
Database 'hrdb' running the upgrade step from version 810 to version 811.
Database 'hrdb' running the upgrade step from version 811 to version 812.
Database 'hrdb' running the upgrade step from version 812 to version 813.
Database 'hrdb' running the upgrade step from version 813 to version 814.
Database 'hrdb' running the upgrade step from version 814 to version 815.
Database 'hrdb' running the upgrade step from version 815 to version 816.
Database 'hrdb' running the upgrade step from version 816 to version 817.
Database 'hrdb' running the upgrade step from version 817 to version 818.
Database 'hrdb' running the upgrade step from version 818 to version 819.
Database 'hrdb' running the upgrade step from version 819 to version 820.
Database 'hrdb' running the upgrade step from version 820 to version 821.
Database 'hrdb' running the upgrade step from version 821 to version 822.
Database 'hrdb' running the upgrade step from version 822 to version 823.
Database 'hrdb' running the upgrade step from version 823 to version 824.
Database 'hrdb' running the upgrade step from version 824 to version 825.
Database 'hrdb' running the upgrade step from version 825 to version 826.
Database 'hrdb' running the upgrade step from version 826 to version 827.
Database 'hrdb' running the upgrade step from version 827 to version 828.
Database 'hrdb' running the upgrade step from version 828 to version 829.
Database 'hrdb' running the upgrade step from version 829 to version 830.
Database 'hrdb' running the upgrade step from version 830 to version 831.
Database 'hrdb' running the upgrade step from version 831 to version 832.
Database 'hrdb' running the upgrade step from version 832 to version 833.
Database 'hrdb' running the upgrade step from version 833 to version 834.
Database 'hrdb' running the upgrade step from version 834 to version 835.
Database 'hrdb' running the upgrade step from version 835 to version 836.
Database 'hrdb' running the upgrade step from version 836 to version 837.
Database 'hrdb' running the upgrade step from version 837 to version 838.
Database 'hrdb' running the upgrade step from version 838 to version 839.
Database 'hrdb' running the upgrade step from version 839 to version 840.
Database 'hrdb' running the upgrade step from version 840 to version 841.
Database 'hrdb' running the upgrade step from version 841 to version 842.
Database 'hrdb' running the upgrade step from version 842 to version 843.
Database 'hrdb' running the upgrade step from version 843 to version 844.
Database 'hrdb' running the upgrade step from version 844 to version 845.
Database 'hrdb' running the upgrade step from version 845 to version 846.
Database 'hrdb' running the upgrade step from version 846 to version 847.
Database 'hrdb' running the upgrade step from version 847 to version 848.
Database 'hrdb' running the upgrade step from version 848 to version 849.
Database 'hrdb' running the upgrade step from version 849 to version 850.
Database 'hrdb' running the upgrade step from version 850 to version 851.
Database 'hrdb' running the upgrade step from version 851 to version 852.
Database 'hrdb' running the upgrade step from version 852 to version 853.
Database 'hrdb' running the upgrade step from version 853 to version 854.
Database 'hrdb' running the upgrade step from version 854 to version 855.
Database 'hrdb' running the upgrade step from version 855 to version 856.
Database 'hrdb' running the upgrade step from version 856 to version 857.
Database 'hrdb' running the upgrade step from version 857 to version 858.
Database 'hrdb' running the upgrade step from version 858 to version 859.
Database 'hrdb' running the upgrade step from version 859 to version 860.
Database 'hrdb' running the upgrade step from version 860 to version 861.
Database 'hrdb' running the upgrade step from version 861 to version 862.
Database 'hrdb' running the upgrade step from version 862 to version 863.
Database 'hrdb' running the upgrade step from version 863 to version 864.
Database 'hrdb' running the upgrade step from version 864 to version 865.
Database 'hrdb' running the upgrade step from version 865 to version 866.
Database 'hrdb' running the upgrade step from version 866 to version 867.
Database 'hrdb' running the upgrade step from version 867 to version 868.
Database 'hrdb' running the upgrade step from version 868 to version 869.

SQL Server – Installation/Upgrade – Extract package files

Background

Here I am struggling to install Microsoft SQL Server.

It just will not install.

And, stubborness is not bailing me out.

Take a look at the files

Googled till I bled.

One of things I was asked to do was to take a look at the installation package.

Artifacts

RTM

For the RTM, which is delivered as an ISO, we are able to do the following :-

  1. Mount ISO
  2. Extract using 7-Zip

Service Packs ( SP ) and Cumulative patch ( CU )

Service Packs ( SP ) and Cumulative patches ( CU ) are delivered as an executable.

To uncompress them, please use the executable file itself and pass along the -extract argument.

Command Line

Syntax

[package] -extract:[target-folder]

Sample

SQLServer2017-KB4466404-x64.exe -extract:extract

Output

extract.20190313.1108AM.PNG

SQL Server – v2019 – OS Requirements

Background

We are 3 months into 2019, and it is way past time to start evaluating SQL Server v2019.

Product Development Cycle

In terms of community participation, the product engagement cycle is :-

  1. Community Technology Preview ( CTP )
  2. Release Candidate
  3. Release To Manufacturing
  4. Cumulative Updates

Within each of this cycle, there will be tiered releases.

For instance :-

  1. Community Technology Preview ( CTP )
    • CTP 1
    • CTP 2
    • CTP 3
  2. Release Candidate
    • RC 1
    • RC 2
  3. Release To Manufacturing
    • RTM
  4. Cumulative Updates
    • CU 1
    • CU 2
    • CU 3
    • CU [n]

 

Where are we ?

As of today, we are at “SQL Server 2019 CTP 2.3 Evaluation Edition”.

 

Requirements

Blocking issue

Will love to download, install, and start playing and planning along.

But, for this issue :-

evaluationedition.20190304.0515PM.PNG

My OS is MS Windows 2012, but I need to be on at least MS Windows 2016.

Official

Link

hardwareANDsoftwareRequirements.20190304.0635PM

 

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.