SQL Server 2005 – 2008 Transactional Replication

Transactional replication is the method to replicate the data from one database table to another database table immediately which might be on the same server or some other server. Initially a snapshot is applied from the source database to the destination database and then all the transactions on the source database tables are applied immediately to the destination database tables.

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

· AdventureWorks (Source Database)

· Books (Destination Database)

Setting Up the Publisher and Distributor:

Right click on the local publication under the replication section in the source server and select New Publication:

Select the source database:

Select the replication type as Transactional Replication. I have shown how to perform snaphot replication in my previous post: http://masoodhashim.blogspot.com/2011/03/sql-server-2005-2008-snapshot_21.html

Select the database article tables which you want to replicate:

Setup the security context under which the transactional replication agent should run:

Click Next to create the initial snapshot of the source tables which will be applied for the first time to the destination database tables when the subscription is setup:

Click finish:

The job for the publisher is automatically created:

Setting Up the Subscriber:

Right click on the local subscription under the replication section in the destination server and select New Subscriptions:

Select the publisher that we had created earlier:

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

Define the security context under which the replication agent should run in the destination server:

Select immediately to apply the snapshot to the destination database that was created earlier while setting up the publisher:

Click on finish to subscribe to the publication that was created earlier:

The subscription jobs are created automatically under the SQL server agent:

Publishers source table:

Initial snapshot created at the subscription database:

To check if the transactional replication is working:

Update a row at the publisher database:

use AdventureWorks

update Person.Address

set AddressLine2 = 'This is my new address'

where AddressID = 1

select * from Person.Address

where AddressID = 1

The above transaction is replicated at the destination database (Books) immediately:

use Books

select * from Person.Address

where AddressID = 1

Replication Monitor

Right click on the replication section and select “Launch Replication Monitor”:

Replication Monitor shows you all the details and data flow that is occurring between the publisher and the subscriber:

You can also check the latency rate in this window:

Transactional replication is very bandwidth intensive as all the frequent transactions at the publisher keeps applying to the subscriber immediately.

You can also opt for option to display a warning if the latency exceeds the threshold:

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: