Cơ sở dữ liệu - Chapter 7: Maintaining high availability

Chapter 7Maintaining High AvailabilityAgendaAvailabilityLog shippingData mirroringClustering1. AvailabilityHigh availability isn’t always about full-time operations, but, about services being accessible to your users when they need them2. Log shippingLog shipping is: To send transaction logs from the primary database to the secondary database. To back up the transaction logs from a primary database and then copy and restore them to a secondary database, keep the secondary database nearly synchronized with the primary database. The destination server acts as a backup server.Log shipping can be used with databases using the full or bulk-logged recovery models.2. Log shippingLog shipping architecture: 2. Log shippingLog shipping architecture: Primary server: The SQL instance that needs to be protected in case of hardware failure, software error, ... The primary server is configured to execute a SQL Agent job to back up the transaction log to a fileSecondary server:The backup SQL Server 2008 instance that maintains a copy of the primary server database. The secondary server is configured with two SQL Agent jobs: one to copy the transaction-log file from the shared backup folder and the other to restore the transaction log2. Log shippingLog shipping architecture: Monitor server: Having a monitor server as part of log shipping is optional but recommended. When the monitor server participates in log shipping, it manages the jobs that produce monitoring information (such as the last time the transaction log was backed up on the primary server, the last transaction log that was restored on the secondary server, )2. Log shippingLog shipping process: Back up the transaction log on the primary server. A SQL Agent job on the primary server backs up the transaction log at a user-configurable time interval to a file in a backup folder.Copy the transaction log to the secondary server. A SQL Agent job on the secondary server accesses the backup folder on the primary server to copy the transaction-log file to a local folder on the secondary server.Restore the transaction log on secondary server. A SQL Agent job on the secondary server restores the transaction log on the secondary server. 2. Log shippingLog shipping deployment: Before beginning the log shipping process, need to do some initial configuration first.Then, choose regarding how to deploy: using the SQL Server 2008 Management Studio or using T-SQL scripts. Typically, a DBA uses SQL Server 2008 Management Studio to configure log shipping and then generates SQL scripts for future redeployment.2. Log shippingLog shipping deployment: Initial configuration: Create a backup folder that the primary server can access; share it, and ensure that it is accessible by the secondary server.Create a destination folder on the secondary server, The secondary server’s SQL Agent account or the proxy account executing the job must have read and write permission to this folder.The recovery model for the log-shipped database must be set to either Full or bulk_logged2. Log shippingEnabling log shipping involves the following basic steps:Choose servers for your primary server, secondary server, and optional monitor server.Chose the backup compression behavior of log backups if neccessary. Create a file share for the transaction log backups.Choose a backup schedule for the primary database.Create a folder for each secondary server into which the transaction log backup files will be copied. (These folders are usually located on the secondary servers.)Configure one or more secondary databases.3. Database mirroringDatabase mirroring is a primarily software solution for increasing database availability. Mirroring is implemented on a per-database basis and works only with databases that use the full recovery model. Database mirroring maintains two copies of a single database that must reside on different server instances of SQL Server Database Engine. 3. Database mirroringOne server instance serves the database to clients (the principal server). The other instance acts as a server (the mirror server), depending on the configuration and state of the mirroring session. When a database mirroring session is synchronized, database mirroring provides a hot standby server that supports rapid failover without a loss of data from committed transactions. When the session is not synchronized, the mirror server is typically available as a warm standby server (with possible data loss). 3. Database mirroring Database mirroring architecture:3. Database mirroring Database mirroring architecture:Principal Server—The principal server hosts the copy of the database that clients connect to and interact with. As transactions occur on the principal database, the transaction log records are forwarded to the mirror database.Mirror Server—The mirror server hosts a copy of the principal database and applies the transaction log records sent by the principal database to keep the mirrored database in sync with the principal database.3. Database mirroring Database mirroring architecture:Witness Server (optional):Needed if automatic failover to the mirror server is required in the case of a principal database failure. The witness server monitors the status of the principal and mirror servers in a high-availability configuration3. Database mirroring How database mirroring works:The principal and mirror servers communicate and cooperate as partners in a database mirroring session. The two partners perform complementary roles in the session: the principal role and the mirror role. Database mirroring involves redoing every insert, update, and delete operation that occurs on the principal database onto the mirror database as quickly as possible. 3. Database mirroring How database mirroring works:Redoing is accomplished by sending every active transaction log record to the mirror server, which applies log records to the mirror database, in sequence, as quickly as possible. Unlike replication, which works at the logical level, database mirroring works at the level of the physical log record.3. Database mirroring Database mirroring modes:The database can be configured in 3 operating modes:High-performance modeHigh-safety without automatic failover modeHigh-safety with automatic failover mode.3. Database mirroring Database mirroring modes:High-performance mode:Use asynchronous processing.The principal server sends an acknowledgment to the client application of a successful transaction as soon as it sends the corresponding log record to the mirror server, but it does not wait for acknowledgment from the mirror server that the log record was received. Under normal workload conditions, the latency between the principal and the mirror is relatively small. However, if the principal server is under heavy workload, this can increasethe gap between the two partners.3. Database mirroring Database mirroring modes:High-Safety without Automatic Failover Mode:Support synchronous operation. When a session starts, the mirror server synchronizes the mirror database together with the principal database as quickly as possible. As soon as the databases are synchronized, a transaction is committed on both partners3. Database mirroring Database mirroring modes:High-Safety with Automatic Failover Mode:A witness server is used to provide automatic failover. The witness server does not directly participate in the mirroring process but acts as an overseer between the two servers. Within the context of a database mirroring session, the principal and mirror roles are typically interchangeable in a process known as role switching.3. Database mirroring Database mirroring modes:High-Safety with Automatic Failover Mode Role switching involves transferring the principal role to the mirror server. In role switching, the mirror server acts as the failover partner for the principal server. When a role switch occurs, the mirror server takes over the principal role and brings its copy of the database online as the new principal database. The former principal server, if available, assumes the mirror role, and its database becomes the new mirror database. Potentially, the roles can switch back and forth repeatedly.3. Database mirroring Configure Database mirroring :To set up mirroring, begin by opening the Mirroring page on the Properties sheet of the database. 3. Database mirroring Configure Database mirroring :Clicking on the ‘‘Configure Security’’ button will launch a Wizard to confige the connection options for the principal, mirror, and witness server endpoints.Identify which servers will be configured through the wizard. Configure the options for the principal serverConfigure the same information for the mirror server. 3. Database mirroring Monitor Database mirroring :On both the principal and mirror servers, the following system catalog views can be used to view the status and configuration of all mirrors on that server:sys.database_mirroringsys.database_mirroring_endpoints3. Database mirroring Monitor Database mirroring :In addition, user can use “Launch Database Mirroring Monitor’’ function from the Tasks menu of a database to view or configure the current settings for s mirroring condition.4. Clustering Clustering is the most important technology supported by Database Engine to achieve high availability.It is a process in which the operating system and database system work together to provide availability in the event of failures. A failover cluster consists of a group of redundant servers, called nodes, that share an external disk system. When a node within the cluster fails, the instance of Database Engine on that machine shuts down. Microsoft Cluster Service transfers resources from a failing machine to an equally configured target node automatically. The transfer of resources from one node to the other node in a cluster occurs very quickly.4. Clustering Clustering Component includesA couple of servers. Although having identical hardware in both servers is not required, at the very least, the hardware in both servers should be comparable (same processors, disk configuration, memory, etc.). A shared storage device with at least two volumes. One volume will be used as a witness disk, which holds the cluster configuration information. The other data volumes used for applications.4. Clustering Clustering models:Active/Passive Clustering: For a single application, one node is designated as the primary node.All requests that come into the cluster virtual server are directed to this primary cluster. Secondary nodes are failover nodes and only become active when the primary node is unavailable and no heartbeat exists for that node. When this happens, a secondary node begins taking over the ork for the cluster, allowing applications to continue running with little noticeable interruption. 4. Clustering Clustering models:Active/Passive Clustering: 4. Clustering Clustering models:Active/Active Clustering: Ensure that everyone is playing an equal part in making this application highly available.Describe a scenario in which more than one cluster-aware application is running, and each application has a different node configured as the primary node for that application. 4. Clustering Clustering models:Active/Passive Clustering: