How to use INTERSECT in MySQL or MariaDB - MySQL / MariaDB Developer Tutorial

How to use INTERSECT in MySQL or MariaDB 

INTERSECT operator returns distinct records from two more more query results. To use INTERSECT operator below rules show be satisfied

  1. The number of columns should be same in each query 
  2. The data type of columns should be compatible
Neither MySQL nor MariaDB has INTERSECT operator. We have to work around to achieve INTERSECT operator results. We can use Distinct with INNER JOIN to achieve INTERSECT.

Syntax:


SELECT DISTINCT
    a.column1, a.column2, b.column1, b.column2
FROM
    table1 a
        INNER JOIN
    table12 b ON a.column1 = b.column1
        AND a.column2 = b.column2;


Example: 


Let's create two tables. Customer and Customer1. We will have more records in Customer table and few records in Customer1 table. We will use distinct with Inner Join to achieve Intersect operator results.

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
) ;
 

Let's create Customer1 table with same definition.

CREATE TABLE `customer1` (
  `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
) ;

Let's insert records in Customer table.


insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Aamir','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'); 


Let's write our query for customer and customer1 table to achieve intersect operator results.


SELECT DISTINCT
    a.*
FROM
    customer a
        INNER JOIN
    customer1 b ON a.idcustomer = b.idcustomer
        AND a.firstname = b.firstname
        AND a.lastname = b.lastname
        AND a.age = b.age
        AND a.phonenumber = b.phonenumber
        AND a.dob = b.dob
        AND a.gender = b.gender;


The above query returned two matching distinct records from customer and customer1 table that we will get if INTERSECT operator is used in any other relational database system.


INTERSECT Operator in MySQL / MariaDB - Alternative to INTERSECT Operator in MySQL

How to use Minus in MySQL or MairaDB - MySQL / MariaDB Developer Tutorial

How to use Minus in MySQL or MairaDB

MINUS operator is use to get all the records from first result set which are not present in second result set.
MySQL or MariaDB does not provide the MINUS operator.  We can achieve Minus operation by using Left Join.

Syntax:

SELECT 
    column_list 
FROM 
    table1
    LEFT JOIN table12 ON join_predicate
WHERE 
    table2.id IS NULL;


Example :

Let's create two tables. Customer and Customer1. We will have more records in Customer table and few records in Customer1 table. By using left join , we will achieve results which Minus operator returns in other relational databases systems.



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
) ;

Let's create Customer1 table with same definition.

CREATE TABLE `customer1` (
  `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
) ;

Let's insert records in Customer table.


