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 – Configuration – Processors

Background

There are a few ways to throttle SQL Server Performance.

We can cap the number of memory available.

We can also cap the number of processors that it can use.

 

Configuration – Processor

SQL Server Management Studio

Outline

If we are to limit CPU availability to a lone processor :-

  1. Launch SSMS
  2. Connect to the SQL Server Instance
  3. Right click on the SQL Server Instance
  4. Choose Properties from the drop-down menu
  5. Access the Processors tab
    • Enable processors
      • Set Processor Affinity to CPU0

Images

processors.ssms.linux.processor.01.20181129.1008pm

Validate

Linux

OS

ps
Command

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

Output

 

 

linux.ps.20181129.0757pm

Explanation
  1. For our SQL Server Engine Process :-
    • Our PSR is at 1

 

/proc/[pid]/stat
Syntax

cat /proc/[PID]/stat | cut -d ' ' -f39

Command

cat /proc/24333/stat | cut -d ' ' -f39

Output

proc.stat.20181129.1014pm.PNG

Explanation
  1. For our SQL Server Engine Process :-
    • Our PSR is at 1

 

taskset
Syntax

taskset -c -p [PID]

Command

taskset -c -p 24333

Output

taskset.20181129.1021pm

Explanation
  1. For our SQL Server Engine Processor :-
    • Our current affinity list is set to 0-3
      • All processes
        • This does not seem right

 

Referenced Work

  1. Stack Exchange
    • Unix & Linux
      • Determining the particular processor on which a process is running
        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

 

 

SQL Server On Linux – OS Processes

Background

Let us review which OS Processes underpins SQL Server when running on a Linux Server.

Outline

  1. OS Tools
    • Services
      • systemctl
    • Processes
      • ps

 

Processing

OS Tools

Services

systemctl

Sample

sudo systemctl restart mssql-server.service

Output

osProcesses.20181128.0641PM

Example
  1. Process
    • Process Count :- 2

 

Processes

ps

Sample

ps -eo pid,ni,pri,psr,pcpu,stat,wchan:40,comm | grep sql
Output

ps.20181128.0656PM

Example
  1. Process
    • Process Count :- 2

Summary

In SQL Server for Linux :-

  1. SQL Server Agent
    • built into engine and does it not run wholly in its own process/address space
    • Only Supports SQL payload
  2. Edition
    • Edition :- Developer
      • Teletype is always running as its own process, can not be disabled