Replication means replicating production data from primary server to secondary server with different locations so that user can read data without any issue. Secondary server is basically used as reporting server to avoid the burden on the production server.
In replication, we are going to use following terminology publication, distribution and subscription
Publication: acts as main source of information which publishes information to distributor.
Distributor: Helps to replicate data from Publication to Subscriber.
Subscription: Here subscriber pulls information from distributor.
* Snapshot replication
* Transaction Replication
* Transaction replication with updatable subscription
* Merge Replication
Agents involve in the process of replication
Log Reader Agent – Reads data from publisher database and updates in distribution database.
Snapshot Agent – It creates copy of the data to initialize the secondary database.
Distributor Agent – It manage the data flow from publisher to distributor and distributor to subscriber.
In this article, we will discuss about snapshot replication which creates static view to production data and transmitted over network to other location. This can be used as reporting server, normally runs on end of day to replicate data from production server to secondary server (Reporting Server).
Steps to create Snapshot Replication in SQL Server 2005
1. Connect to SSMS ? type server name ? User name and password to connect server.
2. Click on Replication folder we will find the two folder of local publisher and local subscriber.
3. Right click on Publication folder, it will display the replication wizard click on next 4. Select database for replication and click on next.
5. It will display list of replication types available in SQL Server 2005, Select Snapshot Replication and click on next to proceed.
6. List of tables for snapshot replication, click on tables to participate and click on next.
7. In this window, you can specific the query to filter the data from tables. Now click on next
8. Options are available to create snapshot replication immediately and same can be schedule and Click on next.
9. Click on Security Setting ? Use Domain ID and password to access the server ? use default setting for the publisher ? Give the name of the Agent ? Click on OK
10. Now Click on Finish to end the replication wizard.
11. We can see the Snapshot replication is created under the local publication and replication folder.
Now create subscriber
1. In replication folder, right click on local subscription ? new subscription
2. Select publisher server, it will show list of available publisher database. Click on next
3. Select the distribution location whether it should create on publisher end or subscriber end. click on next
4. Select subscriber database and click on next.
5. Give the login and password to run agent. Click on next.
6. Schedule the agent and click on next.
7. Select from the list whether you have to initialize immediately or at first synchronization. Click on next.
8. Click on next and finish the setup. You will see the node under local subscription folder.
To test the snapshot replication Run the snapshot agent and check at the subscriber database whether changes are reflected on that database or not. You can use this as a reporting server for the other business user.