insert into customer(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Aamir','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');

Now insert fewer records in Customer1 table than Customer table. I am going to only insert two records instead of 3.


insert into customer1(idcustomer,firstname,lastname,age,phonenumber,dob,gender)
values
(1,'Aamir','Ali',39,'505-4141969','1980-01-01','M'),
(2,'Aamir','Naz',39,'505-4141969','1980-01-01','M');

Let's write our Left Join query to achieve Minus operator results on customer and customer1 table.


SELECT 
    a.*
FROM
    customer a
        LEFT JOIN
    customer1 b ON a.idcustomer = b.idcustomer
        AND a.firstname = b.firstname
        AND a.lastname = b.lastname
        AND a.age = b.age
        AND a.phonenumber = b.phonenumber
        AND a.dob = b.dob
        AND a.gender = b.gender
WHERE
    b.idcustomer IS NULL

As customer table has 3 records and customer1 table has only two matching records. The above query is going to return the single row from table customer which does not match exactly with customer1 table rows or not present in customer1 table.
How to use Minus operator in MySQL or MairaDB - Alternative to Minus Operator in MySQL


SQL Server High Availability on Azure Tutorial

How to Configure SQL Server Availability Group in MS Azure

How to Configure SQL Server Availability Group in MS Azure

How to Configure SQL Server Availability Group in MS Azure explains all below steps

  1. How to enable AlwaysOn Availability in SQL Server?
  2. How to prepare database to be added in Availability group?
  3. How to change SQL Server Service accounts ?
  4. Resolved: Can't create Availability Group
  5. Resolved: Account doesn't have permission to access end points
  6. Resolved: Joining takes long time and then fails
How to configure SQL Server Availability Group in MS Azure

How to create SQL Server High Availability Listener in MS Azure

How to create SQL Server High Availability Listener in MS Azure

How to create SQL Server High Availability Listener in MS Azure explains 

  1. How to create SQL Server High Availability Listener in MS Azure?
  2. Error: Cannot connect to High Availability Listener
  3. Resolution: Why can't I connect to HA listener name from Secondary Replica Azure?
  4. How to configure Static IP of HA Listener in Azure?

How to create SQL Server High Availability Listener in Microsoft Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure - SQL Server High Availability on Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure

How to Configure Cluster Cloud Witness Quorum in MS Azure explains all below steps
  1. How to configure Cluster Quorum Cloud witness in MS Azure?
  2. How to access Storage Account in MS Azure?
  3. How to access Access key in MS Azure? 
  4. Resolved: Access key can't be valided
  5. Resolved: An error occurred while validating access key to Azure
  6. Resolved: An error occurred while validating access to Azure from cluster node

How to Configure Cluster Cloud Witness Quorum in MS Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure - SQL Server HA on Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure

How to Create and Configure Load Balancer for AG Listener in MS Azure explains all below steps.

  1. How to create load balancer in Azure Portal?
  2. How to Configure Internal Load Balancer in Azure? 
  3. How to Configure FrontEndIP of Load balancer in Azure?
  4. How to configure backend pool in Load balancer in Azure?
  5. How to configure Health Probes in Azure?
  6. How to configure Load Balancer for Availability Group Listener in Azure?
  7. How to Configure AG listener port in Load Balancer in Azure? 
  8. How to Configure Cluster to use Load balancer in Azure?

Script Used in this video: 
$AG = Get-ClusterResource | Where-Object { $_.resourcetype -eq "SQL Server Availability Group" }
$Listener = Get-ClusterResource | Where-Object { $_.Name -like $AG.Name + "*" -and $_.resourcetype -eq "Network Name" }
$IP = Get-ClusterResource | Where-Object { $_.Name -like $AG.Name + "*" -and $_.resourcetype -eq "IP Address" }

Get-ClusterResource $IP.Name | Set-ClusterParameter -Multiple @{"ProbePort"="80";"OverrideAddressMatch"=1;}
Get-ClusterResource $Listener.Name | Set-ClusterParameter -Name "PublishPTRRecords" -Value 1
Get-ClusterResource $Listener.Name | Set-ClusterParameter -Name "HostRecordTTL" -Value 60


How to Create and Configure Load Balancer for AG Listener in MS Azure

How to Create SQL Server Availability Group Cluster in MS Azure - SQL Server on Azure Tutorial

How to Create SQL Server Availability Group Cluster in MS Azure - SQL Server on Azure Tutorial

How to Create SQL Server Availability Group Cluster in MS Azure explain all below steps

  1. How to add Failover clustering feature of windows in Azure?
  2. How to Prepare nodes to be added in cluster in Azure?
  3. How to run custom cluster configuration tests in Azure?
  4. How to Change IP address of Cluster in DNS in Azure?
  5. How to resolve IP conflict of Cluster in Azure? 
  6. How to start cluster services on Nodes in Azure?
  7. How to start cluster forcefully in Azure? 
  8. How to change Cluster IP in Cluster Manager in Azure?
How to create SQL Server Availability Group Cluster in MS Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure explains all below steps.

1. How to create SQL Server Virtual Machine in Azure?
2. How to create new Availability Group for SQL Server Virtual Machines in Azure?
3. How to setup Static IP of SQL Server Virtual Machine in Azure?
4. How to Setup Custom DNS of a virtual Machine in Azure?
5. How to add Azure Virtual Machine to a domain?
6. How to setup memory of SQL Server in Azure?

How to create SQL Server Virtual Machine for Availability Group Cluster in Azure

How to create Virtual Machine for Domain Controller in Azure

How to create Virtual Machine for Domain Controller in Azure

How to create Virtual Machine for Domain Controller in Azure video explains all below topics
  1. How to create virtual machine in MS Azure? 
  2. How to create Windows Virtual Machine in Azure?
  3. How to create resource group in Azure?
  4. How to create Virtual Network in Azure?
  5. How to create Storage Account in Azure?
  6. How to create new disk in Azure?
  7. How to create Availability Set in Azure?
  8. How to open specific open ports in Azure Virtual Machine?
How to create Virtual Machine for Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure

How to add second Domain Controller to an existing Domain Controller in Azure explains below topics
  1. How to configure DNS of Azure Virtual machine?
  2. Resolved error: Domain Controller can't be contacted in Azure
  3. Adding virtual Machine to existing Domain Controller to act as Domain as well 
  4. How to setup replication between two domain Controllers in Azure?
  5. How to create user in Active Directory in Azure?

How to add second Domain Controller to an existing Domain Controller in Azure

How to create your own Domain Controller in MS Azure -SQL Server High Availability On Azure Tutorial

How to create your own Domain Controller in MS Azure -SQL Server High Availability On Azure Tutorial

How to create your own Domain Controller in MS Azure video explains all below steps in detail

  1. How to connect to virtual machine in MS Azure?
  2. How to configure Virtual Machine's static IP in MS Azure?
  3. How to Enable/Disable Ports and protocol in MS Azure?
  4. How to configure your Own DNS and Active Directory in Azure?

How to create your own Domain Controller in MS Azure

Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial

Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial


Introduction To MS Azure Portal - SQL Server High Availability On Azure Tutorial explains all below steps for SQL Server on Azure starting point

  1. Setting up SQL Server High Availability in MS Azure Portal
  2. How to access MS Azure Portal?
  3. How to setup free Account of MS Azure?
  4. Introduction to MS Azure Portal components

What is MS Azure Portal and How to Setup step by step

How to manually failover SQL Server Availability Group in Linux CentOS 7 - SQL Server Linux Tutorial

How to manually failover SQL Server Availability Group in Linux CentOS 7

How to manually failover SQL Server Availability Group in Linux CentOS 7 explains all the steps you need to perform to failover Availability Group in SQL Server when installed on Linux. You will learn below items in this video 1-- Check Constraints a) Location Constraint b) Ordering Constraint 2 -- Failover Availability Group to Specific Node 3 -- Remove Location Constraint to remove preferred node settings. Scripts used in this video: # Check current owner node of SQL Sever AG pcs status # Check Constraints pcs constraint list --full # Move SQL Server Availability Group resources from One node to another pcs resource move TBSLinuxRG-master TBSLinuxNode2 --master # Remove location constraint for successful Failover pcs constraint remove cli-prefer-TBSLinuxRG-master


