Thomas Casey
Microsoft Corporation
August 1997
The use of replication is becoming more prevalent in business applications. The need for data warehousing and improved Online Transaction Processing (OLTP) performance through the offloading of data for decision support to secondary servers is growing rapidly. Additionally, leveraging multiple SQL Servers improves performance, data availability, and local site autonomy through distributed processing.
Microsoft SQL Server 6.5 replication provides high volume, continuous replication of data while maintaining transactional consistency. The scalability of this solution allows application developers and database administrators to deploy replication for a variety of application architectures.
Several factors unique to database applications that use replication must be considered during the design phase of such projects. Several common replication scenarios are presented in this paper. While you can add replication to an existing database application, there is no substitute for incorporating the constraints and leveraging the power of replication in the initial design phases of a database project. Doing so improves the flexibility of the solution, reduces deployment time, and maximizes scalability.
You can administer replication through SQL Enterprise Manager, an easy-to-use graphical interface. However, the system components of replication are also exposed through SQL-DMO (Distributed Management Objects) and system stored procedures. Developers of widely deployed applications often rely upon SQL scripts or DMO for administering and monitoring replication in a production environment. For these users, a more intimate knowledge of the replication architecture is often helpful.
This paper provides an overview of the Microsoft SQL Server 6.5 replication architecture and covers many of the factors that contribute to a database and application design suitable for replication. It is intended to supplement the SQL Server 6.5 documentation by providing details about the structure and interaction of the system components used to implement replication. Additionally, it provides a starting point for application design and deployment considerations, including integrated security requirements and troubleshooting suggestions.
While this paper applies to some aspects of the SQL Server 6.0 implementation of replication, it specifically targets SQL Server 6.5 replication.