Search This Blog
Showing posts with label SQL Server Replication Step by Step. Show all posts
Showing posts with label SQL Server Replication Step by Step. Show all posts

Setting up Transactional Replication in SQL Server 2008 R2.


Click Part 1: SQL Server Replication Step by Step
Replication is one of the High Availability features available in SQL Server. Transactional Replication is used when DML or DDL schema changes performed on an object of a database on one server needs to be reflected on the database residing on another server. This change happens almost in real time (i.e. within seconds). In this article, I will demonstrate the step by step approach to configuring transactional replication in SQL Server 2008 R2.
Scenario: An Address table which belongs to the Person schema in the Adventureworks Database is replicated to the Adventureworks_Replication database residing on the same server. The Adventureworks_Replication database acts as a subscriber. The subscriber is normally present on a  separate database server.
Before we start with the configuration, we need to understand three important terms:
1.       Publisher
2.       Subscriber
3.       Distributor Database
Let’s discuss each these in detail.
Publisher:
The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.
Subscriber:
The Subscriberis the  database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.
Distribution Database:
A database which contains all the Replication commands. Whenever any DML or DDL schema changes are performed on the publisher, the corresponding commands generated by  SQL Server are stored in the Distribution database. This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance. Normally, I have observed that if you keep the distributoion database on the same machine as that of the publisher database and if there are many publishers then it always has an impact on the performance of the system. This is because for each publisher, one distrib.exe file gets created. 
Let us now begin with the Configuring of the Transactional Replication.
There are 3 steps involved for Configuring the Transactional Replication:
1.       Configuring the Distribution Database.
2.       Creating the publisher.
3.       Creating the subscriber.
1.Configuring the Distribution Database.
A database which contains all the Replication commands. Whenever any DML or DDL schema changes are performed on the publisher, the corresponding commands generated by  SQL Server are stored in the Distribution database. This database can reside on the same server as the publisher, but it is always recommended to keep it on a separate server for better performance. Normally, I have observed that if you keep the distributoion database on the same machine as that of the publisher database and if there are many publishers then it always has an impact on the performance of the system. This is because for each publisher, one distrib.exe file gets created. 
2.Creating the publisher.
The Publisher can be referred to as a database on which the DML or DDL schema changes are going to be performed.
3.Creating the subscriber.
The Subscribers the database which is going to receive the DML as well as DDL schema changes which are performed on the publisher. The subscriber database normally resides on a different server in another location.

How it works
Transactional replication is implemented by the Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.
The Log Reader Agent monitors the transaction log of each database configured for transactional replication and copies the transactions marked for replication from the transaction log into the distribution database. The Distribution Agent moves the initial snapshot jobs and the transactions held in the distribution database tables to Subscribers.
Configuring the Distribution Database
1.       Connect to the Microsoft SQL Server 2008 R2 Management Studio.
2.        Right Click on the Replication node and Select Configure Distribution as shown in the screen capture below:
3.       A new window appears on the screen as shown in the screen capture below:
4.       Click  the Next> button and a new window appears on the screen as shown in the screen capture below:
5.       As you can see in the above screen capture, it gives the user two choices. The first choice says that whether the server on which the Replication will be configured will be Hosting the distribution database. The second choice asks the user whether some other server will be Hosting the distribution database. The user can select any one of the either choices are per his/her requirements. I decide to use the First option, i.e. the server on which the Replication is configured will itself be holding the distribution database. Then Click on the Next> button as shown in the screen capture above.
6.       A new window appears as shown in the screen capture below:
7.       Select the first option, i.e. Yes, configure the SQL Server Agent service to start automatically and click on the Next> button as shown in the screen capture above.
8.       A new window appears on the screen as shown in the screen capture below:
As you can see in the above screen capture, you are asked where the Snapshot folder should reside on the Server. Let us first understand what the Snapshot folder exactly is.
The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder. This folder should never be placed on the C drive of the server i.e. the drive which is hosting the Operating System.
Create a folder on any other drive to hold the Snapshot folder and Click on the Next> button as shown in the screen capture above.
9.       A new window appears as shown in the screen capture below:
As you can see in the above screen capture, it displays information such as what will be the distribution database name, the location where the data and the log file will reside. Click on the Next> button as shown in the screen capture above.
10.   A new window appears as shown in the screen capture below:
11.   Click on the Next> button.
12.   Click on the Next> button as shown in the screen capture below:
  
13.   Click on the Finish button as shown in the screen capture below:
14.   Once done, a new database named distribution gets created. In order to confirm it just expand the System Database node and you shall be able to view the distribution database, please refer the screen capture below:
         
