Setting Up Transactional Replication with SQL Server

• By Baya Dewald
• Mar 26, 2004

Like this article? We recommend
Microsoft SQL Server™ 2000: A Guide to Enhancements and New Features

In my first article in this series, I mentioned that setup is only one of the many steps involved in managing replication. If you have done your homework and planned for replication appropriately, setup is relatively straightforward. Yet, it is very important to get things right the first time. Mistakes made during replication setup are not easy to correct and might require downtime.

Much of replication setup can be accomplished through wizards. However, it is often easier to set up replication by executing replication stored procedures. Indeed, if you have development and quality assurance servers that should be identical to your production environment, you might not want to click through the wizard screens each time you configure replication.

This article covers setting up transactional replication by using wizards as well as by running replication stored procedures. Hope you find it useful.

The Scenario

To make the examples shown in this article a bit more realistic, let's consider a sample problem in which transactional replication could be helpful. Suppose that you are a database administrator managing a server queried by hundreds of concurrent users each day. When you first joined the company, the user base was small, and a single server was sufficient for transaction processing as well as reporting. But imagine that your PUBS database has grown by leaps and bounds, and users who run reports are complaining about the application being very slow. You also notice that the other set of users, who enter data eight hours a day, complain about application screens—often "hanging." You have examined your database design and queries and have done all the tuning you can. Yet data entry and reporting users still hate each other... and you. After consulting your boss, you find that she is willing to buy another server (or two) if that will help, but you better have this problem fixed soon. Congratulations! It's time to learn transactional replication.