High Availability

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 Reply

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