Pankaj Kulkarni ... Its Me: April 2010

Here are the some Key Concept about SQL Server Replication what I learn in last few dez...

Definition of Replication :

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency.

Key Roles in SQL Server Replication :

i. Publisher

ii. Distributor

iii. Subscriber

Publisher :

Publisher is the sever/database that sends the data to another server/database. In simple words, is the source of data.

Distributor :

Distributor is the server that manages the flow of data through the replication system. It contains the Distribution Database also.

Subscriber :

Subscriber is the server/database that receives the data from source i.e. from Publisher.

Types of Replication :

1. Snapshot Replication

2. Transactional Replication

3. Merge Replication

Now we will go in details with each type of replication.

Snapshot Replication :

Snapshot Replication is suited for replicating the data that changes infrequently and the size of replicated data is not very large. Data size must be consider first. With this type of Replication, all replicated data (generally called as Replica) will be copied from Publisher database to the Subscriber database on a periodic basis. It does not monitor to the updates of data on publisher database.

Transactional Replication :

Transactional Replication is typically used when data changes frequently or the size of replicated data is high. With the Transactional Replication, SQL server captures all changes that were made in Article (tables) and stores Insert, Update, Delete statements in the distribution database. Then the changes are send to Subscriber from the Distribution database and applied in the same order. This type of replication gives the high throughput, scalability as well as availability.

Merge Replication :

Merge replication is somehow difficult. It makes possible autonomous changes to replicate data on Publisher and on the Subscriber. Merge Replication is primarily designed for Mobile Applications or distributed server applications that have possible data conflicts.

Replication Agents :

Replication Agents are manifested through the SQL Server Agent Jobs, but they are simply executable files.
We will concentrate on the agents which Transactional replication use. Following are the snapshot agents used for Transactional Replication.
1. Snapshot Agent
2. Log Reader Agent
3. Distribution Agents

Snapshot Agent :

Snapshot agent is the replication agent that creates the snapshot files, stores the snapshot on Distributor and keep the track of information about status in the Distribution database. This agent can be administrated by using SQL Server Enterprise Manager.

Log Reader Agent :

The Log Reader Agent is responsible for moving transactions marked for replication from the Transaction Log on Publisher to the Distribution database.

Distribution Agent :

This agent is responsible for moving Snapshot Jobs from teh Distribution database to the Subscribers and moves all the transactions waiting to be distributed to subscriber.

Some Necessary Conditions :

Only members of the Sysadmin server role can setup and configure replication
SQLServerAgent Service (MSSQLServer Service) need to be start first.
Should allocate the sufficient disk space in snapshot folder.
Should allocate the sufficient disk space for Distribution database.

Example : Transactional Replication

Following is process to setup and configure the Transactional Replication.

1. Register new remote server in which will be replicated.

2. Enter the name of remote server and specify Remote Login Mapping.

3. You will receive the following, if all is correct.

4. Select Tools Menu -->; Replication -->; Configure Publishing and Subscribers

5. This will launch the Configure Publishing and Distribution Wizard

6. We have to create the distributor first. Select the Next button to create the Distributor.

7. Now, you can choose the default settings or set the distribution database name and location, enable other Publishers, or set another settings. Click the Next button.

8. Click the Finish button.

9. Now, SQL Server creates the distribution database, enables publishing, and setting up the distributor

10. Because we installed CHIGRIK_A_U as Distributor, the Replication monitor has been added to the console tree on the CHIGRIK_A_U server.

11. Now, we are ready to start creating publications and articles. Select Tools -->; Replication --> Create and Manage Publications

12. You will see Create and Manage Publications dialog box:

13. Choose pubs database and click the Create Publication button.

14. This will launch the Create Publication wizard.

15. Select Transactional publication and click Next button.

16. You can allow immediate-updating subscriptions on this step. If you choose this option then all changes will be applied at the Subscriber and Publisher simultaneously.

17. Choose Subscriber types on this step. If all subscribers are Microsoft SQL Server subscribers, choose the first option.

18. Select tables or stored procedures to publish and click Next button.

19. Choose publication name and description on this step.

20. You can define data filters or set another options on this step.

21. Click Finish button to create the publication.

22. If publication was created successfully, you will receive the below message.

23. Create new push subscription now.

24. Click Next button.

25. Choose subscribers. Click Next button.

26. Choose destination database. It's pubs database in this example.

27. If you want to have continuous replication that will send the data as soon as possible, you can choose Continuously option. Otherwise, choose schedule option. Select the Continuously option and click the Next button.