Setting up Transactional Replication in SQL Server 2008 R2.

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.

    

Continues…

Pages: 1 2 3




Array

No comments yet... Be the first to leave a reply!