REPLICATION WITH SNAPSHOT

Widi Jatmiko

0703040043

Jurusan Teknik Informatika, Fakultas Teknik,Universitas Muhammadiyah Purwokerto

Jl. Raya Dukuh Waluh Banyumas

E-mail: zatmiko_it@yahoo.co.id

http://widjat.blogspot.com

ABSTRAKS

Secara default, semua tiga jenis replikasi menggunakan snapshot untuk menginisialisasi Pelanggan. SQL Server Agen Snapshot selalu menghasilkan file snapshot, tapi agen yang memberikan file berbeda, tergantung pada jenis replikasi yang digunakan. Snapshot replikasi dan replikasi transaksional menggunakan Agen Distribusi untuk mengirimkan file, sedangkan menggabungkan replikasi menggunakan Server Agen Gabung SQL. Agen Snapshot berjalan pada Distributor. Agen Distribusi dan Agen Merge dijalankan pada Distributor untuk langganan mendorong, atau Pelanggan untuk langganan menarik. Untuk informasi lebih lanjut tentang mendorong dan menarik langganan, lihat Berlangganan Publikasi . Untuk informasi lebih lanjut tentang agen, lihat Ikhtisar Replikasi Agen .

Pada artikel ini, saya ingin memberitahu Anda tentang beberapa topik umum Microsoft SQL Server replikasi: replikasi topologi, jenis replikasi, dan agen replikasi. Saya juga akan membahas replikasi Snapshot: cara untuk memeriksa kondisi yang diperlukan untuk jenis replikasi dan bagaimana untuk backup dan restore database dalam skenario replikasi. Untuk ilustrasi konsep-konsep ini, saya juga akan menyediakan panduan langkah demi langkah untuk membuat sebuah proses Snapshot replikasi. Karena itu hanya contoh tes, saya menggunakan hanya satu server untuk replikasi data: Penerbit, Pelanggan dan Distributor database tinggal pada mesin yang sama.

1. KONSEP UMUM

Replikasi adalah proses dimana data disalin antara database pada server yang sama atau server yang berbeda dihubungkan oleh LAN, WAN, atau Internet. Microsoft replikasi SQL Server menggunakan metafora penerbit, distributor dan pelanggan.

Publisher adalah server atau database yang mengirimkan datanya ke server lain atau database.

Pelanggan adalah server atau database yang menerima data dari server lain atau database.

Distributor adalah server yang mengelola aliran data melalui sistem replikasi. Server ini berisi database distribusi.

Penerbit berisi publikasi / publikasi. Publikasi adalah kumpulan dari satu atau lebih artikel yang dikirim ke server pelanggan atau database.dalam makalah ini.

Berlangganan adalah kelompok data yang server atau database akan menerima.

Ada push dan pull langganan. Langganan Push berlangganan ketika server penerbitan berkala akan mendorong transaksi ke server berlangganan atau database.

Tarik berlangganan berlangganan adalah ketika server berlangganan berkala akan terhubung ke database distribusi dan menarik informasi.

Database Distribusi adalah database sistem, yang disimpan di Distributor dan tidak mengandung tabel pengguna. Database ini digunakan untuk menyimpan pekerjaan snapshot dan semua transaksi menunggu untuk didistribusikan kepada Pelanggan.

2. REPLICATION TOPOLOGIES

Microsoft SQL Server mendukung replikasi topologi berikut :

• Central subscriber

• Central publisher with remote distributor

• Central distributor

• Publishing subscriber

2.1 Central publisher

This is one of the most used replication topologies. In this scenario, one server is configured as Publisher and Distributor and another server/servers is/are configured as Subscriber/Subscribers.

2.2 Central Subcriber

This is a common topology in data warehousing. Many servers or databases replicate their data to a single central server in one or more databases.

2.3 Central publisher with remote distributor

In this topology, the distribution database resides on another server than publisher. This topology is used for performance reasons when the level of replication activity increases or the server or network resources become constrained. It reduces Publisher loading, but it increases overall network traffic. This topology requires separate Microsoft SQL Server installations, one for the Publisher and one for the Distributor.

2.4 Central distributor

This is a dual role topology. In this topology, two servers publish the same data. One publishing server sends data to the subscriber, and then this subscriber publishes data to any number of other subscribers. This is useful when a Publisher must send data to Subscribers over a slow or expensive communications link.

3. REPLICATION TYPES

Snapshot replication is the simplest type of replication. With this kind of replication, all replicated data (replica) will be copied from the Publisher database to the Subscriber's/Subscribers' database(s) on a periodic basis. Snapshot replication is best used as a method for replicating data that changes infrequently and when the size of replicated data is not very large.

With Transactional replication, SQL Server captures all changes that were made in an article and stores INSERT, UPDATE, and DELETE statements in the distribution database. These changes are then sent to subscribers from the distribution database and applied in the same order. Transactional replication is best used when the replicated data changes frequently or when the size of replicated data is not small and is not necessary to support autonomous changes of the replicated data on the Publisher and on the Subscriber.

Merge replication is the most difficult replication type. It makes possible autonomous changes to replicated data on the Publisher and on the Subscriber. With Merge replication, SQL Server captures all incremental data changes in the source and in the target databases, and reconciles conflicts according to rules you configure or using a custom resolver you create. Merge replication is best used when you want to support autonomous changes of the replicated data on the Publisher and on the Subscriber.

4. REPLICATION AGENTS

Microsoft SQL Server 7.0/2000 supports the following replication agents:

• Snapshot Agent

• Log Reader Agent

• Distribution Agent

• Merge Agent

The Snapshot Agent is a replication agent that make snapshot files, stores the snapshot on the Distributor, and records information about the synchronization status in the distribution database. The Snapshot Agent is used in all replication types (Snapshot, Transactional, and Merge replications) and can be administered by using SQL Server Enterprise Manager.

The Log Reader Agent is a replication agent that moves transactions marked for replication from the transaction log on the Publisher to the distribution database. This replication agent is not used in Snapshot replication.

The Distribution Agent is a replication agent that moves the snapshot jobs from the distribution database to Subscribers, and moves all transactions waiting to be distributed to Subscribers. The Distribution Agent is used in Snapshot and Transactional replications and can be administered by using SQL Server Enterprise Manager.

The Merge Agent is a replication agent that applies initial snapshot jobs from the publication database tables to Subscribers, and merges incremental data changes that have occurred since the initial snapshot was created. The Merge Agent is used only in Merge replication.