Linux

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

Daniel Adeniji Full-Text Search, FULLTEXTSERVICEPROPERTY, Linux, Microsoft, SQL Server, Technical

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

Daniel Adeniji CentOS, dltest ( unixodbc - utility ), ISQL, ldd, Linux, Microsoft, Microsoft ODBC Driver 17 for SQL Server ( ODBC - Driver - Microsoft ), Microsoft ODBC Driver for SQL Server on Linux and macOS ( ODBC - Driver ), ODBC ( SQL Server ), odbcinst, SQL Server, UnixODBC, utility ( unixodbc ), v7 ( centOS - Version ), Version ( centOS)

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

Daniel Adeniji Integration Services, Linux, Microsoft, Microsoft Baseline Configuration Analyzer, SSIS

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

Daniel Adeniji bcp, CentOS, Linux, Microsoft, SQL Server, SQLCmd, SQLCMD, Technical, whereis, yum

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



 


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. 

  2. We entered the username
    3. 

  3. And, the query returns the username
    4. 



 




References


    

  1. Microsoft

      

    • Docs / SQL / SQL Server on Linux

        

      • Install sqlcmd and bcp the SQL Server command-line tools on Linux
        
Link
        • 

      

      • 

    

    2. 


					

		
		
	




			
				


	

		

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

								

								 Daniel Adeniji
															Linux, Microsoft, SQL Server, Technical					
												

					


				

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



Outline


    

  1. OS Tools

      

    • Processes

        

      • ps
        • 

      

      • 

    

    2. 



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


      • 

    

    2. 



 


 

					

		
		
	




			
				


	

		

					
SQL Server On Linux – OS Processes

								

								 Daniel Adeniji
															Linux, Microsoft, SQL Server, Technical					
												

					


				

			
Background


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


Outline


    

  1. OS Tools

      

    • Services

        

      • systemctl
        • 

      

      • 

    • Processes

        

      • ps
        • 

      

      • 

    

    2. 



 


Processing


OS Tools


Services


systemctl


Sample



sudo systemctl restart mssql-server.service




Output


osProcesses.20181128.0641PM


Example


    

  1. Process

      

    • Process Count :- 2
      • 

    

    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
      • 

    

    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. 

  2. Edition

      

    • Edition :- Developer

        

      • Teletype is always running as its own process, can not be disabled
        • 

      

      • 

    

    3. 


					

		
		
	




			
				


	

		

					
SQL Server On Linux – Post Installation – Configuration – Set Folders

								

								 Daniel Adeniji
															Linux, Microsoft, SQL Server, SQL Server Management Studio (SSMS), Technical, v2017 - ( SSMS - Version ), Version ( SSMS )					
												

					


				

			
Background


Upon installation, SQL Server on Linux utilizes the same storage folders for data, log, and backups.


Outline


    

  1. Review Current Settings
    2. 

  2. Provision Folders
    3. 

  3. Register Folder Settings on SQL Server
    4. 

  4. Restart SQL Server
    5. 

  5. Review new folder settings
    6. 





Processing


Review Current Settings


SQL Server Management Studio ( SSMS )


Let us use our GUI Tool, SSMS, to determine current default folder settings.


Server Properties


Server Properties – Database Settings


Images


configuration.defaultLocations.ssms.20181127.1135AM


Explanation


    

  1. Data, Log, Backup

      

    • /var/opt/mssql/data/
      • 

    

    2. 



Provision Folders


Outline


    

  1. Create An Array
    2. 

  2. Fill Array with list of folders
    3. 

  3. Iterate Array List

      

    • Get Folder
      • 

    • Determine if folder exists
      • 

    • If Folder does not exist, please create it
      • 

    • Set Folder Attributes

        

      • Set Folder Owner to mssql
        • 

      • Set Folder Group to mssql
        • 

      

      • 

    

    4. 



Script



#!/bash/bash

echo "Preparing folders"

declare -a _folderList=(
                          "/app/Microsoft/sqlServer/data"
                          "/app/Microsoft/sqlServer/log"
                          "/app/Microsoft/sqlServer/backup"
                          "/app/Microsoft/sqlServer/dumpCrash"
)

#Get Number of elements
_iNumberofElements=${#_folderList[*]}

echo "Number of elements $_iNumberofElements "

_i=0

while [ $_i -lt $_iNumberofElements ]
do

  #Current Folder
  _folder=${_folderList[_i]}

  _folderExistence=false

   #Does Folder Exist
   if [ -d $_folder ]
   then

     _folderExistence=true

   fi

   #Speak Folder
   echo "$_i ) $_folder "

   #if Folder does not exist, create it
   if [ $_folderExistence = false ]
   then

       echo -e "\tCreating Folder $_folder"

       mkdir --parents $_folder

  fi

  #Change Folder owner
  echo -e "\tChange Folder Owner"
  chown mssql $_folder

  #Change Folder Group
  echo -e "\tChange Folder Group"
  chgrp mssql $_folder

  #Move to next array element
  _i=$((_i+1))

done






Configure SQL Server Default Folders


Outline


    

  1. Issue folderlocation.default[x]

      

    • Data
      • 

    • Log
      • 

    • Backup
      • 

    • Dump
      • 

    

    2. 



Script



/opt/mssql/bin/mssql-conf set filelocation.defaultdatadir /app/Microsoft/sqlServer/data

/opt/mssql/bin/mssql-conf set filelocation.defaultlogdir /app/Microsoft/sqlServer/log

/opt/mssql/bin/mssql-conf set filelocation.defaultbackupdir /app/Microsoft/sqlServer/backup

/opt/mssql/bin/mssql-conf set filelocation.defaultdumpdir /app/Microsoft/sqlServer/dumpCrash




Restart SQL Server Service


Outline


Restart SQL Server Service to effect changes


Script



sudo systemctl restart mssql-server.service




Review new settings


SQL Server Management Studio ( SSMS )


Let use again use SSMS to review folder settings.


Server Properties


Server Properties – Database Settings


Images


configuration.defaultLocations.ssms.post.20181127.0245PM


Explanation


    

  1. We were able to change our targeted folders

      

    • Data, Log, Backup
      • 

    

    2.