Step by Step Guide to MS SQL 2008 Transactional Replication over Internet Part – I

Replication is can be defined as a process of copying or reproducing information between two or more resources to improve reliability, fault-tolerance or accessibility. Replication can be configured on LAN, WAN or on Internet for database where same data is copied and stored on multiple database servers or for web services where website data is distributed on various locations to increase the availability of website/database in case of any failure at one location.

Database is a very important constraint of any website or origination as it stores all the data. Any problem or failure in database server can lead many problems like work stoppage, unwanted downtime, loss in terms of money and time etc. to over come such problems most of the organization opt for the database replication solution. Most of the database servers like MySQL, Oracle, MSSQL supports database replications and provide various tools for the it.

In this series I am going to explain how you can configure MSSQL 2008 Transactional Replication between two servers which are located in countries on two different continents i.e. America and India. Transactional Replication is a automatic process of distributing changes between master and slave. In this process data is copied in real-time from the master server which is termed as publisher to the slave server which plays the role of subscriber. On its first run Transactional Replication makes complete copy of the Publisher data, and then all subsequent copies transfer modified data to the Subscriber only. Microsoft SQL Server replication uses a publish, distributor and subscribe relation.

Publisher : Publisher is the Primary or Master server which host the original data or database where actual or real-time changes/updates are made.
Subscriber : The Secondary or slave server which gets or fetch data from Primary (Publisher) Database.
Distributor : This is a system or server which manage the data flow in complete replication setup. We can configure Distributor and publisher on single server.

The Snapshot Agent prepares snapshot files of tables and database objects and records synchronization jobs on distributor. Log Reader Agent monitors the transaction log of each database configured in replication and copies the transaction recorded by the Snapshot Agent. The Distribution Agent moves the initial snapshot jobs and the transactions held in the distribution database tables to Subscribers.

Transactional replication is helpful when:

  • You want the changes made on primary server get reflected on secondary server as soon as they occur.
  • You want database to follow ACID properties.
  • Availability or connectivity of subscriber to the publisher is frequent.

User X have a website hosted in a US based Datacenter and the website is expected to have heavy traffic which is going to put lots of stress on database. Data of the website including user data, their profiles and other information is stored in the MSSQL 2008 Database. Now the owner want to configure a replication of database (in a remote) to increase the availability and fault-tolerance for the database, so he contacts a datacenter in India to Host his secondary database server in their facility.

In next post (Guide to MS SQL 2008 Transactional Replication (Configure Distributor) Part – II) I have explained how to configure the servers to host replication and steps to configure distribution server.

About: Mike

Milind Koyande loves to work on new technologies specially virtualization and troubleshoot server problems. I’m an avid photographer and love to spend my free time close to nature, trying to capture its glory on my camera.