AWS/EC2 – Instance – Metadata

Background

Continuing with our study on AWS/EC2.

Let us quickly cover how we go about querying the EC2 instance for rudimentary system information.

Environment

On the instance itself, please send a payload against Link-local address; specifically 169.254.169.254.  The full URL is http://169.254.169.254/latest/meta-data/

Tool

We are on MS Windows and we have browsers loaded and we will use them as simple HTTP Client.

if you are on Linux and all you have is a terminal mode connection, please use curl ( Client URL ).

Top Level

Here is our top level node.

Browser

Block Device Mapping

Lists Storage Devices; please note only block devices and not RAW Storage Devices.

Please also keep in mind detailed storage info is not available; merely names.

Images

Image-Block Device Mapping

hostname

Images

Image-hostname

instance-id

Images

Image-instance-id

instance-type

Images

Image-instance-type

hostname

Images

hostname

local-hostname

Images

Image-Localhostname

local-ipv4

Images

Image-Local IP4 Address

Mac

Please read further; specifically under Network\Interface\Macs.

Network

interface

Macs

Network Mac Addresses which can be fairly useful for network troubleshooting, etc.

Images
Network Mac -01

Placement

Availability Zone

Here we talk about Country/Region/Availability Group.

In our case we are in

  1. Country :- US
  2. Region :- East Coast
  3. Availability Zone :- 1C

Images

Image.us-east-1c

Security-Groups

Security Groups are the Security groups each instance is assigned.

In the screen shots below we have two distinct results.

First is workplace for AWS Workplace node and the second is one of our custom security groups.

Images

Image – workplace

Image – taskRunner

Summary

Quick summary.

Instance metadata allows a bit of introspection on each EC/2 instance.

Akin to Windows Management Interface, WMI, in MS Windows.

References

  1. Amazon
    • AWS Documentation » Amazon EC2 » User Guide for Windows Instances » Amazon EC2 Instances » Configuring Your Windows Instance
      • Instance Metadata and User Data
        Link
    • AWS Documentation » Amazon EC2 » User Guide for Windows Instances » Monitoring Amazon EC2 » Monitoring the Status of Your Instances
      • Scheduled Events for You
        Link
  2. Curl for Windows

Eclipse/Hibernate – Installation – Day/01

 

Outline

  1. Download & Install Eclipse
  2. JDBC Driver
    • Microsoft JDBC Driver
      • Download Microsoft JDBC Driver
  3. Configure Database
    • Microsoft SQL Server
      • Provision Database
        • WideWorldImporters ( Sample Database )
          • Restore Database
      • Provision Principal
        • Create SQL Server Principal
        • Grant Principal access to database
        • Grant principal access to database objects
  4. Configure Eclipse
    • Install Components
      • JBoss Tools
        • Hibernate

Tasks

Download & Install Eclipse

Please peruse this post per installing Eclipse :-

Eclipse IDE – Installation On MS Windows
Link

JDBC Drivers

Our database back-end is Microsoft SQL Server and so we will reach for Microsoft SQL Server JDBC Drivers.

Microsoft

SQL Server

JDBC Driver
Links
  1. Microsoft JDBC Driver for SQL Server
    Link
  2. Download Microsoft JDBC Driver for SQL Server
    Link
Available

Here are all the drivers available as of 2019-May-6th :-drivers.microsoft.jdbc.20190506.1231PM

We are on 7.0.

And, we will cover that in this post!

Database

Again, our database back-end is Microsoft SQL Server.  There are a few sample databases on that platform; the latest official is WorldWideImporters.

Microsoft

SQL Server

Worldwide Importers
Links
  1. SQL Samples
    Link
  2. Wide World Importers
    • Wide World Importers sample database v1.0
      Link
Brochure

Brochure

microsoft.sample.20190506.1246PM

Choice

We can go with traditional database backup files ( bak ).

Or with bacpac.

We chose :-

SQL Server 2016 SP1 (or later) Any Edition aside from LocalDB; SQL Server 2016 RTM (or later) Evaluation/Developer/Enterprise Edition
WideWorldImporters-Full.bak

Restore database

Launched SQL Server Management Studio and restored the backup file


USE [master]
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\'
go

exec master..xp_create_subdir N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\'
go

RESTORE DATABASE [WideWorldImporters]
FROM  DISK = N'C:\downloads\Microsoft\SQLServer\Sample\WideWorldImporters\WideWorldImporters-Full.bak'
WITH
       FILE = 1
    ,  MOVE N'WWI_Primary' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters.mdf'
    ,  MOVE N'WWI_UserData' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_UserData.ndf'
    ,  MOVE N'WWI_Log' TO N'C:\Microsoft\SQLServer\Logfiles\WideWorldImporters\WideWorldImporters.ldf'
    ,  MOVE N'WWI_InMemory_Data_1' TO N'C:\Microsoft\SQLServer\Datafiles\WideWorldImporters\WideWorldImporters_InMemory_Data_1'
    ,  NOUNLOAD
    ,  STATS = 5
    ,  REPLACE

GO
Create Database Account

Let us create SQL Server Account


use [WideWorldImporters]
go

/*
    SQL Server Principal - helloDB
*/
if suser_id('helloDB') is null
begin

    create login [helloDB]
    with password = 'Nu3Hu5RuSt'
       , DEFAULT_DATABASE = [WideWorldImporters]
       , CHECK_EXPIRATION = OFF
       , CHECK_POLICY = OFF

end
go

/*
    SQL Server Principal - helloDB
*/
if user_id('helloDB') is null
begin

    create user [helloDB]
    from login [helloDB];

end
go

grant select, insert, update, delete on schema::[Application] to [helloDB]
go

grant select, insert, update, delete on schema::[Purchasing] to [helloDB]
go

grant select, insert, update, delete on schema::[Sales] to [helloDB]
go

grant select, insert, update, delete on schema::[Warehouse] to [helloDB]
go

jboss.org

Available Software

In the Work With entry box, please enter :-

http://download.jboss.org/jbosstools/photon/development/updates

Once entered please click the “Add…” button

install.AvailableSoftware.02.WorkWith.20190505.0225PM.PNG

Add Repository

Please give a Name for the Repository we are adding.

In our case we chose jboss.

install.AvailableSoftware.03.WorkWith.20190505.0225PM.PNG

 

Check the items that you wish to install

Using Web Services, jboss.org is reached.

Available Items are returned and displayed.

install.AvailableSoftware.05.AddRepository.20190505.0228PM.PNG

Item – jBoss Data Services Development

We selected the following items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.06.AddRepository.20190505.0229PM.PNG

Item – jBoss Data Services Development – Install Details

Please review the selected items :-

  1. JBoss Data Services Development
    • Hibernate Tools

install.AvailableSoftware.07.AddRepository.20190505.0230PM.PNG

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

 

Item – Review Licenses

Please review License Agreement

  1. License text(for Hibernate Tools 5.4.4.v20190328-1505)

Item – Installing Software

 

Item – Security Warning

Security Warning – The authenticity or validity of the software cannot be established

Even Hibernate.org is shipping software without SSL Certificates?

 

It is good, please click “Install anyway”.

Restart

Please restart Eclipse ID to effect the changes.

Restarted

Upon restart, we are asked to opt in for anonymous usage statistics.

Summary

Enough shenanigans for Day 01.

 

 

SQL Server – Error – “Full-Text Search is not installed, or a full-text component cannot be loaded”

Background

While trying to deploy an Application that I am working on ran into an expected error.

Error

Error Image

FullTextSearchIsNotInstalled.20181209.0457PM

Error Message


Msg 7609, Level 17, State 5, Line 31513
Full-Text Search is not installed, or a full-text component cannot be loaded.

Troubleshooting

Transact SQL

FULLTEXTSERVICEPROPERTY

Code


SELECT
	[IsFullTextInstalled]
	  = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Output

metadata.FULLTEXTSERVICEPROPERTY.No.20181209.0511PM

Explanation

Full-text is not installed.

Remediation

Install and configure Full Text.

Outline

  1. Install
    • Install Fulltext
  2. Restart MS SQL Server Engine
  3. Validate FullText Installation

 

Install

Install Fulltext

Code

Syntax

sudo yum install -y {package}

Sample

sudo yum install -y mssql-server-fts

Output
Output – 01

yum.install.mssql-server-fts.20181209.0528PM.PNG

Output – 02

yum.install.mssql-server-fts.20181209.0537PM

Restart SQL Server Engine Services

systemctl

Syntax


sudo systemctl restart {service}

Sample

sudo systemctl restart mssql-server

FULLTEXTSERVICEPROPERTY

Code


SELECT
	[IsFullTextInstalled]
	  = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled')

Output

metadata.FULLTEXTSERVICEPROPERTY.Yes.20181209.0545PM

Explanation

Full-text is installed.

 

References

  1. Docs \ SQL \ SQL Server on Linux
    • Install SQL Server Full-Text Search on Linux
      Link

Linux :- ODBC / SQL Server – Configuration

Background

On our Linux box, wanted to configure a Database Source to a SQL Server Instance.

Lineage

In a previous post, we installed SQL Server Command Line Tools and ODBC Development Tools.

That post is :-

SQL Server On Linux – Installing Command Line Tools
Link

ODBC Manager

Here are the ODBC Managers that ship with a Linux system :-

  1. iODBC
  2. unixODBC
    • Project Homepage
      Link

Microsoft relies on unixODBC and that fact is documented here :-

Docs / SQL / Connect your client to SQL / ODBC / Linux and Mac
Installing the Driver Manager
Link

Install MS SQL Server ODBC Driver

Linux

CentOS

Our os is Linux and so we will get Version Number for that os.

Get Version Number

Syntax

cat /etc/centos-release

Output

linux.version.20181204.1220AM.PNG

Explanation

The returned value is “CentOS Linux release 7.5.1804 (Core)“; version 7.

Installation

Driver

Register Repository

Syntax

sudo su

curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo
exit

Install ODBC Driver ( msodbcsql17 )

Sample

sudo ACCEPT_EULA=Y yum install msodbcsql17

Output

yum.install.20181204.1233AM

Review ODBC Manager

odbcinst

odbcinst -j
Sample
odbcinst -j
Output

odbcinst-j.20181204.0224PM

Explanation
  1. version
    • unixODBC 2.3.1
  2. Drivers
    • /etc/odbcinst.ini
  3. System Data Sources
    • /etc/odbc.ini
  4. File Data Source
    • /etc/ODBCDataSources
  5. User Data Source
    • /home/dadeniji/.odbc.ini

Review Installed Library ( msodbcsql17 )

Folder List

Sample
ls -l /opt/microsoft/msodbcsql17/lib64
Output

msodbcsql.lib64.20181204.1241AM

ldd ( Print Shared Object’s Dependency )

Objective

Validate that the ODBC Driver can be successfully accessed and loaded.

Sample
ldd /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1
Output

ldd.20181204.0550AM

dltest ( Library Symbol Test )

Objective

Test that driver’s module can be loaded and that specific function can be invoked.

Syntax

dltest /opt/microsoft/msodbcsql17/lib64/libmsodbcsql[version-number] SQLGetInstalledDrivers

Sample

dltest /opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.2.so.0.1 SQLGetInstalledDrivers

Output

dltest.20181204.1244AM

Data Source Registration

/etc/odbcinst.ini

cat

Sample

cat /etc/odbcinst.ini
Output

odbcinst.ini.20181204.1249AM

Data source Registration

/etc/odbc.ini

vi

sudo to vi and edit /etc/odbc.ini.

Add entries for targeted SQL Server Instance.

Sample

sudo vi /etc/odbc.ini
Output

odbc.ini.20181204.1257AM

List configured Data Sources

Syntax

odbcinst -q -s
Sample

odbcinst -q -s
Output

odbc.list.20181204.0100AM

Query

isql

Launch

Syntax


isql -v [datasource] [user] [pwd]

Sample


isql -v bible dadeniji mystupidpwd

Output

launch.20181204.0108AM

Query

Query – Get Current Database

Sample

select db_name as [dbname]

Output

query.getDBName.20181204.0207PM

Referenced Work

  1. Microsoft
  2. capside
    • Using Azure SQL Database and Azure SQL DataWarehouse with Perl
      Link
  3. MAPR
    • Support Portal
      • How to test ODBC connections for debugging in Linux using unixODBC
        Link
  4.  mkleehammer/pyodbc
    • mkleehammer/pyodbc
      • Connecting to SQL Server from RHEL or Centos
        Link
  5. Snowflake
    • DOCS » CONNECTING TO SNOWFLAKE » ODBC DRIVER
      • INSTALLING AND CONFIGURING THE ODBC DRIVER FOR LINUX
        Link
  6. azurewebsites
    • Create PHP apps using SQL Server on RHEL
      Link
  7.  isql
  8. Man7.org
    • Linux Programmers Manual
  9. unixodbc
    • odbcinst.ini
      • unixODBC without the GUI
        Link
    • dltest
      • dltest man page
        Link
  10. systutorials.com
    • odbcinst
      • odbcinst (1) – Linux Man Pages
        Link
  11. SQReam
    • ODBC for Linux documentation
      Link

SQL Server On Linux – Integration Services – Installation

Background

Let us install SQL Server Integration Services on our Linux system.

os

Our os is centOS and so we will be using yum as our Installer.

Installation

Installer

Find Installer Installer

yum search

syntax

yum search [search-tag]

sample

yum search mssql

output

yum.search.20181202.0326AM.PNG

Explanation
  1. Our match is
    • mssql-server-is.x86_x64

 

Confirm Installer

yum info

syntax

yum info [tag]

sample

yum info mssql-server-is.x86_64

output

yum.info.20181202.1131AM.PNG

Explanation
  1. Package Info
    • Name :- mssql-server-is
    • Architecture :- x86_64
    • Version :- 14.0.3015.40
    • Size :- 815 MB
    • Server :- Microsoft SQL Server Integration Services

 

Install

yum install

syntax


yum install [search-tag]

sample


yum install mssql-server-is

output

output -01

yum.install.20181202.0328AM.PNG

output -02

yum.install.20181202.0334AM.PNG

Explanation

Package downloaded & installed.

 

Confirm Installation

ssis-conf

Configure ssis by invoking /opt/ssis/bin/ssis-conf.

Please pass along the setup argument.

syntax


sudo /opt/ssis/bin/ssis-conf setup 

sample


sudo /opt/ssis/bin/ssis-conf setup 

output

  1. We are prompted for the following :-
    • SQL Server Edition
    • Agreement to License Terms

output

output -01

configure.20181202.0337AM.PNG

 

Review Installation

Services

systemctl

systemctl – list

syntax

systemctl --all 

sample

systemctl --all | grep "Microsoft"

output

systemctl.20181202.1152AM.PNG

Explanation

The listed services are :-

  1. mssql-server.service
    • Database Engine
  2. ssis-telemetry.service
    • SQL Server Integration Services
      • Telemetry

We noticed that unlike the Windows Install, we do not have an actual Integration Services Engine; just the telemetry app.

 

References

  1. Microsoft
    • Docs / SQL / SQL Server on Linux
      • Install SQL Server Integration Services (SSIS) on Linux
        Link
      • Configuration SQL Server Integration Services on Linux with ssis-configure-ssis
        Link
      • Limitations and known issues for SSIS on Linux
        Link
      • Schedule SQL Server Integration Services package execution on Linux with cron
        Link

 

SQL Server On Linux – Installing Command Line Tools

Background

It is time to start playing around with command line client tools for SQL Server On Linux.

os

Each os has its own installation platform.

Our os is CentOS and so that is the os we singularly target.

Components

Here are the actual components that are part of SQL Server Client Tools :-

  1. sqlcmd
    • SQL Query Tools
  2. bcp
    • Transfer data in and out from SQL Server to text-file

 

Repositories

Review Registered Repositories

Syntax


yum repolist

Output

yum.repolist.20181130.0947PM

Explanation

  1. packages-microsoft-com-prod
    • That is the repo we are are looking for

Register Repository

If the Repository is not registered, please register it!

Syntax

sudo curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/msprod.repo

Output

yum.register.20181130.1000PM

Explanation

  1. Added Repositories
    • Added msprod.repo

 

Install Application

Is Application installed

Syntax


sudo yum list installed | grep -i mssql

Output

yum.list.installed.20181130.0908PM

Explanation

  1. Review installed mssql applications
    • mssql-server.x86_64
      • Server
    • client???

Review Applications

List all available applications.

Our options are :-

  1. yum list
  2. yum search

yum list

Syntax

yum list | grep -i mssql

Output

yum.list.20181130.0903PM

yum search

Syntax


yum search mssql

Output

yum.search.20181201.0757AM

Explanation

  1. The applications we need are :-
    • mssql-tools.x86_64

 

Application Info

List all available applications.

Syntax

yum info mssql-tools.x86_64

Output

yum.info.mssql-tools.x86_64.20181201.0736AM

Install Application

Install application.

Syntax

sudo yum install mssql-tools unixODBC-devel

Output

yum.install.mssql-tools.20181130.1045PM.PNG

 

Application Installed Location

whereis

Syntax


whereis [app]

Sample


whereis sqlcmd


Sample


whereis bcp

Output

whereIs.20181201.0742AM

Explanation

  1. Our applications, sqlcmd and bcp, are installed in the /opt/mssql-tools/bin folder

 

Invoke Application

sqlcmd

Syntax


/opt/mssql-tools/bin/sqlcmd -S localhost -U dadeniji -Q "select @@servername as servername"

Output

query.servername.20181130.0930PM

Explanation

  1. We are prompted for the password to our username
  2. We entered the username
  3. And, the query returns the username

 

References

  1. Microsoft
    • Docs / SQL / SQL Server on Linux
      • Install sqlcmd and bcp the SQL Server command-line tools on Linux
        Link

SQL Server On Linux – OS Processes – Which is which?

Background

In our previous post we spoke about the OS Processes that under-girdles SQL Server when running on Linux.

We mentioned that on the Developer edition one will have two processes.

Let us see how to tell them apart.

Lineage

  1. SQL Server On Linux – OS Processes
    Link

Outline

  1. OS Tools
    • Processes
      • ps

Processing

OS Tools

Processes

ps

Sample

ps -eo pid,ni,pri,psr,pcpu,pmem,stat,wchan:40,user,comm,stime,time,sz | egrep "sql|PID"

Output

ps.20181129.0841AM

Explanation

Hopefully from the metrics above it should be clear which one is the engine and which one is CEIP.

Referenced Work

  1. Lawrence Livermore National Laboratory
    • Commands Reference