How to manually failover SQL Server Availability Group in Linux CentOS 7

How to configure SQL Server High Availability Groups in Linux CentOS 7- SQL Server on Linux Tutorial

How to configure SQL Server High Availability Groups in Linux CentOS 7

How to configure SQL Server High Availability Groups in Linux CentOS 7 explains all below steps to configuration SQL Server High Availability installed on Linux. Installing SQL Server High Availability Package Installing and Enabling SQL Server Agent if its not installed and enabled already Enable SQL server High Availability on each Node Creating AG Group EndPoint and Certificates Copy Certificates of each node into all other Nodes Change ownership and group association to mysql(User) Restore each Certificate with authenticated user ( create user if you don't have already one) Grant AG Group using SSMS Create SQL Server Login and Permission for Pacemaker Create Availability Group resource in pacemaker Create IP for Listener in PackeMaker Create Listener using same IP Test Failover Script used in this video: # Install SQL Server High Availability Package sudo yum install mssql-server-ha # Enable AlwaysOn Avaiability Groups and resetart SQL Server on both nodes sudo /opt/mssql/bin/mssql-conf set hadr.hadrenabled 1 sudo systemctl restart mssql-server # Open SSMS and create Certificate for each node # Node Name : TBSLinuxNode1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123'; GO CREATE CERTIFICATE TBSLinuxNode1_Cert WITH SUBJECT = 'TBSLinuxNode1 AG Certificate'; GO BACKUP CERTIFICATE TBSLinuxNode1_Cert TO FILE = '/var/opt/mssql/data/TBSLinuxNode1_Cert.cer'; GO CREATE ENDPOINT TBSSQLAG STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE TBSLinuxNode1_Cert, ROLE = ALL); GO #Now samething on Node2 (TBSLinuxNode2) CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Pass@123'; GO CREATE CERTIFICATE TBSLinuxNode2_Cert WITH SUBJECT = 'TBSLinuxNode2 AG Certificate'; GO BACKUP CERTIFICATE TBSLinuxNode2_Cert TO FILE = '/var/opt/mssql/data/TBSLinuxNode2_Cert.cer'; GO CREATE ENDPOINT TBSSQLAG STATE = STARTED AS TCP ( LISTENER_PORT = 5022, LISTENER_IP = ALL) FOR DATABASE_MIRRORING ( AUTHENTICATION = CERTIFICATE TBSLinuxNode2_Cert, ROLE = ALL); GO # Copy Certificate of one node to other using SCP # on Node1 scp -r root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode1_Cert.cer # On Node 2 scp -r root@TBSLinuxNode2:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer root@TBSLinuxNode1:/var/opt/mssql/data/TBSLinuxNode2_Cert.cer # Change Ownership of certificate to mssql on each node(In my case I have only two nodes) sudo chown mssql:mssql TBSLinuxNode2_Cert.cer sudo chown mssql:mssql TBSLinuxNode1_Cert.cer # Create instance Level SQL User (TBSAGUser in my case on each node) using SSMS Open SSMS and create User # Restore certificate of Other Nodes into the present node using SSMS below: Login to TBSLinuxNode1 CREATE CERTIFICATE TBSLinuxNode2_Cert AUTHORIZATION TBSAGUser FROM FILE = '/var/opt/mssql/data/TBSLinuxNode2_Cert.cer'; # Grant permission to connec to the endpoint of TBSLinuxNode1 GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser; # Let's do the same thing by connecting to TBSLinuxNode2 and restore TBSLinuxNode1.cert CREATE CERTIFICATE TBSLinuxNode1_Cert AUTHORIZATION TBSAGUser FROM FILE = '/var/opt/mssql/data/TBSLinuxNode1_Cert.cer'; # Grant permission to connec to the endpoint of TBSLinuxNode2 GRANT CONNECT ON ENDPOINT::TBSSQLAG TO TBSAGUser; # Create Availability Group using SSMS with Cluster type External # Create a new login or use the same login to give Pacemaker permission and provide view server permission, I will give #sysadmin to this user just for this demo # On all Nodes Edit vi /var/opt/mssql/secrets/passwd using emacs and update with user and password that you created for Pacemaker and save it TBSAGUser Pass@123 # Hold down the CTRL key and then press X, then C, to exit and save the file # setup right permission sudo chmod 400 /var/opt/mssql/secrets/passwd # Create the AG resource in the Pacemaker cluster sudo pcs resource create TBSLinuxRG ocf:mssql:ag ag_name=TBSLinuxAG meta failure-timeout=30s --master meta notify=true # Create IP resource for Listener sudo pcs resource create LinuxSQLProdList ocf:heartbeat:IPaddr2 ip=192.168.1.104 cidr_netmask=24 # Create an ordering constraint to ensure that the AG resource is up and running before the IP address. While the colocation #constraint implies an ordering constraint, this enforces it sudo pcs constraint order promote TBSLinuxRG-master then start LinuxSQLProdList




