How To Do SQL Server 2008 R2 Replication To Distribute Your Data Or Make Mirror

Replication means making exact copy or synchronize from source database to target with interval of time. So if source data get inserted then for short time target data will get inserted with same data from source.
Same as delete operation, if some records in source data deleted then same data in target table will deleted.

From technical point of view, Replication and Mirroring are different. Replication is synchronization process between databases and intended to be used by day to day application. Whereas mirroring is intended for high availability and data protection of database. So if live database corrupted then mirror database will be used automatically.
However from non technical point of view, they as if similar. Both source & target (secondary) server has same data but replication can have sql query in order to replicate for just specific data and/or combined data.

For some reasons, we want to have a replica for our database. In example, you have two servers with almost or same specification, one server is serving OLTP and other intended to reporting server.
The OLTP server will supply or export its data to Reporting Server, so that overall application load will be devided.

This export data from OLTP to Reporting can be done automatically using SQL Server 2008 R2 Replication. Users do transactional data from their application, data saved to OLTP database.
After that replication do insert or update or delete according source data in short time interval to target which is Reporting database. This makes exact state of data between source & target.

Another example is distribute central data to branches or vice versa. Let’s say you have central data that inputted by head office staffs.
You want some of the data to be distribute to branch servers so that branch users can view central data from their own server.
This also can be done using replication.

SQL Server Replication explanation

Copying some definitions from MS technet site =>
Replication uses a publishing industry i.e magazine and/or newspaper metaphor to represent the components in a replication topology, which include Publisher, Distributor, Subscribers, publications, articles, and subscriptions.

The Publisher is a database instance that makes data available to other locations through replication. The Publisher can have one or more publications, each defining a logically related set of objects and data to replicate.

The Distributor is a database instance that acts as a store for replication specific data associated with one or more Publishers. Each Publisher is associated with a single database (known as a distribution database) at the Distributor. The distribution database stores replication status data, metadata about the publication, and, in some cases, acts as a queue for data moving from the Publisher to the Subscribers. In many cases, a single database server instance acts as both the Publisher and the Distributor. This is known as a local Distributor. When the Publisher and the Distributor are configured on separate database server instances, the Distributor is known as a remote Distributor.

A Subscriber is a database instance that receives replicated data. A Subscriber can receive data from multiple Publishers and publications. Depending on the type of replication chosen, the Subscriber can also pass data changes back to the Publisher or republish the data to other Subscribers.

An article identifies a database object that is included in a publication. A publication can contain different types of articles, including tables, views, stored procedures, and other objects. When tables are published as articles, filters can be used to restrict the columns and rows of the data sent to Subscribers.

A publication is a collection of one or more articles from one database. The grouping of multiple articles into a publication makes it easier to specify a logically related set of database objects and data that are replicated as a unit.

A subscription is a request for a copy of a publication to be delivered to a Subscriber. The subscription defines what publication will be received, where, and when. There are two types of subscriptions: push and pull.

How To Do

This blog post will show you step by step to do replication using SQL Server 2008 R2 in easy way.
Also I only use 1 computer only for demonstration although replication is usually meant for syncing to another computers.

  1. Set up your source database and table. This source database acts as Publisher.
    I called source database TestDB and its table tb_person.
    Below is create script for tb_person:

    CREATE TABLE [dbo].[tb_person](
    	[Name] [nvarchar](50) NULL,
    	[City] [nvarchar](50) NULL,
    	[Province] [nvarchar](50) NULL,
    	[ID] ASC
    ) ON [PRIMARY]
  2. Set up your target database and table. This target database acts as Subcriber
    I created the target database TestRepl and its table name is same as Publisher which is tb_person.

Create the Publication

  • Start Sql Server Agent Service if not started yet
  • Expand Replication folder, Local Publications, Right click, New Publications
  • Click Next on Welcome screen and then choose the database you want to publish.
  • Choose your publication type. In this demo, I use ‘Transactional publication’ which means that publisher sends a stream to subscribers and they cannot update directly to their own data.
    This is one way synchronization. You can choose others choice if you want to.
  • Select your articles. These can be tables or views. I only use ‘tb_person’ table to demo replication.
  • You can add filter to articles but in this demo, I leave it blank for demonstration purposes.
  • Specify when to run snapshot agent. I choose create immiediately
  • Specify agent security account. I use window user for running snapshot & log reader agent. Just click security setting button.
  • Entry account screen will appear. Insert necessary Windows user ID & password
  • Create the publication and click next
  • Completing this wizard by filling in publication name and then click finish
  • Publication has been created. One more thing is to change where snapshot folder will be located.
    Right click on publication and choose Properties. Go to snapshot and specify its folder.
    The snapshot folder is used to share replication data. So Make sure this folder can be full accessed by Agent Security account.

    If your subscriber is database of other PC/server then this snapshot folder must be accessed from network user.
  • Create Subscription

    1. Expand Replication folder, Local Subscriptions, right click, New Subsriptions
    2. Click Next on Welcome screen and then choose your publication that you created earlier
    3. Choose where to run the Distribution Agent(s). I use pull subcription to reduce processing overhead at Distributor and/or Publisher.
      Other choice is push subscription which is publisher pushing snapshot to subscribers when changes are made. However you must be careful especially with heavy daily transaction. It could slowdown publisher performance.
    4. Choose subscriber database. In this demo, I use my own ‘TestRepl’
    5. Specify Subscriber process account and connection to Distributor account. I use Windows user id & password.
    6. Specify the synchronization schedule. This decide when or how copying/syncing data has to occur. I
      You can schedule it with a time interval or daily, weekly.
    7. Specify whether to initialize this subscription. Subsription must be initialize to start replication process.
      In large table with a lot of transaction in minutes should better initialize in off peak hours.
    8. Create the subscription and click next
    9. Complete the subsription’s wizard by clicking finish

    Test The Replication

    You might want to test it. Just try to insert/update/delete data in source / publisher database table which in this demo is ‘tb_person’ in TestDB database.
    After that please look or use select query on target / subscriber database table, ‘tb_person’ in TestRepl database. They should be synced automatically.

    Agung Gugiaji

    Leave a Reply

    Fill in your details below or click an icon to log in: Logo

    You are commenting using your account. Log Out /  Change )

    Google photo

    You are commenting using your Google account. Log Out /  Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out /  Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out /  Change )

    Connecting to %s