.::: 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

 

.::: Install PAF (PostgreSQL Automatic Failover ) on centos7 ( base on pacemaker, pcs, corosys ):::.

 

A. Install PAF 

1. Prepare Node 

Node01 

10.10.10.241

Node02

10.10.10.242

Ip Virtual 

10.10.10.243

2. Install pacemaker, corosys, pacemaker & configuration 

insttal pcsd

https://teguhth.blogspot.com/2018/08/how-to-install-and-configuration-pcs.html

.::: How To Set Up Redis as a Cache for MariaDB / MySQL with PHP :::.

1. Install Redish

https://teguhth.blogspot.com/2021/08/how-to-install-database-redis-on-centos.html

2. Install lamp

[root@teguhth /]# yum -y install php libapache2-mod-php php-pdo php-json php-cli

3. Install MariaDB

https://teguhth.blogspot.com/2017/10/install-mariadb-101-on-centosrhel-using.html

.::: How To Install Database Redis on Centos 7 :::.

1. Enable repository

[root@teguhth data]# yum -y install epel-release yum-utils

[root@teguhth data]# yum -y install http://rpms.remirepo.net/enterprise/remi-release-7.rpm

[root@teguhth data]# yum-config-manager --enable remi

[root@teguhth data]# 

.:: How To Replicate Set (Redundant /Mirroring) MongoDB 5.0 on Centos 7 :::.

A. Configuring the Replica Set

1. Setting up the environment Add /etc/host to nodeA nodeB 

[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.21 teguhth01 NodeA

10.10.10.22 teguhth02 NodeB

[root@teguhth01 ~]# 

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

127.0.0.1   localhost localhost.localdomain localhost4 localhost4.localdomain4

::1         localhost localhost.localdomain localhost6 localhost6.localdomain6

10.10.10.21 teguhth01 NodeA

10.10.10.22 teguhth02 NodeB

[root@teguhth02 ~]# 

.::: Install MongoDB 5.x on Centos/Redhat 7 using repository :::.

 

1. Create repo the /etc/yum.repos.d/mongodb-org-5.0.repo file for MongoDB.

[root@teguhth02 ~]# cat /etc/yum.repos.d/mongodb-org-5.0.repo

[mongodb-org-5.0]

name=MongoDB Repository

baseurl=https://repo.mongodb.org/yum/redhat/$releasever/mongodb-org/5.0/x86_64/

gpgcheck=1

enabled=1

gpgkey=https://www.mongodb.org/static/pgp/server-5.0.asc

[root@teguhth02 ~]# 

.::: Change Standby Slave Database to Master/Normal on PostgreSQL ERROR: cannot execute CREATE DATABASE in a read-only transaction :::.

A. Change Database Standby/Slave to Normal /Master

1. test create database for test write

teguhth=# create database tka;

ERROR:  cannot execute CREATE DATABASE in a read-only transaction

teguhth=# 

2. Promote Standby/Slave

/usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data

[root@teguhth02 ~]# su - postgres

Last login: Sun Aug  1 15:14:46 WIB 2021 on pts/0

-bash-4.2$ /usr/pgsql-13/bin/pg_ctl promote -D /var/lib/pgsql/13/data

waiting for server to promote.... done

server promoted

-bash-4.2$

.::: How to Setting Streaming replication Redundant / Mirroring (Master Slave) postgreSQL 13 on Centos 7 :::.

 

A. 

B. Setting di Master Node

1. Create user replication

su - postgres

CREATE USER replicate REPLICATION LOGIN CONNECTION LIMIT 1 ENCRYPTED PASSWORD 'admin'

[root@teguhth01 13]# su - postgresq;

su: user postgresq does not exist

[root@teguhth01 13]# su - postgres

Last login: Sun Aug  1 13:38:09 WIB 2021 on pts/0

-bash-4.2$ 

-bash-4.2$ 

-bash-4.2$ psql

psql (13.3)

Type "help" for help.

.::: Install postgreSQL Linux using Repository Centos 7 & PostgreSQL 14 :::.

 

A. Install progress using Repository CentOS 7.

1. Update Server (optional)

[root@teguhth01 ~]# yum -y update

2. Install PostgreSQL

[root@teguhth01 ~]# yum install postgresql-server postgresql-contrib -y

3. Inisialisasi Database

[root@teguhth01 ~]# postgresql-setup initdb

Initializing database ... OK

systemctl start postgresql

.::: Importing Exsiting server or cluster MySQL/MariaDB using Severalnines :::.

 1. Generate an SSH key to be used by ClusterControl when connecting to all managed hosts. In this example, we are using the root user to connect to the managed hosts. To generate an SSH key for the root user

[root@teguhth ~]# ssh-keygen -t rsa

2. Before creating or importing a database server/cluster into ClusterControl, set up passwordless SSH from the ClusterControl host to the database host(s). Use the following command to copy the SSH key to the target hosts

.::: Install & Configuration Cluster control Severalnines for HA MariaDB, MySQL :::.

A. Setup ClusterControl repository 
1. Manually import the Severalnines repository public key into your RPM keyring:
[root@teguhth data]# wget http://repo.severalnines.com/severalnines-repos.asc
[root@teguhth data]# rpm --import severalnines-repos.asc 
[root@teguhth data]# 

.::: How to Create HA (High Availability) MariaDB/MySQL uting Galera Cluster on Linux (Include trouble shoot Crash case :::.

A. Installation Galera Cluster (example on 3 node = node1, node2, node3)
1. Disable selinux
[root@teguhth01 ~]# sestatus
SELinux status:                 disabled
[root@teguhth01 ~]# cat /etc/selinux/config 
# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted 
[root@teguhth01 ~]# 

 

.::: Implementation Store Procedure on Datawarehouse SQL Server ( Sample table barang, customer, suplier, pasok & pembelian) :::.

 
1. add table barang
-- if using Query SQL
select * from barang;
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-01','RICE COOKER','BUAH',20);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-02','LEMARI ES','UNIT',8);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-03','TELEVISI','UNIT',30);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-04','RADIO/TAPE','BUAH',35);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-05','KOMPUTER','UNIT',28);
insert into barang(KODE_BARANG,NAMA_BARANG,SATUAN_BARANG,STOK_BARANG) values('ELK-06','KIPAS ANGIN','BUAH',38);
select * from barang;

