SQL Server on Linux Tutorial

SQL Server on Linux Tutorial

  1. How to Install Linux CentOS 7 for SQL Server Installation
  2. How to Join Linux CentOS 7 to Active Directory
  3. How to Install SQL Server 2017 on CentOS7 Step by Step
  4. How to Configure SQL Server Windows Authentication in Linux CentOS 7
  5. How to Install Cluster Pacemaker on Linux CentOS 7 for SQL Server High Availability
  6. How to Configure SQL Server High Availability Groups in Linux CentOS 7
  7. How to manually Failover SQL Server Availability Group in Linux CentOS 7

How to Join CentOS 7 to an Existing Windows Domain

How to Join CentOS 7 to an Existing Windows Domain


In this video, you will learn How to Join CentOS 7 to an Existing Windows Domain. This video is part of effort in which we finally will install SQL Server on Linux Box. In our last video, you learn how to install Linux CentOS from starch. Script used in this video:



# Installing realmd package






# yum install sssd realmd oddjob oddjob-mkhomedir adcli samba-common samba-common-tools krb5-workstation openldap-clients policycoreutils-python

Edit Host File to add DNS IP address and Server Information

# vi /etc/hosts


View File /etc/resolv.conf It should resolve Domain name and IP address Join with Windows Domain

#realm join --user=clusteradmin tbsdc.Techbrothers.local


Verify domain Join

# realm list



# id clusteradmin@Techbrothers.local


Turning off Fully Qualified Name requirement of AD user

# vi /etc/sssd/sssd.conf


systemctl restart sssd systemctl daemon-reload
Adding user to sudo or admin - Where "wheel" is group and cluster admin is active directory user

# sudo usermod -a -G wheel clusteradmin



How to Join Linux CentOS 7 to Windows Active Directory

How to Install Linux CentOS 7 for SQL Server Installation

How to Install Linux CentOS 7 for SQL Server Installation


In this video you are going to learn how to install Linux Operation System for SQL Server Installation. below are the items you are going to learn in this step by step video for CentOS installation.
  • Download CentOS 7 ISO (Minimul ISO)
  • Create Bootable Flash Drive to boot Physical Machine
  • Boot Server from BIOS with Bootable Flash Drive
  • Setting up Language in CentOS
  • Setting up Installation Destination Media
  • Network Configuration
  • User Configuration
  • Test Installation
Download CentoS 7.6 Link:
https://www.centos.org/download/





FIND_IN_SET Function - How to find position of string in Comma separate values list

FIND_IN_SET Function - How to find position of string in Comma separate values list

FIND_IN_SET function is used to find the position of string in list of comma separated values. FIND_IN_SET can return depending upon the searched value and values present in comma separated list.

Syntax:


FIND_IN_SET( StringtoFind,Comma_Separated_String_List);



  1. Either StringToFind or Comma_Separated_String_List is null, then output is going to be null for find_in_set.
  2. Find_IN_Set will return zero if the StringtoFind is not present in Comma_Separated_String_List.
  3. If StringtoFind is present in Comma_Separated_String_List then positive number will be returned.
Below is example that covers all above scenarios or result set returned by find_in_set.

Select find_in_set('aamir',null),
find_in_set(null,'aamir,shahzad'),
find_in_set('Robert','aamir,shahzad'),
find_in_set('robert','aamir,shahzad,robert,test,test2');


How to use Find_in_set in MySQL to find the position in Comma Delimited value list

How to find line break and carriage return (\r\n) in MySQL - MySQL Tutorial

How to find line break and carriage return (\r\n) in MySQL

You are working as MySQL Developer and you need to write a query that should return all the values from column which has carriage return or line break or both of them.

Let's create sample test table with some sample data and then write the query to get the records with line break and carriage return (\r\n).


create table test(id int, name varchar(100));

