8.

DATA TRANSFER & TASK AUTOMATION

Objektif:
Setelah mempelajari materi ini, mahasiswa diharapkan dapat melakukan hal berikut:

1. Dapat melakukan operasi data transfer dengan SQL Server 2014.

2. Dapat mengetahui komponen task automation dalam SQL Server 2014.
3. Dapat melakukan operasi Trigger dengan SQL Server.
4. Dapat melakukan operasi Rollback dengan SQL Server.

8.1. Pendahuluan
Dalam pengelolaan sistem basis data modern, transfer data dan otomatisasi tugas
administrasi merupakan dua aspek penting yang membantu memastikan kelancaran operasional
dan efisiensi. Transfer data mencakup proses perpindahan data dari satu sumber ke sumber lain,
baik itu untuk migrasi ke sistem basis data yang lebih kuat, seperti dari Microsoft Access ke SQL
Server, ataupun untuk integrasi data dari berbagai sistem manajemen basis data (DBMS) yang
berbeda. Proses ini sering kali melibatkan langkah-langkah seperti memuat data, melakukan
backup, serta melakukan migrasi data, dan dapat memerlukan alat-alat seperti Data Transformation
Services (DTS) dan Bulk Copy Program (BCP) untuk memindahkan dan mengubah data sesuai
kebutuhan.

Sementara itu, untuk meningkatkan efisiensi pengelolaan sistem, banyak tugas

administratif yang dilakukan secara berulang dapat diotomasi. Tugas-tugas seperti backup
database, pemeriksaan integritas data, dan pembuatan indeks dapat dijadwalkan secara otomatis
dengan bantuan SQL Server Agent. Dengan otomatisasi, tugas-tugas ini dapat dijalankan tanpa
intervensi manual, mengurangi risiko kesalahan manusia, serta meningkatkan efisiensi
operasional. Otomatisasi ini memastikan bahwa tugas-tugas penting, seperti backup data, dapat
dilakukan secara teratur dan tepat waktu, menjaga integritas dan keamanan data.
8.2. Proses Data Transfer

Proses data transfer dalam SQL Server adalah rangkaian langkah yang dilakukan untuk
memindahkan data dari satu lokasi ke lokasi lain, baik antar database, antar server, atau antar
format. Dalam proses ini, data bisa diimpor (dimasukkan ke SQL Server dari sumber eksternal)
atau diekspor (dipindahkan dari SQL Server ke target lain). Berikut adalah beberapa subproses
utama yang biasanya terjadi dalam data transfer:

Proses transfer data dapat mencakup berbagai tugas utama, yaitu:

1. Memuat (Loading) Data ke Dalam Database: Memuat data adalah proses mengimpor data
dari sumber eksternal, seperti file, ke dalam database. Ini merupakan langkah dasar dalam
transfer data.
2. Pengarsipan Data (Backup): Pengarsipan adalah bentuk ekspor data di mana salinan database
dan/atau log transaksi dibuat sebagai cadangan. Proses ini penting untuk memastikan
keamanan data dan memungkinkan pemulihan jika diperlukan.
3. Migrasi Data: Migrasi data adalah proses yang lebih kompleks dibandingkan dengan memuat
data, melibatkan beberapa langkah, seperti mengidentifikasi sumber data, mengekspor data,
menentukan tempat penyimpanan tujuan, dan mengimpor data ke dalam target.

Transformasi data adalah proses penting dalam migrasi data, terutama saat data dipindahkan
antar sistem yang memiliki format atau struktur berbeda. Beberapa tugas utama dalam transformasi
data termasuk:

1. Mengubah Representasi Data : Ini melibatkan perubahan bentuk atau format data, seperti
mengonversi data dari kode EBCDIC ke format ASCII.
2. Restrukturisasi Data: Proses ini melibatkan pengubahan dan pengorganisasian data dari
berbagai sumber agar sesuai dengan kebutuhan penyimpanan dan analisis yang lebih efisien,
sering kali mencakup agregasi dan perhitungan kompleks.

8.3. Data Transformation Services (DTS)

DTS (Data Transformation Services) adalah metode alternatif yang bisa digunakan untuk
memindahkan data dari Microsoft Access ke SQL Server. DTS menyediakan kemampuan untuk
mengimpor dan mengekspor data antara SQL Server dan sumber data lain yang mendukung OLE
DB atau ODBC, termasuk Microsoft Access. SQL Server sudah dilengkapi dengan DTS dan fitur
DTS Import and Export Wizard yang memungkinkan pengguna untuk membuat dan menjalankan
paket DTS dengan mudah.

DTS Package (Data Transformation Services Package) adalah kumpulan instruksi atau
tugas yang digunakan untuk memindahkan, mengubah, dan mengelola data antara berbagai sumber
dan target data dalam SQL Server. Setiap DTS package dirancang untuk menyelesaikan tugas-
tugas tertentu, seperti transfer data, pembersihan data, transformasi, atau integrasi data dari satu
sistem ke sistem lain.

8.4. BCP UTILITY

BCP (Bulk Copy Program) adalah alat yang digunakan untuk menyalin data dalam jumlah
besar antara SQL Server dan file data dengan format yang ditentukan oleh pengguna. Dengan BCP,
Anda bisa mengimpor banyak baris data ke dalam tabel SQL Server, atau mengekspor data dari
tabel ke file data. Kecuali jika menggunakan opsi query out, Anda tidak perlu memahami Transact-
SQL untuk menggunakan alat ini. Saat mengimpor data ke tabel, Anda perlu menggunakan file
format yang sesuai dengan tabel tersebut atau memahami struktur tabel dan tipe data yang
digunakan di setiap kolom.

MENGGUNAKAN BCP UNTUK EKSPOR TIPE DATA KARAKTER

Untuk mengekspor data dari tabel ke file data, menggunakan bcp dengan opsi keluar,
spesifikasinya sebagai berikut:
Spesifikasi Deskripsi
-C Menentukan format karakter
t, Menentukkan koma (,) sebagai terminator lapangan
-T Menentukkan bahwa utilitas bcp menghubungkan ke SQL Server dengan koneksi
terpercaya menggunakan keamanan terpadu. Jika –T tidak ditentukan, maka perlu
menentukan –U dan –P untuk berhasil login
Contoh berikut ekspor data dalam format karakter dari tabel myTestCharData ke dalam file data
baru bernama data file [Link] yang menggunakan koma (,) sebagai terminator
lapangan. Pada command prompt Microsoft Windows, masukkan:
BCP AdventureWorks..myTestCharData out C: \ [Link] -c -t, -T

8.5. AUTOMATING SYTEM ADMINISTRATION TASKS

Automating System Administration Tasks adalah proses menggunakan alat, skrip, atau
teknologi untuk mengotomatiskan tugas-tugas administrasi sistem yang biasanya dilakukan secara
manual oleh administrator sistem. Tujuan utama dari otomatisasi ini adalah untuk meningkatkan
efisiensi, mengurangi kesalahan manusia, dan menghemat waktu dalam pengelolaan sistem dan
infrastruktur TI.

Terdapat beberapa tugas administrative yang sering dikerjakan dan oleh karenanya perlu untuk di
otomasi, yaitu:

1. Backup database dan Transaction Log

2. Transfer Data

3. Menghapus dan membuat kembali Index

4. Memeriksa integritas

Komponen SQL Server yang digunakan dalam otomasi adalah sebagai berikut:
1. SQL Server Service, digunakan untuk mencatat peristiwa (event) ke dalam Windows
NT/2000 event log. Beberapa peristiwa dicatat secara otomatis, sementara yang lain perlu
dihasilkan oleh administrator sistem.

2. Windows NT/200 event log adalah tempat di mana semua pesan dari sistem operasi
Windows NT/2000, serta pesan dari komponennya, dicatat. Fungsi dari event log dalam
proses otomatisasi adalah untuk memberi tahu SQL Server Agent tentang adanya peristiwa
tersebut.
 3. SQL Server Agent adalah layanan yang menghubungkan Windows NT/2000 event log
dengan SQL Server Service. Peran SQL Server Agent dalam proses otomatisasi adalah
untuk mengambil tindakan setelah menerima notifikasi dari Windows NT/2000 event log.

8.6. SQL SERVER AGENT

SQL Server Agent adalah layanan pada Microsoft Windows yang dirancang untuk
menjalankan dan mengotomatisasi berbagai tugas administratif pada SQL Server. Tugas ini dikenal
sebagai "jobs" dan dapat dijadwalkan atau dieksekusi berdasarkan peristiwa tertentu. Contoh
umum dari tugas yang dapat diotomatisasi termasuk backup database, pemeriksaan integritas data,
dan pemotongan transaction log.

SQL Server Agent memungkinkan pembuatan alert untuk memantau kondisi kritis.
Misalnya, jika terjadi pengisian log transaksi yang melebihi batas toleransi, alert akan diaktifkan
dan sistem administrator akan diberi notifikasi melalui email. Jika terdapat kegagalan pada backup
transaction log, SQL Server Agent dapat secara otomatis menjalankan tugas untuk memotong log
transaksi, terutama jika kegagalan tersebut disebabkan oleh overflow. Agent ini memainkan peran
penting dalam memantau kejadian-kejadian kritis dan meresponsnya secara otomatis untuk
menjaga kelancaran operasional sistem.

Sebelum dapat menggunakan SQL Server Agent, layanan ini harus diaktifkan terlebih
dahulu. Ada beberapa cara untuk memulai SQL Server Agent, termasuk melalui:

 SQL Service Manager

 SQL Server Enterprise Manager
 Service Application pada Control Panel
 Net command

Konfigurasi SQL Server Agent juga mencakup pengaturan akun login service serta profil
email untuk pemberitahuan. Selama instalasi, akun login yang digunakan bisa bersifat lokal
maupun domain. Sistem operasi Microsoft NT/2000 menyediakan aplikasi email bernama
Windows NT/2000 Mail, yang digunakan oleh SQL Server untuk berkomunikasi dan bertukar
pesan antar komputer. SQL Server menggunakan SQL Mail, sebuah layanan yang mendukung
Messaging Application Programming Interface (MAPI), untuk mengirim dan menerima pesan,
baik berupa teks, file, atau hasil query.

Sebelum SQL Server dapat mengirimkan dan menerima pesan, sistem harus dikonfigurasi
sebagai mail client. Proses ini melibatkan penentuan nama profil email yang digunakan oleh SQL
Server Agent untuk memulai mail session dan mengirimkan notifikasi melalui email setiap kali
layanan SQL Server Agent dijalankan. Dengan pengaturan ini, SQL Server Agent dapat
memberikan pemberitahuan otomatis kepada operator melalui email, mempermudah pemantauan
dan pengelolaan sistem secara efektif.

8.7. Membuat Jobs dan Operator

Tugas adalah pembuatan job. Job dapat dibuat menggunakan Create Job Wizard atau SQL
Server Enterprise Manager. Secara umum, terdapat tiga langkah yang harus diikuti saat membuat
job:

1. Membuat job dan langkah-langkahnya.

2. Membuat jadwal eksekusi job jika job tidak dieksekusi sesuai permintaan pengguna.
3. Notifikasi operator mengenai status job.
Sebuah job dapat terdiri dari satu atau lebih langkah yang didefinisikan dengan cara berikut:
 Menggunakan perintah Transact-SQL (T-SQL).
 Eksekusi utilitas SQL Server.
 Menjalankan program yang dikembangkan dengan bahasa pemrograman seperti Visual
Basic.
Sebagai contoh, untuk melakukan backup database, Anda akan menggunakan perintah BACKUP
DATABASE. Jika job memiliki beberapa langkah, penting untuk menentukan aksi yang harus
diambil ketika terjadi kegagalan, seperti seberapa sering langkah seharusnya dicoba kembali jika
gagal.

8.7.1. Membuat Job Menggunakan Enterprise Manager

Untuk membuat job backup database, pilih database dan klik tombol New Job pada toolbar
di Enterprise Manager. Pada tab General, masukkan nama job, misalnya backup_sample, dan
centang kotak Enabled. Pilih sumber eksekusi (local server atau multiple servers). Pada tab Steps,
klik New untuk mendefinisikan langkah job. Masukkan nama langkah, pilih jenis langkah, dan
masukkan perintah SQL yang sesuai.

8.7.2. Membuat Jadwal Job

Setiap job yang dibuat dapat dieksekusi sesuai permintaan atau menggunakan satu atau
lebih jadwal. Untuk membuat jadwal, klik kanan job di panel detail dan pilih Properties. Pilih tab
Schedules dan klik New Schedule. Atur frekuensi eksekusi, misalnya, setiap hari Jumat jam 20:00.

8.7.3. Membuat Operator untuk Notifikasi

Setelah job selesai, Anda dapat memberikan notifikasi kepada operator. Untuk membuat
operator, pilih SQL Server Agent, klik kanan pada Operators, dan pilih New Operator. Masukkan
nama operator dan metode notifikasi (email atau net send). Atur pemberitahuan berdasarkan status
job (sukses atau gagal) pada tab Notification.

8.7.4. Menampilkan Job History Log

SQL Server menyimpan informasi aktivitas job dalam tabel sysjobhistory di database
msdb, yang mencatat riwayat job. Riwayat job dapat dilihat melalui Enterprise Manager dengan
mengklik kanan job dan memilih View Job History. Setiap baris dalam log mencakup tanggal,
waktu, status (sukses/gagal), operator yang diberi tahu, durasi, dan pesan error. Secara default, log
riwayat job terbatas hingga 1000 baris, dengan maksimum 100 baris per job. Untuk memperbesar
ukuran log, buka SQL Server Agent Properties, lalu sesuaikan pengaturan pada tab Job System.
Cek box dapat dibersihkan untuk menghilangkan batas ukuran log.

8.8. Alert di SQL Server

Alert pada SQL Server adalah fitur yang memberi peringatan ketika kondisi tertentu terjadi.
Informasi terkait job dan pesan kesalahan disimpan dalam event log, yang dibaca oleh SQL Server
Agent. Jika pesan di log sesuai dengan alert yang ditetapkan, SQL Server Agent memicu alert.
Alert dapat digunakan untuk menangani masalah potensial seperti penuhnya transaction log,
kesalahan SQL Server, atau kesalahan yang didefinisikan pengguna. Alert bisa diatur berdasarkan
error number atau grup error tertentu dan dapat dibuat melalui Enterprise Manager atau Create
Alert Wizard.

8.8.1. Membuat Alert pada SQL Server Error Number

Membuat alert berdasarkan error number di SQL Server adalah proses mengonfigurasi
SQL Server untuk memantau dan merespons error tertentu yang terjadi dalam sistem, berdasarkan
nomor error spesifik. Ketika error dengan nomor tertentu muncul, SQL Server Agent akan
membangkitkan peringatan (alert) dan dapat melakukan tindakan otomatis seperti mengirimkan
notifikasi kepada operator, menjalankan job, atau mengirim [Link] peringatan harus
memiliki nama. Nama peringatan harus unik dalam contoh SQL Server dan dapat tidak lebih dari
128 [Link] membuat alert deadlock, klik kanan Alert dan klik New Alert pada dialog box
New Alert Properties, masukkan nama alert dalam box Name. Klik Error number dan masukkan
1205 (Error ini menindikasikan bahwa SQL Server menemukan deadlock dan proses dipilih
sebagai “korban”). Sistem akan secara otomatis menampilkan teks pesan dari error number disisi
sebelah kanan angka. Pada list Database name, pilih sample.

Langkah kedua menentukan respon untuk alert. Klik tab Response. Pertama pilih Execute
Job, kemudian klik job untuk dieksekusi ketika alert terjadi. Hal ini dapat dilaksanakan dengan
meng-klik tiga titik(…) pada sisi sebelah kanan dari box. Pada frames Operators to notify , dapat
dipilih operator dan metode dari pemberitahuan tersebut.

8.8.2. Membuat Alert pada SQL Server Severity Level

Proses konfigurasi untuk memonitor dan memberikan peringatan ketika error dengan
tingkat keparahan (severity level) tertentu terjadi. SQL Server memiliki severity level yang
berkisar dari 0 hingga 25, yang menunjukkan tingkat seriusnya suatu kesalahan, mulai dari
informasi ringan hingga masalah fatal yang dapat mengganggu server. Severity level lebih tinggi
adalah error yang lebih serius. SQL Server error dengan severity level 20 sampai 25 adalah fatal
error. SQL Server error dengan severity level 19 samapi 25 dituliskan pada Windows NT/2000
event log. Terdapat demo alert, gunakan demo alert untuk severity level 24, bagaimana alert
tersebut dapat dimodifikasi dan diperluas.

8.8.3. Membuat Trigger

Trigger Sql Server merupakan kode prosedural yang akan secara otomatis dieksekusi akibat
dipicu oleh sebuah event/ kejadian pada suatu tabel. Trigger digunakan untuk pemrosesan kondisi
statement DML (Data Manipulation Language) seperti INSERT, DELETE, dan UPDATE, namun
trigger tidak dapat digunakan dalam statement SELECT.

Manfaat dari penggunaan trigger diantaranya:

 Trigger merupakan salah satu mekanisme untuk menjalankan aturan bisnis dan menjaga
integritas data dalam sebuah basis data. Mekanisme yang lainnya adalah penggunaan
CONSTRAINT.

 Trigger dapat mengevaluasi status dari sebuah tabel sebelum dan sesudah terjadi perubahan
data dan melakukan aksinya sesuai perbedaan yang terjadi.

 Trigger dapat melakukan perubahan cascade melalui relasi tabel pada database, akan tetapi
perubahan ini akan lebih efisien jika dieksekusi menggunakan referential integrity constraints.

 Trigger dapat melakukan pencegahan akses atas data yang kompleks dibanding dengan
menggunakan CHECK CONSTRAINT

Mendeklarasikan Trigger

CREATE TRIGGER {nama_trigger}

ON {nama_table}

FOR {DELETE | INSERT | UPDATE}

AS

{SQL STATEMENT}
Berikut ini bentuk umum untuk menghapus trigger:

DROP TRIGGER {nama_trigger}

Berikut ini bentuk umum untuk memodifikasi trigger :

ALTER TRIGGER {nama_trigger}

ON {nama_table}

FOR {DELETE | INSERT | UPDATE}

AS

{SQL Statement}

Berikut ini bentuk umum untuk mengaktifkan & menonaktifkan Trigger

DISABLE TRIGGER <nama_trigger> ON TABLE

ENABLE TRIGGER <nama_trigger> ON TABLE

Trigger vs Constraint

 Trigger digunakan untuk menjalankan logika bisnis yang kompleks secara otomatis ketika
terjadi perubahan pada data (seperti INSERT, UPDATE, atau DELETE). Trigger lebih fleksibel
karena dapat menangani aksi lintas tabel atau melakukan logging, tetapi bisa memperlambat
performa jika tidak digunakan dengan tepat.
 Constraint adalah aturan yang digunakan untuk menjaga integritas data secara otomatis pada
tingkat tabel atau kolom. Constraint lebih sederhana dan efisien untuk memastikan validasi
data dasar seperti PRIMARY KEY, FOREIGN KEY, CHECK, atau NOT NULL. Namun,
mereka tidak bisa digunakan untuk logika yang kompleks.
8.8.4. Membuat Rollback
Rollback dalam SQL Server adalah perintah yang digunakan untuk membatalkan
perubahan yang telah dilakukan dalam sebuah transaksi sebelum transaksi tersebut dikomit
(committed). Rollback mengembalikan database ke kondisi seperti sebelum perubahan dilakukan,
sehingga tidak ada data yang diubah atau ditambahkan selama transaksi yang dibatalkan.

Contoh: Studi Kasus

Sebuah institusi pendidikan memiliki sistem manajemen database untuk mengelola

informasi mahasiswa. Tabel yang menyimpan data mahasiswa bernama Mahasiswa. Salah satu
kebijakan penting dari institusi ini adalah bahwa mahasiswa dengan status "Aktif" tidak boleh
dihapus dari sistem. Oleh karena itu, perlu dibuat mekanisme untuk mencegah penghapusan data
mahasiswa dengan status tersebut.

Tujuan dari studi kasus ini adalah untuk menerapkan trigger di SQL Server yang akan
membatalkan operasi DELETE jika mahasiswa yang ingin dihapus memiliki status "Aktif".
Dengan cara ini, data penting tetap terjaga dan integritas database dapat dipelihara.

Contoh: Trigger dengan Rollback untuk Operasi DELETE

Gambar 8. 1 Trigger dengan Rollback untuk Operasi DELETE

8.8.5. Membuat Error Message

Untuk membuat user-defined error, gunakan Enterprise Manager atau stored prosedur
sp_addmessage. Membuat error message untuk contoh penggunaan stored prosedur
sp_addmessage. Contohnya :
sp_addmesssage 50010,16,’The shipping date of product is earlier than the order
date’,’us_english’,’true’

Stored prosedur sp_addmessage dalam contoh membuat user-defined error message

dengan error number 50010 dan severity level 16. Semua user-defined error message disimpan
dlam tabel sistem sysmessages dari database master. Error number pada contoh adalah 50010
karenaa semua user-defined error harus lebih besar dari 50000 (semua error number kurang dari
50000 adalah untuk SQL Server sistem.

Untuk setiap user-defined error message, dapat secara opsional ditentukan bahasa pada saat
pesan ditampilkan. Penentuan dapat menjadi penting jika banyak bahasa diinstall pada computer
(Ketika banhasa diabaikan, session language adalah bahasa default.

Secara default, user-defined message tidak dituliskan pada Windows NT/2000 event log,
disamping itu , message harus ditulis ke event log jika ingin dibangkitkan alert pada error
messagenya. Oleh karenanya, parameter prosedur sstem sp_addmessage yang sesuai pada contoh
harus di set menjadi true.

8.9. Membuat Proxies dalam SQL Server Agent

Sebuah proxy SQL Server agent mendefinisikan konteks keamanan untuk langkah
pekerjaan. Sebuah proxy menyediakan SQL Server Agent dengan akses keamanan untuk pengguna
Microsoft Windows. Setiap proxy dapat dikaitkan dengan satu atau lebih subsistem. Sebuah
langkah pekerjaan yang menggunakan proxy dapat mengakses subsistem ditentukan dengan
menggunakan konteks keamanan pengguna Windows. Sebelum SQL Server Agent menjalankan
langkah pekerjaan yang menggunakan proxy, SQL Server Agent impersonates didefinisikan dalam
proxy, dan kemudian menjalankan langkah pekerjaan dengan menggunakan konteks keamanan.

Membuat Proxies dengan SSMS, Caranya adalah :

 Klik SQL Server Agent

 Klik kanan Proxies, pilih New Proxie

  Pada halaman General dari dialog New Proxy Account, tentukan nama proxy, nama
credential, dan deskripsi untuk proxy baru. jika belum tersedia, Perhatikan bahwa harus
membuat credential name terlebih dahulu sebelum membuat proxy.

 checklist subsystem yang sesuai untuk proxy.

8.10. Error Logs

SQL Server error log berisi peristiwa yang ditentukan pengguna dan aktivitas sistem
tertentu. Anda dapat menggunakan log kesalahan ini untuk memecahkan masalah yang berkaitan
dengan SQL Server.

Untuk melihat log kesalahan SQL Server :

 Di bagian Object Browser pilih Management.

 Klik tanda plus(+) pada bagian Management.

 Klik kanan SQL Server Logs.

 Pilih View lalu SQL Server Log.