SQLKonf-01
Red Hat 8.8


root
marcelo
sqlkonf1234!@#$

sa
P@$$w0rd


Network 1 in Lan
Network 2 in 192.168.110.63

---------------------------------------------------------
cat /etc/redhat-release
cat /etc/fstab
df -h
top
free -m
iostat (will install)



--to check user and group
id mssql
id marcelo

chown marcelo.marcelo mssql-server-16.0.4065.3-4.x86_64.rpm

--offline install:
--downloaded from https://packages.microsoft.com/rhel/8/mssql-server-2022/Packages/m/
--offline packages location: D:\sqlonlinuxrpms

rpm -qpR mssql-server-16.0.4065.3-4.x86_64.rpm

sudo yum localinstall mssql-server_versionnumber.x86_64.rpm
rpm -qpR mssql-server_versionnumber.x86_64.rpm

sudo yum localinstall mssql-server_versionnumber.x86_64.rpm


--adding the online repo location to the catalog
sudo curl -o /etc/yum.repos.d/mssql-server.repo https://packages.microsoft.com/config/rhel/8/mssql-server-2022.repo


sudo yum install -y mssql-server

-->Checkpoint here

sudo /opt/mssql/bin/mssql-conf setup

SA: 1qaz@WSX3edc

--check status of the services
systemctl status mssql-server

--check configs
sudo /opt/mssql/bin/mssql-conf list

--enable Agent
sudo /opt/mssql/bin/mssql-conf set sqlagent.enabled 1

systemctl restart mssql-server.service

sudo usermod -a -G mssql marcelo

--Data files are here: /var/opt/mssql
--Binaries are here: /opt/mssql

--check error log
cat or tail
top to see cpu usage
--check tempdb file placement

--check current ip address, try to connect
ifconfig







--firewall config
sudo firewall-cmd --zone=public --add-port=1433/tcp --permanent
sudo firewall-cmd --reload

--Open SSMS
--connect to instance

--let's put a backup file in the OS filesystem

mkdir /opt/stage

--copy C:\temp\bkp\AdventureWorksLarge_compression.bak
--restore the db
--Sales.SalesOrderDetailEnlarged
--Sales.SalesOrderHeaderEnlarged
/*
use AdventureWorksLarge
go
select * from Sales.SalesOrderDetailEnlarged
select * from Sales.SalesOrderHeaderEnlarged
*/

--restore the db:
USE [master]
RESTORE DATABASE [AdventureWorksLarge] FROM  DISK = N'/opt/stage/AdventureWorksLarge_compression.bak' WITH  FILE = 1,  MOVE N'AdventureWorks2017' TO N'/var/opt/mssql/data/AdventureWorksBIG.mdf',  MOVE N'AdventureWorks2017_log' TO N'/var/opt/mssql/data/AdventureWorksBIG_log.ldf',  NOUNLOAD,  STATS = 5

GO

query: 

select * from sys.configurations
where description like '%memory%'

----------------------------------------------------

--install tools: bcp and sqlcmd
sudo curl https://packages.microsoft.com/config/rhel/8/prod.repo > /etc/yum.repos.d/mssql-release.repo

sudo yum install -y mssql-tools18 unixODBC-devel


echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bash_profile
echo 'export PATH="$PATH:/opt/mssql-tools18/bin"' >> ~/.bashrc
source ~/.bashrc

sqlcmd -S localhost -U sa -No

--if local install is needed:
sudo yum localinstall unixODBC-2.3.11-1.rh.x86_64.rpm
sudo yum localinstall msodbcsql18-18.3.1.1-1.x86_64.rpm
sudo yum localinstall mssql-tools18-18.2.1.1-1.x86_64.rpm

-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------
-----------------------------------------------------------------------------------------------------

--remove if needed

sudo yum remove mssql-tools18.x86_64
sudo yum remove unixODBC-devel.x86_64
sudo yum remove mssql-server.x86_64
rm -rf /opt/mssql
rm -rf /var/opt/mssql

---------------------------------------------------------------------
--check type of FS on mounted disks
cat /etc/fstab

--to do: add a backup file 


--------------------------------------------------------------------
--Install Diskpd

yum install libaio-devel

in the unzipped diskpd folder
make

/*
  Parameter	Description
  -b	Block size of the I/O, specified as (K/M/G). For example –b8K means an 8KB block size, which is relevant for SQL Server
  -d	Test duration in seconds. Tests of 30-60 seconds are usually long enough to get valid results
  -o	Outstanding I/Os (meaning queue depth) per target, per worker thread
  -t	Worker threads per test file target
  -h	Disable software caching at the operating system level and hardware write caching, which is a good idea for testing SQL Server
  -r	Random or sequential flag. If –r is used random tests are done, otherwise sequential tests are done
  -w	Write percentage. For example, –w25 means 25% writes, 75% reads
  -Z	Workload test write source buffer size, specified as (K/M/G). Used to supply random data for writes, which is a good idea for SQL Server testing
  -L	Capture latency information during the test, which is a very good idea for testing SQL Server
  -c	Creates workload file(s) of the specified size, specified as (K/M/G)

The following example writes directly (without buffering) to the partition /dev/sdc1 without touching the first 1GiB of the disk, using 1MiB blocks. Warm up is only 2 seconds, and results are recorded for 60 seconds. Output latency information and standard deviation for IOPS are displayed. 8 threads are used in total each keeping at most 4 operations in flight at a time, for a total of up to 32 in flight operations at a time.

    diskspd -b1M -B1G -w100 -Sh -W2 -d60 -L -D -t8 -o4 /dev/sdc1
Useful options:

-F and -t set the total number of threads to use. Mutually exclusive with -t

-d modifies the duration of the test (default is 10 seconds)

-Sh opens files with O_DIRECT and O_SYNC flags, ensuring no caching is done in the OS. Note that all I/O must be aligned to the underlying device's physical sector size for this option to work.

-t modifies how many threads do work on each file (default is 1)

-W modifies the warmup time (default is 5 seconds)

-z uses C++'s random_device to seed the offsets generated by -r (default is a seed of 0)

-Zr uses separate read and write I/O buffers, and fill them with random data (default is ascending bytes)
*/

diskspd -b64K -d30 -o4 -t4 -sh -r -w25 -L -c10G /opt/stage/test1.dat

diskspd -b64K -d30 -o4 -t8 -h -r -w25 -L -c10G d:\test1.dat