-- insert some sample data
insert into test 
values(1,'Aamir\n'),
(2,'shahzad'),
(3,'Robert\r\n'),
(4, 'This is \n test'); 



Below query will return all the values which has \n or \r or both of them.

select * from test;
SELECT * FROM test WHERE
name like '%\n%'
or name like '%\r\n%';


How to find records with line break and carriage return in MySQL - MySQL Tutorial

TRIM Function - How to use TRIM Function in MySQL

TRIM Function - How to use TRIM Function in MySQL

Trim function in MySQL is used to remove unwanted characters from starting of ending of string. There are different ways to remove the unwanted characters by using Trim. You might only want to remove leading or trailing or both.


TRIM('String Value');
LTRIM('String Value');
RTRIM('String Value')


TRIM function above will remove the leading and trailing spaces from String value. LTrim will only remove blank spaces from starting and RTRIM will remove trailing blank spaces from String value.


Select ' TechBrothersIT ',
trim(' TechBrothersIT '),
ltrim(' TechBrothersIT '),
rtrim(' TechBrothersIT ');


How to use Trim Function in MySQL - MySQL Tutorial

There is another way to write the TRIM syntax, if you want to also remove specific characters from trailing or leading you can do that.

Syntax:


TRIM([{BOTH|LEADING|TRAILING} [string you want to remove]] FROM str);


In below example, we are removing starting and trailing spaces and also in last part, we are removing Trailing IT from string.


Select ' TechBrothersIT ',
trim(both from ' TechBrothersIT '),
TRIM(Leading from ' TechBrothersIT '),
TRIM(Trailing from ' TechBrothersIT '),
TRIM(Trailing 'IT' from ' TechBrothersIT');


How to use TRIM Function in MySQL to remove Leading or Trailing Values from String

SUBSTRING Function - How to use SUBSTRING Function in MySQL

SUBSTRING Function - How to use SUBSTRING Function in MySQL


SUBSTRING Function in MySQL is used to extract a substring from a position with specific position.

Syntax:


SUBSTRING(string,position);
SUBSTRING(string,position,length);



So in below example if we want to start from 3rd characters and returned the rest of rows we can do that. In second part of select we are specificity telling start from 4th characters but only returned 4 records from 4th position.


Select substring('My Test string',3),
substring('My Test string',4,4) from customer;


How to use SUBSTRING Function in MySQL - MySQL Tutorial

REPLACE Function - How to find and replace string in value in MySQL

REPLACE Function - How to find and replace string in value in MySQL


REPLACE Function in MySQL can be used to find some string and then replace with new string value you like.

Syntax:


REPLACE(ColumnNameOrValue,old_string,New_string)


Let's create sample table customer and insert some data into it.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Some of the value in firstname has "1" , let's replace that with blank space. Also in phonenumber we have "-" and we would like to replace with ".".

Select firstname,
replace(firstname,'1','') as fname,
phonenumber,replace(phonenumber,'-','.') as pnumber from customer;

How to use Replace function in MySQL - MySQL Developer Tutorial

LEFT Function - How to get Some Characters from LEFT from String in MySQL

LEFT Function - How to get Some Characters from LEFT from String in MySQL

LEFT Function in MySQL is used to get left part of string with specified length.


LEFT('Value',Length)


Let's create sample table customer with sample data and then we will use Left function on different columns to get only left characters according to the length specified.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- insert sample data
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');


Let's get area code those are first 3 characters in phone number and also get Initial Character(First Lettter) from FirstName column.


Select phonenumber,left(phonenumber,3),
firstname,left(firstname,1) from customer;


How to use LEFT Function in MySQL - MySQL Developer Tutorial

Char_length Function - How to find number of characters in string in MySQL

Char_length Function - How to find number of characters in string in MySQL

Char_Length is the function in MySQL that can be used to find the number of characters .

Let's create sample table customer and insert some sample data.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- insert some sample records
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');


Let's use Char_Length to get character count for values in different columns.


