Proven SQL Server Architectures for High Availability and Disaster

Download Proven SQL Server Architectures for High Availability and Disaster .PDF

1841675 Downloads

7.44 MB .pdf

It can be very useful to review published reference implementations from SQL Server customers, both to see what technology choices worked for the customers’ requirements, and also to potentially learn from their experiences. Without limiting the rights under copyright, no part of this document 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. The architecture that the travel company deployed is illustrated in Figure 10 below. This white paper is for informational purposes only. When the network link between the principal and mirror servers is not sufficient to synchronously send the transaction log records without leading to workload performance degradation, database mirroring can be configured to send the transaction log records asynchronously. The database volumes on the SAN in the main data center are mirrored to another SAN in a secondary data center, which does not necessarily need to be attached to another failover cluster. aspx?casestudyid=40000034 21 Proven SQL Server Architectures for High Availability and Disaster Recovery Failover Clustering for High Availability Combined with SAN-Based Replication for Disaster Recovery This architecture uses failover clustering to provide local high availability and SAN-based replication to provide disaster recovery. They chose to implement a combination of peer-to-peer replication as well as traditional transactional replication to use the disaster-recovery hardware to process the read-only workload. 17 Proven SQL Server Architectures for High Availability and Disaster Recovery Finally, while SQL Server 2005 provides all the technologies needed to implement a successful high-availability and disaster-recovery architecture, SQL Server 2008 has many enhancements to these technologies, and includes many others that can aid with security, maintainability, and performance The information presented in this whitepaper, and in those to which it links, should provide a basis for anyone tasked with evaluating and choosing SQL Server 2008 technologies, with the goal of protecting and increasing the availability of critical business data. QR Limited migrated their SAP databases from a legacy mainframe onto a SQL Server 2005 and wanted to provide high availability and disaster recovery capabilities for the various SAP 11 Proven SQL Server Architectures for High Availability and Disaster Recovery databases and the one terabyte ERP database, but with the ability to seamlessly protect against loss of a data center without having to perform protracted disaster recovery. Peer-to-peer replication essentially makes a database both a publication and a subscription database, and so local insert, update, and delete activity is permitted in the same database and tables that are receiving updates from other nodes. If database mirroring is used between two data centers, it is recommended to place the witness in a third data center, for the highest availability. Microsoft, <plus, in alphabetical order, all Microsoft trademarks used in your white paper> are trademarks of the Microsoft group of companies. Figure 8: High-availability and disaster-recovery architecture deployed by Progressive. There are a number of variations and configuration options for this architecture depending on the business requirements, including the following: 1. 4 Deployment Example: CareGroup Healthcare System . Deployment Example: Progressive Insurance Progressive Insurance is one of the largest auto insurance companies in the United States with revenues of more than $14 billion. If the servers fail in the main data center, the SQL Server instances are started in the secondary data center in a manner similar to when the servers are collocated and the clients reconnect in the same way as for a failover of a regular failover cluster (and vice-versa). After a database mirroring failover has occurred, the client simply has to reconnect and the connection will automatically be made to the secondary data center. This architecture is typically lower cost than one involving failover clustering, as the principal and mirror servers can be standalone servers with direct-attached storage, rather than each part of a multi-server failover cluster with SAN storage