SQL Server Replication : a tutorial for building a simple replication

Introduction

In this blogpost I'll show you how to set up a basic replication in SQL Server (2008 R2). There are multiple sites, blogs and other stuff where you can read the ins and outs of replication. I'll not go into detail about replication.

The basic components

Let's explain some basics. There are a couple of different types of replication possible: transactional, snapshot, and mergereplication. In this blogpost I'll describe the transactional replication. And, there are a couple of keyplayers involved : the distributor, the publisher and the subscriber:

• A distributor. The distributor identifies whether changes has happened on the publisher.
• A publisher. The publisher is the SQL Server instance that publishes the articles.
• A subscriber. SQL Server instance that subscribes to a publication.

There are also other components involved :

• Articles : for each SQL Server object an article needs to be defined.
• Publication : a collection of articles.
• Publication database : a database that contains the articles that needs to be published.
• Distribution database : the distribution database stores metadata and replicated data.
• Subscription : a collection of articles.
• Subscription database : the target database where the articles are stored.

And, there are a couple of agents that take care of the replication:

• Snapshot agent. The snapshot agent creates the snapshot files with the schema and the data of the to be published tables and database objects
• Log reader agent. The log reader Agent monitors the transaction log of every database that is configured for replication. And it copies the data into the distribution database.
• Distribution agent. The distribution agent copies the initial snapshot files of the snapshot folder and the data from the distribution agent into the subscriber database.
• The Queue Reader Agent is used with transactional replication with the queued updating option. 

The tutorial described in this blogpost is executed on one virtual environment. Currently setting up a testenvironment with a Domain controller, a publisher and a distributor in separate Virtual machines. I'll blog about this configuration later.

Setting up the distribution

In this blogpost I'll describe a simple replication setup. Don't bother (yet) about the right AD accounts. Just a plain and simple replication on one database instance (in a virtual environment on my laptop). Okay let's start the tutorial:

1. Start SQL Server Management Studio and connect to an instance.

2. In the Object Explorer, right click on the replication map and click on Configure Distribution.

3. Click on Next.

4. Choose the current instance for setting up the distributor. Don't change anything and press Next.

5. Although it's not recommend to choose Yes here, because of not following security best practices, choose Yes;-). This tutorial is about setting up a simple replication.

6. Now it's time to specify the snapshot folder. The snapshot folder is used for the initial synchronization of the transactional replication. In this tutorial I created the snapshot folder on the same machine. I can imagine that you want to create this snapshot folder on a share on the network.

7. After setting up the snapshot folder, the following window appears. This window specify the distribution database and the location of the database and log files. Click Next.

8. Now we are entering the Publisher window. Here you can specify the Publisher. It grants the server access to the Distributor.

Setting up the publisher

9. Now you have setup the Distributor it's time to setup the Publisher. In SQL Server Management Studio right click on Local Publications, and then New Publication.

10. Press Next.

11. Press Next.

12. Press Next.

13. Press Next.

14. Choose the database that database that should be replicated (in my case AdventureWorksLT). Press Next.

15. Choose transactional replication and press Next.

16. Choose the tables, stored procedures or even fields in the table you want to replicate. Press Next

17. Add filters if you want and press Next.

18. In the following window you can specify when you want to run the snapshot. Choose "Create a snapshot immediately and keep the snapshot available to initialize subscriptions".

19. Use the SQL Server Agent account for the snapshot agent.. Press Next.

20. Press Next..

21. Enter some properties depending on what you want. Press Next.

22. On the Complete the wizard window an overview is given of the settings. Press Next.

Settting up the subscription

23. Now it's time to setup the subscription. The subscription resides mostly on a different machine. In this simple tutorial I'll keep the subscriber on the same machine. Right click in the Object explorer, Replication, Local Subscription and New Subscription and press Next on the opening window of the wizard.

24. Choose the server and the Publisher database. Press Next.

25. Specify whether this is a pull or push subscription.

26. Choose server and database where the subscription database should be. Press Next.

27. Specify the security account for the distributor. Press ... .

28. Choose options like the one in the window below. Press OK.

29. Press Next.

30. Specify the synchronization schedule. Choose Run continuously and press Next.

31. Press Next.

32. Press Next.

33. Press Next.

34. And we are ready to execute. Press Next.

35. Wait and see how the subscriptions are generated.

Looking around

The next thing I did was browsing SQL Server and folders to see what has changed. Below the definitions of the replication process.

The snapshot folder:

The replication is executed by a number of replication agents (jobs) :

• Snapshot agent. performs initial synchronization between the publication database and the subscription database.
• Log reader agent. Reads the transaction log of the Publication database.
• Distribution agent. Applies changes to the subscription database (from the recorded changes in the distribution database).

Below the jobs that controls the replication of articles.

Conclusion

Although it's a simple tutorial, some considerations are shown that can help you when you're implementing replication in a real world scenario. In this blogpost I showed a simple Next Next tutorial for building a replication process.

In future blogpost I'll implement replication on multiple machines and with better security options.

Greetz,
Hennie