Select firstname,char_length(firstname),lastname,
char_length(lastname),phonenumber,char_length(phonenumber) from customer;


Length Function - How to get Data of String in Bytes in MySQL

Length Function - How to get Data of String in Bytes in MySQL

Length function is used to return the size of string in Bytes in MySQL. It is equivalent to DataLength( ) Function in SQL Server.

Syntax: 


Select Length('StringValue');



Let's say we have customer table with some records and we would like to get the data length for firstname and lastname values.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;
-- Insert sample records
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');


use Length( ) function to get the size of column values in Bytes.

Select firstname,length(firstname), lastname,length(lastname),phonenumber,length(phonenumber) from customer;


How to get size of Column in MySQL by using Length ( ) Function - MyQL Tutorial

CONCAT Function - How to Concatenate multiple rows into single filed in MySQL

CONCAT Function - How to Concatenate multiple rows into single filed in MySQL


Concat function in MySQL is used to concatenate string values. Concat function really helps when you need to row values in table.

Syntax:


Concat('firstvalue','secondvalue',.....)


Example :
Let's say if we want to concat two values "aamir" and shahzad" and have space between them. we can use below example.

Select concat('aamir',' ','shahzad') as MyColumn;


How to use Concat function in MySQL or MariaDB
Let's create sample table customer and insert some rows and then we will use Concat to concatenate firstname and lastname column.

CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

-- insert sample data
insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza',null,39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Now let's use CONCAT function in MySQL to concatenate firstname and lastname.

Select *, concat(firstname,' ',ifnull(lastname,'')) as fullname from customer;


I have added ' ' between firstname and lastname, you can add any string in Concat function. I used the space so the full name is more readable.

Use Concat function to concatenate two columns in MySQL Table - MySQL developer Tutorial

Notice that the full name in first row is Null, that is because the lastname in first row is Null. If you will Concat Null with other values, the output is going to be Null. 

To handle Null values, you can use IFNull to replace null with blank space so you don't get the Null output, As shown below.


Select *, concat(IfNull(firstname,''),' ',IfNull(lastname,'')) 
as fullname from customer;


How to use IFNull in Concat function in MySQL to replace null with blank space - MySQL Tutorial

How to create AFTER UPDATE Trigger in MySQL 2nd Method - MySQL Developer Tutorial

How to create AFTER UPDATE trigger in MySQL

You are working as MySQL Developer and you are asked to create a trigger that should keep records of updated records. You are required to keep the old records and after update. To keep the records of updated records, you can use After Update Trigger in MySQL. In below example, we are going to create customer Audit table
Method  2: 
You can have only one set of columns and then an extra column that tells about Record type such as "OLD" or "NEW".
Method 1: 
Create audit table with list of same columns but name them old_column1,old_column2....,new_column1,new_column2....


Let's create sample table "customer" and insert some sample data.


CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Create Audit table which will save the deleted records.

CREATE TABLE `customer_audit` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
  ,RecordType varchar(100)
  ,actiondate datetime
  ,actionby varchar(100)
) ;




Now let's create After Update Trigger. From here you can get the idea how your After Insert Trigger will look like.

Create AFTER UPDATE Trigger in MySQL:



-- drop trigger customer_after_update;
DELIMITER //

CREATE TRIGGER customer_after_update
AFTER update
   ON customer FOR EACH ROW

BEGIN
-- declare variables
  DECLARE var_User varchar(50);
  DECLARE var_date datetime;


   -- Get user name who is performing update
   SELECT USER() INTO var_User;
    SELECT SYSDATE() into var_date;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,actiondate,actionby,RecordType
 )
       VALUES
   ( old.idcustomer,
   old.firstname,old.lastname,old.age,old.phonenumber,
   old.dob,old.gender,var_date,
     var_User,"oldrecord"
   );
   
   insert into customer_audit(idcustomer,firstname,lastname,age,phonenumber,
   dob,gender,actiondate,actionby,RecordType
 )
   VALUES(
   new.idcustomer,
   new.firstname,new.lastname,new.age,new.phonenumber,
   new.dob,new.gender, var_date, var_User,"updatedRecord" );
    

