Steps for table replication:

Steps to setup Publication (Source):

1.       Open SSMS and connect to the instance.

2.       From the object explorer, go to Replication.

3.       Right click on Replication and select New -> Publication

4.       Publication wizard will open. Click next and select the database where the table(s) resides which needs to be replicated and click next.

5.       Select the type of publication which will satisfy the requirement and click next.

6.       You will be presented with the tables in the DB. Select the required Table(s) and click next.

7.       You will be presented with filter options. Select the required filters if you don’t want all the data to be replicated. You may skip if you don’t want to filter the data.

8.       In the next step you can specify how often to replicate. You can schedule to run daily at certain times or you can replicate as soon as new data enters the table.

9.       In the next steps you have to specify the security settings for SQL AGENT. Follow this carefully.

10.   At the end you will be provided with options to create the publication and generate script for publication.

11.   Select both so that you may reuse the script for later deployment to other environments.

Steps to setup Subscription (Target)

1.       Once the publication is created, the selected tables are ready for replication.

2.       Right click on Replication and select New ->Subscription. Wizard opens. Click Next.

3.       You will be presented with the DB where the source tables are (Publisher). Select the DB and click next.

4.       You will be presented with two options. Select the appropriate option according to your needs and click next.

5.       In this step you will have to select the target Instance and the target DB and click next.

6.       Select the security options for the SQL AGENT and click next.

7.       Specify how often the job should run and click next.

8.       Specify how the initialization would be and click next.

9.       Click both the options and click next. You will be prompted to save the file. Specify save location and click finish.

10.   Table is automatically created in the target DB.

You have successfully replicated the table. For a test, add new data in the publisher table and do a select on the Subscription table. Depending on the type of snapshot and how often the SQL AGENT job runs, the data will be replicated. 

Table replication in SQL Server 2008/R2

Replication comes in handy when you need other environments to be in sync with your production environment. Generally, Database replication is often used. But, what if, if you need only some tables in a particularDB in your dev environment needs to be synced with Prod environment? The answer is just replicate the required tables. You can replicate tables from one DB to another DB in the same Server/instance or you can from One server to another server. Once the tables are replicated, new field [msrepl_tran_version] will be added to the end of the table. You may hide this to external users by creating a view. In replication terminology, we call Publisher and Subscriber. Publisher is the mainDB that has the tables. Subscriber is the target. Once you subscribe to a publisher for a particular table(s), you will see them in the Subscriber (the target DB). You can also specify the replication strategy whether the replication is transactional/snapshot etc...