Mecanismos de alta disponibilidad con Microsoft SQL Server 2008 Por: ISC Lenin López Fernández de Lara Temario Database Mirroring Log Shipping Replication Failover Clustering Demo 1.1 Database Mirroring Roles 1.2 Operating Modes High Availability High Performance High Safety 1.2.1 High Availability Operating Mode Automatic failover Uses simple ping Performance impact Writes a transaction to th transaction log synchronous The transaction is first committed on the mirror database 1.2.2 High Performance Operating Mode No automatic failover Asynchronous 1.2.3 High Safety Operating Mode Manually failover Performance impact Writes a transaction to th transaction log Synchronous The transaction is first committed on the mirror database Log Shipping ¿What is? Provides a means to maintain a secondary server on an automated basis using a chain of transaction log backups Also allows you to configure a monitor server that can verify the health 1.1 Log Shipping Scenarios Offloading Report Activity Initialization for Database Mirroring Upgrading Versions or Migrating to a New Platform Primary or Secondary Availability Solution 1.1.1 Offloading Report Activity Reporting server Standby Mode SELECT statements 1.1.2 Initialization for Database Mirroring backups of the principal minimizes the time principal and mirror are synchronized 1.1.3 Upgrading Versions or Migrating to a New Platform build the new instance move the databases a brief outage on the applications 1.1.4 Primary or Secondary Availability Solution Secondary databases that applications can switch an outage of the primary database 1.2 Log Shipping Components Replication ¿What is? Replication is designed as a data-distribution mechanism. The core replication engine is designed for very flexible implementation The core architecture can be used to provide availability for a database because a redundant copy of data is maintained in synchronization with a master copy 1.1 Replication Components Articles • The basic building block of replication • Can be defined against a table, view, stored procedure, or function Publications • Publications are groupings of articles that define the replication set Filters • You can apply one or more filters to each article that restrict the set of data that is replicated. • You can fi lter articles by rows or by columns 1.2 Replication Roles publisher • Maintains the master copy of the data within a replication architecture subscriber • Is the database that is receiving changes from the replication engine defined by the publication to which it is subscribing distributor • Is the main engine within a replication architecture • The distribution database is stored on the instance that is configured as the distributor (An instance of SQL). 1.3 Replication Topologies Central Publisher Central Subscriber Other 1.3.1 Central Publisher Topology 1.3.2 Central Subscriber Topology 1.6 Replication Methods Snapshot Replication Transactional Replication Merge Replication 1.6.1 Snapshot Replication It is not normally used for high availability 1. Snapshot Agent extracts the schema and BCPs the data 2. Distribution Agent then picks up and applies the snapshot to each subscriber (tables are dropped and recreated, then the data is copied using BCP) full replace of data 1.6.2 Transactional Replication Begins with an initial snapshot being applied to the subscriber to ensure that the two databases are synchronized As subsequent transactions are issued against the publisher, the replication engine applies them to the subscriber. 2.2 Transactional Options Merge Replication ¿What is? Merge replication is another alternative that can be applied to high-availability systems. Merge replication was primarily designed for mobile, disconnected users. By translation, the mechanisms are already built in for changes to occur at any location and get synchronized, as well as to be able to withstand failures and continue processing 3.1 Change Tracking (synchronize process) MSmerge _genhistor y MSmerge _genhistor y MSmerge_to mbstone MSmerge_to mbstone Windows Clustering ¿What is? Windows clustering enables multiple pieces of hardware to act as a single platform for running applications. 1.1 Windows Cluster Components 1.2 Types of Clusters Standard Windows Cluster Majority Node Set Cluster 2.2 Failover Cluster Instance Components The components that you need to configure for a SQL Server failover clustered instance are the following: IP addresses Network names Disk drives on the shared drive array SQL Server services Service accounts Demo Mirroring High Availability ¿######?