END; //

DELIMITER ;


We declare var_User variable so we can save the user name who is updating the record. We used SYSDATE() function to record the datetime for update.

Now you can update a record in customer table and then check the customer_audit table if all changes are audited successfully.

How to create After Update Trigger in MySQL Method 1 - MySQL Developer Tutorial

How to create AFTER UPDATE trigger in MySQL

You are working as MySQL Developer and you are asked to create a trigger that should keep records of updated records. You are required to keep the old records and after update. To keep the records of updated records, you can use After Update Trigger in MySQL. In below example, we are going to create customer Audit table which will have old columns and new columns, (I am calling this Method1) You can have only one set of columns and then an extra column that tells about Record type such as "OLD" or "NEW", (I am calling this Method2) .  For this example , I have decided to duplicate the columns.


Let's create sample table "customer" and insert some sample data.


CREATE TABLE `customer` (
  `idcustomer` int, 
  `firstname` varchar(50)  NULL,
  `lastname` varchar(30)  NULL,
  `age` int(11) DEFAULT NULL,
  `phonenumber` char(11) DEFAULT NULL,
  `dob` date DEFAULT NULL,
  `gender` char(1) NOT NULL
) ;

insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Raza','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M'),
(3,'Aamir','Shahzad',39,'505-4141900','1980-01-01','M'),
(4,'Aamir1','Shahzad',39,'505-4141900','1980-01-01','M'),
(5,'Robert','Ladson',69,'505-345900','1960-01-01','M');



Create Audit table which will save the deleted records.

CREATE TABLE `customer_audit` (
  `old_idcustomer` int, 
  `old_firstname` varchar(50)  NULL,
  `old_lastname` varchar(30)  NULL,
  `old_age` int(11) DEFAULT NULL,
  `old_phonenumber` char(11) DEFAULT NULL,
  `old_dob` date DEFAULT NULL,
  `old_gender` char(1) NOT NULL,
  `new_idcustomer` int, 
  `new_firstname` varchar(50)  NULL,
  `new_lastname` varchar(30)  NULL,
  `new_age` int(11) DEFAULT NULL,
  `new_phonenumber` char(11) DEFAULT NULL,
  `new_dob` date DEFAULT NULL,
  `new_gender` char(1) NOT NULL
  ,actiondate datetime
  ,actionby varchar(100)
) ;




Now let's create After Update Trigger. From here you can get the idea how your After Insert Trigger will look like.

Create AFTER UPDATE Trigger in MySQL:



DELIMITER //

CREATE TRIGGER customer_after_update
AFTER update
   ON customer FOR EACH ROW

BEGIN
-- declare variables
  DECLARE var_User varchar(50);

   -- Get user name who is performing update
   SELECT USER() INTO var_User;

   -- Insert record into customer_audit table
   INSERT INTO customer_audit
   ( old_idcustomer,old_firstname,old_lastname,old_age,old_phonenumber,
   old_dob,old_gender,
   new_idcustomer,new_firstname,new_lastname,new_age,new_phonenumber,
   new_dob,new_gender,
   actiondate,actionby)
    
   VALUES
   ( old.idcustomer,
   old.firstname,old.lastname,old.age,old.phonenumber,
   old.dob,old.gender,
   new.idcustomer,
   new.firstname,new.lastname,new.age,new.phonenumber,
   new.dob,new.gender,
     SYSDATE(),
     var_User );

END; //

DELIMITER ;


We declare var_User variable so we can save the user name who is updating the record. We used SYSDATE() function to record the datetime for update.

Now you can update a record in customer table and then check the customer_audit table if all changes are audited successfully.