Transactional Replication

This article teaches you how to set up transactional replication using wizards and scripts. In most cases, youll set up the initial publication and subscribers using wizards; however, if you need to apply the same publication in multiple environments, youll appreciate the option of applying the scripts as opposed to going through wizards time and again.

I have used 3 instances of SQL server of which two are SQL 2005 and other one is SQL Express for configuring transactional replication namely Sansu, SansuTest, SansuSQLExpress. All the 3 instances are SQL Server 2005 Enterprise edition with Service Pack2.

There are 3 steps involves in this namely,

• Configuring the Distributor
• Configuring the Publisher
• Configuring the one or more Subscribers

Testing Replication

Now that we’ve configured replication, we can run a few SQL statements to test it. Ill execute the following UPDATE statement on the publisher:

UPDATE Dimgeography
SET City='Chennai'
WHERE GeographyKey=4

SQL Server informs me that this query affected one row. Next, I switch the database context to the distribution database and run the stored procedure sp_browsereplcmds. SQL Server shows the following command being delivered:

{CALL [sp_MSupd_dboDimGeography] (,N'Chennai',,,,,,,,,4,0x0200)}

Finally, I query the subscribing database to see whether the changes made on the publisher were replicated to the subscriber:

Select City from Dimgeography WHERE GeographyKey=4 

Results:

  City  ----------  Chennai