2021-08-10

LOAD BALANCING MICROSOFT SQL SERVER WITH HAPROXY

LOAD BALANCING MICROSOFT SQL SERVER WITH HAPROXY

In case you are told it isn’t possible, I can confirm that it is in fact possible to load balance requests to databases using HAProxy. Here are the specifics.

First, the databases in question are SQL 2012 Web Edition databases. They get populated by replication from a SQL 2012 Standard Edition publisher. Reads are sent to these replicated databases, but all writes go to the master database.

Here is the entire HAProxy config for a basic database load balancing listener, taking connections from a list of approved addresses and distributing them to three servers, depending on whether they appear to be up.

listen sql-db
    bind *:1433
    mode tcp
    balance leastconn
    acl db_white_list src 0.0.0.0 1.1.1.1 2.2.2.2 3.3.3.3
    tcp-request connection reject if !db_white_list
    option log-health-checks
    server DB-1 4.4.4.1:1433 check port 1433 inter 1000
    server DB-2 4.4.4.2:1433 check port 1433 inter 1000
    server DB-3 4.4.4.3:1433 check port 1433 inter 1000

You can get more advanced here – you could perform more advanced checks using SQL to make sure you have more than just a connection to a port – but you get the idea.

What does this cost?

Unlike HTTP load balancing, you will actually start to see CPU usage on your HAProxy server. You will also see a big jump in network traffic, as web pages are usually smaller than the data you might query to generate them. You’ll need to make sure this isn’t a major problem. You’ll also see a little latency as it will take slightly longer to get your data as there is something else in the middle.

2021-07-22

MONITOR ROUTE TABLE IN LINUX

MONITOR ROUTE TABLE IN LINUX

#!/bin/bash
#!/bin/sh -e
#/opt/script/routing-monitor-stats.sh
##############################################
#LastUpdate: 2021.07.23 8:28:04
##############################################
# Check ROUTING status every minute:
# * * * * * /opt/script/routing-monitor-stats.sh >> /opt/log/routing-monitor-stats/routing-monitor-stats.log 2>&1
##############################################
now1="$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
##############################################
COMMAND1="ip route"
SUBNET1="192.168.202"
##############################################
mkdir -p /opt/log/routing-monitor-stats/
cd /opt/log/routing-monitor-stats/

VAR1=$($COMMAND1 | grep "$SUBNET1" | awk '{print $1}');
if [ -z "$VAR1" ]
then
      /opt/script/route-2-dbzone.sh
      echo "[$now1]: Subnet [$SUBNET1"".0/24] is MISSING";
else      
      echo "[$now1]: Subnet [$SUBNET1"".0/24] is RUNNING";
fi

#truncate file, keep 7days log:
FILE_NAME="/opt/log/routing-monitor-stats/routing-monitor-stats.log"
tail -10080 $FILE_NAME > $FILE_NAME.temp && cat $FILE_NAME.temp > $FILE_NAME
#THE_END

2021-07-17

Windows Subsystem for Linux Installation Guide for Windows 10/11

#Windows Subsystem for Linux Installation Guide for Windows 10/11:
#REF: https://docs.microsoft.com/en-us/windows/wsl/install-win10

# Manual Installation Steps
# Step 1 - Enable the Windows Subsystem for Linux
# You must first enable the "Windows Subsystem for Linux" optional feature before installing any Linux distributions on Windows.
# Open PowerShell as Administrator and run:
# PowerShell
dism.exe /online /enable-feature /featurename:Microsoft-Windows-Subsystem-Linux /all /norestart

# Step 2 - Check requirements for running WSL 2
# To update to WSL 2, you must be running Windows 10.

# Step 3 - Enable Virtual Machine feature
# Before installing WSL 2, you must enable the Virtual Machine Platform optional feature. Your machine will require virtualization capabilities to use this feature.
# Open PowerShell as Administrator and run:
# PowerShell
dism.exe /online /enable-feature /featurename:VirtualMachinePlatform /all /norestart
#Restart your machine to complete the WSL install and update to WSL 2.

# Step 4 - Download the Linux kernel update package
# Download the latest package:

# Step 5 - Set WSL 2 as your default version
# Open PowerShell and run this command to set WSL 2 as the default version when installing a new Linux distribution:
# PowerShell
wsl --set-default-version 2

# Step 6 - Install your Linux distribution of choice
# Open the Microsoft Store and select your favorite Linux distribution.
wsl --list --verbose

#9:36 2021.07.18
#REF: https://github.com/microsoft/WSL/issues/5393
If someone have trouble in Windows 11
you should:
wsl --update
wsl --shutdown

#https : //wslstorestorage.blob.core.windows.net/wslblob/wsl_update_x64.msi

Install WSL on Windows 10 | Microsoft Docs


CONGRATULATIONS! You've successfully installed and set up a Linux distribution that is completely integrated with your Windows operating system!

2021-06-18

Get file content without comment

#Select all content, paste it into linux shell
#__________[CONFIG]:BEGIN
FILE_NAME="/opt/script/file-uncommented-line.sh"
now1="$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
chattr -i -f $FILE_NAME
cp -v $FILE_NAME $FILE_NAME-[$now1].bk

cat <<EOF > $FILE_NAME
#!/bin/bash
#!/bin/sh -e
###################################
#FILE_NAME: $FILE_NAME
#Author: qwerty
#LastUpdate: #$now1
###################################
#Usage: file-uncommented-line.sh <filename>
###################################CONTENT:BEGIN
now1="\$(date +'%Y.%m.%d-%H.%M.%S.%3N')"
TEMP_LOC="/opt/temp"; mkdir -p \$TEMP_LOC

FileWithFullPath="\$1"
FileNameOnly="\${FileWithFullPath##*/}"
FileName_WithoutExtension="\${FileNameOnly%.*}"
FileExtensionOnly="\${FileNameOnly##*.}"

echo "FileWithFullPath         : \$FileWithFullPath"
echo "FileNameOnly             : \$FileNameOnly"
echo "FileName_WithoutExtension: \$FileName_WithoutExtension"
echo "FileExtensionOnly        : \$FileExtensionOnly"

echo "=========================="
echo "REMOVE HASH COMMENT: DONE"
grep -v '^ *#' \$FileWithFullPath > \$TEMP_LOC/\$FileName_WithoutExtension"_RemovedHash".\$FileExtensionOnly
sleep 2


echo "=========================="
echo "REMOVE EMPTY LINES: DONE"
sed '/^\$/d' \$TEMP_LOC/\$FileName_WithoutExtension"_RemovedHash".\$FileExtensionOnly > \$TEMP_LOC/\$FileName_WithoutExtension"_RemovedEmpyLines".\$FileExtensionOnly
sleep 2


echo "=========================="
echo "RESULT: "
ls -l \$TEMP_LOC/\$FileName_WithoutExtension*
###################################CONTENT:END
#THE-END
EOF

cat $FILE_NAME
chmod +x $FILE_NAME
chattr +i -f $FILE_NAME
echo "FILE_NAME: $FILE_NAME"; echo ""
#__________[CONFIG]:END



#RESULT:
root@host /opt/temp# file-uncommented-line.sh /etc/MailScanner/MailScanner.conf 
FileWithFullPath         : /etc/MailScanner/MailScanner.conf
FileNameOnly             : MailScanner.conf
FileName_WithoutExtension: MailScanner
FileExtensionOnly        : conf
==========================
REMOVE HASH COMMENT: DONE
==========================
REMOVE EMPTY LINES: DONE
==========================
RESULT: 
-rw-r--r--. 1 root root 14609 Jun 18 14:22 /opt/temp/MailScanner_RemovedEmpyLines.conf
-rw-r--r--. 1 root root 14983 Jun 18 14:22 /opt/temp/MailScanner_RemovedHash.conf
root@host /opt/temp# 

2020-12-14

GitHub Download SubFolder Only with SVN

#13:57 2020.12.14
#Git-Clone-SubFolderOnly-C3-UsingSVN.sh
#REF: https://coderwall.com/p/o2fasg/how-to-download-a-project-subdirectory-from-github
sudo apt-get install subversion
#REPO: 
#FULL: 
#https://github.com/mgiay/UbuntuDebianEnv.git
#https://github.com/mgiay/UbuntuDebianEnv/
#SUB : https://github.com/mgiay/UbuntuDebianEnv/tree/main/HaProxy/HaProxy-2.2.6

GITHUB_USERNAME="mgiay"
GITHUB_PROJECT="UbuntuDebianEnv"
GITHUB_SUB_FLD="HaProxy/HaProxy-2.2.6"
DEST_LOCAL="HaProxy-2.2.6"

#LISTING: svn ls https://github.com/$GITHUB_USERNAME/$GITHUB_PROJECT
#DOWNLOADING: 
svn export https://github.com/$GITHUB_USERNAME/$GITHUB_PROJECT/trunk/$GITHUB_SUB_FLD $DEST_LOCAL
#

#RESULT:


2020-11-26

Linux CLI, SpeedTest

#/opt/script/SPEEDTEST.sh
#10:06 2020.04.07
##############################
#REF: https://williamyaps.github.io/wlmjavascript/servercli.html
now1="$(date +'%Y.%m.%d_%H.%M.%S.%3N')"
CITY="Hanoi"
##############################

#_________[SpeedTest_Script]:BEGIN
RESULT_FLD="/opt/temp"
mkdir -p $RESULT_FLD
mkdir -p /opt/script/;cd /opt/script/
#wget https://raw.githubusercontent.com/sivel/speedtest-cli/master/speedtest.py
#chmod +x speedtest.py
/opt/script/speedtest.py --list > $RESULT_FLD/speedtest-server-list-all.txt
cat $RESULT_FLD/speedtest-server-list-all.txt | grep "$CITY" | awk '{gsub(")",""); print $1}' > $RESULT_FLD/$CITY-isp-full-name.txt

cd $RESULT_FLD
while read LINE; \
do /opt/script/speedtest.py --server "$LINE"; \
done < $RESULT_FLD/$CITY-isp-full-name.txt > $RESULT_FLD/$CITY-isp-result-$now1.txt
#_________[SpeedTest_Script]:END


#______________________DELETE_OLD_DATA:BEGIN
# SERVER_NAME=$(hostname)
# mkdir -p /opt/bk/$SERVER_NAME/
# cd /opt/bk/$SERVER_NAME/
NumberOfFileWantToKeep=3
#Giu lai [3] file moi nhat trong thu muc [$FOLDER_DST]:
#XOA FILE:
#/bin/rm -rf `ls -t "result*.txt" | awk 'NR>3'`
/bin/rm -rf `ls -t *.txt | awk 'NR>3'`
#XOA FOLDER:
/bin/rm -rf `ls -td */ | awk 'NR>3'`
#______________________DELETE_OLD_DATA:END
#THE_END

2020-11-25

Solve “The following signatures were invalid: BADSIG” in Ubuntu

 #15:30 2020.11.25

Solve “The following signatures were invalid: BADSIG” in Ubuntu
In this post, we will discuss another very common problem that occurs when users upgrade or update their system. This error is “BADSIG” error which looks something like this:

W: A error occurred during the signature verification. The repository is not updated and the previous index files will be used. GPG error: http://extras.ubuntu.com precise Release: The following signatures were 
invalid: BADSIG 16126D3A3E5C1192 Ubuntu Extras Archive Automatic Signing Key

W: GPG error: http://ppa.launchpad.net precise Release:
The following signatures were invalid: BADSIG 4C1CBC1B69B0E2F4 Launchpad PPA for Jonathan French W: Failed to fetch 


http://extras.ubuntu.com/ubuntu/dists/precise/Release
sudo apt-get clean
cd /var/lib/apt
sudo mv lists oldlist
sudo mkdir -p lists/partial
sudo apt-get clean
sudo apt-get update


REF: https://itsfoss.com/solve-badsig-error-quick-tip/