SQL Server Replication Providing High Availability using Database MirroringWord下载.docx

1、 Diana SteinmetzPublished: August2008Applies To: SQL Server 2008, SQL Server 2005 Summary: This white paper describes how to use database mirroring to increase the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored environment, the e

2、ffect of mirroring partnership state changes, and the effect of mirroring failovers on replication. In addition, it describes how to use LSN-based initialization to recover from the failover of a mirrored subscriber database.Although brief overviews are given of both replication and database mirrori

3、ng, it is easier to understand this white paper if the reader has some experience with one or both of these technologies, and has at least a rudimentary knowledge of database concepts such as transactions.CopyrightThe information contained in this document represents the current view of Microsoft Co

4、rporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publicat

5、ion.This White Paper is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS DOCUMENT.Complying with all applicable copyright laws is the responsibility of the user. Without limiting the rights under copyright, no part of this d

6、ocument may be reproduced, stored in or introduced into a retrieval system, or transmitted in any form or by any means （electronic, mechanical, photocopying, recording, or otherwise）, or for any purpose, without the express written permission of Microsoft Corporation. Microsoft may have patents, pat

7、ent applications, trademarks, copyrights, or other intellectual property rights covering subject matter in this document. Except as expressly provided in any written license agreement from Microsoft, the furnishing of this document does not give you any license to these patents, trademarks, copyrigh

8、ts, or other intellectual property.Unless otherwise noted, the example companies, organizations, products, domain names, e-mail addresses, logos, people, places and events depicted herein are fictitious, and no association with any real company, organization, product, domain name, email address, log

9、o, person, place or event is intended or should be inferred. 2008 Microsoft Corporation. All rights reserved.Microsoft, SQL Server, and the Server Identity Logo are either registered trademarks or trademarks of Microsoft Corporation in the United States and/or other countries.The names of actual com

10、panies and products mentioned herein may be the trademarks of their respective owners.Table of ContentsIntroduction 1Technologies 2Transactional Replication Architecture 2Database Mirroring Architecture 3Deploying Database Mirroring and Replication Together 5Mirroring the Publication Database 6Confi

11、guring Replication with a Mirrored Publication Database 6Effect of the Mirroring State on the Replication Log Reader 7Changing the Replication Log Reader Behavior by Using Trace Flag 1448 8Effect of a Mirroring Failover on the Replication Log Reader 9Log Reader Agent Behavior if the Mirroring Partne

12、rship is Broken 10Mirroring the Subscription Database 11Configuring the Distribution Retention Period 12Manual Synchronization Types for Subscriptions 12How Does Initializing from an LSN Work? 13Recovering the Replication Stream Following a Mirroring Failover 15Conclusion 18IntroductionTransactional

13、 replication is the mechanism that Microsoft SQLServer provides to publish incremental data and schema changes to subscribers. The changes are published （the replication stream） in the order in which they occur, and typically there is low latency between the time the change is made on the Publisher

14、and the time the change takes effect on the Subscriber. This enables a number of scenarios, such as scaling out a query workload or propagating data from a central office to remote offices and vice-versa. This form of replication always uses a hierarchical hub and spoke topology.The addition of peer

15、-to-peer transactional replication in SQL Server2005 simplifies the implementation of a bi-directional transactional replication topology, where the replication stream flows both ways. In this topology, any participating node may read or update the data. Properly partitioned modifications are propag

16、ated between all nodes in a full mesh topology （as shown in Figure1）, allowing the data to be highly available in the event that one server is unavailable. This feature has been further improved in SQL Server2008 with conflict detection and online changes for peer-to-peer topologies.Figure 1: Full m

17、esh topology for peer-to-peer replication with three and four nodesTransactional replication topologies can be made more resilient to server failures, and hence more highly available, by adding redundant copies of the various databases involved. This is especially important for hub and spoke topolog

18、ies. Care must be taken, however, because replication is reliant on the server names of the servers in the topology, so any failover to another server can result in the replication stream being broken.Various mechanisms in SQLServer provide database-level redundancy, such as backup/restore, log ship

19、ping, and database mirroring （in SQL Server2005 and later）. Database mirroring is the only mechanism that provides a real-time, exact copy of the protected database with the guarantee of zero data loss （when the mirror is synchronized）.This white paper describes how to use database mirroring to incr

20、ease the availability of the replication stream in a transactional environment. It covers setting up replication in a mirrored environment, the effect of mirroring partnership state changes, and the effect that mirroring failovers have on replication. In addition, it describes how to use LSN-based i

21、nitialization to recover from the failover of a mirrored subscriber database.TechnologiesTransactional Replication ArchitectureTransactional replication and peer-to-peer replication use the same architecture to move changes between the servers in a replication topology. The following illustration is

22、 an overview of the components involved in transactional replication.Figure 2: Transactional replication architecture overviewA minimum of three server roles are required for transactional replication: Publisher, hosting the publication database Distributor, hosting the distribution database Subscri

23、ber, hosting the subscription databaseDepending on the complexity of the replication topology, there may be multiple Subscriber servers or, in the case of peer-to-peer replication, multiple peer servers with the replication stream flowing in both directions between the peers. Furthermore, the roles

24、of the various replication servers can be played by one server or by individual servers （the more common case）, and it is possible for a server to play any combination of roles. Regardless, the various servers and databases must be protected to ensure that the replication stream is highly available.

25、Transactional replication relies on various agents to perform the tasks associated with tracking changes and distributing data. These agents are: Snapshot Agent, which runs at the Distributor. This agent prepares schema and initial data files of published tables and other objects, stores the snapsho

26、t files, and records information about synchronization in the distribution database. Log Reader Agent, which runs at the Distributor. This agent connects to the Publisher and moves transactions marked for replication from the transaction log of the publication database to the distribution database.

27、Distribution Agent, which runs at the Distributor for push subscriptions, and at the Subscriber for pull subscriptions. This agent applies the （optional） initial snapshot to the Subscribers and moves transactions held in the distribution database to Subscribers Queue Reader Agent, which runs at the

28、Distributor. This agent is only used for transactional replication with updateable subscriptions and moves changes made on the Subscribers back to the Publisher.It should be mentioned that there is also a Merge Agent, but it is used only for merge replication, which is not covered in this paper.This

29、 white paper focus mainly on the Log Reader Agent and the Distribution Agent.For more detailed information on SQL Server Replication, see the following SQLServer Replication topics in SQLServer Books Online: for SQL Server 2008 for SQL Server 2005Database Mirroring ArchitectureDatabase mirroring wor

30、ks at the database level and provides a single copy of the mirrored database that must reside on a different server instance, usually on a separate physical server in a different location. One server instance serves the database to clients （the principal server）. The other instance acts as a hot or warm standby server （the mirror server）, depending on the configuration of the database mirroring session and the mirroring state of the mirrored databases. The two servers are said to be partners in the mirroring session.When the mirrored database is synchron