How to configure SQL Server High Availability Group in Linux CentOS 7

How to install cluster Pacemaker on linux CentOS 7 for SQL Server High Availability

How to install cluster Pacemaker on linux CentOS 7 for SQL Server High Availability

How to Install cluster pacemaker on linux CentOS 7 for SQL Server High Availability video explains below Validating Nodes IP address Disable firewall and SELINUX (for Installation Only) -- Not Recommended for Prod Installing pacemaker Configuring Cluster User, i.e. hacluster Starting and Enabling Pacemaker services Adding members node to cluster Creating and starting cluster Confuring cluster resources a) Virtual IP of the cluster b) Apache Services -httpd-- Just to test the failover cluster Create Constraint of cluster resources Test Fail-over Script used in this video: #How to create Pacemaker Cluster on Linux CentoS 7 # IP Configuration verification of all possible nodes # installing httpd to verify in the end of cluster on both nodes yum install httpd -y # Disable SELINUX and Firewall vi /etc/sysconfig/selinux Change SELINUX=enforcing to Permissive and save the file on both nodes and setenforce 0 systemctl stop firewalld systemctl disable firewalld # Install Pacemaker software on both nodes yum install pacemaker pcs fence-agents-all -y # Once installed, you will see user created named hacluster - we need reset the password for this user on both nodes tail /etc/passwd --- To see if user is created passwd hacluster # Start and Enable PaceMaker Services on both nodes systemctl start pcsd systemctl enable pcsd # Add membership of the cluster using hacluster username pcs cluster auth TBSLinuxNode1 TBSLinuxNode2 -u hacluster # Create Cluster named "LinuxSQLCluster" in my case pcs cluster setup --name LinuxSQLCluster TBSLinuxNode1 TBSLinuxNode2 # View Configuration of the Cluster on both nodes Cat /etc/corosync/corosync.conf #Start the cluster pcs cluster start --all #Check the status of the cluster and enable the cluster pcs status pcs cluster enable -all # Configure the resources of the cluster a) Virtual IP of the cluster pcs resource create VirtIP IPAddr ip=192.168.1.102 cidr_netmask=24 op monitor interval=60 b) apache services (To check the failover, not necessary for SQL Server) pcs resource create Httpd apache Configuration="/etc/httpd/conf/httpd.conf" op monitor interval=60s -- # Check the status again to see if resources are configured right and you will notice both resources are created and in stopped state # Let's create Constraint and Policies on the cluster pcs constraint colocation add Httpd with VirtIP INFINITY pcs property set stonith-enabled=false pcs property set no-quorum-policy=ignore pcs property set default-resource-stickiness="INFINITY" # Check the status again to see if resources are online pcs status # Check virtual IP on active node ip a # Now let's create an HTML page to see where which node our resources are running vi /var/www/html/index.html #Type anything for example Hey I am TBSClusterNode1 (if creating on TBSLinuxNode1 in my case) etc. and save it on each node # Now let's view the page using IE or any browser # Let's failover the cluster and recheck the html page file we just created pcs cluster stop TBSLinuxNode1 (If it is running on node1, if node2, stop cluster services on that node)

How to Install cluster pacemaker on Linux CentOS 7 for SQL Server High Availability



How to Configure SQL Server Windows Authentication in Linux CentOS 7 - SQL Server on Linux Tutorial

How to Configure SQL Server Windows Authentication in Linux CentOS 7


How to Configure SQL Server Windows Authentication in Linux CentOS 7 video explains all below steps
Create Active Directory Service Account for SQL Server Setup SPN for SQL Server AG Service Account Validating Key version Number (kvno) Create MSSQLScv Keytab Setting Proper Permission of Keytab Configure SQL Server Instance to use Keytab file for kerberos Authentication Restarting SQL server and Testing Using SSMS Script Used in this Demo:


#Create SQL Server Service Account in Your Active Directory Domain UserName= mssql PasswordExpire= never # Setting ServicePrincipalName (SPN) for SQL Server Service account on Your Domain Conroller setspn -A MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433 mssql # Checking KVNO (Key Version Number) number for AD Account-usually its 2 kinit mssql@TECHBROTHERS.LOCAL kvno MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433 # Creating SQL Keytab File (AD Account with password must set this up) using ktutil sudo ktutil ktutil: addent -password -p MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433@TECHBROTHERS.LOCAL -k 3 -e aes256-cts-hmac-sha1-96 ktutil: addent -password -p MSSQLSvc/TBSLinuxNode1.Techbrothers.local:1433@TECHBROTHERS.LOCAL -k 3 -e rc4-hmac ktutil: wkt /var/opt/mssql/secrets/mssql.keytab quit # Setting up Proper permission (ownership) sudo chown mssql:mssql /var/opt/mssql/secrets/mssql.keytab sudo chmod 400 /var/opt/mssql/secrets/mssql.keytab # Configuring SQL Server to Point to KeyTab File sudo /opt/mssql/bin/mssql-conf set network.kerberoskeytabfile /var/opt/mssql/secrets/mssql.keytab # Restarting SQL Server Services sudo systemctl restart mssql-server # Connecting to SSMS and Testing Windows Authentication


How to Configure SQL Server Windows Authentication in Linux CentOS 7

How to Install SQL Server 2017 on CentOS 7 Step by Step

How to Install SQL Server 2017 on CentOS7 Step by Step

In this video we are going to learn how to install SQL Server on Linux Step by Step. How to Configure SQL Server Repository Download SQL Server Red Hat Repository configuration File Install SQL Server using yum Command Configure SQL Server and setup SA Password Restart SQL Server services Open Firewall port to allow remote connection to SQL Server Enable and start SQL Server Agent Services Download and Install SQL server Tools to connect to SQL Server Connecting to SQL Server using SSMS 2017 with SQL Server Authentication Scripts used in this video: # Let's check if mssql-server repository already exists? sudo ls /etc/yum.repos.d | grep mssql-server.repo # If it does, we need to check contents of this repository and remove that, otherwise we will face issues during the install sudo cat/etc/yum.repos.d/mssql-server.repo sudo rm -rf /etc/yum.repos.d/mssql-server.repo # Configure and Download SQL Server Repository sudo curl -o /etc/yum.repos.d/mssql-server.repo
https://packages.microsoft.com/config... # Install SQL Server using below commands sudo yum install -y mssql-server # Configure SQL Server and Setup SA password sudo /opt/mssql/bin/mssql-conf setup # Restart SQL Server Services systemctl restart mssql-server # Configure repository and install Sqlcmd and other Tools to internally connect to newly installed SQL Server sudo curl -o /etc/yum.repos.d/msprod.repo https://packages.microsoft.com/config... sudo yum install -y mssql-tools unixODBC-devel #Set Environment Tools to your Path Environment # Connect to SQL Server using sqlcmd tool sqlcmd -S localhost -U SA -P Pass@123 # Connect to SQL Server Remotely using SQL Server Authentication SSMS - Error and Resolution # Enable Firewall port 1433 sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent sudo firewall-cmd --reload # Enable and start SQL Server Agent services sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled true

How to Install SQL Server 2017 on CentOS 7



SQL Server on Linux Tutorial

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