Configuring Transactional Replication -SQL Server 2008



 There are 3 steps involved in setting up Transactional Replication.

1. Configuring the Distribution database.
2.  Creating the Publisher.
3. Creating the Subscriber.

Scenario:

Here I am taking two SQL Server instances ( Roli-w7\rolit & Roli-w7\roliSQL) installed on same server. I will create Distribution database on 'rolit' instance and Publisher on roliSQL instance. A contact table which belongs to Person schema in the Adventureworks Database is replicated to rollsubs database present on same server.

Configuring the Distribution database

Step1. Connect to the Microsoft SQL Server 2008 R2 Management Studio.
Step2. Right Click on the Replication node and Select Configure Distribution.



Step3. A new window appears which holds only information about the wizard. Click Next.

 

 

 

 

 

 

 

 

 Step4. A new window will appear where you can make choice whether you want your distribution database to reside on same server or some other server will host the distributor.

 

 

Note : Distributer 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.

Step5. Next window will appear. 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 screenshot  below.

 

Step6. In below screen, you are asked about location of  the Snapshot folder.

 

Step7. The new screen will display information such as what will be the distribution database name, the location where the data and the log file will reside. Make required changes if you want and click Next.

  Step8 : Click Next.

Note here you get option of adding publisher.

 

Step 9: Choose to configure distribution database.

Step10. Click Finish.

 

Step11.  Snapshot folder is created & Distributor database is configured.

 

After this a new database named distribution gets created. You can confirm this by expanding the System Database node and you shall be able to view the distribution database, please refer below screenshot.

 

 2. Creating the Publisher

Step1: Right Click on Local Publications and select New Publication.

 

Step2: New Publication window will appear. Click Next.

 

 

Step3: Choose the Publication Database and Click Next.

 

 

Step4: Choose ' Trasaction Replication' in Publication type.

 

 

Step5: You have to choose here what you want to publish

 

 

Step6: I have choosen only one table for replication.

 

 

 

Step7: If want to add filter you can add filter here however I did not add any filter in this example. Click next.

 

 

 

Step8: Choose when you want to run snapshot agent.

Step9: Click Security Settings

 

Step10:  Here specify the domain or machine account under which snapshot Agent process will run.

Step11: Click the check box of - Create the publication. and proceed to the next screen.

 

Step12: Give publication name. I have named my publication as RollRep.

 

 

Publication is created and now we will move ahead to create subscriber. 

Creating the Subscriber

Step 1: Open SSMS and go to Replication. Click new Subscriptions.

 

 

Step 2 : A new Subcription wizard will appear click Next.

 

 

Step 3 : This step will allow you to choose the publication for which you want to create subsciptions. You can create one or more subscriptions.

 

 

Step 4: Now choose the type of Replication you want to create Push or Pull.

After choosing the type of subscriptions click next.

 

 

 Step5 : Choose Subscriber and click next.

 

Step 6: Choose connection to Dstributor and Subscriber.

 

 

 

 

Step 7: Specify Distribution agent security and mode of connection to distributor and subscriber.



Step 8: Choose the Synchronization schedule.

 

Step 9: Initialize subscriptions 

Step 10: Choose Create Subscription and click Next.

 

Step 11: New Subcription is successfully created.

 

 To ensure that you replication is working fine keep a close eye on replication monitor.

 

 

 

Replication monitor is used for measuring Latency and Validating Connections for Transactional Replication.Transactional replication provides the tracer token feature, which provides a convenient way to measure latency in transactional replication topologies and to validate the connections between the Publisher, Distributor and Subscribers. A token (a small amount of data) is written to the transaction log of the publication database, marked as though it were a typical replicated transaction, and sent through the system, allowing a calculation of:

• How much time elapses between a transaction being committed at the Publisher and the corresponding command being inserted in the distribution database at the Distributor.
• How much time elapses between a command being inserted in the distribution database and the corresponding transaction being committed at a Subscriber.

From these calculations, you can answer a number of questions, including:

• Which Subscribers take the longest to receive a change from the Publisher?
• Of the Subscribers expected to receive the tracer token, which, if any, have not received it?

Replication Monitor displays performance quality values for transactional replication and merge replication in the Current Average Performance and Current Worst Performance columns for publications and the Performance column for subscriptions. The values are:
Excellent
Good
Fair
Poor
Critical (transactional replication only)