Transcription
Business Continuity Solutions on Dell/EMC StorageA White Paper Series for Microsoft SQL ServerAbstractThis white paper is one in a series of Business Continuity papers that describe the various options from Dell and Dell’s premier partners such as Microsoft and EMC on how to achievemaximum data availability, protection and integrity for Microsoft SQL Server 2005 databases.April 2008THIS WHITE PAPER IS FOR INFORMATIONAL PURPOSES ONLY, AND MAY CONTAINTYPOGRAPHICAL ERRORS AND TECHNICAL INACCURACIES. THE CONTENT ISPROVIDED AS IS, WITHOUT EXPRESS OR IMPLIED WARRANTIES OF ANY KIND.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage1
Microsoft and SQL Server are registered trademarks of Microsoft Corporation. Other trademarksand trade names may be used in this document to refer to either the entities claiming the marksand names or their products. Dell disclaims proprietary interest in the marks and names of others. Copyright 2008 Dell Inc. All rights reserved. Reproduction in any manner whatsoever withoutthe express written permission of Dell Inc. is strictly forbidden. For more information, contact Dell.Information in this document is subject to change without notice.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage2
Table of ContentsINTRODUCTION . 4PURPOSE AND SCOPE . 4OVERVIEW. 4SQL SERVER LOCAL REPLICATION AND RECOVERY . 5SNAPVIEW:. 5SQL SERVER REMOTE REPLICATION AND RECOVERY . 7SAN COPY . 7MIRRORVIEW . 9CONCLUSION .12GLOSSARY: .13REFERENCES: .14Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage3
IntroductionMost of today’s business applications are data-centric, requiring fast and reliable access tointelligent information architectures that can often be provided by a high-performance relationaldatabase system. Microsoft SQL Server is one among the relational database systems thatprovides such a back-end data store for mission-critical, line-of-business applications. MicrosoftSQL Server 2005 offers significant architectural enhancements in performance, scalability,availability, and security.It is disruptive and expensive when customers, employees, partners, and other stakeholders are adversely affected by database outages. DELL / EMC along with Microsoft has extended itsreach to increase productivity and keep information close to hand, flexible enough to meet yourorganization’s administrative model.This technical report delivers an overview of how to achieve Business Continuity for MicrosoftSQL Server 2005. In this series of white papers we will explore the options and techniquesavailable at the database, hardware and storage layers as well as Dell partner software solutions.In addition, the Dell Engineering team will be providing deep-dive into each of these technologiesproviding use cases, best practices, manageability, and data protection and how they fit into theBusiness Continuity model.Purpose and ScopeThe purpose of this technical report is to provide a summary of the various options andtechniques available at the Database layer. We look into each of the features Microsoft has builtinto the Database engine and how they fit into the business continuity strategy. The targetaudience is database and systems administrators, decision makers and architects that arebeginning to implement a business continuity strategy.OverviewDELL / EMC provide a full range of solutions to manage data within the datacenter for replicationand disaster recovery. As part of a Business continuity strategy, careful consideration need beapplied to safeguard mission-critical databases.These storage-based solutions are extremely efficient in replicating large amounts of data in SQLServer databases stored in DELL / EMC storage systems without utilizing database serverresources.Depending on the strategy used to safe guard data in case of failure and business requirements,these types of replication exists:Local – usually within same datacenter or even within storage enclosuresRemote replication – usually in separate datacenters The DELL / EMC replication software SnapView provides local replication while MirrorView/S, MirrorView/A, SAN Copy provide remote replication.This section describes how DELL / EMC storage-based replication technology can be utilized fordesigning replication and disaster recovery solutions suitable for Microsoft SQL Serverdeployments. Various DELL / EMC replication software options including SnapView, SAN Copy, and MirrorView are discussed and general guidelines for applying these features in designingSQL Server disaster recovery solutions are provided.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage4
SQL Server Local Replication and RecoveryThe SQL Server local replication and recovery can be done using DELL / EMC SnapViewreplication product.SnapView:SnapView is a storage-based software product option of the DELL / EMC CX3 UltraScale series product lines that can create point-in-time snapshots or full copy clones of storage LUNswithout using host processor cycles. These SnapView snapshots or clones can be used forcapturing a copy of all relevant SQL Server LUNs with minimal impact to the productiondatabase. These snapshots and clones can be presented to secondary servers as independentlyusable database copies for different functions such as debugging, product testing, and datamigration while the production host continues processing. The database can be restarted fromclones if the primary productions LUNs become unavailable or corrupted. SnapView persistentmode ensures the session can be used in a rollback operation and that the session will survivestorage processor failures.SnapView Snapshot:SnapView snapshot is a point-in-time image of a LUN. With SnapView snapshots, the entire SQLServer database can be replicated in seconds because snapshots are pointer-based. For eachsnapshot session, SnapView keeps track of how the source LUN looks at a particular point intime. Any writes to the source LUN results in SnapView storing a copy of the original data on areserved LUN. This copy is referred to as copy-on-first-write (COFW) and occurs only once foreach data block that is modified on the source LUN. The snapshots can be presented to a hostserver for read and write access as shown in Figure2.The snapshot becomes visible to asecondary host server when a snapshot session is activated, and the snapshot included in astorage group projects the snapshot as a distinct LUN to the host.Advantages:It can be created almost instantaneously, and consumes only a small fraction of the totalsize of the source LUN, sufficient to track all original source LUN data that may havebeen changed in the source LUN since the snapshot session was started. The size of thestorage may vary, and is typically 10 to 20 percent of the source LUN depending on howmuch data has been changed. The snapshot storage is allocated from the reserved LUNpool.SnapView can roll back a snapshot session to the source LUN in the event the LUN iscorrupted due to application error or user error.Disadvantages:Because a snapshot contains pointers to the source LUN, heavy concurrent usage of thesource LUN by the production host and the corresponding snapshot by the repurposinghost may result in undesired performance impact.Becomes unusable if the source LUN is damaged.SnapView can manage up to eight snapshot copies of a single source LUN. A snapshotis most useful for providing point in time view of a SQL database that can be used forperforming short duration functions such as online backup and production testing. Thesnapshot should not be used as permanent operational backup storage for a SQL Serverdatabase, because losing the source LUN makes the snapshot unusable.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage5
1. SnapView createssnapshots ofdatabase LUNsSQL Production HostData2. Mount snapshots onsecondary hosts forquick testing and backupto tape.LogSQL Test Host3. SnapView sessioncan be rolled back to thesource LUN in case oferrorsSnapshotSQL Backup HostCX3 SeriesFigure 1. Local replication using SnapView snapshotsSnapView Clone:SnapView clone is a point-in-time physically separate and complete copy of the production LUN,rather than a copy-on-first-write model of snapshots.Advantages:Clones provide better data availability and performance than snapshots do.Unlike a snapshot, which is unusable if the production LUN is damaged, a fully populatedclone is usable even if the production storage spindles or storage processor fail.Once the clone is created, it needs to be synchronized, which is the process of copying the datafrom the source LUN to the clone. While the clone is being synchronized, the source LUN can stillbe accessed by the production host. Initial synchronization normally takes longer to complete,depending on the size of the source LUN. After the synchronization, it can be fractured from thesource LUN. A change tracking map is maintained to keep track of any changes that may havebeen made to the source but have not yet been reflected in the clone. The map content ispersistently stored in a special area in the reserved LUN pool, so the change delta information willalways be accurate even should a storage system failure. The tracking map is used to drivebitwise resynchronization when needed.Like a snapshot, a clone can be presented to a host server for read and write access when it is inthe fractured state. Because the clone typically occupies space on disks separate from where thesource LUN data resides, concurrent use of both source and clone will not interfere with eachother. A clone’s reverse-synchronization feature can be used for replacing the contents of theproduction LUN with the contents of the clone in case the production LUN becomes corrupteddue to user or application errors or an earlier point-in-time data is desired. Figure 3 shows howclones can be used for replicating and reverse synchronizing SQL Server database LUNs. Aclone can be used for mirroring, but it has the additional overhead of mirroring writes whenunfractured. SnapView can create up to eight clones of a production LUN. Snapshots can also becreated from clones, allowing a maximum of eight snapshots of each clone.Reverse-synchronize clones back to the production LUNs in case of production databasecorruption.Disadvantages:Can take considerable amount of time to create depending on database size.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage6
Clones take more storage space than snapshots since they are exact replicas of theirsource LUNs. Clones are usually created from physical space separate from those usedfor holding the source data.SQL Production HostDataLog3. SnapView reversesynchronizes clones toproduction1. SnapView createsclones of databaseLUNs2. Mount clones onsecondary hosts forlong term testingHostCloneCloneHostCX3Figure 2: Local replication using SnapView clonesSnapView Snapshot of Clones:SQL TestHostTaking Snapshots of already created Clones provides an extra layer of protection in case thesource LUN and Clone become corrupted due to application or user error.SQL Server Remote Replication and RecoveryMicrosoft SQL Server remote replication and recovery can be provided using EMC SAN Copy,MirrorView/S, MirrorView/A products.SAN CopySAN Copy can copy data directly from a source logical unit on one storage system to destinationlogical units on another, without using host resources. SAN Copy can perform multiple copies —each in its own copy session — simultaneously. The RAID type of the logical units participating ina copy session does not have to be the same; that is, the source and destination logical units canbe different RAID types.The SAN Copy software adds value to customer systems by offering the following features:A storage-system based data mover application that uses Storage Area Network (SAN)rather than host resources to copy data resulting in a faster copy process.An easy-to-use web-based application for configuring and managing SAN Copy.Software that you can use in conjunction with replication software, allowing I/O with thesource logical unit to continue during the copy process.Simultaneous sessions that can copy data to multiple Dell/EMC storage systems.Incremental SAN Copy sessions copy only the changed data since the last update.Integration with EMC SnapView software.Integration with EMC MirrorView software.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage7
SAN Copy gives its best performance if the source and destination arrays are connected throughmultiple front-end sources to destination pairs. SAN Copy typically utilizes Fibre Channel betweenprimary and secondary arrays.Creating a consistent SQL Server database copy at the recovery site usingSAN CopyConsistent replicas of SQL Server database replicas suitable for backup or disaster recovery canbe created at the recovery site using SAN Copy in conjunction with SnapView and ReplicationManager as shown in Figure 3. Replication Manager will then quiesce the database with the aidof SQL Server’s VDI mode. Replication Manager in combination with SnapView creates theproduction database clones. Clones are then replicated to the recovery site using SAN Copy. Theconsistent replicas can be mounted on backup servers. The following steps are used for creatinga consistent replica of a SQL Server database at the recovery site.1. Create a target LUN for each source LUN on the recovery site.2. Using Replication Manager (RM), invoke SQL Server VDI mode to quiesce the database.3. Using RM, create production database clones.4. Using RM, disable VDI mode.5. Using RM, replicate clones to the recovery site using SAN Copy.6. Mount consistent clone images on the backup host.Production site1. Replication Manager (RM)invokes SQL Server VDImode to quiesce databaseRecovery site5. Mounts imageson secondaryhostsSQL Production HostDataLog2. RM creates consistentproduction databaseclonesSQL Backup HostData3. RM disablesSQL Server VDILogCloneClone4. SAN Copy replicatesproduction images toremote storageCX3CX3Figure 3. Creating a consistent SQL Server backup copy at the recovery siteSQL Server Log shippingSQL Server database recovery can be performed at the recovery site using production databasetransaction log files. This involves maintaining a standby database at the recovery site andapplying incremental database logs to the standby database to restore production at recovery. Inthis scenario, SnapView creates clones of database log LUNs and they are replicated to therecovery site using SAN Copy as shown in Figure 7. Thereafter, production database log LUNsDell Database Solutions – Business Continuity Solutions on Dell/EMC Storage8
are periodically shipped to the recovery site through SAN Copy. In case of production failure, theincremental database log backups are applied to the standby database to restore production to aspecific point-in-time at the recovery site. The following steps are used for replication andrecovery of the SQL Server database at the recovery site using SnapView and SAN Copy:1. Create a target LUN for each source LUN on the recovery site.2. Using SnapView, create production database clones.3. Fracture clones from source LUNs4. Create a SAN Copy session for each clone.5. Unmount destination LUNs from the host before starting the SAN Copy sessions.6. Start each session and then monitor and complete the sessions.7. Mount log replicas on the recovery host.8. Apply incremental logs to the database using the SQL Server RESTORE DATABASEcommand with the no-recovery option.Production siteRecovery siteSQL Production HostData1. SnapView createsconsistent clone ofproduction databaselog LUN sDataLogSQL Recovery HostLogLog3. Recovery by applyinglog images on standbydatabaseCloneCX32. SAN Copy shipslog image toremote recoverysiteCX3Figure 4. SQL Server log shipping using SAN CopyMirrorViewMirrorView is a storage-based replication software product that can replicate SQL Serverdatabase LUNs to remote locations for disaster recovery.EMC MirrorView provides highly available data storage across a campus environment, across thestate, or across the globe. MirrorView ensures data availability for important business functions.MirrorView is array-based, so there is no impact on servers or applications, and it is easilymanaged from within the Navisphere management software.MirrorView achieves complete data integrity by a feature called Consistency Group in which LUNmembers of this special group are kept synchronized between production and remote sites.MirrorView software offers two complementary mirroring products: MirrorView/S andMirrorView/A.MirrorView/SMirrorView/S is a replication software product that can mirror data images of a production hostLUN synchronously in real time to secondary storage at a remote site. An acknowledgement issent back to the server once the data has been written to both the source and target arrays,Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage9
ensuring a complete transaction on both sides of the mirror. Synchronous mirroring thereforeoffers zero data loss in the event of the failure of a production site.MirrorView/S mirroring has the following features:Disaster Recovery with Minimal OverheadIntegration with EMC SnapViewOperates on array and is transparent to all servers & applicationsConsistent and restorable remote copy at all times.By creating a synchronous mirror between two Dell/EMC systems, MirrorView/S maintains anexact byte-for-byte copy of your production data in a secure, remote location. The mirrored copycan then be used for failover, online restore from backup, and running backups against aSnapView snapshot of the remote mirror.The mirror image of the production data at remote sites can be used to restart production at therecovery site in the case of primary site failure. Bandwidth and latency of the MirrorView/Sinterconnect are critical to successful deployments with SQL Server replication. The greater thedistance is between the production site and the remote protection site, the greater thepropagation latency. Latency must remain low enough to prevent database and applicationbottlenecks that could ripple through to the end user. High bandwidth and low latencyrequirements restrict the use of MirrorView/S for long-distance replication. MirrorView/S istherefore primarily targeted for short-distance (metropolitan/campus) replication.MirrorView/S can work in conjunction with, but is independent of, the other major DELL / EMCsoftware options such as PowerPath software and SnapView snapshots. MirrorView/S workswith LUNs in SAN storage systems, and thus can be used to mirror one or more LUNs that maycompose a SAN Storage GroupMirrorView/AMirrorView/A is an asynchronous replication software that offers long distance replication basedon a periodic incremental-update model. MirrorView/A enables you to utilize existing WANbandwidth for maximum flexibility, shortened time-to-deployment, and extended distances. Itperiodically updates the remote copy of the data with all the changes that occurred on the localcopy since the last update. This can result in minutes of data loss in case of the failure of theprimary site. The performance of MirrorView/A depends on:The distance between production and recovery sitesLatency and bandwidth characteristics of the connecting linkThe amount of data transferDuration of the updateBecause of these performance factors, user must properly size the replication requirementsbefore deploying MirrorView/A for SQL Server disaster recovery. MirrorView/A is generally suitedfor replicating images with lower rates of data changes over long distance on lower bandwidthlinks.MirrorView/A mirroring has the following features:Disaster Recovery with Minimal OverheadBidirectional MirroringIntegration with EMC SnapViewReplication over Long DistancesDell Database Solutions – Business Continuity Solutions on Dell/EMC Storage10
By converting MirrorView FC frames to IP packets, you can typically reduce your communicationlink costs, which can then be reinvested in revenue-generating projects. Moreover, withMirrorView/Asynchronous, unlimited distance is supported so you can also increase the distancebetween target sites beyond the current Fibre Channel connectivity options.In a MirrorView/Asynchronous (MirrorView/A) configuration, a server is attached to the source orproduction Dell/EMC system. The server writes to the source array. There is no need to wait foran acknowledgement from the target array; acknowledgement is immediate, eliminating the delaycaused by distance that MirrorView/S has. This allows MirrorView/A to be configured on a WANand perform over thousands of miles. The resulting mirror can be used for many purposes; mostimportantly, for fast failover to the target site so business operations can continue.MirrorView/A has the ability to size the communication links to the average write workload ratherthan the peak workload, which is what occurs in a traditional asynchronous solution.MirrorView/A remote replication and failoverThe replication involves mirroring SQL Server database LUNs to the recovery site. Failover isinitiated by fracturing the mirror to create a restartable database replica, which is then mountedon the recovery host to restart the database at the recovery site. The following steps are used forreplication and failover using MirrorView/A:1. Establish remote mirrors.2. Create a consistency group.3. Include SQL Server production database LUNs to be replicated in the consistency group.4. Periodically replicate the consistency group to remote mirrors.5. In the event of the failure of the production database, fracture mirrors at the recovery site.6. Promote mirrors to primary state.7. Mount mirrors on a recovery host to restart the database.Production siteRecovery site1. MirrorView/Acreates mirrors ofproduction LUNSQL Production Host4. Mounts secondarymirror images onrecovery hostDataDataLogLog2. MirrorView/Aperiodicallyreplicatesproduction LUNs toremote mirrorsCX3SQL Recovery Host3. Fractures andpromotes mirrorsCX3Figure 5: SQL Server remote replication using MirrorView/ACreating a gold copy of a SQL Server database using clones of mirrorsMirrorView offers clones of mirror feature that allows SnapView clones of a mirror to be used as asecondary image. Clones of mirrors can be used for creating a full copy of a production databaseat a remote recovery site. Performance-sensitive SQL Server applications should consider usingDell Database Solutions – Business Continuity Solutions on Dell/EMC Storage11
clones of mirrors instead of snapshots for restartable copy at the secondary site because unlikesnapshots, clones once fractured do not put load on the primary site. Also clones beingindependent copy of the mirrors provide greater data protection than snapshots.The following steps are used for replication and failover using clones or mirrors:1. Establish remote mirrors of production database LUNs2. Create a consistency group for the LUNs.3. Include LUNs to be replicated in the consistency group.4. Use MirrorView/A, periodically to replicate production LUNs to target mirrors.5. Use SnapView, to create clones of mirrors at the recovery site.6. Use SnapView, to fracture clones of mirrors to create a gold copy of the productiondatabase.7. Mount full copy images on the recovery host in case of a production site failure.Production siteSQL Production Host1. MirrorView/Aperiodicallyreplicatesproduction LUNsto target storagesystemRecovery siteDataDataLogLog2. SnapViewcreates clones ofmirrorsCX33. Mounts secondaryimages onrecovery hostSQL Recovery HostCloneCloneCX3Figure 6. Creating a gold copy of a production database using clones of mirrorsConclusionDELL / EMC data replication solutions provide an additional layer of protection for MicrosoftSQL Server deployments. As part of the business continuity strategy, organizations canbenefit from these replication technologies to maintain data availability and integrity.SQL Server provides native replication solutions to support database recovery in the event ofa disaster. However total disaster protection solutions for SQL Server deploymentsfrequently require more than just the SQL Server database. An example may bedeployments involving federated databases.Federated database environments consist of a collection of distinct databases and otherrelated application data that must maintain data content relationship coherency for theenterprise operation to be supported correctly. In the event of a disaster, while it may bepossible to individually recover the databases replicated through the native solutions, it maybe very difficult, if not impossible, to operationally verify that the recovered databases arecoherently related to each other to allow operation to resume properly.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage12
When these federated data sources are stored and replicated as a consistent group of dataLUNs using DELL / EMC consistency group technology, the consistency of the recovereddata can be guaranteed.Another advantage of DELL / EMC replication is that unlike host-based SQL Serverreplication, DELL / EMC storage-based solutions do not consume host processing cycles,thus avoiding performance impact on the production database. These features are well suitedfor efficient SQL Server replication in low bandwidth environments.Glossary:Recovery Time objective (RTO): The maximum time an outage can be tolerated is referred toas recovery time objective.Recovery Point objective (RPO): The amount of data loss that can be tolerated is referred asrecovery point objective.Consistency group: A set of mirrors that are managed as a single entity and whose secondaryimages always remain in a consistent and restartable state with respect to their primary imageand each other.Fracture: A condition in which I/O is not mirrored to the secondary image; this can happen whenyou initiate the fracture (Admin Fracture) or when the system determines that the secondaryimage is not reachable (System Fracture).Logical unit number (LUN): A unique identifier that is used to distinguish among logical devicesthat share the same bus.Primary image: The LUN containing the production data, the contents of which are replicated tothe secondary image.Promote: The operation by which the administrator changes an image’s role from secondary toprimary. As part of this operation, the previous image becomes a secondary image.Secondary image: The LUN that contains a mirror of a primary image LUN. This LUN mustreside on a different DELL / EMC storage system from the primary image.Local replication: Local replication involves replicating SQL Server database LUNs usingSnapView snapshots or SnapView clones usually within same storage unit. These point-in-timereplicas can be quickly reverted to production in the event of a production corruption due toapplication or user errors.Remote replication: Remote replication replicates SQL Server database LUNs to a remoterecovery site using SAN Copy, MirrorView/S, or MirrorView/A.Synchronous replication: Ensures that data replicated at a secondary site is an identical copyof the primary site. The data update won’t be acknowledged to the host as completed until thedata update is successfully completed at both the primary and secondary sites. This guaranteesthat the data recovered from the secondary server is a completely up-to-date copy of the primarysite data. Used primarily for local or short distance replication.Asynchronous replication: The application does not wait for the data update to beacknowledged from the secondary site. Because of this the application does not suffer from theresponse time degradation caused by each update incurring the cost of a network round trip.Dell Database Solutions – Business Continuity Solutions on Dell/EMC Storage13
Instead application updates are written at the primary site, and data is also queued in order andforwarded to the secondary site as network bandwidth allows. Asynchronous replication isprimarily used for long distance replication.Failover: Failover is the process of switching the production processing to another host in thesame site or to another site in the event of unanticipated production site failure or for a scheduledmaintenance of the production site. The transitioning of operation can be achieved through eitherrestart or recovery approaches.Restart: Restart involves rep
The DELL / EMC replication software SnapView provides local replication while MirrorView/S, MirrorView/A, SAN Copy provide remote replication. This section describes how DELL / EMC storage-based replication technology can be utilized for designing replication and disaster recovery solutions suitable for Microsoft SQL Server deployments.
