.::: Change Server Name in Microsoft SQL Server MSSQL :::.

1. Check before
select @@servername as ServerName, @@version as VersionSQL;
exec sp_helpserver;

 

 

.::: Set Limit Max Server Memory in SQL Server :::.

1. Check Memory server 

 

2. Capture before
SELECT @@servername as ServerName,[name],[value], [value_in_use], description
FROM sys.configurations
WHERE [name] = 'max server memory (MB)' or
      [name] = 'min server memory (MB)' ;

.::: Upgrade SQL Server 2019 to MSSQL 2022 :::.

1. Backup Database
2. Capture version before upgrade
---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

.::: Backup Database Full & Transaction & Restore Database SQL Server :::.

correlation with https://teguhth.blogspot.com/2023/06/backup-database-full-transaction.html

 

A. check status backup and restore

SELECT command, percent_complete,total_elapsed_time, estimated_completion_time, start_time
  FROM sys.dm_exec_requests
  WHERE command IN ('RESTORE DATABASE','BACKUP DATABASE')
 

.::: Change License Key SQL Server 2019 from Evaluation to Enterprice :::.

A. Check before update License

 ---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address
   

.:: Check ServerName, SQL Server Version, IP, compatibility_level, log_reuse_wait_desc, recovery_model_desc , Active Transaction :::.

---Open SSMS & Run Command to check version (Capture)
select @@servername as ServerName, @@version as VersionSQL;

-- select IP
SELECT  @@servername as ServerName,
   CONNECTIONPROPERTY('net_transport') AS net_transport,
   CONNECTIONPROPERTY('protocol_type') AS protocol_type,
   CONNECTIONPROPERTY('auth_scheme') AS auth_scheme,
   CONNECTIONPROPERTY('local_net_address') AS local_net_address,
   CONNECTIONPROPERTY('local_tcp_port') AS local_tcp_port,
   CONNECTIONPROPERTY('client_net_address') AS client_net_address

.::: Repair Unable to launch SQL Server Configuration Manager – Invalid class [0x80041010] :::.

1. Get Error open SQL SQL Server 2019 Configuration Manager
 

Here is the text of the error message:

—————————
SQL Server Configuration Manager
—————————
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager.
Invalid class [0x80041010]
—————————
OK 

.::: Check Hostname, IP & Version for MSSQL, MariaDB, MySQL, OracleDB, PostgreSQL from SQL Server LinkedServer :::.

1. MSSQL
select @@servername as ServerName,@@VERSION as VersionSQL from sys.databases;
select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION as VersionSQL from sys.databases
 

.::: Remove a node to existing MariaDB Galera Cluster :::.

 A. Temporary remove
1. check status existing galera status before remove. for 3 node
show status like 'wsrep_incoming_addresses';
show status like 'wsrep_cluster_weight';
show status like 'wsrep_cluster_size';
show status like 'wsrep_connected';

.::: Adding a node to existing MariaDB Galera Cluster :::.

1. additional hosts node at hosts <add node3= teguhth03>
[root@teguhth01 ~]# cat /etc/hosts
127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4
::1         localhost localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.15 loadbalancer
10.10.10.11 teguhth01
10.10.10.12 teguhth02
10.10.10.13 teguhth03
[root@teguhth01 ~]#

.::: How to Shrink Log Database SQL Server :::.

 1.check active transcation

select @@servername as ServerName,name,log_reuse_wait_desc, recovery_model_desc,compatibility_level,@@VERSION  from sys.databases
 

.::: Check Running Query In SQL Server/ Check Query Database Berjalan :::.

1. query 1 - full not simple

SET NOCOUNT ON   
SET ANSI_PADDING ON
SET QUOTED_IDENTIFIER ON  
DECLARE @record_id int, @SQLProcessUtilization int, @CPU int,@EventTime datetime--,@MaxCPUAllowed int   
select  top 1  @record_id =record_id,
      @EventTime=dateadd(ms, -1 * ((SELECT ms_ticks from sys.dm_os_sys_info) - [timestamp]), GetDate()),-- as EventTime,
      @SQLProcessUtilization=SQLProcessUtilization,
      --SystemIdle,
      --100 - SystemIdle - SQLProcessUtilization as OtherProcessUtilization,
      @CPU=SQLProcessUtilization + (100 - SystemIdle - SQLProcessUtilization) --as CPU_Usage
from (

 

.::: Reindex, Reorganize and Rebuild Indexes & Update statistics in SQL Server base on Fragmentation :::.

1. Check Database_ID (contoh database id 5 & 6 )
select @@Servername as ServerName,name as dbname,database_id  from sys.databases;
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'teguhth';
select @@Servername as ServerName,name as dbname,database_id  from sys.databases where name = 'AdventureWorks2019';

.::: Install & Configure High Availability MariaDB 10.5 Using New Galera Cluster Using IST & SST :::.

 1. Install MariaDB latest
http://teguhth.blogspot.com/2023/05/how-to-install-mariadb-105-or-latest.html

enable /etc/hosts 

State Transfers

The process of replicating data from the cluster to the individual node, bringing the node into sync with the cluster, is known as provisioning. There are two methods available in Galera Cluster to provision nodes:

• State Snapshot Transfers (SST) Where a snapshot of the entire node state transfers.
• Incremental State Transfers (IST) Where only the missing transactions transfer.

https://galeracluster.com/library/documentation/state-transfer.html#state-transfer-sst

2. check galera location
[root@teguhth01 ~]# yum install -y mlocate
[root@teguhth01 ~]# updatedb
[root@teguhth01 ~]# locate libgalera_smm.so
/usr/lib64/galera-4/libgalera_smm.so
[root@teguhth01 ~]#

::: How to Install MariaDB 10.5 or Latest using mariadb_repo (Enable Remote Root) :::.

1. download repository
yum wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup
yum install wget -y
wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

[root@teguhth ]# wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup

2. install repo
chmod +x mariadb_repo_setup
sudo ./mariadb_repo_setup

[root@teguhth yum.repos.d]# chmod +x mariadb_repo_setup
[root@teguhth yum.repos.d]# sudo ./mariadb_repo_setup
# [info] Checking for script prerequisites.
# [info] MariaDB Server version 10.11 is valid
# [info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
# [info] Adding trusted package signing keys...
/etc/pki/rpm-gpg /etc/yum.repos.d
/etc/yum.repos.d

.::: How to Remove ENCRYPTION_KEY_ID from file backup MariaDB / MySQL :::.

1. find ENCRYPTION_KEY_ID in backup mariadb example 'teguhth_encrypt.sql'
cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID

 

[root@teguhth-lab02 backup]# cat teguhth_encrypt.sql  | grep -i  ENCRYPTION_KEY_ID
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234;
) ENGINE=InnoDB DEFAULT CHARSET=utf8 `ENCRYPTED`=YES `ENCRYPTION_KEY_ID`=1234;
[root@teguhth-lab02 backup]#