Stuck with the MSSQL replication setup? We can help you.

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

At Bobcares, we receive a request to set-up MSSQL replication as part of our Server Management Services.

Today, let us discuss how our Support Engineers set-up MSSQL replication for our customers.

What are the replication components and how it works?

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 engineers to setup MSSQL replication. Let us discuss how we helped him to perform the same.

• Distribution configuration

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

1. Open Microsoft SQL management studio. Expand the SQL instance.
2. Then, right-click on Replication and select Configure distribution.
3. In the distribution configuration window, click Next.
4. Then select “SERVERNAME which will act as its own Distributor”. This will make the current SQL instance the distributor.
5. Finally, select the snapshot folder. We select the default folder location shown in the windows and click Next to proceed further.

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.

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

If an error pop-up stating that “SQL server agent failed to start automatically”. We will restart the SQL server agent service manually.

• Configure snapshot publisher

Now let’s discuss how our support engineers configure SQL replication publisher.

1. Expand the SQL instance and further expand Replication
2. Then Right-click on Local Publication >> New.  Now select the option Publication >> Next.
3. Now we select the database from the publication database list and click Next.
4. Then from the multiple publication types, we choose one. Our engineers select transactional publication as it is more flexible.
5. Select the table that needs to be in the publication from the article page.
6. On the review screen, verify the tables and click next.
7. Select the option “Create a snapshot immediately”  from the snapshot agent.  Click next and enter the windows user logins.
8. Then a preview screen will appear. Finally, verify the details and click finish. We will receive a success message.

Possible error

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.

• Subscriber configuration

Moving ahead, we are at the final stage of our setup. Let’s discuss how our support engineers configure subscribers.

1. Expand the SQL instance and further expand Replication
2. Then, Right-click on Local Subscriptions  >> New Subscriptions >> Next.
3. Now select the publisher name and select the option next.
4. Then from the window, select Run all agents at the Distributor >> Next
5. Add SQL Server Subscriber and then select a new database and click Ok.
6. Then we enter the windows user logins >> Next.
7. A preview screen will appear. Therefore, verify the details and click finish.

• Check the configuration

In addition, let us discuss how our Support Techs 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 help with MSSQL replication setup? Contact us now]

Conclusion

In short, The MSSQL replication is a bit long procedure to configure replication. We have discussed how our Support Engineers setup MSSQL replication and fix related errors.