.::: Create Report SQL Server Using SSRS (Sample Lab ) :::.

A. Check Table SQL Server
select * from barang;

B. Install & Enable SSRS Service

.::: Performance Compare 4 CPU vs 1 CPU In SQL Server Using Execution Plan (Setting MaxDop Max Degree Of Parallelism) :::.

 
1. Download & restore AdventureWorks2019
2. Open New Query and run Query below & Execute
set Statistics io, time on
use AdventureWorks2019

-- Query running on 4 logical processors

.::: Lab Integration SSIS with SQL Server using SQL Jobs Agent :::.

A. Create Database & Table from Source & Destination

1. Create database source

tgh_source

2. create table source

create table barang(

KODE_BARANG char(6) not null ,

NAMA_BARANG varchar(25), 

SATUAN_BARANG varchar(20),

STOK_BARANG decimal(4),

primary key (KODE_BARANG));

.::: Configure Enable NTP Server & Client Windows Server using Group Policy Editor :::.

 A. Windows Server become NTP Server

1. Open Group Policy Editor (gpedit.msc)

2. Open Time Provider

Computer Configuration > Administrative Templates > System > Windows Time Service > Time Providers.

.::: Cannot Remote Desktop Connection : An Authentication error has Occured. Due to CredSSP Encryption Oracle Remedian :::.

 

1. Open Registry

2. Open Computer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\CurrentVersion\Policies\System\CredSSP\Parameters

3. Double-click the AllowEncryptionOracle DWORD to open its Edit DWORD window.

Enter the value ‘2‘ in the Value data text box, and press the OK button.

.::: Create & Test Maintenance Cleanup Task in SQL Server MSSQL :::.

1. Create Maintenance Plan

.::: Install Database Oracle 19C in Linux Centos 7 :::.

 .::: Install Database Oracle 19C in Linux Centos 7 :::.

1. Disable firewalld

[root@teguhth ~]# systemctl stop firewalld

[root@teguhth ~]# systemctl disable firewalld

Removed symlink /etc/systemd/system/multi-user.target.wants/firewalld.service.

Removed symlink /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.

[root@teguhth ~]# 

2. add /etc/hosts

[root@teguhth ~]# cat /etc/hosts

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.10.10.50 teguhth

[root@teguhth ~]# 

.::: Latihan SSIS convert csv file to SQL / MSSQL :::.

1. Create database on SQL Server 

create database LatSSIS;

use LatSSIS;

go

.::: Configure log shipping SQL Server :::.

 to monitor log shipping

http://teguhth.blogspot.com/2023/09/monitor-transaction-log-shipping-using.html

1. Enable Log Shipping on Database 1

.::: How to Install Replica SQl Server (MSSQL) include distributor, publisher, subscriber :::.

1. Enable SQL replication setup

2. configure a SQL replication distributor

.::: Testing Failover Always On SQL Server (MSSQL) and collect Log after Failover :::.

 

A. Failover using GUI / SQL Management Studio

1. Capture always on before Failover

.::: SQL Server: how to simulate and catch deadlock :::.

1. Create database 

create database dead_lock_lab

go 

2. create table & row data 

USE dead_lock_lab;

 

CREATE TABLE dbo.invoices (

   id int NOT NULL,

   num nvarchar(20) NOT NULL,

   customer nvarchar(100) NOT NULL,

   created_at datetime NOT NULL,

   updated_at datetime NOT NULL,

   CONSTRAINT PK_invoices PRIMARY KEY (id)

);

 

.::: How to Sample Simulation Blocking SQL Server & Trouble shoot :::.

 

1. Login to database and create database

create database blocking;

go 

2. Open 1 Session to New Quary Windows

Begin Tran ;

select 'query 1'

CREATE TABLE ##Employees (

    EmpId INT IDENTITY,

    EmpName VARCHAR(16),

    Phone VARCHAR(16)

)

GO

INSERT INTO ##Employees (EmpName, Phone)

VALUES ('Martha', '800-555-1212'), ('Jimmy', '619-555-8080')

GO

CREATE TABLE ##Suppliers(

    SupplierId INT IDENTITY,

    SupplierName VARCHAR(64),

    Fax VARCHAR(16)

)

GO

INSERT INTO ##Suppliers (SupplierName, Fax)

VALUES ('Acme', '877-555-6060'), ('Rockwell', '800-257-1234')

GO