Learn Transactional Replication SQL Server 2008

In multinational organizations where everything is based on huge databases, there is always a chance of hardware failure. When that happens we need a backup server to which we can shift the data flow in and out. Transactional replication is the process that will update both servers simultaneously.

  1. Publisher
  2. Subscriber
  3. Distributor Database

Note: Dear readers, please ensure that you have installed the Microsoft Server operating system with SQL Server 2008 R2.

Configuring the Distribution Database


Step 1: Connect to the Microsoft SQL Server 2008 R2 Management Studio.

Step 2: Right-click on the "Replication" node and select "Configure Distribution" as shown in the screen capture below:

1.gif

Step 3: It will open a new window on the screen as shown in the screen capture below:

2.gif 

Step 4: Click the "Next" button.

3.gif

Step 5: In this phase we have selected the first option as shown in the picture.

4.gif

Step 6: Now we will select the "Yes" option and click the "Next" button.

Step 7: A new window appears on the screen as shown in the screen capture below:

5.gif

As you can see in the preceding screen capture, you are asked where the Snapshot folder should reside on the Server. Let us first understand what the Snapshot folder exactly is. 

Step 8: A new window appears as shown in the screen capture below:

6.gif

As you can see in the preceding 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. 

Step 9: A new window appears as shown in the screen capture below:

7.gif

Step 10: Click on the "Next>" button as shown in the screen capture below:

8.gif

Step 11: Click on the "Finish" button as shown in the screen capture below:

9.gif

Step 12: Once done, a new database named "Distribution" gets created. In order to confirm it, just expand the System Database node and you will be able to view the distribution database, please refer to the screen capture below:

10.gif

Publisher

Step 1: Dear reader, first of all we need to create to two databases, one on the main server and the other is on the backup server. In this example we will create two databases.

  • Main database
  • Secondary database

Both of these databases must have the same table structures.

Step 1: This exercise must be done on the Main Server.

11.gif

Step 2: Create the table on the main server.

CREATE TABLE [dbo].[Client_Profile](
      [CPR_Client_Id] [varchar](16) NOT NULL,
      [CPR_Client_Name] [varchar](100) NULL,
       CONSTRAINT [PK_Client_Profile] PRIMARY KEY CLUSTERED
(
      [CPR_Client_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY] 

Step 3: This exercise must be done on the Backup Server.

12.gif

Step 4: Create the following table on the backup server.

CREATE TABLE [dbo].[Client_Profile](
      [CPR_Client_Id] [varchar](16) NOT NULL,
      [CPR_Client_Name] [varchar](100) NULL,
       CONSTRAINT [PK_Client_Profile] PRIMARY KEY CLUSTERED
(
      [CPR_Client_Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 80) ON [PRIMARY]
) ON [PRIMARY]

Step 5: Click on new publication

13.gif

Step 6: Dear reader, in this step we will select the database to use for the replication purposes. So we have selected the Main database.

14.gif

Step 7: Select "Transactional publication" and click on the "Next" button.

15.gif

Step 8: Select the Client_Profile as shown in the diagram.

16.gif

Step 9: Select the "Add" button as shown in the diagram.

17.gif

Step 10: Select the "Create a Snapshot" option and click the "Next" button.

18.gif

Step 11: Select the security setting.

19.gif 

Step 12: Select "Run" under the SQL Server Agent service as shown in the picture and connect to the publisher. In the second section we need to provide the login details on the backup server.

20.gif

Step 13: In this step we will select the security settings button.

21.gif

Step 14: In this step we will select the security settings button.

22.gif

Step 15: In this step we will provide the publication name.

23.gif 

Step 16: Now click on the "Finish" button.

24.gif 

Step 17: Now click on finish button.

Subscriptions

Use the following to create the subscriptions.

Step 1: In this section we will create the new subscription.

25.gif

Step 2: In this section we will create the new subscription.

26.gif

Step 3: In this section we will create the new subscription.

27.gif

Step 4: In this step we will select the "Run all agents at the distribution" option as shown in the picture.

28.gif

Step 5: This is a very important step. In the step we will click the "Add SQL Server Subscriber" option as shown in the picture.

29.gif

Step 6: This is a very important step. In this step we need to provide the backup server name and the user name and password as shown in the picture.

30.gif

Step 7: Now we need to select the secondary database from the list of backup servers. Then click on the "Next" button.

31.gif

Step 8: Now click on the button under the "Connection to server" option as shown in the picture.

32.gif

Step 9: Now select the option as shown in the picture and provide the backup server user name and password.

33.gif

Step 10: Click the "Next" button to proceed further. It is currently showing your user login details.

34.gif

Step 11: Select the "Run Continuously" option from the drop down list.

35.gif

Step 12: Select the "Immediately" option from the drop down list and click on the "Next" button.

36.gif

Step 13: Simply click on the "Next" button.

37.gif

Step 14: Finally it will show you the final details of the subscription.

38.gif

Dear readers, after completing all these steps, now it's time to ensure that the replication was done properly. To check this we need to use the following procedure.

Step 1: Select the client_pub from the replication option and select the view snapshot agent status.

39.gif

Step 2: Now it will show the status of the subscription, whether it's working properly.

40.gif

Step 3: Now select the client profile table in the Main server database and open it in the edit option. Then enter the data in the table as shown in the diagram.

41.gif
42.gif


Step 3: Now select the client profile table in the backup server database and open the table. You will find the same record here in the backup server that we had entered in the main server.

43.gif