.::: Create table Inventory Datawarehouse SQL Server Original (Converting script from SQL Studio :::.

A. Create Database & Table 
create database teguhth
go

1. Table barang

-- table barang with index
CREATE TABLE [dbo].[barang](
[KODE_BARANG] [char](6) NOT NULL,
[NAMA_BARANG] [varchar](25) NULL,
[SATUAN_BARANG] [varchar](20) NULL,
[STOK_BARANG] [decimal](4, 0) NULL,
PRIMARY KEY CLUSTERED ([KODE_BARANG] ASC))

.::: Create Stored Procedure & Function Using SQL Server (Sample database moonton Mobile Legend) :::.

Stored Procedure

Stored Procedure merupakan sekumpulan perintah-perintah SQL yang tersimpan dengan nama tertentu dan diproses sebagai sebuah kesatuan. Bisa diakatan sebuah sub program yang tersimpan di database.

Membuat Function

Function adalah suatu blok SQL yang memiliki konsep sama dengan procedure, hanya saja pada function terdapat pengembalian nilai (return value)

A. Create database
create database mobile_legend
go

.::: Basic Store Procedure SQL Server :::.

1. Sample 1
-- SQL Query 1
select kode_barang, stok_barang from barang;

-- Store Procedure 1
create procedure kodestokbarang 
as 
select kode_barang, stok_barang from barang;

.::: Create Linked Server MSSQL to use Open Query (to another MSSQL) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
No Need Configuration ODBC

2. create Linked Server
Server Objects -> Linked Servers -> New Linked Server
General
Linked Server: MSSQL
Server Type: SQL Server
or 
Linked Server: MSSQL\instance
Linked Server: IP_SQLServer\instance

.::: Create Linked Server MSSQL to use Open Query (Oracle) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
No Need Configuration ODBC

2. create Linked Server
Server Objects -> Linked Servers -> New Linked Server
General
Linked Server: Oracle
Provider: Oracle Provider for OLE DB
Product name: Oracle
Data Source: localhost:1521/tka

.::: Create Linked Server MSSQL to use Open Query (MariaDB/MySQL) insert, update, delete :::.

A. Preconfigurasi
1. Konfiguration ODBC Data Source (64)
system DSN > Add >> MySQL ODBC 5.3 ANSI Driver

Connection Parameters

Data Source Name: dbatka

TCP/IP Server: teguhth-otherserver port 3306

user: root

password: []

Database: teguhth

.::: How to backup and restore table & database Oracle :::.

A. Backup & Restore table Oracle
1. Backup Table Database oracle 
exp aisyah/hanin@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

2. Restore Table Oracle 
imp teguh/triharto@tka
imp teguh/triharto@tka tables=pasok file="D:\labolatorium\oracle\backup\pasok.dmp"

.::: Sample Aljabar(Algebra) Iner join, normal join, equal join(=),left outer join and right outer join In SQL :::.:::.

1. join/normal join/equal join(=)

bertujuan untuk mencari kesamaan data antara table yang satu dengan table yang lain
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT     table-1.field-1,...,table-1.field-n,
        table-2.field-1,...,table-2.field-n,
        table-n.field-1,...,table-n.field-n,
FROM     table-1,...,table-n
WHERE     table-1.field_PK = table-2.field_PK AND
        table-2.field_PK = table-n.field_PK
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

.::: Sample Aljabar(Algebra): union(gabungan), intersection(irisan), MINUS (difference), distinct (proyeksi) In SQL :::.

1. UNION (Combine/gabungan)
union bertujuan menggabungkan dua query atau lebih menjadi satu kesatuan query dengan syarat query/subquery yang akan digabung harus memiliki domain kolom yang sama
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
SELECT         field 1,...,field n,[agregate function]
FROM         table
 [WHERE]     [criteria]
 [GROUP BY] [field 1,...,field n]
UNION
SELECT         field 1,...,field n,[agregate function]
FROM         table
 [WHERE]     [kriteria]
 [GROUP BY] [field 1,...,field n]
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

.::: Sample Query, Sub Query from one table or More in SQL :::.

1. select one table

select * from BARANG;

select * from SUPLIER;

select * from PASOK;

2. select more table

select BARANG.NAMA_BARANG, PASOK.TANGGAL_PASOK as TGL_PASOK,PASOK.JUMLAH_PASOK from BARANG, PASOK where BARANG.KODE_BARANG=PASOK.KODE_BARANG;

select BARANG.kode_BARANG,PASOK.kode_PASOK, BARANG.nama_BARANG,BARANG.satuan_BARANG, PASOK.tanggal_PASOK as tglPASOK, jumlah_PASOK from BARANG, PASOK where BARANG.kode_BARANG=PASOK.kode_BARANG;

.::: Sample min, max, avg, sum, count, Group by, order by, Having, where, comparison (=,<>,>,<,>= atau <=), Between, In, like / not like on Grouping, Sorting & Criteria Data SQL :::.

1. pengelompokan data (group by) dan aggregation function
select * from PASOK;
select KODE_BARANG as KD_BRG, min(JUMLAH_PASOK) as MINIMUM_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, max(JUMLAH_PASOK) as MAXIMUM_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, avg(JUMLAH_PASOK) as AVERAGE_PASOK from PASOK group by KODE_BARANG;
select KODE_BARANG as KD_BRG, sum(JUMLAH_PASOK) as JUMLAH_PASOK from PASOK  group by KODE_BARANG;
select KODE_BARANG as KD_BRG, count(JUMLAH_PASOK) as COUNT_PASOK from PASOK group by KODE_BARANG;

.::: How To Add Column, Update, Insert & Delete row table on SQL example MariaDB :::.

1. Check original table Barang

2. Add Column table Barang (add KETERANGAN)

3. update row table ( update/fill column keterangan)

4. delete row table (example delete KODE_BARANG='ELK-99')

5. Modify Column table ( example change ELK02-ket to ELK02-ketvv2)

6. drop column on table (example delete/drop column KETERANGAN)

1. Check original table Barang

syntax

mysql -u root -p

show tables;

desc barang;

select * from barang;

.::: Migration Oracle to MSSQL using SQLines Data Migration :::.

A. Prepare software migration
1. Download SQLines Data & extract 
http://www.sqlines.com/download
SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.
Note:
SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

2. Download SQLines SQL Converter (Optional)
SQLines SQL Converter
SQLines SQL Converter helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

.::: Migration MariaDB/MySQL to MSSQL using SQLines Data Migration :::.

A. Prepare software migration

1. Download SQLines Data & extract 

http://www.sqlines.com/download

SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.

Note:

SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

.::: Migration MariaDB/MySQL to Oracle using SQLines Data Migration :::.

A. Prepare software migration
1. Download SQLines Data & extract 
http://www.sqlines.com/download
SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.
Note:
SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

2. Download SQLines SQL Converter (Optional)
SQLines SQL Converter
SQLines SQL Converter helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

.::: Migration SQL Server (MSSQL) to Oracle using SQLines Data Migration :::.

A. Prepare software migration

1. Download SQLines Data & extract 

http://www.sqlines.com/download

SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.

Note:

SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

2. Download SQLines SQL Converter (Optional)

SQLines SQL Converter

SQLines SQL Converter helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

.::: Migration SQL Server (MSSQL) to MariaDB/MySQL using SQLines Data Migration :::.

  

A. Prepare software migration

1. Download SQLines Data & extract 

http://www.sqlines.com/download

SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.

Note:

SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

2. Download SQLines SQL Converter (Optional)

SQLines SQL Converter

SQLines SQL Converter helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

3. Extract SQLlines Data

.::: Migration MariaDB / MySQL to Oracle Using SQL Developer and Metode Copy to Oracle (Only Table) :::.

A. Pre Upgrade
1. Download SQL Developer & Extract
2. To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server,  Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download  . Extract the dowloaded zip file named jtds-1.2-dist.zip

.::: Migration SQL Server to Oracle Using SQL Developer and Metode Copy to Oracle (Only Table) :::.

A. Pre Upgrade

1. Download SQL Developer & Extract

2. To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server,  Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download  . 

Extract the dowloaded zip file named jtds-1.2-dist.zip

3. Click “Tools” and then “Prefenrences”.

.::: Migration SQL Server to Oracle Using SQL Developer with Migration Metode :::.

A. Pre Upgrade

1. Download SQL Developer & Extract

2. To connect to third-party database (MySQL, Microsoft SQL Server, Sybase Adaptive Server,  Microsoft Access, IBM DB2) using SQL Developer, we need jTDS driver. You can download needed jTDS driver from following link. http://sourceforge.net/projects/jtds/files/jtds/1.2/jtds-1.2-dist.zip/download  . 

Extract the dowloaded zip file named jtds-1.2-dist.zip

3. Click “Tools” and then “Prefenrences”.

.::: Migration Oracle to MariaDB/MySQL using SQLines Data Migration :::.

A. Prepare software migration
1. Download SQLines Data & extract 
http://www.sqlines.com/download
SQLines Data is a tool for data transfer, schema conversion and data migration validation/testing.
Note:
SQLines Data tool does not convert views, stored procedures, functions, and triggers, use SQLines SQL Converter to convert them.

2. Download SQLines SQL Converter (Optional)
SQLines SQL Converter
SQLines SQL Converter helps you convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers.

.::: Migration Oracle Database to Microsoft SQL Server (MSSQL) using SSMA for Oracle :::.

A. Pre Migration

1. Download & Install SQL Server Migration Assistant for Oracle 

https://docs.microsoft.com/id-id/sql/ssma/oracle/installing-ssma-for-oracle-client-oracletosql?view=sql-server-2017

SSMAforOracle_8.16.0.msi

SSMAforOracleExtensionPack_8.16.0.msi

.::: Configure Microsoft Distributed Transaction Coordinator (MSDTC). Trouble shoot Failed to restart the MS DTC Service :::.

Modules such as the Web Portal, the iOS app, and any custom built API rely on Integration Services to communicate with the database. Integration Services enables access so these programs can authenticate, read, and update information in your Perspective database.

Integration Services is based on Microsoft Distributed Transaction Coordinator (MSDTC). The MSDTC service is a component of modern versions of Microsoft Windows that are responsible for coordinating transactions that span multiple resource managers, such as databases, message queues, and file systems.

MSDTC needs to be configured only if your database and the Perspective application reside on separate servers. 

Configure MSDTC

1. From Start, search for dcomcnfg and press Enter on your keyboard.

.::: Migration Microsoft SQL Server to MariaDB/MySQL using MySQL WorkBench :::.

 

A. Pree

1. capture mysql for destination/Target

  show databases

 

2. capture MSSQL for source

select * from sys.databases
use teguhth 
select * from INFORMATION_SCHEMA.TABLES
exec sp_columns barang
select * from barang

.::: Migration MySQL/MariaDB to Microsoft SQL Server (MSSQL) :::.

Pre Migration

1. Download & Install SQL Server Migration Assistant for MySQL 

https://docs.microsoft.com/id-id/sql/ssma/sql-server-migration-assistant?view=sql-server-2017

2. Download Driver Connector ODCB ( version 5.2 - 5.3.3) & Install 

https://dev.mysql.com/downloads/connector/odbc/

3. Optional Backup Database MySQL/MariaDB

.::: Check Table Capacity & Size Report SQL Server on LocalServer & Linked Server (Open Query) :::.

A. Access Local Server
1. Check Database_ID
select @@Servername as LocalServer,@@servername as ServerName_Link, name as dbname,database_id  from sys.databases
select @@Servername as LocalServer,@@servername as ServerName_Link, name as dbname,database_id  from sys.databases where name = 'teguhth';