SQL Server 2005 - 2008 – Snapshot Replication

In this blog I will show you the steps on how to replicate your tables from one database to the other database that might be on the same server or a different server. The same steps can be followed for both 2005 as well as 2008 SQL servers.

There are 2 databases that I will be using in this replication:

· AdventureWorks (Source Database)

· Books (Destination Database)

Setting Up the Publisher and Distributor:

Go to Replications section of the source server and right click on new publication:

Click Next:

You can specify the folder where the snapshot files should be placed:

Select the source database name:

Select the replication type: Snapshot replication in this case.

In my other blogs I will show how to create other types of replications :

Select the tables that you want to replicate:

Click on article properties to set the properties of the destination articles like the schema name for example. If you want the schema name of the destination table to be different from the source then you can specify it here:

Click Next:

You can specify the filter properties by clicking add:

Click Next:

Specify the security setting for the SQL server snapshot agent under what security context it should run:

You can specify the windows account or the SQL server agent service account.

Specify a publication name:

The sql server jobs are automatically created for these operations that can be used to create a snapshot in future which you can schedule also.

The snapshot files are created in the folder that you had mentioned earlier:

Setting Up the Subscriber:

Right click on the local subscriptions in the replication pane of the destination server:

Select the publisher that you had created earlier:

Select the destination database where the replicated tables are to be stored:

Specify the security context as before as to how the agent has to run:

Refresh the database to see the replicated tables:

The subscription jobs are also created automatically:

Source Table:

Destination table:

Schema name is different from the source if you had noticed as we had specified its property while setting up the publisher: