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:

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

 

Step 4: Click the "Next" button.

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

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:

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:

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:

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

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

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:

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.

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.

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

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.

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

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

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

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

Step 11: Select the security setting.

 

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.

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

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

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

 

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

 

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.

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

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

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

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.

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.

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

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

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

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

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

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

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

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

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.

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

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.

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.