Replication In SQL Server: Part Three

Overview:

Earlier, in previous parts we saw two types of replication in snapshot.

Here in part three we will be covering transactional replication.

Types of SQL Server Replication:
  • Snapshot Replication. (Explained in Part Two)
  • Transactional Replication.(Part Three)
  • Merge Replication.
  • Peer-to-Peer Replication.

Let’s see each type by an example so we will get a clear gist what each and every replication is doing, starting with Transactional Replication.

Transactional Replication
  • Let’s start with transactional replication. The term transactional replication copies data from source destination to target destination. Here the logs are transferred from source database to destination database. I.e. publisher to various subscribers.

  • If a change is made to the source database, that change can be synched to the target database immediately, or the synchronization can be scheduled.

  • In transactional replication, log reader scans the publication database and checks for each committed rows, if any changes in replicated areas the changes are done in distributed database respectively. The distributor agent then replicates those changes to the subscriber . (source: Microsoft TechNet).

    custom
Transactional Replication Configuration

As you know, replication architecture has three components: Publisher, Distributor and Subscriber that we need to configure.
  • Scenario

    In a retail environment, management wants to know how many items are sold hourly. To avoid impacting the Sales database, they process reports and statistics on another server.

  • Architecture
    Architecture

Let’s Start:

Logically it is advisable to take three instances. Here I am explaining transactional replication local server (SQL).

NOTE

To Configure and to check on your local PC you need to install SQL Server (2008, 2008R2, 2012, 2014 and so on). Express Edition will have less features in local PC.

  • Open SQL Server Management Studio

    sql

  • Click on Replication,

    Replication  

  • Configure Distribution

    config

  • Click on Configure Distribution -> Next ->

    Configure

    Select the Server or PC Hostname it will act as its own distributor and click next.

  • Path Selection

    path

    Next

  • Configure Database Name (Distributor)

    Configure

    In this Case I had kept by default databasename as distributor .Click Next

  • Configuring Instances

    Instances

    If you are running on multiple instances you can do it by clicking on ADD.

    Here, in this case using everything in local Server. Hence the checkbox is checked. Click Next and check Configure distribution Option -> Finish

  • Executing

    Executing

  • Click on Replication ->Distributor Properties

    Properties

    page

    Now here you will see transaction Retention are removed after 72 hours and agent history is removed after 48 hours. On Click of tab you will able to see the complete log path respectively .

    path

  • Go to Jobs

    Jobs

    Here you will see the highlighted section you will see new jobs got created.

  • Creating Snapshot Publisher.

    Click on Replication ->Local Publication ->New publication

    New

    It prompts you to select database which you want to make as a publisher.

    Here in this scenario, selecting bloodbankmanagement database.

    Click Next

    Next

    In this case we need to select Transactional Replication->Next,

    Next

    Select tables, stored procedures, and user defined functions and on...

    In this case, selected tables ->Next ->

  • Creating Snapshot and scheduler

    scheduler

    tab

    Click on create Snapshot immediately ->Click security Settings.

    Settings

    Use SQL server Authentication.>OK -> Next ->Create publication ->Give publication name as “”TEST” -> Finish

    finish

    test

    You will see TEST publisher in Local Publications.

    TEST

    You will see job as TEST appearing in SQL Server Jobs.

    Creating Subscription:

    Creating

    Click Next,

    Next

    Click Next ->

    Next

    Select subscriber database will be selection simple blog database->Next,

    Next

    Configure Security setting for subscriber,

    Configure

    Click Ok ->Next,

    Next

    Now, it’s asking agent to run continuously, run on demand or schedule it. This depends on your business requirements how you want your data. Here am continuing with default selection run continuously. ->click Next ->Run immediately ->Create the subscription -> Finish,

    finish

    finish

    See the Local Subscriber Simple Blog Subscriber appeared.

    Subscriber

    Job created in SQL server Jobs ->Right Click ->Start Job as Step.



    After Successful completion of job. You will able to see tables of Blood bank management database which we selected appeared in Simple blog.

    blog

Conclusion

Transactional Replication is suitable for small databases where replication is needed. For huge database (TB) on server make sure you have plenty of resources available for replication (Transactional) running efficiently.