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,

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 

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

By Deepak

Microsoft Most Valuable Professional (MVP) award-winning Database Designer with 5 years of experience in designing, administering, developing & performance tuning relational databases and data warehouse. I possess strong leadership skills experience by serving as the Chapter Leader of Professional Association for SQL Server (PASS) which has around 50 members. I am passionate about solving business problems using my technical skills and hence I am interested in the field of Technology Consulting in Information Management.

Leave a Reply

Leave a comment

Your email address will not be published. Required fields are marked *