Creating the Publisher
The following steps need to be followed while creating the publisher.
1.      Right Click on Local Publications and select New Publications, please refer the screen capture below:
2.      Click on the Next> button as shown in the screen capture below.
3.      Select the database which is going to act as a publisher. In our case, I select the AdventureWorks database. Please refer the screen capture below and Click on the Next> button.
4.      Select Transactional Replication from the available publication type and Click on the Next> button as shown in the screen capture below:
5.      Select the Objects that you want to publish. In this example, we will select a table named Person which we need to Replicate. Select the table as shown in the screen capture below and Click on the Next> button. One important point to note is that Only those tables can be replicated in Transaction Replication which has a Primary Key column in it.
6.      Since there are no filtering conditions, Click on the Next> button as shown in the screen capture below:
  
7.      Check the first radio button as shown in the screen capture below and Click on the Next> button.
8.      Click on the Security Settings tab as shown in the screen capture below.
A new window appears as shown in the screen capture below.
Select Run under the SQL Server Agent service account as the account under which the Snapshot Agent process will run and Connect to the publisher By impersonating the process account as shown in the screen capture below and then Click on the OK button.
Click on the Next> button as shown in the screen capture below.
9.      Click on the Next> button as shown in the screen capture below.
 
10.  Give a suitable name to the publisher ad Click on the Finish button as shown in the screen capture below.
Creating the Subscriber
Once the publisher is created the next step is to create the subscriber for it.
The following steps needs to be performed for creating the subscriber.
1.       Right Click on the publisher created and select New Subscriptions as shown in the screen capture below.
2.       Click on the Next> button as shown in the screen capture below.
3.       Click on the Next>  button as shown in the screen capture below.
4.       Click on the Next> button as shown in the screen capture below.
5.       As shown in the screen capture below, it asks for the Subscriber name as well as the subscription database. The subscriber database can be created by restoring the publisher database at the start itself or by creating a new database as shown in the screen capture below.
If you have already restored the backup of the database which is a publisher, then the database name will appear in the dropdown as shown in the screen capture below:
If we wan’t to now create the subscriber database then it can be done as follows:
Click on New Database as shown in the screen capture below.
A new window appears as shown below. Give a suitable database name as well as the path where the data and log file are going to reside.
Click on the OK button.
If the subscriber is some other server, then the following steps need to be performed.
Click on the down arrow available on the Add Subscriber button as shown in the screen capture below.
Click on Add SQL Server Subscriber as shown in the screen capture above.
A new window appears which asks for the SQL Server Name as well as the Authentication neeed to connect to the SQL Server, please refer the screen capture below.
6.       Click on the Next> button as shown in the screen capture below.
                      
7.       Click on the button as shown in the screen capture below. Here we need to specify              
     Process account as well as the connection options for the distribution agent.
     
8.        A new window appears as shown in the screen capture below.
   
9.       Specify the distribution agent to run under the SQL Server Agent Service Account. Also connect to the distributor as well as the subscriber by impersonating the process account. Please refer the screen capture below.
10.   Click on the OK button as shown in the screen capture above.
11.   Click on the Next> button as shown in the screen capture below.
12.   Ensure that the Agent is scheduled to Run Continuously and then click on the Next> button as shown in the screen capture below.
13.   Ensure that the Subscriber is initialized immediately and then click on the Next> button as shown in the screen capture below.
14.   Click on the Next> button as shown in the screen capture below.
15.   Click on the Finish button as shown in the screen capture below.
16.   This creates a subscriber for the corresponding publisher.
17.   Expand the publisher node and you shall be able to view the subscriber as shown in the screen capture
Thus, we have successfully set the Transactional Replication in SQL Server 2008 R2.

SQL Server Replication Step by Step

Introduction

Earlier, most of the applications were using standalone environment where a single centralized server was responding to multiple users, working in different locations.

Centralized Approach and Problems

  • Performance problems
  • Availability problems
  • Maintenance problems
To overcome all the above problems, we can use replication solution.
Replication allows to maintain same database multiple copies at different locations. Log shipping and mirroring allows to maintain complete database redundancy whereas replication allows to maintain some part of the database (a set of required objects) at users location. Changes made at different user locations are synchronized to the main server. It is object level high availability feature. According to Books Online:
Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.
Unlike other methods of high availability, it doesn’t distribute entire database, but only distributes some part of database like tables or views.

Advantages

  • Improved performance
  • To reduce locking conflicts when multiple users are working
  • Improved availability
  • Easy maintenance
  • To allow sites work independently. So that each location can set up its own rules and procedures for working with its copy of the data.
  • To move data closer to the user

SQL Server 2005 Features

  • Restartable Snapshots
  • Oracle Publishing
  • Replicating all DDLs
  • Merge Replication allows to introduce custom business logic into the synchronization process
  • Merge Replication provides the ability to replicate data over HTTP with web synchronization option
  • Updatable Transactional Subscriptions can now handle updates to large data types at Subscribers

SQL Server 2008 Features

  • In SQL Server 2005, replication had to be stopped in order to perform some actions like adding nodes, making schema changes, etc. But in 2008, these can be done online.
  • Conflict detection capacity in peer-to-peer replication.
  • All types of conflicts are detected and reported through agent error log or conflicts table.
  • In SQL Server 2005, switch partition is unsupported, but in 2008 it supports.
    • @allow_partition_switch
    • @replicate_partition_switch
  • Performance improvements, under Windows 2008
    • Snapshot delivery of more than 500MB/minute
    • Time to deliver 100000 varbinary(max) records in less than 2minutes where in 2005 223 minutes.

SQL Server 2012 Features

  • Updatable subscriptions with transactional publications are discontinued.
  • Four new stored procedures provide replication support for AlwaysOn.
    • sp_get_redirected_publisher
    • sp_redirect_publisher
    • sp_validate_replica_hosts_as_publishers
    • sp_validate_redirected_publisher
  • Replication supports the following features on Availability groups:
    • A publication database can be part of an availability group. The publisher instances must share a common distributor.
    • In an AlwaysOn Availability Group, an AlwaysOn secondary cannot be a publisher. Republishing is not supported when replication is combined with AlwaysOn.
    • Heterogeneous replication to non-SQL Server subscribers is deprecated. To move data, create solutions using change data capture and SSIS.
    • Oracle Publishing is deprecated.

Replication Architecture


REPLICATION ENTITIES

SQL Server replication is based on the “Publish and Subscribe” metaphor. Let us look at each of the individual components in detail.
  1. Publisher
    • It is a source database where replication starts. It makes data available for replication.
    • Publishers define what they publish through a publication.
  2. Article
    • Articles are the actual database objects included in replication like tables, views, indexes, etc.
    • An article can be filtered when sent to the subscriber.
  3. Publication
    • A group of articles is called publication.
    • An article can’t be distributed individually. Hence publication is required.
  4. Distributor
    • It is intermediary between publisher and subscriber.
    • It receives published transactions or snapshots and then stores and forwards these publications to the subscriber.
    • It has 6 system databases including distribution.
  5. Subscriber
    • It is the destination database where replication ends.
    • It can subscribe to multiple publications from multiple publishers.
    • It can send data back to publisher or publish data to other subscribers.
  6. Subscription
    • It is a request by a subscriber to receive a publication.
    • We have two types of subscriptions - push and pull.
  7. Push Subscriptions
    • With this subscription, the publisher is responsible for updating all the changes to the subscriber without the subscriber asking those changes.
    • Push subscriptions are created at the Publisher server
  8. Pull Subscriptions -
    • With this subscription the subscriber initiates the replication instead of the publisher.
    • The subscriptions are created at the Subscriber server.

REPLICATION AGENTS

  • We have discussed that replication process works in the background with the help of jobs.
  • These jobs are also called as agents. These jobs internally uses respective .exe files present in …………….. \110\COM folder.
  • All the agents information is present in Distribution db in the following tables.
    • dbo.MSxxx_agents
    • dbo.MSxxx_history

Snapshot Agent

  • It is an executable file that prepares snapshot files containing schema and data of published tables and db objects.
  • It stores the files in the snapshot folder, and records synchronization jobs in the distribution database.

Distribution Agent

  • It is used with snapshot and transactional replication.
  • It applies the initial snapshot to the Subscriber and moves transactions held in the Distribution db to Subscribers.
  • It runs at either the Distributor for push subscriptions or at the Subscriber for pull subscriptions.

Log Reader Agent

  • It is used with transactional replication, which moves transactions marked for replication from the transaction log on the publisher to the distribution db.
  • Each db has its own Log Reader Agent that runs on the Distributor and connects to the Publisher.

Merge Agent

  • It is used with merge replication.
  • It applies the initial snapshot to the Subscriber and moves incremental data changes that occur.
  • Each merge subscription has its own Merge Agent that connects to both the Publisher and the Subscriber and updates both.
  • It captures changes using triggers.

Queue Reader Agent

  • It is used with transactional replication with the queued updating option.
  • It runs at the Distributor and moves changes made at the Subscriber back to the Publisher.
  • Unlike Distribution Agent and Merge Agent, only one instance of the Queue Reader Agent exists to service all Publishers and publications for a given distribution db.

REPLICATION TYPES

  • Snapshot Replication
  • Transactional Replication
  • Merge Replication

1. Snapshot Replication:

  • The snapshot process is commonly used to provide the initial set of data and database objects for transactional and merge publications.
  • It copies and distributes data and database objects exactly as they appear at the current moment of time.
  • Snapshot replication is used to provide the initial data set for transactional and merge replication.
  • It can also be used when complete refreshes of data are appropriate (BOL).
  • Scenarios
  • When the data is not changing frequently.
  • If we want to replicate small amount of data.
  • To replicate Look-up tables which are not changing frequently.
  • It is acceptable to have copies of data that are out of date with respect to the publisher for a period of time
For example, if a sales organization maintains a product price list and the prices are all updated at the same time once or twice each year, replicating the entire snapshot of data after it has changed is recommended.
Snapshot Replication Architecture
Source: BOL

How it Works?

  • Snapshot Agent establish a connection from distributor to publisher and generates fresh snapshot into snapshot folder by placing locks.
  • Snapshot agent writes copy of the table schema for each article to .sch file.
  • Copies data from published table at the Publisher and writes data to the snapshot folder in the form of .bcp file.
  • Appends rows to the Msrepl_commands and Msrepl_transactions.
  • Releases any locks on published tables.

Click More:Setting up Transactional Replication in SQL Server 2008 R2.

 Configuring Replication

  1. Configuring distributor
  2. Configuring publisher
  3. Creating publication of required type
  4. Creating subscription(s)
Step 1: Configuring distributor and publisher
  1. Take three instances
  2. Go to second instance -> Right click on Replication -> Configure Distribution…
  3. Next -> Select ‘SERVER2’ will act as its own distributor;
  4. Next


  5. Next
  6. Next
  7. Uncheck the check box present at Server2 -> Add
  8. Select instance Server1
  9. Next
  10. Enter strong password. (Automatically one login is created in distributor with the name Distributor_Admin)
  11. Next
  12. Next
  13. Finish

Observations
  1. Go to distributor -> Databases -> Find the new database “Distribution”
  2. Go to Security -> Logins -> Find a new login “Distributor_admin”
  3. Go to Server Objects -> Linked servers -> Find new linked server “repl_distributor”
  4. Right Click on Replication -> Select distributor Properties
  • Transactions stored in distribution database are removed after 72 hrs and agents history is removed after 48 hrs.
  • To view snapshot folder path -> Click on publishers -> click on browse button (…) present to right side of publisher name.
  • Go to SQL Server Agent -> Jobs -> Find 6 new jobs are created automatically.
Step 2: Creating Snapshot Publication
  1. Go to publisher (Server1) -> Replication -> Right Click on Local Publications -> New publication.
  2. Next
  3. Select second option -> Click on Add -> Select Distributor instance (Server2)
  4. Connect ? Next
  5. Enter password of Distributor_admin login which we have mentioned while configuring publisher.
  6. Next
  7. Select required database. For example SSISDb
  8. Next
  9. Select “Snapshot Publication” -> Next
  10. Select required tables -> Next
  11. Next -> Next
  12. Select the check box to create snapshot as follows
  13. Next
  14. Click on security settings
  15. Select as follows
  16. OK
  17. Next
  18. Next -> Next
  19. Enter publication name as follows
  20. Finish

Observations
  1. Go to publisher -> Replication -> Local publications -> Find new publication is created
  2. To check snapshot was created or not -> Right click on the publication (SSISDBSP) -> View Snapshot Agent Status
  3. Go to repldata folder as follows:
  4. Go to sub folders find the snapshot files (.bcp, .sch, idx, .trg)
  5. Go to distributor -> SQL Server Agent -> Jobs -> Find snapshot agent job was created
FAQ: How to display database names which consists of publications?
Ans: Go to publisher -> take new query ->
select name from sys.databases where is_published=1 or is_subscribed=1

Creating Subscription

  1. Go to publisher -> Replication -> Local Publications -> Right Click on SSISDBSP -> New Subscription
  2. Next
  3. Select the publication name: SSISDBSP
  4. Next
  5. Select Push subscriptions

  6. Next
  7. Add Subscriber -> Select third instance (Server1\test) -> Connect
  8. Next
  9. Under Subscription Database if there is no database exists with same name -> Select New database -> Enter Database Name -> OK -> Next
  10. Click on browse button (…) under distribution agent security page.
  11. Select “Run under Agent Service Account” and “By impersonating the process account” options as both distributor and subscriber’s service accounts are same. If the service account of subscriber is different, then create a login in subscriber with sysadmin privileges then mention that login details.
  12. Next
  13. Under Agent Schedule -> Select “Run Continuously”
  14. Under Initialize when select -> Immediately
  15. Next -> Next -> Finish
Observations
  1. Go to subscriber -> SSISDB -> Tables -> Find two tables are created
  2. Go to distributor -> SQL Server Agent -> Find new job is created, related to Distribution Agent

Verifying Replication

  1. Go to publisher perform some changes in any table present in publication
  2. Go to distributor run Snapshot Agent Job
  3. Go to subscriber observe the changes in the respective table
FAQ: How many articles may be there in a snapshot publication?
32767
FAQ: Max columns in a table?
1000
Subscribe to: Posts (Atom)