Are you trying to perform MSSQL replication setup?

This guide will help you.

SQL Server replication is a technology for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency and integrity of the data. In most cases, replication is a process of reproducing the data at the desired targets.

MSSQL replication is a very long process. The process can also be confusing for the database owners.

Here at Ibmi Media, as part of our Server management Services, we regularly help our Customers to set up MSSQL replication.

In this context, we shall look into how to set up MSSQL replication for our customers.

More information about replication components?

The major three components for the replication is Publisher, Distributor, Subscriber.

Here, the publisher is the source database and the subscriber is the destination database. Whereas, the distributor acts as a store for replication specific data from source to destination.

A connection is established from the distributor to the publisher using a service agent. It creates a new snapshot and stores it in the snapshot folder.

MSSQL replication setup

Recently we had a customer who requested our support experts to setup MSSQL replication. Let us discuss how we helped him to perform the same.

1. Distribution configuration

Usually, we start from how we configure SQL replication distributor:

i. Open Microsoft SQL management studio. Expand the SQL instance.

ii. Then, right-click on Replication and select Configure distribution.

iii. In the distribution configuration window, click Next.

iv. Then select “SERVERNAME which will act as its own Distributor”. This will make the current SQL instance the distributor.

v. Finally, select the snapshot folder. We select the default folder location shown in the windows and click Next to proceed further.

vi. Now to configure the SQL replication distribution database, we proceed with the default values and click Next. Then select the SQL replication publisher and Distribution database and click Next.

vii. In Wizard action select configure distribution. On the screen to review the setting we verify the details and click Finish to configure the distributor. A success message will be shown.

Possible error with Distribution configuration

If an error pop-up stating that "SQL server agent failed to start automatically". 

To fix, We will simply restart the SQL server agent service manually.

2. Configure snapshot publisher

Now let's discuss how our support experts configure SQL replication publisher.

i. Expand the SQL instance and further expand Replication.

ii. Then Right-click on Local Publication >> New.  Now select the option Publication >> Next.

iii. Now we select the database from the publication database list and click Next.

iv. Then from the multiple publication types, we choose one. Our engineers select transactional publication as it is more flexible.

v. Select the table that needs to be in the publication from the article page.

vi. On the review screen, verify the tables and click next.

vii. Select the option “Create a snapshot immediately”  from the snapshot agent.  Click next and enter the windows user logins.

viii. Then a preview screen will appear. Finally, verify the details and click finish. We will receive a success message.

Possible error with snapshot publisher

If an error pop-up stating “SQL Server could not connect to the distributor using the specified password” Click Ok.

The screen appears and will ask to enter the password again. 

Make sure to use the correct login.

3. Subscriber configuration

Now, we are at the final stage of our setup.

Let's discuss how our support experts configure subscribers.

i. Expand the SQL instance and further expand Replication.

ii. Then, Right-click on Local Subscriptions  >> New Subscriptions >> Next.

iii. Now select the publisher name and select the option next.

iv. Then from the window, select Run all agents at the Distributor >> Next

v. Add SQL Server Subscriber and then select a new database and click Ok.

vi. Then we enter the windows user logins >> Next.

vii. A preview screen will appear. Therefore, verify the details and click finish.

4. Check the configuration

In addition, let us discuss how our Support Experts verify the configuration.

The configuration is verified from MS SQL management studio. 

Right-click on Replication and then click Launch Replication Monitor.

Finally, we verify the status to make sure the replication setup is running.

[Need urgent help with MSSQL replication setup? Contact us now ]